34

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?

58

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.

3

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.

  • 1
    I guess, if this is officially meant to work, this should be the correct answer. Is it a new option? – matt May 31 '18 at 16:04
  • This solution would benefit from explaining the options being used, as well as the usage of the PGDATA environment variable. My attempt at running this command resulted in a fail as there was no such database under my Linux user name. – Stephane Jul 2 '18 at 10:08
  • show data_directory can be run without specifying a database, and indeed none of my servers have a database in my name. Nor does it require PGDATA, so I'm at a loss to explain your fail. – dland Jul 13 '18 at 15:24
0

This work for me ref. https://stackoverflow.com/a/5408501/248616

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

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.