Thread: 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/ > 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.
Attachment
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/ > 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.
Attachment
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 > > Matt > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/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/ > 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.