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