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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
