Thread: pg_dump With OIDs Supported?
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
Dylan Hansen <dhansen@pixpo.com> writes:
> 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
Hi Tom, thanks for your reply.
On 23-Jun-06, at 11:17 AM, Tom Lane wrote:
--oids is only meant to preserve OIDs within user tables; it never hasand 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...
The real problem here is pg_autovacuum, which doesn't have any dump/restoresupport at the moment. This is because we stuck it into 8.1 at the lastminute and aren't yet convinced it will survive in its current form.
It strikes me that a relatively trivial hack would make it easier todump and restore pg_autovacuum manually using COPY: change the declaredtype of the vacrelid column to "regclass". This would make nodifference to the internal use of the table, but it'd cause COPY to emitthe 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