BUG #4901: Column name "window" breaks pg_dump/pg_restore
The following bug has been logged online:
Bug reference: 4901
Logged by: Steve Purcell
Email address: steve@sanityinc.com
PostgreSQL version: 8.4.0
Operating system: Linux (Debian Unstable)
Description: Column name "window" breaks pg_dump/pg_restore
Details:
The DB schema for openx (openx.org) contains a table with a column called
"window", which now causes a syntax error if unquoted. pg_dump doesn't
quote the column, so pg_restore/psql fail on the pg_dump output. Here's
what pg_dump spits out for the table:
CREATE TABLE ox_data_bkt_a (
server_conv_id bigint NOT NULL,
server_ip character varying(16) DEFAULT ''::character varying NOT NULL,
tracker_id integer NOT NULL,
date_time timestamp without time zone,
action_date_time timestamp without time zone,
creative_id integer NOT NULL,
zone_id integer NOT NULL,
ip_address character varying(16) DEFAULT ''::character varying NOT
NULL,
action integer,
window integer,
status integer
);
....
COPY ox_data_bkt_a (server_conv_id, server_ip, tracker_id, date_time,
action_date_time, creative_id, zone_id, ip_a
ddress, action, window, status) FROM stdin;
"Steve Purcell" <steve@sanityinc.com> writes:
The DB schema for openx (openx.org) contains a table with a column called
"window", which now causes a syntax error if unquoted. pg_dump doesn't
quote the column, so pg_restore/psql fail on the pg_dump output.
This is one of the reasons why it's recommended that you use the later
version's pg_dump to perform a cross-version dump and restore. 8.4's
pg_dump knows that "window" is a reserved word, but 8.3's could hardly
be expected to.
regards, tom lane
Thanks for the quick reply, Tom. That makes sense, and yes, that does
appear to be the problem.
I thought that I *was* using the newer pg_dump, but I'm doing this on
Debian:
# su - postgres -c "env PGCLUSTER=8.3/main pg_dump openx|env
PGCLUSTER=8.4/main pg_restore -d template1 -C"
And the debian pg_wrapper script ends up selecting the 8.3 pg_dump
binary in the first case. I'll have to figure out if there's even a
way to execute the newer pg_dump against the old database, which I
expect to be fiddly.
Thanks again, and sorry for the noise.
-Steve
On 5 Jul 2009, at 17:55, Tom Lane wrote:
Show quoted text
"Steve Purcell" <steve@sanityinc.com> writes:
The DB schema for openx (openx.org) contains a table with a column
called
"window", which now causes a syntax error if unquoted. pg_dump
doesn't
quote the column, so pg_restore/psql fail on the pg_dump output.This is one of the reasons why it's recommended that you use the later
version's pg_dump to perform a cross-version dump and restore. 8.4's
pg_dump knows that "window" is a reserved word, but 8.3's could hardly
be expected to.regards, tom lane
Steve Purcell <steve@sanityinc.com> writes:
# su - postgres -c "env PGCLUSTER=8.3/main pg_dump openx|env
PGCLUSTER=8.4/main pg_restore -d template1 -C"And the debian pg_wrapper script ends up selecting the 8.3 pg_dump binary in
the first case. I'll have to figure out if there's even a way to execute
the newer pg_dump against the old database, which I expect to be
fiddly.
I guess you could trick the binary choice then force another server
choice :
pg_dump --cluster 8.4/main -p <port of 8.3> ...
But I'd simply go with:
/usr/lib/postgresql/8.4/bin/pg_dump <options to target 8.3>
For people unaware of debian way of supporting more than one major
version and cluster, all PostgreSQL user binaries in /usr/bin are a link
to their pg_wrapper, which support the --cluster 8.X/clustername option
and will choose the right versionned binary and port number etc.
Regards,
--
dim
On 2009-07-05, Steve Purcell <steve@sanityinc.com> wrote:
Thanks for the quick reply, Tom. That makes sense, and yes, that does
appear to be the problem.I thought that I *was* using the newer pg_dump, but I'm doing this on
Debian:# su - postgres -c "env PGCLUSTER=8.3/main pg_dump openx|env
PGCLUSTER=8.4/main pg_restore -d template1 -C"And the debian pg_wrapper script ends up selecting the 8.3 pg_dump
binary in the first case. I'll have to figure out if there's even a
way to execute the newer pg_dump against the old database, which I
expect to be fiddly.
easiest is to bypass pg_wrapper
/usr/lib/postgresql/8.4/bin/pg_dump -p 5432
I have raised this with debian.
Terrific, thanks for that. I'd figured out the first trick you
suggested, but the second is much clearer.
-Steve
On 6 Jul 2009, at 12:28, Dimitri Fontaine wrote:
Show quoted text
Steve Purcell <steve@sanityinc.com> writes:
# su - postgres -c "env PGCLUSTER=8.3/main pg_dump openx|env
PGCLUSTER=8.4/main pg_restore -d template1 -C"And the debian pg_wrapper script ends up selecting the 8.3 pg_dump
binary in
the first case. I'll have to figure out if there's even a way to
execute
the newer pg_dump against the old database, which I expect to be
fiddly.I guess you could trick the binary choice then force another server
choice :
pg_dump --cluster 8.4/main -p <port of 8.3> ...But I'd simply go with:
/usr/lib/postgresql/8.4/bin/pg_dump <options to target 8.3>For people unaware of debian way of supporting more than one major
version and cluster, all PostgreSQL user binaries in /usr/bin are a
link
to their pg_wrapper, which support the --cluster 8.X/clustername
option
and will choose the right versionned binary and port number etc.Regards,
--
dim