47

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?

4 Answers 4

73

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.

2
  • in that console output, there are two columns of numbers 871, 794 for instance. Which one is the PID? Jun 7, 2020 at 22:11
  • 1
    @mLstudent33 cmd, pid, parent_pid, ... Jun 8, 2020 at 6:40
7

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.

5
  • 1
    I guess, if this is officially meant to work, this should be the correct answer. Is it a new option?
    – matanox
    May 31, 2018 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, 2018 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, 2018 at 15:24
  • Using kill is safe with Postgres, but using pg_ctl is recommended to make sure associated processes are terminated along with it: postgresql.org/docs/9.3/server-shutdown.html Mar 24, 2022 at 18:01
  • ^ don't link to obsolete documentation. 9.3 was EOL 4 years ago. Better: postgresql.org/docs/14/server-shutdown.html
    – dland
    Mar 28, 2022 at 16:04
2

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

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

You can simply run the command

systemctl stop postgresql

When you wish to restart it run

systemctl start postgresql
  • It is likely these commands will require you to append sudo

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .