From owner-freebsd-questions@FreeBSD.ORG Sat Jan 12 19:48:37 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 DCD1516A46E for ; Sat, 12 Jan 2008 19:48:37 +0000 (UTC) (envelope-from peter.schuller@infidyne.com) Received: from smtp.infidyne.com (ds9.infidyne.com [88.80.6.206]) by mx1.freebsd.org (Postfix) with ESMTP id 8320313C4D9 for ; Sat, 12 Jan 2008 19:48:37 +0000 (UTC) (envelope-from peter.schuller@infidyne.com) Received: from c-8216e555.03-51-73746f3.cust.bredbandsbolaget.se (c-8216e555.03-51-73746f3.cust.bredbandsbolaget.se [85.229.22.130]) by smtp.infidyne.com (Postfix) with ESMTP id 2B8A576261; Sat, 12 Jan 2008 20:48:36 +0100 (CET) From: Peter Schuller To: freebsd-questions@freebsd.org, Albert.Shih@obspm.fr Date: Sat, 12 Jan 2008 20:48:45 +0100 User-Agent: KMail/1.9.7 References: <20080110221058.GA81742@pcjas.obspm.fr> In-Reply-To: <20080110221058.GA81742@pcjas.obspm.fr> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="nextPart2465871.3KpF1vovvL"; protocol="application/pgp-signature"; micalg=pgp-sha1 Content-Transfer-Encoding: 7bit Message-Id: <200801122048.54367.peter.schuller@infidyne.com> Cc: 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: Sat, 12 Jan 2008 19:48:37 -0000 --nextPart2465871.3KpF1vovvL Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline > 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 ? pg_dump should work as usual. No need to stop database access since read-on= ly=20 access won't block anything else and it won't be blocked by other processes. That said, pg_dump:ing a database that is 2 terrabytes in size is going to= =20 take some significant time. If you want to maintain frequent backups you ma= y=20 want to look into using point in time recovery and WAL archiving. See: http://www.postgresql.org/docs/8.2/static/continuous-archiving.html Also the very long transaction used for the backup will prevent vacuuming f= rom=20 freeing tuples for the duration of the backup. If you have tables that rely= =20 on very frequent vacuuming for performance, those may be affected. =2D-=20 / Peter Schuller PGP userID: 0xE9758B7D or 'Peter Schuller ' Key retrieval: Send an E-Mail to getpgpkey@scode.org E-Mail: peter.schuller@infidyne.com Web: http://www.scode.org --nextPart2465871.3KpF1vovvL Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part. -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.4 (FreeBSD) iD8DBQBHiRmmDNor2+l1i30RApeRAJ9FVb4Fg+5+GvD+974fvF6v+vWGmwCgwGJh 1CfrDVkHjkhbTdqDiJboE60= =HXqH -----END PGP SIGNATURE----- --nextPart2465871.3KpF1vovvL--