From owner-freebsd-questions@FreeBSD.ORG  Sat Oct 29 03:06:07 2005
Return-Path: <owner-freebsd-questions@FreeBSD.ORG>
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 9C7FB16A41F
	for <freebsd-questions@freebsd.org>;
	Sat, 29 Oct 2005 03:06:07 +0000 (GMT)
	(envelope-from lists@stringsutils.com)
Received: from zoraida.natserv.net (p65-147.acedsl.com [66.114.65.147])
	by mx1.FreeBSD.org (Postfix) with ESMTP id 20BB943D46
	for <freebsd-questions@freebsd.org>;
	Sat, 29 Oct 2005 03:06:05 +0000 (GMT)
	(envelope-from lists@stringsutils.com)
Received: from [127.0.0.1] (localhost.natserv.net [127.0.0.1])
	by zoraida.natserv.net (Postfix) with ESMTP id B1BC77DAE;
	Fri, 28 Oct 2005 23:06:04 -0400 (EDT)
Message-ID: <4362E71C.9040803@stringsutils.com>
Date: Fri, 28 Oct 2005 23:06:04 -0400
From: Francisco Reyes <lists@stringsutils.com>
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050911)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Pat Maddox <pergesu@gmail.com>
References: <810a540e0510241002l56e9e0d1ra4c4f949da4a8fb9@mail.gmail.com>
In-Reply-To: <810a540e0510241002l56e9e0d1ra4c4f949da4a8fb9@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Cc: FreeBSD Questions <freebsd-questions@freebsd.org>
Subject: Re: Backing up postgresql data
X-BeenThere: freebsd-questions@freebsd.org
X-Mailman-Version: 2.1.5
Precedence: list
List-Id: User questions <freebsd-questions.freebsd.org>
List-Unsubscribe: <http://lists.freebsd.org/mailman/listinfo/freebsd-questions>, 
	<mailto:freebsd-questions-request@freebsd.org?subject=unsubscribe>
List-Archive: <http://lists.freebsd.org/pipermail/freebsd-questions>
List-Post: <mailto:freebsd-questions@freebsd.org>
List-Help: <mailto:freebsd-questions-request@freebsd.org?subject=help>
List-Subscribe: <http://lists.freebsd.org/mailman/listinfo/freebsd-questions>, 
	<mailto:freebsd-questions-request@freebsd.org?subject=subscribe>
X-List-Received-Date: Sat, 29 Oct 2005 03:06:07 -0000

Pat Maddox wrote:

>I've got postgresql 8 running on my system, and want to do nightly
>backups of the database.
>

I recommend
1- Nightly dumpall
2- More frequent backup of databases that change often.

You can setup a script like:
#!/bin/sh
PGUSER=<user>
PGPASSWORD=<passwd>
export PGUSER
export PGPASSWORD
/usr/local/bin/pg_dumpall |/usr/bin/bzip2 -c > <backup_file>

Of course you can change it to a "sh" script. :-)

For the single DB ones you can use
#!/bin/sh
PGUSER=<user>
PGPASSWORD=<password>
export PGUSER
export PGPASSWORD

cd <backup dir>
echo Dumping database to BACKUP directory
pg_dump <db>  > db.sql
echo Making tar file
tar -cyvf `date "+%Y%m%d"`-db.tbz db.sql


Hope that helps.