linux下pg数据库备份,Linux下的pg_dump周期性备份

OS: CentOS

DB: PostgreSQL9.3 , OS user: postgres

一、编写pg_dump备份脚本:

# get the day of week 1-7 starting mon=1

DOW=`date +"%u"`

# define variables

DB_INSTALL_DIR='/opt/PostgreSQL/9.3'

# DB_TYPE='PostgreSQL'

# DB_SERVER='127.0.0.1'

# DB_PORT='5432'

DB_INSTANCE='mydb'

DB_USER='postgres'

DB_PASSWORD='postgres'

DB_STORAGE='/home/postgres/backup'

# specify the postgres password in the PGPASSWORD var

# for pg_dump not to prompt for a password

export PGPASSWORD=$DB_PASSWORD

# change to the bin dir

cd $DB_INSTALL_DIR/bin

# run pg_dump

./pg_dump -f $DB_STORAGE/${DOW}-mydb.sql -U $DB_USER -F p -a $DB_INSTANCE

将以上脚本保存到/home/postgres/backup/backup.sh。