#!/bin/bash
#
# 创建/pg_bak目录，并执行 chown -R postgres. /pg_bak
#
# 修改pg_hba.conf为本地登录数据库无需密码
# host    all             all             127.0.0.1/32            trust
#
# 脚本赋予执行权限,创建计划任务 定期执行备份和清理过期备份集
#
# 注意 创建对应的备份目录，并且postgres有写入权限，部署脚本后进行手动测试，查看备份集是否正常

export PATH=/app/sgrdb/pg/3.0/bin:$PATH
export PGDATA=/pgdata/data
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=15432
export PGLOCALEDIR=/app/sgrdb/pg/3.0/share/locale
export LD_LIBRARY_PATH=/app/sgrdb/pg/3.0/lib

export PATH=/app/sgrdb/pg/3.0/bin:$PATH

filename=`date +%Y%m%d`

BK_DR=/backup


# 备份数据库全局用户信息

pg_dumpall -g > $BK_DR/pg_bak_role$filename.sql


# 备份数据库
# -h为目标ip -p为数据库端口 -U为数据库用户名 -d为要备份的目标数据库 请根据实际环境修改
# 如果存在多个库，每个库一条命令并修改-d参数为对应的库名
# 如果数据库存在多个库，每个库备份命令一行

pg_dump -Fc -b -h 127.0.0.1 -p 15432 -U postgres --no-password -d postgres > $BK_DR/pg_bak_postgres_$filename.dump

pg_dump -Fc -b -h 127.0.0.1 -p 15432 -U postgres --no-password -d iistpssg > $BK_DR/pg_bak_iistpssg_$filename.dump

pg_dump -Fc -b -h 127.0.0.1 -p 15432 -U postgres --no-password -d pay_analysis > $BK_DR/pg_bak_pay_analysis_$filename.dump

pg_dump -Fc -b -h 127.0.0.1 -p 15432 -U postgres --no-password -d tj_yj_cim > $BK_DR/pg_bak_tj_yj_cim_$filename.dump

pg_dump -Fc -b -h 127.0.0.1 -p 15432 -U postgres --no-password -d tj_yj_test > $BK_DR/pg_bak_tj_yj_test_$filename.dump

pg_dump -Fc -b -h 127.0.0.1 -p 15432 -U postgres --no-password -d xzgds > $BK_DR/pg_bak_xzgds_$filename.dump

pg_dump -Fc -b -h 127.0.0.1 -p 15432 -U postgres --no-password -d pis > $BK_DR/pg_bak_pis_$filename.dump


# 删除7天前的备份
find $BK_DR -name "pg_bak*" -type f -mtime +6 -exec rm {} \;

exit