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.
The error is harmless but to get rid of it, I think you need to break this restore into two commands, as in:
dropdb -U postgres mydb && \
pg_restore --create --dbname=postgres --username=postgres pg_backup.dump
The --clean
option in pg_restore doesn't look like much but actually raises non-trivial problems.
For versions up to 9.1
The combination of --create
and --clean
in pg_restore options used to be an error in older PG versions (up to 9.1). There is indeed some contradiction between (quoting the 9.1 manpage):
--clean
Clean (drop) database objects before recreating them
and
--create
Create the database before restoring into it.
Because what's the point of cleaning inside a brand-new database?
Starting from version 9.2
The combination is now accepted and the doc says this (quoting the 9.3 manpage):
--clean
Clean (drop) database objects before recreating them. (This might generate some harmless error messages, if any objects were not present in the destination database.)
--create
Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before connecting to it.
Now having both together leads to this kind of sequence during your restore:
DROP DATABASE mydb;
...
CREATE DATABASE mydb WITH TEMPLATE = template0... [other options]
...
CREATE SCHEMA public;
...
CREATE TABLE...
There is no DROP
for each individual object, only a DROP DATABASE
at the beginning. If not using --create
this would be the opposite.
Anyway this sequence raises the error of public
schema already existing because creating mydb
from template0
has imported it already (which is normal, it's the point of a template database).
I'm not sure why this case is not handled automatically by pg_restore
. Maybe this would cause undesirable side-effects when an admin decides to customize template0
and/or change the purpose of public
, even if we're not supposed to do that.
Best Answer
Not really a solution but a workaround that worked is to restore through a csv file.