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
    Jul 15 '18 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
    Jul 15 '18 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
    Jul 16 '18 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
    Jul 16 '18 at 15:29
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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