pg流复制实施步骤.docx

1、pg流复制实施步骤PostgreSQL12.2流复制配置手册一 主备机器规划主机名 角色 主机名 ipMaswer： Pg1 192.168.18.211 Slave： Pg2 192.168.18.212PG版本：用源码编译安装的12.2版本二 创建流复制2.1 设置hostmaster,slave两节点都要操作。# vim /etc/hosts#编辑内容如下：192.168.18.211 pg1192.168.18.212 pg22.2 在主库设置 2.2.1先初始化新数据库 $ initdb -D /usr/local/pg12.1/data U postgres 2.2.2启动数据库并

2、建立同步用户 $pg_ctl -D /usr/local/ pg12.1/data start 创建用户： create role 同步用的用户名 login replication encrypted password 密码;postgres=# create role repl login replication encrypted password oracle; CREATE ROLE postgres=#q -退出psql2.2.3配置$PGDATA/data/pg_hba.conf，添加下面内容：格式：host replication 同步用的用户名 备库IP地址或域名/24 tr

3、ust host replication repl pg2 trusthost replication repl 192.168.18.0/24 trusthost all all 192.168.18.0/24 trust2.2.4配置主备库的postgres.con文件，因为以后要进行角色切换，所有现在都添加一样的参数。主库配置/data/postgres.conf 查找并修改成以下内容 listen_addresses = *wal_level = replica -10以后的版本为replica 主从设置为在线模式，流复制必选max_wal_senders=10 -流复制允许连接进程，

4、主备库这个参数值必须一样wal_keep_segments =64archive_mode = on - 设置归档模式archive_command = cp %p /home/postgres/arch/%f -设置归档cp命令listen_addresses = *wal_level = replicamax_wal_senders=20wal_keep_segments =64archive_mode = onarchive_command = cp %p /home/postgres/arch/%frestore_command = cp /home/postgres/arch/%f

5、%precovery_target_timeline = latestfull_page_writes = onwal_log_hints = on2.2.5重启主库服务，以更新配置 $pg_ctl -D /data/ -l /log/pglog.log restart2.3 在备库设置 2.3.1不需要初始化，直接从主库备份就行，如有DATA直接删掉或改名掉:$ pg_basebackup -h pg1 -p 1922 -U repl -R -F p -P -D $PG_HOME/data备注： -h，主库主机，-p，主库服务端口； -U，复制用户； -F，p是默认输出格式，输出数据目录和表

6、空间相同的布局，t表示tar格式输出； -P，同-progress，显示进度； -D，输出到指定目录； -R 创建一个recovery.conf文件，10版本后就没有该文件，改为standby.signal文件，需要自己创建，所以该参数可以省略如果报错：pg_basebackup: error: could not connect to server: could not connect to server: No route to host Is the server running on host pg1 (192.168.18.211) and accepting TCP/IP conn

7、ections on port 1922?解决方法：发现是系统防火墙的问题：# 查看防火墙状态service iptables status # 停止防火墙service iptables stop # 永久关闭防火墙chkconfig iptables off 2.3.2备库修改配置文件 postgresNanoPI-006:$vi /data/postgresql.conf 添加以下内容 listen_addresses = *wal_level = replicamax_wal_senders=20wal_keep_segments =64archive_mode = onarchive

8、_command = cp %p /home/postgres/arch/%frestore_command = cp /home/postgres/arch/%f %precovery_target_timeline = latestfull_page_writes = onwal_log_hints = onhot_standby = on #在备份的同时允许查询，默认值 max_standby_streaming_delay = 30s #可选，流复制最大延迟 wal_receiver_status_interval = 10s #可选，从向主报告状态的最大间隔时间 hot_standb

9、y_feedback = on #可选，查询冲突时向主反馈 2.3.3配置/data/pg_hba.conf 添加下面内容 host replication repl pg1 trusthost replication repl 192.168.18.0/24 trusthost all all 192.168.18.0/24 trust#在备库中维护的主库IP地址是为了以后切换使用2.3.4、创建备库文件standby.signal primary_conninfo = host=pg1 port=1922 user=repl password=oracle options=-c wal_s

10、ender_timeout=5000restore_command = cp /home/postgres/arch/%f %parchive_cleanup_command = pg_archivecleanup /home/postgres/arch %rstandby_mode = on第一行参数：#连接到主库信息第二行参数：将来变成主库时需要用到的参数。第三行参数：变成主库后需要清空的归档日志。第四行参数：把备库变成read-only transaction模式，不允许进行写操作。允许查询。这一点非常好。2.3.5启动备库数据服务 $pg_ctl -D $PGDATA -l /log/

