1

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?

Improve this question
2
  • 2
    Can’t you create a new db and import the data and keep the old one around to make your first tests before you do the switchover? (I always think this is a great advantage of cloud services to have that kind of short term capacity)
    – eckes
    Dec 19, 2018 at 0:43
  • Just as a matter of interest, why do you want to downgrade?
    – Vérace
    Oct 31, 2019 at 12:01

3 Answers 3

Reset to default
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.

Improve this answer
0

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 ?

Improve this answer
1
  • A quite possible option: you have same proprietary software capable to deal only with 9.x .
    – peterh
    Sep 12, 2019 at 14:47
0

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

Improve this answer

Your Answer

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

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