Postgres – moving a db from a newer version


You have more than one DB and one of them is a newer version of POSTGRES. so when you try and PG_DUMP and PG_RESTORE from the new one to the older one it doesn’t work.

You will get “pg_restore: [archiver] unsupported version (1.12) in file header”


so here’s how to do it:

  •  make a new db.
  • restore data from your existing db on your older version (this is so you get all the functions) PgAdmin makes this easy
  • pg dump your new db *** using plain format***
  • drop all your tables:  best way is
'DROP TABLE '||c.relname ||' CASCADE;'
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)

then copy and paste the result…

(above code found at )

  • use psql -f dumpfile.sql YourNewDB

and voila you have a very close approximation to what you had before. Note you may still have issues with encrypted fields and/or bytea fields (which are probably the same)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s