PostgreSQL12同步流复制搭建-同步不生效的问题

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

  

 

 

上一篇:Failover/Reinstate within Dataguard configuration fails with ORA-16653 /ORA-16795 (Doc ID 1161094.1)


下一篇:HadoopHa介绍及执行流程