Re: database backup - Mailing list pgsql-general

Well... the standard tools don't provide for that. Perhaps someone else
has written other tools that implement incremantals but I doubt it.
Perhaps some of the changes that are due for 7.3 in point in time recovery
will provide for that. In the mean time you're going to take full
snapshots and like it.

Here's a copy of my own automated backup script. You'll note that it
does each database separately; first the schema then the data + blobs.
Lastly the cluster's globals.

#!/bin/sh
rm -rf /tmp/mkiso-data.*
TMPDIR=`mktemp -d /tmp/mkiso-data.XXX`

# Create the temporary directory
mkdir $TMPDIR/cd

cd /
tar cpvXf - \
    / \
    /home/[d-kq-z]* \
    /var/[a-lnq-z]* \
    /var/mail \
    /var/msgs \
    /var/preserve \
    | gzip -c9 > $TMPDIR/cd/data.tgz

# Backup PostgreSQL separately

# dump each database schema/data separately
su -l postgresql -c "psql -At -F ' ' -U postgresql -d template1 <<__END__
SELECT datname FROM pg_database WHERE datallowconn;
__END__
" | while read DB; do
    echo "PostgreSQL db $DB"
    mkdir -p $TMPDIR/cd/postgres/$DB

    # schema
    su -l postgresql -c "pg_dump -Cs -F c -Z 9 -S postgresql $DB" \
         > $TMPDIR/cd/postgres/$DB/schema.pg

    # data
    su -l postgresql -c "pg_dump -bd -F c -Z 9 -S postgresql $DB" \
         > $TMPDIR/cd/postgres/$DB/data.pg
done
# dump all globals (users/groups)
su -l postgresql -c "pg_dumpall -g" \
 > $TMPDIR/cd/postgres/globals.sql


# Backup MySQL separately


cd $TMPDIR/cd
mkisofs -o $TMPDIR/image.iso -v . 1> /dev/null
cd $TMPDIR
rm -rf $TMPDIR/cd

/root/bin/burnimage $TMPDIR/image.iso

echo Bootable system cd is at $TMPDIR/image.iso

Joshua b. Jore ; http://www.greentechnologist.org

On Sat, 6 Jul 2002, Sanjeev Rathore wrote:

> Hi,
>
> I am looking into how I should backup my PostgreSQL
> database.  Is there a way to do incremental backup?  I
> think incremental back seems to be a better solution
> in the long run, because it is more scalable.  Does
> anybody have any suggestions as to how I should go
> about backing up my database?
>
> thanks,
> Sanjeev
>
> __________________________________________________
> Do You Yahoo!?
> Sign up for SBC Yahoo! Dial - First Month Free
> http://sbc.yahoo.com
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>