測試環境：

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
----------20000000postgres=# 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參數來強制歸檔，即使日志沒有滿，這樣就可以實現備庫自動的最終一致性。