always归档模式下的备库归档

duqk1982

已于 2022-11-03 13:14:22 修改

阅读量284

点赞数

分类专栏： PostgreSQL 文章标签：数据库 postgresql

于 2022-11-03 10:17:41 首次发布

本文链接：https://blog.youkuaiyun.com/dqk1982/article/details/127664888

版权

PostgreSQL 专栏收录该内容

20 篇文章

订阅专栏

主库：192.168.13.22
备库：192.168.13.23

数据库版本：

PostgreSQL 11.15

测试前提：

两节点按照主备关系配置好流复制，并配置如下参数：

postgres=# select version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 11.15 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

postgres=# 

postgres=# show archive_mode;
 archive_mode 
--------------
 always
(1 row)

postgres=# show archive_command; 
   archive_command    
----------------------
 cp %p /data/pgarc/%f
(1 row)


postgres=# show synchronous_commit;
 synchronous_commit 
--------------------
 on
(1 row)

postgres=# show max_standby_streaming_delay;
 max_standby_streaming_delay 
-----------------------------
 -1
(1 row)

postgres=# show hot_standby_feedback;
 hot_standby_feedback 
----------------------
 off
(1 row)

postgres=#

结论：

在流复制的架构下，当archive_mode为on的时候，只是主库产生估归档。当archive_mode为always的时候，在主库和备库的归档路径下都会有wal归档日志。根据官方文档的解释：是备库每次接受流复制传过来的wal之后，备库会调用归档命令完成归档，这和备库是否应用流复制传过来的wal无关。

官方文档如下：
When continuous WAL archiving is used in a standby, there are two different scenarios: the WAL archive can be shared between the primary and the standby, or the standby can have its own WAL archive. When the standby has its own WAL archive, set archive_mode to always, and the standby will call the archive command for every WAL segment it receives, whether it’s by restoring from the archive or by streaming replication. The shared archive can be handled similarly, but the archive_command must test if the file being archived exists already, and if the existing file has identical contents. This requires more care in the archive_command, as it must be careful to not overwrite an existing file with different contents, but return success if the exactly same file is archived twice. And all that must be done free of race conditions, if two servers attempt to archive the same file at the same time.

测试过程：

主库

postgres=# create table t1(id int,name varchar(20));
CREATE TABLE
postgres=# create table t2(id int,name varchar(20)); 
CREATE TABLE
postgres=# insert into t1 values(1,'t1'),(2,'t1'),(3,'t1');
INSERT 0 3
postgres=# insert into t2 values(1,'t2'),(2,'t2'),(3,'t2'),(4,'t2');
INSERT 0 4
postgres=#

备库

postgres=# select * from t1;
 id | name 
----+------
  1 | t1
  2 | t1
  3 | t1
(3 rows)

postgres=# select * from t2;
 id | name 
----+------
  1 | t2
  2 | t2
  3 | t2
  4 | t2
(4 rows)

postgres=# begin;
BEGIN
postgres=# select * from t1;
 id | name 
----+------
  1 | t1
  2 | t1
  3 | t1
(3 rows)

postgres=#

主库

postgres=# drop table t1;
DROP TABLE
postgres=# delete from t2 where id=2;
DELETE 1
postgres=#

备库

postgres=# select * from t1;
 id | name 
----+------
  1 | t1
  2 | t1
  3 | t1
(3 rows)

postgres=# select * from t2;
 id | name 
----+------
  1 | t2
  2 | t2
  3 | t2
  4 | t2
(4 rows)

postgres=#

主库

postgres=# select pid,usename,client_addr,state,
postgres-# pg_wal_lsn_diff(pg_current_wal_lsn(),write_lsn) write_delay,
postgres-# pg_wal_lsn_diff(pg_current_wal_lsn(),flush_lsn) flush_delay,
postgres-# pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) replay_dely
postgres-# from pg_stat_replication;

 pid  | usename |  client_addr  |   state   | write_delay | flush_delay | replay_dely 
------+---------+---------------+-----------+-------------+-------------+-------------
 2159 | replica | 192.168.13.23 | streaming |           0 |           0 |       35536
(1 row)

postgres=#

—备库接受到主库传过来的wal，但是没有应用。

查看主库上的wal日志和归档信息：

[postgres@du102 ~]$ ls -trl /data/pgarc/
total 147460
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000003
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000004
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000005
-rw------- 1 postgres postgres      337 Apr  9  2022 000000010000000000000005.00000060.backup
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000006
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000007
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000008
-rw------- 1 postgres postgres 16777216 Nov  3 09:39 000000010000000000000009
[postgres@du102 ~]$ 

[postgres@du102 ~]$ ls -trl /data/pgdata/pg_wal/
total 147464
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000003
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000004
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000005
-rw------- 1 postgres postgres      337 Apr  9  2022 000000010000000000000005.00000060.backup
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000006
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000007
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000008
-rw------- 1 postgres postgres 16777216 Nov  3 09:39 000000010000000000000009
drwx------ 2 postgres postgres     4096 Nov  3 09:39 archive_status
-rw------- 1 postgres postgres 16777216 Nov  3 09:49 00000001000000000000000A
[postgres@du102 ~]$

