pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Solving 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>
  2005-10-17 17:04 ` Re: Solving OID wrap-around in 7.4 DB? Chris Browne <cbbrowne@acm.org>
  2005-10-18 16:13 ` Re: Solving OID wrap-around in 7.4 DB? Andrew Sullivan <ajs@crankycanuck.ca>
  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-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   ` Re: Solving OID wrap-around in 7.4 DB? Scott Marlowe <smarlowe@g2switchworks.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-16 11:28 Solving OID wrap-around in 7.4 DB? Jeff Boes <jeff@endpoint.com>
  2005-10-17 17:04 ` Re: Solving OID wrap-around in 7.4 DB? Chris Browne <cbbrowne@acm.org>
@ 2005-10-17 19:50   ` Scott Marlowe <smarlowe@g2switchworks.com>
  2005-10-17 21:39     ` Re: Solving OID wrap-around in 7.4 DB? Michael Fuhr <mike@fuhr.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-16 11:28 Solving OID wrap-around in 7.4 DB? Jeff Boes <jeff@endpoint.com>
  2005-10-17 17:04 ` Re: Solving OID wrap-around in 7.4 DB? Chris Browne <cbbrowne@acm.org>
  2005-10-17 19:50   ` Re: Solving OID wrap-around in 7.4 DB? Scott Marlowe <smarlowe@g2switchworks.com>
@ 2005-10-17 21:39     ` Michael Fuhr <mike@fuhr.org>
  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-16 11:28 Solving OID wrap-around in 7.4 DB? Jeff Boes <jeff@endpoint.com>
@ 2005-10-18 16:13 ` Andrew Sullivan <ajs@crankycanuck.ca>
  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