From: Rural Hunter <ruralhunter@gmail.com>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: wrong database name in error message?
Date: Sat, 14 Sep 2013 13:27:05 +0800
Message-ID: <5233F3A9.30901@gmail.com> (raw)
In-Reply-To: <20130914045519.GD5033@eldon.alvh.no-ip.org>
References: <5233BA90.3020107@gmail.com>
<20130914045519.GD5033@eldon.alvh.no-ip.org>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Sure. thanks any away and have a good night.
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"
Suggestion：Stop the postmaster and use a standalone backend 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.
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=,db=,host= WARNING:
database "db1" must be vacuumed within 999775 transactions
2013-09-14 08:56:44 CST [5536]: [454-1] user=,db=,host= HINT: To avoid
a database shutdown, execute a database-wide VACUUM in that database.
Here the error message still points to db1.
5. When I ran the single connection vacuum on other dbs(not db1), there
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?
2. How would that happend? Shouldn't auto vacuum handle it and avoid
such problem?
3. How to detect such problem earlier?
于 2013/9/14 12:55, Alvaro Herrera 写道:
> The ultimate source of truth here are the pg_class and pg_database
> catalogs (pg_class for each database stores the age of every table in
> that database; pg_database stores the minimum of such values in each
> database). The database name you see in the error messages is stored
> in pg_control (actually it's the OID that's stored not the name), but
> vacuuming other databases might have updated the pg_control info
> because of updated calculations from the shared catalog. I don't know
> how to explain the discrepancy other than concurrent processing by
> autovacuum, though. Perhaps autovacuum, in the last few Xids you had
> left, processed that database, but the field in pg_control didn't get
> updated until after you processed the other databases? Not sure about
> this. But it's past my bed time here, so no further speculation from me.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ruralhunter@gmail.com, alvherre@2ndquadrant.com
Subject: Re: wrong database name in error message?
In-Reply-To: <5233F3A9.30901@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox