Mixed Pg-9.1.2 and 8.4.4 environment
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?
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...
^
Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL
procedural language';
pg_restore: [archiver (db)] Error from TOC entry 3462; 0
20928 TABLE DATA wiki_content_versions
hll_redmine_db_admin
pg_restore: [archiver (db)] COPY failed: ERROR: invalid
input syntax for type bytea
CONTEXT: COPY wiki_content_versions, line 1, column data:
"\x68312e205b5b57696b6953746172747c48617274652026204c796e65204c696d697465642053746172745d5d0d0a0d0a0d..."
WARNING: errors ignored on restore: 4
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
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.klaver@gmail.com
On Tue, January 10, 2012 09:28, Adrian Klaver wrote:
On Tuesday, January 10, 2012 6:17:27 am James B. Byrne
wrote: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.
We upgraded the backup systems to v.9.1.2, which was
fairly trivial given that they simply load pgdumps to
begin with.
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3