Unable to force drop Postgres database

Question

I'm trying to forcibly drop a PostgreSQL 9.4.4 database from a Bash script using psql:

#!/bin/bash
db="mydb"
psql -d $db -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '$db';"

I'm using the solution shown in this question but it doesn't work. What I see is this:

FATAL: terminating connectoin due to administrator command
server closed the connection unexpectedly
         This probably means the server terminated abnormally
         before or while processing the request.

What am I doing wrong? I own the database and my user role has the "Superuser" attribute as shown when I do \du from the Postgres console.

score 6 · Accepted Answer · answered Nov 07 '15 at 20:35

6

You are killing your own connection with that statement. You need to add and pid <> pg_backend_pid() to exclude the session where you run that statement:

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = '$db'
  and pid <> pg_backend_pid();

answered Nov 07 '15 at 20:35

Thanks. That worked. Is there any way to indicate to the user that they've been kicked off? When I run this command from my script when I have a postgres console session open in another window, it doesn't appear that I've been kicked off as I still see the console prompt. It's only when I try to enter a command in the console and I get a strange prompt "!>" that it appears that something is amiss. – Jim Nov 07 '15 at 21:00
How should Postgres notify the user if the connection to that user has been terminated? Plus: there is no "push" available from the server to the client. The server only responds to queries from the client – Nov 07 '15 at 21:12
Good point. Thanks for the clarification. Much appreciated. – Jim Nov 07 '15 at 22:48
@a_horse_with_no_name The client could do a non-blocking read on its socket periodically. It gets notified that the server closed the connection by the protocol message written to the socket before the server closes it, and by the following TCP socket close. Most clients - like psql and the underlying libpq library - generally don't do so, because it requires polling that is a pointless waste of resources almost 100% of the time. In other words, the server does push data to the client, the client just doesn't notice. – Craig Ringer Nov 08 '15 at 14:39

score 2 · Answer 2 · answered Nov 09 '15 at 07:44

The thing is, there can be a new connection between 'terminate' statement and 'drop' statement the safest way I use is like;

First connect another database (lets say 'postgres')
Set connection limit to 0
Terminate connections to the db
Drop db

The whole script is like;

ALTER DATABASE $db WITH CONNECTION LIMIT 0;

SELECT pg_terminate_backend(sa.pid) FROM pg_stat_activity sa WHERE 
sa.pid <> pg_backend_pid() AND sa.datname = '$db';

DROP DATABASE $db;

Unable to force drop Postgres database

2 Answers2