Обсуждение: 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: > "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: > 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 >