TECH SOLUTIONS

Wednesday, July 30, 2025

Step-by-Step Runbook: Enabling Logging in PostgreSQL 13.21

### 1. Identify Configuration File and Data Directory

- Connect to your PostgreSQL database using `psql`.

- Run:

```

SHOW config_file;

SHOW data_directory;

```

- Note down the returned paths for use in the next steps.

### 2. Edit the `postgresql.conf` File

- Open the `postgresql.conf` file in a text editor (replace the path as appropriate):

```sh

sudo vi /var/lib/pgsql/13/data/postgresql.conf

```

- In `postgresql.conf`, find and set these parameters:

```

logging_collector = on

log_destination = 'csvlog' # Or 'stderr', or use both separated by comma

log_directory = 'pg_log' # Folder for logs; default is relative to data_directory

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_statement = 'ddl' # 'all' for everything, 'ddl' or 'mod' for less

log_connections = on

log_disconnections = on

```

> *Uncomment the lines by removing any leading `#`.*

### 3. Save and Exit

- Write your changes and exit the editor.

### 4. Restart PostgreSQL Service

- Restart the PostgreSQL service to apply logging changes:

```sh

sudo systemctl restart postgresql-13

```

- Or, based on your system:

```sh

sudo service postgresql-13 restart

```

### 5. Verify Logging

- After restart, check the log directory, for example:

```sh

cd /var/lib/pgsql/13/data/pg_log

ls -lh

```

- Open a recent log file to confirm logs are being generated.

### 6. (Optional) Use ALTER SYSTEM for Dynamic Logging (Some Parameters Only)

- For some log settings (not `logging_collector`), you can use:

```sql

ALTER SYSTEM SET log_statement = 'ddl';

SELECT pg_reload_conf();

```

> *A full restart is required for collector activation; parameter reload is sufficient for most log detail settings.*

You have now enabled and verified logging for your PostgreSQL 13.21 installation, ensuring SQL activities and session events are recorded for review and auditing.

Tuesday, June 24, 2025

LONG RUNING QUERIES

*** LONG RUNING QUERIES

✅ STEP-BY-STEP: Investigate and Resolve Long-Running Queries

🧭 STEP 1: Identify Long-Running Queries

Use this SQL to list all currently running queries ordered by duration:

SELECT
    pid,
    usename,
    datname,
    client_addr,
    application_name,
    state,
    now() - query_start AS duration,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE state != 'idle'
  AND now() - query_start > interval '2 minutes'
ORDER BY duration DESC;

You can adjust the 2 minutes threshold based on your SLAs.

🧭 STEP 2: Investigate Query Type and Intent

Look at the output:

SELECT → check if it's scanning large tables without indexes
UPDATE / DELETE → large data change without batching
DDL like CREATE INDEX → may block writes unless concurrent

🛠 Check if the query is:

Holding locks
Waiting on disk/network (wait_event_type = IO/Client)

🧭 STEP 3: Check for Blocking Locks (if any)

Long-running queries may be blocked or are blocking others.

Run:

SELECT
    blocked_locks.pid     AS blocked_pid,
    blocked_activity.query AS blocked_query,
    now() - blocked_activity.query_start AS blocked_duration,
    blocking_locks.pid     AS blocking_pid,
    blocking_activity.query AS blocking_query,
    now() - blocking_activity.query_start AS blocking_duration
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocking_locks.transactionid = blocked_locks.transactionid
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

🧭 STEP 4: Check Query Plan (Optional but Useful)

If you're unsure why it's slow, run EXPLAIN (ANALYZE, BUFFERS) for the same query manually (on a test DB).

This shows:

Seq Scan vs Index Scan
Sort/Join bottlenecks
Rows returned vs expected

🧭 STEP 5: Terminate a Truly Problematic Query

If the query is:

Running for an unusual time
Not critical
Blocking other sessions

Terminate it safely:

SELECT pg_terminate_backend(<pid>);

Or just cancel it (safer):

SELECT pg_cancel_backend(<pid>);

