DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com;
 h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
 b=shKzBTW7zpT+7s0pTwxOvymOHW6AJh+tqOy117WtLU6UrIX5eq0gIH5jbUZQNfvJk2DzdZDfMhZoQPvZ+RxR8X/8Q4xx6Xx+8vkAaTwioIHdrWkZGu0FOOw5Ixn3nIoeiqbLNNDdBiW+LOWFAoXSkYmvs0DWTxdGCyGIh3rvtl0=;
References: <B150080B-9E9A-456F-8B8D-110F07621165@ebureau.com>
Message-ID: <1379421830.30619.YahooMailNeo@web162902.mail.bf1.yahoo.com>
Date: Tue, 17 Sep 2013 05:43:50 -0700 (PDT)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: Dumping a database that is not accepting commands?
To: Natalie Wenz <nataliewenz@ebureau.com>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
In-Reply-To: <B150080B-9E9A-456F-8B8D-110F07621165@ebureau.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Natalie Wenz <nataliewenz@ebureau.com> wrote:

> I have a large database from our test environment that got into trouble w=
ith
> some high volume and some long-running queries about=E2=80=A6six weeks ag=
o? We have a
> buffer mechanism that has been storing the new data since the database st=
opped
> 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 1=
00% cpu
> most of the time, except when it's doing a lot of writes. We did cancel t=
he
> vacuum once so we could stop the database and take a snapshot of the file=
system
> (we are using ZFS on FreeBSD) and copy the snapshot to another machine. T=
his
> allowed us to get a fresh database started (with 9.3!) where we could uns=
pool
> the last six weeks of data into a fresh database, and be able to access a=
t least
> that much of our data.

I'm not sure how you could have done that without dealing with the
wraparound before the upgrade.

> Now:
> I have a copy of the database (with data from all time up until the datab=
ase
> shut itself down six weeks ago) that I just need the data from. I am beco=
ming
> 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 ab=
out
> 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 i=
n 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.

The copy running on 9.3, or the original?

> 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 w=
ithin
> the million or so that I have)? I suppose I could use copy?

pg_dump uses COPY (by default, anyway), and does all its work in a
single transaction.=C2=A0 But it needs to start that transaction.

> 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.

I'm not sure that pg_dump followed by restore would be expected to
be faster than finishing the VACUUM, unless that is configured to
pace itself way too slowly.

> Any ideas or suggestions?

After getting past this crisis, I would take a close look at your
vacuuming regimen -- it sounds like it is not aggressive enough to
keep you out of trouble.

I'm sorry that I don't have a better suggestion for resolving the
crisis than running VACUUM at maximum speed.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin