Postgresql – pg_dump custom format between postgres versions

backuppostgresqlrestore

I was wondering how stable the custom pg_dump format is between Postgres versions.
Can I for example restore a custom format dump created with Postgres 8.x in postgres 9.x?

Background:

I am building a virtual appliance which contains all configuration in a PostgreSQL DB. I have to decide whether I use the SQL format or the custom format for the backup. As the appliance evolves over time (couple of years) I will use newer PostgreSQL versions and still need to be able to restore old backups without a lot of trouble.

Best Answer

In my experience, you can use pg_dump without formatting to backup and restore databases between versions without any issues. This is when using pg_dump to simply generate a SQL file.

example 

pg_dump mydb > db.sql

However, when using -Fc as an option

$ pg_dump -Fc mydb > db.dump

You can only restore the dump file with a version of PostgreSQL that's higher i.e. more recent. So if you create the dump file with PostgreSQL 8.1, you can only restore that same file with pg_dump from a 8.1 or more recent version of PostgreSQL.

There may be other elements that must exist on the target server such as plpgsql or postgis, if those same elements exist on the source server.

Here is the documentation from Postgres on pg_dump.

Here is the relative paragraph

Because pg_dump is used to transfer data to newer versions of PostgreSQL, the output of pg_dump can be expected to load into PostgreSQL server versions newer than pg_dump's version. pg_dump can also dump from PostgreSQL servers older than its own version. (Currently, servers back to version 7.0 are supported.) However, pg_dump cannot dump from PostgreSQL servers newer than its own major version; it will refuse to even try, rather than risk making an invalid dump. Also, it is not guaranteed that pg_dump's output can be loaded into a server of an older major version — not even if the dump was taken from a server of that version. Loading a dump file into an older server may require manual editing of the dump file to remove syntax not understood by the older server.

Related Question