June 19th, 2013, 11:08 AM
Postgres upgrade issues
I'm trying to switch from Postgres 8.3 to 9.2 but am encountering some issues. Postgres is installed with my software on Windows machines, because it is automated backdoor solutions requiring manual steps are not what I need.
If I install 9.2 instead of 8.3 (so only 9.2 exists on the computer), then it appears to work as normal. Until I try to restore an older backup of an 8.3 database. The 9.2 Pg_restore is failing when restoring an 8.3 backup. What could be wrong? I assume it is an incompatibility with either the dump or restore that can be overcome by the right options.
Here is what is being used to backup and restore (also using the password file pgpass.conf):
pg_dump.exe -U myuser -h localhost -p 5432 -F c -b -v -i -f backupLocation DBName
pg_restore.exe -h localhost -p 5432 -U myuser -v -d DBName backupLocation
How to upgrade a computer that already has 8.3, so 8.3 is replaced by 9.2? I can install both, but am having trouble moving everything in the 8.3 DB to the 9.2 DB and still use the standard 5432 port.
The Postgres installer seems to pick the next port depending on how many versions are installed. When I try to set a specific port with the installer flags, it doesn't seem to care what I choose (maybe this is hidden somewhere and not cleared when I uninstall?).
I appreciate any advice that can be given, as I am not yet a Postgres pro and am banging my head against a wall.
Surely someone must have successfully upgraded Postgres in a smooth and automated manner before.