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)

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s