JIYIK CN >

In this article, we will learn how to terminate a PostgreSQL session. Any open connections are run by background processes or tasks, PSQLwhich may no longer exist despite exiting the user interface or command line tool.

Use ps -efor grepto 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 PIPEto make the attached commands meaningful and work.

Let's separate the above command into two different syntaxes; . psand grep. as defined in the LINUX manual psare used to view all the running and available processes of our system.

grammar:

ps [options]

OPTIONSIncluding the following contents.

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

We did not add more available options to this option list because we currently want to look at -efkeywords. Using any of the above methods will make the command work properly.

Because we greppipe it with to give us results that match a specific pattern, it is MANPAGESlisted as a clause in to print the lines that match a specific pattern.

grammar:

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

grepSearch for a pattern in FILE. Sometimes this may not be a file but a file index.

File indexes are common in Linux systems and can indicate standard input, open files, or a set of results displayed from a command.

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

Therefore, we can killterminate the process using the command, which IDis shown in our results. This command passes a signal to the process specified to be terminated.

Order:

int kill(pid_t pid, int sig);

pidYou pass as an argument to Process-ID. This is an effective way to kill any PostgreSQL processes, tasks, and queries that may be currently executing.

We can use the following command to kill the process that is found once and appears to be our PostgreSQL session.

Order:

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

SUDO KILL -9Not suitable because it cannot clean up temporary files accessed and left behind by the process in the system and reset its terminal connection. The process also cannot delete connections to sockets it is currently using or attached to.

Instead -9, you can issue -15or -2or -1, which completely replaces -9the keyword.

killUse the following syntax.

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

To see if it worked perfectly, you can check from the results returned below.

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

PostgreSQL Basic TERMINATE_BACKEND()Functions

The solutions provided above are applicable only to Linux systems and their versions. However, we have a generic solution that may work for PostgreSQL sessions running on various operating systems.

System AdministrationUnder the hood, a set of functions are defined in PostgreSQL that return either or Server Signalingon success and failure , and send control signals to the mentioned or selected process.TrueFalse

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

It is best SUPERUSERto access the database as 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]'

PG_STAT_ACTIVITYis a view of processes in the active backend. And PIDis a column in that table (view). We then compare PIDwith our .BACKGROUND_PID

We cannot terminate our current PostgreSQL session because it is responsible for killing all concurrent and pre-existing PostgreSQL connections.

As an alternative, you can also use PG_CANCEL_BACKEND(pid), since both send SIGINTand SIGTERMto the mentioned background process.

These signals are the ones with a stricter implementation KILL. 系统信息Under the function you will be able to find PG_BACKEND_PID(), their usage is described 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. In older versions of PostgreSQL, you can use PROCPIDinstead of PID.

PostgreSQL connection in LINUXRESTART

We can RESTARTterminate all connections connected to a PostgreSQL session using the kill keyword. As mentioned in the introduction, we can kill background processes from the Task Manager in Windows, or completely reboot our system to terminate all connections that are not suitable due to inefficiency.

Command - In Linux:

sudo service postgresql restart

As for BREW, HOMEBREWa tool for installing software packages from the LINUX command line.

Command - In Linux:

brew services restart postgresql

BREWThis will work if you installed PostgreSQL using .

pg_ctl restart in UBUNTU is used to terminate a pre-existing PostgreSQL session

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

grammar:

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

To keep things simple, we can issue the name of the directory containing Postgres and then run the following command.

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

If your directories are different and distinct from the ones given above, you can find them using the next command.

ps aux | grep postgres

To understand this command, you can refer to one of the similar commands explained in detail in the first solution provided in this tutorial. Sometimes, you may need to use LAUNCHCTLthe command to enable subcommands on the command line, which may come from standard input.

Order:

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

Again, if is not found HOMEBREW``PLIST, DIRyou can run AUXthe command again.

We hope you understand the various ways we can terminate or kill PostgreSQL connection in different operating systems.