如何搭建PostgreSQL流复制系统？

发表时间: 2021-07-16 16:43

1 背景介绍

流复制是PostgreSQL 9.0之后才提供的新的传递WAL日志的方法。通过流复制，备库不断地从主库同步相应的数据，并在备库apply每个WAL record，这里的流复制每次传输单位是WAL日志的record。它的好处是只要主库一产生日志，就会马上传递到备库，同WAL日志文件相比有更低同步延迟。

同时PostgreSQL9.0之后提供了Hot Standby能力，备库在应用WAL record的同时也能够提供只读服务。

PostgreSQL流复制的核心部分由walsender，walreceiver和startup三个进程组成：

walreceiver启动后通过postgresql.auto.conf文件中的primary_conninfo参数信息连向主库，主库通过连接参数replication=true启动walsender进程。
walreceiver执行identify_system命令，获取主库systemid/timeline/xlogpos等信息，执行TIMELINE_HISTORY命令拉取history文件。
执行wal_startstreaming开始启动流复制，通过walrcv_receive获取WAL日志，期间也会回应主库发过来的心跳信息（接收位点、flush位点、apply位点），向主库发送feedback信息（最老的事务id），避免vacuum删掉备库正在使用的记录。
执行walrcv_endstreaming结束流复制，等待startup进程更新receiveStart和receiveStartTLI，一旦更新，重新进入2/3/4步骤。

在从库可以看到walreceiver和startup进程:

主库查看walsender进程:

2 搭建流复制

基础环境搭建参见PostgreSql数据库基于源码安装

2.1 主库配置文件postgresql.conf准备:

listen_addresses = '0.0.0.0'  port = 1921  # 监听端口  max_connections = 2000  # 最大允许的连接数 max_wal_senders = 10		# max number of walsender processeswal_keep_segments = 4096		# in logfile segments; 0 disablessuperuser_reserved_connections = 10  unix_socket_directories = '.'  unix_socket_permissions = 0700  tcp_keepalives_idle = 60  tcp_keepalives_interval = 60  tcp_keepalives_count = 10  shared_buffers = 2048MB          # 共享内存，建议设置为系统内存的1/4  .  maintenance_work_mem = 64MB     # 系统内存超过32G时，建议设置为1GB。超过64GB时，建议设置为2GB。超过128GB时，建议设置为4GB。  work_mem = 8MB                        # 1/4 主机内存 / 256 (假设256个并发同时使用work_mem)wal_buffers = 64MB                    # min( 2047MB, shared_buffers/32 ) dynamic_shared_memory_type = posix  vacuum_cost_delay = 0  bgwriter_delay = 10ms  bgwriter_lru_maxpages = 500  bgwriter_lru_multiplier = 5.0  effective_io_concurrency = 0  max_worker_processes = 128                 max_parallel_workers_per_gather = 2        # 建议设置为主机CPU核数的一半。  max_parallel_workers = 2                   # 看业务AP和TP的比例，以及AP TP时间交错分配。实际情况调整。例如 主机CPU cores-2wal_level = replica  fsync = on  synchronous_commit = off  full_page_writes = on                  # 支持原子写超过BLOCK_SIZE的块设备，在对齐后可以关闭。或者支持cow的文件系统可以关闭。wal_writer_delay = 10ms  wal_writer_flush_after = 1MB  checkpoint_timeout = 35min  max_wal_size = 4GB                    # shared_buffers*2 min_wal_size = 1GB                     # max_wal_size/4 archive_mode = on  archive_command = 'test ! -f /root/archive/%f && cp %p /root/archive/%f'  max_wal_senders = 10  max_replication_slots = 10  wal_receiver_status_interval = 1s  max_logical_replication_workers = 4  max_sync_workers_per_subscription = 2  random_page_cost = 1.2  parallel_tuple_cost = 0.1  parallel_setup_cost = 1000.0  min_parallel_table_scan_size = 8MB  min_parallel_index_scan_size = 512kB  effective_cache_size = 5GB                 # 建议设置为主机内存的5/8。     log_destination = 'csvlog'  logging_collector = on  log_directory = 'log'  log_filename = 'postgresql-%a.log'  log_truncate_on_rotation = on  log_rotation_age = 1d  log_rotation_size = 0  log_min_duration_statement = 5s  log_checkpoints = on  log_connections = on                            # 如果是短连接，并且不需要审计连接日志的话，建议OFF。log_disconnections = on                         # 如果是短连接，并且不需要审计连接日志的话，建议OFF。log_error_verbosity = verbose  log_line_prefix = '%m [%p] '  log_lock_waits = on  log_statement = 'ddl'  log_timezone = 'PRC'  log_autovacuum_min_duration = 0   autovacuum_max_workers = 5  autovacuum_vacuum_scale_factor = 0.1  autovacuum_analyze_scale_factor = 0.05  autovacuum_freeze_max_age = 1000000000  autovacuum_multixact_freeze_max_age = 1200000000  autovacuum_vacuum_cost_delay = 0  statement_timeout = 0                                # 单位ms, s, min, h, d.  表示语句的超时时间，0表示不限制。  lock_timeout = 0                                     # 单位ms, s, min, h, d.  表示锁等待的超时时间，0表示不限制。  idle_in_transaction_session_timeout = 2h             # 单位ms, s, min, h, d.  表示空闲事务的超时时间，0表示不限制。  vacuum_freeze_min_age = 50000000  vacuum_freeze_table_age = 800000000  vacuum_multixact_freeze_min_age = 50000000  vacuum_multixact_freeze_table_age = 800000000  datestyle = 'iso, ymd'  timezone = 'PRC'  lc_messages = 'en_US.UTF8'  lc_monetary = 'en_US.UTF8'  lc_numeric = 'en_US.UTF8'  lc_time = 'en_US.UTF8'  default_text_search_config = 'pg_catalog.simple'

