Hi, can someone point me where is explained how can i recover from a
wraparound that vanish all databases in a cluster?

Mon, Dec 26, 2005 at 03:21:03PM -0500, Jaime Casanova wrote:
Hi, can someone point me where is explained how can i recover from a
wraparound that vanish all databases in a cluster?

Some recent investigation indicates that simply doing a VACUUM on the
databases in question should bring the data back, as long as it hasn't
been too long the wraparound.

However, to be more specific you'll need to provide info as to which
version of PostgreSQL and what your VACUUM setup so far has been.

Have a nice day,

12/26/05, Martijn van <kleptog (AT) svana (DOT) orgwrote:
Mon, Dec 26, 2005 at 03:21:03PM -0500, Jaime Casanova wrote:
Hi, can someone point me where is explained how can i recover from a
wraparound that vanish all databases in a cluster?

Some recent investigation indicates that simply doing a VACUUM on the
databases in question should bring the data back, as long as it hasn't
been too long the wraparound.

However, to be more specific you'll need to provide info as to which
version of PostgreSQL and what your VACUUM setup so far has been.

yes, it seems that's enough

i was trying to help to a buddy in the spanish list and my first
recommendation was to copy all data directory when he tries that he
makes a vacuum and the data go back

i told him to review their data to be sure

Have a nice day,

Mon, Dec 26, 2005 at 04:11:49PM -0500, Jaime Casanova wrote:
yes, it seems that's enough

i was trying to help to a buddy in the spanish list and my first
recommendation was to copy all data directory when he tries that he
makes a vacuum and the data go back

i told him to review their data to be sure

For the record, the data dissappears from view after 2 billion
transactions but it's not until 3 billion that VACUUM considers the
data in the future and thus removable. VACUUM fixes it so it appears
again and all is well.

The only issue I can think of is that constraints might be violated
(duplicate keys in unique index) because one of the records might have
been invisible when the second was created

Have a nice day,

Mon, 26 Dec 2005, Tom Lane wrote:

This might be a good time to press your buddy to move to 8.1 ;-)
PG 8.1 contains logic that should positively prevent a wraparound, by
shutting down the server if wraparound gets too close.

But if VACUUM fixes the wraparound issue, shouldn't even a badly
configured autovacuum make the wraparound not be a problem in 8.1? did
I miss understand how this works?

12/27/05, Lic. Martin Marques <martin (AT) bugs (DOT) unl.edu.arwrote:
Mon, 26 Dec 2005, Tom Lane wrote:

This might be a good time to press your buddy to move to 8.1 ;-)
PG 8.1 contains logic that should positively prevent a wraparound, by
shutting down the server if wraparound gets too close.

that was my advice :)

But if VACUUM fixes the wraparound issue, shouldn't even a badly
configured autovacuum make the wraparound not be a problem in 8.1? did
I miss understand how this works?

but you can disable autovacuum (i do not why you can do something like
that but i guess someone will have a good reason) actually it comes
off by default in all distros i have seen but windows

Sat, Dec 31, 2005 at 10:34:51AM -0500, Matthew T. 'Connor wrote:
Anyone think it might be reasonable to add a GUC option that tells
autovacuum to monitor for wraparound only, and not for more general
usage based vacuuming? Something like autovac_wraparound_only. Not
sure I like the idea, but thought it might be worth some discussion.

We don't want the autovacuum to be running the whole time monitoring
for something that won't happen to most people. But I think something
like:

Ie, when you reach the billion transaction mark and postmaster begins
emitting warning, it will, once off, spawn autovacuum to vacuum the
most neediest database.

ISTM that many people who run into wraparound issue don't because they
don't have a vacuum policy, but because they made one very clever but
forgot to do the catalog or something else. Having the postmaster
spawning it once every billion transactions seems sensible enough.

question, does it rely on other options (like stats) to work for
this purpose?