☰

Docker Service Postgres 将 9.6-alpine 数据迁移至 13.2-alpine

tag-icon • tag-icon docker postgresql migration
Docker Service Postgres 将 9.6-alpine 数据迁移至 13.2-alpine

不久前我从 docker service postgres 9.5 升级到了 9.6-alpine，但似乎无法将 9.6-alpine 升级到 13.2-alpine。

迄今采取的措施均未成功。

#!/bin/bash 
# Upgrade test script 9.6-alpine to 13.2-alpine 
# 
docker exec greenlight_db_1 /usr/local/bin/pg_dump -U postgres -Fc postgres -f /var/lib/postgresql/data/production.dump 
docker-compose down 
# Make backups
cp -v -a db db.bak 
mv -v db/production/production.dump . 
rm -v -r db/ 
# Upgrade to 13.2-alpine
sed -i 's+9.6-alpine+13.2-alpine+g' docker-compose.yml 
docker-compose up -d 
docker-compose down 
# Fix security
sed -i 's+md5+trust+g' db/production/pg_hba.conf 
docker-compose up -d 
# Wait for image to completely load before drop
echo -e "\x1B[96m ## Loading image. You will be prompted when complete... ## \x1B[0m" 
sleep 90s # Waits 90 seconds. 
read -p $'\e[1;33m## Image Loaded. Press [Enter] to continue. ##\e[0m ' 
docker exec greenlight_db_1 /usr/local/bin/psql -U postgres -c "DROP DATABASE greenlight_production;" 
mv production.dump db/production/
docker exec greenlight_db_1 /usr/local/bin/pg_restore -U postgres -l /var/lib/postgresql/data/production.dump

当前 9.6-alpine docker-compose.yml

version: '3'

services:
  app:
    entrypoint: [bin/start]
    image: redzed:release-v2.8.2
    container_name: bayden10-v2.8.2
    env_file: .env
    restart: unless-stopped
    ports:
      - 127.0.0.1:5000:80
# When using external logging
#    logging:
#      driver: $LOG_DRIVER
#      options:
#        syslog-address: $LOG_ADDRESS
#        tag: $LOG_TAG
    volumes:
      - ./log:/usr/src/app/log
      - ./storage:/usr/src/app/storage
#      - ./terms.md:/usr/src/app/config/terms.md
# When using sqlite3 as the database
#      - ./db/production:/usr/src/app/db/production
# When using postgresql as the database
    links:
      - db
  db:
    image: postgres:9.6-alpine
    restart: unless-stopped
    ports:
      - 127.0.0.1:5432:5432
    volumes:
      - ./db/production:/var/lib/postgresql/data
    environment:
      - POSTGRES_DB=postgres
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=[password]

结果

; ; Archive created at 2021-03-23 16:05:50 UTC ; dbname: postgres ; TOC Entries: 9 ; Compression: -1 ; Dump Version: 1.13-0 ; Format: CUSTOM ; Integer: 4 bytes ; Offset: 8 bytes ; Dumped from database version: 9.6.21 ; Dumped by pg_dump version: 9.6.21 ; ; ; Selected TOC Entries: ; 3; 2615 2200 SCHEMA - public postgres 2120; 0 0 COMMENT - SCHEMA public postgres 1; 3079 12390 EXTENSION - plpgsql 2121; 0 0 COMMENT - EXTENSION plpgsql

我对 postgres 或 docker 不够熟悉，无法开始研究不同的转储和升级方法。如能得到任何帮助，我将不胜感激。

~b10

答案1

如何将dockerized Postgres9升级到10-13：

  1. 备份/转储数据库
  2. 停止docker
  3. 删除容器的 postgres 数据目录
  4. 编辑 docker-compose.yml 并设置新的 Postgres 镜像版本
  5. 作为新版本启动数据库容器
  6. 它将初始化数据库。
  7. 在启动的容器中运行 pg_restore
  8. 它将在新版本的 Postgres 中重新创建你的数据库

近似命令：

docker exec -t docker_database_1 pg_dumpall -c -U postgres > dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql
docker compose down
sudo rm -Rf  /var/lib/docker/volumes/docker_postgres-data/_data/*

docker-compose.yml 用于升级：

version: '3'
services:
  database:
    image: postgres:13-alpine
    env_file:
      - ./environment
    volumes:
      - postgres-data:/var/lib/postgresql/data
    restart: always
volumes:
  postgres-data: {}

然后恢复数据库进行升级：

docker-compose up
cat dump.sql | docker exec -i docker_database_1 psql -U postgres -d postgres

相关内容