PostgreSQL DBA Troubleshooting Scripts

Essential PostgreSQL Database Administration Commands: A Comprehensive Guide

Database administrators need a reliable set of commands for managing and maintaining PostgreSQL databases. This guide provides essential commands for common administrative tasks, from checking database versions to managing users and performing maintenance operations.

Basic Database Information

Version Information

To check your PostgreSQL version:

sql
show server_version;
-- or
SELECT version();

User Management

Check current user:

sql
SELECT CURRENT_USER;

Switch between users:

sql
SET session authorization your_username;
-- Reset to default user
SET session authorization DEFAULT;

Database Size Information

View sizes of all databases:

sql
SELECT 
    pg_database.datname AS "database_name",
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size_in_mb
FROM pg_database
ORDER BY size_in_mb DESC;

Time Zone Management

Check timezone settings:

sql
show timezone;
SELECT current_setting('TIMEZONE');
SELECT CURRENT_TIME;

Detailed timezone configuration:

sql
SELECT 
    name,
    setting,
    short_desc,
    boot_val
FROM pg_settings
WHERE name = 'TimeZone';

Table and View Analysis

View table sizes and row counts:

sql
SELECT 
    nspname AS schemaname,
    relname AS tablename,
    reltuples AS rows,
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
    AND relreplident !~ '0'
ORDER BY pg_total_relation_size(C.oid) DESC 
LIMIT 20;

Session Management

View Active Sessions

View all database sessions:

sql
SELECT 
    pid AS process_id,
    usename AS username,
    datname AS database_name,
    client_addr AS client_address,
    application_name,
    backend_start,
    state,
    state_change,
    query
FROM pg_stat_activity;

Monitor Query Execution Time

View active queries and their execution duration:

sql
SELECT 
    datname,
    pid,
    usename,
    SUBSTRING(query, 1, 100),
    wait_event,
    client_addr,
    query_start,
    state,
    (NOW() - xact_start) AS executiontime
FROM pg_stat_activity
WHERE state IN ('active')
ORDER BY executiontime DESC;

Session Management Commands

Terminate specific sessions:

sql
-- Kill specific session
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = '1123';

-- Kill all sessions for specific user
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'specific_user';

Lock Management

View current locks:

sql
SELECT 
    t.relname,
    l.locktype,
    page,
    virtualtransaction,
    pid,
    mode,
    granted
FROM pg_locks l,
     pg_stat_all_tables t
WHERE l.relation = t.relid
ORDER BY relation ASC;

Find blocking sessions:

sql
SELECT 
    pid AS blocked_pid,
    usename,
    pg_blocking_pids(pid) AS "blocked_by(pid)",
    query AS blocked_query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Maintenance Operations

ANALYZE Commands

Update table statistics:

sql
-- Analyze specific table
ANALYZE schema_name.table_name;

-- Analyze specific columns
ANALYZE schema_name.table_name (column1, column2);

-- Analyze with verbose output
ANALYZE verbose schema_name.table_name;

VACUUM Commands

Perform table maintenance:

sql
-- Basic vacuum
VACUUM schema_name.table_name;

-- Vacuum and analyze
VACUUM ANALYZE schema_name.table_name;

-- Full vacuum (requires exclusive lock)
VACUUM FULL schema_name.table_name;

Index Maintenance

Rebuild indexes:

sql
-- Rebuild specific index
REINDEX INDEX index_name;

-- Rebuild all indexes on table
REINDEX TABLE table_name;

-- Rebuild all indexes in schema
REINDEX SCHEMA schema_name;

-- Rebuild without locking (PostgreSQL 12+)
REINDEX TABLE CONCURRENTLY table_name;

Server Information

Check server uptime:

sql
SELECT NOW() - pg_postmaster_start_time() AS "uptime";
SELECT pg_postmaster_start_time();

Conclusion

These commands form a solid foundation for PostgreSQL database administration. Remember to adjust parameters and table names according to your specific database environment. Always test commands in a non-production environment first, especially those that require exclusive locks or affect multiple sessions.