pg12.3 搭建流复制

配置流复制

standby 服务器需要提前安装postgres 环境

主库

添加复制账号replic

create user replic replication login connection limit 32 encrypted password 'replic';

添加replic 到 pg_hba.conf

host    replication     replic          172.0.0.2/32           md5

创建slot

postgres=# select * from pg_create_physical_replication_slot('first_slave');
  slot_name  | lsn
-------------+-----
 first_slave |
(1 row)

postgres=# select * from pg_replication_slots ;
  slot_name  | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
-------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
 first_slave |        | physical  |        |          | f         | f      |            |      |              |             |
(1 row)

postgres=#

数据库配置参数

wal_level = logical
synchronous_commit = on
archive_mode = on
archive_command = '/bin/true'
max_wal_senders = 10
wal_keep_segments = 1000
max_replication_slots = 10

pg_start_backup('')

-- pg_start_backup()
$ psql
psql (12.3)
Type "help" for help.

postgres=# select pg_start_backup('base_backup');
 pg_start_backup
-----------------
 0/2000028
(1 row)

打包数据文件并传输到备机

tar -zcvf pg_data.tar.gz pg_data
scp pg_data.tar.gz root@172.0.0.2:/data

pg_stop_backup()

$ psql
psql (12.3)
Type "help" for help.

postgres=# select pg_stop_backup();
NOTICE:  all required WAL segments have been archived
 pg_stop_backup
----------------
 0/2000170
(1 row)

postgres=#

备库

解压数据文件

tar -zxvf pg_data.tar.gz

创建standby.signal文件

pg12 新特性，需要在数据目录下创建standby.signal文件，否则数据库会以master的方式启动
touch standby.signal

数据库配置参数

primary_conninfo = 'host=172.0.0.1 port=5432 user=replic password=replic application_name=slave1'
primary_slot_name = 'firt_slave'
hot_standby = on
max_standby_archive_delay = 30s
max_standby_streaming_delay = 30s
max_logical_replication_workers = 4
max_sync_workers_per_subscription = 2

启动

pg_ctl -D /data/pg_data start

--查看walreceiver进程
ps -ef |grep -i wal
postgres 25527 25522  0 17:08 ?        00:00:00 postgres: walreceiver   streaming 0/3000148

验证

主库创建数据库

postgres=# select usename,application_name,state from pg_stat_replication ;
 usename | application_name |   state
---------+------------------+-----------
 replic  | slave1           | streaming
(1 row)

我们可以看见，slave1已经添加成功

postgres=# create database test;
CREATE DATABASE
postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7953 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7809 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7809 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7809 kB | pg_default |
(4 rows)

postgres=#

备库验证

psql
psql (12.3)
Type "help" for help.

postgres=# create database test;
ERROR:  cannot execute CREATE DATABASE in a read-only transaction
postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7953 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7809 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7809 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7809 kB | pg_default |
(4 rows)

postgres=#

备库是read-only，不允许任何数据库操作，符合预期