2.2 启动主库创建复制用户

pg_ctl start psqlcreate role repl login encrypted password 'REpliCa12343231_-1!' replication;

2.3 配置pg_hba文件

host all all 0.0.0.0/0 md5host replication repl 0.0.0.0/0 md5

2.4 使配置生效

pg_ctl reload

3 从库执行

mkdir /archive chown R postgres.postgres /archiverm -rf $PGDATA/*-bash-4.2$ pg_basebackup -D /pgdata/pg_backup/ -Ft -Pv -U repl -h 10.55.2.152 -p 1921 -RPassword: pg_basebackup: initiating base backup, waiting for checkpoint to completeWARNING:  skipping special file "./.s.PGSQL.1921"pg_basebackup: checkpoint completedpg_basebackup: write-ahead log start point: 3/D8000060 on timeline 1pg_basebackup: starting background WAL receiverpg_basebackup: created temporary replication slot "pg_basebackup_27249"WARNING:  skipping special file "./.s.PGSQL.1921"backup//base.tar        )40492/40492 kB (100%), 1/1 tablespace                                         pg_basebackup: write-ahead log end point: 3/D8000138pg_basebackup: waiting for background process to finish streaming ...pg_basebackup: syncing data to disk ...pg_basebackup: base backup completed-bash-4.2$ cd /pgdata/pg_backup/-bash-4.2$ tar xf base.tar -C /pgdata/12/data/-bash-4.2$ tar xf pg_wal.tar -C /archive/-bash-4.2$ vim $/PGDATA/standby.signal写入:standby_mode = 'on'

3.2 启动从库

pg_ctl start查看状态:server started-bash-4.2$ psql -c "\x" -c "SELECT * FROM pg_stat_wal_receiver;"Expanded display is on.-[ RECORD 1 ]---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------pid                   | 16112status                | streamingreceive_start_lsn     | 3/D8000000receive_start_tli     | 1received_lsn          | 3/DA000060received_tli          | 1last_msg_send_time    | 2021-07-16 15:07:07.891213+08last_msg_receipt_time | 2021-07-16 15:07:07.940033+08latest_end_lsn        | 3/DA000060latest_end_time       | 2021-07-16 15:07:07.421746+08slot_name             | sender_host           | 10.55.2.152sender_port           | 1921conninfo              | user=repl password=******** dbname=replication host=10.55.2.152 port=1921 fallback_application_name=walreceiver sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any

至此.一个PG的流复制就搭建成功了!