PostgreSQL主从配置

环境准备

服务器	IP	主机名
primary server	192.168.0.100	ecs-76d5-0001
standby server	192.168.0.101	ecs-76d5-0002

在两个节点按照PostgreSQL安装安装好PG数据库。

配置主服务器

主库添加一个用于复制的用户replica

CREATE ROLE replica REPLICATION LOGIN PASSWORD '123456';

主库添加白名单

在文件 /var/lib/pgsql/13/data/pg_hba.conf 下添加：

host     all             all          192.168.0.1/24          trust
# 允许从库通过replica用户连接主库
host   replication      replica       192.168.0.101/32          md

主库创建归档目录

mkdir /var/lib/pgsql/13/archivelog

主库设置，开启归档

/var/lib/pgsql/13/data/postgresql.conf

listen_addresses = '*' 
port = 5432
max_connections = 100 
max_wal_size = 1GB
min_wal_size = 80MB
log_timezone = 'Asia/Shanghai'
archive_mode = on
archive_command = 'test ! -f /var/lib/pgsql/13/archivelog/%f && cp %p /var/lib/pgsql/13/archivelog/%f'
wal_level = replica
max_wal_senders = 10
wal_sender_timeout = 60s

配置完重启主库

systemctl restart postgresql-13.service

从库配置

同步主库的data目录

# 删除从库的data目录
rm -rf /var/lib/pgsql/13/data
# 同步主库的data目录，pg_basebackup是PostgreSQL自带的基础备份工具
pg_basebackup -h 192.168.0.100 -U replica -D /var/lib/pgsql/13/data -X stream -P

修改data目录的权限

chmod -R 700 /var/lib/pgsql/13/data

创建文件standby.signal(版本11开始)

/var/lib/pgsql/13/data/standby.signal
（pg版本11后已经废除recovery.conf）

# 表示该节点是从库
standby_mode = on 

修改从库的postgresql.conf文件

primary_conninfo = 'host=192.168.0.100  port=5432  user=replica  password=123456'
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on

重启从库

systemctl restart postgresql-13.service

验证主库从库时候同步成功

主库查询

postgres=# select * from pg_stat_replication;
  pid  | usesysid | usename | application_name |  client_addr  | client_hostname | client_port |         backend_start         | 
backend_xmin |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | 
sync_state |          reply_time           
-------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+-
-------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+-
-----------+-------------------------------
 21228 |    16384 | replica | walreceiver      | 192.168.0.101 |                 |       39558 | 2022-03-22 23:16:39.903294+08 | 
         490 | streaming | 0/C000A58 | 0/C000A58 | 0/C000A58 | 0/C000A58  |           |           |            |             0 | 
async      | 2022-03-22 23:19:00.131093+08
(1 row)