Re: pg_dump after transaction id wraparound failure

On 5/15/06, Scott Marlowe <smarlowe@g2switchworks.com> 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