A comprehensive Postgres troubleshooting guide

PostgreSQL is a relational database system known for its versatility in handling not only structured data but also JSON and JSON-binary formats. As the backbone of many distributed IT infrastructures, Postgres plays a crucial role in managing and storing large volumes of data.

Given its central importance, it becomes crucial to troubleshoot and resolve Postgres issues quickly before they ripple through the entire ecosystem. This guide will walk you through all the common problems that you may face with Postgres, including startup and connection problems, configuration errors, replication issues, and performance bottlenecks.

Startup and connection problems

Let’s look at some issues you may face when starting or connecting with a Postgres server.

Issue # 1 – Postgres service not starting

Problem: You are trying to start the Postgres service on your machine but it fails to launch.

Detection: The server log might show some errors or remain silent.

Troubleshooting steps:

• The Postgres logs (usually located in postgresql.log) often contain clues about startup failures. Look for error messages related to port conflicts, configuration errors, storage space, or missing permissions. If you don’t find anything helpful, add -v to the Postgres startup command to increase the logging verbosity and then retry.
• Ensure the port specified in the Postgres configuration file (default: 5432) is not already being used by another process. Use the netstat command to check:

  netstat -nlp | grep 5432

• Ensure the server’s storage and memory resources are sufficient. Postgres documentation states that it will not start if you don’t have “well over” 20 MBs of free space under /usr/local and at least 25 MBs free on the disk partition where the database resides.
• Double-check your configuration file for typos or syntax errors. Pay attention to settings such as listen_addresses and data_directory, as problems in these parameters can directly lead to startup failures.

Issue # 2 – Unable to connect to Postgres

Problem: Postgres clients (such as psql, pgAdmin, and web applications) are unable to connect to the Postgres server.

Detection: You get errors on the client application related to connection failures/timeouts.

Troubleshooting steps:

• Ensure the Postgres server is actually running. Check the process list (using the ps command) or use pg_ctl status to verify.
• Check the Postgres logs for errors related to connection rejections.
• Double-check the hostname, port, username, and password used for connection. Are they correct as per your configuration?
• If the client is remote, make sure that the pg_hba.conf file allows connections from the client’s IP address or network range.
• If you are observing authentication-related failures, verify that the pg_hba.conf file allows the authentication method you are using (e.g., password, trust).
• Check if firewall rules are blocking access to the Postgres port. To test this, consider temporarily disabling the firewall.

Issue # 3 – Slow startup

Problem: Postgres takes an unusually long time to start.

Detection: After issuing the command to start Postgres, you have to wait several seconds for the Postgres service to reach a healthy state.

Troubleshooting steps:

• Check the Postgres server logs for any warnings or errors.
• If you see the line “database system was not properly shut down; automatic recovery in progress” in the logs, it indicates that the server was either killed or crashed, and is now trying to get back to a consistent state. In scenarios like this, it’s important to let the recovery process take its course.
• If you are running a containerized version of Postgres, ensure that you are using an official Postgres image. Moreover, check for volume mounting or networking related issues.
• Review configuration settings, especially those related to shared_buffers and effective_cache_size. These parameters control the amount of memory Postgres allocates for caching at startup.

Postgres configuration issues

Let’s now explore some common Postgres misconfigurations and find out how to detect and resolve them.

Misconfiguration #1 – Overly generous listen_addresses

Misconfiguration: The listen_addresses setting specifies the network addresses where the Postgres server listens for incoming connections. If you specify “*” here, the server starts accepting connections from everywhere, which exposes your database to potential attackers.

Detection: Review the Postgres configuration file or execute this command on Postgres:

SHOW listen_addresses;

Resolution: Limit listen_addresses to specific IPs or trusted networks. Use pg_hba.conf to enforce more granular access control and stronger authentication.

Misconfiguration # 2 – Unoptimized shared_buffers

Misconfiguration: The shared_buffers parameter manages the shared memory buffer for frequently accessed data. It’s a great way to boost throughput but it must be configured judiciously. Setting it too low can cause frequent disk accesses and bottlenecks, whereas overallocation can consume valuable system resources.

Detection: Try to calculate the buffer hit ratio to determine the efficiency of the shared_buffers setting. A low ratio indicates cache misses and potential shared buffer issues. For example, you can use the following query to estimate the buffer_hit_ratio, calculated as the ratio of hits to total (hits + reads).

SELECT
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit) as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as buffer_hit_ratio
FROM pg_statio_user_tables;

Resolution: Tune shared_buffers based on available memory and workload. You can also use the pg_buffercache extension for this purpose.

Misconfiguration # 3 – A suboptimal value for max_connections

Misconfiguration: The max_connections setting dictates the maximum number of concurrent connections allowed. Setting it too low may lead to frequent connection failures while exceeding available resources can impact server stability.

