PostgreSQL Monitoring and Alerts: Practical Examples for Reliable Operations

Intro

PostgreSQL is a powerful open-source relational database, but its reliability depends on how well you monitor it and respond to alerts. Many teams treat monitoring as an afterthought until an outage forces them to scramble. This article provides practical, actionable guidance for setting up monitoring and alerts for PostgreSQL, aimed at developers, DevOps engineers, and technical startup teams who manage their own databases. You will learn what metrics to collect, how to interpret them, how to configure meaningful alerts, and how to build a dashboard that helps you diagnose problems quickly. Each section includes concrete commands, expected outputs, and recovery steps so you can apply these practices immediately.

We focus on operational safety: observe before making changes, limit the blast radius of any intervention, use placeholders instead of secrets in configuration, verify the result of every action, and document recovery paths in advance. Throughout, we assume a PostgreSQL environment without managed cloud monitoring services, so the examples use native tools like psql, SQL queries, and shell commands. The goal is to give you a solid foundation that works on any PostgreSQL deployment.

Version and Environment Inventory

Before you can monitor PostgreSQL effectively, you need to know exactly what you are dealing with. Start by identifying the installed version, deployment topology, and prerequisites. This inventory will guide every subsequent step, because commands and configuration options vary across PostgreSQL versions. For example, the pg_stat_statements extension requires different setup steps on PostgreSQL 13 versus 14, and connection poolers like PgBouncer add another layer to consider.

Run the following read-only command to get the version and some basic settings:

SELECT version();

Expected output looks like:

PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 12.2.1 20221121 (Red Hat 12.2.1-4), 64-bit

This tells you the major version (15), minor version (3), operating system, and compiler. It is critical because monitoring queries sometimes rely on columns or views added in later releases. For instance, pg_stat_activity includes backend_type since PostgreSQL 10, but older versions do not have it.

Next, check the current configuration relevant to monitoring:

SHOW shared_preload_libraries;
SHOW track_activities;
SHOW track_counts;
SHOW track_functions;
SHOW log_min_duration_statement;

Sample output:

           shared_preload_libraries
-----------------------------------------------
 pg_stat_statements, auto_explain
(1 row)

 track_activities
------------------
 on
(1 row)

 track_counts
--------------
 on
(1 row)

 track_functions
-----------------
 none
(1 row)

 log_min_duration_statement
----------------------------
 -1
(1 row)

Here, shared_preload_libraries includes pg_stat_statements, which is essential for query performance monitoring. track_activities and track_counts are on, so session and cumulative statistics are collected. track_functions is set to none, meaning function call statistics are not tracked; you may want to change this to pl or all if you rely on stored procedures. log_min_duration_statement is -1, which disables logging of long-running statements; setting it to a positive value like 1000 (milliseconds) would log any query taking over a second, useful for slow query identification.

For a deployment running under Docker, you might also check:

docker exec <container_name> psql -U <user> -c "SELECT version();"

Replace <container_name> and <user> with actual values. This command allows you to run psql inside a container without entering it interactively.

Prerequisites for this section: you need access to the PostgreSQL instance via psql or another client, with read-only privileges on system catalogs. The observation commands above are read-only and safe to run on a production system. They do not change any state.

If you encounter a permission error like:

ERROR: permission denied for function version

It means your user lacks the necessary privileges. In that case, either grant USAGE on the relevant functions or use a superuser or monitoring-specific role. To recover, adjust the role permissions and re-run the command.

Safe Configuration Path

Configuring monitoring in PostgreSQL often requires changing settings that need a server restart or reload. The safe path is to first observe the current configuration, understand the impact of each change, make the smallest possible modification, and verify the result. Never apply multiple changes at once, because if something goes wrong, it is hard to isolate the cause.

A common monitoring change is enabling the pg_stat_statements extension. This extension records statistics about all SQL statements executed, including query text, execution counts, total time, and resource usage. It is invaluable for identifying slow or frequently run queries.

First, check if the extension is available and installed:

SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

If the first query returns a row with installed_version as NULL, the extension is not installed. If the second query returns no rows, it confirms the absence.

To enable pg_stat_statements, you need to add it to shared_preload_libraries in postgresql.conf. This requires a restart. The safe steps are:

shared_preload_libraries = 'pg_stat_statements'

1. Backup your current postgresql.conf file (or at least note the current value of shared_preload_libraries).
2. Edit postgresql.conf and add pg_stat_statements to the list, for example:
3. Restart PostgreSQL. On a systemd-based Linux, this is often:

   sudo systemctl restart postgresql

