solving wraparound
Hi, can someone point me where is explained how can i recover from a
wraparound that vanish all databases in a cluster?
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
On 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,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
On 12/26/05, Martijn van Oosterhout <kleptog@svana.org> wrote:
On 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,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
On 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,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
Martijn van Oosterhout <kleptog@svana.org> writes:
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...
More generally, application-driven updates of derived data might be
wrong because they omitted consideration of data that had become
invisible.
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.
regards, tom lane
On 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? Or did
I miss understand how this works?
--
08:20:01 up 4 days, 19:32, 1 user, load average: 0.04, 0.08, 0.15
---------------------------------------------------------
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática | '@' || 'unl.edu.ar';
Universidad Nacional | DBA, Programador,
del Litoral | Administrador
---------------------------------------------------------
On 12/27/05, Lic. Martin Marques <martin@bugs.unl.edu.ar> wrote:
On 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? Or 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...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
Jaime Casanova wrote:
But if VACUUM fixes the wraparound issue, shouldn't even a badly
configured autovacuum make the wraparound not be a problem in 8.1? Or 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...
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.
Matt
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.
I believe 8.1 will actually stop allowing transactions if a wraparound
is going to occur.
Joshua D. Drake
Show quoted text
Matt
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
"Joshua D. Drake" <jd@commandprompt.com> writes:
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.
I believe 8.1 will actually stop allowing transactions if a wraparound
is going to occur.
Yeah. I don't see any value to running autovac *only* for this purpose.
regards, tom lane
On Sat, Dec 31, 2005 at 10:34:51AM -0500, Matthew T. O'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:
spawn_autovacuum_on_wraparound_danger=true
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.
Only question, does it rely on other options (like stats) to work for
this purpose?
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.