Excerpts from Torsten Zuehlsdorff's message of vie nov 11 04:51:48 -0300 2011:
> Heikki Linnakangas schrieb:
> > You won't lose data. There are safeguards in place to print warnings in
> > the log when you approach transaction wrap-around, and after a certain
> > point the system will stop accepting new transactions, to prevent data
> > loss.
>
> The manual says that autovacuum protects againt loss of very old data:
> http://www.postgresql.org/docs/9.1/static/routine-vacuuming.html
>
> So either your statement is wrong or the manual. ;)
Heikki is correct. The docs state, in the section that expands on the
single phrase talking about very old data being lost:
If for some reason autovacuum fails to clear old XIDs from a table, the
system will begin to emit warning messages like this when the database's oldest
XIDs reach ten million transactions from the wraparound point:
WARNING: database "mydb" must be vacuumed within 177009986 transactions
HINT: To avoid a database shutdown, execute a database-wide VACUUM in "mydb".
(A manual VACUUM should fix the problem, as suggested by the hint; but
note that the VACUUM must be performed by a superuser, else it will fail to
process system catalogs and thus not be able to advance the database's
datfrozenxid.) If these warnings are ignored, the system will shut down and
refuse to start any new transactions once there are fewer than 1 million
transactions left until wraparound:
ERROR: database is not accepting commands to avoid wraparound data loss in database "mydb"
HINT: Stop the postmaster and use a standalone backend to VACUUM in "mydb".
The 1-million-transaction safety margin exists to let the administrator
recover without data loss, by manually executing the required VACUUM commands.
However, since the system will not execute commands once it has gone into the
safety shutdown mode, the only way to do this is to stop the server and use a
single-user backend to execute VACUUM. The shutdown mode is not enforced by a
single-user backend. See the postgres reference page for details about using a
single-user backend.
So you don't actually lose data, but it's better not to get into that
situation in the first place.
--
Ãlvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support