postgres备份与恢复
一、脚本说明
1.环境 docker部署的postgres服务端，宿主机安装postgres客户端，使用命令进行备份恢复 2.数据库备份： 使用 pg_dump 命令来备份数据库，并将其压缩为 .sql.gz 或者.tar.gz 格式。 在备份时会包含创建数据库的语句。 3.备份清理： 使用 find 命令清理超过指定天数（RETENTION_DAYS）的旧备份文件。 4.恢复交互式： 列出备份目录中的备份文件，显示时间、大小等信息。 用户输入备份文件编号来选择恢复的文件，进行交互式操作。 5.使用方法： 备份： 脚本会定期创建数据库备份，并保存在 BACKUP_DIR 指定的目录下。 清理： 旧备份文件会被自动删除，保留最近 RETENTION_DAYS 天的备份。 恢复： 如果需要恢复数据库，可以使用 sh pg_bak.sh restore 传参方式，选择要恢复的备份文件，并确认恢复
二、docker-compose部署postgres数据库
说明：生产环境中，很多是直接部署在宿主机的，我是为了测试方便使用docker部署，但操作方式一致
1.按照好docker、docker-compose
#略
2.创建目录
mkdir -p /data/aibox-common/postgres
3.执行部署
[root@localhost pg]# cat docker-compose.yaml version: '3.8' services: postgres: image: postgres:15.6-bullseye container_name: postgres restart: always ports: - "5432:5432" environment: POSTGRES_USER: postgres POSTGRES_PASSWORD: 123456 PGDATA: /var/lib/postgresql/data/pgdata volumes: - /etc/localtime:/etc/localtime - /data/aibox-common/postgres:/var/lib/postgresql/data
4.查看容器
[root@localhost pg]# docker ps |grep postgres 4657eb31724f postgres:15.6-bullseye "docker-entrypoint.s…" 54 minutes ago Up 54 minutes 0.0.0.0:5432->5432/tcp postgres
5.在宿主机下载postgres客户端
（1）postgres客户端必须和部署的服务端大版本一致，否则备份有问题
（2）因为我的postgres服务端版本是15.6，所以我的客户端必须也是15，在yum安装时报错，需要安装一个libzstd
https://rpmfind.net/linux/rpm2html/search.php?query=libzstd(x86-64)
#进入网页进行下载
#下载后rpm -ivh libzstd-1.4.4-1.el8.x86_64.rpm 安装，
（3）libzstd安装完成后，继续安装postgres15客户端
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm yum install postgresql15 -y
三、创建模拟数据
1.登陆postgres数据库中
[root@localhost pgbak]# docker exec -it postgres psql -U postgres -h 172.16.4.60 -p 5432 Password for user postgres: #输入docker-compose中设置的密码 123456
2.创建数据库
CREATE DATABASE aibox_cloud;
3.切换到新创建的数据库并查看其中的表
\c aibox_cloud
\dt
4.创建一个测试表
CREATE TABLE test_data ( id SERIAL PRIMARY KEY, name VARCHAR(100), value INTEGER );
5.插入数据
-- 插入字符串和数值类型的数据 INSERT INTO test_data (name, value) VALUES ('test11', 1100); INSERT INTO test_data (name, value) VALUES ('test12', 1200); INSERT INTO test_data (name, value) VALUES ('test13', 1300); INSERT INTO test_data (name, value) VALUES ('test14', 1400); INSERT INTO test_data (name, value) VALUES ('test15', 1500); -- 插入日期数据 INSERT INTO test_data (name, value) VALUES ('test16', 1600); INSERT INTO test_data (name, value) VALUES ('test17', 1700); INSERT INTO test_data (name, value) VALUES ('test18', 1800); INSERT INTO test_data (name, value) VALUES ('test19', 1900); INSERT INTO test_data (name, value) VALUES ('test20', 2000); -- 插入一些带有特殊字符的数据 INSERT INTO test_data (name, value) VALUES ('test$1', 100); INSERT INTO test_data (name, value) VALUES ('test@2', 200); INSERT INTO test_data (name, value) VALUES ('test#3', 300); INSERT INTO test_data (name, value) VALUES ('test^4', 400); INSERT INTO test_data (name, value) VALUES ('test&5', 500);
6.查看表内容
SELECT * FROM test_data;
psql (15.10, server 15.6 (Debian 15.6-1.pgdg110+2)) Type "help" for help. root=# \c aibox_cloud; psql (15.10, server 15.6 (Debian 15.6-1.pgdg110+2)) You are now connected to database "aibox_cloud" as user "root". aibox_cloud=# \dt List of relations Schema | Name | Type | Owner --------+-----------+-------+------- public | test_data | table | root (1 row) aibox_cloud=# select * from test_data; id | name | value ----+--------+------- 1 | test11 | 1100 2 | test12 | 1200 3 | test13 | 1300 4 | test14 | 1400 5 | test15 | 1500 6 | test16 | 1600 7 | test17 | 1700 8 | test18 | 1800 9 | test19 | 1900 10 | test20 | 2000 11 | test$1 | 100 12 | test@2 | 200 13 | test#3 | 300 14 | test^4 | 400 15 | test&5 | 500 (15 rows)
四、进行备份
1.备份脚本（一个是gzip压缩解压、一个是tar.gz压缩解压，主要怕有些内网环境没有gzip命令，下边我们以tar.gz为例）
#gzip压缩
[root@localhost pgbak]# cat pg_bak_gzip.sh #!/bin/bash # 配置部分 PG_USER="postgres" # 数据库用户名 PG_PASSWORD="123456" # 数据库密码 PG_HOST="172.16.4.60" # 数据库主机地址 PG_PORT="5432" # 数据库端口 DB_NAME="aibox_cloud" # 要备份的数据库名 BACKUP_DIR="/data/backup/pgbak/sqlfile" # 备份存储目录（宿主机目录） RETENTION_DAYS=7 # 保留的天数 LOG_FILE="/data/backup/pgbak/backup.log" # 日志文件路径 # 日志输出函数 log() { local level=$1 local message=$2 echo "$(date '+%Y-%m-%d %H:%M:%S') [$level] $message" | tee -a "$LOG_FILE" } # 创建备份 backup() { local timestamp=$(date '+%Y%m%d_%H%M%S') # 检查备份目录是否存在 if [ ! -d "$BACKUP_DIR" ]; then mkdir -p "$BACKUP_DIR" log "info" "创建备份目录：$BACKUP_DIR" fi # 备份指定的数据库 local backup_file="${BACKUP_DIR}/backup_${DB_NAME}_${timestamp}.sql.gz" log "info" "开始备份数据库：$DB_NAME" # 执行备份，包含创建数据库语句 local backup_command="PGPASSWORD=$PG_PASSWORD pg_dump -U $PG_USER -h $PG_HOST -p $PG_PORT --create $DB_NAME -v | gzip > $backup_file" bash -c "$backup_command" if [ $? -eq 0 ]; then log "info" "数据库备份成功：$backup_file" else log "error" "数据库备份失败：$backup_file" fi } # 清理旧备份 cleanup() { find "$BACKUP_DIR" -type f -name "backup_${DB_NAME}_*.sql.gz" -mtime +$RETENTION_DAYS -exec rm -f {} \; if [ $? -eq 0 ]; then log "info" "清理超过 $RETENTION_DAYS 天的备份文件完成" else log "error" "清理备份文件失败" fi } # 恢复指定备份 restore_interactive() { # 检查备份目录是否存在 if [ ! -d "$BACKUP_DIR" ]; then log "error" "备份目录不存在：$BACKUP_DIR" return 1 fi # 列出备份文件，按时间排序并显示文件大小和时间 log "info" "列出备份目录中的备份文件：" local files=$(ls -lt "$BACKUP_DIR"/backup_${DB_NAME}_*.sql.gz 2>/dev/null) if [ -z "$files" ]; then log "error" "未找到备份文件，无法恢复" return 1 fi # 显示备份文件列表及信息 printf "%-5s %-40s %-20s %-10s\n" "编号" "文件名" "修改时间" "大小" local i=1 for file in $(ls -t "$BACKUP_DIR"/backup_${DB_NAME}_*.sql.gz); do local filename=$(basename "$file") local filesize=$(du -h "$file" | cut -f1) local filetime=$(stat -c %y "$file" | cut -d. -f1) printf "%-5s %-40s %-20s %-10s\n" "$i" "$filename" "$filetime" "$filesize" files_array[$i]="$file" ((i++)) done # 用户选择恢复文件 echo -n "请输入要恢复的备份文件编号：" read file_index local selected_file=${files_array[$file_index]} if [ -z "$selected_file" ]; then log "error" "无效的编号，恢复取消" return 1 fi log "info" "您选择恢复的文件是：$selected_file" # 确认恢复操作 echo -n "确认要恢复此文件吗？(yes/no)：" read confirm if [ "$confirm" != "yes" ]; then log "info" "恢复操作已取消" return 1 fi # 执行恢复 log "info" "开始恢复数据库，使用备份文件：$selected_file" local restore_command="PGPASSWORD=$PG_PASSWORD gunzip -c $selected_file | psql -U $PG_USER -h $PG_HOST -p $PG_PORT" bash -c "$restore_command" if [ $? -eq 0 ]; then log "info" "数据库恢复成功：$DB_NAME" else log "error" "数据库恢复失败" fi } #主程序 if [ "$1" == "restore" ]; then log "info" "开始执行数据库恢复操作" restore_interactive else log "info" "PostgreSQL 备份任务开始" backup cleanup log "info" "PostgreSQL 备份任务完成" fi
#tar.gz压缩
[root@localhost pgbak]# cat pg_bak.sh #!/bin/bash # 配置部分 PG_USER="postgres" # 数据库用户名 PG_PASSWORD="123456" # 数据库密码 PG_HOST="172.16.4.60" # 数据库主机地址 PG_PORT="5432" # 数据库端口 DB_NAME="aibox_cloud" # 要备份的数据库名 BACKUP_DIR="/data/backup/pgbak/sqlfile" # 备份存储目录（宿主机目录） RETENTION_DAYS=7 # 保留的天数 LOG_FILE="/data/backup/pgbak/backup.log" # 日志文件路径 # 日志输出函数 log() { local level=$1 local message=$2 echo "$(date '+%Y-%m-%d %H:%M:%S') [$level] $message" | tee -a "$LOG_FILE" } # 创建备份 backup() { local timestamp=$(date '+%Y%m%d_%H%M%S') # 检查备份目录是否存在 if [ ! -d "$BACKUP_DIR" ]; then mkdir -p "$BACKUP_DIR" log "info" "创建备份目录：$BACKUP_DIR" fi # 临时备份文件路径 local backup_temp_dir="${BACKUP_DIR}/temp" # 检查临时备份目录是否存在 if [ ! -d "$backup_temp_dir" ]; then mkdir -p "$backup_temp_dir" log "info" "创建临时备份目录：$backup_temp_dir" fi #备份文件名称 local backup_temp_file="${backup_temp_dir}/backup_${DB_NAME}_${timestamp}.sql" # 执行 pg_dump 输出到临时文件 log "info" "开始备份数据库：$DB_NAME" PGPASSWORD=$PG_PASSWORD pg_dump -U $PG_USER -h $PG_HOST -p $PG_PORT --create $DB_NAME -v -f "$backup_temp_file" if [ $? -eq 0 ]; then log "info" "数据库备份成功：$backup_temp_file" # 压缩备份文件 local backup_file="${BACKUP_DIR}/backup_${DB_NAME}_${timestamp}.tar.gz" log "info" "开始压缩备份文件：$backup_file" tar -czf "$backup_file" -C "$backup_temp_dir" "$(basename "$backup_temp_file")" if [ $? -eq 0 ]; then log "info" "备份压缩成功：$backup_file" else log "error" "备份压缩失败" fi # 删除临时文件 rm -f "$backup_temp_file" rmdir "$backup_temp_dir" else log "error" "数据库备份失败" fi } # 清理旧备份 cleanup() { find "$BACKUP_DIR" -type f -name "backup_${DB_NAME}_*.tar.gz" -mtime +$RETENTION_DAYS -exec rm -f {} \; if [ $? -eq 0 ]; then log "info" "清理超过 $RETENTION_DAYS 天的备份文件完成" else log "error" "清理备份文件失败" fi } # 恢复指定备份 restore_interactive() { # 检查备份目录是否存在 if [ ! -d "$BACKUP_DIR" ]; then log "error" "备份目录不存在：$BACKUP_DIR" return 1 fi # 列出备份文件，按时间排序并显示文件大小和时间 log "info" "列出备份目录中的备份文件：" local files=$(ls -lt "$BACKUP_DIR"/backup_${DB_NAME}_*.tar.gz 2>/dev/null) if [ -z "$files" ]; then log "error" "未找到备份文件，无法恢复" return 1 fi # 显示备份文件列表及信息 printf "%-5s %-40s %-20s %-10s\n" "编号" "文件名" "修改时间" "大小" local i=1 for file in $(ls -t "$BACKUP_DIR"/backup_${DB_NAME}_*.tar.gz); do local filename=$(basename "$file") local filesize=$(du -h "$file" | cut -f1) local filetime=$(stat -c %y "$file" | cut -d. -f1) printf "%-5s %-40s %-20s %-10s\n" "$i" "$filename" "$filetime" "$filesize" files_array[$i]="$file" ((i++)) done # 用户选择恢复文件 echo -n "请输入要恢复的备份文件编号：" read file_index local selected_file=${files_array[$file_index]} if [ -z "$selected_file" ]; then log "error" "无效的编号，恢复取消" return 1 fi log "info" "您选择恢复的文件是：$selected_file" # 确认恢复操作 echo -n "确认要恢复此文件吗？(yes/no)：" read confirm if [ "$confirm" != "yes" ]; then log "info" "恢复操作已取消" return 1 fi # 执行恢复 log "info" "开始恢复数据库，使用备份文件：$selected_file" local restore_command="PGPASSWORD=$PG_PASSWORD tar -xzf $selected_file -O | psql -U $PG_USER -h $PG_HOST -p $PG_PORT" bash -c "$restore_command" if [ $? -eq 0 ]; then log "info" "数据库恢复成功：$DB_NAME" else log "error" "数据库恢复失败" fi } # 主流程 # 判断传入参数并决定是备份还是恢复 if [ "$1" == "restore" ]; then log "info" "开始执行数据库恢复操作" restore_interactive else log "info" "PostgreSQL 备份任务开始" backup cleanup log "info" "PostgreSQL 备份任务完成" fi
2.执行备份，可以取消pg_dump中的-v 参数不输出备份信息
[root@localhost pgbak]# sh pg_bak.sh 2024-12-12 17:40:38 [info] PostgreSQL 备份任务开始 2024-12-12 17:40:38 [info] 创建临时备份目录：/data/backup/pgbak/sqlfile/temp 2024-12-12 17:40:38 [info] 开始备份数据库：aibox_cloud 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 table default expressions pg_dump: flagging inherited columns in subtables pg_dump: reading partitioning data pg_dump: reading indexes 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 of tables pg_dump: reading publication membership of schemas 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: creating DATABASE "aibox_cloud" pg_dump: connecting to new database "aibox_cloud" pg_dump: creating TABLE "public.test_data" pg_dump: creating SEQUENCE "public.test_data_id_seq" pg_dump: creating SEQUENCE OWNED BY "public.test_data_id_seq" pg_dump: creating DEFAULT "public.test_data id" pg_dump: processing data for table "public.test_data" pg_dump: dumping contents of table "public.test_data" pg_dump: executing SEQUENCE SET test_data_id_seq pg_dump: creating CONSTRAINT "public.test_data test_data_pkey" 2024-12-12 17:40:38 [info] 数据库备份成功：/data/backup/pgbak/sqlfile/temp/backup_aibox_cloud_20241212_174038.sql 2024-12-12 17:40:38 [info] 开始压缩备份文件：/data/backup/pgbak/sqlfile/backup_aibox_cloud_20241212_174038.tar.gz 2024-12-12 17:40:38 [info] 备份压缩成功：/data/backup/pgbak/sqlfile/backup_aibox_cloud_20241212_174038.tar.gz 2024-12-12 17:40:38 [info] 清理超过 7 天的备份文件完成 2024-12-12 17:40:38 [info] PostgreSQL 备份任务完成
3.查看备份数据
[root@localhost pgbak]# ls /data/backup/pgbak/sqlfile
backup_aibox_cloud_20241212_173159.tar.gz backup_aibox_cloud_20241212_174038.tar.gz
4.可以解压验证备份的有效性
五、进行恢复
1.删除aibox_cloud库下的test_data表
aibox_cloud=# DROP TABLE IF EXISTS test_data; DROP TABLE aibox_cloud=# \dt Did not find any relations.
2.数据恢复
[root@localhost pgbak]# sh pg_bak.sh restore 2024-12-12 17:56:34 [info] 开始执行数据库恢复操作 2024-12-12 17:56:34 [info] 列出备份目录中的备份文件： 编号 文件名 修改时间 大小 1 backup_aibox_cloud_20241212_175439.tar.gz 2024-12-12 17:54:39 4.0K 2 backup_aibox_cloud_20241212_175414.tar.gz 2024-12-12 17:54:14 4.0K 3 backup_aibox_cloud_20241212_174038.tar.gz 2024-12-12 17:40:38 4.0K 4 backup_aibox_cloud_20241212_173159.tar.gz 2024-12-12 17:31:59 4.0K 请输入要恢复的备份文件编号：1 2024-12-12 17:56:44 [info] 您选择恢复的文件是：/data/backup/pgbak/sqlfile/backup_aibox_cloud_20241212_175439.tar.gz 确认要恢复此文件吗？(yes/no)：yes 2024-12-12 17:56:46 [info] 开始恢复数据库，使用备份文件：/data/backup/pgbak/sqlfile/backup_aibox_cloud_20241212_175439.tar.gz Password for user root: SET SET SET SET SET set_config ------------ (1 row) SET SET SET SET ERROR: database "aibox_cloud" already exists ALTER DATABASE You are now connected to database "aibox_cloud" as user "root". SET SET SET SET SET set_config ------------ (1 row) SET SET SET SET SET SET CREATE TABLE ALTER TABLE CREATE SEQUENCE ALTER TABLE ALTER SEQUENCE ALTER TABLE COPY 15 setval -------- 15 (1 row) ALTER TABLE 2024-12-12 17:56:49 [info] 数据库恢复成功：aibox_cloud
3.查看数据库，表和数据已经恢复了
aibox_cloud=# \dt List of relations Schema | Name | Type | Owner --------+-----------+-------+------- public | test_data | table | root (1 row) aibox_cloud=# select * from test_data; id | name | value ----+--------+------- 1 | test11 | 1100 2 | test12 | 1200 3 | test13 | 1300 4 | test14 | 1400 5 | test15 | 1500 6 | test16 | 1600 7 | test17 | 1700 8 | test18 | 1800 9 | test19 | 1900 10 | test20 | 2000 11 | test$1 | 100 12 | test@2 | 200 13 | test#3 | 300 14 | test^4 | 400 15 | test&5 | 500 (15 rows)
4.恢复完成
