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_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!