Almost zero downtime major release upgrades with logical replication
Based on a similar mechanism with aivendbmigrate I would like to have major release upgrade mechanism based on logical replication.
Steps:
- create an empty target instance with new major release
- create a full logical replication setup (like aivendbmigrate does) 2.1. inform users about limitations (lobs, schema changes during that period etc.) 2.2. inform users about testing and rollback capabilities
- perform the usual migrate thing with transferring everything and switch to a CDC state until next steps are defined
- provide specific cutover scenarios 4.1. DNS switch if possible? 4.2. some pgbouncer magic to prevent forced reconnections of clients (maybe some prerequisitis necessary)
- offering cutover in place or just to new instance
Benefits are
- in worst case downtime of the major release upgrade is just a "reconnect" away
- testing capabilities during the CDC phase to verify that everything is fine after the major upgrade
- if tests not successful a rollback is even not necessary
- after cutover the old DB could be conserved for some time to get back to at least the last state before the cutover as kind of a disaster recovery case.
The basics are IMHO already there in aivendbmigrate and even with the limitations of logical replication (which will be better with the next versions, and are better with pglogical) this would be a huge differentiator to other vendors, as this is best practices on premises but almost no cloud vendor offers similar solutions.
AdminDirk Krautschick
(Senior Solutions Architect, Aiven.io)
shared this idea
-
Adiyaka Niastya Ihsan Maulana commented
As a DevOps Engineer,
I want to be able to do in-place major version upgrade for Postgresql while maintaining ability to rollback to previous version in case there's apps error with the minimal data loss.
So that I can reduce effort for major version upgrade, which currently we need to fork and do logical replication (with other necessary stuff to do) for keeping the data in-sync with the master if we want the ability to rollback.
In addition, we have done the fork and logical replication with ability to rollback to old version while keeping the data in-sync between the forked and master database and it is doable. However since we are using Aiven Managed Database, we'd like to reduce those kind of work from our operational side.