Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id CE3621337B4A for ; Wed, 24 Nov 2010 06:00:17 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 83741-01-8 for ; Wed, 24 Nov 2010 10:00:08 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-px0-f174.google.com (mail-px0-f174.google.com [209.85.212.174]) by mail.postgresql.org (Postfix) with ESMTP id A6EE41337B90 for ; Wed, 24 Nov 2010 06:00:06 -0400 (AST) Received: by pxi15 with SMTP id 15so2311638pxi.19 for ; Wed, 24 Nov 2010 02:00:06 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:subject:mime-version :content-type:from:in-reply-to:date:cc:content-transfer-encoding :message-id:references:to:x-mailer; bh=jGoa6Pd1gUSdNBDG1V/FCjlSBwkKbuipNzfJ5kJIEzg=; b=se3pbQzvj7/JPYJk/GU0zGADxfSqrobDbOq1ow88+MGjLT3KjjOoQfSihJ3FSroXXX YhPFM258X7zugttSBemuFGe/jS6QiyFVTWLh4avWzqSStRRxgm3XiWpDcOyiSvNfEGYy IAroqd4To0abTvZKHDvyataJoJ2vf/qE32wkQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=subject:mime-version:content-type:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to:x-mailer; b=LS0m8rIrgPjxWlFeTWr+VzeBzoyXwNv7msshsyrIaD6DIMFd0oBsj1Omfupr5Yb9oJ t+BzLUoupzE6ydXTR6OkbbTCPw6d18tzmnAkIPKdqIukijzvMSdK7gOPGBr9kNPacY3j 8/y74ekC1Jdj+kKemaQwRPZkLq6JK/NPHdsbM= Received: by 10.142.231.12 with SMTP id d12mr8155269wfh.73.1290592806175; Wed, 24 Nov 2010 02:00:06 -0800 (PST) Received: from 192-168-1-8.tpgi.com.au (220-244-225-66.static.tpgi.com.au [220.244.225.66]) by mx.google.com with ESMTPS id q13sm9191397wfc.17.2010.11.24.02.00.03 (version=TLSv1/SSLv3 cipher=RC4-MD5); Wed, 24 Nov 2010 02:00:04 -0800 (PST) Subject: Re: pg_dump and XID limit Mime-Version: 1.0 (Apple Message framework v1081) Content-Type: text/plain; charset=us-ascii From: Elliot Chance In-Reply-To: <6420.1290578869@sss.pgh.pa.us> Date: Wed, 24 Nov 2010 20:59:59 +1100 Cc: pgsql-admin@postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: References: <6420.1290578869@sss.pgh.pa.us> To: Tom Lane X-Mailer: Apple Mail (2.1081) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201011/211 X-Sequence-Number: 36177 On 24/11/2010, at 5:07 PM, Tom Lane wrote: > Elliot Chance writes: >> This is a hypothetical problem but not an impossible situation. Just = curious about what would happen. >=20 >> Lets say you have an OLTP server that keeps very busy on a large = database. In this large database you have one or more tables on super = fast storage like a fusion IO card which is handling (for the sake of = argument) 1 million transactions per second. >=20 >> Even though only one or a few tables are using almost all of the IO, = pg_dump has to export a consistent snapshot of all the tables to = somewhere else every 24 hours. But because it's such a large dataset (or = perhaps just network congestion) the daily backup takes 2 hours. >=20 >> Heres the question, during that 2 hours more than 4 billion = transactions could of occurred - so what's going to happen to your = backup and/or database? >=20 > The DB will shut down to prevent wraparound once it gets 2 billion = XIDs > in front of the oldest open snaphot. >=20 > regards, tom lane Wouldn't that mean at some point it would be advisable to be using 64bit = transaction IDs? Or would that change too much of the codebase?=