PostgreSQL 12配置手册

1) 安装PostgreSQL 12
[root@srv1 ~]# yum install yum-plugin-priorities centos-release-scl-rh centos-release-scl -y

[root@srv1 ~]# sed -i -e "s/\]$/\]\npriority=10/g" /etc/yum.repos.d/CentOS-SCLo-scl.repo
[root@srv1 ~]# sed -i -e "s/\]$/\]\npriority=10/g" /etc/yum.repos.d/CentOS-SCLo-scl-rh.repo

[root@srv1 ~]# sed -i -e "s/enabled=1/enabled=0/g" /etc/yum.repos.d/CentOS-SCLo-scl.repo
[root@srv1 ~]# sed -i -e "s/enabled=1/enabled=0/g" /etc/yum.repos.d/CentOS-SCLo-scl-rh.repo

[root@srv1 ~]# yum --enablerepo=centos-sclo-rh install rh-postgresql12-postgresql-server -y

2) 初始化PostgreSQL
[root@srv1 ~]# scl enable rh-postgresql12 bash

[root@srv1 ~]# postgres -V
postgres (PostgreSQL) 12.1

[root@srv1 ~]# which postgres
/opt/rh/rh-postgresql12/root/usr/bin/postgres

# 实现环境自动加载
[root@srv1 ~]# vim /etc/profile.d/rh-postgresql12.sh
source /opt/rh/rh-postgresql12/enable
export X_SCLS="`scl enable rh-postgresql12 'echo $X_SCLS'`"

[root@srv1 ~]# source /etc/profile.d/rh-postgresql12.sh

[root@srv1 ~]# postgresql-setup --initdb --unit rh-postgresql12-postgresql
 * Initializing database in '/var/opt/rh/rh-postgresql12/lib/pgsql/data'
 * Initialized, logs are in /var/lib/pgsql/initdb_rh-postgresql12-postgresql.log

3) 启动PostgreSQL
[root@srv1 ~]# systemctl enable --now rh-postgresql12-postgresql

4) 确认端口开放PostgreSQL
# PostgreSQL默认仅监听本地端口
[root@srv1 ~]# netstat -lnatp | grep 5432
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      2731/postmaster     
tcp6       0      0 ::1:5432                :::*                    LISTEN      2731/postmaster 

# PostgreSQL配置文件中所定义的默认仅监听本地端口
[root@srv1 ~]# grep listen_addresses /var/opt/rh/rh-postgresql12/lib/pgsql/data/postgresql.conf
#listen_addresses = 'localhost'         # what IP address(es) to listen on;

5) PostgreSQL默认的身份认证方法---均为本地认证
[root@srv1 ~]# grep -v -E "^#|^$" /var/opt/rh/rh-postgresql12/lib/pgsql/data/pg_hba.conf
local   all             all                                     peer
host    all             all             127.0.0.1/32            ident
host    all             all             ::1/128                 ident
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

1) PostgreSQL的账户的创建并确认---必须与系统账户一致才可进行连接
[root@srv1 ~]# useradd snow

[root@srv1 ~]# su - postgres
[postgres@srv1 ~]$ createuser snow
[postgres@srv1 ~]$ createdb testdb -O snow

[postgres@srv1 ~]$ psql -c "select usename from pg_user;"
 usename  
----------
 postgres
 snow
(2 rows)

[postgres@srv1 ~]$ psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | snow     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

[postgres@srv1 ~]$ exit

2) 使用新添加的账户连接所创建的DB并进行简单的测试操作
[root@srv1 ~]# su - snow
[snow@srv1 ~]$ psql testdb
psql (12.1)
Type "help" for help.

testdb=> \du    # 显示账户角色
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 snow      |                                                            | {}

testdb=> \l    # 显示数据库
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | snow     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

testdb=> create table test_table (no int, name text);     # 创建test_table表
CREATE TABLE

testdb=> \dt     # 显示表
          List of relations
 Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
 public | test_table | table | snow
(1 row)

testdb=> insert into test_table (no,name) values (01,'CentOS');      # 插入一条数据
INSERT 0 1

testdb=> select * from test_table;       # 查询指定表中的数据
 no |  name  
