Postgres – moving a db from a newer version

Scenrio:

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

SELECT

'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)

ORDER BY 1;

then copy and paste the result…

(above code found at http://antydba.blogspot.com/2009/10/how-to-drop-all-tables-from-database.html )

• 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)