Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id ACFAE64FCBA for ; Fri, 5 Sep 2008 13:52:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 78173-10 for ; Fri, 5 Sep 2008 13:52:21 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 291BB64FF09 for ; Fri, 5 Sep 2008 13:52:20 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m85GqH24026691; Fri, 5 Sep 2008 12:52:18 -0400 (EDT) To: Arctic Toucan cc: pgsql-admin@postgresql.org Subject: Re: How can I avoid Frozenxid wraparound on failover to a standby(PITR) database? In-reply-to: References: Comments: In-reply-to Arctic Toucan message dated "Fri, 05 Sep 2008 11:28:29 -0400" Date: Fri, 05 Sep 2008 12:52:17 -0400 Message-ID: <26690.1220633537@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200809/46 X-Sequence-Number: 30139 Arctic Toucan writes: > I next started looking at the age(refrozenxid) of the tables in my DB, and was surprised to see that over 4000 of the 5000 tables in this DB had an age over 2Billion. So thats 4000 tables representing over a terabyte of data that need to be vacuumed! I am now vacuuming those tables one at a time, which is taking a long time(This is a scripted process). So there is no way I could have vacuumed the tables quickly enough even given a warning of impending XID wraparound. > Looking through the support mailing lists(Bugs) I see some discussion about the frozenxid updates on the master not being propogated to the slave through the WAL logs, and comments from Tom, Alvaro and Heikki suggesting that they were looking into a solution for PG 8.3 and needed a way around the problem in PG 8.2. Hmm ... that did get fixed in 8.2 http://archives.postgresql.org/pgsql-committers/2006-11/msg00026.php so I'm a bit confused about what happened here. What were your autovacuum settings on the old master? Particularly autovacuum_freeze_max_age? regards, tom lane