The pg_upgrade
utility is the preferred way to migrate data between versions of PostrgreSQL. It is typically used when you are installing a new version of PostgreSQL side-by-side with an old one. You run pg_upgrade
from the new installation, point it at the old one, and let it move the data over. pg_upgrade
takes advantage of consistencies between point revisions of the PostgreSQL data format to speed up the migration process, so it is not only the most convenient way to migrate but typically the fastest.
Another way to perform a migration is to use PostgreSQL replication (e.g., Slony). Here you use the new version of PostgreSQL to create a standby server for a server running the older version. This method results in the least amount of downtime, because the replication happens in the background. Once the replication is finished, all you need to do is redirect all connections from the old server to the new one. However, if you’re not using replication in the first place, this might involve jumping through a few too many hoops.
The pg_dumpall utility exports the entire contents of a PostgreSQL server to one or more SQL dump files. The resulting script or archive file can then be executed on or imported to the new server. It’s also possible to set up the old and new servers side-by-side, and use pg_dumpall
to “pipe” the dump operation between them. The biggest downside of using pg_dumpall
is that it’s slow, but it can be used as an absolute fallback method.