本文详细介绍了在PostgreSQL12中如何设置和管理主从流复制，包括环境准备、同步与异步模式切换、从节点的添加与删除，确保数据一致性与低延迟。
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 po
最低0.47元/天 解锁文章
扫一扫
3436
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
