PostgreSQL 16 to 17 Version Data Migration Record

Introduction

The latest stable version of PostgreSQL, version 17, has recently been released. As an ardent follower of new releases, I naturally felt compelled to upgrade to the latest version. This document chronicles the process of upgrading my Postgres database from version 16 to 17, including the data migration.

Establishing the New Database

1. My database primarily utilizes Docker for launch, with the following Docker Compose script:

yaml
version: '3.8'

services:
  # Legacy Database
  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
  # New Database, Port Can Be Arbitrarily Defined, Default Port 5432 Will Be Used After Data Migration
  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. We then launch both containers using the docker compose up -d command.

Exporting the Database

1. Create a script named postgres_backup.sh and add the following content. Grant the script execution permission with chmod +x postgres_backup.sh and then execute ./postgres_backup.sh to begin backing up the database. Once completed, a compressed archive named postgres.tar.gz will be generated in the ~/postgresql_backup directory, encompassing all database data.

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"

# Verify the existence of the $HOME/postgresql_backup directory
if [ ! -d "$backupPath" ]; then
    mkdir -p "$backupPath"
fi

# Retrieve all database names from PostgreSQL
databaseNames=$(docker exec $ContainerName psql -U $dbUser -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('template0', 'template1')")

# Iterate through each database and export to a separate SQL file
for dbName in $databaseNames; do
    docker exec $ContainerName pg_dump -U $dbUser "$dbName" >"$backupPath/$dbName.sql"
done

# Create a tar archive and add all exported SQL files
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 .

# Delete all exported SQL files
rm "$backupPath"/*.sql

echo "Backup file has been compressed to $HOME/postgresql_backup/postgres.tar.gz"

Restoring Data to the New Database

1. Navigate to the $HOME/postgresql_backup/ directory.
2. Create a script named restore.sh and add the following content. Grant the script execution permission with chmod +x restore.sh and then execute ./restore.sh to begin restoring the database.

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."
}

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

# Mac OS uses this decompression command
# 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"

Replacing the Legacy Container

1. First, comment out or delete the legacy container, then start the new database container with its port changed to 5432.
2. Stop the container using docker stop postgre16.
3. Delete the container using docker rm postgre16. If you wish to retain the legacy container, you can skip this step.

yaml
version: '3.8'

services:
  # Legacy Database
  # 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
  # New Database, Port Changed Back to 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:

Changing Ports

1. Navigate to the directory containing the Compose script.
2. Execute docker compose up -d again to complete the port switch and finalize the migration.

Further Questions

1. Why utilize Docker to launch the database instead of installing directly with apt?

• The VPS I purchased is quite inexpensive and may experience occasional failures, even posing a risk of "running away." To swiftly restore service, I believe Docker offers a faster recovery time for website services, outweighing any potential performance drawbacks. Additionally, Docker allows me to run multiple databases concurrently, enabling backup and hot-swapping capabilities.

Non-Docker Upgrade Method

Command-line arguments are as follows:

• -b Old version binary directory
• -B New version binary directory
• -d Old version data directory
• -D New version data directory
• -o Old version main configuration file
• -O New version main configuration file
• -c Check only; does not perform the upgrade. You can use -c first to check for errors before running the upgrade.

Use the pg_upgrade command for cross-version upgrades.

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"