【Postgresql】—PostgreSQL的流复制环境部署

1、介绍

PostgreSQL的主从复制有两种方式，分别是：
一：WAL日志归档（base-file）
二：流复制（streaming replication）

第一种是写完一个WAL日志后，才把WAL日志文件拷贝到standby数据库中，简言之就是通过cp命令实现远程备份，这样通常备库会落后主库一个WAL日志文件。而第二种流复制是postgresql9.x之后才提供的新的传递WAL日志的方法，它的好处是只要master库一产生日志，就会马上传递到standby库，同第一种相比有更低的同步延迟，所以我们肯定也会选择流复制的方式。

流复制属于物理层面的复制，可以从实例级复制出一个与主库一模一样的实例级的从库，流复制同步方式有同步、异步两种。

异步流复制模式中，主库提交的事务不会等待备库接收WAL日志流并返回确认信息，因此异步流复制模式下主库与备库的数据版本上会存在一定的处理延迟（毫秒级)，当主库宕机，这个延迟就主要受到故障发现与切换时间的影响而拉长。该模式为默认模式。

同步流复制模式中，要求主库把WAL日志写入磁盘，同时等待WAL日志记录复制到备库、并且WAL日志记录在任意一个备库写入磁盘后，才能向应用返回Commit结果。一旦所有备库故障，在主库的应用操作则会被挂起，所以此方式建议起码是1主2备。

2、环境准备

拉取镜像
docker pull postgres:12

创建网卡
docker network create --subnet=172.172.1.0/24 pg-network

创建容器数据库目录，用于容器内部映射
mkdir -p /pg/pg-master/data
mkdir -p /pg/pg-standby01/data
mkdir -p /pg/pg-standby02/data

主库
docker rm -f pg-master
rm -rf /pg/pg-master/data
rm -rf /pg/pg-master/bk

docker run -d --name pg-master -h pg-master \
   -p 54340:5432 --net=pg-network --ip 172.172.1.40 \
   -v /pg/pg-master/data:/var/lib/postgresql/data \
   -v /pg/pg-master/bk:/bk \
   -e POSTGRES_PASSWORD=lhr \
   -e TZ=Asia/Shanghai \
   postgres:12

从库01
docker rm -f pg-standby01
rm -rf /pg/pg-master/data
rm -rf /pg/pg-master/bk

docker run -d --name pg-standby01 -h pg-standby01 \
   -p 54341:5432 --net=pg-network --ip 172.172.1.41 \
   -v /pg/pg-standby01/data:/var/lib/postgresql/data \
   -v /pg/pg-standby01/bk:/bk \
   -e POSTGRES_PASSWORD=lhr \
   -e TZ=Asia/Shanghai \
   postgres:12
  
从库02
docker rm -f pg-standby02
rm -rf /pg/pg-master/data
rm -rf /pg/pg-master/bk
  
docker run -d --name pg-standby02 -h pg-standby02 \
   -p 54342:5432 --net=pg-network --ip 172.172.1.42 \
   -v /pg/pg-standby02/data:/var/lib/postgresql/data \
   -v /pg/pg-standby02/bk:/bk \
   -e POSTGRES_PASSWORD=lhr \
   -e TZ=Asia/Shanghai \
   postgres:12

进入容器内部
 docker exec -it pg-master bash

远程数据库登录
psql -U postgres -h 172.21.209.100 -p 54340
本地登录
psql -U postgres -h 127.0.0.1 -p 5432

配置防火墙
cat  << EOF > /pg/pg-master/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


重启数据库
/usr/lib/postgresql/12/bin/pg_ctl restart -D /var/lib/postgresql/data/

3、主从流复制配置

3.1、主库配置

创建归档目录，该路径也需要在从库创建
 mkdir -p /postgresql/archive
 chown -R postgres.postgres /postgresql/archive

配置文件添加如下参数
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'

max_wal_senders=10
wal_keep_segments=256
wal_sender_timeout=60s

EOF

配置后重启数据库
root@pg-master:/var/lib/postgresql/data# su - postgres

