PostgreSQL DBA : October 2024

Sunday, 27 October 2024

Master and Slave - Sync check - PostgreSQL

1) Run the below Query on Primary:-

SELECT

pid,

usename,

application_name,

client_addr,

state,

sync_state,

sent_lsn,

write_lsn,

flush_lsn,

replay_lsn,

(sent_lsn - replay_lsn) AS replication_lag

FROM

pg_stat_replication;

postgres=# SELECT

postgres-# pid,

postgres-# usename,

postgres-# application_name,

postgres-# client_addr,

postgres-# state,

postgres-# sync_state,

postgres-# sent_lsn,

postgres-# write_lsn,

postgres-# flush_lsn,

postgres-# replay_lsn,

postgres-# (sent_lsn - replay_lsn) AS replication_lag

postgres-# FROM

postgres-# pg_stat_replication;

------+---------+------------------+---------------+-----------+------------+------------+------------+------------+------------+-----------------

(1 row)

2. Check pg_stat_wal_receiver on the Standby Server

On the standby server, you can check the pg_stat_wal_receiver view, which provides details about the WAL receiver process:

run on standby :-

SELECT * FROM pg_stat_wal_receiver;

postgres=# SELECT * FROM pg_stat_wal_receiver;

conninfo

-------+-----------+-------------------+-------------------+-------------+-------------+--------------+----------------------------------+--------

--------------------------+----------------+----------------------------------+-----------+---------------+-------------+-------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------

11743 | streaming | 0/8000000 | 1 | 0/3FCE4B98 | 0/3FCE4B98 | 1 | 2024-10-27 21:57:51.021617+05:30 | 2024-10

-27 21:57:51.014668+05:30 | 0/3FCE4B98 | 2024-10-27 21:51:50.271248+05:30 | | 192.168.0.201 | 5433 | user=repuser passfile=/v

ar/lib/pgsql/.pgpass channel_binding=prefer dbname=replication host=192.168.0.201 port=5433 fallback_application_name=walreceiver sslmode=prefer s

slcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any

(1 row)

3. Using pg_current_wal_lsn()

While the pg_current_wal_lsn() function is not available during recovery, it can be used on the primary server to get the current WAL location:

postgres=#

postgres=# SELECT pg_current_wal_lsn();

pg_current_wal_lsn

--------------------

0/3FCE4B98

(1 row)

4. Check for Replication Lag

If the function is not available, you can calculate the replication lag using simple arithmetic with LSN values,

since pg_stat_replication provides both the current WAL LSN and the replay LSN.

Here's how to do it:

SELECT

application_name,

pg_current_wal_lsn() AS current_lsn,

replay_lsn,

pg_current_wal_lsn() - replay_lsn AS lag_bytes

FROM

pg_stat_replication;

postgres=# SELECT

postgres-# application_name,

postgres-# pg_current_wal_lsn() AS current_lsn,

postgres-# replay_lsn,

postgres-# pg_current_wal_lsn() - replay_lsn AS lag_bytes

postgres-# FROM

postgres-# pg_stat_replication;

application_name | current_lsn | replay_lsn | lag_bytes

------------------+-------------+------------+-----------

walreceiver | 0/3FCE4B98 | 0/3FCE4B98 | 0

PostgreSQL - Performance Queries

Query to Find Long-Running Queries

This SQL query returns queries that have been running for more than a specific duration.

Here, 60 seconds (1 minute) is used as an example, but you can adjust the interval as needed:

SELECT

pid,

usename,

datname,

state,

query,

NOW() - query_start AS duration

FROM

pg_stat_activity

WHERE

state = 'active'

AND NOW() - query_start > interval '60 seconds'

ORDER BY

duration DESC;

---------------------------

Alternative to Find All Running Queries

To view all running queries, regardless of duration, you can simply remove the time condition:

SELECT

pid,

usename,

datname,

state,

query,

NOW() - query_start AS duration

FROM

pg_stat_activity

WHERE

state = 'active'

ORDER BY

duration DESC;

-------------------------------------------------------------

postgres=# select count(*),usename,state from pg_stat_activity group by usename, state;

count | usename | state

-------+----------+--------

5 | |

1 | sam | idle

1 | postgres |

1 | postgres | active

7 | postgres | idle

1 | repuser | active

(6 rows)

--------------------------------------------------------------------

Locking session in PostgreSQL :-

-----------------------------------

SELECT

blocked_locks.pid AS blocked_pid,

blocked_activity.usename AS blocked_user,

blocking_locks.pid AS blocking_pid,

blocking_activity.usename AS blocking_user

FROM

pg_catalog.pg_locks blocked_locks

JOIN

pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid

JOIN

pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype

AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database

AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation

AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page

AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple

AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid

AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid

AND blocking_locks.virtualtransaction IS NOT DISTINCT FROM blocked_locks.virtualtransaction

JOIN

pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid

WHERE

NOT blocked_locks.granted;

Kill the Blocking Session:

-----------------------------------

If a transaction is blocking other sessions indefinitely,

you might need to terminate the blocking session using:

SELECT pg_terminate_backend(blocking_pid);

Common Wait Events in PostgreSQL

--------------------------------------

Lock Waits: This occurs when a session is waiting for a lock held by another session. Common types include:

RowLock: Waiting for a row-level lock.

TableLock: Waiting for a table-level lock.

IO Waits: This indicates that a session is waiting for input/output operations to complete. For instance:

BufferIO: Waiting for data to be read from or written to disk.

Network Waits: These wait events occur when sessions are waiting for data over the network.

Timeout Waits: This happens when a session waits for an operation to complete but exceeds the specified timeout.

Checking Wait Events

----------------------

To analyze wait events in your PostgreSQL instance, you can query the pg_stat_activity view,

which provides information about the current activity of all sessions:

SELECT

pid,

usename,

state,

wait_event_type,

wait_event,

query

FROM

pg_stat_activity

WHERE

state = 'active';

-- or other filters based on your needs

This query will show you the session IDs (PIDs), usernames, states, wait event types, and the queries that are currently running.

Example of Analyzing Wait Events

----------------------------------------

If you want to specifically see which sessions are waiting for locks, you can use:

SELECT

blocking.pid AS blocking_pid,

blocked.pid AS blocked_pid,

blocked.query AS blocked_query,

blocked.wait_event_type,

blocked.wait_event

FROM

pg_catalog.pg_locks blocked_locks

JOIN

pg_catalog.pg_stat_activity blocked ON blocked.pid = blocked_locks.pid

JOIN

pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype

AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database

AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation

AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page

AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple

JOIN

pg_catalog.pg_stat_activity blocking ON blocking.pid = blocking_locks.pid

WHERE

NOT blocked_locks.granted;