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?

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. https://stackoverflow.com/a/5408501/248616

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