Hi all, Background:
I have a large database from our test environment that got into trouble with some high volume and some long-running queries about…six weeks ago? We have a buffer mechanism that has been storing the new data since the database stopped accepting connections, so we haven't really lost any data, which is good. But the single-user backend vacuum freeze is still grinding away, using 100% cpu most of the time, except when it's doing a lot of writes. We did cancel the vacuum once so we could stop the database and take a snapshot of the filesystem (we are using ZFS on FreeBSD) and copy the snapshot to another machine. This allowed us to get a fresh database started (with 9.3!) where we could unspool the last six weeks 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 just need the data from. I am becoming impatient with the vacuum, as it appears to have not even started working on the files for one of the largest relations in the database (that table was about 14TB last I saw). I'm trying to find alternatives to waiting another who knows how many weeks for the vacuum to finish just to have the database in a state where I can dump the data out, since this is no longer the "live" version. This copy running on hardware with plenty of space to 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 will work when the database is protecting itself from data loss. If it were possible, would it be wise (do those utilities use tons of transactions to dump, or would it be safely within the million or so that I have)? I suppose I could use copy? Would there be any risks if I were to do that? Maybe none of this is risky at this point because we can always clone the original snapshot, and try again. Any ideas or suggestions? Thanks! Natalie -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
