Hi all,
Background:
I have a large database from our test environment that got into trouble with some high volume and some long-running
queriesabout…six weeks ago? We have a buffer mechanism that has been storing the new data since the database stopped
acceptingconnections, so we haven't really lost any data, which is good. But the single-user backend vacuum freeze is
stillgrinding away, using 100% cpu most of the time, except when it's doing a lot of writes. We did cancel the vacuum
onceso we could stop the database and take a snapshot of the filesystem (we are using ZFS on FreeBSD) and copy the
snapshotto another machine. This allowed us to get a fresh database started (with 9.3!) where we could unspool the last
sixweeks of data into a fresh database, and be able to access at least that much of our data.
Now:
I have a copy of the database (with data from all time up until the database shut itself down six weeks ago) that I
justneed the data from. I am becoming impatient with the vacuum, as it appears to have not even started working on the
filesfor one of the largest relations in the database (that table was about 14TB last I saw). I'm trying to find
alternativesto waiting another who knows how many weeks for the vacuum to finish just to have the database in a state
whereI can dump the data out, since this is no longer the "live" version. This copy running on hardware with plenty of
spaceto work with. The database has about a million transactions before it wraps.
Is it possible to somehow just dump the data using pg_dump or pg_dumpall? I haven't tried to see if those utilities
willwork when the database is protecting itself from data loss. If it were possible, would it be wise (do those
utilitiesuse tons of transactions to dump, or would it be safely within the million or so that I have)? I suppose I
coulduse copy? Would there be any risks if I were to do that? Maybe none of this is risky at this point because we can
alwaysclone the original snapshot, and try again.
Any ideas or suggestions?
Thanks!
Natalie