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.
pg_lsclusters
systemctl status postgresql --no-pager
sudo journalctl -u postgresql -n 60 --no-pagerThe 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.
grep -n "include" /etc/postgresql/15/main/postgresql.conf
ls -l /etc/postgresql/15/main/conf.dinvalid 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.
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.
tail -n 40 /var/log/api.log
netstat -ant | head -n 20These 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
| Observation | Next check | How to interpret it |
|---|---|---|
| Cluster down after deployment | Startup logs and new includes | Start with the reported directive rather than rebuilding the database. |
| Cluster up with many client connections | Pools, pg_stat_activity and API timeouts | Inspect how the application consumes and retains connections. |
| Database recovers but API pressure remains | Retry rate and resource trends | The 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
- Check the intended version, cluster and port.
- Verify the original application data remains accessible.
- Repeat the original API request through its normal route.
- 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?
configuration file: conf.d/95-tuning.conf
parameter: shared_buffers
value: 512XB
cluster status: downExplore 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.
max_connections=100
current client connections=98
API worker count=16
connection wait risingWhy 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.
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.
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.
No comments yet. Share your first impression or ask a question about this topic.