pg_dump With OIDs Supported?

[Dylan Hansen]

Greetings everyone,

Since we've started using the pg_autovacuum table we've come to
realize that keeping OID values between our database dumps is
critical. I've been doing some testing using the pg_dump command
with the --oids option. For some reason, it doesn't seem like the
the OID values are being dumped, because when I restore the dump into
a different database I find that the OID values for my tables are
different.

As a test, I did the following:
createdb testdb1
psql -c "create table mytest(words varchar)" testdb1
psql -c "select oid from pg_class where relname = 'mytest'" testdb1
oid = 52178917
pg_dump -f testdb.sql --oids testdb1
createdb testdb2
psql testdb2 < testdb.sql
psql -c "select oid from pg_class where relname = 'mytest'" testdb2
oid = 52178923

As you can see, the OID values are different in each database.
Looking at the SQL dump I do not see any information related to OIDs.

I also tried using pg_dump with the "-F c" and "-F t" parameter,
using the pg_restore command and I see the same result. I've tested
with PostgreSQL 8.1.3 on Mac OSX as well as PostgreSQL 8.1.4 on RHEL-3.

Is this a bug or is this feature not supported anymore? Thanks for
any input!
--
Dylan Hansen
Enterprise Systems Developer

[Tom Lane]

Post by Dylan Hansen
Since we've started using the pg_autovacuum table we've come to
realize that keeping OID values between our database dumps is
critical. I've been doing some testing using the pg_dump command
with the --oids option.

--oids is only meant to preserve OIDs within user tables; it never has
and never will preserve OIDs for system-catalog entries. The real
problem here is pg_autovacuum, which doesn't have any dump/restore
support at the moment. This is because we stuck it into 8.1 at the last
minute and aren't yet convinced it will survive in its current form.

It strikes me that a relatively trivial hack would make it easier to
dump and restore pg_autovacuum manually using COPY: change the declared
type of the vacrelid column to "regclass". This would make no
difference to the internal use of the table, but it'd cause COPY to emit
the column values in a symbolic format that would restore correctly.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

[Dylan Hansen]

Hi Tom, thanks for your reply.

Post by Tom Lane
--oids is only meant to preserve OIDs within user tables; it never has
and never will preserve OIDs for system-catalog entries.

So just to clarify, the table's OID itself will never be preserved,
but the data inside the table will? Does each row have it's own
OID? Pardon my n00b-ness on this question...

Post by Tom Lane
The real problem here is pg_autovacuum, which doesn't have any dump/
restore
support at the moment. This is because we stuck it into 8.1 at the last
minute and aren't yet convinced it will survive in its current form.
It strikes me that a relatively trivial hack would make it easier to
dump and restore pg_autovacuum manually using COPY: change the
declared
type of the vacrelid column to "regclass". This would make no
difference to the internal use of the table, but it'd cause COPY to emit
the column values in a symbolic format that would restore correctly.

What I have done for the time being is created a script to be done
that executes after every restore of the database that enters into
pg_autovacuum based on the table name. For example:

INSERT INTO pg_autovacuum
(vacrelid, enabled, vac_base_thresh, vac_scale_factor,
anl_base_thresh, anl_scale_factor, vac_cost_delay, vac_cost_limit)
VALUES ((select oid from pg_class where relname = 'tablename'),
true, 500, 0.1, 200, 0.05, -1, -1);

This will work for now. It would be nice to have the vacrelid stay
the same for each restore as the app we are currently using
PostgreSQL with allows a script to be run before the dump is
restored. I will just have to alter it to be run after the dump is
restored.

Thanks!
--
Dylan Hansen
Enterprise Systems Developer