Postgres: How to restart postgres server inside docker container

[thucnc]

I want to restart postgres server to load new configuration in /var/lib/postgresql/data/postgresql.conf but failed to run: "/etc/init.d/postgresql restart" or "pg_ctl restart" (got error message: 'pg_ctl: cannot be run as root Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.')

Please help.

[yosifkit]

Since there is no init system running in the container, the only process running in the container is postgres itself, so docker restart container-name or docker kill -sTERM container-name followed by docker start container-name will work.

As long as you are not running the process with docker run -it --rm ..., or doing docker rm after you stop it, then the container's data will stick around and you can start it up again.

[RichardJECooke]

What if we want to do something in the container while it's running? Like for instance move Postgresql into /dev/shm so tests run superfast in RAM. You need to stop and restart Postgresql while the container is up for that or the RAM disk gets deleted.

[RichardJECooke]

I thought maybe I could try

su - postgres -c '/usr/lib/postgresql/9.6/bin/pg_ctl stop -D /var/lib/postgresql/data'
cp -r /var/lib/postgresql/data /dev/shm
su - postgres -c '/usr/lib/postgresql/9.6/bin/pg_ctl -D /dev/shm start'

But my container exits immediately after running the first line

[Rajashreegr]

I tried to restart the Postgres while it had one active connection. Now I'm not able to start it again. Is there anyway I can start it? I don't want to loose any data :(

[RichardJECooke]

I wrote up how to do this extensively a while back, it might help:
https://richardcooke.info/how-to-run-your-tests-against-an-in-memory-database-with-a-rails-rspec-mysql-example/
and
https://stackoverflow.com/questions/42226418/how-to-move-postresql-to-ram-disk-in-docker

[isharov]

You can run postgres in background (by supervisor for example), or set restart policy in compose file.

[tianon]

For the ramdisk use-case, I usually start another container to keep the ramdisk around appropriately, ala:

$ docker volume create \
--driver local \
--opt type=tmpfs \
--opt device=tmpfs \
--opt o=size=1048576k \
my-new-volume

When starting PostgreSQL:

$ docker run ... -v my-new-volume:/var/lib/...

When I want to restart PostgreSQL or re-create the container:

$ docker run -d --name temp -v my-new-volume:/save-me:ro alpine:3.7 top
$ docker stop my-postgres
$ docker run ...
$ docker rm -f temp

Since the original question is answered, I'm going to close.

[SeWieland]

As is stumbled upon the same problem a few minutes ago:
su - postgres -c "PGDATA=$PGDATA /usr/lib/postgresql/10/bin/pg_ctl -w restart"
in case of version 10, worked for me ;) :+1:

[Dean-Christian-Armada]

@SeWieland , your command will restart the container. It's not as smooth as nginx's nginx -s reload.

Command below is the perfect command to avoid restart and most importantly does not disrupt ongoing queries

$ docker exec -it {postgres_container}  psql -U postgres -c "SELECT pg_reload_conf();"

[SeWieland]

@Dean-Christian-Armada thank you! That was exactly what I searched for.
And it is actually the accurate answer to the original question.

[Budgiebrain994]

Calling pg_reload_conf() will only reload parameters which don't require a restart to update. [Source]
For this reason, a restart remains a viable option for cases where configuration options can't be reloaded.

[Dean-Christian-Armada]

But still, this proves to be useful on some configurations rather than restarting a container and having downtime

[Luokun2016]

login with postgres user, and
execute "SELECT pg_reload_conf()".