I have to upgrade from PostgreSQL 9.2 to 9.6. Following are the challenges that I am facing:
Since we have streaming replication setup, and PostgreSQL doesn't support lower to upper version upgrade when in streaming replication mode, when I upgrade the master I have to rebuild the slaves and that takes 3 hours. I don't have that time to spare. At any given time we need one slave and one master available. Is there any other way to upgrade without having to rebuild the slaves, using streaming replication?
To build logical replication we thought of using slony but slony has some limitations in that it does not automatically replicate:
- Changes to large objects (BLOBS)
- Changes made by DDL commands
- Changes to users and roles
...and our application has continuous create commands. So we cannot use slony.
Please suggest something to avoid slave rebuild and to do upgrade with minimal downtime and one master and one slave ready.