Downgrading PostgreSQL 10 to 9.4

Question

Are there any particular precautions I should take when downgrading PG 10 to 9.4?

I have a database on Heroku (PG 9.4) which I'm going to upgrade to PG 10.6. In case something goes wrong, I want to be able to go back to 9.4.

Things I already tested:

1. create a backup of the already upgraded PG 10.6 DB (Heroku creates binary compressed dumps)
2. create a new PG 9.4 DB
3. upload the backup from step 1. to DB from step 2

The backup seems to be restored properly. What should focus on in particular to be sure this procedure is all right? Are there any other ways to downgrad PostgreSQL?

Answer 1

Downgrading PostgreSQL is not supported.

You may get lucky and it may work if you have a simple database. Your way is the best way; test the result as well as you can.

Answer 2

First, keep in mind Heroku may be based on Postgres, it's not Postgres. It's a closed source database and you have no idea what they changed.

The only way to do so would be to export all your data in SQL and to import it back to Postgres (using psql). If you only have access to binary compressed dump from Heroku, you may suppose they're using custom format from pg_dump (I haven't find any documentation about it) and so you'll need pg_restore to either transform it to SQL or to import it to your Postgres database.

Cheers,

Arkhena

PS : I'm curious, why downgrading it to 9.4 instead of importing it to a postgres 10 or 11 database ?

Answer 3

In case something goes wrong, I want to be able to go back to 9.4.

Shut down the database and take a backup of the whole VM. If anything goes wrong, restore that backup. That will put back the machine, all its configuration, the database software and all its configuration and the database itself.

Sure, there are other ways of trying to put things back the way they were but this, I would argue, is the most reliable (and, probably the, quickest).