POSTGRESQL 9.1 backup and restore to 8.4

Question

I'm trying to upload a database, which I developed locally, into our development server.

I installed PostgreSQL 9.1 on my machine and the development server uses 8.4.

When trying to restore the database to 8.4 using the dump file created by 9.1 I get the error:

pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or near "EXTENSION"
LINE 1: CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalo...

and a quick research tells me that "EXTENSION" doesn't exist prior to 9.1.

I'm not really sure I should look for an option in pg_dump that ignores "extensions" as the database I'm trying to upload relies on the PostGIS extension for most of data.

While upgrading the development server and installing PostGIS in the dev server is an option, I'd like to know of a different route, one wherein I do not need to edit anything on the server while maintaining the functions of the database I developed.

Of course other workarounds are welcomed, my sole aim in uploading my database to the server is to reduce the amount of reconfiguration I have to do on my project whenever I need to deploy something for our team.

Answer 1

This is an old post but I had the same problem today and there is a better more reliable way of loading a PG 9.1 db into a PG 8.4 server. The method proposed by Craig will fail on the target machine because the PLPGSQL language will not be created.

pg_dump -Upostgres -hlocalhost > 9.1.db

replace this line

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

with this line

CREATE LANGUAGE plpgsql;

delete this line or comment it out

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

you can use sed to make the changes

Often it is not possible to upgrade an 8.4 server because of application dependencies.

Answer 2

Backporting databases can be painful and difficult.

You could try using 8.4's pg_dump to dump it, but it'll probably fail.

You'll probably want to extract the table and function definitions from a --schema-only dump text file, load them into the old DB by hand, then do a pg_dump --data-only and restore that to import the data.

After that, if you're going to continue working on your machine too, install PostgreSQL 8.4 and use that for further development so you don't introduce more incompatibilities and so it's easy to move dumps around.

In your position I'd just upgrade the outdated target server to 9.1.