воскресенье, 5 декабря 2010 г.

XID wraparround and postgresql single user mode

I've just found on pgsql-general mailing list interesting detail. This case supports the idea that manuals should be read from cover to cover. Of course, I've never bothered with this :)
The case was the following: DBMS refused to start with message:

2010-12-01 08:36:42 EST LOG: database system was shut down at
2010-11-29 17:38:17 EST
2010-12-01 08:36:42 EST FATAL: invalid cache id: 19
2010-12-01 08:36:43 EST FATAL: the database system is starting up
2010-12-01 08:36:43 EST LOG: startup process (PID 640) exited with exit
code 1
2010-12-01 08:36:43 EST LOG: aborting startup due to startup process
failure

The previous messages in the log were

2010-11-29 12:39:17 EST ERROR: database is not accepting commands to
avoid wraparound data loss in database "fps_data"
2010-11-29 12:39:17 EST HINT: Stop the postmaster and use a standalone
backend to vacuum that database.


I'm glad, that it was not mine DBMS. As we see from log, postgresql shutted down itself to avoid XID wrapparound. It is covered in detail in the manual. The main idea is that every row in table is marked with a XID of last transaction, which modified it. So, when row is not modified for long, vacuum (or autovacuum) will increase its XID, and it will not wrap (to compare XIDs PostgreSQL using modulo-2^31 arithmetic, so that "for every normal XID, there are two billion XIDs that are "older" and two billion that are "newer"; another way to say it is that the normal XID space is circular with no endpoint" (c) PostgreSQL 9.0 Documentation).
In this situation, server had seen that we were close to maximum 2 billion difference between current XID and oldest one in DB and shutted down. So, now we have to run postgresql in single user mode and do vacuum on affected database. Something like that:

# su - pgsql -c '/usr/local/bin/postgres --single -D /pgdata dbname'
PostgreSQL stand-alone backend 9.0.0
backend> vacuum;
backend> ^D
# /usr/local/etc/rc.d/postgresql start

Комментариев нет:

Отправить комментарий