How I have to do to migrate Postgresql 8.2 to 9.0?

Hi all...

I'm here to collect more info about this migration I must do.

I have a database on Postgresql 8.2 on FreeBSD (old server).

I will have to migrate to a Postgresql 9.0 on a CentOS 5.6 (new server)

Until now I'm planning to do this:

On the old server using PgAdmin:

1) dump PostgreSQL database using Backup option in context menu;

2) dump CorporativeDatabase;

3) dump Template01;

4) dump Template02;

5) copy log files;

6) copy pg_hba.conf and postgresql.conf;

With all that files change to the new server:

1) Install Postgresql 9 by compiling;

2) initdb with locale parameter - the old server is in Latin1. I will have to maintain this compatibility with the legacy software;

3) replace pg_hba.conf and postgresql.conf;

on terminal using postgres user logon

4) Restore postgres dump backup;

Using PgAdmin

5) restore Template01;

6) restore Template02;

7) restore CorporativeDatabase;

Lots of prey...

So... I will thankful if have more tips of how proceed with this,
like sugestions of what command I will do on the step 4 (restore postgres dump backup)
Or how could logs can help me on this entire proceding...
Something that I'm missing...

thank you guys in advanced.

see ya more times now

This message was edited 1 time. Last update was at 21 Jun 2011 02:53:31

If database size is small then, you can do:

If its a large database and you don't have long downtime window, then you might like to consider using slony/bucardo replication for upgrade.

hi all.
thanks for this tip

Even I just look this thread after the migration, I follow this principle... I think.

Here I paste a log not so precise for the steps I took.

*new_server(centos 5.6) and old_server(freebsd) are fictional names, just to say.
*pgsql I use as PostgreSQL diminutive...

accessing new_server as root user (by sudo) to install postgresql 9

installing repository wich contains pgsql9

installing pgsql9 repository

install pgsql9 packages

stopping pgsql9 service to update pgsql9 settings.

change to postgres user

initialize database at /var/lib/pgsql/9.0/data/ with language and enconding as LATIN1 - centos 5.6 include support to utf-8 and iso88591(latin1)

back to root user(by sudo)

include postgresql service on boot

rebooting new_server.

accessing old_server.

backup folow files: pg_hba.conf, postgresql.conf

no log files. I see log settings are enabled but not to save on file, just basic redirect to stderr. So I'm not sure is really this, so I quit to looking for this files...

backup database from old_server

shutdown pgsql8.2 service

remove 2 line from /etc/rc.conf which start pgsql8.2 on boot

back to the new_server

setting pg_hba.conf based on pg_hba.conf backup file from the old_server to pgsql9 only accepts connections on internal lan/net (not sure the term...)

setting postgresql.conf: include ip listen, set maximum connections, log enable (already per default), autovacuum enable

Load backup of database from file dump_18062011.out

Optimize database

End of migration, applications work until now...

thanks all. hope its help. see ya