如何用pg_dump批量备份指定前缀的PostgreSQL数据库？

核心思路 #

通过PostgreSQL系统表pg_database筛选出符合前缀的数据库列表，再通过Shell循环调用pg_dump逐个备份。

步骤实现 #

1. 获取目标数据库列表
   使用psql查询系统表，仅返回符合前缀的非模板数据库名称：

   psql -U 你的数据库用户名 -t -c "SELECT datname FROM pg_database WHERE datname LIKE '你的前缀%' AND datistemplate = false;"
   

   参数说明：

   • -t：仅输出查询结果数据，去除表头和冗余格式
   • datistemplate = false：排除PostgreSQL自带的template0、template1等模板数据库

2. 编写自动化备份脚本
   创建Shell脚本（例如backup_prefix_dbs.sh），实现批量备份逻辑：

   #!/bin/bash
   
   # 配置自定义参数
   DB_USER="你的数据库用户名"
   BACKUP_DIR="/你要存放备份的目录路径"
   PREFIX="目标数据库前缀"
   
   # 创建备份目录（不存在则自动创建）
   mkdir -p "$BACKUP_DIR"
   
   # 获取符合条件的数据库列表
   DATABASES=$(psql -U "$DB_USER" -t -c "SELECT datname FROM pg_database WHERE datname LIKE '$PREFIX%' AND datistemplate = false;")
   
   # 遍历并备份每个数据库
   for DB in $DATABASES; do
     # 跳过空行（避免因查询结果格式问题报错）
     [ -z "$DB" ] && continue
     # 生成带时间戳的备份文件名
     BACKUP_FILE="$BACKUP_DIR/${DB}_$(date +%Y%m%d_%H%M%S).sql"
     echo "正在备份数据库: $DB -> $BACKUP_FILE"
     # 执行备份
     pg_dump -U "$DB_USER" "$DB" > "$BACKUP_FILE"
     # 可选：启用以下命令压缩备份文件（节省磁盘空间）
     # gzip "$BACKUP_FILE"
   done
   
   echo "所有符合前缀的数据库备份完成！"
   

3. 运行脚本

   • 给脚本添加执行权限：

     chmod +x backup_prefix_dbs.sh
     

   • 执行脚本：

     ./backup_prefix_dbs.sh
     

注意事项 #

• 如果希望执行时无需输入密码，可配置~/.pgpass文件（格式：hostname:port:database:username:password），或调整pg_hba.conf配置信任本地连接。
• 若需要自定义pg_dump的备份选项（如自定义格式、压缩、排除对象等），可直接修改脚本中pg_dump的命令参数。

内容的提问来源于stack exchange，提问作者Joachim DARMALINGON