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)
