User Tools

Site Tools


database:pgsql:backup

PostgreSQL Backups & Restore

Dumping and restoring databases

dumping single database

Example for dumping database dbtest, whit user dbuser as text file:

 pg_dump -U postgres -f /home/root2/dbtest.dump dbtest

For dumping a database containing large objects it's better to use the tar obtion:

 pg_dump -U postgres -f /home/root2/dbtest.tar -Ft dbtest

restoring single database

Restoring from a dumped text file:

 psql -U postgres -d dbtest -f dbtest.dump

Restoring from a dumped tar file:

 pg_restore -U postgres -d dbtest dbtest.tar

dumping / restoring a specific table

pg_dump -Ft -d postgres -t mytable | gzip > mytable.tar.gz
gunzip mytable.tar.gz
pg_restore -d myotherdb /path/to/mytable.tar

dumping / restoring whole database server (all db's)

Write all db's to dump file:

pg_dumpall > db.out

Reimport all db's:

psql -f db.out postgres

Automated Backups

The following bash script uses pg_dump, creates a backup for each database on the server and can be scheduled with a cron-job.

#!/usr/bin/env bash
 
BACKUP_DIR="/db/pgsql/backup"
 
PGHOST="localhost"
PGUSER="postgres"
PGDUMP="/usr/bin/pg_dump"
PSQL="/usr/bin/psql"
DATE=`date +%Y-%m-%d`
 
function pg_backup_database
{
  DB=$1
  echo `date +'%F %T'` starting postgresql backup for database $DB ...
  $PGDUMP -C -Ft $DB | bzip2 > $BACKUP_DIR/$DATE-$DB.tar.bz2
  echo "finished"
}
 
if [ -n "$1" ]; then
    pg_backup_database $1
else
    echo "-----------------------------------------------------"
    echo `date +'%F %T'` Starting postgresql backup
    echo "-----------------------------------------------------"
 
    DB_LIST=`$PSQL -l -t |/usr/bin/cut -d'|' -f1 |/bin/sed -e 's/ //g'`
    for DB in $DB_LIST
    do
        if [ "$DB" != "template0" ] && [ "$DB" != "template1" ]; then
            pg_backup_database $DB
        fi
    done
 
    echo "----------------------------------------------------"
    echo `date +'%F %T'` End postgresql backup
    echo "----------------------------------------------------"
fi
/srv/wiki.niwos.com/data/pages/database/pgsql/backup.txt · Last modified: 2009/08/15 12:14 (external edit)