本节简单介绍了PostgreSQL 12搭建流复制的过程。PG 12把recovery.conf废掉了，与PG 11略有不同，但大同小异。 **主库** 创建复制用户 ``` [pg12@localhost pg120db]$ psql -c "CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'test'" -d testdb Timing is on. Expanded display is used automatically. CREATE ROLE Time: 30.796 ms ``` 常规参数配置 ``` [pg12@localhost pg120db]$ grep 'listen' postgresql.conf listen_addresses = '*' # what IP address(es) to listen on; pg12@localhost pg120db]$ grep 'replication' pg_hba.conf # DATABASE can be "all", "sameuser", "samerole", "replication", a # keyword does not match "replication". Access to replication # "all", "sameuser", "samerole" or "replication" makes the name lose # Allow replication connections from localhost, by a user with the # replication privilege. local replication all trust host replication all 127.0.0.1/32 trust host replication all 192.168.0.0/16 md5 host replication all ::1/128 trust [pg12@localhost pg120db]$ ``` 重新加载配置参数 ``` [pg12@localhost pg120db]$ psql -c "select pg_reload_conf()" -d testdb Timing is on. Expanded display is used automatically. pg_reload_conf ---------------- t (1 row) Time: 454.580 ms ``` 确认该库为master主库 ``` [pg12@localhost pg120db]$ psql -c "select pg_is_in_recovery()" -d testdb Timing is on. Expanded display is used automatically. pg_is_in_recovery ------------------- f (1 row) Time: 23.530 ms [pg12@localhost pg120db]$ ``` **备库** 使用pg_basebackup执行主库备份 ``` [pg12@localhost ~]$ pg_basebackup -h 192.168.26.28 -U replicator -p 5432 -D $PGDATA -Fp -Xs -P -R Password: 426401/9113562 kB (4%), 0/1 tablespace ``` 其中-Fp表示以plain格式数据，-Xs表示以stream方式包含所需的WAL文件，-P表示显示进度，-R表示为replication写配置信息。 备份完成，使用-R选项，在data目录下自动生成standby.signal“信号”文件（可手工使用touch命令生成）以及更新了postgresql.auto.conf文件，postgresql.auto.conf中写入了主库的连接信息（可手工添加primary_conninfo信息）。 ``` [pg12@localhost ~]$ pg_basebackup -h 192.168.26.28 -U replicator -p 5432 -D $PGDATA -Fp -Xs -P -R Password: 9113571/9113571 kB (100%), 1/1 tablespace [pg12@localhost ~]$ [pg12@localhost ~]$ cd $PGDATA [pg12@localhost testdb]$ ls backup_label pg_commit_ts pg_log pg_replslot pg_stat_tmp PG_VERSION postgresql.conf base pg_dynshmem pg_logical pg_serial pg_subtrans pg_wal standby.signal current_logfiles pg_hba.conf pg_multixact pg_snapshots pg_tblspc pg_xact global pg_ident.conf pg_notify pg_stat pg_twophase postgresql.auto.conf [pg12@localhost testdb]$ ll standby.signal -rw-------. 1 pg12 pg12 0 Nov 12 16:35 standby.signal [pg12@localhost testdb]$ [pg12@localhost testdb]$ cat postgresql.auto.conf # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. primary_conninfo = 'user=replicator password=test host=192.168.26.28 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any' [pg12@localhost testdb]$ [pg12@localhost testdb]$ grep 'primary_conninfo' postgresql.* postgresql.auto.conf:primary_conninfo = 'user=replicator password=test host=192.168.26.28 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any' postgresql.conf:#primary_conninfo = '' # connection string to sending server [pg12@localhost testdb]$ ``` 在PG 11中，需要创建recovery.conf文件，在此文件中配置standby_mode和primary_conninfo参数，PG 12已不再需要该文件，改为standby.signal文件以及通过配置参数直接设置。 备库启动数据库，通过pg_is_in_recovery确认是否正常配置 ``` [pg12@localhost testdb]$ pg_ctl -D $PGDATA start waiting for server to start....2019-11-12 16:46:31.635 CST [20436] LOG: starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit 2019-11-12 16:46:31.636 CST [20436] LOG: listening on IPv4 address "0.0.0.0", port 5432 2019-11-12 16:46:31.636 CST [20436] LOG: listening on IPv6 address "::", port 5432 2019-11-12 16:46:31.638 CST [20436] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2019-11-12 16:46:31.750 CST [20436] LOG: redirecting log output to logging collector process 2019-11-12 16:46:31.750 CST [20436] HINT: Future log output will appear in directory "pg_log". done server started [pg12@localhost testdb]$ psql -c "select pg_is_in_recovery()" -d testdb pg_is_in_recovery ------------------- t (1 row) [pg12@localhost testdb]$ ``` **完成搭建** 通过pg_stat_replication可查询复制状态（异步复制） ``` [pg12@localhost pg120db]$ psql -x -c "select * from pg_stat_replication" -d testdb Timing is on. Expanded display is used automatically. -[ RECORD 1 ]----+------------------------------ pid | 4503 usesysid | 155959 usename | replicator application_name | walreceiver client_addr | 192.168.26.25 client_hostname | client_port | 35172 backend_start | 2019-11-12 16:46:31.000236+08 backend_xmin | state | streaming sent_lsn | 6/A3000148 write_lsn | 6/A3000148 flush_lsn | 6/A3000148 replay_lsn | 6/A3000148 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async reply_time | 2019-11-12 16:48:32.509887+08 Time: 149.682 ms [pg12@localhost pg120db]$ ``` 下一节将介绍如何使用HAproxy配置负载均衡。 **参考资料** [How to Set Up Streaming Replication in PostgreSQL 12](https://www.percona.com/blog/2019/10/11/how-to-set-up-streaming-replication-in-postgresql-12/)