how to nicely stop all postgres processes

How do you nicely stop all postgres processes with pg_ctl (or otherwise), when you don't recall what the database directory is, nor have the PGDATA environment variable defined?

3 Answers

It's safe to:

sudo pkill -u postgres

That kills all processes running as user postgres. Or:

pkill postgres

That kills all processes named 'postgres'.

Do not use kill -9 (kill -KILL). Just kill (without options) does a SIGTERM, which is what you want.

Alternatively, you can check the pgdata location if you can connect to PostgreSQL. For example:

sudo -u postgres psql -c "SHOW data_directory";

...or by checking its environment variables in /proc/[postmaster pid]/environ, where you identify the postmaster with ps -fHC postgres. Look for the one that's the parent of the other postgres processes. For example:

postgres 794 1 0 Nov06 ? 00:00:03 /usr/pgsql-9.3/bin/postgres -D /var/lib/pgsql/9.3/data -p 5432
postgres 857 794 0 Nov06 ? 00:00:00 postgres: logger process
postgres 871 794 0 Nov06 ? 00:00:00 postgres: checkpointer process
postgres 872 794 0 Nov06 ? 00:00:00 postgres: writer process
postgres 873 794 0 Nov06 ? 00:00:00 postgres: wal writer process
postgres 874 794 0 Nov06 ? 00:00:03 postgres: autovacuum launcher process
postgres 875 794 0 Nov06 ? 00:00:07 postgres: stats collector process 

Its datadir will generally be shown on its command line.

It makes me nervous seeing kill and postgres in the same command. To answer the question using only pg_ctl, that would be:

pg_ctl -D $(psql -Xtc 'show data_directory') stop

The -X argument says to ignore the .psqlrc file. This is useful if you have psql configured to emit the time taken by a query (via the \timing command).

The -t argument says to remove the column name at the top of the output and the total number of rows produced.

The -c argument contains the SQL code to be executed.

Running a bare psql -c 'show data_directory' will probably produce the following output:

 data_directory
-------------------------- /path/to/postgresql/data
(1 row)

Hence, backticking this through $( ... ) will deliver /path/to/postgresql/data to the -D argument of pg_ctl, which will then stop the database in an orderly manner.

This work for me ref.

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'YOUR_NAME';