postgresqlbackup.txt

实现JSP开发的BBS源码

2.postgresql的备份与恢复

#备份：

pg_dump db_name > backup-file.sql

#or

pg_dump -f backup-file.sql db_name

#or

pg_dump -s -f backup-file.sql db_name #只备份结构。

#or

pg_dump -a -f backup-file.sql db_name #只备份数据。

#恢复：

pg_restore -d db_name backup-file.sql

#OR

pg_restore -d db_name -t atable backup-file.sql #only one table

#or

pg_restore -d db_name -a backup-file.sql # only data

#or

pg_restore -d db_name -s backup-file.sql # only schema

#自动备份脚本

# Crontab starts here.
00 01 * * * /path/to/script/backup > /dev/null 2>&1
#--------------------------------------------------

# Backup script starts here.

#!/bin/bash
# Location of the backup logfile.
logfile="/path/to/logfile.log"
# Location to place backups.
backup_dir="/directory/to/place/backups"
touch $logfile
timeslot=`date +%H-%M`
databases=`psql -h localhost -U postgres -q -c "\l" | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'}`

for i in $databases; do
        timeinfo=`date '+%T %x'`
        echo "Backup and Vacuum complete at $timeinfo for time slot $timeslot on database: $i " >> $logfile
        /server/pgsql/bin/vacuumdb -z -h localhost -U postgres $i >/dev/null 2>&1
        /server/pgsql/bin/pg_dump $i -h 127.0.0.1 | gzip > "$backup_dir/postgresql-$i-$timeslot-database.gz"
done
#-------------------------------------------------