11、pglog.log start增加日志输出设置，从而获得更多的信息。三、验证3.1、观察主从两库的归档日志的位置，或者主库两边的pg_wal目录下的内容，发现主库日志切换后，备库pg_wal目录下就会产生新的日志文件，但是在备库的归档目录下没有内容，应该是主库的归档日志传递到备库的pg_wal目录下了。3.2、主库修改后，日志没有归档，但是备库已经同步了，类似于oracle同步时用lgwr方式进行写standby_logfile进行同步。3.3、查看当前备库状态：testdb=# select pg_is_in_recovery(); pg_is_in_recovery - tt ：true，

12、意味着处于recovery状态f ：false，意味着处于正常服务状态主库查询：testdb=# xtestdb=# select * from pg_stat_replication;- RECORD 1 -+-pid | 3711usesysid | 16384usename | replapplication_name | walreceiverclient_addr | 192.168.18.212client_hostname | pg2client_port | 49206backend_start | 2020-03-03 22:08:47.924435-05backend_xm

13、in | state | streamingsent_lsn | 0/210000D8write_lsn | 0/210000D8flush_lsn | 0/210000D8replay_lsn | 0/210000D8write_lag | flush_lag | replay_lag | sync_priority | 0sync_state | asyncreply_time | 2020-03-03 22:13:02.990258-05#application_name 很重要，以后同步复制需要用到。3.4、备库数据库日志内容：cp: cannot stat /home/postgre

14、s/arch/00000002.history: No such file or directorycp: cannot stat /home/postgres/arch/000000010000000000000009: No such file or directory2020-02-29 04:48:45.734 EST 4938 FATAL: could not connect to the primary server: could not connect to server: Connection refused Is the server running on host pg1

15、(192.168.18.211) and accepting TCP/IP connections on port 1922?cp: cannot stat /home/postgres/arch/00000002.history: No such file or directorycp: cannot stat /home/postgres/arch/000000010000000000000009: No such file or directory2020-02-29 04:48:50.747 EST 4941 LOG: started streaming WAL from primar

16、y at 0/9000000 on timeline 1 如果主库关闭，备库数据库日志内容：cp: cannot stat /home/postgres/arch/00000002.history: No such file or directorycp: cannot stat /home/postgres/arch/00000001000000000000000C: No such file or directory2020-02-29 05:22:55.757 EST 5048 FATAL: could not connect to the primary server: could n

17、ot connect to server: Connection refused Is the server running on host pg1 (192.168.18.211) and accepting TCP/IP connections on port 1922?cp: cannot stat /home/postgres/arch/00000002.history: No such file or directory 3.5、主库后台进程：ps -ef|grep walpostgres 3753 3749 0 21:21 ? 00:00:00 postgres: walwrite

18、r postgres 3844 3749 0 21:49 ? 00:00:00 postgres: walsender repl 192.168.18.212(33595) streaming 0/8000148 3.6、备库后台进程，一个进程负责接收，一个负责recovery：ps -ef|grep postgrespostgres 3472 3471 0 21:49 ? 00:00:00 postgres: startup recovering 000000010000000000000008postgres 3475 3471 0 21:49 ? 00:00:00 postgres: c

19、heckpointer postgres 3476 3471 0 21:49 ? 00:00:00 postgres: background writer postgres 3478 3471 0 21:49 ? 00:00:00 postgres: stats collector postgres 3479 3471 0 21:49 ? 00:00:00 postgres: walreceiver streaming 0/8000148四、主从切换 4.1、停掉主库 pg_ctl stop -m fast4.2、执行以下命令进行主从切换，把备库改成主库，执行之后发现standby.signa

20、l被删除了：pg_ctl promote查看最新状态：pg_controldata | grep cluster Database cluster state: in production4.3、这一步非常关键，注意原来的备库的postgresql.auto.conf文件中会自动添加一行primary_conninfo的信息，要把这一行给注释掉，否则虽然现在是主库了，但是配置还是当作备库，自相矛盾，且在跟踪日志中会报“background worker logical replication launcher (PID 6304) exited with exit code 1”错误。这可能是

21、PG12.2的bug。postgresql.auto.conf文件内容如下，注意下面内容只是一行数据，/home/postgres/.pgpass其实没有没有这个文件，不需要创建：primary_conninfo = user=repl passfile=/home/postgres/.pgpass host=pg1 port=1922 sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any 重启数据库，查看后台进程，实验发现walsender进程要等备库正

22、常启动后才会启动，备库关闭时该进程也自动中断：ps -ef|grep postgres |grep -v sshd |grep -v bashpostgres 3215 3164 0 Feb29 pts/3 00:00:00 tail -f pg_logpostgres 6329 1 0 07:08 ? 00:00:00 /usr/local/pg12.2/bin/postgrespostgres 6331 6329 0 07:08 ? 00:00:00 postgres: checkpointer postgres 6332 6329 0 07:08 ? 00:00:00 postgres:

23、 background writer postgres 6333 6329 0 07:08 ? 00:00:00 postgres: walwriter postgres 6334 6329 0 07:08 ? 00:00:00 postgres: autovacuum launcher postgres 6335 6329 0 07:08 ? 00:00:00 postgres: archiver postgres 6336 6329 0 07:08 ? 00:00:00 postgres: stats collector postgres 6337 6329 0 07:08 ? 00:00

24、:00 postgres: logical replication launcher postgres 6353 6329 0 07:12 ? 00:00:00 postgres: walsender repl 192.168.18.212(33609) streaming 0/1A01C7F84.4、在新备库上创建一个standby.signal文件，添加如下内容：primary_conninfo = host=pg1 port=1922 user=repl password=oracle options=-c wal_sender_timeout=5000restore_command =

25、 cp /home/postgres/arch/%f %parchive_cleanup_command = pg_archivecleanup /home/postgres/arch %rstandby_mode = on 4.5、在新备库的postgresql.auto.conf文件中添加如下内容，这一步非常关键，第一次搭建备库的时候会自动添加，但是切换后却不能：primary_conninfo = user=repl passfile=/home/postgres/.pgpass host=pg2 port=1922 sslmode=disable sslcompression=0 gs

26、sencmode=disable krbsrvname=postgres target_session_attrs=any 注意/home/postgres/.pgpass其实没有没有这个文件，不需要创建。 4.6、启动新备库： pg_ctl start -l pg_log 4.7、查看后台进程： ps -ef|grep postgre |grep -v ssh |grep -v bashpostgres 3274 3237 0 Feb29 pts/3 00:00:00 tail -f pg_logpostgres 6441 1 0 07:12 ? 00:00:00 /usr/local/pg

27、12.2/bin/postgrespostgres 6442 6441 0 07:12 ? 00:00:00 postgres: startup recovering 00000003000000000000001Apostgres 6447 6441 0 07:12 ? 00:00:00 postgres: checkpointer postgres 6448 6441 0 07:12 ? 00:00:00 postgres: background writer postgres 6450 6441 0 07:12 ? 00:00:00 postgres: stats collector p

28、ostgres 6451 6441 0 07:12 ? 00:00:05 postgres: walreceiver streaming 0/1A01C7F8 4.8、验证主备库是否能够同步 在主库进行dml操作，发现备库能够正常同步，切换成功。 4.9、主库变成备库时，有时候启动会失败，日志显示找不到xxx.history日志文件，解决办法，在postgresql.conf中指定明确的timeline，把原来的latest替换掉： recovery_target_timeline = 3 总结： 经过实验，发现主备切换不太灵活和智能，需要后续进行手动修改，特别是postgresql.auto

29、.conf文件中自动添加的一行，在主备切换的时候不会自动删除，没有相关文档，造成了隐性的问题，给DBA造成了很大的麻烦，不容易故障排除。 主库在正常运行中，备库可以随意切换为主库，没有一个制约机制，感觉不严谨，此时变成两个主库，数据无法同步。如果此时两边的数据库都各自发生变化，将来想把一台主库当作备库，则需要在备库上对当前的数据进行同步，然后就可以变成备库，用以下的命令进行同步： pg_rewind -target-pgdata $PGDATA -source-server=host=192.168.18.211 port=1922 user=postgres dbname=testdbpg_

30、rewind: servers diverged at WAL location 0/1C01F280 on timeline 5pg_rewind: rewinding from last common checkpoint at 0/1C01F1D0 on timeline 5pg_rewind: Done!五、pg_rewind工具如果备库是意外崩溃，如果新的主库修改了数据，经过的时间很长，归档日志又删除了，无法同步，原来的数据库如果想变成备库，需要对数据库做一次同步，那么就可以用到pg_rewind工具进行同步。pg_rewind使一个PostgreSQL数据目录与另一个数据目录（该目

31、录从第一个PostgreSQL数据目录创建而来）一致。描述pg_rewind是一个在集群的时间线参数偏离之后，用于使一个PostgreSQL集群与另一个相同集群的拷贝同步的工具。一个典型的场景是在故障转移之后，让一个老的主服务器重新在线作为一个standby跟随新主服务器。其结果相当于使用源数据目录替换目标数据目录。所有的文件都被拷贝，包括配置文件。与做一个基础备份或者像rsync这样的工具相比，pg_rewind的优势是pg_rewind不需要读取所有集群中没有更改的文件。当数据库很大，并且只有一小部分不同的集群之间，使它的速度快得多。pg_rewind检查源集群与目标集群的时间线历史来检测它们产生分歧的点，并希望在目标集群的pg_xlog目录找到WAL回到分歧点的所有方式。在典型的故