----+--------
  1 | CentOS
(1 row)

testdb=> drop table test_table;        # 删除表
DROP TABLE

testdb=> \dt
Did not find any relations.

testdb=> \q        # 退出

[snow@srv1 ~]$ dropdb testdb        # 删除数据库
[snow@srv1 ~]$ psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

1) 修改配置文件
[root@srv1 ~]# vim /var/opt/rh/rh-postgresql12/lib/pgsql/data/postgresql.conf
......
......
# 取消59行注释,并允许PostgreSQL监听本地所有地址
listen_addresses = '*'          # what IP address(es) to listen on;
......
......

[root@srv1 ~]# vim /var/opt/rh/rh-postgresql12/lib/pgsql/data/pg_hba.conf
......
......

# 于文件最底部追加如下内容---允许验证远程
host    all             all             192.168.10.0/24             md5

[root@srv1 ~]# systemctl restart rh-postgresql12-postgresql

2) 防火墙配置
[root@srv1 ~]# firewall-cmd --add-service=postgresql --permanent
success
[root@srv1 ~]# firewall-cmd --reload
success

3) 设定PostgreSQL的snow账户的密码
[snow@srv1 ~]$ psql -d testdb
psql (12.1)
Type "help" for help.

testdb=> \password
Enter new password: 
Enter it again: 

testdb=> \q

或

[root@srv1 ~]# su - postgres
[postgres@srv1 ~]$ psql -c "alter user snow with password 'password';"
ALTER ROLE

4) 测试
[root@srv2 ~]# psql -h srv1.1000y.cloud -d testdb -U snow
Password for user snow:   # 输入PostgreSQL的snow账户的密码
psql (12.1)
Type "help" for help.

testdb-> \q
[root@srv2 ~]# 

1) 生成SSL证书
[root@srv1 ~]# cd /etc/pki/tls/certs
[root@node1 certs]# openssl genrsa -aes128 2048 > postgres.key
Generating RSA private key, 2048 bit long modulus (2 primes)
......+++++
.................+++++
e is 65537 (0x010001)
Enter pass phrase:     # 设定密码
Verifying - Enter pass phrase:

[root@node1 certs]# openssl rsa -in  postgres.key -out  postgres.key
Enter pass phrase for  postgres.key:     # 输入密码,完成脱密
writing RSA key

[root@node1 certs]# openssl req -utf8 -new -key  postgres.key -out  postgres.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:BeiJing
Locality Name (eg, city) [Default City]:BeiJing
Organization Name (eg, company) [Default Company Ltd]:1000cc.net
Organizational Unit Name (eg, section) []:tech
Common Name (eg, your name or your server's hostname) []:node1.1000cc.net
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

[root@node1 certs]# openssl x509 -in  postgres.csr -out  postgres.crt -req -signkey  postgres.key -days 3650
Signature ok
subject=/C=CN/ST=BeiJing/L=BeiJing/O=1000cc.net/OU=tech/CN=node1.1000cc.net
Getting Private key

2) 将所生成的key及crt复制到PostgreSQL目录
[root@srv1 certs]# cp postgres.* /var/opt/rh/rh-postgresql12/lib/pgsql/data/
[root@srv1 certs]# chown postgres /var/opt/rh/rh-postgresql12/lib/pgsql/data/postgres.*
[root@srv1 certs]# chmod 600 /var/opt/rh/rh-postgresql12/lib/pgsql/data/postgres.*
[root@srv1 certs]# cd
[root@srv1 ~]# 

3) 配置PostgreSQL以支持SSL
[root@srv1 ~]# vim /var/opt/rh/rh-postgresql12/lib/pgsql/data/postgresql.conf
......
......
# 取消100行注释,并开启ssl
ssl = on
#ssl_ca_file = ''
# 取消102行注释,并指定SSL证书文件
ssl_cert_file = '/var/opt/rh/rh-postgresql12/lib/pgsql/data/postgres.crt'
#ssl_crl_file = ''
# 取消104行注释,并指定SSL Key文件
ssl_key_file = '/var/opt/rh/rh-postgresql12/lib/pgsql/data/postgres.key'
......
......