🧭 STEP 6: Audit User and Source (Who’s Running It?)

SELECT pid, usename, application_name, client_addr, query_start, query
FROM pg_stat_activity
WHERE now() - query_start > interval '2 minutes'
ORDER BY query_start;

Use this to check:

Who triggered the query
Is it a batch job or application process?

🛡️ STEP 7: Prevent Long Queries in Future

Problem	Prevention
Full table scans	Add appropriate indexes
Large batch updates	Use `LIMIT` + `OFFSET` batching
Idle transactions	Set `idle_in_transaction_session_timeout`
Infinite reports	Use `LIMIT` in queries
Application retries	Throttle, set timeouts at app level

Example config (postgresql.conf):

statement_timeout = '2min'
log_min_duration_statement = '30s'

🧠 BONUS: Monitor Long Queries in Health Checks

Add this SQL in your pp_health_checks_01.sql:

-- Long Running Queries (above 2 min)
SELECT
    now() as captured_at,
    pid,
    usename,
    client_addr,
    now() - query_start AS runtime,
    query
FROM pg_stat_activity
WHERE state != 'idle'
  AND now() - query_start > interval '2 minutes'
ORDER BY runtime DESC;

You can output this to .html format from your shell script as part of daily health logs.

LOCKS

#####LOCKS

🔍 STEP-BY-STEP: Check and Resolve Locks in PostgreSQL

✅ Step 1: List Active Locks

SELECT
    pid,
    usename,
    datname,
    locktype,
    relation::regclass AS table_name,
    mode,
    granted,
    application_name,
    client_addr,
    now() - pg_stat_activity.query_start AS query_age,
    pg_stat_activity.query
FROM pg_locks
JOIN pg_stat_activity USING (pid)
WHERE NOT granted
ORDER BY query_age DESC;

This shows who is waiting for a lock, and which object is blocked.

✅ Step 2: Check Blocked vs Blocking Sessions

SELECT
    blocked_locks.pid     AS blocked_pid,
    blocked_activity.usename  AS blocked_user,
    blocked_activity.query    AS blocked_query,
    blocking_locks.pid     AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocking_activity.query   AS blocking_query,
    blocking_activity.state   AS blocking_state,
    now() - blocking_activity.query_start AS blocking_duration
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN 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.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

This gives blocking → blocked chains, with full query details and duration.

✅ Step 3: Identify the Type of Lock

Common mode values:

AccessExclusiveLock → BLOCKS all
RowExclusiveLock, ShareLock, etc. → moderate impact

Use this table:

Lock Mode	Description
AccessExclusiveLock	Full table lock (e.g., `DROP`, `ALTER`)
ExclusiveLock	`VACUUM FULL`, `REINDEX`
RowExclusiveLock	Insert/update/delete
ShareRowExclusiveLock	CREATE INDEX CONCURRENTLY conflict

✅ Step 4: Find and Terminate Blocking Queries

🔍 Option 1: Manually Terminate Blocking PID

SELECT pg_terminate_backend(<blocking_pid>);

Only do this after checking:

The blocking query is idle or stuck
It is not a system/critical session

✅ Step 5: Monitor Lock Wait Time

To monitor session locks in real-time:

SELECT pid, now() - query_start AS wait_duration, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY wait_duration DESC;

✅ Step 6: Prevent Future Locks — Good Practices

Tip	Action
🧽 Keep transactions short	Avoid leaving `BEGIN` sessions open
🛑 No DDL in business hours	Schedule `ALTER`, `DROP` off-hours
🧵 Index your foreign keys	Missing indexes cause long share locks
🔁 Avoid large UPDATE/DELETE in one go	Break into batches
💡 Set `lock_timeout` or `statement_timeout`	Prevent endless wait

SET lock_timeout = '5s';  -- optional

🛠 Example Shell Script to Auto-Detect Blocking Sessions

Let me know if you'd like a shell script that logs blocking chains, so you can add it to cron or monitoring scripts like your pp_health_checks.sh.