2018年4月18日

PostgreSQL流复制

PostgreSQL流复制

环境 PostgreSQL 10

配置Master

创建数据库

initdb -E UTF-8 --locale=en_US.UTF-8 s1

配置 Master(s1) postgresql.conf

# listen
port = 15001
unix_socket_directories = '/tmp'

# ssl
ssl = on
ssl_cert_file = '/home/miao/pg_certs/s1.crt'
ssl_key_file = '/home/miao/pg_certs/s1.key'
ssl_ca_file = '/home/miao/pg_certs/ca.crt'

# wal
wal_level = replica
wal_log_hints = on
max_wal_senders = 5
max_replication_slots = 5

# sync
fsync = off
synchronous_commit = remote_apply
synchronous_standby_names = '1 (s1, s2, s3)'

# archive
archive_mode = on
archive_command = 'test ! -f /home/miao/pg_archive/s1/%f && cp %p /home/miao/pg_archive/s1/%f'

# environment
timezone = 'UTC'
lc_messages = 'en_US.UTF-8'

# slave mode
hot_standby = on

创建archive目录

mkdir -p /home/miao/pg_archive/s1

启动 Master(s1) pg_ctl -D s1 start

定义临时env

# 避免之后每次都要输入 -h 127.0.0.1 -p 15001 可忽略
env='-h 127.0.0.1 -p 15001'

创建 Master(s1) slots

psql -h 127.0.0.1 -p 15001 postgres -c "SELECT pg_create_physical_replication_slot('s1')";
psql -h 127.0.0.1 -p 15001 postgres -c "SELECT pg_create_physical_replication_slot('s2')";
psql -h 127.0.0.1 -p 15001 postgres -c "SELECT pg_create_physical_replication_slot('s3')";

配置Slave

备份数据库 Master(s1) 到 Slave(s2, s3)

for id in 2 3; do
    name=s$id
    mkdir -p /home/miao/pg_archive/$name

    # -R write recovery.conf for replication
    # -X include required WAL files with specified method
    pg_basebackup -h 127.0.0.1 -p 15001 -R -D $name -S $name -X stream

    # 修改 Slave 的端口
    sed -i "s/15001/1500$id/g" $name/postgresql.conf 

    # archive路径
    sed -i "s@/s1@/$name@g" $name/postgresql.conf 

    # 给 Slave recovery.conf 添加application_name
    grep application_name $name/recovery.conf || sed -i "s/15001/& application_name=$name/" $name/recovery.conf 
done

启动 Slave(s2, s3)

pg_ctl -D s2 start
pg_ctl -D s3 start

在 Master(s1) 上可以看到 s2 与 s3都已经连接

# 显示 Master 的slot
psql -h 127.0.0.1 -p 15001 postgres  -c 'SELECT * FROM pg_replication_slots;' 
 slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn 
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
 s3        |        | physical  |        |          | f         | t      |      21169 |      |              | 0/250000D0  | 
 s2        |        | physical  |        |          | f         | t      |      21140 |      |              | 0/250000D0  | 
 s1        |        | physical  |        |          | f         | f      |            |      |              |             | 
(3 rows)

# 显示复制状态
psql -h 127.0.0.1 -p 15001 postgres  -c 'SELECT application_name, replay_lsn, replay_lag, sync_state FROM pg_stat_replication;'
 application_name | replay_lsn | replay_lag | sync_state 
------------------+------------+------------+------------
 s2               | 0/26929D80 |            | sync
 s3               | 0/26929D80 |            | potential
(2 rows)

测试

添加测试数据

createdb -h 127.0.0.1 -p 15001 pgbench
pgbench -h 127.0.0.1 -p 15001 -i pgbench

# 可以看到s2中也同步插入了数据
psql -h 127.0.0.1 -p 15002 pgbench -c 'SELECT count(1) FROM pgbench_accounts;'
 count  
--------
 100000
(1 row)

当 Slave s2 当掉

kill -9 `cat s2/postmaster.pid|head -n1`

 application_name | replay_lsn | replay_lag | sync_state 
------------------+------------+------------+------------
 s3               | 0/26929D80 |            | sync

s3 由备选成为同步状态

Master当前的lsn

SELECT pg_current_wal_lsn()

Slave当前的的lsn

SELECT pg_last_wal_replay_lsn();