Detection: Monitor server connection statistics and resource utilization to identify connection spikes. For example, the following query returns a list of currently active connections:

SELECT * FROM pg_stat_activity WHERE state = 'active';

Resolution: Set max_connections based on expected workload and available resources. Also consider enabling connection pooling in applications to optimize resource utilization.

Postgres performance bottlenecks

Next, let’s talk about some common performance bottlenecks that can occur on any Postgres instance.

Bottleneck # 1 – Long-running queries

Bottleneck: Applications become sluggish and users complain about delayed responses.

Detection: Monitor pg_stat_activity for queries that have been running for an extended period. For example, use this command to check for active queries with high execution times.

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

Resolution: Analyze the query plan using EXPLAIN to understand the execution path. Look for inefficient joins, complex calculations, and missing indexes. Once you have identified the root cause, optimize the query by rewriting it, indexing relevant columns, or utilizing materialized views. You may also consider query caching or partitioning large tables.

Bottleneck # 2 – Too many idle connections

Bottleneck: Clients are struggling to connect to the Postgres server because there are too many idle connections.

Detection: Monitor pg_stat_activity for connections that have been idle for too long. For example, you can use this command to view sessions that have been idle for 5 or more minutes:

SELECT
  pid,
  now() - pg_stat_activity.query_start AS idle_duration,
  query
FROM pg_stat_activity
WHERE state = 'idle'
  AND now() - pg_stat_activity.query_start >= interval '5 minutes';

Resolution:

• Consider enabling connection pooling to promote connection reuse and better manage idle connections.
• Configure an appropriate value for the idle_in_transaction_session_timeout parameter. You can run the following query to check the currently configured value. A value of 0 indicates that no timeout is configured.

SELECT name, setting
FROM pg_settings
WHERE name = 'idle_in_transaction_session_timeout';

Bottleneck # 3 – Excessive resource utilization

Bottleneck: Metrics like CPU usage, memory utilization, or I/O operations skyrocket, and you notice a corresponding decline in the throughput of the server.

Detection: Use tools like top and iostat to identify the reasons for high utilization. If the Postgres server has the highest footprint, analyze the currently active, resource-intensive queries via the pg_stat_activity view. For example, the following query outputs the top 10 most intensive active queries:

SELECT *
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start DESC
LIMIT 10;

Resolution:

• Optimize queries and indexes to reduce resource-intensive operations.
• Consider allocating additional resources to the system.

Bottleneck # 4 – Lock contentions

Bottleneck: Queries are taking longer to execute because different sessions are competing for the same locks.

Detection:

• Run the following query to see all the queries potentially waiting to acquire a lock:

  SELECT *
  FROM pg_stat_activity
  WHERE state = 'waiting' AND wait_event IS NOT NULL;

• Run the following query to retrieve more information about the locks being waited for:

  SELECT
    pg_stat_activity.pid,
    pg_stat_activity.datname,
    pg_stat_activity.usename,
    pg_stat_activity.query,
    pg_locks.locktype,
    pg_locks.database,
    pg_locks.relation,
    pg_locks.page,
    pg_locks.tuple,
    pg_locks.virtualxid,
    pg_locks.transactionid,
    pg_locks.classid,
    pg_locks.objid,
    pg_locks.objsubid,
    pg_locks.virtualtransaction,
    pg_locks.pid,
    pg_locks.mode,
    pg_locks.granted
  FROM
    pg_locks
  JOIN
    pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
  WHERE
    pg_locks.granted = 'f';

Resolution:

• Optimize transactions to minimize lock contention.
• Consider using more granular locks or adjusting isolation levels.

Bottleneck # 5 – Database fragmentation

Bottleneck: Database operations are taking longer to complete, potentially due to fragmentation.

Detection:

• Use the following command to keep tabs on overall table sizes:

  SELECT schemaname, relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size
  FROM pg_stat_all_tables
  ORDER BY pg_total_relation_size(relid) DESC;

• Use the following command to check for dead rows (with a high number indicating table bloat):

  SELECT schemaname, relname, n_dead_tup
  FROM pg_stat_all_tables
  WHERE n_dead_tup > 0
  ORDER BY n_dead_tup DESC;

• Use the following command to analyze all index sizes:

  SELECT schemaname, relname, indexrelname, pg_size_pretty(pg_total_relation_size(indexrelid)) AS total_size
  FROM pg_stat_user_indexes
  ORDER BY pg_total_relation_size(indexrelid) DESC;

Resolution:

• Implement routine maintenance tasks like VACUUM and ANALYZE.
• Use native Postgres extensions like pg_repack to remove bloat from tables and indexes.

Postgres replication issues

Here are a few common replication-related issues in Postgres and ways to fix them.

Issue # 1 – Replication lag

Problem: The standby node is lagging behind the primary due to slow replication.

