备:172.30.78.2
先按照https://blog.csdn.net/shipeng1022/article/details/108887170的方式搭建从库,不需要初始化数据库。
1 修改pg_hba以及将主库数据拉到备库
#主备添加下列内容
[postgres@dbserver data]$ vi pg_hba.conf
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 172.30.78.7/32 trust
host replication all ::1/128 trust
host replication repuser 172.30.78.2/32 md5
host replication repuser 172.30.78.7/32 md5
[postgres@dbserver data]$ pg_basebackup -D $PGDATA -Fp -R -P -v -h 172.30.78.7 -p 5432 -U repuser
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
WARNING: skipping special file "./.s.PGSQL.5432"
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: DE/9000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_25000"
WARNING: skipping special file "./.s.PGSQL.5432"013/00000001000000DE00000006)
5262211/5262211 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: DE/9000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed
2 修改standby.signal
[postgres@dbserver data]$ vi standby.signal
standby_mode = 'on'
3 修改postgres.conf
#从机配置 增加以下内容
primary_conninfo = 'host=172.30.78.2 port=5432 user=repuser password=repuser'
recovery_target_timeline = latest #默认
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on
4 重新启动从库
5验证
主库插入数据
[postgres@ycdb ~]$ psql -h 172.30.78.7
psql (12.2)
Type "help" for help.
postgres=# select * from t;
id | id2
----------+----------
10000000 | 10000000
10000000 | 10000000
(2 rows)
postgres=# insert into t values(1,1);
INSERT 0 1
postgres=# insert into t values(1,2);
从库验证
[postgres@dbserver data]$ psql -h 172.30.78.2
psql (12.2)
Type "help" for help.
postgres=# select * from t;
id | id2
----------+----------
10000000 | 10000000
10000000 | 10000000
1 | 1
1 | 2
6 pg11以及以前版本从库搭建方法
vi /data/pgdata/recovery.conf
standby_mode = 'on'
recovery_target_timeline = 'latest'
primary_conninfo = 'user=repuser password=repuser host=172.20.78.7 port=5432'
重启从库
7 pg11和pg12从库搭建不一致的地方
pg12已经将recovery.conf移除(not supported),在standby.conf以及postgres.conf进行配置。否则从库启动会报错:
,FATAL,XX000,"using recovery command file ""recovery.conf"" is not supported