大家好，欢迎来到IT知识分享网。

1 背景介绍

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

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

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

1. walreceiver启动后通过postgresql.auto.conf文件中的primary_conninfo参数信息连向主库，主库通过连接参数replication=true启动walsender进程。
2. walreceiver执行identify_system命令，获取主库systemid/timeline/xlogpos等信息，执行TIMELINE_HISTORY命令拉取history文件。
3. 执行wal_startstreaming开始启动流复制，通过walrcv_receive获取WAL日志，期间也会回应主库发过来的心跳信息（接收位点、flush位点、apply位点），向主库发送feedback信息（最老的事务id），避免vacuum删掉备库正在使用的记录。
4. 执行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 processes
wal_keep_segments = 4096		# in logfile segments; 0 disables
superuser_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-2
wal_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'

IT知识分享网

2.2 启动主库创建复制用户

IT知识分享网pg_ctl start 
psql
create role repl login encrypted password 'REpliCa12343231_-1!' replication;

2.3 配置pg_hba文件

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

2.4 使配置生效

IT知识分享网pg_ctl reload 

3 从库执行

mkdir /archive 
chown R postgres.postgres /archive
rm -rf $PGDATA/*
-bash-4.2$ pg_basebackup -D /pgdata/pg_backup/ -Ft -Pv -U repl -h 10.55.2.152 -p 1921 -R
Password: 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
WARNING:  skipping special file "./.s.PGSQL.1921"
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 3/D8000060 on timeline 1
pg_basebackup: starting background WAL receiver
pg_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/D8000138
pg_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                   | 16112
status                | streaming
receive_start_lsn     | 3/D8000000
receive_start_tli     | 1
received_lsn          | 3/DA000060
received_tli          | 1
last_msg_send_time    | 2021-07-16 15:07:07.891213+08
last_msg_receipt_time | 2021-07-16 15:07:07.940033+08
latest_end_lsn        | 3/DA000060
latest_end_time       | 2021-07-16 15:07:07.421746+08
slot_name             | 
sender_host           | 10.55.2.152
sender_port           | 1921
conninfo              | 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的流复制就搭建成功了!