[root@srv1 ~]# vim /var/opt/rh/rh-postgresql12/lib/pgsql/data/pg_hba.conf
......
......

# 于文件最底部追加如下内容
hostssl all             all             192.168.10.0/24             md5

[root@srv1 ~]# systemctl restart rh-postgresql12-postgresql

4) 测试
[root@srv1 ~]# su - snow
[snow@srv1 ~]$ psql -h srv1.1000y.cloud testdb
Password for user snow: 
psql (12.1)
# 当看到TLS....字样时即意味着Postgre SQL已支持SSL
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

testdb=> \q
[snow@srv1 ~]$

1) 所有节点均需安装 PostgresSQL12

2) 配置Master环境
[root@srv1 ~]# vim /var/opt/rh/rh-postgresql12/lib/pgsql/data/postgresql.conf
......
......

# 取消59行注释，并指定Postgre监听所有本地IP地址
listen_addresses = '*'
......
......

# 取消193行注释
wal_level = replica
......
......

# 取消198行注释
synchronous_commit = on
......
......

# 取消286行注释
max_wal_senders = 10
......
......

# 取消288行注释,并修改为以下内容
wal_keep_segments = 10
......
......

# 取消300行注释
synchronous_standby_names = '*'
......
......

[root@srv1 ~]# vim /var/opt/rh/rh-postgresql12/lib/pgsql/data/pg_hba.conf
......
......

# 于文件最底部追加如下内容,并将默认的replication为local地址的行注释
#host    replication     all             127.0.0.1/32            ident
#host    replication     all             ::1/128                 ident
host    replication     rep_user        192.168.1.11/32            md5
host    replication     rep_user        192.168.1.12/32            md5

3) 创建账户
[root@srv1 ~]# su - postgres

[postgres@srv1 ~]$ createuser --replication -P rep_user
Enter password for new role: 
Enter it again: 

[postgres@srv1 ~]$ exit
logout

[root@srv1 ~]# systemctl restart rh-postgresql12-postgresql

4) 配置Slave Server
[root@srv2 ~]# systemctl stop systemctl restart rh-postgresql12-postgresql

# 删除原有的数据
[root@srv2 ~]# rm -rf /var/opt/rh/rh-postgresql12/lib/pgsql/data/*

# 同步Master的数据
[root@srv2 ~]# su - postgres
[postgres@srv2 ~]$ pg_basebackup -R -h srv1.1000y.cloud -U rep_user \
-D /var/opt/rh/rh-postgresql12/lib/pgsql/data -P
Password: 
32626/32626 kB (100%), 1/1 tablespace

[postgres@srv2 ~]$ exit

[root@srv2 ~]# vim /var/opt/rh/rh-postgresql12/lib/pgsql/data/postgresql.conf
......
......

# 取消59行注释，并指定Postgre监听所有本地IP地址
listen_addresses = '*'
......
......

# 取消315行注释
hot_standby = on
......
......

[root@srv2 ~]# vim /var/opt/rh/rh-postgresql12/lib/pgsql/data/pg_hba.conf
......
......

# 于文件最底部追加如下内容,并将默认的replication为local地址的行注释
#host    replication     all             127.0.0.1/32            ident
#host    replication     all             ::1/128                 ident
host    replication     rep_user        192.168.1.11/32            md5
host    replication     rep_user        192.168.1.12/32            md5

[root@srv2 ~]# vim /var/opt/rh/rh-postgresql12/lib/pgsql/data/postgresql.auto.conf
# 将[application_name]添加到自动生成的身份验证文件---名字可自行定义
primary_conninfo = 'user=rep_user password=p0pswd07 host=srv1.1000y.cloud port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any application_name=srv2'

[root@srv2 ~]# systemctl start rh-postgresql12-postgresql

5) 测试
[root@srv1 ~]# su - postgres
[postgres@srv1 ~]$ psql -c "select usename, application_name, client_addr, state, sync_priority, sync_
state from pg_stat_replication;"
 usename  | application_name | client_addr  |   state   | sync_priority | sync_state 
----------+------------------+--------------+-----------+---------------+------------
 rep_user | srv2             | 192.168.1.12 | streaming |             1 | sync
(1 row)