Postgresql备份脚本

pg备份脚本

一、pg_dumpall

1.创建备份角色

CREATE ROLE backup WITH SUPERUSER login password 'x9K_poc!4Ul0';

ALTER ROLE backup SET default_transaction_read_only TO 'on';

2.配置信任登录

$ vi pg_hba.conf
host    all             backup                      127.0.0.1/32          trust

注意:
如果pg_hba.conf配置文件有存在以下信任记录，存不需要添加上面信任
host    all             all             127.0.0.1/32            trust

3.创建备份脚本

#!/bin/bash



export PGHOME=/usr/local/pgsql
export PGDATA=/data/pgdata
export PGPORT=5532
export LD_LIBRARY_PATH=$PGHOME/lib
export PATH=$PGHOME/bin:$PATH

backup_path=/data/backup
backup_user=backup
host='127.0.0.1'
DATE=`date +%Y%m%d`



pg_dumpall -h $host -U $backup_user --inserts > $backup_path/full_pg_backup_${DATE}.sql


/usr/bin/find $backup_path -name "*.sql" -mtime +15 |xargs rm -rf 

3.创建定时任务

$ crontab -l
30 22 * * *    /home/postgres/script/pg_dumpall.sh >> /home/postgres/script/pg_dumpall.log 2>&1