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