Linux & CentOS 7.6 环境下执行PostgreSQL定时自动备份和删除_HoneySJ_centos postgresql备份

网络 02-07 5895

CentOS 环境下执行PostgreSQL定时自动备份和删除一.创建备份shell脚本 1.创建备份脚本存放路径 mkdir -p /u01/db-bak/2/bak-file/all

注：① 把shell脚本存放在/u01/db-bak/2 中

② 备份后的pgsql.backup 放到 /u01/db-bak/2/bak-file/all 中

2.准备备份shell脚本

进入/u01/db-bak/2

cd /u01/db-bak/2

创建备份shell脚本

vim pgsql-all-backup.sh

shell脚本

#!/bin/bash echo "开始执行 PostgreSql 数据库 dbtest 的备份！" echo "backup ing -------------------" nowtime=$(date +%F+%T) export PGPASSWORD=123456 echo "时间：" $nowtime set timeout 500 /usr/pgsql-14/bin/pg_dump --file "/u01/db-bak/2/bak-file/all/pgsql-all-"$nowtime".backup" --host "172.25.76.251" --port "5432" --username "honeysj" --dbname "dbtest" --verbose --role "honeysj" --format=c --blobs --encoding "UTF8" echo "数据库 dbtest 备份结束！" exit;

解释：

nowtime=$(date +%F+%T) --当前时间 PGPASSWORD=123456 --数据库密码 host "172.25.76.251" --需备份的数据库ip 二.创建删除shell脚本 1.创建删除脚本存放路径

注：路径与备份shell一致，便于管理，这里就不需要创建了

2.准备删除shell脚本

进入/u01/db-bak/2

cd /u01/db-bak/2

创建删除shell脚本

vim delete-pgbak.sh

shell脚本

#!/bin/bash echo "删除7天前的数据库备份文件！" find /u01/db-bak/2/bak-file/all/ -name "pgsql-all*" -mtime +6 -exec rm -rf {} \; set timeout 1000 echo "7天前的数据库备份文件删除完毕！" 三.配置定时任务 1.添加定时任务

进入/u01/db-bak

cd /u01/db-bak

添加定时任务

crontab -e 30 23 * * * /u01/db-bak/2/delete-pgbak.sh 0 23 * * * /u01/db-bak/2/pgsql-all-backup.sh

解释：每天23点30分执行删除脚本；每天23点执行数据库备份脚本。

2.查看定时任务 crontab -l 四.赋权 chmod +x /u01/db-bak/2/* 五.测试shell脚本

进入/u01/db-bak/2

cd /u01/db-bak/2

测试执行脚本

bash pgsql-all-backup.sh bash delete-pgbak.sh

扩展：执行如下

[root@HoneySJ 2]# bash pgsql-all-backup.sh 开始执行 PostgreSql 数据库 dbtest 的备份！ backup ing ------------------- 时间： 2022-03-28+17:45:53 pg_dump: last built-in OID is 16383 pg_dump: reading extensions pg_dump: identifying extension members pg_dump: reading schemas pg_dump: reading user-defined tables pg_dump: reading user-defined functions pg_dump: reading user-defined types pg_dump: reading procedural languages pg_dump: reading user-defined aggregate functions pg_dump: reading user-defined operators pg_dump: reading user-defined access methods pg_dump: reading user-defined operator classes pg_dump: reading user-defined operator families pg_dump: reading user-defined text search parsers pg_dump: reading user-defined text search templates pg_dump: reading user-defined text search dictionaries pg_dump: reading user-defined text search configurations pg_dump: reading user-defined foreign-data wrappers pg_dump: reading user-defined foreign servers pg_dump: reading default privileges pg_dump: reading user-defined collations pg_dump: reading user-defined conversions pg_dump: reading type casts pg_dump: reading transforms pg_dump: reading table inheritance information pg_dump: reading event triggers pg_dump: finding extension tables pg_dump: finding inheritance relationships pg_dump: reading column info for interesting tables pg_dump: finding the columns and types of table "public.odin_open_application_data" pg_dump: finding the columns and types of table "public.yida_entity_instance" pg_dump: finding the columns and types of table "public.tianshu_form_data" pg_dump: finding default expressions of table "public.tianshu_form_data" pg_dump: finding the columns and types of table "public.tianshu_instance_relation" pg_dump: finding default expressions of table "public.tianshu_instance_relation" pg_dump: finding the columns and types of table "public.tianshu_form_data_operation_log" pg_dump: finding default expressions of table "public.tianshu_form_data_operation_log" pg_dump: flagging inherited columns in subtables pg_dump: reading indexes pg_dump: reading indexes for table "public.odin_open_application_data" pg_dump: reading indexes for table "public.yida_entity_instance" pg_dump: reading indexes for table "public.tianshu_form_data" pg_dump: reading indexes for table "public.tianshu_instance_relation" pg_dump: reading indexes for table "public.tianshu_form_data_operation_log" pg_dump: flagging indexes in partitioned tables pg_dump: reading extended statistics pg_dump: reading constraints pg_dump: reading triggers pg_dump: reading rewrite rules pg_dump: reading policies pg_dump: reading row-level security policies pg_dump: reading publications pg_dump: reading publication membership pg_dump: reading subscriptions pg_dump: reading large objects pg_dump: reading dependency data pg_dump: saving encoding = UTF8 pg_dump: saving standard_conforming_strings = on pg_dump: saving search_path = pg_dump: saving database definition pg_dump: dumping contents of table "public.odin_open_application_data" pg_dump: dumping contents of table "public.tianshu_form_data" pg_dump: dumping contents of table "public.tianshu_form_data_operation_log" pg_dump: dumping contents of table "public.tianshu_instance_relation" pg_dump: dumping contents of table "public.yida_entity_instance" 数据库 dbtest 备份结束！ [root@HoneySJ 2]# bash delete-pgbak.sh 删除7天前的数据库备份文件！ 7天前的数据库备份文件删除完毕！六.扩展 1.查看备份文件

进入/u01/db-bak/2/bak-file/all

cd /u01/db-bak/2/bak-file/all

查看

ls -l total 20 -rw-r--r-- 1 root root 16655 Mar 28 17:45 pgsql-all-2022-03-28+17:45:53.backup 2.删除备份文件

进入/u01/db-bak/2/bak-file/all

cd /u01/db-bak/2/bak-file/all

删除

rm pgsql-all-2022-03-28+17:41:45.backup rm: remove regular file ‘pgsql-all-2022-03-28+17:41:45.backup’? y