CentOS7系统下安装PostgreSQL17数据库

系统：CentOS7.9

关闭了系统防火墙firewalld服务和SELinux

一、安装

1、下载安装包

wget https://ftp.postgresql.org/pub/source/v17.4/postgresql-17.4.tar.gz

2、解压安装包

tar -xf postgresql-17.4.tar.gz

3、安装依赖

yum install gcc gcc-c++ make libicu-devel bison flex readline-devel zlib-devel

4、编译安装

cd postgresql-17.4/

./configure --prefix=/usr/local/pgsql

make

make install

5、添加数据库用户，并创建数据存储目录

useradd postgres

mkdir /usr/local/pgsql/data

chown postgres:postgres /usr/local/pgsql/data

6、配置环境变量

vim /etc/profile.d/pgsql.sh
export PGHOME=/usr/local/pgsql
export PGDATA=/usr/local/pgsql/data
export PATH=$PGHOME/bin:$PATH

#使用环境变量生效
source /etc/profile.d/pgsql.sh

7、 系统参数优化

vim /etc/sysctl.conf
#添加以下内容
vm.nr_hugepages = 6144

#使用参数生效
sysctl -p

8、数据库初始化

#切换到postgres用户
su - postgres

#执行数据初始化
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

#退出postgres用户
exit

9、修改配置文件

vim /usr/local/pgsql/data/postgresql.conf
#修改以下配置
listen_addresses = '*'
max_connections = 300
shared_buffers = 4GB
work_mem = 64MB
maintenance_work_mem = 1GB
effective_cache_size = 12GB
wal_buffers = 16MB
checkpoint_completion_target = 0.9

• listen_addresses：监听地址，默认使用localhost，使用’*'表示所有
• max_connections：决定允许的最大数据库连接数。过多的连接会增加系统开销和资源竞争。通常可以使用连接池工具（如PgBouncer）来控制并发连接数；
• shared_buffers：这是PostgreSQL用于缓存表数据的共享内存区域，通常建议设置为物理内存的25%-40%。如果设置过低，会导致频繁的磁盘访问；设置过高则会占用操作系统内存，减少可用的文件缓存；
• work_mem：每个查询操作（如排序、哈希表）所使用的内存。这个参数是每个查询连接单独分配的，因此需要根据查询复杂度和并发量合理设置。如果过小，查询需要频繁进行磁盘交换；过大会导致内存不足。典型值在10MB-100MB之间；
• maintenance_work_mem：控制PostgreSQL在执行维护操作时使用的内存大小，比如创建索引、VACUUM。推荐设置为较大的值，尤其是在大规模数据集上操作时；
• effective_cache_size：PostgreSQL根据此参数判断系统可用的文件系统缓存大小，从而决定是否使用索引扫描或全表扫描。建议设置为物理内存的50%-75%；
• wal_buffers：建议设置为shared_buffers的1/32，用于缓冲WAL数据，避免频繁写入磁盘；
• checkpoint_completion_target：设置为接近1的值可以平滑WAL日志写入压力，减少突发I/O操作。

10、启动服务

(1) 使用自带脚本方式启动

#复制源码包里的脚本至etc/init.d目录下，并加执行权限
cp ./contrib/start-scripts/linux /etc/init.d/postgresql
chmod +x /etc/init.d/postgresql

#启动服务
service postgresql start

#设置开机启动
chkconfig --add postgresql

(2) 使用systemd进行管理

service文件如下：

vim /etc/systemd/system/postgresql.service
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
After=network-online.target

[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGDATA=/usr/local/pgsql/data
ExecStart=/usr/local/pgsql/bin/pg_ctl start -D ${PGDATA}
ExecStop=/usr/local/pgsql/bin/pg_ctl stop -D ${PGDATA} -m fast
ExecReload=/home/postgres/bin/pg_ctl reload -D ${PGDATA}
TimeoutSec=300s

[Install]
WantedBy=multi-user.target

systemctl daemon-reload
systemctl start postgresql

如果使用Type=notify要求服务器配置时使用./configure --with-systemd构建

(3) 直接使用命令行启动：

su - postgres

/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data

11、配置远程访问权限

vim /usr/local/pgsql/data/pg_hba.conf
#添加以下内容，管理员用户postgres不允许远程连接
host    all             postgres        0.0.0.0/0               reject
host    all             all             0.0.0.0/0               password

#然后重启数据库
service postgresql restart

二、常用操作

1、登录数据库

#切换到postgres用户
su - postgres
psql

#或在root用户下使用以下命令
psql -U postgres

2、查看当前登录用户/数据库

postgres=# \c
You are now connected to database "postgres" as user "postgres".

postgres=# select user;
   user   
----------
 postgres
(1 row)

postgres=# select current_user;
 current_user 
--------------
 postgres
(1 row)

postgres=# select current_database();
 current_database 
------------------
 postgres
(1 row)

3、创建数据库

postgres=# create database mydb;
CREATE DATABASE

4、切换数据库

postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=# 

\l 列举数据库
\dt 列举表
\d tblname 查看表结构
\di 查看索引

5、创建角色与用户并授权

PostgreSQL使用角色的概念管理数据库访问权限。一个角色可以被看成是一个数据库用户或者是一个数据库用户组，这取决于角色被怎样设置。角色可以拥有数据库对象（例如，表和函数）并且能够把那些对象上的权限赋予给其他角色来控制谁能访问哪些对象。此外，还可以把一个角色中的成员资格授予给另一个角色，这样允许成员角色使用被赋予给另一个角色的权限。

角色的概念把“用户”和“组”的概念都包括在内。在PostgreSQL版本 8.1 之前，用户和组是完全不同的两种实体，但是现在只有角色。任意角色都可以扮演用户、组或者两者。

(1) 创建用户：

postgres=# CREATE USER testuser with password 't123456';
CREATE ROLE

CREATE USER等同于CREATE ROLE， 但CREATE USER默认包含LOGIN，而CREATE ROLE不包含。

如下两者等价，创建具有登录权限的角色：

CREATE ROLE name LOGIN;
CREATE USER name;

查看角色信息：\du 或\du+ 查看，也可以查看系统表 select * from pg_roles;

(2) 创建schema（与用户名同名【租户】）

postgres=# \c mydb;
You are now connected to database "mydb" as user "postgres".
mydb=# create schema testuser;
CREATE SCHEMA

(3) 授权用户的schema所有权限：

mydb=# GRANT ALL PRIVILEGES ON SCHEMA testuser TO testuser;
GRANT

(4) 普通用户登录

psql -U testuser -d mydb

6、回收所有授权

(1) 回收用户在SCHEMA下所有表的所有权限

postgres=# REVOKE ALL PRIVILEGES ON SCHEMA testuser FROM testuser;