PostgreSQL 14 → 18 升級筆記

環境資訊

• 來源版本: PostgreSQL 14.12-alpine (Docker)
• 目標版本: PostgreSQL 18-alpine (Docker)
• 主機: Ubuntu (Snap 版 Docker)
• 業務資料庫: d_cga, d_feyche, d_kumon, d_opal_auth, d_pinns, d_seesea

---

升級流程

1. 啟動 PostgreSQL 18 測試容器

cd ~/docker-postgresql

cat > docker-compose-test.yml << 'EOF'
services:
  postgres18:
    image: postgres:18-alpine
    restart: unless-stopped
    volumes:
      - postgres18_data:/var/lib/postgresql/data
    ports:
      - "5433:5432"
    environment:
      - POSTGRES_USER=root
      - POSTGRES_PASSWORD=YOUR_PASSWORD

volumes:
  postgres18_data:
EOF

docker compose -f docker-compose-test.yml up -d

2. 匯出並匯入 Role

# 從 14 匯出 role（包含密碼）
docker exec docker-postgresql-postgres-1 pg_dumpall -U root --roles-only > ~/pg_roles.sql

# 匯入到 18
docker exec -i docker-postgresql-postgres18-1 psql -U root -d postgres < ~/pg_roles.sql

注意: role "root" already exists 錯誤可忽略

3. 建立目標資料庫

# 逐一建立（PostgreSQL 不能在 transaction 內執行 CREATE DATABASE）
docker exec docker-postgresql-postgres18-1 psql -U root -d postgres -c "CREATE DATABASE d_cga;"
docker exec docker-postgresql-postgres18-1 psql -U root -d postgres -c "CREATE DATABASE d_feyche;"
docker exec docker-postgresql-postgres18-1 psql -U root -d postgres -c "CREATE DATABASE d_kumon;"
docker exec docker-postgresql-postgres18-1 psql -U root -d postgres -c "CREATE DATABASE d_opal_auth;"
docker exec docker-postgresql-postgres18-1 psql -U root -d postgres -c "CREATE DATABASE d_pinns;"
docker exec docker-postgresql-postgres18-1 psql -U root -d postgres -c "CREATE DATABASE d_seesea;"

4. 備份並還原資料

for db in d_cga d_feyche d_kumon d_opal_auth d_pinns d_seesea; do
  echo "備份並還原 $db ..."
  docker exec docker-postgresql-postgres-1 pg_dump -U root -d $db | \
    docker exec -i docker-postgresql-postgres18-1 psql -U root -d $db
done

5. 驗證資料

# 確認版本
docker exec docker-postgresql-postgres18-1 psql -U root -d postgres -c "SELECT VERSION();"

# 確認資料庫列表
docker exec docker-postgresql-postgres18-1 psql -U root -d postgres -c "\l"

# 確認 role
docker exec docker-postgresql-postgres18-1 psql -U root -d postgres -c "\du"

# 確認表數量
docker exec docker-postgresql-postgres18-1 psql -U root -d d_seesea -c "\dt" | tail -5

6. 切換正式環境

cd ~/docker-postgresql

# 停止測試容器
docker compose -f docker-compose-test.yml down

# 更新正式 docker-compose.yml
cat > docker-compose.yml << 'EOF'
services:
  postgres:
    image: postgres:18-alpine
    restart: always
    volumes:
      - docker-postgresql_postgres18_data:/var/lib/postgresql/data
    ports:
      - "${POSTGRES_PORT}:5432"
    environment:
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}

volumes:
  docker-postgresql_postgres18_data:
    external: true
EOF

# 停止舊的 14
docker compose down

# 啟動新的 18
docker compose up -d

---

保留舊版本（回滾用）

建立 docker-compose-bak.yml：

services:
  postgres14:
    image: postgres:14.12-alpine
    restart: unless-stopped
    volumes:
      - ./data/postgres:/var/lib/postgresql/data
    ports:
      - "5434:5432"
    environment:
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}

啟動/關閉舊版：

# 啟動（port 5434）
docker compose -f docker-compose-bak.yml up -d

# 關閉
docker compose -f docker-compose-bak.yml down

---

常見問題

Q1: role "xxx_user" does not exist 錯誤

原因: 目標資料庫沒有對應的 role

解決: 先匯入 role 再匯入資料

docker exec docker-postgresql-postgres-1 pg_dumpall -U root --roles-only > ~/pg_roles.sql
docker exec -i docker-postgresql-postgres18-1 psql -U root -d postgres < ~/pg_roles.sql

Q2: CREATE DATABASE cannot run inside a transaction block

原因: PostgreSQL 不允許在 transaction 內執行 CREATE DATABASE

解決: 分開執行每個 CREATE DATABASE 語句

Q3: Snap 版 Docker bind mount 失敗

錯誤訊息: change mount propagation through procfd: open o_path procfd

原因: Snap 版 Docker 的 overlay2 storage driver 有限制

解決方案:

1. 使用 named volume（推薦）
2. 改用 apt 安裝 Docker（非 snap 版）

Q4: psql: error: database "root" does not exist

原因: 沒有指定資料庫

解決: 加上 -d postgres 參數

docker exec container_name psql -U root -d postgres -c "..."

---

系統資料庫說明

以下是 PostgreSQL 自帶的系統資料庫，不需要備份還原：

資料庫	用途
postgres	預設管理資料庫
template0	乾淨的原始模板，不可修改
template1	建立新資料庫時的預設模板

---

相關指令速查

# 列出資料庫
\l

# 列出 role
\du

# 列出表
\dt

# 查看版本
SELECT VERSION();

# 列出 Docker volume
docker volume ls | grep postgres

# 查看容器
docker ps