How to Terminate a PostgreSQL Connection

1. Use ps -ef or grep to View and Terminate Processes in PostgreSQL
2. the PostgreSQL Basic TERMINATE_BACKEND() Function
3. PostgreSQL Connection RESTART in LINUX
4. pg_ctl restart in UBUNTU for Terminating Pre-Existing PostgreSQL Session

We’ll be learning how to terminate a PostgreSQL session in this article. Any opened connection is run through background processes or tasks that may cease to exist despite exiting the user interface or the command-line tool, PSQL.

Use ps -ef or grep to View and Terminate Processes in PostgreSQL

Let’s see how we can issue these commands to kill all processes currently accessing the PostgreSQL session. Linux systems use PIPE to make appended commands make sense and then work.

Let us divide the command above into two different syntaxes; ps and grep. As defined in the LINUX manual, the ps is used to view all our system’s running and available processes.

Syntax:

ps [options]

OPTIONS include the following.

Use the standard syntax to see every process on the system:
          ps -e
          ps -ef
          ps -eF
          ps -ely

We have not added more of the available options to this options list because we currently want to look at the -ef keyword. Using any of the above will work for the command to work.

Because we pipeline it with grep, which brings us results that match a specific pattern, it is listed in the MANPAGES as a clause that prints lines that match a certain pattern.

Syntax:

grep [OPTION...] PATTERNS [FILE...]
       grep [OPTION...] -e PATTERNS ... [FILE...]
       grep [OPTION...] -f PATTERN_FILE ... [FILE...]

The grep searches for patterns in a FILE. Sometimes there may not be a file but a file index.

A file index is commonly seen in Linux systems which may indicate either a standard input, an opened file or a set of results displayed from a certain command.

In the case above, we get the set of all current processes in the Linux system and then grab the results from this set that match the PostgreSQL keyword or processes attached to PostgreSQL.

Hence, we can use the kill command to terminate the processes with their IDs as displayed in our results. The command delivers a signal to the process specified for termination.

Command:

int kill(pid_t pid, int sig);

Where in pid, you pass the Process-ID as the argument. This is an effective way to kill PostgreSQL processes, tasks and queries that may currently be in execution.

We can use the command below to kill a process found once that seems to be our PostgreSQL session.

Command:

SUDO KILL -9 $(lsof -i :3000 -t) or '#' (The former depends on MacOSX)

SUDO KILL -9 is not suitable because it cannot clean temporary files accessed and left by a process in our system and reset its terminal connections. The process can also not remove the connections to sockets to which it was currently using or attached.

Instead of -9, you may issue -15 or -2 or -1, much better alternatives altogether to the -9 keyword.

The kill uses syntax as follows.

kill [-signal|-s  signal|-p] [-q  value]

And to view if it worked perfectly, you can check from the following returned results.

0      success
1      failure
64     partial success (when more than one process is specified)

the PostgreSQL Basic TERMINATE_BACKEND() Function

The solution provided above is specific to only Linux systems and their versions. However, we have universal solutions that may work for PostgreSQL sessions being run on various operating systems.

Under the System Administration functions defined in PostgreSQL, a set of Server Signaling functions return True or False on success and failure and send control signals to processes mentioned or selected.

As listed in the documentation, these functions are restricted to superusers by default, but access may be granted to others using GRANT, with noted exceptions.

It is better to access the database being a SUPERUSER to use such commands. To call this function, you can write the following query.

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE
pid != pg_backend_pid()  AND datname = '[your_Database_name]'

The PG_STAT_ACTIVITY is a view of the processes that are in the active backend. And PID is a column in this table (view). We then compare the PID to our BACKGROUND_PID.

We can not terminate our current PostgreSQL session, as it is responsible for killing all concurrent and pre-existing PostgreSQL connections.

As an alternative, you can also use PG_CANCEL_BACKEND(pid) as they both send SIGINT and SIGTERM to the background processes mentioned.

These signals are KILL signals with a stricter implementation. Under the System Information Functions, you will be able to find the PG_BACKEND_PID(), and its use is explained below.

pg_backend_pid()    int Process ID of the server process attached to the current session

Make sure not to accidentally terminate our current session as well. In older versions of PostgreSQL, you may use PROCPID instead of PID.

PostgreSQL Connection RESTART in LINUX

We can kill all connections attached to a PostgreSQL session using the RESTART keyword. As stated in the introduction, we can either kill the background processes from the task manager in Windows or restart our system entirely to terminate all connections which are not suitable due to their in-efficiency.

Command - In Linux:

sudo service postgresql restart

And for BREW, short for HOMEBREW, a tool to install packages from the LINUX command line.

Command - In Linux:

brew services restart postgresql

This would work if PostgreSQL were installed using BREW.

pg_ctl restart in UBUNTU for Terminating Pre-Existing PostgreSQL Session

In the UBUNTU MAN PAGES, you can see PG_CTL, which initializes, starts, stops or controls a PostgreSQL session. Control can also include RESTART.

Syntax:

pg_ctl restart [-w] [-t seconds] [-s] [-D datadir] [-c]
              [-m s[mart] | f[ast] | i[mmediate]] [-o options]

And for simplicity, we can issue the directory name containing Postgres and then run the following command.

pg_ctl restart -D /usr/local/var/postgres

If your directories are different and not the same as those given above, you may find them using this next command.

ps aux | grep postgres

To understand this command, you may refer to one of the similar commands explained in detail in the first solution provided in this tutorial. Sometimes, you might need to use the LAUNCHCTL command to enable sub-commands on the command line, which may be directed from the standard input.

Command:

$ launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
$ launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Once again, if you cannot find the DIR for HOMEBREW PLIST, you may run the AUX command again.

We hope that you learned the various ways through which we can terminate or kill connections of PostgreSQL in different operating systems.