From: | Rural Hunter <ruralhunter(at)gmail(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: wrong database name in error message? |
Date: | 2013-09-14 05:27:05 |
Message-ID: | 5233F3A9.30901@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-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.
From | Date | Subject | |
---|---|---|---|
Next Message | Keith Ouellette | 2013-09-14 14:19:51 | Too many WAL archive files |
Previous Message | Alvaro Herrera | 2013-09-14 04:55:19 | Re: wrong database name in error message? |