[root@openstack ~]# docker start pg-master 
或
/usr/lib/postgresql/12/bin/pg_ctl restart -D /var/lib/postgresql/data/

登录数据库查看参数
psql -U postgres -h 127.0.0.1 -p 5432
select * from pg_settings where name in ('wal_level','archive_mode','archive_command');


postgres=# \x
Expanded display is on.
postgres=# select * from pg_settings where name in ('wal_level','archive_mode','archive_command');
-[ RECORD 1 ]---+------------------------------------------------------------------
name            | archive_command
setting         | test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f
unit            | 
category        | Write-Ahead Log / Archiving
short_desc      | Sets the shell command that will be called to archive a WAL file.
extra_desc      | 
context         | sighup
vartype         | string
source          | configuration file
min_val         | 
max_val         | 
enumvals        | 
boot_val        | 
reset_val       | test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f
sourcefile      | /var/lib/postgresql/data/postgresql.conf
sourceline      | 757
pending_restart | f
-[ RECORD 2 ]---+------------------------------------------------------------------
name            | archive_mode
setting         | on
unit            | 
category        | Write-Ahead Log / Archiving
short_desc      | Allows archiving of WAL files using archive_command.
extra_desc      | 
context         | postmaster
vartype         | enum
source          | configuration file
min_val         | 
max_val         | 
enumvals        | {always,on,off}
boot_val        | off
reset_val       | on
sourcefile      | /var/lib/postgresql/data/postgresql.conf
sourceline      | 756
pending_restart | f
-[ RECORD 3 ]---+------------------------------------------------------------------
name            | wal_level
setting         | replica
unit            | 
category        | Write-Ahead Log / Settings
short_desc      | Set the level of information written to the WAL.
extra_desc      | 
context         | postmaster
vartype         | enum
source          | configuration file
min_val         | 
max_val         | 
enumvals        | {minimal,replica,logical}
boot_val        | replica
reset_val       | replica
sourcefile      | /var/lib/postgresql/data/postgresql.conf
sourceline      | 755
pending_restart | f

postgres=# 


切换归档
select pg_switch_wal();
查看归档状态
select * from pg_stat_get_archiver();


执行过程：
postgres=# select * from pg_stat_get_archiver();
-[ RECORD 1 ]------+------------------------------
archived_count     | 0
last_archived_wal  | 
last_archived_time | 
failed_count       | 0
last_failed_wal    | 
last_failed_time   | 
stats_reset        | 2023-12-10 15:45:23.724072+08

postgres=# select pg_switch_wal();
-[ RECORD 1 ]-+----------
pg_switch_wal | 0/164DFE0

postgres=# 
postgres=# select * from pg_stat_get_archiver();
-[ RECORD 1 ]------+------------------------------
archived_count     | 1
last_archived_wal  | 000000010000000000000001
last_archived_time | 2023-12-10 17:21:35.079489+08
failed_count       | 0
last_failed_wal    | 
last_failed_time   | 
stats_reset        | 2023-12-10 15:45:23.724072+08

postgres=# 



主库创建复制用户
create role repluser login encrypted password 'repluser ' replication;


postgres=# create role repluser login encrypted password 'repluser ' replication;
CREATE ROLE
postgres=# \du
List of roles
-[ RECORD 1 ]----------------------------------------------------------
Role name  | postgres
Attributes | Superuser, Create role, Create DB, Replication, Bypass RLS
Member of  | {}
-[ RECORD 2 ]----------------------------------------------------------
Role name  | repluser
Attributes | Replication
Member of  | {}

postgres=# \x
Expanded display is off.
postgres=# 
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 repluser  | Replication                                                | {}

postgres=#

3.2、从库配置

从库对主库进行备份,拉取主库数据后，启动数据库

创建目录，用于拉取主库的数据
mkdir -p /bk
chown postgres:postgres /bk

拉取主库数据
su - postgres
pg_basebackup -h 172.172.1.40  -p 5432 -U repluser -l bk20231210 -F p -P -R -D /bk


