Backing up postgresql data

[Francisco Reyes]

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.