PostgreSQL Master-Slave#
Requirements#
dockerNetworks.md
pgmaster#
sudo vi pgmaster/pg_hba.conf
host replication replica 172.18.0.101/32 trust
sudo vi pgmaster/postgresql.conf
listen_addresses = '*' # 监听所有IP
archive_mode = on # 允许归档
wal_level = replica # 开启热备
archive_command = '/bin/date' # 用该命令来归档logfile segment,这里取消归档。
max_wal_senders = 32 # 这个设置了可以最多有几个流复制连接,差不多有几个从,就设置几个
wal_keep_segments = 64 # 设置流复制保留的最多的xlog数目,一份是 16M,注意机器磁盘 16M*64 = 1G
wal_sender_timeout = 60s # 设置流复制主机发送数据的超时时间
max_connections = 100 # 这个设置要注意下,从库的max_connections必须要大于主库的
docker exec -it pgmaster bash
psql -U postgres
show archive_mode;
CREATE ROLE replica login replication encrypted password 'replica';
\du
\q
pgslave#
docker exec -it pgslave bash
su - postgres
rm -rf /var/lib/postgresql/data/*
pg_basebackup -h pgmaster -p 5432 -U replica -Fp -Xs -Pv -R -D /var/lib/postgresql/data &
# 混合云,先存datatmp,再替换data
# pg_basebackup -h pgmaster -p 5432 -U replica -Fp -Xs -Pv -R -D /var/lib/postgresql/datatmp &
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_43"
24636/24636 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/2000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed
sudo vi pgslave/pg_hba.conf
host replication replica 172.18.0.100/32 trust
sudo vi pgslave/postgresql.conf
hot_standby = on
hot_standby_feedback = on
sudo vi pgslave/recovery.conf
standby_mode = on
primary_conninfo = 'host=pgmaster port=5432 user=replica password=replica'
recovery_target_timeline = 'latest'
docker exec -it pgmaster bash
psql -U postgres -c "select client_addr,sync_state from pg_stat_replication;"
psql -U postgres
select client_addr,sync_state from pg_stat_replication;
Tests#
docker stop pgmaster
docker exec -it pgslave bash
su postgres
/usr/lib/postgresql/11/bin/pg_ctl promote
psql -U postgres -c "select pg_is_in_recovery();"
master: f
slave: t
vi pgmaster/recovery.conf
standby_mode = on
primary_conninfo = 'host=pgslave port=5432 user=replica password=replica'
recovery_target_timeline = 'latest'