Re: Mixed Pg-9.1.2 and 8.4.4 environment

On Tuesday, January 10, 2012 6:17:27 am James B. Byrne wrote:
> We are in the process of migrating our various servers to
> a kvm host with guest vms. The new guests run pg-9.1.2
> under CentOS-6.2. However, our off site warm spares are
> still running pg-8.4.4 under CentOS-5.7.
>
> We have an automated transfer routine that dumps the live
> database, now running v.9.1.2, and sends the dump file to
> the off site host. There the dump is restored to an 8.4.4
> version.
>
> My question: Is their a way to specify a pg_dump option
> to retain compatibility with a lower version pg database?
> Alternatively, can one install a higher level pg_dump in
> an otherwise 8.4.4 installation?

Short answer is no. The reason is seen in the errors below. EXTENSION does not
exist in Postgres below 9.1. In this particular case the EXTENSION is being used
to load a procedural language and that had a pre 9.1 analog in CREATE LANGUAGE.
In other instances that would not be the case. pg_dump can dump forward i.e use
new pg_dump version to pull data forward from old Postgres version, but not the
other way around.

>
> The reason for the question is that we are receiving these
> warnings:
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 257; 3079
> 12506 EXTENSION plpgsql
> pg_restore: [archiver (db)] could not execute query:
> ERROR: syntax error at or near "EXTENSION"
> LINE 1: DROP EXTENSION plpgsql;
> ^
> Command was: DROP EXTENSION plpgsql;
> 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...
> ^
> Command was: CREATE EXTENSION IF NOT EXISTS plpgsql
> WITH SCHEMA pg_catalog;
> pg_restore: [archiver (db)] Error from TOC entry 3473; 0 0
> COMMENT EXTENSION plpgsql
> pg_restore: [archiver (db)] could not execute query:
> ERROR: syntax error at or near "EXTENSION"
> LINE 1: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL
> procedural languag...
> ^

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com