I've performed a very similar upgrade including postgis upgrade at the same time, we used the following command examples ... also put some simple scripting together to dump multiple databases in parallel as downtime was critical:
Dump database data: pg_dump -Fc database --compress=1 > /mnt/dumps/database.dump
Dump global data: pg_dumpall -g > /mnt/dumps/globals.sql
Parse the global file and create a script to create new directory structure for table spaces etc (also changed paths to new mount points here)
Run the global sql script: psql -f /mnt/dumps/globals.sql postgres
Restore databases without GIS functionality: pg_restore -j 2 -C -d postgres /mnt/dumps/database.dump
Restore databases with GIS functionality (upgrade of postgis version requires this): sh /tmp/postgis_restore.pl /usr/share/postgresql/contrib/postgis-1.5/postgis.sql database_user /mnt/dumps/gisdatabase.dump -E=UTF8
Those were the basic essential steps ... there are other supporting things we did around the outside to streamline the transition, it all worked perfectly on the day.
Best advise is that if its more than a scratch environment, test test test !!
From: Pedro Doria Meunier <pdoria@netmadeira.com>
To: pgsql-general@postgresql.org
Sent: Friday, 18 November 2011 12:40 AM
Subject: [GENERAL] upgrading from 8.3 to 9.0
Hi,
I'm on the verge of upgrading a server (Fedora 8 ehehe) running postgresql 8.3
It also has postgis 1.3 installed.
Thinking of using pgadmin3 to perform the backup and then restore it after
I've upgraded the server to fedora 15/16 and thus upgrading postgresql to 9.0.
I seem to remember problems with restoring from a pgadmin's .backup file in the
past... :S
Any pitfalls I should be aware of?
Btw: it's a reasonably large DB with 30mil+ rows...
Already thankful for any insight,
--
Pedro Doria Meunier
GSM: +351 91 581 88 23
Skype: pdoriam
