Lesson 7 of 10Diagnostics

12 min · theory and practice · Basics

PostgreSQL: startup, configuration and connections

Distinguish a cluster that cannot start from a running database overwhelmed by clients.

Choose between startup logs, configuration inspection and client-connection analysis.

An API reporting “database unavailable” describes the client’s perspective, not PostgreSQL’s state. The cluster may fail on a bad directive, listen on another address or exhaust available connections. Establish which situation exists before changing settings.

Establish the cluster state

On Debian, pg_lsclusters lists individual clusters with version, name, port and status. The umbrella postgresql.service does not always describe each cluster, so compare it with logs and listening ports. In the training cascade, also use its supplied diagnostic commands.

Commands
pg_lsclusters
systemctl status postgresql --no-pager
sudo journalctl -u postgresql -n 60 --no-pager

The first PostgreSQL case uses a real cluster; the cascade models some metrics. SQL examples below apply to an accessible real database.

For startup failure, read the first cause

After a deployment, the error often identifies a file, line and parameter. Inspect includes and conf.d ordering, then compare the added setting with its valid format. Do not delete the data directory or initialize a new database because of a configuration syntax error.

Commands
grep -n "include" /etc/postgresql/15/main/postgresql.conf
ls -l /etc/postgresql/15/main/conf.d
Example output
invalid value for parameter "shared_buffers": "512XB"

This example identifies a unit-format error rather than data corruption. Case variants change the file and directive; read the actual message.

For a running database, inspect clients

In real PostgreSQL, pg_stat_activity shows server processes and their states. idle means waiting for another client command; idle in transaction also means a transaction remains open. Relate a high connection count to the application pool and workload.

Commands
sudo -u postgres psql -c "SELECT state, count(*) FROM pg_stat_activity GROUP BY state;"
sudo -u postgres psql -c "SHOW max_connections;"

This view requires database access and is unavailable when startup fails. Do not begin diagnosing a stopped cluster with a SQL query.

Inspect client settings and the waiting budget

An excessive connect timeout retains API requests and resources. Unbounded retries add load. Increasing max_connections does not fix those causes and may increase memory use; first inspect client timeouts, pool sizes and retries.

Commands
tail -n 40 /var/log/api.log
netstat -ant | head -n 20

These commands are useful in the cascade case. For real PostgreSQL, supplement them with server metrics and pg_stat_activity.

Verify data and the client path

After a startup repair, confirm the original database remains and the API performs the required query. After limiting load, observe connections, latency and errors over time. A running process without recovered requests does not close the incident.

From symptom to check

ObservationNext checkHow to interpret it
Cluster down after deploymentStartup logs and new includesStart with the reported directive rather than rebuilding the database.
Cluster up with many client connectionsPools, pg_stat_activity and API timeoutsInspect how the application consumes and retains connections.
Database recovers but API pressure remainsRetry rate and resource trendsThe first repair stage may have exposed a second cascade symptom.

Common mistakes

  • Deleting data or running initdb because a setting is invalid.
  • Treating the umbrella unit as proof the intended cluster works.
  • Raising max_connections without inspecting load and memory.

How to prove recovery

  1. Check the intended version, cluster and port.
  2. Verify the original application data remains accessible.
  3. Repeat the original API request through its normal route.
  4. Observe connections and errors after the first successful response.

MINI INCIDENT

What must be preserved during a configuration failure?

After deployment PostgreSQL fails with invalid value in a new conf.d file. A colleague proposes recreating the cluster. What evidence supports a narrower repair?

Evidence to investigate
configuration file: conf.d/95-tuning.conf
parameter: shared_buffers
value: 512XB
cluster status: down
Explore the diagnosis

The error identifies a parameter format rather than data corruption. Inspect the include, valid units and deployment change. Repair the confirmed directive while preserving other settings and data, then verify startup and the API request.

WORKSHOP

Workshop: explain the evidence and choose an action

The database accepts connections while the API exhausts its wait budget. Relate connection capacity to client behavior.

Sample evidence
max_connections=100
current client connections=98
API worker count=16
connection wait rising
  1. Why does a running PostgreSQL not establish API health?

    Compare your reasoning

    The server can run while a useful client lacks a slot or wait budget. Inspect connection states, owners and pool budgets rather than only service status.

  2. Which calculation helps assess the hypothesis?

    Compare your reasoning

    Estimate the combined pools across processes and replicas, leaving operational headroom. Sixteen processes with ten connections each already exceed 100 before other clients are considered.

  3. What should be observed after changing the client budget?

    Compare your reasoning

    Observe spare slots, connection states, API latency and whether growth stops. Raising the server limit without assessing memory or retention causes can simply move the failure boundary.

CHECK YOUR UNDERSTANDING

The API reports connection timeout. Should you immediately raise max_connections?

Show explanation

No. Check cluster state, address and reachability, then client count and behavior. A timeout does not prove the server limit was reached.

Documentation

Guest progress is stored separately in this browser.

TAKE IT TO THE TERMINAL

Practice on an incident

First restore PostgreSQL startup after a configuration change. Then investigate the cascade where application pressure on the database must be bounded.

DID IT MAKE SENSE?

Was this article helpful?

Rate the article or tell us what needed more explanation. Your feedback helps improve the lessons.

Helpful 0 Not helpful 0

Sign in to rate this article and leave a comment.

Discussion

0

Join the discussion →

No comments yet. Share your first impression or ask a question about this topic.