Hosting Provided By

Re: Backup Postgressql

From: Craig Sanders <cas(at)taz.net.au>
Date: Wed Sep 26 2007 - 17:25:18 EDT

On Wed, Sep 26, 2007 at 09:17:22PM +0200, Turbo Fredriksson wrote:
> Quoting Tinus Nijmeijers <mlists@deephosting.com>:
>
> > PGPASSWORD=
> > export PGPASSWORD
>
> Or just:
>
> unset PGPASSWORD

yep, to both responses.

i find it's also useful to backup each db individually (with the table definitions aka "schema" in one file, and the data in another), and wrote the following script (which runs nightly from user postgres' crontab)

having the table defs and data backed up separately allows you to easily re-create just the tables OR re-import the data (or do both, one after the other) - without having to make temp copies of the backup text file and edit it in vi or whatever. useful not only for backup/restore purposes but also for moving/duplicating a database from one server to another.

the script also uses savelog to keep the last few versions of the backup dumps.

feel free to use and modify it to suit your needs. e.g. setting PGPASSWORD and backing up databases from remote hosts (or TO a remote host via ssh) are all pretty easy.

oh, and i use the "-D" option to pg_dump when dumping the data because i find the "INSERT INTO..." style more reliable than tab-delimited dumps, even if it does make for much slower restores and bigger dump files (almost negligible after gzip compresses them).

---cut here---
#! /bin/bash

# backup-databases.sh
# by Craig Sanders  2001
#
# this script is licensed under the terms of the GNU GPL.

Do you need help?

DUMPALL='/usr/bin/pg_dumpall'
PGDUMP='/usr/bin/pg_dump'

# get list of databases to backup
DBS=$(echo '\l' | psql -t | grep -v 'template[01]' | awk '{print $1}')

# $DIR should be owned by user postgres, and rwx by postgres. DIR='/var/backups/postgres'
cd $DIR

# first dump entire postgres database, including pg_shadow, to one big file. DBOUT=$DIR/db.out.gz
[ -e $DBOUT ] && savelog -m 640 -c 2 -l $DBOUT $DUMPALL -D | gzip -9 >$DBOUT

# now loop through each individual database and backup the schema and data separately for database in $DBS; do

# rotate old copies of backup databases SCHEMA=$DIR/$database.schema.gz

    DATA=$DIR/$database.data.gz
    [ -e $SCHEMA ] && savelog -m 640 -c 2 -l $SCHEMA
    [ -e $DATA ] && savelog -m 640 -c 2 -l $DATA

# export data from postgres databases to plain text $PGDUMP -c -s $database | gzip -9 > $SCHEMA $PGDUMP -a -D $database | gzip -9 > $DATA done
---cut here---

craig

-- 
craig sanders 

BOFH excuse #72:

Satan did it


-- 
To UNSUBSCRIBE, email to debian-isp-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

Received on Wed Sep 26 17:33:18 2007

Do you need more help?

This message: [ Message body ]
Next message: Nate Duehr: "Re: Changing the default simlink to sh [was: Password file with over 3000 users.]"
Previous message: Nate Duehr: "Re: Changing the default simlink to sh [was: Password file with over 3000 users.]"
In reply to: Turbo Fredriksson: "Re: Backup Postgressql"
Next in thread: Marc Schiffbauer: "Re: Backup Postgressql"

Contemporary messages sorted: [ By Date ] [ By Thread ] [ By Subject ] [ By Author ] [ By messages with attachments ]

This archive was generated by hypermail 2.1.8 : Sun Oct 07 2007 - 00:08:15 EDT