Detection:

• Run the following command and examine the value of the replication_lag_bytes parameter. It provides an indication of how far behind a replication client is compared to the primary.

  SELECT
    application_name,
    client_addr,
    state,
    sync_state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    sync_priority,
    sync_state,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replication_lag_bytes
  FROM
    pg_stat_replication;

• Monitor the Postgres logs for any errors related to replication.

Resolution:

• Optimize network speed and latency between the primary and replicas.
• If applicable, increase the number of replication slots. A replication slot helps manage communication between a primary and replica by keeping track of the write-ahead log (WAL) on the primary server.

Issue # 2 – Replication connection failures

Problem: Replication connections drop or fail intermittently.

Detection: Inspect Postgres logs for error messages related to replication connection failures.

Resolution:

• Adjust network configurations to ensure uninterrupted communication.
• Review and adjust any firewall settings.
• If set too low, consider increasing timeout settings for replication connections.

Issue # 3 – Unexpected failover events

Problem: Unintended failover events are being triggered, impacting high availability of the system.

Detection:

• Check logs for failover-related messages .
• Run the following command on the primary node to check whether its state has changed:

  SELECT * FROM pg_is_in_recovery()

  If the output is true, the server is in standby mode.

Resolution:

• Adjust quorum and failover settings in the Postgres configuration file. Consider refining the failover trigger mechanism.

Postgres best practices

Now that we have explored several common issues that Postgres users deal with, let’s look at a set of best practices that can help avoid these issues in the first place.

Keep Postgres up to date

Formulate a mechanism to apply official patches and updates as soon as they are available. It helps to not only unlock new features and performance improvements but also fix bugs and security vulnerabilities.

Configure stringent security controls

Take the following steps to uphold high levels of security:

• Adhere to the principle of least privilege when assigning roles to users.
• Use advanced authentication mechanisms like SCRAM-SHA-256 (available since Postgres 10) to make unauthorized access harder.
• Encrypt sensitive data both when it’s stored and when it’s transferred.

Monitor regularly

Use advanced monitoring tools, such as Site24x7’s monitoring plugin for Postgres, to track the most important performance and health metrics, including long-running queries, CPU usage, idle user count, and cache usage ratio.

Optimize Postgres configurations

Optimize configuration parameters based on system resources and your operational needs. Focus on the most important parameters, including shared_buffers, work_mem, effective_cache_size, and max_connections.

Set up autovacuum

Configure and enable autovacuum to manage the removal of dead rows and optimize storage automatically. Make sure to adjust autovacuum settings to align with the specific needs of your database workload. You can monitor autovacuum activity using the following command:

SELECT * FROM pg_stat_progress_vacuum;

Leverage Postgres extensions

Install relevant Postgres extensions to enhance your workflows. Here are some extensions you can try out:

• PostGIS: Adds support for storing, indexing, and querying geospatial data.
• pg_stat_statements: Allows you to collect and track several statistics about SQL statements.
• pgcrypto: Enables cryptographic functions.
• pg_trgm: Makes it possible to use trigram matching, a text search technique for similarity comparison and pattern recognition within textual data.

Configure statement level timeout

Configure statement timeouts (statement_timeout) to prevent queries from running indefinitely. Use the following command to fetch the currently configured value:

SHOW statement_timeout;

A value of 0 indicates that no timeout is set. To set it, run this command:

SET statement_timeout = 'your_timeout_in_milliseconds';

Replace 'your_timeout_in_milliseconds' with a number based on the specific needs of your applications.

Write optimized queries

Follow these best practices to write more optimized queries:

• Avoid using SELECT * in queries, as it retrieves all columns, potentially impacting performance. Instead, explicitly specify the columns needed for your query.
• Identify and create indexes on columns that are frequently used in WHERE clauses and JOIN conditions.
• Use DISTINCT sparingly, as it can be resource-intensive. If distinct values are needed, consider alternative approaches like GROUP BY.
• Use LIMIT and OFFSET to fetch only the required rows, especially when dealing with large tables.
• Choose appropriate JOIN types (INNER, LEFT, RIGHT) based on table relationships and query requirements. Avoid unnecessary JOINs that don't contribute to the result set.
• For better performance, use EXISTS instead of COUNT when checking for row existence.
• Regularly use the EXPLAIN statement to analyze query execution plans. Identify potential bottlenecks and optimize accordingly.
• Consider caching the results of frequently executed (and resource-intensive) queries to reduce database load.
• Postgres window functions can be resource-intensive. Use them judiciously, especially when dealing with large data sets.

Conclusion

Postgres is an enterprise-grade database management system that handles both structured and JSON data efficiently. To maximize performance and business continuity, it’s important to take a systematic approach to troubleshooting and fixing Postgres issues, as outlined in this guide.