|
Home > Archive > PostgreSQL Discussion > December 2005 > solving wraparound
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
solving wraparound
|
|
| Jaime Casanova 2005-12-26, 8:23 pm |
| 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 ;)
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Martijn van Oosterhout 2005-12-26, 8:23 pm |
| 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.
| |
| Jaime Casanova 2005-12-26, 8:23 pm |
| On 12/26/05, Martijn van Oosterhout <kleptog@svana.org> wrote:
> On Mon, Dec 26, 2005 at 03:21:03PM -0500, Jaime Casanova wrote:
>
> 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/
>
>
>
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
| |
| Martijn van Oosterhout 2005-12-26, 8:23 pm |
| 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.
| |
| Tom Lane 2005-12-26, 8:23 pm |
| 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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Lic. Martin Marques 2005-12-27, 7:23 am |
| 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
---------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
| |
| Jaime Casanova 2005-12-27, 9:23 am |
| On 12/27/05, Lic. Martin Marques <martin@bugs.unl.edu.ar> wrote:
> On Mon, 26 Dec 2005, Tom Lane wrote:
>
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 ;)
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Matthew T. O'Connor 2005-12-31, 11:23 am |
| Jaime Casanova wrote:
> 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_o
nly. Not
sure I like the idea, but thought it might be worth some discussion.
Matt
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Joshua D. Drake 2005-12-31, 8:23 pm |
|
> 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_o
nly. 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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
| |
| Tom Lane 2005-12-31, 8:23 pm |
| "Joshua D. Drake" <jd@commandprompt.com> writes:
[color=darkred]
> 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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Martijn van Oosterhout 2005-12-31, 8:23 pm |
| 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_o
nly. 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=tr
ue
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.
|
|
|
|
|