postgresql: can't get docker postgres container in single user mode

Question

I need to start my postgresql server in single user mode so I can restore the superuser role. IN this case it is the standard docker image for postgres10

I bash into the container. as user postgres and from /usr/lib/postgresql/10/bin I do:

./pg_ctl stop -D /var/lib/postgresql/data

as a first step before starting it in single mode.

I see waiting for server to shut down ...

and then I am dumped back to the host.

I have worked out it is nothing to do with the restart policy.

EDIT: the postgresql server process is pid 1 so the container exits when postgresql stops.

Complicated. You probably have to install PostgreSQL outside the container and start the server on the data directory (of course after stopping the container). — Laurenz Albe
– Laurenz Albe, Commented Apr 21, 2021 at 14:08
I will first try putting a script in /docker-entrypoint-initdb.d, inspired by stackoverflow.com/q/28244869/401226 — Tim Richardson
– Tim Richardson, Commented Apr 21, 2021 at 21:02

Tim Richardson · Accepted Answer · 2022-08-25 07:30:32Z

8

To do this, you need to start the container like with a shell and without the postgresql server process starting as pid=1, which is how the container normally works. It seems like you are trapped: you can't stop the postgresql server without killing the container.

You need the database volume because roles are stored with the database. So you also need to discover how the database volume is mounted to your container (it may be obvious if you have a named volume, but I use ansible for deployment, so I don't have a named volume.)

So with the docker container running normally (my container when running normally is named django_api_sync_db and the postgres image is postgres:10 The owner user which lost superuser role due to bad ansible copy and paste is django_api_sync_db)

docker inspect django_api_sync_db

and search for the Mounts section. In my case, I see a bind mount:

"Mounts": [
            {
                "Type": "bind",
                "Source": "/var/docker/django_api_sync/db",
                "Destination": "/var/lib/postgresql/data",
                "Mode": "rw",
                "RW": true,
                "Propagation": "rprivate"
            }
        ],

stop the container, and then restart it with an interactive shell like this (in my case with postgresql v 10):

docker run --mount type=bind,source="/var/docker/django_api_sync/db",target="/var/lib/postgresql/data"  -it postgres:10 /bin/bash

now, postgresql is not running. If curious, do

apt-get update && apt-get install -y procps

and

ps -ef

(aside: The default entry point of the postgres image means the postgresql server is pid 1, so you can't go into single user mode without immediately terminating the container.)

Since we are now in the container, discover the directory with the binaries. Note: we are root.

# which postgres

my output is: /usr/lib/postgresql/10/bin/postgres

Change user and go into postgres single user mode:

su postgres

/usr/lib/postgresql/10/bin/postgres --single -D /var/lib/postgresql/data

and from the backend:

backend> alter user django_api_sync with superuser;

You are finished. Exit and do whatever you need to do to get the database container running normally again (e.g. systemctl restart docker)

edited Aug 25, 2022 at 7:30

answered Apr 21, 2021 at 22:55

Tim Richardson

7,3516 gold badges52 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Maze Over a year ago

Are you launching this originally from Compose? docker-compose run will use the volumes: from the docker-compose.yml, so you don't have to rediscover them.

Tim Richardson Over a year ago

My deployment uses an ansible playbook which uses just docker and has this line volumes: /var/docker/django_api_sync/db:/var/lib/postgresql/data/ I don't know much about docker. On my dev machine I use docker compose. I was very, very confused about how to mount the volume in production until I did the docker inspect and saw it was a "bind" mount. Hence, documented for posterity.

Collectives™ on Stack Overflow

postgresql: can't get docker postgres container in single user mode

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related