Switchover/Failover and Session Migration

January 23, 2023

I have already covered switchover and failover. In the case of failover, the old primary is offline so there are no options for migrating clients from the old primary to the new primary. If there is a switchover, there are options for client migration. Assuming you are using streaming replication, only one server can accept writes. Therefore, for client migration, you can either:


  • Force all clients on the old primary to exit, then promote the new primary
  • Wait for all clients on the old primary to exit, then promote the new primary

If you choose force, it will disrupt applications; they must be designed to handle disconnections and potentially reconfigure their sessions, e.g., session variables, cursors, open transactions. If you choose wait, how do you handle clients that want to connect while you are waiting for the existing clients to disconnect? Minor upgrades, which require a restart of the database server, have similar issues.


The only clean solution is to use multi-master replication so new clients can connect to the new primary while waiting for old-primary clients to finish and disconnect. However, it is operationally expensive to support multi-master just to minimize switchover disruption.


(Article originally published in Bruce's personal blog - Friday, October 1, 2018, @ https://momjian.us/main/blogs/pgblog/2018.html#October_1_2018)

Share this

Relevant Blogs

How to run hierarchical queries in Oracle and PostgreSQL

.summary{ background:#f3f7f9; padding:20px; } SUMMARY: This article introduces hierarchical queries and shows the differences in their usage between Oracle and PostgreSQL. 1. Hierarchical query 2. Hierarchical query in Oracle 3. Displaying...
January 24, 2023

More Blogs