To: pgsql-admin@postgresql.org
From: "Milen A. Radev" <milen@radev.net>
Subject: Re: [GENERAL] Encoding problem using pg_dumpall
Date: Sun, 01 Feb 2009 02:59:35 +0200
Lines: 58
Message-ID: <gm2s5m$9g5$1@ger.gmane.org>
References: <75E4C42D37E6A74E9FB57C2E9F1300D60107073E@tiger.nexperience.com>	
	<200901292014.29195@zmi.at> <14708.1233277601@sss.pgh.pa.us>	
	<200901301223.49703@zmi.at>
	<9adabaf40901311547r1444e0c4m4797da5b30671d63@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
In-Reply-To: <9adabaf40901311547r1444e0c4m4797da5b30671d63@mail.gmail.com>

Cliff Pratt написа:
[...]
> You should be able to knock up a simple script in bash, perl or python
> to do what you want.
>
> Sort of like:
>
> Use 'psql' to get a list of the databases
> Sanitize the list.
> Loop through the list doing backup.
> At the end zip them all.
>
> With a little bit of trickery you should be able to zip them as you go.
[...]


#!/bin/bash

PSQL=/usr/bin/psql
PG_DUMP=/usr/bin/pg_dump
PG_DUMPALL=/usr/bin/pg_dumpall

if [ $# -eq 0 ] ; then
        BACKUPDIR=/home/db_backup/dumps
elif [ $# -ne 1 ] ; then
        echo -e "Usage: $0 [directory]\n"
        exit 1
else
        BACKUPDIR=$1
fi

if [ ! -d $BACKUPDIR ] ; then
        if ! mkdir $BACKUPDIR ; then
                echo -e "Cannot create backup directory: $BACKUPDIR\n"
                exit 2
        fi
fi


echo Starting at `date "+%Y-%m-%d %H:%M:%S"`
echo -e -n "Dumping globals...\t\t"
$PG_DUMPALL -U postgres --globals-only > $BACKUPDIR/pg_globals.sql
echo "done."

for db in `$PSQL -U postgres -d template1 -t -c "SELECT datname FROM pg_catalog.pg_database WHERE datname "\!"~ 'template(0|1)';"`
do
        echo -n -e "Dumping database $db...\t\t"
        $PG_DUMP -U postgres --format=c $db > $BACKUPDIR/$db.dump
        echo "done."
done
echo Ended at `date "+%Y-%m-%d %H:%M:%S"`


[...]


-- 
Milen A. Radev