PostgreSQl 12主从流复制及归档配置

上一篇文章说道PostgreSQL 12 的源码部署，这里我们说一下PostgreSQl 12的主从流复制和归档配置。

主从复制的两种形式

1) 基于文件的日志传送

创建一个高可用性(HA)集群配置可采用连续归档，集群中主服务器工作在连续归档模式下，备服务器工作在连续恢复模式下(1台或多台可随时接管主服务器)，备持续从主服务器读取WAL文件。连续归档不需要对数据库表做任何改动，可有效降低管理开销，对主服务器的性能影响也相对较低。直接从一个数据库服务器移动WAL记录到另一台服务器被称为日志传送，PostgreSQL通过一次一文件(WAL段)的WAL记录传输实现了基于文件的日志传送。日志传送所需的带宽取根据主服务器的事务率而变化;日志传送是异步的，即WAL记录是在事务提交后才被传送，那么在一个窗口期内如果主服务器发生灾难性的失效则会导致数据丢失，还没有被传送的事务将会被丢失;数据丢失窗口可以通过使用参数archive_timeout进行限制，可以低至数秒，但同时会增加文件传送所需的带宽。archive_timeout强制N秒以后进行一次归档,若设置太小，很快就会超过wal_keep_segments 的值，导致数据覆盖丢失，因此不要盲目设置。

2)流复制

PostgreSQL在9.x之后引入了主从的流复制机制，所谓流复制，就是备服务器通过tcp流从主服务器中同步相应的数据，主服务器在WAL记录产生时即将它们以流式传送给备服务器，而不必等到WAL文件被填充。默认情况下流复制是异步的，这种情况下主服务器上提交一个事务与该变化在备服务器上变得可见之间客观上存在短暂的延迟，但这种延迟相比基于文件的日志传送方式依然要小得多，在备服务器的能力满足负载的前提下延迟通常低于一秒;在流复制中，备服务器比使用基于文件的日志传送具有更小的数据丢失窗口，不需要采用archive_timeout来缩减数据丢失窗口;PostgreSQL 12开始，在执行通过流复制来配置主备数据库的时候，不再需要配置额外配置recovery.conf文件了。取而代之的是在备库环境的$PGDATA路径下配置一个standby.signal文件，注意该文件是一个普通的文本文件，内容为空。理解起来就是，该文件是一个标识文件。如果备库通过执行pg_ctl promote提升为主库的话，那么该文件将自动消失。

注意：全部操作都以postgres用户进行。

配置主从流复制和归档

1)两台机器做免密登录

我们备份和还原过程中所用的archive_command和restore_command命令都以postgres用户运行，因此我们需要针对postgres用户实现ssh无密码登录。

1. # 用postgres用户登录到主pgsql服务器 
2. ssh-keygen -t rsa  # 一路回车 
3. scp /home/postgres/.ssh/id_rsa.pub postgres@10.10.22.152:/home/postgres/.ssh/authorized_keys 
4.  
5. 或者拷贝id_rsa.pub文件到从pgsql上，然后到从上执行以下命令 
6.  
7. cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys  
8.  
9.  
10. # 登录从pgsql服务器查看权限 
11. chmod 700 /home/postgres/.ssh 
12. chmod 600 /home/postgres/.ssh/authorized_keys 
13.  
14. # 测试登录 
15. ssh postgres@10.10.22.152 
16.  
17.  
18. # 从pgsql库 
19. ssh-keygen -t rsa  # 一路回车 
20. scp /home/postgres/.ssh/id_rsa.pub postgres@10.10.22.151:/home/postgres/.ssh/authorized_keys 
21.  
22. # 登录从pgsql服务器查看权限 
23. chmod 700 /home/postgres/.ssh 
24. chmod 600 /home/postgres/.ssh/authorized_keys 
25.  
26. # 测试登录 
27. ssh postgres@10.10.22.152 

2)主库配置

1. # 创建用户 
2. ceate user replica with replication login password 'replication'; 
3. alter user replica with password 'replication'; 
4.  
5. # 修改pg_hba.conf 
6. host  replication  replica 10.10.0.0/16  md5 
7.  
8.  
9. # 修改配置文件 
10. $ vim /data/postgresql-12/data/postgresql.conf 
11.  
12. # 监听所有IP 
13. listen_addresses = "0.0.0.0" 
14. # 最大连接数，据说从机需要大于或等于该值 
15. max_connections = 200 
16. # 设置主pgsql为生成wal的主机，9.6开始没有hot_standby（热备模式） 
17. wal_level = replica 
18.  
19. # 开启连续归档 
20. archive_mode = on 
21. #归档命令。-o "StrictHostKeyChecking no" 作用是取消第一次连接输入yes或者no 
22. archive_command = 'scp -o "StrictHostKeyChecking no" %p pgslave.ptcloud.t.home:/data/postgresql-12/archive/%f' 
23. # archive_command = 'test ! -f /data/postgresql-12/archive/%f && scp %p pgslave.ayunw.cn:/data/postgresql-12/archive/%f' 
24. archive_cleanup_command = '/usr/local/postgresql-12/bin/pg_archivecleanup -d /data/postgresql-12/data/pg_wal %r >> /data/postgresql-12/log/archive_cleanup.log 2>&1' 
25. # 最多有16个流复制连接。 
26. max_wal_senders = 16 
27. # 设置流服务保留的最多wal(老版本叫xlog)文件个数 
28. wal_keep_segments = 256 
29. # 数据堆清理的最大进程 
30. autovacuum_max_workers = 2 
31. max_worker_processes = 16 
32. max_logical_replication_workers = 10 
33. # 日志设置 
34. log_destination = 'stderr' 
35. logging_collector = on 
36. log_directory = '/data/postgresql-12/log' 
37. log_filename = 'postgresql-%w.log' 
38. log_file_mode = 0600 
39. log_truncate_on_rotation = on 
40. log_rotation_age = 1d 
41. log_rotation_size = 1GB 
42.  
43. log_min_messages = error 
44. # 执行超过300ms的sql语句会记录到pgsql的日志文件,类似于慢日志 
45. # 一般设置300ms就好，慢日志会打到pgsql日志文件，方便查问题 
46. log_min_duration_statement = 300 
47. log_checkpoints = on 
48. log_connections = on 
49. log_disconnections = on 
50. log_error_verbosity = verbose 
51. log_hostname = on 
52. log_line_prefix = '%m [%p] ' 
53. log_lock_waits = on  
54. log_statement = 'ddl' 
55.  
56. # 主库设置完成后，需要root用户重启PG服务才能使以上配置生效 
57. systemctl daemon-reload 
58. systemctl restart postgresql 
59.  
60.  
61. su - postgres 
62. psql 
63.  
64. # 主库创建具有流复制权限的用户replica 
65. CREATE user replica login replication encrypted password 'replication'; 
66.  
67. postgres=# \du; 
68.                                    List of roles 
69.  Role name |                         Attributes                         | Member of 
70. -----------+------------------------------------------------------------+----------- 
71.  postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {} 
72.  replica   | Replication                                                | {} 
73.  
74.  
75. # 主库增加主从复制的信任访问(pg_hba.conf) 
76.  
77. $ vim /data/postgresql-12/data/pg_hba.conf 
78. # replication privilege. 
79. host    replication     replica         10.10.0.0/16            trust 
80.  
81.  
82. # 重启服务 
83. systemctl daemon-reload 
84. systemctl restart postgresql 
85. systemctl status postgresql 

3)从库配置

