Backing up and restoring a PostgreSQL database across different versions

There are a few ways to backup and restore a PostgreSQL database. However, some methods will not work if your source and destination PostgreSQL instances run different database versions, especially if you want to include the data, which is what I wanted to do.

My situation was that I had a new "shell" database ready to roll, with the schema and migrations already executed and in place as the result of a automated deployment to a new server.

What worked

I found the easiest way to get this working was to not use the -Fc flag, or custom format. The following command will dump your data and schema information into a .sql file:

pg_dump -U mydatabaseuser -c -f filename.sql

Running the command may take a while depending on the size of your database.

When you're ready to restore your backup filename.sql file (once you've moved it to wherever it needs to go, like a new server), simply run:

psql -d mydatabasename -U mydatabaseuser -f filename.sql

Note that we don't need to use pg_restore here since our file is plain sql.