执行过程：
root@pg-standby01:/# mkdir -p /bk
root@pg-standby01:/# chown postgres:postgres /bk
root@pg-standby01:/# su - postgres
postgres@pg-standby01:~$ pg_basebackup -h 172.172.1.40  -p 5432 -U repluser -l bk20231210 -F p -P -R -D /bk
24669/24669 kB (100%), 1/1 tablespace

postgres@pg-standby01:~$ cd /bk/
postgres@pg-standby01:/bk$ ls
PG_VERSION    pg_dynshmem       pg_multixact  pg_stat      pg_wal            standby.signal
backup_label  pg_hba.conf       pg_notify     pg_stat_tmp  pg_xact
base          pg_hba.conf-20231210  pg_replslot   pg_subtrans  postgresql.auto.conf
global        pg_ident.conf     pg_serial     pg_tblspc    postgresql.conf
pg_commit_ts  pg_logical        pg_snapshots  pg_twophase  postgresql.conf-20231210
postgres@pg-standby01:/bk$ 
postgres@pg-standby01:/bk$ 


从库操作很重要。
关闭从库，删除从库的数据文件，并且将备份文件覆盖从库的数据文件

这是docker环境，如果是主机环境内部操作即可。
cp -r ./pg-standby01/data ./pg-standby01/data-20231210
rm -rf /pg/pg-standby01/data/*
cp -r /pg/pg-standby01/bk/*  /pg/pg-standby01/data/

执行过程
[root@openstack pg]# ls
pg-master  pg-standby01  pg-standby02
[root@openstack pg]# cp -r ./pg-standby01/data ./pg-standby01/data-20231210
You have new mail in /var/spool/mail/root
[root@openstack pg]# rm -rf /pg/pg-standby01/data/*
[root@openstack pg]# cp -r /pg/pg-standby01/bk/*  /pg/pg-standby01/data/


修改从库配置文件
[root@openstack pg]# cat >> /pg/pg-standby01/data/postgresql.conf <<"EOF"
> 
> primary_conninfo = 'host=172.172.1.40 port=5432 user=repluser password=repluser'
> 
> EOF

重启从库
[root@openstack pg]# docker restart pg-standby01
pg-standby01

3.3、查看主库从库的状态

查看主库和从库进程
ps -ef|grep post

主库查看wal日志发送状态
select * from pg_stat_replication;
从库查看wal日志接收状态
select * from pg_stat_wal_receiver;

也可以通过该命令查看
pg_controldata  | grep state

也可以查看这个，主库是f代表false ；备库是t，代表true
select pg_is_in_recovery();


执行过程：

主库进程：
root@pg-master:/# ps -ef|grep post
postgres     1     0  0 17:18 ?        00:00:00 postgres
postgres    28     1  0 17:18 ?        00:00:00 postgres: checkpointer  
postgres    29     1  0 17:18 ?        00:00:00 postgres: background writer  
postgres    30     1  0 17:18 ?        00:00:00 postgres: walwriter  
postgres    31     1  0 17:18 ?        00:00:00 postgres: autovacuum launcher  
postgres    32     1  0 17:18 ?        00:00:00 postgres: archiver   last was 000000010000000000000003.00000028.backup
postgres    33     1  0 17:18 ?        00:00:00 postgres: stats collector  
postgres    34     1  0 17:18 ?        00:00:00 postgres: logical replication launcher  
postgres   113     1  0 17:34 ?        00:00:00 postgres: walsender repluser 172.172.1.41(45836) streaming 0/4000148
root       222    35  0 17:44 pts/0    00:00:00 grep post
root@pg-master:/# 


从库备库进程
root@pg-standby01:/# ps -ef|grep postgres
postgres     1     0  0 17:34 ?        00:00:00 postgres
postgres    26     1  0 17:34 ?        00:00:00 postgres: startup   recovering 000000010000000000000004
postgres    27     1  0 17:34 ?        00:00:00 postgres: checkpointer  
postgres    28     1  0 17:34 ?        00:00:00 postgres: background writer  
postgres    29     1  0 17:34 ?        00:00:00 postgres: stats collector  
postgres    30     1  0 17:34 ?        00:00:00 postgres: walreceiver  
root       169    31  0 17:40 pts/0    00:00:00 grep postgres

主库查看wal日志发送状态
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 113
usesysid         | 16384
usename          | repluser
application_name | walreceiver
client_addr      | 172.172.1.41
client_hostname  | 
client_port      | 45836
backend_start    | 2023-12-10 17:34:08.379909+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/4000148
write_lsn        | 0/4000148
flush_lsn        | 0/4000148
replay_lsn       | 0/4000148
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2023-12-10 17:44:49.38437+08

postgres=# 


从库查看wal日志接收状态
postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 30
status                | streaming
receive_start_lsn     | 0/4000000
receive_start_tli     | 1
received_lsn          | 0/4000000
received_tli          | 1
last_msg_send_time    | 2023-12-10 17:47:39.657548+08
last_msg_receipt_time | 2023-12-10 17:47:39.657644+08
latest_end_lsn        | 0/4000148
latest_end_time       | 2023-12-10 17:34:08.381928+08
slot_name             | 
sender_host           | 172.172.1.40
sender_port           | 5432
conninfo              | user=repluser passfile=/var/lib/postgresql/.pgpass channel_binding=prefer dbname=replication host=172.172.1.40 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=#

4、测试主从复制

postgres=# \l 
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 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
(3 rows)

postgres=# create database pgtest;
CREATE DATABASE
postgres=# \c pgtest
You are now connected to database "pgtest" as user "postgres".


pgtest=# create table t1(id int,varchar name,age int);
CREATE TABLE


pgtest=# \d  
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t1   | table | postgres
(1 row)

pgtest=# 
pgtest=# INSERT INTO t1 VALUES (1, 'Xiaowang', 29);
INSERT 0 1
pgtest=# 
pgtest=# INSERT INTO t1 VALUES (2, 'Xiaoli', 18);
INSERT 0 1
pgtest=# select * from t1;
 id | varchar  | age 
----+----------+-----
  1 | Xiaowang |  29
  2 | Xiaoli   |  18
(2 rows)

pgtest=# 



主库查询，是否恢复，f表示否
pgtest=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)


从库查询数据同步
pgtest=# \d 
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t1   | table | postgres
(1 row)

pgtest=# select * from t1;
 id | varchar  | age 
----+----------+-----
  1 | Xiaowang |  29
  2 | Xiaoli   |  18
(2 rows)
从库查询，是否为恢复，t表示是恢复正常，表示从库。
pgtest=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)


从库创建数据库失败，因从库是只读的
pgtest=# create database pgtest01;
ERROR:  cannot execute CREATE DATABASE in a read-only transaction
pgtest=# 

说明主库数据同步正常。主数据库是读写的，备数据库是只读的

5、主从切换

主数据库是读写的，备数据库是只读的。当主库出现故障时，我们需要将备库提升为主库进行读写操作。
1）切换后，原主库以从库的身份启动：修复过程类似于重建
2）切换后，原主库以主库的身份启动：
主从故障切换是在保障数据一致情况下，宕主库宕机后，从库阶梯主库继续提供服务。

5.1、主库操作

模拟主库宕机操作，并且将作为备库继续工作
主库宕机
[root@openstack pg]# docker stop  pg-master
pg-master

主库配置文件添加如下信息
cat >> /pg/pg-master/data/postgresql.conf <<"EOF"

primary_conninfo = 'host=172.172.1.41  port=5432 user=repluser password=repluser'

EOF

重启数据库服务
[root@openstack pg]# docker start   pg-master
pg-master

5.2、从库操作

停止备库
[root@openstack pg]# docker stop   pg-standby01
pg-standby01

删除“standby.signal”喷子文件文件
mv  /pg/pg-standby01/data/standby.signal /pg/pg-standby01/data/standby.signal.bak-20231210

修改postgresql.conf
 sed -i 's/primary_conninfo/#primary_conninfo/g' /pg/pg-standby01/data/postgresql.conf

启动数据库服务
[root@openstack pg]# docker start    pg-standby01
pg-standby01

从库查看是否为恢复正常
postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)

从库正常读写操作
postgres=# create database pgtest01;
CREATE DATABASE
postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 pgtest    | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 pgtest01  | 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
(5 rows)

postgres=#

5.3、原主库修复后作为从库加入，该过程相当于重建流复制过程。

原主库操作
创建目录
mkdir -p /bk
chown postgres:postgres /bk

 su - postgres
 pg_basebackup -h 172.172.1.41  -p 5432 -U repluser -l bk20231210 -F p -P -R -D /bk


执行过程：
root@pg-master:/# mkdir -p /bk
root@pg-master:/# chown postgres:postgres /bk
root@pg-master:/#  su - postgres
postgres@pg-master:~$  pg_basebackup -h 172.172.1.41  -p 5432 -U repluser -l bk20231210 -F p -P -R -D /bk
40632/40632 kB (100%), 1/1 tablespace
postgres@pg-master:~$ 




在pg-standby01上查看是否有复制用户，一般做了主从后是有的，如果没有创建。
create role repluser login encrypted password 'repluser ' replication;

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 repluser  | Replication                                                | {}

postgres=# 


关闭从库，并且将备份文件覆盖从库的数据文件
[root@openstack pg]# docker stop    pg-master
pg-master
You have new mail in /var/spool/mail/root
[root@openstack pg]# mv /pg/pg-master/data /pg/pg-master/data-20231210
[root@openstack pg]# mkdir -p /pg/pg-master/data/
[root@openstack pg]# cp -r /pg/pg-master/bk/* /pg/pg-master/data/


修改配置文件信息
sed -i 's/172.172.1.40 /172.172.1.41/g' /pg/pg-master/data/postgresql.conf
sed -i 's/#primary_conninfo/primary_conninfo/g' /pg/pg-master/data/postgresql.conf

启动数据库服务
[root@openstack data]# docker restart     pg-master
pg-master
[root@openstack data]# 


检验主从同步
主库查看进程状态
ps -ef|grep post

主库查看wal日志发送状态
select * from pg_stat_replication;
从库查看wal日志接收状态
select * from pg_stat_wal_receiver;


也可以通过该命令查看
pg_controldata  | grep state

也可以查看这个，主库是f代表false ；备库是t，代表true
select pg_is_in_recovery();

执行过程：
现为从库
postgres@pg-master:~$ ps -ef|grep post
postgres     1     0  0 12:07 ?        00:00:00 postgres
postgres    27     1  0 12:07 ?        00:00:00 postgres: startup   recovering 000000010000000000000007
postgres    28     1  0 12:07 ?        00:00:00 postgres: checkpointer  
postgres    29     1  0 12:07 ?        00:00:00 postgres: background writer  
postgres    30     1  0 12:07 ?        00:00:00 postgres: stats collector  
postgres    31     1  0 12:07 ?        00:00:00 postgres: walreceiver   streaming 0/7000148
root        46    40  0 12:10 pts/0    00:00:00 su - postgres
postgres    47    46  0 12:10 pts/0    00:00:00 -bash
postgres    49    47  0 12:10 pts/0    00:00:00 ps -ef
postgres    50    47  0 12:10 pts/0    00:00:00 grep post


现为主库
postgres@pg-standby01:~$ ps -ef|grep post
postgres     1     0  0 11:40 ?        00:00:00 postgres
postgres    27     1  0 11:40 ?        00:00:00 postgres: checkpointer  
postgres    28     1  0 11:40 ?        00:00:00 postgres: background writer  
postgres    29     1  0 11:40 ?        00:00:00 postgres: walwriter  
postgres    30     1  0 11:40 ?        00:00:00 postgres: autovacuum launcher  
postgres    31     1  0 11:40 ?        00:00:00 postgres: archiver   last was 000000010000000000000006.00000028.backup
postgres    32     1  0 11:40 ?        00:00:00 postgres: stats collector  
postgres    33     1  0 11:40 ?        00:00:00 postgres: logical replication launcher  
root        49    34  0 11:40 pts/0    00:00:00 su - postgres
postgres    50    49  0 11:40 pts/0    00:00:00 -bash
postgres    96     1  0 12:07 ?        00:00:00 postgres: walsender repluser 172.172.1.40(33488) streaming 0/7000148
postgres   102    50  0 12:10 pts/0    00:00:00 ps -ef
postgres   103    50  0 12:10 pts/0    00:00:00 grep post
postgres@pg-standby01:~$

6、新增节点，从节点2添加

6.1、从库配置

创建同步目录
mkdir -p /bk
chown postgres:postgres /bk

su - postgres
pg_basebackup -h 172.172.1.41 -p 5432 -U repluser -l bk20231210 -F p -P -R -D /bk


关闭从库，删除从库的数据文件，并且将备份文件覆盖从库的数据文件
关闭数据库服务
docker stop    pg-standby02
mv /pg/pg-standby02/data /pg/pg-standby02/data-20231210
mkdir -p /pg/pg-standby02/data/
cp -r /pg/pg-standby02/bk/* /pg/pg-standby02/data/


添加或修改配置文件信息，如果存在则修改，没有则添加。

添加
cat >> /pg/pg-standby02/data/postgresql.conf <<"EOF"
primary_conninfo = 'host=172.172.1.41 port=5432 user=repluser password=repluser'
EOF

修改
sed -i 's/172.172.1.40 /172.172.1.41/g' /pg/pg-master/data/postgresql.conf
sed -i 's/#primary_conninfo/primary_conninfo/g' /pg/pg-master/data/postgresql.conf

启动数据库服务
docker restart     pg-standby02




执行过程
root@pg-standby02:/# mkdir -p /bk
root@pg-standby02:/# chown postgres:postgres /bk
root@pg-standby02:/# su - postgres
postgres@pg-standby02:~$ pg_basebackup -h 172.172.1.41 -p 5432 -U repluser -l bk20231210 -F p -P -R -D /bk
40632/40632 kB (100%), 1/1 tablespace
postgres@pg-standby02:~$ 


[root@openstack data]# docker stop    pg-standby02
pg-standby02
You have new mail in /var/spool/mail/root
[root@openstack data]# mv /pg/pg-standby02/data /pg/pg-standby02/data-20231210
[root@openstack data]# mkdir -p /pg/pg-standby02/data/
[root@openstack data]# cp -r /pg/pg-standby02/bk/* /pg/pg-standby02/data/


[root@openstack data]# cat >> /pg/pg-standby02/data/postgresql.conf <<"EOF"
> 
> primary_conninfo = 'host=172.172.1.41 port=5432 user=repluser password=repluser'
> 
> EOF
[root@openstack data]# 

[root@openstack data]# docker restart     pg-standby02
pg-standby02

6.2、从库状态检测

主库从库操作
1、查看进程
ps -ef|grep post

2、查看你日志状态
主库查看wal日志发送状态
select * from pg_stat_replication;
从库查看wal日志接收状态
select * from pg_stat_wal_receiver;


3、查看数据库的状态
主库从库都可以查看
pg_controldata  | grep state

4、查看恢复的状态，主库是f代表false ；备库是t，代表true
select pg_is_in_recovery();

5、从库查看数据同步的表和数据
select * from t1;

6、在主库查看主从状态
select * from pg_stat_replication;


主从同步的状态查看
postgres=# select * from pg_stat_replication;
 pid | usesysid | usename  | application_name | client_addr  | client_hostname | client_port |         backend_start
         | backend_xmin |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | repla
y_lag | sync_priority | sync_state |          reply_time           
-----+----------+----------+------------------+--------------+-----------------+-------------+----------------------
---------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------
------+---------------+------------+-------------------------------
  96 |    16384 | repluser | walreceiver      | 172.172.1.40 |                 |       33488 | 2023-12-10 20:07:52.0
87953+08 |              | streaming | 0/9000148 | 0/9000148 | 0/9000148 | 0/9000148  |           |           |      
      |             0 | async      | 2023-12-10 20:37:23.120884+08
 138 |    16384 | repluser | walreceiver      | 172.172.1.42 |                 |       47518 | 2023-12-10 20:28:01.2
35939+08 |              | streaming | 0/9000148 | 0/9000148 | 0/9000148 | 0/9000148  |           |           |      
      |             0 | async      | 2023-12-10 20:37:23.120688+08
(2 rows)


postgres=# select client_addr,state,sync_state from pg_stat_replication;
 client_addr  |   state   | sync_state 
--------------+-----------+------------
 172.172.1.40 | streaming | async
 172.172.1.42 | streaming | async
(2 rows)

postgres=# 

主库的
postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)
从库的
postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)

postgres=# 


从库查询wal日志的状体
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 31
status                | streaming
receive_start_lsn     | 0/9000000
receive_start_tli     | 1
received_lsn          | 0/9000148
received_tli          | 1
last_msg_send_time    | 2023-12-10 20:42:13.576832+08
last_msg_receipt_time | 2023-12-10 20:42:13.576913+08
latest_end_lsn        | 0/9000148
latest_end_time       | 2023-12-10 20:31:12.534758+08
slot_name             | 
sender_host           | 172.172.1.41
sender_port           | 5432
conninfo              | user=repluser passfile=/var/lib/postgresql/.pgpass channel_binding=prefer dbname=replication host=172.172.1.41 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=#

7、修改流复制为同步复制，默认为异步复制

主库查看流复制的状态，async为异步复制
postgres=# select client_addr,state,sync_state from pg_stat_replication;
 client_addr  |   state   | sync_state 
--------------+-----------+------------
 172.172.1.40 | streaming | async
 172.172.1.42 | streaming | async
(2 rows)

配置参数，所有节点都配置如下参数
cat >> /pg/pg-master/data/postgresql.conf <<"EOF"
synchronous_commit='on'
synchronous_standby_names='*' 
EOF

cat >> /pg/pg-standby01/data/postgresql.conf <<"EOF"
synchronous_commit='on'
synchronous_standby_names='*' 
EOF

cat >> /pg/pg-standby02/data/postgresql.conf <<"EOF"
synchronous_commit='on'
synchronous_standby_names='*' 
EOF

重启所有节点的数据库服务
[root@openstack data]# docker restart pg-master 
pg-master
[root@openstack data]# docker restart pg-standby01
pg-standby01
[root@openstack data]# 
[root@openstack data]# docker restart pg-standby02
pg-standby02
[root@openstack data]# 

查看同步模式
postgres=# select client_addr,state,sync_state from pg_stat_replication;
 client_addr  |   state   | sync_state 
--------------+-----------+------------
 172.172.1.42 | streaming | sync
 172.172.1.40 | streaming | potential
(2 rows)

postgres=# 



如果改为异步复制，修改如下参数，然后重启数据库服务即可。
sed -i  's|synchronous_commit|#synchronous_commit|g'  /pg/pg-master/data/postgresql.conf
sed -i  's|synchronous_standby_names|#synchronous_standby_names|g'  /pg/pg-master/data/postgresql.conf

sed -i  's|synchronous_commit|#synchronous_commit|g'  /pg/pg-standby01/data/postgresql.conf
sed -i  's|synchronous_standby_names|#synchronous_standby_names|g'  /pg/pg-standby01/data/postgresql.conf

sed -i  's|synchronous_commit|#synchronous_commit|g'  /pg/pg-standby02/data/postgresql.conf
sed -i  's|synchronous_standby_names|#synchronous_standby_names|g'  /pg/pg-standby02/data/postgresql.conf

至此，PostgreSQL的流复制部署完毕。

参考：
https://www.modb.pro/db/78858

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 212,294评论 6赞 493
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 90,493评论 3赞 385
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 157,790评论 0赞 348
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 56,595评论 1赞 284
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 65,718评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,906评论 1赞 290
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,053评论 3赞 410
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,797评论 0赞 268
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,250评论 1赞 303
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,570评论 2赞 327
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,711评论 1赞 341
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,388评论 4赞 332
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,018评论 3赞 316
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,796评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,023评论 1赞 266
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 46,461评论 2赞 360
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 43,595评论 2赞 350