1. After restart, connect and run:

   CREATE EXTENSION pg_stat_statements;

1. Verify by running:

   SELECT * FROM pg_stat_statements LIMIT 1;

If the query returns a row (or even no rows but no error), the extension is active.

Expected output after the CREATE EXTENSION command:

CREATE EXTENSION

And a verification query:

SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_stat_statements';

Output:

      extname      | extversion
-------------------+------------
 pg_stat_statements| 1.10
(1 row)

If you are using a managed service or cannot restart, alternative monitoring methods like using pg_stat_activity and pg_stat_database are available, but they provide less query-level detail.

Another safe configuration example is adjusting log_min_duration_statement. This sets the threshold for logging slow queries. For example:

ALTER SYSTEM SET log_min_duration_statement = '1000';
SELECT pg_reload_conf();

This change does not require a restart; pg_reload_conf() sends a signal to reload configuration files. Verify with:

SHOW log_min_duration_statement;

Expected:

 log_min_duration_statement
----------------------------
 1s
(1 row)

Note: PostgreSQL accepts time units, so '1000' is interpreted as 1000 milliseconds, displayed as 1s.

Always test configuration changes in a staging environment before applying to production. Document each change and the reason, and keep a rollback plan (e.g., restore the original postgresql.conf and restart).

Verification and Diagnostics

Once monitoring is set up, you need to verify that data is being collected and use that data for diagnostics. This section provides read-only queries to inspect the health and performance of your PostgreSQL instance.

Start with a simple connectivity and activity check:

SELECT count(*) FROM pg_stat_activity;

This returns the number of current connections, including idle ones. A healthy system typically has a number well below max_connections. If it is near the limit, you may have connection leaks or need a connection pooler.

To see active queries that have been running longer than 5 seconds:

SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE state = 'active' AND now() - pg_stat_activity.query_start > interval '5 seconds'
ORDER BY duration DESC;

Sample output:

 pid  |    duration     |                              query                              | state
------+-----------------+-----------------------------------------------------------------+--------
 1234 | 00:12:34.56789  | SELECT * FROM large_table WHERE unindexed_column = 'value';     | active
(1 row)

This shows a query running for over 12 minutes, likely stuck due to a missing index or a lock. To investigate further, you can check for blocking locks:

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_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.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 blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

This query identifies sessions waiting for locks and the sessions holding those locks. The output includes the PIDs and query texts, helping you decide whether to cancel or terminate a blocking query. To cancel a query gracefully:

SELECT pg_cancel_backend(<blocking_pid>);

Or if that fails, terminate the backend:

SELECT pg_terminate_backend(<blocking_pid>);

Use termination with caution, as it may cause the blocked query to fail.

Another diagnostic is checking replication lag, critical in streaming replication setups:

SELECT application_name, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;

Output on a healthy replica with minimal lag:

 application_name | lag_bytes
------------------+-----------
 standby1         |        0
(1 row)

If lag_bytes is consistently high (e.g., several megabytes or more), the replica may be falling behind, risking data loss if a failover occurs. Investigate network latency, replica resource contention, or long-running transactions on the primary.

For query performance, pg_stat_statements provides aggregated statistics:

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Sample output:

-[ RECORD 1 ]----+---------------------------------------------------------------
query            | SELECT * FROM orders WHERE customer_id = $1;
calls            | 15000
total_exec_time  | 120000.5
mean_exec_time   | 8.0
rows             | 15000
-[ RECORD 2 ]----+---------------------------------------------------------------
query            | UPDATE inventory SET quantity = quantity - $1 WHERE product_id = $2;
calls            | 8000
total_exec_time  | 60000.2
mean_exec_time   | 7.5
rows             | 8000

Here, the top query is executed frequently and consumes significant time. The mean execution time of 8 ms may be acceptable, but since it runs 15,000 times, optimizing it (e.g., adding an index on customer_id) could yield substantial benefits.

Verify that the statistics are collecting properly by checking the view's timestamp:

SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();

If stats_reset is recent unexpectedly, stats may have been reset manually or by a crash.

Failure Modes and Recovery

Monitoring itself can fail, and so can the PostgreSQL instance. This section covers common failure modes, how to detect them via monitoring, and steps to recover.

Failure: Monitoring queries stop returning data.

If your dashboard suddenly shows no data, first check if the monitoring connection is working. Run a simple query from the monitoring tool's host:

psql -h <db_host> -U <monitoring_user> -d postgres -c "SELECT 1;"

