1

I'm upgrading an existing dockerized postgres:9.6 to 10. There are plenty of good Q/As on DBA and SO that go the path of pg_upgradecluster, but it is not working for me.

When I start a vanilla pg:10 instance, whether I mount the old volume or not, pg_lsclusters is empty.

root@server:# docker run -it -d --name pg10 postgres:10
...
fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
...
2018-07-15 05:11:52.569 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2018-07-15 05:11:52.635 UTC [68] LOG:  database system was shut down at 2018-07-15 05:11:52 UTC
2018-07-15 05:11:52.651 UTC [1] LOG:  database system is ready to accept connections

Connections work:

root@server:# docker exec -it pg10 psql -h localhost -U postgres postgres
psql (10.4 (Debian 10.4-2.pgdg90+1))
Type "help" for help.

postgres=#

But clusters are not found:

root@server:# docker exec -it pg10 su postgres -c pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file

(Nothing given.) If that will work, then I'm intending to mount the old db volume as another cluster (via steps listed in https://stackoverflow.com/a/47233300/3358272).

I don't understand enough about the db layout to know why the default postgresql image does not produce its default database as a cluster that can be seen with this utility. Help?

Edit: the current installation lists them just fine, though it's an instance of sameersbn/postgresql, not the pg-central version:

root@server:# docker-compose exec postgresql pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

The library image defaults to creating a new database under /var/lib/postgresql/data (no version, no main).

4
  • /var/lib/postgresql/data looks un-debian
    – Jasen
    Commented Jul 15, 2018 at 5:50
  • It's in the Dockerfile as FROM debian:stretch-slim, and it's the explicit default for PGDATA in the docker hub library readme (scroll down to the *Enrivonment Variables*/PGDATA section). How does that even matter, @Jasen?
    – r2evans
    Commented Jul 15, 2018 at 6:11
  • version and main come from pg_wrapper, a debian thing to allow multiple instances and versions of postgres to co-exist. I don't know much about docker. debian specific docs in /usr/share/doc/postgresql-common
    – Jasen
    Commented Jul 16, 2018 at 6:11
  • Thanks, that's been helpful to hunt some more down. I think it's because the container uses initdb to create the new database vice using pg_createclusters. I'm trying to override that, hoping it'll facilitate the next step.
    – r2evans
    Commented Jul 16, 2018 at 15:29

1 Answer 1

0

(This is a weak answer, and I'll happily consider other answers if offered.)

Ultimately, instead of trying to migrate the pg-way, I did it manually with a raw dump of the data, started a new instance, and reloaded/reconfigured from scratch. This involved shifting from the sameersbn/postgresql image (that, as of this writing, has not been updated in over a year) to the canonical postgres image. Since shifting to that image, all upgrades/migrations have gone without issue.


Update: I've been quite happy with the tianon/postgres-update set of docker images for upgrading. It's straight-forward to use; adding to the default run, I add a mount for SSL certs and have been quite happy with:

# docker-compose stop postgres && echo y | docker-compose rm -v postgres
# docker run --rm \
  -v /my/path/docker/postgres/ssl/:/ssl/ \
  -v /my/path/docker/postgres/:/var/lib/postgresql \
  tianon/postgres-upgrade:13-to-14 --link
# docker-compose up -d postgres

(The /ssl portion is required based on the configuration in the /13/ path of files.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.