2

The recommended way to shutdown postgres is to send a signal representing the different shutdown modes (fast, smart, immediate) or using init managers (initd, systemd, etc.) or their equivalent on non-Linux OS. In a containerized environment it's sometimes more convenient to gain access through psql than docker exec and thus more convenient to stop the server through psql. Is there a way to do that (given that the privileges have been granted)?

The solution can be hacky since this will mostly be used to stop a postgres to delete a PostgreSQL data directory in a development environment.

  • The level of access needed to delete the data directory should be the same level of access needed to shut it down correctly. Why not just do that? – jjanes Oct 26 '18 at 15:34
3

The documentation tells you to send a signal to the master postgres process, or to let pg_ctl do this.

In SQL, you can extract the PID of the master process from pg_read_file('postmaster.pid'), but pg_cancel_backend() does not accept this PID.

However, you should be able to execute these commands with COPY (depending on what rights the postgres OS user has):

COPY (SELECT 1) TO PROGRAM 'kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`';
COPY (SELECT 1) TO PROGRAM 'pg_ctl -D /usr/local/pgsql/data stop';

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.