X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F0280D7F6B for ; Mon, 17 Oct 2005 15:08:19 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 66635-03 for ; Mon, 17 Oct 2005 18:08:11 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 20742D7CEA for ; Mon, 17 Oct 2005 15:08:12 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id j9HI8Bob068284 for ; Mon, 17 Oct 2005 18:08:11 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id j9HI42U7066984 for pgsql-admin@postgresql.org; Mon, 17 Oct 2005 18:04:02 GMT (envelope-from news) From: Chris Browne X-Newsgroups: pgsql.admin Subject: Re: Solving OID wrap-around in 7.4 DB? Date: Mon, 17 Oct 2005 13:04:59 -0400 Organization: cbbrowne Computing Inc Lines: 32 Message-ID: <60fyr0gjlw.fsf@dba2.int.libertyrms.com> References: <43523940.6080403@endpoint.com> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.17 (Jumbo Shrimp, linux) Cancel-Lock: sha1:Vwvk0gplw67zfHVxaCdGUKSsAOc= To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=-2.483 required=5 tests=[ALL_TRUSTED=-2.82, AWL=0.337] X-Spam-Level: X-Archive-Number: 200510/192 X-Sequence-Number: 19264 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