From owner-freebsd-questions@FreeBSD.ORG Mon Jan 14 15:55:32 2008 Return-Path: Delivered-To: freebsd-questions@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id CA98B16A41A for ; Mon, 14 Jan 2008 15:55:32 +0000 (UTC) (envelope-from david.robillard@gmail.com) Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.239]) by mx1.freebsd.org (Postfix) with ESMTP id 66ABE13C4CC for ; Mon, 14 Jan 2008 15:55:32 +0000 (UTC) (envelope-from david.robillard@gmail.com) Received: by nz-out-0506.google.com with SMTP id l8so894545nzf.13 for ; Mon, 14 Jan 2008 07:55:31 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:mime-version:content-type:content-transfer-encoding:content-disposition; bh=ymCy9OhwvuUQ1HcC/X3Il2q5o2SQjYTydX8MQZ1UINA=; b=mBUEyMnUshVkad3PxTHpZ/5whzYIlfJpIJAPTKnT07HW3n4CyiDYDVGv00KPA2zAubBErr+Bm1+vUEKntNFuxT39W7W0md4ZQ6assYe2rI/GemIiw3wBS02DXykDTy53NYWhs0gGBZldHJoJoTGm7wRNpLq8nHmcJ0Be3NZt95c= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:mime-version:content-type:content-transfer-encoding:content-disposition; b=M8bTpCQauuuhXezs82ahgikiU83F6/IvCDDzi0obubhPsNnFvF9HIRDR5dH95uMYR6hv61i8Z2vvwTf73K/XMbaBwQmMux+q3yH9VVyzTZUsxZk4s3XvrQi0HnvtV4WvnJUy/6npz+h9fwxSmLlA4f5IpoOsA3BjjD82utERzQI= Received: by 10.141.172.6 with SMTP id z6mr3891885rvo.80.1200326130732; Mon, 14 Jan 2008 07:55:30 -0800 (PST) Received: by 10.141.186.16 with HTTP; Mon, 14 Jan 2008 07:55:30 -0800 (PST) Message-ID: <226ae0c60801140755h154f0a96r1f50ab88a02a7193@mail.gmail.com> Date: Mon, 14 Jan 2008 10:55:30 -0500 From: "David Robillard" To: Albert.Shih@obspm.fr MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Cc: FreeBSD Questions Subject: Re: How backup huge pgsql ? X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 14 Jan 2008 15:55:32 -0000 > I want to known how can I make backup of huge postgresql database (huge mean ~ 2To). > > I can stop the access of the database during N>>1 hours. > > Any idea about this ? I came around this particular problem by setting up a read only mirror of an Oracle instance using Oracle DataGuard. Of course the product is Oracle-specific, but the idea should apply to PostgreSQL databases as well and it"s what we're in the process of installing here. The idea is to setup an identical but read-only copy of the production database on a seperate machine. This read-only copy is kept in sync with the production database using the various PostgreSQL High-Availability features (discussed here postgresql.org/docs/8.2/static/high-availability.html) Such as a Master-Slave Replication or a Synchronous Multi-Master Replication. Say you're using a Master-Slave Replication. With this setup, you can stop the Master-Slave replication before running the backup on the read-only copy on the slave machine. This way you have a consistent view of your data while you backup and the production database is still online. Once your backup is over, you simply turn on the replication again to update your slave's data with what has changed on the master while the replication was offline. Simple and effective. Beware, you will take a performance hit when you turn replication on. What's more, since you now have a read-only database, you can use it in your pre-production and test environments without any impact on your production systems. HTH, David -- David Robillard UNIX systems administrator & Oracle DBA CISSP, RHCE & Sun Certified Security Administrator Montreal: +1 514 966 0122