以正确的方式设置 PostgreSQL 故障转移和故障恢复！

archive_mode = on
wal_level = hot_standby
max_wal_senders = 10
hot_standby = on
wal_log_hints = on
archive_command = 'cp %p $HOME/pg/archive/%f'
restore_command = 'cp $HOME/pg/archive/%f %p'

$ pg_ctl promote -D $HOME/pg/rep
waiting for server to promote.... done
server promoted

postgres=# insert into t1 values (7,8);
INSERT 0 1
postgres=# select * from t1;
a | b 
---+---
1 | 2
3 | 4
5 | 6
7 | 8
(4 rows)

postgres=# select * from t1;
a | b 
---+---
1 | 2
3 | 4
5 | 6
(3 rows)

$ pg_ctl -D $HOME/pg/data stop
waiting for server to shut down.... done
server stopped

recovery_target_timeline = 'latest'
primary_conninfo = 'user=postgres host=localhost port=5433'
primary_slot_name = 'main'
port = 5432

$ psql -d postgres -U postgres -c "select * from pg_create_physical_replication_slot('main');" -p 5433
slot_name | lsn 
-----------+-----
main      | 
(1 row)
$ psql -d postgres -U postgres -p 5433 -c "select * from pg_replication_slots;" -x
-[ RECORD 1 ]-------+---------
slot_name | main
plugin | 
slot_type | physical
datoid | 
database | 
temporary | f
active | f
active_pid | 
xmin | 
catalog_xmin | 
restart_lsn | 
confirmed_flush_lsn | 
wal_status | 
safe_wal_size | 
two_phase | f

$ touch $HOME/pg/data/standby.signal

cp -n $HOME/pg/archive/* $HOME/pg/data/pg_wal/

$ pg_rewind --target-pgdata=data --source-server="port=5433 user=postgres dbname=postgres"
pg_rewind: servers diverged at WAL location 0/3000000 on timeline 1
pg_rewind: rewinding from last common checkpoint at 0/2000060 on timeline 1
pg_rewind: Done!

primary_slot_name = 'main'
recovery_target_timeline = 'latest'
primary_conninfo = 'user=postgres host=localhost port=5433 sslmode=prefer sslcompression=0 gssencmode=disable target_session_attrs=any'
port = 5432
postgresql.auto.conf文件添加以下内容
primary_conninfo = 'user=postgres host=localhost port=5433 sslmode=prefer sslcompression=0 gssencmode=disable target_session_attrs=any'
port = 5432

$ pg_ctl -D $HOME/pg/data -l data.log start
waiting for server to start.... done
server started

$ psql -d postgres -c "select * from t1;"
a | b 
---+---
1 | 2
3 | 4
5 | 6
(3 rows)

$ pg_ctl -D $HOME/pg/rep stop

$ pg_ctl -D $HOME/pg/data promote

$ touch $HOME/pg/rep/standby.signal

$ pg_ctl -D $HOME/pg/rep -l rep.log start

$ psql -U postgres -c "select * from pg_create_physical_replication_slot('replica');" -p 5432
$ psql -U postgres -c "select * from pg_drop_replication_slot('main');" -p 5433

$ psql -d postgres -c "insert into t1 values (0,0);" -p 5432
INSERT 0 1
$ psql -d postgres -c "select * from t1;" -p 5433
a | b  
---+----
1 |  2
3 |  4
5 |  6
9 | 10
0 |  0
(5 rows)

PGCCC，公众号：PostgreSQL考试认证中心PostgreSQL认证考试，2023春节特惠，开始啦！