postgresql12 流复制搭建

基于上次的postgresql单机安装,部署postgresql异步流复制


环境准备:

IP
配置
角色
192.168.56.117 1C/2G/20GB centos7
192.168.56.118 1C/2G/20GB centos7


主库执行:

创建用户:

groupadd postgresuseradd -g postgres postgres

创建目录:

mkdir -p /postgresql/datachown -R postgres.postgres /postgresql/datamkdir -p /postgresql/archivechown -R postgres.postgres /postgresql/archive

初始化主库:

initdb -D /postgresql/data

修改主库配置:

vim /postgresql/data/postgresql.conf

listen_addresses = '*'port = 5432max_wal_senders = 10wal_level = replicaarchive_mode = onarchive_command = 'cp %p /postgresql/archive/%f'hot_standby = on

vim /data/pg/pg_hba.conf

在最后添加一行

host    replication     repl            192.168.56.0/24         md5

重启主库:

su - postgrespg_ctl -D /postgresql/data/ restart

创建复制用户:

psqlCREATE USER repl replication LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD '123456';


备库执行:

创建用户:

groupadd postgresuseradd -g postgres postgres

创建数据目录: 

mkdir -p /postgresql/datachown -R postgres.postgres /postgresql/datamkdir -p /postgresql/archivechown -R postgres.postgres /postgresql/archive

创建基于主库的基础备份:

su - postgrespg_basebackup -h 192.168.56.117 -U repl -W -p 5432 -Fp -Xs -v -P -D /postgresql/data -l backup_label -R


[postgres@localhost data]$ pg_basebackup -h 192.168.56.117 -U repl -W -p 5432 -Fp -Xs -v -P -D /postgresql/data -l backup_label -RPassword: pg_basebackup: initiating base backup, waiting for checkpoint to completepg_basebackup: checkpoint completedpg_basebackup: write-ahead log start point: 0/6000028 on timeline 1pg_basebackup: starting background WAL receiverpg_basebackup: created temporary replication slot "pg_basebackup_2964"32554/32554 kB (100%), 1/1 tablespace                                         pg_basebackup: write-ahead log end point: 0/6000100pg_basebackup: waiting for background process to finish streaming ...pg_basebackup: syncing data to disk ...pg_basebackup: base backup completed


不同于postgresql11版本及之前,postgresql12在建立流复制方面有所改动:

recovery.conf is no longer used, and the server will not start if the file exists. recovery.signal and standby.signal are now used to switch into non-primary mode. trigger_file has been renamed to promote_trigger_file. The standby_mode setting has been removed.

大致包含如下改动:

  • recover.conf不再支持,其中的参数合并至postgresql.conf,如该文件存在,则数据库启动失败

  • 不再支持standby_mode参数

  • 增加recover.signal文件,表示数据库处于recover模式

  • 增加standby.signal文件,表示数据库处于standby模式

  • trigger_file参数重命名为promote_trigger_file

在基础备份pb_basebackup中指定-R则会在数据目录生成standby.signal

在postgresql.auto.conf中,也生成了primary_conninfo参数配置

[postgres@localhost data]$ cat postgresql.auto.conf # Do not edit this file manually!# It will be overwritten by the ALTER SYSTEM command.primary_conninfo = 'user=repl password=123456 host=192.168.56.117 port=5432 sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

启动备库:

pg_ctl -D /data/pg start


验证流复制状态:

主库执行:

select * from pg_stat_replication;\x

查看流复制状态:

postgres=# select * from pg_stat_replication;\x-[ RECORD 1 ]----+------------------------------pid              | 3048usesysid         | 16384usename          | replapplication_name | walreceiverclient_addr      | 192.168.56.118client_hostname  | client_port      | 60894backend_start    | 2020-09-04 10:37:11.915897+08backend_xmin     | state            | streamingsent_lsn         | 0/7000148write_lsn        | 0/7000148flush_lsn        | 0/7000148replay_lsn       | 0/7000148write_lag        | 00:00:00.030901flush_lag        | 00:00:00.032029replay_lag       | 00:00:00.032136sync_priority    | 0sync_state       | asyncreply_time       | 2020-09-04 10:37:11.939726+08
Expanded display is off.


上一篇:postgresql备份与PITR恢复


下一篇:达梦数据库性能优化-SQL优化案例分析