Thank you very much for reviewing, appreciate the feedback. As pointed out by you, it is always best to test it out with the latest version, so, I tested the same approach with postgres 9.0.2 on windows just now, and it works!
I forgot to mention earlier that in addition to setting vacuum_freeze_table_age to 0, vacuum_freeze_min_age must also be set to 0 to reset xmin with the FrozenXid.
And you were spot on with regards to permission issues with roles. I had been testing with the postgres account, which is a superuser and it always works. After the database files are copied over in the deploy instance, any object that had ownership set to a custom role gets messed up, and logging in as that user gives permission denined error. But, there is a easy fix to this. As the postgres user, I ran the
alter table <objectname> owner to <rolename>
command for every object, followed by
grant all on <objecttype> <objectname> to <rolename>
command for every object, which resolved the permission denied issue. Thanks for pointing this out.
Please let me know if you or anyone think of any other potential issues. Thanks again for reviewing.
Srini
