Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.


share|improve this question
up vote 2 down vote accepted

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

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.

share|improve this answer
This answer is much better than mine – Andrew Brennan Mar 21 '14 at 14:13
thanks..i'll do some migration simulation to have an evaluation of downtime and to be sure that all works. I'll try also the other methods, also because i'm interested eventually in setting up replication on new machine. luckily we have 2 data centers (one is backup) so i can have exactly the same enviroment to do all the tests i want :) – SUPERALEX Mar 21 '14 at 16:57

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.