Message-ID: <5233F3A9.30901@gmail.com>
Date: Sat, 14 Sep 2013 13:27:05 +0800
From: Rural Hunter <ruralhunter@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1;
 rv:17.0) Gecko/20130801 Thunderbird/17.0.8
MIME-Version: 1.0
To: Alvaro Herrera <alvherre@2ndquadrant.com>
CC: pgsql-admin@postgresql.org
Subject: Re: wrong database name in error message?
References: <5233BA90.3020107@gmail.com>
 <20130914045519.GD5033@eldon.alvh.no-ip.org>
In-Reply-To: <20130914045519.GD5033@eldon.alvh.no-ip.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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