Finding and Killing Connections in Postgres
How to kill idle connections in PostgreSQL. This episode is brought to you by Hashrocket, expert consultants in PostgreSQL - learn more at https://hashrocket.com and www.pgcasts.com
Transcript:
Finding and Killing Connections
Earlier this week, I ran into an issue with a connection to a project's
database. The connection should have been closed almost immediately but
instead it was hanging around idly. The details of this aren't important.
There are all sorts of scenarios where you may end up with an unwanted
connection to your database. What we want to focus on is how to track down
the connection and then kill it.
Now, there are all sorts of tools and approaches for tracking down
and killing processes. What we are about to do is more fun though because it
will all happen within a PSQL session.
To get started, let's create a connection to our database by, well,
connecting to our database.
```bash
$ psql pgcasts
```
To get a clear picture of what is going on, I am going to start a
transaction, create a table with some rows, and then select all rows from
that table:
```sql
begin;
create table franks_hats ( slogan varchar primary key );
insert into franks_hats values ('Ninja Expert'), ('Done Deal'), ('Double Cheese'), ('Arcade Champ');
select * from franks_hats;
```
Now that our idle connection is in place. Let's open up another connection
to the database.
```bash
open new tmux pane
$ psql pgcasts
```
It is from this connection that we can track down and kill our idle
connection. In addition to all of the things we typically think of Postgres
doing for us, it also monitors server process activity. Postgres exposes
this information through the `pg_stat_activity` view. One of the processes
being monitored is our idle connection. With the right query, we can find
that process.
So, what information does this view expose?
```sql
\d pg_stat_activity
```
The things that stand out to me are `datname`, the name of our database;
`pid`, the process id; and `query`, the most recent query run on this
connection.
Let's see what we find if we grab the `pid` and `query` columns for
the current database:
```sql
select pid, query from pg_stat_activity where datname = current_database();
```
We get two results and they are pretty telling. One is our current
connection and the other is our idle connection.
With the `pid` of our idle process in hand, we can now terminate the
connection using none other than the `pg_terminate_backend(int)` function.
```sql
select pg_terminate_backend(123);
```
The single row result of true tells us that the connection termination was
successful. However, it doesn't look like anything happened to our other
connection. Let's try rerunning our previous query from that connection
again.
```sql
select * from franks_hats;
```
The connection was, in fact, terminated. In this case, psql is able to
reconnect our session. Because the connection was terminated though, any
transactions will have been rolled back and locks released.
We can try this query again and see that our transaction from earlier was
indeed rolled back:
```sql
select * from franks_hats;
```
As you may have noticed, there is a lot more information packed in the
`pg_stat_activity` view, so feel free to explore it further. Also, keep in
mind that `pg_terminate_backend()` and friends are system admin functions
and you will need the appropriate permissions to run them successfully.
Check out the links in the resources for more details.
Until next time, may your data be consistent and your queries performant.
Resources
[`pg_stat_activity` view](https://www.postgresql.org/docs/curre...)
[`pg_terminate_backend(pid int)` and other system admin functions](https://www.postgresql.org/docs/curre...)
Watch video Finding and Killing Connections in Postgres online without registration, duration hours minute second in high quality. This video was added by user PG Casts by Hashrocket 07 September 2016, don't forget to share it with your friends and acquaintances, it has been viewed on our site 5,125 once and liked it 64 people.