Menu
Article 砖工技术

Docker版PostgreSQL升级迁移（慎用Watchtower更新基础服务）

gal

gal

• 6 min read

Docker容器更新是个麻烦事，我的话因为大部分服务只有自己在用，也做了数据持久化和备份，就用Watchtower一键更新，这样做很方便但也可能会产生问题，因为有些项目版本迭代会导致配置、数据以及网络环境不兼容，我碰到过的就有：Alist从V2升级到V3导致配置重置；一个更新失败导致后续所有的容器丢失（大坑，建议还是一个一个更新而不是全部一起更新）等等。这次又作死更新了postgres，发现大版本已经从14升级到15了，提示数据库不兼容：

PostgreSQL database files are incompatible with server.

迁移方式

官方支持的方式：

  1. pg_dump或pg_dumpall，这种方式将数据库转储为一个sql文件，在新旧数据集导出和导入，缺点是速度较慢，迁移多个数据库比较麻烦，似乎也不支持非文本数据，我没有测验过，参考教程1、教程2；

  2. pg_upgrade，此种方式不需要中间文件转储，速度较快，但是除了要复制数据文件，还要手动复制容器中的运行库（参考：Docker环境下升级PostgreSQL），不知道会碰到什么问题， 看起来比较棘手。

第三方工具

好在我找到了一款专门用于Docker版Postgres升级的开源工具：tianon/docker-postgres-upgrade，它的内部也是用pg_upgrade实现的，只需要几行命令，不需要考虑运行库的问题，实测可行，不过仍有踩坑的地方（先看后面的步骤）：

  1. 升级前要先以正确方式停止旧容器的运行，否则可能会报错：
There seems to be a postmaster servicing the old cluster.
Please shutdown that postmaster and try again.
Failure, exiting
  1. 不推荐默认的用硬链接（命令加--link）在规定目录结构中迁移数据，因为会改动旧数据文件——万一升级失败不方便回滚。我用直接复制的方式（不加--link并分别映射新旧数据目录）：
$ docker run --rm \
	-v PGDATAOLD:/var/lib/postgresql/OLD/data \
	-v PGDATANEW:/var/lib/postgresql/NEW/data \
	tianon/postgres-upgrade:OLD-to-NEW
...
  1. 如果原数据库指定了超级用户则会报错：
connection to server on socket "/var/lib/postgresql/.s.PGSQL.50432" failed: FATAL:  role "postgres" does not exist

could not connect to source postmaster started with the command:
"/usr/lib/postgresql/14/bin/pg_ctl" -w -l "/var/lib/postgresql/15/data/pg_upgrade_output.d/20221024T140201.995/log/pg_upgrade_server.log" -D "/var/lib/postgresql/14/data" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start
Failure, exiting

需要添加两个环境变量（参考）：

# $POSTGRES_USER is the old data role
-e PGUSER=$POSTGRES_USER
-e POSTGRES_INITDB_ARGS="-U $POSTGRES_USER"
  1. pg_hba.conf文件可能需要手动复制或添加行，否则可能无法登录：
PG::ConnectionBad: FATAL:  no pg_hba.conf entry for host "172.18.0.1", user "my_user", database "my_database", SSL off

具体步骤

  1. 停止旧容器：
docker stop postgres-old
  1. 将旧容器挂载的数据目录复制到新目录：
cd /volume1/docker
mkdir postgres-new/data
mkdir postgres-new/backup
cp -r postgres/data postgres-new/backup
  1. 用升级工具执行升级，版本标签在tianon/postgres-upgrade Tags | Docker Hub中可以找到：
# $POSTGRES_USER 替换为旧数据库超级用户名
# $OLD_DATA 替换为旧数据备份目录，此处对应/volume1/docker/postgres-new/backup/data
# $NEW_DATA 替换为新数据目录，此处对应/volume1/docker/postgres-new/data
# $OLD 旧版本号，此处对应14
# $NEW 新版本号，此处对应15
# $OLD-to-$NEW，镜像标签，此处对应14-to-15

sudo docker run --rm \
-e PGUSER=$POSTGRES_USER \
-e POSTGRES_INITDB_ARGS="-U $POSTGRES_USER" \
-v $OLD_DATA:/var/lib/postgresql/$OLD/data \
-v $NEW_DATA:/var/lib/postgresql/$NEW/data \
tianon/postgres-upgrade:$OLD-to-$NEW
  1. 注意控制台日志中可能会有警告提示：
syncing data to disk ... initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

将旧的“pg_hba.conf”复制到新的数据文件夹中，或者将缺失的行添加到新pg_hba.conf中：

# $NEW_DATA 替换为新数据目录，此处对应/volume1/docker/postgres-new/data
 echo "host all all all scram-sha-256" >> $NEW_DATA/pg_hba.conf

  1. 导出旧容器设置，然后修改容器名称和映射的端口号。

  2. 用旧容器设置导入生成新的容器，启动运行。

  3. 测试一段时间无误后可以删除备份文件夹postgres-new/backup、旧容器以及旧容器的挂载目录。

升级日志

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/15/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
ok


Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/15/data -l logfile start

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating dump of global objects                             ok
Creating dump of database schemas                           ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok
Checking for new cluster tablespace directories             ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_xact to new server                           ok
Setting oldest XID for new cluster                          ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster               ok
Copying user relation files                                 ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to delete old cluster                       ok
Checking for extension updates                              ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /usr/lib/postgresql/15/bin/vacuumdb --all --analyze-in-stages

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh
Share