Backing up and restoring POSTGRESQL 9.1 to 8.4

Question

I am trying to upload a database that I developed locally to our development server.

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

When I try to restore the database to 8.4 using the dump file created using 9.1, I get an error message:

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 quick research tells me that "EXTENSION" does not exist until 9.1.

I'm not sure if I need to find an option in pg_dump that ignores "extensions", since the database I'm trying to load depends on the PostGIS extension for most of the data.

During the upgrade of the PostGIS development and installation server, there is an option in the dev server, I would like to know about another route in which I do not need to edit anything on the server, while maintaining the database functions I developed.

Of course, other workarounds are welcome, the only purpose of uploading my database to the server is to reduce the amount of reconfiguration that I have to perform in my project when I need to deploy something for our team.

Answer 1

Bacterial databases can be painful and difficult.

You can try using 8.4 pg_dump to dump it, but it is likely to fail.

You probably want to extract the table and function definitions from the --schema-only dump text file, manually load them into the old database, then run pg_dump --data-only and restore them to import the data.

After that, if you continue to work on your machine, install PostgreSQL 8.4 and use it for further development, so as not to introduce any more incompatibilities, and therefore it is easy to move the dumps.

In your position, I would just upgrade the legacy target server to 9.1.

Answer 2

This is an old post, but today I had the same problem, and there is a more reliable way to upload PG 9.1 db to the PG 8.4 server. The method proposed by Craig will not work on the target computer because PLPGSQL 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 on it

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

you can use sed to make changes

It is often not possible to upgrade server 8.4 due to application dependencies.