Thread: 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