'Pg_dump from newer version (9.3.9) to pg_restore with an older version (9.1.13)

I need to restore data from my newer postgres database to an older on another machine. I know pg_restore doesn't work with dumps from newer versions, but somehow I've got to get that data.

Acording to this post there is a way but it involves manually modifying the rows that are different across versions, but there are no informations online what these differences are.

What is the possible fix for such problem? Is there any way to import data from newer to older postgres?

psql db < dump | head yields:

ERROR:  relation "cities" does not exist
invalid command \. 
ERROR:  syntax error at or near "1" 
LINE 1: 1 Kraków 0 0 2013-10-30 08:39:45.232006 2013-10-30 08:39:4....

And thousand more of these.

postgresqlpsqlpg-dump


Solution 1:[1]

We don't have any option to take dump (in higher version of Postgres) in custom, directory or tar format and to restore back (in lower version of Postgres).

But if we need to copy/move data from higher version of Postgres to lower version of Postgres we can do so by taking dump in plain format like below

pg_dump -h <db-ip> -p <db-port> -U <db-user> -F p -f <backup-path> <db-name>

It will dump backup in plain sql queries that can be retored to any database (even in other than Postgres)

We can restore above plain format database backup in any version of Postgres as below

psql --host <db-ip> --port <db-port> --username <db-user> --dbname <db-name> -f <backup-path>

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Anil Agrawal

Related Questions