8

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. – Craig Ringer Aug 29 '17 at 7:45
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.

  • 2
    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. – dezso Aug 29 '17 at 8:40
  • But taking dump doesn't need downtime. – Lohit Gupta Aug 29 '17 at 9:11
  • 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. – dezso Aug 29 '17 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 '17 at 17:02
  • 1
    Step 2 won't work. The WAL format is not compatible between major versions, hence the need for pg_upgrade. – dezso Aug 29 '17 at 19:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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