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:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
 b=hbJIbSJgp13FatKMcdUdYO2QROPcJy9yTezoNJ9AcS3oMq2h4+/72xQ5m/jSWIYPn7PBgRxbFVxkJOdEo6jAE7Gz8vxA5wlHZi0cVi+vEz/wPofBOhqsi2alhKxtdm3uaG8wPtgrUTvrDj8SSOwko4D+xcc/Xt3SDoeUd0Nk1b8=;
References: <5233BA90.3020107@gmail.com>
 <20130914045519.GD5033@eldon.alvh.no-ip.org> <5233F3A9.30901@gmail.com>
Message-ID: <1379178368.98727.YahooMailNeo@web162901.mail.bf1.yahoo.com>
Date: Sat, 14 Sep 2013 10:06:08 -0700 (PDT)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: wrong database name in error message?
To: Rural Hunter <ruralhunter@gmail.com>,
 Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
In-Reply-To: <5233F3A9.30901@gmail.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

Rural Hunter <ruralhunter@gmail.com> wrote:

> Let me put here the whole scenario:
> 1. I was called by our application users that all the updating was
> failing. So I went to check the db. Any update transaction including
> manual vacuum is blocked out by the error message:
> ERROR: database is not accepting commands to avoid wraparound data loss
> in database "db1"
>=C2=A0=C2=A0 Suggestion=EF=BC=9AStop the postmaster and use a standalone b=
ackend to
> vacuum that database.
>
> 2. Since db1 is a very large database(it is the main db the user is
> using) I can not afford to take long time to vacuum full on that. So I
> thought about to try on other small dbs first.

Why in the world would you want to use VACUUM FULL in this circumstance?

> 3. I stop the instance.
>
> 4. I use "echo 'vacuum full;' | postgres --single -D /pgdata [other
> dbs]" to vacuum some other dbs. I still got several warning messages
> when vacuum the first database(let's say db2):
> 2013-09-14 08:56:44 CST [5536]: [453-1] user=3D,db=3D,host=3D WARNING:
> database "db1" must be vacuumed within 999775 transactions
> 2013-09-14 08:56:44 CST [5536]: [454-1] user=3D,db=3D,host=3D HINT:=C2=A0=
 To avoid
> a database shutdown, execute a database-wide VACUUM in that database.

WARNING, not error, so the VACUUM would have run.

> Here the error message still points to db1.

I'm not sure which database would be referenced if the table which
needed the VACUUM was a shared table, like pg_database or
pg_authid.

> 5.=C2=A0 When I ran the single connection vacuum on other dbs(not db1), t=
here
> was not any error/warning message. So I tried to start whole instance.
>
> 6. I started the instance and found everything is fine.
>
> So actually I have 3 questions here:
> 1. Was the db name in the error message wrong?

Probably not, to the extent that running VACUUM (FULL is not
necessary) against that database would have solved the problem.=C2=A0 If
it was a shared catalog table it might be that it was not the
*only* database which would work.

> 2. How would that happend? Shouldn't auto vacuum handle it and avoid
> such problem?

There are two possibilities -- either you had a long-running
transaction in the cluster or your autovacuum is not configured to
be aggressive enough to keep you out of trouble.

> 3. How to detect such problem earlier?

We would need a description of the machine (cores, RAM, storage
system) and the output of these queries to be able to make good
suggestions on tuning autovacuum:

SELECT version();
SELECT name, current_setting(name), source
=C2=A0 FROM pg_settings
=C2=A0 WHERE source NOT IN ('default', 'override');

In addition, make sure that you are monitoring for long-running
transactions.=C2=A0 A reasonable monitoring scheme might be to alert
when either of these queries returns any rows:

select * from pg_stat_activity where xact_start < (now() - interval '1 hour=
');
select * from pg_prepared_xacts where prepared < (now() - interval '1 minut=
e');

You can, of course, adjust the intervals to what makes the most
sense for your environment.=C2=A0 If you have max_prepared_transactions
set to zero, the latter query is not really necessary.

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