1. # 停止从库，删除从pgsql数据目录中的数据 
2.  
3. # 备份数据目录 
4. mkdir -p /opt/pgsqldata_backup 
5. mv /data/postgresql-12/data/* /opt/pgsqldata_backup 
6.  
7. pg_ctl -D /data/postgresql-12/data -l logfile stop 
8. rm -rf /data/postgresql-12/data/* 

4)从库做基础备份

从主服务器上copy数据到从服务器，这一步叫做“基础备份”

1. su - postgres 
2.  
3. $ pg_basebackup -h 10.10.22.151 -p 5432 -U replica -W -R -Fp -Xs -Pv -D /data/postgresql-12/data/ 
4. Password: 
5. pg_basebackup: initiating base backup, waiting for checkpoint to complete 
6. pg_basebackup: checkpoint completed 
7. pg_basebackup: write-ahead log start point: 0/8000028 on timeline 1 
8. pg_basebackup: starting background WAL receiver 
9. pg_basebackup: created temporary replication slot "pg_basebackup_13370" 
10. 31384/31384 kB (100%), 1/1 tablespace 
11. pg_basebackup: write-ahead log end point: 0/8000100 
12. pg_basebackup: waiting for background process to finish streaming ... 
13. pg_basebackup: syncing data to disk ... 
14. pg_basebackup: base backup completed 

参数说明：

• -h 启动的主库数据库地址
• -p 主库数据库端口
• -U 流复制用户
• -W 使用密码验证，要用replica的密码
• -Fp 备份输出正常的数据库目录
• -Xs 使用流复制的方式进行复制
• -Pv 输出复制过程的详细信息
• -R 为备库创建recovery.conf文件。但是pgsql 10以后的新版本的pgsql不需要这个文件了。
• -D 指定创建的备库的数据库目录

5) 配置从库的配置文件

注意：这时候，从库数据目录下的postgresql.conf文件是刚才从主的pgsql上同步过来的，并不是pgsql的配置文件，你需要将原先老的从库上的配置文件拿过来用。

1. $ cd /data/postgresql-12/data/ 
2. $ mv postgres.conf  postgres.conf_master.bak 
3.  
4. $ cp /opt/pgsqldata_backup/postgres.conf postgres.conf 
5.  
6. $ vim /data/postgresql-12/data/postgres.conf 
7.  
8. # 监听所有IP 
9. listen_addresses = "0.0.0.0" 
10.  
11. # 最大连接数，从pgsql需要大于或等于主的值 
12. max_connections = 300 
13.  
14. restore_command = 'cp /data/postgresql-12/archive/%f %p' 
15. archive_cleanup_command = '/usr/local/postgresql-12/bin/pg_archivecleanup -d /data/postgresql-12/data/pg_wal %r && /usr/local/postgresql-12/bin/pg_archivecleanup -d /data/postgresql-12/archive %r >> /data/postgresql-12/log/archive_cleanup.log 2>&1' 
16.  
17. # 9.6开始没有hot_standby（热备模式） 
18. wal_level = replica 
19. # 最多有16个流复制连接。 
20. max_wal_senders = 16 
21.  
22. # 设置比主库大，可以设置为2倍的数值 
23. wal_keep_segments = 512 
24. max_logical_replication_workers = 10 
25.  
26. autovacuum_max_workers = 2 
27. # 和主的值保持一致即可 
28. max_worker_processes = 16 
29.  
30. # 说明这台机器不仅用于数据归档，还可以用于数据查询 
31. hot_standby = on 
32. #流备份的最大延迟时间 
33. max_standby_streaming_delay = 30s  
34. # 向主机汇报本机状态的间隔时间 
35. wal_receiver_status_interval = 10s  
36. # 出现错误复制，向主机反馈 
37. hot_standby_feedback = on 
38.  
39.  
40. # 日志设置 
41. log_destination = 'stderr' 
42. logging_collector = on 
43. log_directory = '/data/postgresql-12/log' 
44. log_filename = 'postgresql-%w.log' 
45. log_file_mode = 0600 
46. log_truncate_on_rotation = on 
47. log_rotation_age = 1d 
48. log_rotation_size = 1GB 
49.  
50. log_min_messages = error 
51. # 执行超过300ms的sql语句会被记录到pgsql的日志文件中 
52. log_min_duration_statement = 300 
53. log_checkpoints = on 
54. log_connections = on 
55. log_disconnections = on 
56. log_error_verbosity = verbose 
57. log_hostname = on 
58. log_line_prefix = '%m [%p] ' 
59. log_lock_waits = on  
60. log_statement = 'ddl' 

6) 重启从库

要保证从库的数据目录是postgres属主和属组，且权限为0700

1. su - postgres 
2. pg_ctl -D /data/postgresql-12/data restart 

验证pgsql主从

1. # 登录主库 
2. su - postgres 
3.  
4. postgres=# psql 
5.  
6. postgres=# select client_addr,sync_state from pg_stat_replication; 
7.  client_addr  | sync_state 
8. --------------+------------ 
9.  10.10.22.152 | async 
10. (1 row) 
11.  
12.  
13. select pid, usename, application_name, client_addr,  
14.       backend_start, client_port, state, sync_state from pg_stat_replication; 
15.  
16.   pid  | usename | application_name | client_addr |         backend_start         | client_port |   state   | sync_state 
17. -------+---------+------------------+-------------+-------------------------------+-------------+-----------+------------ 
18.  28356 | repl    | walreceiver      | 10.10.22.152 | 2021-12-30 17:00:59.357653+08 |       48660 | streaming | async 
19. (1 row) 

以上说明10.10.22.152服务器是从节点，在接收异步流复制

到这里，主流复制和归档配置完成。

本文转载自微信公众号「运维开发故事」

原文链接：https://mp.weixin.qq.com/s/JT4GCYX1ZuUWkQnXfdQ9HA