PostgreSQL流复制配置

主库配置

编辑$PGDATA/postgresql.conf文件
  1. log_timezone = 'PRC'
  2. datestyle = 'iso, mdy'
  3. timezone = 'PRC'
  4. default_text_search_config = 'pg_catalog.english'
  5. listen_addresses = '*'
  6. port = 5432
  7. wal_level = hot_standby
  8. hot_standby = on
  9. archive_mode = on
  10. archive_command = 'cp "%p" "/data/pgsql/archivedir/%f"'
  11. max_wal_senders = 5
  12. wal_keep_segments = 1280
  13. wal_sender_timeout = 60s
  14. max_connections = 2500
  15. max_prepared_transactions = 2500
  16. wal_log_hints = on
  17. max_parallel_maintenance_workers = 16
  18. maintenance_work_mem = 3GB
  19. max_worker_processes = 64
  20. max_parallel_workers_per_gather = 24
  21. max_parallel_workers = 36
  22. effective_cache_size = 64GB
  23. huge_pages = try 
  24. shared_buffers = 64GB
  25. #idle_in_transaction_session_timeout = 180000
  26. wal_buffers = 16MB
  27. shared_preload_libraries = 'pg_stat_statements'
  28. pg_stat_statements.max = 10000
  29. pg_stat_statements.track = all
  30. checkpoint_timeout = 30min
  31. max_sync_workers_per_subscription = 32
  32. max_wal_size = 64GB
  33. min_wal_size = 4GB
  34. wal_compression = on
  35. tcp_keepalives_idle = 600
  36. tcp_keepalives_interval = 10
  37. tcp_keepalives_count = 6
  38. autovacuum = on
  39. autovacuum_max_workers = 8 
  40. autovacuum_work_mem = 4GB
  41. log_autovacuum_min_duration = 0
  42. autovacuum_vacuum_scale_factor = 0.02
  43. autovacuum_analyze_scale_factor = 0.01
  44. autovacuum_freeze_max_age = 1200000000
  45. autovacuum_multixact_freeze_max_age = 1250000000
  46. autovacuum_vacuum_cost_delay = 0ms
  47. vacuum_freeze_table_age = 200000000
  48. vacuum_multixact_freeze_table_age = 200000000
  49. min_parallel_table_scan_size =0 
  50. min_parallel_index_scan_size =0 
  51. parallel_tuple_cost =0  
  52. parallel_setup_cost =0

配置pg_hba.conf文件

因为安全原因，使用replica用户用作复制

假设所有的pgpool2服务器和PostgreSql服务器都在200.100.128.0/23子网，编辑pg_hba.conf文件支持此用户的md5验证。

PostgreSql数据库中创建用户

  1. # 创建流复制用户
  2. create role replica login replication encrypted password '<replicaPass>';

修改pg_hba.conf文件

  1. #TYPE  DATABASE        USER            ADDRESS                 METHOD
  2. local   all             postgres                                peer
  3. local   all             all                                     peer
  4. host    all             all             127.0.0.1/32            md5
  5. host    all             all             15.16.0.0/24        md5 
  6. host    replication     all             127.0.0.1/32            md5
  7. host    replication     replica         15.16.22.14/24       md5
  8. host    replication     replica         15.16.22.15/24       md5

使用pgpool2的故障转移和在线恢复功能，需要所有pgpool2和PostgreSql服务器的postgres系统用户允许ssh免密登录。

  1. # 生成postgres用户的ssh免密互登录
  2. su postgres
  3. ssh-keygen
  4. 配置免密略
  6. # 流复制用户replica免密
  7. su - postgres
  8. cat > .pgpass <<EOF
  9. hhhtlt-pg1:5432:replication:replica:APzqI4B6d5h2k
  10. hhhtlt-pg2:5432:replication:replica:APzqI4B6d5h2k
  11. EOF

配置 recovery.done文件

  1. standby_mode = 'on'
  2. primary_conninfo = 'host=hhhtlt-pg2 port=5432 user=replica'
  3. restore_command = 'scp -P 22 hhhtlt-pg2:/data/pgsql/archivedir/%f %p'

重启

  1. su - postgres
  2. /usr/local/postgresql/11/bin/pg_ctl -D /data/pgsql/11/data -l /var/log/postgres/postgresql-11-main.log stop
  3. /usr/local/postgresql/11/bin/pg_ctl -D /data/pgsql/11/data -l /var/log/postgres/postgresql-11-main.log start

从库配置

配置完主节点后使用pg_basebackup将主库的配置同步到从库

  1. 注： 执行前要先把data目录清空
  2. pg_basebackup -X stream -c fast -P -D /data/pgsql/11/data -h hhhtlt-pg1 -p 5432 -U replica

修改recovery.done文件

  1. # mv recovery.done recovery.conf
  2. standby_mode = 'on'
  3. primary_conninfo = 'host=sxsz-spzw-pg1 port=5432 user=replica'
  4. restore_command = 'scp -P 22 sxsz-spzw-pg1:/data/pgsql/archivedir/%f %p'

启动

  1. su - postgres
  2. /usr/local/postgresql/11/bin/pg_ctl -D /data/pgsql/11/data -l /var/log/postgres/postgresql-11-main.log start

验证

  1. 主库执行
  2. postgres=# select * from pg_stat_replication;
  3. -[ RECORD 1 ]----+------------------------------
  4. pid              | 3174
  5. usesysid         | 16384
  6. usename          | replica
  7. application_name | walreceiver
  8. client_addr      | 15.16.22.15
  9. client_hostname  | 
  10. client_port      | 34444
  11. backend_start    | 2021-09-09 21:59:17.238057+08
  12. backend_xmin     | 
  13. state            | streaming
  14. sent_lsn         | 0/40000D0
  15. write_lsn        | 0/40000D0
  16. flush_lsn        | 0/40000D0
  17. replay_lsn       | 0/40000D0
  18. write_lag        | 
  19. flush_lag        | 
  20. replay_lag       | 
  21. sync_priority    | 0
  22. sync_state       | async