PostgreSQL 13 流复制配置

// os version
$ more /etc/redhat-release
CentOS Linux release 7.7.1908 (Core)

// hosts file
$ more /etc/hosts
127.0.0.1 localhost
11.203.18.37 pg13
$

2、数据库角色配置

master，IP:11.203.18.37，PGDATA: /pgdata , PORT:5432
standby，IP:11.203.18.37,PGDATA: /pgstandby, PORT: 5433

启动master

二、standby配置步骤

1.创建standby basebackup

[postgres@pg13 ~]$ pg_basebackup -h pg13 -U postgres -D /pgstandby/ --verbose --progress --checkpoint=fast --wal-method=stream
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_1412"
24294/24294 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/2000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
[postgres@pg13 ~]$ cd /pgstandby
[postgres@pg13 pgstandby]$ ll
total 200
-rw------- 1 postgres postgres 224 Sep 30 18:55 backup_label
-rw------- 1 postgres postgres 135702 Sep 30 18:55 backup_manifest
drwx------ 5 postgres postgres 41 Sep 30 18:55 base
-rw------- 1 postgres postgres 30 Sep 30 18:55 current_logfiles
drwx------ 2 postgres postgres 4096 Sep 30 18:55 global
drwx------ 2 postgres postgres 58 Sep 30 18:55 log
drwx------ 2 postgres postgres 6 Sep 30 18:55 pg_commit_ts
drwx------ 2 postgres postgres 6 Sep 30 18:55 pg_dynshmem
-rw------- 1 postgres postgres 4826 Sep 30 18:55 pg_hba.conf
-rw------- 1 postgres postgres 1636 Sep 30 18:55 pg_ident.conf
drwx------ 4 postgres postgres 68 Sep 30 18:55 pg_logical
drwx------ 4 postgres postgres 36 Sep 30 18:55 pg_multixact
drwx------ 2 postgres postgres 6 Sep 30 18:55 pg_notify
drwx------ 2 postgres postgres 6 Sep 30 18:55 pg_replslot
drwx------ 2 postgres postgres 6 Sep 30 18:55 pg_serial
drwx------ 2 postgres postgres 6 Sep 30 18:55 pg_snapshots
drwx------ 2 postgres postgres 6 Sep 30 18:55 pg_stat
drwx------ 2 postgres postgres 6 Sep 30 18:55 pg_stat_tmp
drwx------ 2 postgres postgres 6 Sep 30 18:55 pg_subtrans
drwx------ 2 postgres postgres 6 Sep 30 18:55 pg_tblspc
drwx------ 2 postgres postgres 6 Sep 30 18:55 pg_twophase
-rw------- 1 postgres postgres 3 Sep 30 18:55 PG_VERSION
drwx------ 3 postgres postgres 60 Sep 30 18:55 pg_wal
drwx------ 2 postgres postgres 18 Sep 30 18:55 pg_xact
-rw------- 1 postgres postgres 315 Sep 30 18:55 postgresql.auto.conf
-rw------- 1 postgres postgres 27949 Sep 30 18:55 postgresql.conf
-rw-rw-r-- 1 postgres postgres 187 Sep 30 18:55 serverlog
[postgres@pg13 pgstandby]$

2.手工创建stream standby配置文件standby.signal

