postgresql11做主从

主库
sudo -u postgres psql
create user replica with replication password '123456';

vim /etc/postgresql/11/main/postgresql.conf
listen_addresses = '*' # 监听所有IP
archive_mode = on # 允许归档
archive_command = '/bin/date' # 用该命令来归档logfile segment,这里取消归档。
wal_level = replica #开启热备
max_wal_senders = 32 # 这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个
wal_keep_segments = 64 ＃ 设置流复制保留的最多的xlog数目，一份是 16M，注意机器磁盘 16M*64 = 1G
wal_sender_timeout = 60s ＃ 设置流复制主机发送数据的超时时间
max_connections = 200 # 这个设置要注意下，从库的max_connections必须要大于主库的

从库：
su - postgres
pg_basebackup -D /var/lib/postgresql/slave -Fp -Xs -v -P -h 192.168.219.20 -U replica -W
在slave目录添加recovery.conf
standby_mode = on
primary_conninfo = 'host=192.168.219.20 port=5432 user=replica password=123456'
recovery_target_timeline = 'latest'

#recovery_target_time = '2020-04-03 14:09:59'

 

修改postgresql.conf
wal_level = replica
max_connections = 1000
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on

 

从库还可以：

pg_basebackup -R -D /var/lib/postgresql/11/main3 -Fp -Xs -v -P -h 192.168.219.30 -U replica -W        #使用了-R 会在main3里面自动添加recovery.conf

 

---

检查主库备库：

/usr/lib/postgresql/11/bin/pg_controldata -D /var/lib/postgresql/11/main

备库状态：Database cluster state:               in archive recovery

主库状态：Database cluster state:               in production