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
网友评论