If this fails with a connection error, the issue may be network, authentication, or PostgreSQL is down. Check the PostgreSQL service status:

sudo systemctl status postgresql

If the service is active but connections are refused, check pg_hba.conf for the monitoring user's authentication rules and ensure the user has CONNECT privilege on the database. For example, a rule might be:

host    postgres    monitoring_user    192.168.1.0/24    md5

If the rule is missing, add it and reload:

sudo systemctl reload postgresql

Failure: Disk space full.

A full disk can cause PostgreSQL to stop accepting writes or crash. Monitor disk usage with:

df -h /var/lib/postgresql/data

If usage is above 80%, set an alert. To recover, you can archive or delete old WAL files (after ensuring they are no longer needed for replication or point-in-time recovery), clean up old logs, or add storage. Be careful not to delete critical files; use pg_archivecleanup if using continuous archiving.

Failure: Replication lag too high.

As mentioned, high lag can be dangerous. Monitor pg_stat_replication regularly and set an alert if lag exceeds, say, 100 MB. To recover, first identify the cause. Check for long-running transactions on the primary:

SELECT pid, now() - xact_start AS duration, query FROM pg_stat_activity WHERE state = 'idle in transaction' AND now() - xact_start > interval '1 minute';

These idle transactions can hold locks and prevent WAL replay. Terminate them if safe. Also, ensure the replica has enough resources (CPU, I/O). If lag persists, you may need to rebuild the replica using pg_basebackup.

Failure: Too many connections.

If pg_stat_activity count approaches max_connections, new connections will fail with "FATAL: sorry, too many clients already". To recover temporarily, you can increase max_connections (requires restart) but that consumes more memory. Better long-term solution: implement connection pooling with PgBouncer or adjust application connection settings. Monitor connection count and set an alert at 80% of max.

For each failure mode, document a runbook with steps for detection, diagnosis, and recovery. Test the runbook in a staging environment.

Operations Checklist

This checklist summarizes the key tasks for ongoing PostgreSQL monitoring and alerting. Assign a single accountable owner for each task and set a review frequency. Example: Priya Shah, Engineering Lead, reviews these weekly.

Use this checklist as a starting point and adapt it to your environment. Automate as much as possible using a monitoring system like Prometheus with postgres_exporter, or Zabbix with its PostgreSQL template. The key is consistency: monitoring is not a one-time setup.

Common Pitfalls and How to Avoid Them

1. Ignoring version differences: Using queries or settings from an old version on a newer one can produce errors or misleading data. Always verify your PostgreSQL version and consult the official documentation for that version.

1. Collecting too many metrics without purpose: It is tempting to enable every available metric, but that creates noise and storage overhead. Start with a focused set: connections, query performance, replication lag, disk usage, and error rates. Add more only when needed.

1. Setting alert thresholds too tight or too loose: If alerts fire for every small deviation, people start ignoring them (alert fatigue). If thresholds are too high, you miss real problems. Set thresholds based on baseline performance over time, and adjust as your workload changes.

1. Not testing alerts: An alert that does not actually notify anyone is useless. Regularly test your alerting pipeline by triggering a test alert manually.

1. Using superuser for monitoring connections: This grants unnecessary privileges and increases security risk. Create a dedicated monitoring role with read-only access to statistics views and minimal other permissions.

1. Neglecting log monitoring: PostgreSQL logs contain valuable information about errors, long queries, and autovacuum issues. Configure log_min_duration_statement, log_checkpoints, and log_connections appropriately, and ship logs to a central system for analysis.

1. Not documenting recovery steps: In an emergency, you do not want to figure out what to do. Write runbooks for common issues like disk full, high CPU, and replication lag, and keep them accessible.

Conclusion

PostgreSQL monitoring and alerts are essential for maintaining a reliable database service. By following the practices in this article, you can detect problems early, diagnose them efficiently, and recover with minimal downtime. Remember to start with a clear inventory of your environment, make configuration changes safely, verify that monitoring is working, and prepare for failure modes with documented recovery procedures. Regularly review your monitoring setup and adjust as your application evolves. With these practical examples, you can build a solid observability foundation for your PostgreSQL deployment.

As a next step, choose one low-risk verification from this article, such as confirming the pg_stat_statements extension is enabled or checking replication lag. Run the command, note the current state, and compare the result with the expected output. Then set up a simple alert for a key metric, and test that it reaches your team. Over time, expand your monitoring to cover more metrics and integrate with your incident response process. A reliable monitoring system makes failures visible before they become outages, protects your data, and gives you confidence in your operations.