PostgreSQL12主从流复制（一主两从）
一、简介
流复制就是备服务器通过tcp流从主服务器中同步相应的数据，主服务器在WAL记录产生时即将它们以流式传送给备服务器，而不必等到WAL文件被填充。
流复制同步方式有同步、异步两种。
物理复制优点：
- 物理层面完全一样，是主要的复制方式，类似于Oracle的物理DG。
- 延迟低，事务执行过程中产生REDO record，实时的在备库apply，事务结束时，备库里面能见到数据。
- 物理复制的一致性，可靠性高。不必担心数据逻辑层面不一致。
二、基于docker搭建1主1从
1主1从环境如下：
| IP | 主机名 | 作用 | port | 类型 | 备注 |
|---|---|---|---|---|---|
| 172.72.6.2 | mambapg64302 | Master | 5432 | 写入 | 对外提供写服务 |
| 172.72.6.3 | mambapg64303 | slave node1 | 5432 | 读 | 对外提供读服务 |
| 172.72.6.4 | mambapg64304 | slave node2 | 5432 | 读 | 对外提供读服务 |
1、环境准备
[root@wcbpg ~]# docker pull postgres:12
[root@wcbpg ~]# docker network create --subnet=172.72.6.0/24 pg-network
9704fe08479201d53299f1829e13f4919a50168c74a4d2658693327b7dc1e9d2
[root@wcbpg ~]# mkdir -p /docker_data/pg/mambapg64302/data
[root@wcbpg ~]#
[root@wcbpg ~]# mkdir -p /docker_data/pg/mambapg64303/data
#主库
[root@wcbpg ~]#docker rm -f mambapg64302
[root@wcbpg ~]#rm -rf /docker_data/pg/mambapg64302/data
[root@wcbpg ~]#docker run -d --name mambapg64302 -h mambapg64302 \
-p 64302:5432 --net=pg-network --ip 172.72.6.2 \
-v /docker_data/pg/mambapg64302/data:/var/lib/postgresql/data \
-v /docker_data/pg/mambapg64302/bk:/bk \
-e POSTGRES_PASSWORD=wcb \
-e TZ=Asia/Shanghai \
postgres:12
# 从库
[root@wcbpg ~]#docker rm -f mambapg64303
[root@wcbpg ~]#rm -rf /docker_data/pg/mambapg64303/data
[root@wcbpg ~]#rm -rf /docker_data/pg/mambapg64303/bk
[root@wcbpg ~]#docker run -d --name mambapg64303 -h mambapg64303 \
-p 64303:5432 --net=pg-network --ip 172.72.6.3 \
-v /docker_data/pg/mambapg64303/data:/var/lib/postgresql/data \
-v /docker_data/pg/mambapg64303/bk:/bk \
-e POSTGRES_PASSWORD=wcb \
-e TZ=Asia/Shanghai \
postgres:12
主库环境准备
[root@wcbpg ~]#cat << EOF > /docker_data/pg/mambapg64302/data/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
local all all trust
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 md5
host replication all 0.0.0.0/0 md5
EOF
[root@wcbpg ~]# docker exec -it mambapg64302 bash
root@mambapg64302:/# mkdir -p /postgresql/archive
root@mambapg64302:/#
root@mambapg64302:/# chown -R postgres.postgres /postgresql/archive
root@mambapg64302:/#cat >> /var/lib/postgresql/data/postgresql.conf <<"EOF"
wal_level='replica'
archive_mode='on'
archive_command='test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f'
restore_command='cp /postgresql/archive/%f %p'
max_wal_senders=10
wal_keep_segments=256
wal_sender_timeout=60s
EOF
root@mambapg64302:/# exit
exit
[root@wcbpg ~]#
[root@wcbpg ~]# docker restart mambapg64302
root@mambapg64302:/# psql -U postgres -h 192.168.142.110 -p 64302
Password for user postgres:
psql (12.9 (Debian 12.9-1.pgdg110+1))
Type "help" for help.
postgres=# select * from pg_settings where name in ('wal_level','archive_mode','archive_command');
postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/164DEF8
(1 row)
postgres=# select * from pg_settings where name in ('wal_level','archive_mode','archive_command');
name | setting | unit | category |
short_desc | extra_desc | context | vartype | source | min_val | max_val |
enumvals | boot_val | reset_val | sourcefile
| sourceline | pending_restart
-----------------+------------------------------------------------------------------+------+-----------------------------+------------
-------------------------------------------------------+------------+------------+---------+--------------------+---------+---------+-
--------------------------+----------+------------------------------------------------------------------+-----------------------------
-------------+------------+-----------------
archive_command | test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f | | Write-Ahead Log / Archiving | Sets the sh
ell command that will be called to archive a WAL file. | | sighup | string | configuration file | | |
| | test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f | /var/lib/postgresql/data/pos
tgresql.conf | 757 | f
archive_mode | on | | Write-Ahead Log / Archiving | Allows arch
iving of WAL files using archive_command. | | postmaster | enum | configuration file | | |
{always,on,off} | off | on | /var/lib/postgresql/data/pos
tgresql.conf | 756 | f
wal_level | replica | | Write-Ahead Log / Settings | Set the lev
el of information written to the WAL. | | postmaster | enum | configuration file | | |
{minimal,replica,logical} | replica | replica | /var/lib/postgresql/data/pos
tgresql.conf | 755 | f
(3 rows)
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_get_archiver();
-[ RECORD 1 ]------+------------------------------
archived_count | 1
last_archived_wal | 000000010000000000000001
last_archived_time | 2021-12-19 22:18:44.628813+08
failed_count | 0
last_failed_wal |
last_failed_time |
stats_reset | 2021-12-19 22:12:25.885834+08
postgres=# select pg_switch_wal();
-[ RECORD 1 ]-+----------
pg_switch_wal | 0/2000078
postgres=# select * from pg_stat_get_archiver();
-[ RECORD 1 ]------+------------------------------
archived_count | 2
last_archived_wal | 000000010000000000000002
last_archived_time | 2021-12-19 22:20:26.62662+08
failed_count | 0
last_failed_wal |
last_failed_time |
stats_reset | 2021-12-19 22:12:25.885834+08
postgres=# \q
root@mambapg64302:/# cd /postgresql/archive/
root@mambapg64302:/postgresql/archive# ls -l
total 32768
-rw------- 1 postgres postgres 16777216 Dec 19 22:18 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Dec 19 22:20 000000010000000000000002
root@mambapg64302:/postgresql/archive# psql -U postgres -h 192.168.142.110 -p 64302
Password for user postgres:
psql (12.9 (Debian 12.9-1.pgdg110+1))
Type "help" for help.
postgres=# create role repwcb login encrypted password 'wcb' replication;
CREATE ROLE
从库环境准备
[root@wcbpg ~]# docker exec -it mambapg64303 bash
root@mambapg64303:/# mkdir -p /bk
root@mambapg64303:/# chown postgres:postgres /bk
root@mambapg64303:/# mkdir -p /postgresql/archive
root@mambapg64303:/# chown -R postgres.postgres /postgresql/archive
root@mambapg64303:/# su - postgres
postgres@mambapg64303:~$ pg_basebackup -h 172.72.6.2 -p 5432 -U repwcb -l bk20210422 -F p -P -R -D /bk
Password:
24632/24632 kB (100%), 1/1 tablespace
postgres@mambapg64303:~$ cd /bk/
postgres@mambapg64303:/bk$ ls -lth
total 52K
-rw------- 1 postgres postgres 320 Dec 19 14:25 postgresql.auto.conf
-rw------- 1 postgres postgres 0 Dec 19 14:25 standby.signal
drwx------ 2 postgres postgres 4.0K Dec 19 14:25 global
-rw------- 1 postgres postgres 1.6K Dec 19 14:25 pg_ident.conf
-rw------- 1 postgres postgres 3 Dec 19 14:25 PG_VERSION
-rw------- 1 postgres postgres 243 Dec 19 14:25 pg_hba.conf
drwx------ 4 postgres postgres 68 Dec 19 14:25 pg_logical
drwx------ 2 postgres postgres 6 Dec 19 14:25 pg_replslot
drwx------ 2 postgres postgres 6 Dec 19 14:25 pg_stat
drwx------ 2 postgres postgres 6 Dec 19 14:25 pg_stat_tmp
drwx------ 2 postgres postgres 6 Dec 19 14:25 pg_tblspc
drwx------ 2 postgres postgres 18 Dec 19 14:25 pg_xact
-rw------- 1 postgres postgres 27K Dec 19 14:25 postgresql.conf
drwx------ 5 postgres postgres 41 Dec 19 14:25 base
drwx------ 2 postgres postgres 6 Dec 19 14:25 pg_commit_ts
drwx------ 2 postgres postgres 6 Dec 19 14:25 pg_dynshmem
drwx------ 4 postgres postgres 36 Dec 19 14:25 pg_multixact
drwx------ 2 postgres postgres 6 Dec 19 14:25 pg_notify
drwx------ 2 postgres postgres 6 Dec 19 14:25 pg_serial
drwx------ 2 postgres postgres 6 Dec 19 14:25 pg_snapshots
drwx------ 2 postgres postgres 6 Dec 19 14:25 pg_subtrans
drwx------ 2 postgres postgres 6 Dec 19 14:25 pg_twophase
drwx------ 3 postgres postgres 60 Dec 19 14:25 pg_wal
-rw------- 1 postgres postgres 209 Dec 19 14:25 backup_label
root@mambapg64303:/# exit
exit
[root@wcbpg ~]#
[root@wcbpg ~]# docker stop mambapg64303
mambapg64303
[root@wcbpg ~]#
[root@wcbpg ~]# rm -rf /docker_data/pg/mambapg64303/data/*
[root@wcbpg ~]#
[root@wcbpg ~]# cp -r /docker_data/pg/mambapg64303/bk/* /docker_data/pg/mambapg64303/data/
[root@wcbpg ~]#cat >> /docker_data/pg/mambapg64303/data/postgresql.conf <<"EOF"
primary_conninfo = 'host=172.72.6.2 port=5432 user=repwcb password=wcb'
EOF
[root@wcbpg ~]# docker start mambapg64303
mambapg64303
查看复制状态
#主库
root@mambapg64302:/# psql -U postgres -h 192.168.142.110 -p 64302
Password for user postgres:
psql (12.9 (Debian 12.9-1.pgdg110+1))
Type "help" for help.
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 114
usesysid | 16384
usename | repwcb
application_name | walreceiver
client_addr | 172.72.6.3
client_hostname |
client_port | 39084
backend_start | 2021-12-19 22:33:17.514563+08
backend_xmin |
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 | 2021-12-19 22:35:47.764274+08
postgres=#
#备库
root@mambapg64303:/# psql -U postgres -h 192.168.142.110 -p 64303
Password for user postgres:
psql (12.9 (Debian 12.9-1.pgdg110+1))
Type "help" for help.
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 35
status | streaming
receive_start_lsn | 0/5000000
receive_start_tli | 1
received_lsn | 0/5000148
received_tli | 1
last_msg_send_time | 2021-12-19 22:36:17.786933+08
last_msg_receipt_time | 2021-12-19 22:36:17.787055+08
latest_end_lsn | 0/5000148
latest_end_time | 2021-12-19 22:33:17.518273+08
slot_name |
sender_host | 172.72.6.2
sender_port | 5432
conninfo | user=repwcb password=******** channel_binding=prefer dbname=replication host=172.72.6.2 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any
#主库查询复制状态
root@mambapg64302:/# psql -U postgres -h 192.168.142.110 -p 64302
Password for user postgres:
psql (12.9 (Debian 12.9-1.pgdg110+1))
Type "help" for help.
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 114
usesysid | 16384
usename | repwcb
application_name | walreceiver
client_addr | 172.72.6.3
client_hostname |
client_port | 39084
backend_start | 2021-12-19 22:33:17.514563+08
backend_xmin |
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 | 2021-12-19 22:38:38.073066+08
postgres=# \q
root@mambapg64302:/# pg_controldata
pg_control version number: 1201
Catalog version number: 201909212
Database system identifier: 7043416275081310245
Database cluster state: in production
pg_control last modified: Sun 19 Dec 2021 10:30:03 PM CST
Latest checkpoint location: 0/5000098
Latest checkpoint's REDO location: 0/5000060
Latest checkpoint's REDO WAL file: 000000010000000000000005
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:488
Latest checkpoint's NextOID: 24576
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 480
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 488
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: Sun 19 Dec 2021 10:30:03 PM CST
Fake LSN counter for unlogged rels: 0/3E8
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
wal_level setting: replica
wal_log_hints setting: off
max_connections setting: 100
max_worker_processes setting: 8
max_wal_senders setting: 10
max_prepared_xacts setting: 0
max_locks_per_xact setting: 64
track_commit_timestamp setting: off
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 0
Mock authentication nonce: 08d844219aa2891b9ee1b0feddb11ea20cef6d9361735b569f9c8a018565c374
#备库
postgres=# \q
root@mambapg64303:/# pg_controldata
pg_control version number: 1201
Catalog version number: 201909212
Database system identifier: 7043416275081310245
Database cluster state: in archive recovery
pg_control last modified: Sun 19 Dec 2021 10:38:17 PM CST
Latest checkpoint location: 0/5000098
Latest checkpoint's REDO location: 0/5000060
Latest checkpoint's REDO WAL file: 000000010000000000000005
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:488
Latest checkpoint's NextOID: 24576
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 480
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 488
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: Sun 19 Dec 2021 10:30:03 PM CST
Fake LSN counter for unlogged rels: 0/3E8
Minimum recovery ending location: 0/5000110
Min recovery ending loc's timeline: 1
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
wal_level setting: replica
wal_log_hints setting: off
max_connections setting: 100
max_worker_processes setting: 8
max_wal_senders setting: 10
max_prepared_xacts setting: 0
max_locks_per_xact setting: 64
track_commit_timestamp setting: off
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 0
Mock authentication nonce: 08d844219aa2891b9ee1b0feddb11ea20cef6d9361735b569f9c8a018565c374
2、主备测试
#主库
root@mambapg64302:/# psql -U postgres -h 192.168.142.110 -p 64302
Password for user postgres:
psql (12.9 (Debian 12.9-1.pgdg110+1))
Type "help" for help.
postgres=# create database pgtest;
CREATE DATABASE
postgres=# \c pgtest
You are now connected to database "pgtest" as user "postgres".
pgtest=#
#备库
root@mambapg64303:/# psql -U postgres -h 192.168.142.110 -p 64303
Password for user postgres:
psql (12.9 (Debian 12.9-1.pgdg110+1))
Type "help" for help.
postgres=#
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
pgtest | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
#在可以执行sysbench的地方执行，快速创建测试数据
sysbench /usr/share/sysbench/oltp_common.lua --db-driver=pgsql \
--pgsql-host=192.168.142.110 --pgsql-port=64302 \
--pgsql-user=postgres --pgsql-password=wcb --pgsql-db=pgtest \
--table-size=100000 --tables=10 --threads=100 \
--events=999999999 --time=60 prepare
#可以查看到主库已经相应的数据
root@mambapg64302:/# psql -U postgres -h 192.168.142.110 -p 64302
Password for user postgres:
psql (12.9 (Debian 12.9-1.pgdg110+1))
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
pgtest | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres=# \c pgtest
You are now connected to database "pgtest" as user "postgres".
pgtest=# \d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-----------------+----------+----------+------------+-------------
public | sbtest1 | table | postgres | 21 MB |
public | sbtest10 | table | postgres | 21 MB |
public | sbtest10_id_seq | sequence | postgres | 8192 bytes |
public | sbtest1_id_seq | sequence | postgres | 8192 bytes |
public | sbtest2 | table | postgres | 21 MB |
public | sbtest2_id_seq | sequence | postgres | 8192 bytes |
public | sbtest3 | table | postgres | 21 MB |
public | sbtest3_id_seq | sequence | postgres | 8192 bytes |
public | sbtest4 | table | postgres | 21 MB |
public | sbtest4_id_seq | sequence | postgres | 8192 bytes |
public | sbtest5 | table | postgres | 21 MB |
public | sbtest5_id_seq | sequence | postgres | 8192 bytes |
public | sbtest6 | table | postgres | 21 MB |
public | sbtest6_id_seq | sequence | postgres | 8192 bytes |
public | sbtest7 | table | postgres | 21 MB |
public | sbtest7_id_seq | sequence | postgres | 8192 bytes |
public | sbtest8 | table | postgres | 21 MB |
public | sbtest8_id_seq | sequence | postgres | 8192 bytes |
public | sbtest9 | table | postgres | 21 MB |
public | sbtest9_id_seq | sequence | postgres | 8192 bytes |
(20 rows)
#备库也有相应的数据
root@mambapg64303:/# psql -U postgres -h 192.168.142.110 -p 64303
Password for user postgres:
psql (12.9 (Debian 12.9-1.pgdg110+1))
Type "help" for help.
postgres=#
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
pgtest | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres=# \c pgtest
You are now connected to database "pgtest" as user "postgres".
pgtest=# \d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-----------------+----------+----------+------------+-------------
public | sbtest1 | table | postgres | 21 MB |
public | sbtest10 | table | postgres | 21 MB |
public | sbtest10_id_seq | sequence | postgres | 8192 bytes |
public | sbtest1_id_seq | sequence | postgres | 8192 bytes |
public | sbtest2 | table | postgres | 21 MB |
public | sbtest2_id_seq | sequence | postgres | 8192 bytes |
public | sbtest3 | table | postgres | 21 MB |
public | sbtest3_id_seq | sequence | postgres | 8192 bytes |
public | sbtest4 | table | postgres | 21 MB |
public | sbtest4_id_seq | sequence | postgres | 8192 bytes |
public | sbtest5 | table | postgres | 21 MB |
public | sbtest5_id_seq | sequence | postgres | 8192 bytes |
public | sbtest6 | table | postgres | 21 MB |
public | sbtest6_id_seq | sequence | postgres | 8192 bytes |
public | sbtest7 | table | postgres | 21 MB |
public | sbtest7_id_seq | sequence | postgres | 8192 bytes |
public | sbtest8 | table | postgres | 21 MB |
public | sbtest8_id_seq | sequence | postgres | 8192 bytes |
public | sbtest9 | table | postgres | 21 MB |
public | sbtest9_id_seq | sequence | postgres | 8192 bytes |
(20 rows)
3、主从switchover切换
主库切换前准备
postgres@mambapg64302:~/data$ pwd
/var/lib/postgresql/data
postgres@mambapg64302:~/data$
postgres@mambapg64302:~/data$ touch standby.signal
postgres@mambapg64302:~/data$ cat >> postgresql.conf <<"EOF"
primary_conninfo = 'host=172.72.6.3 port=5432 user=repwcb password=wcb'
EOF
postgres@mambapg64302:~/data$ exit
logout
root@mambapg64302:/# exit
[root@wcbpg data]# docker stop mambapg64302
mambapg64302
[root@wcbpg data]# docker start mambapg64302
mambapg64302
备库切换前准备
postgres@mambapg64303:~/data$ pwd
/var/lib/postgresql/data
postgres@mambapg64303:~/data$ rm -rf standby.signal
postgres@mambapg64303:~/data$ sed -i 's/primary_conninfo/#primary_conninfo/g' postgresql.conf
postgres@mambapg64303:~/data$ exit
logout
root@mambapg64303:/# exit
exit
[root@wcbpg ~]#
[root@wcbpg ~]# docker stop mambapg64303
mambapg64303
[root@wcbpg ~]#
[root@wcbpg ~]# docker start mambapg64303
mambapg64303
主备切换
[root@wcbpg ~]# docker stop mambapg64302
mambapg64302
[root@wcbpg ~]# docker exec -it mambapg64303 bash
root@mambapg64303:/# psql -U postgres -h 192.168.142.110 -p 64303
Password for user postgres:
psql (12.9 (Debian 12.9-1.pgdg110+1))
Type "help" for help.
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
postgres=# \c pgtest
You are now connected to database "pgtest" as user "postgres".
pgtest=# create table pgtable(id int,name char);
CREATE TABLE
还原6.2
[root@wcbpg ~]# docker start mambapg64302
mambapg64302
[root@wcbpg ~]# docker exec -it mambapg64302 bash
root@mambapg64302:/# mkdir -p /bk
root@mambapg64302:/#
root@mambapg64302:/# chown postgres:postgres /bk
root@mambapg64302:/#
root@mambapg64302:/# su - postgres
postgres@mambapg64302:~$ pg_basebackup -h 172.72.6.3 -p 5432 -U repwcb -l bk20210423 -F p -P -R -D /bk
Password:
293964/293964 kB (100%), 1/1 tablespace
postgres@mambapg64302:~$ exit
logout
root@mambapg64302:/# exit
exit
[root@wcbpg ~]# docker stop mambapg64302
mambapg64302
[root@wcbpg ~]# rm -rf /docker_data/pg/mambapg64302/data/*
[root@wcbpg ~]#
[root@wcbpg ~]# cp -r /docker_data/pg/mambapg64302/bk/* /docker_data/pg/mambapg64302/data/
[root@wcbpg ~]# sed -i 's/172.72.6.2/172.72.6.3/g' /docker_data/pg/mambapg64302/data/postgresql.conf
[root@wcbpg ~]# docker start mambapg64302
mambapg64302
[root@wcbpg ~]# docker exec -it mambapg64302 bash
root@mambapg64302:/# psql -U postgres -h 192.168.142.110 -p 64302
Password for user postgres:
psql (12.9 (Debian 12.9-1.pgdg110+1))
Type "help" for help.
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
4、新增节点
在新增节点之前，根据前面的方法进行主备切换，把6.2在切换回主库。
[root@wcbpg data]# mkdir -p /docker_data/pg/mambapg64304/data
[root@wcbpg data]#docker rm -f mambapg64304
[root@wcbpg data]#rm -rf /docker_data/pg/mambapg64304/data
[root@wcbpg data]#rm -rf /docker_data/pg/mambapg64304/bk
[root@wcbpg data]#docker run -d --name mambapg64304 -h mambapg64304 \
-p 64304:5432 --net=pg-network --ip 172.72.6.4 \
-v /docker_data/pg/mambapg64304/data:/var/lib/postgresql/data \
-v /docker_data/pg/mambapg64304/bk:/bk \
-e POSTGRES_PASSWORD=wcb \
-e TZ=Asia/Shanghai \
postgres:12
[root@wcbpg data]# docker exec -it mambapg64304 bash
root@mambapg64304:/# mkdir -p /bk
root@mambapg64304:/# chown postgres:postgres /bk
root@mambapg64304:/#
root@mambapg64304:/# su - postgres
postgres@mambapg64304:~$
postgres@mambapg64304:~$ pg_basebackup -h 172.72.6.2 -p 5432 -U repwcb -l bk20210422 -F p -P -R -D /bk
Password:
293965/293965 kB (100%), 1/1 tablespace
postgres@mambapg64304:~$ exit
logout
root@mambapg64304:/# exit
exit
[root@wcbpg data]# docker stop mambapg64304
mambapg64304
[root@wcbpg data]#
[root@wcbpg data]# rm -rf /docker_data/pg/mambapg64304/data/*
[root@wcbpg data]#
[root@wcbpg data]# cp -r /docker_data/pg/mambapg64304/bk/* /docker_data/pg/mambapg64304/data/
[root@wcbpg data]#
[root@wcbpg data]# cat >> /docker_data/pg/mambapg64304/data/postgresql.conf <<"EOF"
primary_conninfo = 'host=172.72.6.2 port=5432 user=repwcb password=wcb'
EOF
[root@wcbpg data]#
[root@wcbpg data]#
[root@wcbpg data]# docker start mambapg64304
mambapg64304
[root@wcbpg data]# docker exec -it mambapg64304 bash
root@mambapg64304:/#
root@mambapg64304:/# psql -U postgres -h 192.168.142.110 -p 64304
Password for user postgres:
psql (12.9 (Debian 12.9-1.pgdg110+1))
Type "help" for help.
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 33
status | streaming
receive_start_lsn | 0/1D000000
receive_start_tli | 1
received_lsn | 0/1D000000
received_tli | 1
last_msg_send_time | 2021-12-19 23:15:58.891951+08
last_msg_receipt_time | 2021-12-19 23:15:58.892033+08
latest_end_lsn | 0/1D000148
latest_end_time | 2021-12-19 23:14:58.845821+08
slot_name |
sender_host | 172.72.6.2
sender_port | 5432
conninfo | user=repwcb password=******** channel_binding=prefer dbname=replication host=172.72.6.2 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any
postgres=# \x
Expanded display is off.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
pgtest | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres=# \c pgtest
You are now connected to database "pgtest" as user "postgres".
pgtest=# \d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-----------------+----------+----------+------------+-------------
public | pgtable | table | postgres | 0 bytes |
public | sbtest1 | table | postgres | 21 MB |
public | sbtest10 | table | postgres | 21 MB |
public | sbtest10_id_seq | sequence | postgres | 8192 bytes |
public | sbtest1_id_seq | sequence | postgres | 8192 bytes |
public | sbtest2 | table | postgres | 21 MB |
public | sbtest2_id_seq | sequence | postgres | 8192 bytes |
public | sbtest3 | table | postgres | 21 MB |
public | sbtest3_id_seq | sequence | postgres | 8192 bytes |
public | sbtest4 | table | postgres | 21 MB |
public | sbtest4_id_seq | sequence | postgres | 8192 bytes |
public | sbtest5 | table | postgres | 21 MB |
public | sbtest5_id_seq | sequence | postgres | 8192 bytes |
public | sbtest6 | table | postgres | 21 MB |
public | sbtest6_id_seq | sequence | postgres | 8192 bytes |
public | sbtest7 | table | postgres | 21 MB |
public | sbtest7_id_seq | sequence | postgres | 8192 bytes |
public | sbtest8 | table | postgres | 21 MB |
public | sbtest8_id_seq | sequence | postgres | 8192 bytes |
public | sbtest9 | table | postgres | 21 MB |
public | sbtest9_id_seq | sequence | postgres | 8192 bytes |
(21 rows)
主从查询复制状态
主库
postgres=# select client_addr,state,sync_state from pg_stat_replication;
client_addr | state | sync_state
-------------+-----------+------------
172.72.6.3 | streaming | async
172.72.6.4 | streaming | async
(2 rows)
#备库6.4
[root@wcbpg data]# docker exec -it mambapg64304 bash
root@mambapg64304:/# psql -U postgres -h 192.168.142.110 -p 64304
Password for user postgres:
psql (12.9 (Debian 12.9-1.pgdg110+1))
Type "help" for help.
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 32
status | streaming
receive_start_lsn | 0/1E000000
receive_start_tli | 1
received_lsn | 0/20000060
received_tli | 1
last_msg_send_time | 2021-12-19 23:39:59.594861+08
last_msg_receipt_time | 2021-12-19 23:39:59.594941+08
latest_end_lsn | 0/20000060
latest_end_time | 2021-12-19 23:37:29.32084+08
slot_name |
sender_host | 172.72.6.2
sender_port | 5432
conninfo | user=repwcb password=******** channel_binding=prefer dbname=replication host=172.72.6.2 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any
#备库6.3
[root@wcbpg ~]# docker exec -it mambapg64303 bash
root@mambapg64303:/# psql -U postgres -h 192.168.142.110 -p 64303
Password for user postgres:
psql (12.9 (Debian 12.9-1.pgdg110+1))
Type "help" for help.
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 35
status | streaming
receive_start_lsn | 0/20000000
receive_start_tli | 1
received_lsn | 0/20000060
received_tli | 1
last_msg_send_time | 2021-12-19 23:39:13.975006+08
last_msg_receipt_time | 2021-12-19 23:39:13.975089+08
latest_end_lsn | 0/20000060
latest_end_time | 2021-12-19 23:36:43.84321+08
slot_name |
sender_host | 172.72.6.2
sender_port | 5432
conninfo | user=repwcb password=******** channel_binding=prefer dbname=replication host=172.72.6.2 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any
5、修改为同步模式
[root@wcbpg data]#cat >> /docker_data/pg/mambapg64302/data/postgresql.conf <<"EOF"
synchronous_commit='on'
synchronous_standby_names='*'
EOF
[root@wcbpg data]#cat >> /docker_data/pg/mambapg64303/data/postgresql.conf <<"EOF"
synchronous_commit='on'
synchronous_standby_names='*'
EOF
[root@wcbpg data]#cat >> /docker_data/pg/mambapg64304/data/postgresql.conf <<"EOF"
synchronous_commit='on'
synchronous_standby_names='*'
EOF
[root@wcbpg data]#docker stop mambapg64302
[root@wcbpg data]#docker stop mambapg64303
[root@wcbpg data]#docker stop mambapg64304
[root@wcbpg data]#docker start mambapg64302
[root@wcbpg data]#docker start mambapg64303
[root@wcbpg data]#docker start mambapg64304
[root@wcbpg ~]# docker exec -it mambapg64302 bash
root@mambapg64302:/# psql -U postgres -h 192.168.142.110 -p 64302
Password for user postgres:
psql (12.9 (Debian 12.9-1.pgdg110+1))
Type "help" for help.
postgres=# select client_addr,state,sync_state from pg_stat_replication;
client_addr | state | sync_state
-------------+-----------+------------
172.72.6.3 | streaming | sync
172.72.6.4 | streaming | potential
(2 rows)
6、修改回异步模式
[root@wcbpg data]# sed -i 's|synchronous_commit|#synchronous_commit|g' /docker_data/pg/mambapg64302/data/postgresql.conf
[root@wcbpg data]# sed -i 's|synchronous_standby_names|#synchronous_standby_names|g' /docker_data/pg/mambapg64302/data/postgresql.conf
[root@wcbpg data]#
[root@wcbpg data]# sed -i 's|synchronous_commit|#synchronous_commit|g' /docker_data/pg/mambapg64303/data/postgresql.conf
[root@wcbpg data]# sed -i 's|synchronous_standby_names|#synchronous_standby_names|g' /docker_data/pg/mambapg64303/data/postgresql.conf
[root@wcbpg data]#
[root@wcbpg data]# sed -i 's|synchronous_commit|#synchronous_commit|g' /docker_data/pg/mambapg64304/data/postgresql.conf
[root@wcbpg data]# sed -i 's|synchronous_standby_names|#synchronous_standby_names|g' /docker_data/pg/mambapg64304/data/postgresql.conf
[root@wcbpg data]#
[root@wcbpg data]# docker stop mambapg64302
mambapg64302
[root@wcbpg data]# docker stop mambapg64303
mambapg64303
[root@wcbpg data]# docker stop mambapg64304
mambapg64304
[root@wcbpg data]# docker start mambapg64302
mambapg64302
[root@wcbpg data]# docker start mambapg64303
mambapg64303
[root@wcbpg data]# docker start mambapg64304
mambapg64304
[root@wcbpg data]# docker exec -it mambapg64302 bash
root@mambapg64302:/# psql -U postgres -h 192.168.142.110 -p 64302
Password for user postgres:
psql (12.9 (Debian 12.9-1.pgdg110+1))
Type "help" for help.
postgres=# select client_addr,state,sync_state from pg_stat_replication;
client_addr | state | sync_state
-------------+-----------+------------
172.72.6.3 | streaming | async
172.72.6.4 | streaming | async
(2 rows)
postgres=# \q