—主库000000010000000000000009已经归档，待归档的wal为：00000001000000000000000A

备库
查看备库的wal日志和归档信息：

[postgres@du103 ~]$  ls -trl /data/pgarc/
total 65536
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000006
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000007
-rw------- 1 postgres postgres 16777216 Nov  3 09:30 000000010000000000000008
-rw------- 1 postgres postgres 16777216 Nov  3 09:39 000000010000000000000009
[postgres@du103 ~]$  ls -trl /data/pgdata/pg_wal/
total 98308
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000005
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000006
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000007
-rw------- 1 postgres postgres 16777216 Nov  3 09:30 000000010000000000000008
-rw------- 1 postgres postgres 16777216 Nov  3 09:39 000000010000000000000009
drwx------ 2 postgres postgres     4096 Nov  3 09:39 archive_status
-rw------- 1 postgres postgres 16777216 Nov  3 09:49 00000001000000000000000A
[postgres@du103 ~]$

主库
—在主库模拟业务访问，并切换日志，产生wal归档。

postgres=# delete from t2;
DELETE 3
postgres=# insert into t2 select generate_series(1,1000),'test';
INSERT 0 1000
postgres=# select count(*) from t2;
 count 
-------
  1000
(1 row)

postgres=# delete from t2;
DELETE 1000
postgres=# insert into t2 select generate_series(1,5000),'test';
INSERT 0 5000
postgres=# select count(*) from t2;             
 count 
-------
  5000
(1 row)

postgres=# delete from t2;
DELETE 5000
postgres=# insert into t2 select generate_series(1,10000),'test';
INSERT 0 10000
postgres=# delete from t2;
DELETE 10000
postgres=# insert into t2 select generate_series(1,10000),'test';
INSERT 0 10000
postgres=# checkpoint;
CHECKPOINT
postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/A3398E0
(1 row)

postgres=#

[postgres@du102 ~]$ ls -trl /data/pgdata/pg_wal/
total 163848
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000003
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000004
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000005
-rw------- 1 postgres postgres      337 Apr  9  2022 000000010000000000000005.00000060.backup
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000006
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000007
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000008
-rw------- 1 postgres postgres 16777216 Nov  3 09:39 000000010000000000000009
-rw------- 1 postgres postgres 16777216 Nov  3 09:53 00000001000000000000000A
drwx------ 2 postgres postgres     4096 Nov  3 09:53 archive_status
-rw------- 1 postgres postgres 16777216 Nov  3 09:53 00000001000000000000000B
[postgres@du102 ~]$ 

[postgres@du102 ~]$ ls -trl /data/pgarc/        
total 163844
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000003
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000004
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000005
-rw------- 1 postgres postgres      337 Apr  9  2022 000000010000000000000005.00000060.backup
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000006
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000007
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000008
-rw------- 1 postgres postgres 16777216 Nov  3 09:39 000000010000000000000009
-rw------- 1 postgres postgres 16777216 Nov  3 09:53 00000001000000000000000A
[postgres@du102 ~]$

—主库确认wal：00000001000000000000000A已经归档，时间为 09:53

备库

postgres=# select * from t1;
 id | name 
----+------
  1 | t1
  2 | t1
  3 | t1
(3 rows)

postgres=# select * from t2;
 id | name 
----+------
  1 | t2
  2 | t2
  3 | t2
  4 | t2
(4 rows)

postgres=#

–确认备库数据没有发生变化

[postgres@du103 ~]$  ls -trl /data/pgdata/pg_wal/
total 114692
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000005
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000006
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000007
-rw------- 1 postgres postgres 16777216 Nov  3 09:30 000000010000000000000008
-rw------- 1 postgres postgres 16777216 Nov  3 09:39 000000010000000000000009
-rw------- 1 postgres postgres 16777216 Nov  3 09:53 00000001000000000000000A
drwx------ 2 postgres postgres     4096 Nov  3 09:53 archive_status
-rw------- 1 postgres postgres 16777216 Nov  3 09:53 00000001000000000000000B
[postgres@du103 ~]$ ls -trl /data/pgarc/         
total 81920
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000006
-rw------- 1 postgres postgres 16777216 Apr  9  2022 000000010000000000000007
-rw------- 1 postgres postgres 16777216 Nov  3 09:30 000000010000000000000008
-rw------- 1 postgres postgres 16777216 Nov  3 09:39 000000010000000000000009
-rw------- 1 postgres postgres 16777216 Nov  3 09:53 00000001000000000000000A
[postgres@du103 ~]$

—备库确认wal：00000001000000000000000A已经归档，时间为 09:53 ，和主库相同。

主库

postgres=# select pid,usename,client_addr,state,
pg_wal_lsn_diff(pg_current_wal_lsn(),write_lsn) write_delay,
pg_wal_lsn_diff(pg_current_wal_lsn(),flush_lsn) flush_delay,
pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) replay_dely
from pg_stat_replication;
 pid  | usename |  client_addr  |   state   | write_delay | flush_delay | replay_dely 
------+---------+---------------+-----------+-------------+-------------+-------------
 2159 | replica | 192.168.13.23 | streaming |           0 |           0 |    16152880
(1 row)

postgres=#