You could also look into upgrading via pg_upgrade, if you don't want to go through the dump/restore cycle. Even in that case if you can do a test of pg_dump (one for schema, one for data) and make sure it loads into the new db without any issues. 8.3 -> 9.0 is pretty harmless (you might want to think about 9.1 instead btw). Oh, it's worth mentioning, you should really skim through the release notes and make sure nothing in the incompatabilities applys to you; here's the links to the docs: http://www.postgresql.org/docs/9.1/interactive/release-9-0.html#AEN108545 http://www.postgresql.org/docs/9.1/interactive/release-8-4.html#AEN111313 http://www.postgresql.org/docs/9.1/interactive/release-8-3.html#AEN114593
Robert Treat conjecture: xzilla.net consulting: omniti.com On Thu, Nov 17, 2011 at 8:14 PM, David Morton <davidmor...@xtra.co.nz> wrote: > 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 > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
