服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - PostgreSQL - PostgreSQl 12主从流复制及归档配置

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

2022-01-10 23:44运维开发故事小姜 PostgreSQL

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

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服务器是从节点,在接收异步流复制

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

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

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

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

延伸 · 阅读

精彩推荐
  • PostgreSQL分布式 PostgreSQL之Citus 架构

    分布式 PostgreSQL之Citus 架构

    节点 Citus 是一种 PostgreSQL 扩展,它允许数据库服务器(称为节点)在“无共享(shared nothing)”架构中相互协调。这些节点形成一个集群,允许 PostgreSQL 保存比单...

    未知802023-05-07
  • PostgreSQLPostgresql查询效率计算初探

    Postgresql查询效率计算初探

    这篇文章主要给大家介绍了关于Postgresql查询效率计算的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Postgresql具有一定的参考学习价...

    轨迹4622020-05-03
  • PostgreSQLRDS PostgreSQL一键大版本升级技术解密

    RDS PostgreSQL一键大版本升级技术解密

    一、PostgreSQL行业位置 (一)行业位置 在讨论PostgreSQL(下面简称为PG)在整个数据库行业的位置之前,我们先看一下阿里云数据库在全球的数据库行业里的...

    未知1192023-05-07
  • PostgreSQLpostgresql 中的to_char()常用操作

    postgresql 中的to_char()常用操作

    这篇文章主要介绍了postgresql 中的to_char()常用操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...

    J符离13432021-04-12
  • PostgreSQLPostgresql开启远程访问的步骤全纪录

    Postgresql开启远程访问的步骤全纪录

    postgre一般默认为本地连接,不支持远程访问,所以如果要开启远程访问,需要更改安装文件的配置。下面这篇文章主要给大家介绍了关于Postgresql开启远程...

    我勒个去6812020-04-30
  • PostgreSQL深入理解PostgreSQL的MVCC并发处理方式

    深入理解PostgreSQL的MVCC并发处理方式

    这篇文章主要介绍了深入理解PostgreSQL的MVCC并发处理方式,文中同时介绍了MVCC的缺点,需要的朋友可以参考下 ...

    PostgreSQL教程网3622020-04-25
  • PostgreSQLPostgreSQL标准建表语句分享

    PostgreSQL标准建表语句分享

    这篇文章主要介绍了PostgreSQL标准建表语句分享,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...

    码上得天下7962021-02-27
  • PostgreSQLpostgresql 数据库中的数据转换

    postgresql 数据库中的数据转换

    postgres8.3以后,字段数据之间的默认转换取消了。如果需要进行数据变换的话,在postgresql数据库中,我们可以用"::"来进行字段数据的类型转换。...

    postgresql教程网12482021-10-08