Bruce Momjian Senior Database Architect Oct 5, 2018
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)
Bruce Momjian is a co-founder of the PostgreSQL Global Development Group, and has worked on PostgreSQL since 1996 as a committer and community leader. He is a frequent speaker and Postgres evangelist and travels worldwide appearing at conferences to help educate the community on the business value of Postgres advances and new technology enhancements. He is the author of PostgreSQL: Introduction and Concepts, published by Addison-Wesley.