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?


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.

1 Answer 1


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.


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.

  • Thanks, I think for my purposes the custom format will do nicely. I read through the doc somehow I missed that paragraph before...
    – circus
    Commented Oct 21, 2013 at 8:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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