docker composeで作ったPostgreSQL volumeをアップグレードする

postgres:
  restart: always
  image: postgres:14-alpine
  container_name: postgres
  networks:
    - internal_network
  env_file:
    - ./pg/.config/docker.env
  volumes:
    - db:/var/lib/postgresql/data

postgres       |
postgres       | PostgreSQL Database directory appears to contain a database; Skipping initialization
postgres       |
postgres       | 2022-04-10 xx:xx:xx.xxx UTC [1] FATAL:  database files are incompatible with server
postgres       | 2022-04-10 xx:xx:xx.xxx UTC [1] DETAIL:  The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.2.
postgres       |

git clone https://github.com/tianon/docker-postgres-upgrade
cd docker-postgres-upgrade/{使いたいvariant}
docker buildx build -t "{ﾃｷﾄｰなﾀｸﾞ}:{使いたいvariant}" .

# backup
docker volume create --name pg13backup
docker run --rm -v db:/from -v pg13backup:/to alpine sh -c "cp -av /from/* /to"

# 移行先
docker volume create --name pg14

docker run --rm \
  -v db:/var/lib/postgresql/13/data \
  -v pg14:/var/lib/postgresql/14/data \
  tianon/postgres-upgrade:13-to-14 --link

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/14/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 ... ok

initdb: warning: enabling "trust" authentication for local connections
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.

Success. You can now start the database server using:

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

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

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/13/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/13/data" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start
Failure, exiting

# ダメな例
docker run --rm \
  -e POSTGRES_USER=postgres \
  -v db:/var/lib/postgresql/13/data \
  -v pg14:/var/lib/postgresql/14/data \
  tianon/postgres-upgrade:13-to-14 --link

# イケた例
docker run --rm \
  -e POSTGRES_USER=postgres \
  -e PGUSER=postgres \
  -e POSTGRES_INITDB_ARGS="-U postgres"
  -v db:/var/lib/postgresql/13/data \
  -v pg14:/var/lib/postgresql/14/data \
  tianon/postgres-upgrade:13-to-14 --link

volumes:
  db:
  pg14:
    external: true