I have to upgrade from PostgreSQL 9.2 to 9.6. Following are the challenges that I am facing:

  1. 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?

  2. 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.

  • 3
    The same issues that stop you using Slony will stop you using other tools like Bucardo, Londiste, pglogical, etc. You're going to need to adapt the application, so it doesn't do the continuous creates. And yes, LOBs will be a problem. Aug 29, 2017 at 7:45

1 Answer 1


Upgrading a database will need downtime on your servers. To minimize the downtime, i would suggest you install the newer version in a new server, create a replication setup on that and dump all data from old instance here, and change server pointing so that application points to new server to connect to database.

  • 3
    Well, we recently upgraded a 9 TB database from 9.3 to 9.6, using pg_upgrade. It took minutes (on master and slave combined), while taking a dump alone needs more than 3 hours. Aug 29, 2017 at 8:40
  • But taking dump doesn't need downtime. Aug 29, 2017 at 9:11
  • 1
    Absolutely, what I meant (and forgot to add) was that restoring the backup takes more or less the same time as taking it, meaning a few ours downtime in our case. Aug 29, 2017 at 10:29
  • Thanks for the suggestion , so what i was doing is upgrading master and rebuilding slave , is this the right procedure
    – Prabhat
    Aug 29, 2017 at 17:02
  • 1
    Step 2 won't work. The WAL format is not compatible between major versions, hence the need for pg_upgrade. Aug 29, 2017 at 19:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.