Database CLI Troubleshooting Commands: PostgreSQL, MySQL, and Redis

Database issues can bring your entire application down. The right CLI troubleshooting commands help you quickly identify connection problems, slow queries, replication lag, and resource exhaustion.

This guide covers database-specific troubleshooting. For general CLI troubleshooting, see our essential guide.

PostgreSQL Troubleshooting Commands

PostgreSQL is widely used in production. Here’s how to debug it.

Check Connection

psql -h hostname -U username -d database -c "SELECT 1;"

Basic connectivity test. Fails fast if there’s a connection problem.

Active Connections

SELECT pid, usename, application_name, client_addr, state, query_start, query 
FROM pg_stat_activity 
WHERE state != 'idle' 
ORDER BY query_start;

Find long-running queries and their sources.

Connection Count

SELECT count(*) FROM pg_stat_activity;

Compare against max_connections to check for connection exhaustion.

Connection Count by State

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

Many idle in transaction connections indicate uncommitted transactions.

Lock Investigation

SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query,
       blocking_activity.query AS blocking_query
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.relation = blocked_locks.relation
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Find queries blocking other queries.

Kill a Query

SELECT pg_cancel_backend(pid);

Gracefully cancel a query. Use pg_terminate_backend(pid) if cancel doesn’t work.

Replication Status

SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       (sent_lsn - replay_lsn) AS replication_lag
FROM pg_stat_replication;

Check replica lag and replication health.

Table Bloat

SELECT schemaname, tablename, 
       pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as size
FROM pg_tables 
WHERE schemaname = 'public' 
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC 
LIMIT 10;

Find largest tables that might need vacuuming.

MySQL Troubleshooting Commands

MySQL troubleshooting has its own set of tools.

Check Connection

mysql -h hostname -u username -p -e "SELECT 1;"

Quick connectivity test.

Process List

SHOW FULL PROCESSLIST;

See all active connections and queries.

Kill Query

KILL query_id;

Terminate a problematic query.

Active Queries Only

SELECT * FROM information_schema.processlist 
WHERE command != 'Sleep' 
ORDER BY time DESC;

Filter out idle connections.

Table Locks

SHOW OPEN TABLES WHERE in_use > 0;

Find tables with active locks.

InnoDB Status

SHOW ENGINE INNODB STATUS\G

Detailed InnoDB metrics including deadlock information.

Replication Status (Replica)

SHOW REPLICA STATUS\G

Check replication lag and errors.

Slow Query Log Status

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

Verify slow query logging is enabled.

Recent Slow Queries

mysqldumpslow -t 10 /var/log/mysql/slow.log

Summarize the 10 slowest queries from the log.

Table Size

SELECT table_name, 
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables 
WHERE table_schema = 'your_database' 
ORDER BY (data_length + index_length) DESC 
LIMIT 10;

Find largest tables.

Redis Troubleshooting Commands

Redis problems often relate to memory, connections, or slow commands.

Check Connection

redis-cli -h hostname ping

Should return PONG.

Server Info

redis-cli -h hostname INFO

Comprehensive server statistics.

Memory Usage

redis-cli -h hostname INFO memory

Check used memory versus max memory.

Connected Clients

redis-cli -h hostname INFO clients

Connection count and blocked clients.

Client List

redis-cli -h hostname CLIENT LIST

Details of all connected clients.

Slow Log

redis-cli -h hostname SLOWLOG GET 10

Last 10 slow commands.

Key Count by Database

redis-cli -h hostname INFO keyspace

Key distribution across databases.

Find Big Keys

redis-cli -h hostname --bigkeys

Scan for memory-intensive keys.

Memory Usage for Key

redis-cli -h hostname MEMORY USAGE keyname

Check specific key memory footprint.

Monitor Commands (Use Carefully)

redis-cli -h hostname MONITOR

Live command stream. High overhead—use briefly.

Replication Status

redis-cli -h hostname INFO replication

Check master/replica status and lag.

Connection Troubleshooting

Database connection issues are common across all databases.

Check Port Connectivity

nc -zv db-hostname 5432

Replace port as needed (3306 for MySQL, 6379 for Redis).

DNS Resolution

dig db-hostname +short

Verify the hostname resolves correctly.

Connection from Container

kubectl exec -it pod-name -- nc -zv db-hostname 5432

Test connectivity from within Kubernetes.

SSL/TLS Verification

openssl s_client -connect db-hostname:5432 -starttls postgres

Debug TLS handshake issues.

Building Database Troubleshooting Runbooks

Structure your CLI troubleshooting commands into procedures:

# PostgreSQL Connection Issues

## Step 1: Test Connectivity
```bash
nc -zv db.internal 5432
```

## Step 2: Check Connection Count
```bash
psql -h db.internal -U app -d production -c "SELECT count(*) FROM pg_stat_activity;"
```

## Step 3: Find Long Queries
```bash
psql -h db.internal -U app -d production -c "SELECT pid, now() - query_start as duration, query FROM pg_stat_activity WHERE state != 'idle' ORDER BY duration DESC LIMIT 5;"
```

## Step 4: Check Locks
```bash
psql -h db.internal -U app -d production -c "SELECT * FROM pg_locks WHERE NOT granted;"
```

For more templates, see our database migration runbooks.

Making Database Commands Executable

Stew transforms your database CLI troubleshooting commands into executable runbooks. Run queries with a click, see results inline, and share debugging procedures across your team.

Join the waitlist and streamline your database troubleshooting.