Thread: 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

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 <davidmorton@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 <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
>
>
>