PostgreSQL12的流复制配置不再放到recovery.conf文件中,但是基本配置还是一样的,过年了也没心情工作,就来搭一下试试。
官方文档:
https://www.postgresql.org/docs/12/runtime-config-replication.html
开始:
1)下载安装包:
https://www.postgresql.org/docs/12/runtime-config-replication.html
2)解压并安装
tar xzvf postgresql-12.1.tar.gz cd postgresql-12.1/ ./configure --prefix=/opt/pg12 --without-zlib su root -c 'chown -R postgres:postgres /opt/pg12' make && make install
3)创建目录和环境变量,准备就在本机上创建两个data目录进行试验:data1为主 data2为备
cd /opt/pg12 mkdir data1 mkdir data2 vim ~/pg12.env source ~/pg12.env [postgres@localhost data1]$ cat ~/pg12.env export PGHOME=/opt/pg12/ export PATH=$PGHOME/bin:$PATH export PGDATA=$PGHOME/data1 export PGPORT=54121
4)初始化数据库
cd data1 vim postgresql.conf 修改: port = 54121 wal_level = replica synchronous_commit = on max_wal_senders = 10 wal_keep_segments = 1024 synchronous_standby_names = 'standby_node' hot_standby = on hot_standby_feedback = on logging_collector = on 启动数据库: pg_ctl start cd ../data2 pg_basebackup -R -X stream -Fp -D ./ -h localhost -p 54121 vim postgresql.conf 修改: recovery_target_timeline = 'latest' primary_conninfo = 'application_name=standby_node host=localhost port=54121 user=postgres password=postgres' promote_trigger_file = '/opt/pg12/data2/promote_trigger_file' port=54122 启动备数据库: pg_ctl -D ./ start
5)查看流复制情况,发现是异步流复制,application_name没有生效:
postgres=# select * from pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 19728 usesysid | 10 usename | postgres application_name | walreceiver client_addr | ::1 client_hostname | client_port | 37651 backend_start | 2020-01-21 19:55:14.881115-08 backend_xmin | 488 state | streaming sent_lsn | 0/30175C0 write_lsn | 0/30175C0 flush_lsn | 0/30175C0 replay_lsn | 0/30175C0 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async reply_time | 2020-01-21 20:01:38.445309-08
进一步排查,发现是在postgresql.auto.conf中有自动生成的primary_conninfo配置,里面没有application_name配置,而postgresql.auto.conf文件的优先级高于postgresql.conf文件。在里面添加节点名称:
[postgres@localhost data2]$ cat postgresql.auto.conf # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. primary_conninfo = 'application_name=standby_node user=postgres passfile=''/home/postgres/.pgpass'' host=localhost port=54121 sslmode=disable sslcompression=0 gssencmode=disable target_session_attrs=any'
6)在从节点上reload是不会生效的,必须重启从节点:
pg_ctl -D ./ restart
7)在主节点查看流复制,同步生效:
postgres=# select * from pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 20151 usesysid | 10 usename | postgres application_name | standby_node client_addr | ::1 client_hostname | client_port | 37657 backend_start | 2020-01-21 20:03:52.765047-08 backend_xmin | 488 state | streaming sent_lsn | 0/3017670 write_lsn | 0/3017670 flush_lsn | 0/3017670 replay_lsn | 0/3017670 write_lag | flush_lag | replay_lag | sync_priority | 1 sync_state | sync reply_time | 2020-01-21 20:04:02.884156-08