Restoring postgreSQL 9.1 backup to 8.1

0
votes

I want to restore a postgreSQL backup from version 9.1 (used locally) to an earlier version: 8.1 (on the server)

Can I do this without upgrading the server?

I've tried using pg_dump to create a SQL dump.

When I try to restore it, I get an error message: ERROR: parameter "standard_conforming_strings" cannot be changed

and ERROR: syntax error at or near "(" at character 365 LINE 17: geom900913 geometry(MultiPolygon,900913),

I have tried setting standard_conforming_strings to off in the 9.1 postgres.conf, but I still get this message.

Did postgres 9 change the configuration for geometry columns?

1
postgresql
Maybe I need to setup the postgreSQL 8.1 database so that it uses postGIS? That would explain why it doesn't recognize the geometry column. If so, how do I do that? - Aaron Kreider
If at all it would be a downgrade not an upgrade. And you shouldn't be using 8.1 anyway. But the answer to your question is a clear: no. You can't use a dump generated in a higher version in a lower version (at least not with manually editing the generated SQL file). - a_horse_with_no_name
I'm prepared to manually edit SQL files. - Aaron Kreider

1 Answers

1
votes

Downgrading is generally challenging.

In particular, if your new server runs PostGIS 2, and your old one runs 1.x, you won't have much chance of restoring the backup to the old server. PostGIS has new data types, new representations of data, different functions and catalog tables, and more. I strongly suspect you'll have to export your data to shapefiles, then reload them using shp2pg on the old version.

If they're both on the same PostGIS (1.x or 2.x on both) you may be able to restore from 9.1 down to 8.1 using pg_dump -Fc to take the backup, then pg_restore to restore it. That'll detect the version of the destination server and deal with issues like incompatible parameters. You may need to use 8.1's pg_dump and pg_restore, but try with 9.1's first.

8.1 is outdated, unsupported, and is not receiving bug or security fixes. Rather than downgrading, please consider upgrading your 8.1 server to 9.1 or newer using the upgrade process described in the documentation. Downgrading will be a lot of pain for no real benefit and then you'll just have to upgrade again soon.