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 <pdo...@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
