In this article, we will see how to kill PostgreSQL process ID step by step. we can kill process id by using the function pg_terminate_backend(). To execute this, we must must have superuser privileges.
Query to kill Postgres Process: Continue reading How to kill PostgreSQL process/session/connection
In this article, we will see query to check blocking in PostgreSQL Server.
Syntax to check blocking PostgreSQL Server:
SELECT datname , usename , wait_event_type , wait_event , pg_blocking_pids(pid) AS blocked_by_pid , query FROM pg_stat_activity WHERE wait_event IS NOT NULL;
Continue reading Query to check blocking in PostgreSQL Server
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
In this article, we will see difference between pg_cancel_backend and pg_terminate_backend in PostgreSQL.
pg_cancel_backend(pid) is a milder version og pg_terminate_backend(pid).
The difference between pg_cancel_backend and pg_terminate_backend in PostgreSQL is that Continue reading Difference between pg_cancel_backend and pg_terminate_backend in PostrgreSQL
As administrator, From time to time we need to investigate if there is any query running indefinitely on PostgreSQL. These long running queries may impact database performance and probably they are stuck on some background process.
1. Following queries will return currently running top 10 queries and longest running queries in the front.
Continue reading How to find long running queries in PostgreSQL