In this episode Josh Branchaud will show you how to kill idle connections in PostgreSQL.
Postgres Version 9.5
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.
$ 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:
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.
-# 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
So, what information does this view expose?
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
Let's see what we find if we grab the
query columns for
the current database:
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.
pid of our idle process in hand, we can now terminate the
connection using none other than the
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.
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:
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
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.