postgresql 备份脚本

1. 脚本

# vim /root/pgback.sh
!#/bin/bash
cd /
back=/home/backup

send=`date '+%Y-%m-%d'`

pg_dump --dbname="postgresql://127.0.0.1:5432/tenant_1008412?user=postgres&password=csb123456" -F c -b -v -f "$back/tenant_1008412-"$send".backup"
pg_dump --dbname="postgresql://127.0.0.1:5432/xw_metadata?user=postgres&password=csb123456" -F c -b -v -f "$back/xw_metadata_1008412-"$send".backup"
pg_dump --dbname="postgresql://127.0.0.1:5432/xw_platform?user=postgres&password=csb123456" -F c -b -v -f "$back/xw_platform_1008412-"$send".backup"
pg_dump --dbname="postgresql://127.0.0.1:5432/xw_quartz?user=postgres&password=csb123456" -F c -b -v -f "$back/xw_xw_quartz_1008412-"$send".backup"

2. 修改脚本中的 pg 连接，租户库名称，用户密码

chmod +x /root/pg_back之后添加 crontab 作业

3. 定时任务

# crontab -e
1 1 * * * /root/pgback.sh

4. 恢复数据库

#平台库
create database xw_platform with encoding='utf8';
./pg_restore -U postgres -d xw_platform -v  "/usr/local/pgsql/database_backup/xw_platform.20190428.backup"
#元数据
create database xw_metadata with encoding='utf8';
./pg_restore -U postgres -d xw_metadata -v  "/usr/local/pgsql/database_backup/xw_metadata.2019042801.backup"
#调度库
create database xw_quartz with encoding='utf8';
./pg_restore -U postgres -d xw_quartz -v  "/usr/local/pgsql/database_backup/xw_quartz.20190428.backup"
#租户库
create database tenant_1008428 with encoding='utf8';
./pg_restore -U postgres -d tenant_1008428 -v  "/usr/local/pgsql/database_backup/tenant_1008428.20190428.backup"

 [root@master ~]# /opt/pg10/bin/pg_restore    -h 127.0.0.1  -U postgres -d  runoobdb    -p31800   -v  "/root/runoobdb-2021-03-10.backup"