how to upgrade and migrate postgres dbs to new server machine?

Question

my production databases run on a centos 5.5 virtual machine. This server has 2 postgres 8.4 clusters installed on 2 different partitions; now my idea is to upgrade both db version and server os (centos 6) in order to set up a newer machine with also much more disk space. In this way i will be covered for many years. I read postgres documentation and pg_upgrade seems to be a very useful tool but,if i understood well, it can be used only on same machine and on same mounting point. So how can i get my goal? pgdumpall command? note that clusters' sizes are (at the moment) 10gb and 1gb ,but cluster 1 grows up quite fast.

thanks

Answer 1

The simplest approach is to simply use pg_dumpall -f cluster.dump, then restore with psql -f cluster.dump on the new host.

Slightly more sophisticated is to dump globals, then dump each DB separately:

pg_dumpall --globals-only > globals.sql
psql -qAt -c 'SELECT datname FROM pg_database WHERE NOT datistemplate;' -0 |\
  xargs -0 -i pg_dump -Fc -f "{}".pgbackup "{}"

Restore with something like:

psql -f globals.sql
for f in *.pgbackup; do
  pg_restore -f "$f" -C -d postgres
done

In all these cases you may need to run as the postgres user.

A 10GB dump is quite reasonable.

If you need to do a low downtime upgrade, there are a few more sophisticated options:

• Set up slony-I or Londiste to replicate changes from the old server to the new one; or

• Install the same version of PostgreSQL on the new server. Set up streaming replication. Fail over to the new server, then pg_upgrade to a current PostgreSQL version.

Answer 2

It sounds like the best thing is to do a full export/import of your database. 10GB is not a big database (relatively) and shouldn't be problematic. Export with pg_dump and import with psql.