how to upgrade and migrate postgres dbs to new server machine? 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
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 &gt; 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.