PostgreSQL — More on DBA Basics
PostgreSQL Database Session and Deadlock Management
In my last article, I went through first with PostgreSQL user, role and schema management and then I have shown you how we can manage Database, Tablespace, Tables and Vacuum process.
In the article first, I will go through PostgreSQL session — to enable and disable, then I will go through Deadlock and long-running
First, connect to the database server as a PostgreSQL user
$sudo su — postgres
Then set up a session
> psql
Session Enable and Disable — Database Level or System Level
Database Level — To disable session
+++++++++++++++++++
postgres=# alter database dbadb with allow_connections false;
postgres=# \c dbadb
FATAL: database “dbadb” is not currently accepting connections
Previous connection kept
Database Level — To enable session
+++++++++++++++++++
postgres=# alter database dbadb with allow_connections true;
ALTER DATABASE
postgres=# \c dbadb
You are now connected to database “dbadb” as user “postgres”.
For system level
+++++++++
To disable access at the system level
- set pg_hba.conf (by commenting client connection entries or setting method=reject)
Deadlock
In a database, a deadlock is a situation that occurs when two or more different database sessions are waiting for each other. It caused some data locked, as illustrated in the following diagram (Reference: https://vladmihalcea.com/database-deadlock/).
Lets set up a deadlock session —
First, we see the deadlock timeout parameter value,
postgres=# show deadlock_timeout ;
deadlock_timeout
— — — — — — — — —
1s
(1 row)
From the first session, Going to run an update on the ‘dedlktst’ table. Here we use BEGIN to avoid auto-commit -
postgres=# BEGIN;
BEGIN
postgres=# UPDATE dedlktst SET region=’Asia’ WHERE code=’BNG’;
UPDATE 1
Then from the second session, execute below commands
postgres=# begin;
BEGIN
postgres=# UPDATE dedlktst SET region=’Oceania’ WHERE code=’AUS’;
UPDATE 1
postgres=# UPDATE dedlktst SET population=4901 WHERE code=’BNG’;
— — — Hanging
Now if I go back to the first session and execute below command -it will prompt a message -
postgres=# UPDATE dedlktst SET population=9001 WHERE code=’AUS’;
ERROR: deadlock detected
DETAIL: Process 91836 waits for ShareLock on transaction 600; blocked by process 91896.
Process 91896 waits for ShareLock on transaction 598; blocked by process 91836.
HINT: See server log for query details.
CONTEXT: while updating tuple (0,2) in relation “dedlktst”
To get more in details we can see the log in our PostgreSQL server
$ tail -30 postgresql-Mon.log
— — — —
2019–11–11 15:38:47.325 AEDT [91896] STATEMENT: UPDATE country SET population=15864001 WHERE code=’BNG’;
2019–11–11 15:39:01.242 AEDT [91896] ERROR: current transaction is aborted, commands ignored until end of transaction block
2019–11–11 15:39:01.242 AEDT [91896] STATEMENT: UPDATE dedlktst SET population=15864001 WHERE code=’BNG’;
2019–11–11 15:39:19.098 AEDT [91896] ERROR: current transaction is aborted, commands ignored until end of transaction block
2019–11–11 15:39:19.098 AEDT [91896] STATEMENT: UPDATE dedlktst SET population=15864001 WHERE code=’BNG’;
2019–11–11 15:40:05.402 AEDT [91836] ERROR: deadlock detected
2019–11–11 15:40:05.402 AEDT [91836] DETAIL: Process 91836 waits for ShareLock on transaction 600; blocked by process 91896.
Process 91896 waits for ShareLock on transaction 598; blocked by process 91836.
Process 91836: UPDATE dedlktst SET population=18886001 WHERE code=’AUS’;
Process 91896: UPDATE dedlktst SET population=15864001 WHERE code=’BNG’;
2019–11–11 15:40:05.402 AEDT [91836] HINT: See server log for query details.
2019–11–11 15:40:05.402 AEDT [91836] CONTEXT: while updating tuple (0,2) in relation “dedlktst”
2019–11–11 15:40:05.402 AEDT [91836] STATEMENT: UPDATE dedlktst SET population=18886001 WHERE code=’AUS’;
Long running queries
Finding long running queries
+++++++++++++++++
SELECT
pid,
now() — pg_stat_activity.query_start AS duration,
query,
state
FROM pg_stat_activity
WHERE (now() — pg_stat_activity.query_start) > interval ‘1 minutes’;
Killing long-running queries
++++++++++++++++
To cancel long-running queries either we can stop the query and then terminate the session more safely or kill the process from the OS level:
i) To stop the query we can execute below command
SELECT pg_cancel_backend(<pid>);
ii) If we see that the process is hanging for a long even after stopping the query then we can kill the process executing below command
SELECT pg_terminate_backend (<pid>);
or by executing kill command at the OS level:
kill -9 <PostgreSQL Process Id>
Now we will go through a lab setup
For our testing purpose, we consider for 5 minutes. In a real scenario, it will be based on the much higher job duration time compare to the normal run time.
i. From the first session, we can execute a SQL to run for 5 minutes.
$ psql
postgres=# select pg_sleep(5 * 60);
ii. From the second session, we are executing the below query to a session running more than a minute.
SELECT
pid,
now() — pg_stat_activity.query_start AS duration,
query,
state
FROM pg_stat_activity
WHERE (now() — pg_stat_activity.query_start) > interval ‘1 minutes’;
pid | duration | query | state
— — — -+ — — — — — — — — -+ — — — — — — — — — — — — — + — — — —
93443 | 00:02:24.981683 | select pg_sleep(5 * 60); | active
(1 row)
Cancel the session
===========
postgres=# SELECT pg_cancel_backend(93443);
pg_cancel_backend
— — — — — — — — — -
t
(1 row)
postgres=# SELECT
postgres-# pid,
postgres-# now() — pg_stat_activity.query_start AS duration,
postgres-# query,
postgres-# state
postgres-# FROM pg_stat_activity
postgres-# WHERE (now() — pg_stat_activity.query_start) > interval ‘1 minutes’;
pid | duration | query | state
— — — -+ — — — — — — — -+ — — — — — — — — — — — — — + — — — -
93443 | 00:04:10.8239 | select pg_sleep(5 * 60); | idle
(1 row)
Now terminate the session
==========================
postgres=# SELECT pg_terminate_backend (93443);
pg_terminate_backend
— — — — — — — — — — —
t
(1 row)
postgres=# SELECT
postgres-# pid,
postgres-# now() — pg_stat_activity.query_start AS duration,
postgres-# query,
postgres-# state
postgres-# FROM pg_stat_activity
postgres-# WHERE (now() — pg_stat_activity.query_start) > interval ‘1 minutes’;
pid | duration | query | state
— — -+ — — — — — + — — — -+ — — — -
(0 rows)
In this article, I have gone through how we can manage a deadlock and long-running session in the PostgreSQL database.
