disconnect | PostgreSQL Tutorials and Solutions

How to drop a PostgreSQL database if there are active connections to it

In this article, we will see how to drop a PostgreSQL database if there are active connections to it?

Below query is used to drop active connections on a database

Syntax for PostgreSQL 9.2 and later:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB
  AND pid <> pg_backend_pid();

Syntax for PostgreSQL 9.1 and below 9.1:
Continue reading How to drop a PostgreSQL database if there are active connections to it

How to disconnect NOLOGIN users from PostgreSQL

In this article, we will see how to disconnect NOLOGIN users from PostgreSQL. In order to make sure that all users whose login privileges has been revoked are disconnected right away.

Query to disconnect NOLOGIN users from PostgreSQL:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity sa
JOIN pg_roles r ON sa.usename=r.rolname and not rolcanlogin;

Continue reading How to disconnect NOLOGIN users from PostgreSQL

Related Posts:

Related Posts: