PostgreSQL 12搭建流复制的过程是什么

本篇内容主要讲解"PostgreSQL 12搭建流复制的过程是什么"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"PostgreSQL 12搭建流复制的过程是什么"吧!

主库
创建复制用户

[pg12@localhost pg120db]$ psql -c "CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'test'" -d testdbTiming is on.Expanded display is used automatically.CREATE ROLETime: 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                                     trusthost    replication     all             127.0.0.1/32            trusthost    replication     all             192.168.0.0/16          md5host    replication     all             ::1/128                 trust[pg12@localhost pg120db]$

重新加载配置参数

[pg12@localhost pg120db]$ psql -c "select pg_reload_conf()" -d testdbTiming 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 testdbTiming 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 -RPassword:  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 -RPassword: 9113571/9113571 kB (100%), 1/1 tablespace[pg12@localhost ~]$ [pg12@localhost ~]$ cd $PGDATA[pg12@localhost testdb]$ lsbackup_label      pg_commit_ts   pg_log        pg_replslot   pg_stat_tmp  PG_VERSION            postgresql.confbase              pg_dynshmem    pg_logical    pg_serial     pg_subtrans  pg_wal                standby.signalcurrent_logfiles  pg_hba.conf    pg_multixact  pg_snapshots  pg_tblspc    pg_xactglobal            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 startwaiting 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-bit2019-11-12 16:46:31.636 CST [20436] LOG:  listening on IPv4 address "0.0.0.0", port 54322019-11-12 16:46:31.636 CST [20436] LOG:  listening on IPv6 address "::", port 54322019-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 process2019-11-12 16:46:31.750 CST [20436] HINT:  Future log output will appear in directory "pg_log". doneserver 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 testdbTiming is on.Expanded display is used automatically.-[ RECORD 1 ]----+------------------------------pid              | 4503usesysid         | 155959usename          | replicatorapplication_name | walreceiverclient_addr      | 192.168.26.25client_hostname  | client_port      | 35172backend_start    | 2019-11-12 16:46:31.000236+08backend_xmin     | state            | streamingsent_lsn         | 6/A3000148write_lsn        | 6/A3000148flush_lsn        | 6/A3000148replay_lsn       | 6/A3000148write_lag        | flush_lag        | replay_lag       | sync_priority    | 0sync_state       | asyncreply_time       | 2019-11-12 16:48:32.509887+08Time: 149.682 ms[pg12@localhost pg120db]$

到此，相信大家对"PostgreSQL 12搭建流复制的过程是什么"有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！