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)