On 23-Jun-06, at 11:17 AM, Tom Lane wrote:
--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...
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