Postgres topology discovery

The inventory is wrong

Every organisation running more than a handful of Postgres clusters has a document describing them, and every one of those documents is out of date. A replica was rebuilt on a different host. A failover happened at three in the morning and the roles are now the other way round. Someone added a pooler in front of one cluster and not the others. None of that updates a spreadsheet.

The alternative is to ask the server. Almost everything worth knowing about a cluster’s shape can be established from a read-only connection, and the value of doing it that way is not that it is faster. It is that the answer is true at the moment you asked, which a declared inventory never is.

What follows is the set of questions, and the query that answers each.

Am I connected to the writer

SELECT pg_is_in_recovery()            AS is_standby,
       current_setting('server_version') AS version,
       current_setting('server_version_num')::int AS version_num,
       pg_postmaster_start_time()     AS started_at;

pg_is_in_recovery() is the only reliable answer, and it is worth trusting over the endpoint name you connected through. A connection string with the word “writer” in it is a label on a load balancer, and load balancers can be stale in exactly the situation where you need this to be right. Managed platforms make this sharper rather than softer, because the endpoint is the platform’s abstraction rather than a process you can inspect; DBExplore for Amazon Aurora covers what that means for one of them.

server_version_num is the form to compare against, because it is an integer and string comparison on version numbers is how tooling ends up treating 18 as older than 9.

Which nodes are siblings

Two servers either came from the same original cluster or they did not, and this is not a matter of naming convention:

SELECT system_identifier FROM pg_control_system();

Every node cloned from the same origin shares that identifier, and a cluster built independently has a different one. It is the only way to prove that the box somebody called pg-standby-3 is genuinely a replica of the primary you think it is, rather than a replica of something else entirely, or a restored copy that has been diverging for a month.

The timeline distinguishes history from identity:

SELECT timeline_id, redo_lsn, checkpoint_lsn FROM pg_control_checkpoint();

Timelines fork at promotion. Two nodes with the same system identifier on different timelines came from the same cluster and are no longer the same cluster, which is the shape of a split brain and is worth noticing before something writes to both.

What is connected to me, and what am I connected to

From the primary, the replicas announce themselves:

SELECT application_name, client_addr, client_hostname,
       state, sync_state, backend_start, reply_time
FROM pg_stat_replication
ORDER BY backend_start;

From a standby, the upstream is visible the other way round:

SELECT status, sender_host, sender_port, slot_name,
       conninfo, last_msg_receipt_time, latest_end_lsn
FROM pg_stat_wal_receiver;

Treat conninfo as sensitive. It is the connection string this node uses to reach its upstream, and depending on how the cluster was built it may contain credentials, so it belongs in a diagnosis and not in a ticket.

The gap between those two views is informative. A slot in pg_replication_slots with no matching row in pg_stat_replication is a consumer that is not a streaming replica: a backup tool, a change-data-capture pipeline, or a replica that has been gone long enough to matter. Working out which is the subject of replication lag and slot health.

What manages this cluster

Postgres has no concept of an orchestrator, so this is inferred from settings rather than read directly. On a standby, the recovery settings are ordinary parameters and can be read like any other:

SELECT name, setting, source
FROM pg_settings
WHERE name IN ('cluster_name',
               'primary_conninfo', 'primary_slot_name',
               'restore_command', 'recovery_min_apply_delay',
               'hot_standby_feedback',
               'archive_mode', 'archive_command',
               'wal_level', 'synchronous_standby_names')
ORDER BY name;

cluster_name is often set by whatever provisioned the node and is the cheapest available hint about what did. primary_slot_name tells you whether this standby has a slot reserved for it or is streaming without one, which decides whether falling behind means broken replication or a full disk on the primary. synchronous_standby_names on the primary tells you which replicas commits are waiting for, which is the difference between a slow standby being an annoyance and being an outage.

Again, primary_conninfo can carry a password. Redact before sharing.

What is loaded, and what platform this is

SELECT current_setting('shared_preload_libraries') AS preloaded;

SELECT extname, extversion, extnamespace::regnamespace AS schema
FROM pg_extension
ORDER BY extname;

Preloaded libraries are the ones that had to be there at startup, which is the set that decides what monitoring is even possible on this node. If pg_stat_statements is absent from that list, no amount of querying will produce statement history, and adding it needs a restart.

The platform itself gives itself away through namespaced settings. Extensions and managed services register parameters with a dotted prefix, and core Postgres does not:

SELECT name, setting, source
FROM pg_settings
WHERE name LIKE '%.%'
ORDER BY name;

On a self-hosted server that returns the settings of whatever extensions are loaded. On a managed service it also returns the vendor’s own parameters, and the prefixes name the vendor. It is an inference rather than a declaration, and it is a reliable one, because a platform that customises the server has to expose those knobs somewhere.

Is anything being archived

SELECT archived_count, last_archived_wal, last_archived_time,
       failed_count,   last_failed_wal,   last_failed_time,
       stats_reset
FROM pg_stat_archiver;

archive_mode being on says somebody intended continuous archiving. pg_stat_archiver says whether it is working. A last_failed_time more recent than last_archived_time means archiving is currently broken, which means the recovery window is not what the runbook claims, and it is the sort of thing that is discovered during a restore if it is not discovered by a query. What the view cannot tell you is which mechanism is doing the archiving, or whether the archiver is stuck inside it, and from PostgreSQL 15 both of those are answerable elsewhere: see the archiver says what it is waiting on.

Is there a pooler in the way

This is the one question the server genuinely cannot answer, because a pooler is a client as far as Postgres is concerned. What you get are signals:

SELECT client_addr,
       application_name,
       count(*)                       AS backends,
       count(*) FILTER (WHERE state = 'idle') AS idle,
       min(backend_start)             AS oldest_backend
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY client_addr, application_name
ORDER BY backends DESC;

A single client_addr holding many long-lived backends whose application_name varies between statements is the shape of a transaction-mode pooler. Many short-lived backends from many addresses is the shape of an application connecting directly. Confirming it means reaching the pooler’s own admin interface, which is covered in connection pooling and PgBouncer.

Discovery is a verb

The reason to run all of this rather than to record it once is that every fact above has a half-life. Roles swap at failover. Slots appear when a pipeline is built and are forgotten when it is decommissioned. A minor upgrade changes the version. An extension is enabled in staging and promoted to production with the next release.

An inventory that is refreshed on a schedule is a monitoring system’s foundation, and one that is filled in by hand at onboarding is a source of confident, wrong answers. The practical rule is that anything you could have asked the server, you should ask the server, every time.