We have a PostgreSQL 9.3 database in a production environment and a 9.6 database in a test environment.
I've never used PostgreSQL before and am trying to use the pg_dump and pg_restore tools to back up the database from 9.3 and restore it to 9.6.

I seem to be able to run the backup tool from pgAdmin III 1.18 but when I come to restore the backup file in pgAdmin 3.1 it fails. On backing up, I seem to be able to select a file extension too simply by typing it in. I've tried the .dump extension and saving the backup as a tar file but these seem to fail on restore.
There seems to be a number of interchangeable words to describe the backup types which muddies the waters somewhat. I've used the script/dump/custom file format rather than the archive/directory format and am attempting to run the backup/restore via the pgAdmin GUI. It sounds like I can restore a backup taken from a 9.3 database and restore it to a 9.6 database as they are the same major version however, on opening the restore GUI and trying to find the file I have to select "All Files" from the drop-down menu at the bottom as no files are found initially. Selecting any of the other options from this drop-down (backup/sql/patch) also reveals nothing.

Looking at the command line options is giving me a headache as the syntax referencing seems to be all over the place.
PostgreSQL 9.3.24 Documentation - pg_dump
PostgreSQL 9.6.10 Documentation - pg_restore

Like I said, I've never used this software before so the learning curve is steep and I'd like to be making some progress this century : )

Can anyone give me the lowdown on how to do this please?

Thanks in advance!

  • What is the specific error or problem that you receive? – Michael Hampton Sep 14 '18 at 13:14

Your Answer

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

Browse other questions tagged or ask your own question.