From owner-freebsd-questions@FreeBSD.ORG Mon Oct 24 17:45:20 2005 Return-Path: X-Original-To: freebsd-questions@freebsd.org Delivered-To: freebsd-questions@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 36D0316A41F for ; Mon, 24 Oct 2005 17:45:20 +0000 (GMT) (envelope-from algould@datawok.com) Received: from smtpauth08.mail.atl.earthlink.net (smtpauth08.mail.atl.earthlink.net [209.86.89.68]) by mx1.FreeBSD.org (Postfix) with ESMTP id C622143D48 for ; Mon, 24 Oct 2005 17:45:19 +0000 (GMT) (envelope-from algould@datawok.com) Received: from [206.255.31.21] (helo=grokwell.org) by smtpauth08.mail.atl.earthlink.net with asmtp (TLSv1:AES256-SHA:256) (Exim 4.34) id 1EU6Nm-00039A-L0; Mon, 24 Oct 2005 13:45:14 -0400 Date: Mon, 24 Oct 2005 12:46:02 -0500 From: "Andrew L. Gould" To: Pat Maddox Message-ID: <20051024124602.15a69f63@grokwell.org> In-Reply-To: <810a540e0510241002l56e9e0d1ra4c4f949da4a8fb9@mail.gmail.com> References: <810a540e0510241002l56e9e0d1ra4c4f949da4a8fb9@mail.gmail.com> X-Mailer: Sylpheed-Claws 1.9.11 (GTK+ 2.6.8; i386-portbld-freebsd5.4) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-ELNK-Trace: ee791d459e3d6817d780f4a490ca69563f9fea00a6dd62bcc087ad1f3505550808233f598f116e69350badd9bab72f9c350badd9bab72f9c350badd9bab72f9c X-Originating-IP: 206.255.31.21 Cc: FreeBSD Questions Subject: Re: Backing up postgresql data 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, 24 Oct 2005 17:45:20 -0000 On Mon, 24 Oct 2005 11:02:11 -0600 Pat Maddox wrote: > I've got postgresql 8 running on my system, and want to do nightly > backups of the database. I took a look at the docs, and there are a > few methods of doing backups - pg_dump, file system level, and using > WAL. I just want to be able to rsync my data every night, maybe every > few hours, so that I can recover it if there are any problems...so > what's the best way to do this. Are there any scripts or utilities > available to make my life easy? > > Thanks, > Pat Are your databases very large or extremely active? If not, a nightly pg_dump (or pg_dumpall) should suffice. I've found the combination of PostgreSQL and FreeBSD to be very stable. If you need more frequent backups, you might consider one of the replication solutions for PostgreSQL. Here are links to a couple of them that I found at PgFoundry: http://pgfoundry.org/projects/slony1/ http://pgfoundry.org/projects/pgpool/ I haven't used any of the replication solutions. Hopefully, someone else will chime in with additional information/advice. Best of luck, Andrew Gould