Execute the following SQL on your pg cluster:
SELECT datname, age(datfrozenxid) FROM pg_database;
datname | age
--------------+------------
bp_live | 1075940691
template1 | 1130066178
template0 | 56361936
(3 rows)
Apart from template0 which is a special case (provided its frozen and
readonly (which it is by default)), you want the numbers in the age
column to be less than 2 billion.
This is achieved by vacuuming EACH database including template1
regularly. You don't need to perform a full vacuum either. You just need
to do it regularly.
From my understanding, if numbers in that column have gone negative
than you have already experienced transaction wraparound. This may then
be seen as "data loss" or missing records.
It's also worth checking your pg server log in pgdata/data/serverlog to
see if you are seeing messages like this:
2005-10-27 05:55:55 WARNING: some databases have not been vacuumed in
2129225822 transactions
HINT: Better vacuum them within 18257825 transactions, or you may have
a wraparound failure.
Here's a excerpt from the pg 7.4 manual:
<quote> With the standard freezing policy, the age column will start at
one billion for a freshly-vacuumed database. When the age approaches two
billion, the database must be vacuumed again to avoid risk of wraparound
failures. Recommended practice is to vacuum each database at least once
every half-a-billion (500 million) transactions, so as to provide plenty
of safety margin. To help meet this rule, each database-wide VACUUM
automatically delivers a warning if there are any pg_database entries
showing an age of more than 1.5 billion transactions, for example:
play=# VACUUM;
WARNING: some databases have not been vacuumed in 1613770184 transactions
HINT: Better vacuum them within 533713463 transactions, or you may have
a wraparound failure.
VACUUM
</quote>
If you have suffered data loss for this reason, then you'll need to get
help from the developers to see whether it can be recovered, or what you
can do to reconstruct the data.
Good luck!
John
Venki wrote:
Hi,
Has your system been used long enough that it could be subject to
transaction ID wraparound?
what is this can you give me more information on this or some pointers from
where I can get more information on this and how to solve this. because I
too has experinced this problem disappearinf records.
regards
Venki
-------Original Message-------
From: Tom Lane
Date: 11/01/05 20:30:51
To: Rory Browne
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: Disappearing Records
Rory Browne <rory.browne@xxxxxxxxx> writes:
What is the first thing you would do, when you find that your system
has been losing information? Information is there at one stage, and
later it's not.
Has your system been used long enough that it could be subject to
transaction ID wraparound?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
