3

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.

1
  • 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, 2018 at 15:34

1 Answer 1

5

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';
1
  • thanks, the following command worked for me psql -c "COPY (SELECT 1) TO PROGRAM 'pg_ctl stop -m smart --no-wait';" This allows the transaction/query to finish without waiting for the stop and the database to stop when no more connections are active. Dec 14, 2020 at 11:22

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.