On 5/15/06, Scott Marlowe <smarlowe@xxxxxxxxxxxxxxxxx> wrote:
On Mon, 2006-05-15 at 13:48, Martijn van Oosterhout wrote: > On Mon, May 15, 2006 at 11:44:01AM -0700, Michael L. Boscia wrote: > > Hello- > > > > I currently have a 1.4 TB database (pg version 7.4.8) that is experiencing > > transaction id wraparound. I plan to pg_dump the database and initdb and > > reload. I may reload into an 8.1 database at this time. > > > > I am curious how to make sure that I indeed get all the data out of the > > tables from a pg_dump. I want to ensure that there would not be anything > > "hidden" by the transaction id wraparound failure. > > pg_dump obviously won't see anything that's hidden by wraparound > failure.
Yes- this does make sense.
> > Vacuum will make anything that disappeared by wraparound in the last > billion transactions reappear, so a databasewide vacuum should solve > all your problems, no need to dump...
Are we positive that a database wide vacuum will solve this? I am concerned that while some transactions may "appear" some might still be affected by wraparound failure
> > My current plan is selectively dump a portion of the data (most current, > > organized in tables by date ex xxx_2006_may) and reload. I plan to vaccuum > > each table before dumping. Please warn me if this is not enough. Any > > assistance is greatly appreciated. > > I'd say do the vacuum first, dumping the data isn't really helping much > in this case I think (although for backups it's obviously important).
The only reason I would like to dump is to rid myself of a possibly corrupt pg_catalog tablespace and any issues that may arise out of this. This database was not cared for properly, in that vacuums were not properly scheduled, and I would like to get all the important data out and start over with a new initdb. I am just not sure that I would be getting all the data out.
This is one of those rare instances when I'd shut down the server and take a file system backup if I could. However, at 1.4 TB, that might be a bit difficult... :)
What do I do with a filesystem backup after its done? If I copy and restore somewhere won't I experience all the same issues? Thanks so much for your help -- Mike Boscia mikeboscia at gmail dot com
