pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSolving OID wrap-around in 7.4 DB?
5+ messages / 5 participants
[nested] [flat]
* Solving OID wrap-around in 7.4 DB?
@ 2005-10-16 11:28 Jeff Boes <jeff@endpoint.com>
0 siblings, 2 replies; 5+ messages in thread
From: Jeff Boes @ 2005-10-16 11:28 UTC (permalink / raw)
To: pgsql-admin
On behalf of a previous employer who, due to major downsizing, is left
without an answer to this:
What's the best way to treat impending OID wrap-around in a 7.4.8
database? This DB has been online for about 18 months, and is expected
to hit the dreaded wrap-around in about a month. At an application
level, there's some small chance that OID wrap will cause failures:
there's code that writes rows, and (for tables without primary keys)
relies on DBD::Pg's pg_oid_status method to identify the last INSERT.
(The vast majority of the code does not rely on this, but there's a tiny
fraction that does, and rewriting the code to remove this is not an
option due to the aforementioned downsizing.)
My immediate take on this is that the only sure way to avoid the problem
would be to dump and reload the database. Due to the size of the
database, this is likely to take most of a day. Any other options?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Solving OID wrap-around in 7.4 DB?
@ 2005-10-17 17:04 Chris Browne <cbbrowne@acm.org>
parent: Jeff Boes <jeff@endpoint.com>
1 sibling, 1 reply; 5+ messages in thread
From: Chris Browne @ 2005-10-17 17:04 UTC (permalink / raw)
To: pgsql-admin
jeff@endpoint.com (Jeff Boes) writes:
> On behalf of a previous employer who, due to major downsizing, is left
> without an answer to this:
>
> What's the best way to treat impending OID wrap-around in a 7.4.8
> database? This DB has been online for about 18 months, and is expected
> to hit the dreaded wrap-around in about a month. At an application
> level, there's some small chance that OID wrap will cause failures:
> there's code that writes rows, and (for tables without primary keys)
> relies on DBD::Pg's pg_oid_status method to identify the last INSERT.
> (The vast majority of the code does not rely on this, but there's a tiny
> fraction that does, and rewriting the code to remove this is not an
> option due to the aforementioned downsizing.)
>
> My immediate take on this is that the only sure way to avoid the
> problem would be to dump and reload the database. Due to the size of
> the database, this is likely to take most of a day. Any other
> options?
Another method would be to use replication to load the data into a
fresh DB instance.
You could, in principle, use Slony-I to do this; that is an intended
sort of "use case."
--
output = ("cbbrowne" "@" "acm.org")
http://www3.sympatico.ca/cbbrowne/slony.html
"What if you slept? And what if, in your sleep, you dreamed?
And what if, in your dream, you went to heaven and there
plucked a strange and beautiful flower? And what if, when
you awoke, you had the flower in your hand? Ah, what then?"
--Coleridge
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Solving OID wrap-around in 7.4 DB?
@ 2005-10-17 19:50 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Chris Browne <cbbrowne@acm.org>
0 siblings, 1 reply; 5+ messages in thread
From: Scott Marlowe @ 2005-10-17 19:50 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-admin
On Mon, 2005-10-17 at 12:04, Chris Browne wrote:
> jeff@endpoint.com (Jeff Boes) writes:
> > On behalf of a previous employer who, due to major downsizing, is left
> > without an answer to this:
> >
> > What's the best way to treat impending OID wrap-around in a 7.4.8
> > database? This DB has been online for about 18 months, and is expected
> > to hit the dreaded wrap-around in about a month. At an application
> > level, there's some small chance that OID wrap will cause failures:
> > there's code that writes rows, and (for tables without primary keys)
> > relies on DBD::Pg's pg_oid_status method to identify the last INSERT.
> > (The vast majority of the code does not rely on this, but there's a tiny
> > fraction that does, and rewriting the code to remove this is not an
> > option due to the aforementioned downsizing.)
> >
> > My immediate take on this is that the only sure way to avoid the
> > problem would be to dump and reload the database. Due to the size of
> > the database, this is likely to take most of a day. Any other
> > options?
>
> Another method would be to use replication to load the data into a
> fresh DB instance.
>
> You could, in principle, use Slony-I to do this; that is an intended
> sort of "use case."
I thought that with 7.4 all you needed was to vacuum all databases (full
maybe???) to prevent oid wraparound.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Solving OID wrap-around in 7.4 DB?
@ 2005-10-17 21:39 Michael Fuhr <mike@fuhr.org>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 5+ messages in thread
From: Michael Fuhr @ 2005-10-17 21:39 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Chris Browne <cbbrowne@acm.org>; pgsql-admin
On Mon, Oct 17, 2005 at 02:50:01PM -0500, Scott Marlowe wrote:
> I thought that with 7.4 all you needed was to vacuum all databases (full
> maybe???) to prevent oid wraparound.
Sure you're not confusing oid wraparound with transaction ID wraparound?
--
Michael Fuhr
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Solving OID wrap-around in 7.4 DB?
@ 2005-10-18 16:13 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Jeff Boes <jeff@endpoint.com>
1 sibling, 0 replies; 5+ messages in thread
From: Andrew Sullivan @ 2005-10-18 16:13 UTC (permalink / raw)
To: pgsql-admin
On Sun, Oct 16, 2005 at 07:28:00AM -0400, Jeff Boes wrote:
> On behalf of a previous employer who, due to major downsizing, is left
> without an answer to this:
>
> What's the best way to treat impending OID wrap-around in a 7.4.8
> database? This DB has been online for about 18 months, and is expected
> to hit the dreaded wrap-around in about a month. At an application
How many OIDs have been recovered? If the answer is "none" (i.e. the
database doesn't have many deletes), then there isn't likely much you
can do about it -- reloading the database is going to cause the same
problem.
As Chris says in another message, you can use Slony to replicate the
database, in order not to have a long outage. Note, too, that since
you say most tables don't actually use this pg_oid_status method, you
can get away with creating most of the tables without OIDs, which
will stave off your problem for a while. That'd probably even help
if the database mostly just grows, depending (of course) on where the
growth is.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Information security isn't a technological problem. It's an economics
problem.
--Bruce Schneier
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-10-18 16:13 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-16 11:28 Solving OID wrap-around in 7.4 DB? Jeff Boes <jeff@endpoint.com>
2005-10-17 17:04 ` Chris Browne <cbbrowne@acm.org>
2005-10-17 19:50 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-10-17 21:39 ` Michael Fuhr <mike@fuhr.org>
2005-10-18 16:13 ` Andrew Sullivan <ajs@crankycanuck.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox