PostgreSQL基于归档日志的持续恢复测试

测试环境：

os: linux 

PG: 17.4

src ip: 192.168.100.51

dst ip: 192.168.100.138

src: PGDATA=/home/postgres174/pgdata

dst: PGDATA=/data/174/pgdata_standby

归档路径： 192.168.100.138 /data/174/archivedir

测试流程：

1. 主库(51)设置archive_command参数，将归档日志放到138的/data174/archivedir目录中

archive_command= 'sshpass -p 123456 scp -o StrictHostKeyChecking=no %p postgres174@192.168.100.138:/data/174/archivedir/%f'

2. 目标机器（138）制作基础备份

pg_basebackup -h 192.168.100.51 -U postgres -p 15432 -D pgdata_standby/ -P -R
Password:
16498542/16498542 kB (100%), 2/2 tablespaces

3. 修改pgdata_standby的postgresql.auto.conf

注释掉: primary_conninfo和archive_command两个参数

增加restore_command和archive_clean_up参数

restore_command='cp /data/174/archivedir/%f %p'
archive_cleanup_command = 'pg_archivecleanup /data/174/archivedir %r'

4. 启动备库

5. 测试

51
postgres=# select count(*) from test123;
  count
----------
 20000000

postgres=# delete from test123;
DELETE 20000000

备库：

postgres=# select count(*) from test123;
WARNING:  database "postgres" has a collation version mismatch
DETAIL:  The database was created using collation version 2.28, but the operating system provides version 2.17.
HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
WARNING:  database "postgres" has a collation version mismatch
DETAIL:  The database was created using collation version 2.28, but the operating system provides version 2.17.
HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
  count
----------
 20000000
(1 row)

备库上依然还是2000w行记录，是因为包含commit日志的记录还没有归档，所以并没有同步过来，我们在51上手动切换下日志，就可以了

51
postgres=# select pg_switch_wal();
 pg_switch_wal
---------------
 CEB/F92F17B0
(1 row)


138上查询
postgres=# select count(*) from test123;
 count
-------
     0
(1 row)


注释：手动切换只是测试为目的，实际可以通过设置archive_timeout参数来强制归档，即使日志没有满，这样就可以实现备库自动的最终一致性。