[postgres@pg13 pgstandby]$
[postgres@pg13 pgstandby]$ touch standby.signal
[postgres@pg13 pgstandby]$ ll
total 208
-rw------- 1 postgres postgres 224 Sep 30 19:25 backup_label.old
-rw------- 1 postgres postgres 135702 Sep 30 19:25 backup_manifest
drwx------ 5 postgres postgres 41 Sep 30 19:25 base
-rw------- 1 postgres postgres 30 Sep 30 19:29 current_logfiles
drwx------ 2 postgres postgres 4096 Sep 30 19:27 global
drwx------ 2 postgres postgres 58 Sep 30 19:25 log
drwx------ 2 postgres postgres 6 Sep 30 19:25 pg_commit_ts
drwx------ 2 postgres postgres 6 Sep 30 19:25 pg_dynshmem
-rw------- 1 postgres postgres 4826 Sep 30 19:25 pg_hba.conf
-rw------- 1 postgres postgres 1636 Sep 30 19:25 pg_ident.conf
drwx------ 4 postgres postgres 68 Sep 30 19:25 pg_logical
drwx------ 4 postgres postgres 36 Sep 30 19:25 pg_multixact
drwx------ 2 postgres postgres 6 Sep 30 19:25 pg_notify
drwx------ 2 postgres postgres 6 Sep 30 19:25 pg_replslot
drwx------ 2 postgres postgres 6 Sep 30 19:25 pg_serial
drwx------ 2 postgres postgres 6 Sep 30 19:25 pg_snapshots
drwx------ 2 postgres postgres 6 Sep 30 19:25 pg_stat
drwx------ 2 postgres postgres 6 Sep 30 19:25 pg_stat_tmp
drwx------ 2 postgres postgres 6 Sep 30 19:25 pg_subtrans
drwx------ 2 postgres postgres 6 Sep 30 19:25 pg_tblspc
drwx------ 2 postgres postgres 6 Sep 30 19:25 pg_twophase
-rw------- 1 postgres postgres 3 Sep 30 19:25 PG_VERSION
drwx------ 3 postgres postgres 92 Sep 30 19:27 pg_wal
drwx------ 2 postgres postgres 18 Sep 30 19:25 pg_xact
-rw------- 1 postgres postgres 88 Sep 30 19:25 postgresql.auto.conf
-rw------- 1 postgres postgres 28152 Sep 30 19:29 postgresql.conf
-rw------- 1 postgres postgres 47 Sep 30 19:27 postmaster.opts
-rw------- 1 postgres postgres 68 Sep 30 19:27 postmaster.pid
-rw-rw-r-- 1 postgres postgres 187 Sep 30 19:25 serverlog
-rw-rw-r-- 1 postgres postgres 0 Sep 30 19:25 standby.signal
[postgres@pg13 pgstandby]$

3、启动standby数据库服务器
[postgres@pg13 pgstandby]$ pg_ctl -D /pgstandby start

4、修改standby配置文件

[postgres@pg13 pgstandby]$ vi postgresql.conf

//# These settings are ignored on a master server.

primary_conninfo = ‘user=postgres password=postgres channel_binding=disable host=pg13 port=5432 sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any‘ # connection string to sending server

5、reload 配置文件
[postgres@pg13 pgstandby]$ psql -p 5433
postgres=# select pg_reload_conf();
pg_reload_conf
/----------------
t
(1 row)

postgres=# show primary_conninfo ;
primary_conninfo

user=postgres password=postgres channel_binding=disable host=pg13 port=5432 sslmode=disable sslcompression=0 ssl_min_protocol_version=
TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any
(1 row)

postgres=#

6、检查master复制信息

[postgres@pg13 pgstandby]$ psql -p 5432
postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid | 1704
usesysid | 10
usename | postgres
application_name | walreceiver
client_addr | 11.203.18.37
client_hostname |
client_port | 54360
backend_start | 2020-09-30 19:29:46.226048+08
backend_xmin | 490
state | streaming
sent_lsn | 0/5000148
write_lsn | 0/5000148
flush_lsn | 0/5000148
replay_lsn | 0/5000148
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2020-09-30 19:35:27.535835+08

postgres=#

注意事项：

1、standby running状态，可以手工调整postgresql.conf的primary_conninfo，然后reload，
2、master会根据standby的primary_conninfo信息自动断开或者连接 standby，通过pg_stat_replication检查主从状态。

PostgreSQL 13 流复制配置

原文：https://blog.51cto.com/heyiyi/2539557