postgresql备份数据库

首先配面密登陆

如果是用root帐号则在/root/.pgpass(需要该文件是-rw-------权限)里加入一行文件

localhost:5432:*:*:mima

然后建一个shell脚本

sourseHost=localhost
sourcePort=5432
targetHost=localhost
targetPort=5432

sourceDatabase=uat
targetDatabase=rc


echo '开始同步数据库'${sourseHost}':'${sourcePort}'/'${sourceDatabase}'到'${targetHost}':'${targetPort}'/'${targetDatabase}
psql -h ${targetHost}  -p ${targetPort} -U postgres --command "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='"${targetDatabase}"'"      \
&& psql -h ${targetHost}  -p ${targetPort} -U postgres --command "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='"${targetDatabase}"'"      \
&& psql -h ${targetHost}  -p ${targetPort} -U postgres --command 'DROP DATABASE "'${targetDatabase}'"'                                  \
&& psql -h ${targetHost}  -p ${targetPort} -U postgres --command 'CREATE DATABASE "'${targetDatabase}'"'                                  \
&& pg_dump -h ${sourseHost} -w -U postgres -p ${sourcePort} ${sourceDatabase}|psql --quiet -h ${targetHost} -p ${targetPort} -w -d ${targetDatabase} -U postgres      \
&& echo '同步数据库'${sourseHost}':'${sourcePort}'/'${sourceDatabase}'到'${targetHost}':'${targetPort}'/'${targetDatabase}'成功' || echo '备份失败'

执行一下该脚本。这里面要使用pg_terminate_backend杀两次