CODEWITHSUNDEEP
postgresql
Aaron Kreider
Aaron Kreider

Reputation: 1819

Restoring postgreSQL 9.1 backup to 8.1

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?

Upvotes: 0

Views: 666

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324275

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.

Upvotes: 1

Related Questions