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
Best Answer
The simplest approach is to simply use
pg_dumpall -f cluster.dump
, then restore withpsql -f cluster.dump
on the new host.Slightly more sophisticated is to dump globals, then dump each DB separately:
Restore with something like:
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.