Обсуждение: Postgres switchover and switchback

Hi, folks;

I’m a former Oracle database administrator of many years who is new to PostgreSQL.  I’m in the process of testing PG v12.7 hot streaming standby’s in async mode, and specifically in the areas of switchover and switchback. 

I have set up test primary’s and created standby’s successfully with pg_basebackup. I can switch over/switch roles (standby -> primary, primary -> standby) successfully one time.  When I switch roles a second time, the standby becomes primary just fine but the primary to standby fails to open properly.  Logfile messages for both primary and standby are attached as well as pg_wal directory lists. 

I shut down the primary first and then promote the standby.  The only action I take on the standby side is to set the standby.signal file in $PGDATA.  (Well almost.  Initially, I do have to have an empty 00000000.history file in the archive locations or the standby’s fail to start.) 

My archive directories are independent from each other and the archive command is the given test … cp -p example in the docs.  Archive is ‘on’ on both sides.  

Are there manual steps I’m should be completing before starting up new standby?    So far, I’ve found the switching of roles to be less than guaranteed.

I would appreciate review of the attached logs to help pinpoint the issue.   Inst1 is the initial primary, and inst2 is the initial standby.

Many thanks! 
David

In SQL Server, at least, you must be in synchronous replication mode before doing a (manual or automatic) non-crash failover, to guarantee that all the transaction log data has reached the replica before the switchover.