Thread: migrating data from 7.3.x down to 7.2.x
Hi,
I tried to migrate a database from 7.3.2 down to PostgreSQL 7.2.2 and
got some trouble. The database contains various plpgsql triggers. Here
what I tried:
On the machine running 7.3.2, I typed:
pg_dump -U sguru sguru_test > sguru.dmp
sguru.dmp is a text-style file. I copied sguru.dmp to the maschine
running 7.2.2 and typed there
createdb -U postgres sguru_test
psql -f sguru.dmp sguru_test sguru
It gives me some errors, see below.
My next step was to connect pg_dump remotely from the 7.2.2 box to dump
the 7.3.2 database (with -i option). This gave me this error.
pg_dump: query to obtain list of data types failed: ERROR: Attribute
"typprtlen" not found
I think, the 7.3.2 dump file format uses some syntax 7.2.2 does not
understand. Is there a way to force 7.3.2's pg_dump to produce a 7.2.2
compatible output?
Or any other suggestions?
Kind regards,
Stefan
***************** ERRORS of psql call ********************************
You are now connected as new user postgres.
psql:sguru.dmp:7: ERROR: parser: parse error at or near "public"
psql:sguru.dmp:16: NOTICE: ProcedureCreate: type language_handler is
not yet defined
CREATE
psql:sguru.dmp:24: ERROR: PL handler function plpgsql_call_handler()
isn't of return type Opaque
You are now connected as new user sguru.
psql:sguru.dmp:29: ERROR: parser: parse error at or near "public"
...
...
***************** The first lines of sguru.dmp are ********************
--
-- PostgreSQL database dump
--
\connect - postgres
SET search_path = public, pg_catalog;
--
-- TOC entry 163 (OID 29597)
-- Name: plpgsql_call_handler (); Type: FUNCTION; Schema: public; Owner:
postgres
--
CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler
AS '$libdir/plpgsql', 'plpgsql_call_handler'
LANGUAGE c;
--
-- TOC entry 162 (OID 29598)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: public; Owner:
--
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
\connect - sguru
SET search_path = public, pg_catalog;
--
-- TOC entry 2 (OID 29599)
-- Name: mail_spooler; Type: TABLE; Schema: public; Owner: sguru
--
CREATE TABLE mail_spooler (
objectid numeric(19,0) NOT NULL,
trial smallint,
error_msg character varying(255),
to_adr character varying(255) NOT NULL,
subject character varying(255),
body text,
objectversion integer NOT NULL,
from_adr character varying(50) NOT NULL,
cc_adr character varying(255),
bcc_adr character varying(255)
);
On Monday 14 July 2003 16:04, Stefan Armbruster wrote: > Hi, > > I tried to migrate a database from 7.3.2 down to PostgreSQL 7.2.2 Any particular reason? > and > got some trouble. The database contains various plpgsql triggers. Here > what I tried: (...) > It gives me some errors, see below. > > My next step was to connect pg_dump remotely from the 7.2.2 box to dump > the 7.3.2 database (with -i option). This gave me this error. > pg_dump: query to obtain list of data types failed: ERROR: Attribute > "typprtlen" not found Connecting older utilities to newer backends generally won't work. > I think, the 7.3.2 dump file format uses some syntax 7.2.2 does not > understand. Is there a way to force 7.3.2's pg_dump to produce a 7.2.2 > compatible output? No. > Or any other suggestions? If triggers are your only problem it might just be a case of manually tweaking the trigger function definitions to return OPAQUE instead of TRIGGER, this is the error here: > psql:sguru.dmp:24: ERROR: PL handler function plpgsql_call_handler() > isn't of return type Opaque > You are now connected as new user sguru. > psql:sguru.dmp:29: ERROR: parser: parse error at or near "public" (...) > SET search_path = public, pg_catalog; The above line won't work in 7.2.x. The other major problem, at least in terms of DDL, is CREATE OR REPLACE ..., you will need to remove the "OR REPLACE" bit. Ian Barwick barwick@gmx.net
Hi, Am Mon, 2003-07-14 um 21.01 schrieb Ian Barwick: > On Monday 14 July 2003 16:04, Stefan Armbruster wrote: > > Hi, > > > > I tried to migrate a database from 7.3.2 down to PostgreSQL 7.2.2 > > Any particular reason? Yes, I'm developing with 7.3.2, production is 7.2.2. > > > and > > got some trouble. The database contains various plpgsql triggers. Here > > what I tried: > (...) > > It gives me some errors, see below. > > > > My next step was to connect pg_dump remotely from the 7.2.2 box to dump > > the 7.3.2 database (with -i option). This gave me this error. > > pg_dump: query to obtain list of data types failed: ERROR: Attribute > > "typprtlen" not found > > Connecting older utilities to newer backends generally won't work. > > > I think, the 7.3.2 dump file format uses some syntax 7.2.2 does not > > understand. Is there a way to force 7.3.2's pg_dump to produce a 7.2.2 > > compatible output? > > No. > > > Or any other suggestions? > > If triggers are your only problem it might just be a case of manually tweaking > the trigger function definitions to return OPAQUE instead of TRIGGER, this is > the error here: > > > psql:sguru.dmp:24: ERROR: PL handler function plpgsql_call_handler() > > isn't of return type Opaque > > > You are now connected as new user sguru. > > psql:sguru.dmp:29: ERROR: parser: parse error at or near "public" > (...) > > SET search_path = public, pg_catalog; > > The above line won't work in 7.2.x. The other major problem, at least > in terms of DDL, is CREATE OR REPLACE ..., you will need to remove > the "OR REPLACE" bit. In other words: a short script with some cut & replace operations could do the job? Is there a specific document describing all the DDL changes from 7.2 to 7.3?ßß Stefan
On Tue, Jul 15, 2003 at 09:51:40AM +0200, Stefan Armbruster wrote:
> Yes, I'm developing with 7.3.2, production is 7.2.2.
I hate to be snarky, but doesn't that seem to you like a bad idea?
(In any case, you'd better get your production version to 7.2.4.
There are some nasty bugs in 7.2.2.)
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
On Tuesday 15 July 2003 09:51, Stefan Armbruster wrote: > Hi, > > Am Mon, 2003-07-14 um 21.01 schrieb Ian Barwick: > > On Monday 14 July 2003 16:04, Stefan Armbruster wrote: > > > Hi, > > > > > > I tried to migrate a database from 7.3.2 down to PostgreSQL 7.2.2 > > > > Any particular reason? > > Yes, I'm developing with 7.3.2, production is 7.2.2. Not hostsharing.net by any chance? (...) > In other words: a short script with some cut & replace operations could > do the job? Yes, although depending on your data it might take a bit of trial and error. > Is there a specific document describing all the DDL changes > from 7.2 to 7.3?ßß The release notes: http://www.postgresql.org/docs/7.3/static/release.html#RELEASE-7-3 are usually a good starting point. Ian Barwick barwick@gmx.net
Hello, You really don't want to do this. 7.3.x is much more stable, offers better support for various things and handles load much better. If you need a 7.3.x host look at (plug) www.commandprompt.com or even phHoster.com J Ian Barwick wrote: > On Tuesday 15 July 2003 09:51, Stefan Armbruster wrote: > >>Hi, >> >>Am Mon, 2003-07-14 um 21.01 schrieb Ian Barwick: >> >>>On Monday 14 July 2003 16:04, Stefan Armbruster wrote: >>> >>>>Hi, >>>> >>>>I tried to migrate a database from 7.3.2 down to PostgreSQL 7.2.2 >>> >>>Any particular reason? >> >>Yes, I'm developing with 7.3.2, production is 7.2.2. > > > Not hostsharing.net by any chance? > (...) > >>In other words: a short script with some cut & replace operations could >>do the job? > > > Yes, although depending on your data it might take a bit of trial and error. > > >>Is there a specific document describing all the DDL changes >>from 7.2 to 7.3?ßß > > > The release notes: > http://www.postgresql.org/docs/7.3/static/release.html#RELEASE-7-3 > > are usually a good starting point. > > > Ian Barwick > barwick@gmx.net > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)