How to backup and restore postgres, How to backup and restore postgres

Have you tried phpPgAdmin?

http://phppgadmin.sourceforge.net/doku.php?id=start

http://www.mactasia.co.uk/using-postgresql-in-lion-server#more-775

Also have you considered doing a RAID 1? At least this can protect you from hard drive failure.

or a automated backup script for postgresql?

Take my little script here and adjust it to your requirements. I got the base of this script somwhere else and enhanced it myself. Read some explanations below:

# #!/bin/bash

if (test "`whoami`" != "root") then

echo "You need to be root to start this"

exit 1

fi

PG_PORT="5432"

PG_USER="_postgres"

PG_BIN="/usr/bin/"

ACT_DATE=$(date '+%y-%m-%d')

EXP_DATE=$(date -v -2w '+%y-%m-%d')

BACKUP_DIR="/Backups/postgres"

LOGFILE=$BACKUP_DIR"/_pg_backup.log"

COMPRESSION="9"

PG_CON="-U $PG_USER"

BACKUP_OPTIONS="$PG_CON -b -C -F c -Z $COMPRESSION"

VACUUM_OPTIONS="$PG_CON -eq"

echo "***** DB_BACKUP $ACT_DATE *****" >>$LOGFILE

for db in `$PG_BIN/psql $PG_CON -lt | sed /\eof/p | grep -v = | awk {'print $1'}`

do

# vacuum

if [ "X" == "X$db" ]; then

continue;

fi

echo $(date '+%c')" -- vacuuming database $db" >> $LOGFILE

if $PG_BIN/vacuumdb $VACUUM_OPTIONS $db

then

#echo "OK!" >>$LOGFILE

sleep 1

else

echo "No Vacuum in database $db!" >>$LOGFILE

fi

# backup

echo $(date '+%c')" -- backing up database $db" >>$LOGFILE

if $PG_BIN/pg_dump $BACKUP_OPTIONS -f $BACKUP_DIR/$db-$ACT_DATE.pgdump $db

then

if [ -e $BACKUP_DIR/$db-$EXP_DATE.pgdump ]; then

echo $(date '+%c')" -- deleting old backup" >>$LOGFILE

rm $BACKUP_DIR/$db-$EXP_DATE.pgdump

fi

else

echo "FAIL Database $db backup!" >>$LOGFILE

fi

done

This script dumps all databases in postgres individually.

Databases are vacuumed and then dumped.

Dump-Format is currently set as compressed pgsql dump (See "-F c" in $BACKUP_OPTIONS)

File names consist of "databasename"-"backupdade".pgdump

I have programmed to automatically purge backups older than two weeks (see " -2w " in $EXP_DATE)

If you drop a database you will have to remove backups for that database manually.

I have enabled a daily run of this scrip by placing the executable script somwhere and add it to the crontab of root: sudo crontab -e

Enter the following line and your backups start every day at 6:00. Adjust paths and scriptname according to your own setup.

0 6 * * * ~/.bin/postgres_dump.sh > /dev/null 2>&1

I did something dumb 2 weeks ago and reloaded from Time Machine, and it worked fine for me. In fact, it was ONLY psotgres. Wiki, etc all continued to function just fine.

RAID is fine, we use that too, unless, someone breaks in a steals both drives, or, the building burns down. So, offsite is good also.

So, not sure why you did not have success. Time Machine we have never had any issues with. And it's better than a system volume copy as it's automatic, and, you can go back to various instants in time.

Perhaps you excluded some key folder somewhere? SOme of the wiki, etc. data also is stored in the filesystem, it's not 100% postgres.