Thread: Having trouble with pg_dumpall -o
Hi PostgreSQL users, I'm having difficulty migrating a postgres 8.4.11 database to postgres 9.1.2, neither of the included pg_dumpall tools appear to honour the -o or --oids options and fail to dump the table oids from the old database as we require. I've tried various combinations and orders of options including those below:- pg_dumpall -o -p 5432 -U postgres > dump_o.sql pg_dumpall --oids -p 5432 -U postgres > dump_o.sql pg_dumpall -p 5432 -U postgres -o > dump_o.sql pg_dumpall -p 5432 -U postgres --oids > dump_o.sql I'm beginning to think it could just be a bug but I'm not familiar with this tool and wanted to run it by the community before jumping to conclusions. Any help is appreciated! Thanks in advanced, Matt
On 1 May 2012 11:12, Matthew Churcher <Matthew.Churcher@realvnc.com> wrote: > Hi PostgreSQL users, > > I'm having difficulty migrating a postgres 8.4.11 database to postgres > 9.1.2, neither of the included pg_dumpall tools appear to honour the -o or > --oids options and fail to dump the table oids from the old database as we > require. > > I've tried various combinations and orders of options including those > below:- > pg_dumpall -o -p 5432 -U postgres > dump_o.sql > pg_dumpall --oids -p 5432 -U postgres > dump_o.sql > pg_dumpall -p 5432 -U postgres -o > dump_o.sql > pg_dumpall -p 5432 -U postgres --oids > dump_o.sql I can confirm that pg_dumpall's -o option doesn't appear to work, but pg_dump's -o does. This looks like a bug to me, especially since it also doesn't produce the WITH OIDS option on the CREATE TABLE statement for tables which do have OIDs. -- Thom
Thanks Thom, that's really useful to know however I've been unable to get it working with pg_dump either. Are you able to offer any insight there? What command line options are you using? I get the same result with: pg_dump -o mydatabase pg_dump mydatabase Thanks again, Matt -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Thom Brown Sent: 01 May 2012 11:23 To: Matthew Churcher Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Having trouble with pg_dumpall -o On 1 May 2012 11:12, Matthew Churcher <Matthew.Churcher@realvnc.com> wrote: > Hi PostgreSQL users, > > I'm having difficulty migrating a postgres 8.4.11 database to postgres > 9.1.2, neither of the included pg_dumpall tools appear to honour the > -o or --oids options and fail to dump the table oids from the old > database as we require. > > I've tried various combinations and orders of options including those > below:- > pg_dumpall -o -p 5432 -U postgres > dump_o.sql pg_dumpall --oids -p > 5432 -U postgres > dump_o.sql pg_dumpall -p 5432 -U postgres -o > > dump_o.sql pg_dumpall -p 5432 -U postgres --oids > dump_o.sql I can confirm that pg_dumpall's -o option doesn't appear to work, but pg_dump's -o does. This looks like a bug to me, especially since it also doesn't produce the WITH OIDS option on the CREATE TABLE statement for tables which do have OIDs. -- Thom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 1 May 2012 11:55, Matthew Churcher <Matthew.Churcher@realvnc.com> wrote: > Thanks Thom, that's really useful to know however I've been unable to get > it working with pg_dump either. Are you able to offer any insight there? > What command line options are you using? > > I get the same result with: > pg_dump -o mydatabase > pg_dump mydatabase Can you confirm your tables actually use OIDs? By default they don't. -- Thom
OK, I think I've worked out what's going on. I've got my wires crossed between table column OIDS (deprecated) and the OID which uniquely identifies each table (?always enabled?). We're not using OID for each column, only to reference the tables themselves as that's how triggers are referring to them. It appears the -o flag is for migrating table column oids which we're not using. So... any ideas how we can migrate the OID of the table itself? Or are we doing something we shouldn't? Thanks, Matt. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Thom Brown Sent: 01 May 2012 11:58 To: Matthew Churcher Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Having trouble with pg_dumpall -o On 1 May 2012 11:55, Matthew Churcher <Matthew.Churcher@realvnc.com> wrote: > Thanks Thom, that's really useful to know however I've been unable to > get it working with pg_dump either. Are you able to offer any insight there? > What command line options are you using? > > I get the same result with: > pg_dump -o mydatabase > pg_dump mydatabase Can you confirm your tables actually use OIDs? By default they don't. -- Thom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 1 May 2012 11:22, Thom Brown <thom@linux.com> wrote: > On 1 May 2012 11:12, Matthew Churcher <Matthew.Churcher@realvnc.com> wrote: >> Hi PostgreSQL users, >> >> I'm having difficulty migrating a postgres 8.4.11 database to postgres >> 9.1.2, neither of the included pg_dumpall tools appear to honour the -o or >> --oids options and fail to dump the table oids from the old database as we >> require. >> >> I've tried various combinations and orders of options including those >> below:- >> pg_dumpall -o -p 5432 -U postgres > dump_o.sql >> pg_dumpall --oids -p 5432 -U postgres > dump_o.sql >> pg_dumpall -p 5432 -U postgres -o > dump_o.sql >> pg_dumpall -p 5432 -U postgres --oids > dump_o.sql > > I can confirm that pg_dumpall's -o option doesn't appear to work, but > pg_dump's -o does. This looks like a bug to me, especially since it > also doesn't produce the WITH OIDS option on the CREATE TABLE > statement for tables which do have OIDs. Actually I am mistaken here. I was looking at the same table in a different database in the dump. The -o option works fine in pg_dumpall. -- Thom
On 1 May 2012 12:37, Matthew Churcher <Matthew.Churcher@realvnc.com> wrote: > OK, I think I've worked out what's going on. I've got my wires crossed > between table column OIDS (deprecated) and the OID which uniquely identifies > each table (?always enabled?). > > We're not using OID for each column, only to reference the tables themselves > as that's how triggers are referring to them. > > It appears the -o flag is for migrating table column oids which we're not > using. > > So... any ideas how we can migrate the OID of the table itself? Or are we > doing something we shouldn't? You shouldn't be relying on OIDs as they're not really for the end-user. Instead you should refer to objects by name. How are you using them in triggers? -- Thom
The triggers are being used to track changes to the tables. The developers are concerned that using string references for the table names in this case would create too much overhead as this is a frequent operation and is performance critical. Sounds like we have the choice of using string names or implementing a custom migration script. Thanks for all your help Thom. Regards, Matt -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Thom Brown Sent: 01 May 2012 12:43 To: Matthew Churcher Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Having trouble with pg_dumpall -o On 1 May 2012 12:37, Matthew Churcher <Matthew.Churcher@realvnc.com> wrote: > OK, I think I've worked out what's going on. I've got my wires crossed > between table column OIDS (deprecated) and the OID which uniquely > identifies each table (?always enabled?). > > We're not using OID for each column, only to reference the tables > themselves as that's how triggers are referring to them. > > It appears the -o flag is for migrating table column oids which we're > not using. > > So... any ideas how we can migrate the OID of the table itself? Or are > we doing something we shouldn't? You shouldn't be relying on OIDs as they're not really for the end-user. Instead you should refer to objects by name. How are you using them in triggers? -- Thom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
"Matthew Churcher" <Matthew.Churcher@realvnc.com> writes:
> The triggers are being used to track changes to the tables. The developers
> are concerned that using string references for the table names in this case
> would create too much overhead as this is a frequent operation and is
> performance critical.
"Premature optimization is the root of all evil". Do you have any
actual evidence that you bought a meaningful amount of performance
with this idea? It seems like a terrible restriction from here
--- for instance, you are absolutely locked out of doing plain
dump and restore.
> Sounds like we have the choice of using string names or implementing a
> custom migration script.
pg_upgrade might save your bacon here. But I'd rewrite that code ASAP.
The fact that pg_upgrade preserves table OIDs is an implementation
detail, not a feature we promise to preserve.
regards, tom lane
On 05/01/2012 05:06 AM, Matthew Churcher wrote: > The triggers are being used to track changes to the tables. The developers > are concerned that using string references for the table names in this case > would create too much overhead as this is a frequent operation and is > performance critical. > > Sounds like we have the choice of using string names or implementing a > custom migration script. Might want to take a look at: http://www.postgresql.org/docs/9.1/interactive/datatype-oid.html > > Thanks for all your help Thom. > > Regards, Matt > > -- Adrian Klaver adrian.klaver@gmail.com