从 RDS 到 Docker Compose 自建 Postgres 数据库

Postgres 备份与恢复

Postgres 总共有三种备份方式：SQL dump，File system level backup 和 Continuous archiving

SQL dump 的方式很简单。最简单的命令是下面这样的

pg_dump dbname > dumpfile

sql_dump.sh

pg_dump 最大的好处是，它不限制迁移前后的server的版本，以及操作系统的架构。毕竟导出的都是的SQL语句。

也正是因为导出的都是一条一条的 SQL语句，所以导入的时候只是简单的运行如下的命令就可以了。

psql  --single-transaction dbname < dumpfile

psql_import

关于上条语句，"--single-transaction" 代表只能全部成功或者全部失败

我在导出数据时添加了 "-F c" 参数，这时将导出成 Postgres 特定的格式。

version: '3'

services:
  backup:
    image: postgres:15
    volumes:
      - ./bk:/bk
    env_file:
      - .env
    command: |
      bash -c "pg_dump -h $$POSTGRES_ADDRESS -p $$POSTGRES_PORT -U $$POSTGRES_USERNAME -W -d evrane_blog -F c -f /bk/evrane_blog.backup &&
               pg_dump -h $$POSTGRES_ADDRESS -p $$POSTGRES_PORT -U $$POSTGRES_USERNAME -W -d evrane_identity -F c -f /bk/evrane_identity.backup &&
               pg_dump -h $$POSTGRES_ADDRESS -p $$POSTGRES_PORT -U $$POSTGRES_USERNAME -W -d evrane_identity_server -F c -f /bk/evrane_identity_server.backup &&
               pg_dump -h $$POSTGRES_ADDRESS -p $$POSTGRES_PORT -U $$POSTGRES_USERNAME -W -d evrane_pinehamster_api -F c -f /bk/evrane_pinehamster_api.backup &&
               pg_dump -h $$POSTGRES_ADDRESS -p $$POSTGRES_PORT -U $$POSTGRES_USERNAME -W -d evrane_pinehamster_currency -F c -f /bk/evrane_pinehamster_currency.backup &&
               pg_dump -h $$POSTGRES_ADDRESS -p $$POSTGRES_PORT -U $$POSTGRES_USERNAME -W -d evrane_pinehamster_report -F c -f /bk/evrane_pinehamster_report.backup"

rds_export/docker-compose.yml

在导入时，我使用了如下的命令

#!/bin/bash
set -e

# 打印创建数据库的信息
echo "Starting to create databases..."

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
    CREATE DATABASE evrane_blog;
    CREATE DATABASE evrane_identity_server;
    CREATE DATABASE evrane_identity;
    CREATE DATABASE evrane_pinehamster_api;
    CREATE DATABASE evrane_pinehamster_currency;
    CREATE DATABASE evrane_pinehamster_report;
EOSQL

echo "Databases created successfully."

# 打印从备份中恢复数据的信息
echo "Starting to restore evrane_blog from backup..."
pg_restore --no-owner --dbname=evrane_blog ./download-rds/bk/evrane_blog.backup
echo "Restored evrane_blog successfully."

echo "Starting to restore evrane_identity_server from backup..."
pg_restore --no-owner --dbname=evrane_identity_server ./download-rds/bk/evrane_identity_server.backup
echo "Restored evrane_identity_server successfully."

echo "Starting to restore evrane_identity from backup..."
pg_restore --no-owner --dbname=evrane_identity ./download-rds/bk/evrane_identity.backup
echo "Restored evrane_identity successfully."

echo "Starting to restore evrane_pinehamster_api from backup..."
pg_restore --no-owner --dbname=evrane_pinehamster_api ./download-rds/bk/evrane_pinehamster_api.backup
echo "Restored evrane_pinehamster_api successfully."

echo "Starting to restore evrane_pinehamster_currency from backup..."
pg_restore --no-owner --dbname=evrane_pinehamster_currency ./download-rds/bk/evrane_pinehamster_currency.backup
echo "Restored evrane_pinehamster_currency successfully."

echo "Starting to restore evrane_pinehamster_report from backup..."
pg_restore --no-owner --dbname=evrane_pinehamster_report ./download-rds/bk/evrane_pinehamster_report.backup
echo "Restored evrane_pinehamster_report successfully."

echo "All databases restored successfully."

init-db.sh

在使用 docker compose 启用新容器时可以使用如下的脚本

version: '3'
services:
  postgres:
    image: postgres:15
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    volumes:
      - ./data/postgres:/var/lib/postgresql/data # 数据库存储
      - ./init-scripts:/docker-entrypoint-initdb.d # 首次启动脚本
      - ./download-rds/bk:/download-rds/bk # 在首次启动脚本中使用的恢复用备份文件
    ports:
      - "5432:5432"
  pgadmin:
    image: dpage/pgadmin4:7
    environment:
      PGADMIN_DEFAULT_EMAIL: ${PGADMIN_DEFAULT_EMAIL}  # 用于登录 pgAdmin 的默认邮箱地址
      PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_DEFAULT_PASSWORD}  # 用于登录 pgAdmin 的默认密码
    ports:
      - "5050:80"
    volumes:
      - ./data/pgadmin:/var/lib/pgadmin
    depends_on:
      - postgres
    user: "${UID}:${GID}"

postgres/docker-compose.yml

参考：https://www.postgresql.org/docs/current/backup.html