PostgreSQL 16 升级到 17 版本数据迁移记录

引言

最近 PostgreSQL 发布了 17 的正式版本，作为一个狂热的新版本追随者当然需要更新到最新版本，以下是我将 Postgres 16 升级到 17 版本并完成数据迁移的过程记录

创建新版数据库

1. 我的数据库主要使用 docker 启动，docker compose 脚本如下：

yaml
version: '3.8'

services:
  # 旧版本数库
  postgres16:
    container_name: postgres16
    image: postgres:16-alpine
    volumes:
      - /data/postgre16:/var/lib/postgresql/data
    ports:
      - '5432:5432'
    environment:
      - POSTGRES_PASSWORD=xxxxxx
      - POSTGRES_DB=example
    restart: always
    networks:
      - pgsql
  # 新版本数库，端口号可以随意指定，在数据完成迁移后将会使用默认的 5432 端口
  postgres17:
    container_name: postgres17
    image: postgres:17-alpine
    volumes:
      - /data/postgre17:/var/lib/postgresql/data
    ports:
      - '5433:5432'
    environment:
      - POSTGRES_PASSWORD=xxxxxx
      - POSTGRES_DB=example
    restart: always
    networks:
      - pgsql
networks:
  pgsql:

2. 然后我们使用 docker compose up -d 命令来启动这两个容器。

导出数据库

1. 新建一个脚本命名为 postgres_backup.sh,并添加以下内容，然后执行 chmod +x postgres_backup.sh 为脚本添加执行权限，然后使用 ./postgres_backup.sh 开始备份数据库，完成后会在 ~/postgresql_backup 目录下生成一个 postgres.tar.gz 压缩包，包含了所有数据库的数据

sh
#!/bin/bash
# Created by KevinYouu on 2023-08-03 12:26:38
# Description: Export table from PostgreSQL database

backupPath="$HOME/postgresql_backup/tar"
ContainerName="postgres17"
dbUser="postgres"

# 检测是否存在$HOME/postgresql_backup目录
if [ ! -d "$backupPath" ]; then
    mkdir -p "$backupPath"
fi

# 获取 PostgreSQL 中所有的数据库名称
databaseNames=$(docker exec $ContainerName psql -U $dbUser -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('template0', 'template1')")

# 遍历每个数据库并导出到单独的 SQL 文件
for dbName in $databaseNames; do
    docker exec $ContainerName pg_dump -U $dbUser "$dbName" >"$backupPath/$dbName.sql"
done

# 创建 tar 文件并将所有导出的 SQL 文件添加到其中
cd "$HOME/postgresql_backup" || exit
# tar -czvf "$(date +%Y%m%d_%H%M%S).tar.gz" -C tar .
tar -czvf "postgres.tar.gz" -C tar .

# 删除所有导出的 SQL 文件
rm "$backupPath"/*.sql

echo "备份文件已压缩到 $HOME/postgresql_backup/postgres.tar.gz"

还原数据到新的数据库

1. 切换到 $HOME/postgresql_backup/ 目录
2. 新建一个脚本命名为 restore.sh,并添加以下内容，然后执行 chmod +x restore.sh 为脚本添加执行权限，然后使用 ./restore.sh 开始还原数据库

sh
#!/bin/bash
# Created by KevinYouu on 2024-05-03 22:27:04
# Description: Restore database

dir="."

# Function to reset the specified database
restore_db() {
    local db_name="$1"
    local sql_dir="$2"
    local sql_file="$sql_dir/${db_name}.sql"

    # Check if the database name and directory are provided
    if [ -z "$db_name" ] || [ -z "$sql_dir" ]; then
        echo "Usage: restore_db <database_name> /path/to/sql_directory"
        return 1
    fi

    # Check if the SQL file exists
    if [ ! -f "$sql_file" ]; then
        echo "SQL file not found at: $sql_file"
        return 1
    fi

    # Drop the existing database if it exists
    docker exec -i postgres17 psql -U postgres -c "DROP DATABASE IF EXISTS $db_name;"

    # Create a new database
    docker exec -i postgres17 psql -U postgres -c "CREATE DATABASE $db_name;"

    # Import the SQL file into the database
    docker exec -i postgres17 psql -U postgres -d "$db_name" <"$sql_file"

    # Remove the SQL file
    rm "$sql_file"

    echo "Database $db_name has been reset successfully."
}

# 解压 tar.gz
tar -xzvf "$dir/postgres.tar.gz" -C "$dir" --wildcards --no-anchored 'example.sql' 'example2.sql' 'example3.sql'

# Mac OS 使用这个解压命令
# bsdtar -xzf "$dir/postgres.tar.gz" -C "$dir" go.sql umami.sql artalk.sql english.sql
restore_db example "$dir"
restore_db example2 "$dir"
restore_db example3 "$dir"

替换旧容器

1. 首先注释或者删除旧容器，然后启动将新数据库的容器修改为 5432 端口
2. 使用docker stop postgre16停止容器
3. 使用docker rm postgre16删除容器。如果你想要保留旧版本容器，可以跳过这个步骤

yaml
version: '3.8'

services:
  # 旧版本数库
  # postgres16:
  #   container_name: postgres16
  #   image: postgres:16-alpine
  #   volumes:
  #     - /data/postgre16:/var/lib/postgresql/data
  #   ports:
  #     - '5432:5432'
  #   environment:
  #     - POSTGRES_PASSWORD=xxxxxx
  #     - POSTGRES_DB=example
  #   restart: always
  #   networks:
  #     - pgsql
  # 新版本数库，将端口号修改回 5432
  postgres17:
    container_name: postgres17
    image: postgres:17-alpine
    volumes:
      - /data/postgre17:/var/lib/postgresql/data
    ports:
      - '5432:5432'
    environment:
      - POSTGRES_PASSWORD=xxxxxx
      - POSTGRES_DB=example
    restart: always
    networks:
      - pgsql
networks:
  pgsql:

更换端口

1. 切换到 compose 脚本所在目录
2. 再次使用 docker compose up -d 完成端口的切换即可完成迁移

其他疑问

• 为什么要使用 docker 启动数据库而不是直接使用 apt 安装？

• 因为我买的 VPS 很便宜，可能会出现故障，也有跑路的风险，为了尽快恢复服务，我认为这里的 docker 能够让我快速恢复网站服务的收益高于它的性能损失所带来的弊端。同时我也可以同时运行多个数据库，进行备份热替换等等

非 Docker 版本的升级方式

命令行参数如下

• -b 旧版本二进制文件目录
• -B 新版本二进制文件目录
• -d 旧版本数据目录
• -D 新版本数据目录
• -o 旧版本主配置文件
• -O 新版本主配置文件
• -c 仅check，不执行升级，可先加-c检查是否有报错，没有报错再运行升级

使用 pg_upgrade 命令跨版本升级

bash
/usr/lib/postgresql/16/bin/pg_upgrade \
  -b /usr/lib/postgresql/16/bin \
  -B /usr/lib/postgresql/17/bin \
  -d /var/lib/postgresql/16/main \
  -D /var/lib/postgresql/17/main \
  -o " -c config_file=/etc/postgresql/16/main/postgresql.conf" \
  -O " -c config_file=/etc/postgresql/17/main/postgresql.conf"