skip to content

PostgreSQL DBA Tooling

The tools DBAs run around a PostgreSQL server rather than inside it: the psql client, PgBouncer pooling, Patroni failover, backup managers and log or plan analysers. Interviewers probe tool choice.

on this pageshow

explore

questions

page 1 of 2

In PgBouncer, what do the session, transaction and statement values of pool_mode each do, and which one is the default?

level: juniorimportance: must knowfreq 76%

answer

  1. when the server goes back
  2. disconnect vs end of transaction vs query
  3. one mode refuses BEGIN blocks
  4. the default is the polite one

basics

~10 s

PgBouncer's pool_mode sets when a server connection returns to the pool: session (the default) on client disconnect, transaction at the end of each transaction, statement after each query, which refuses multi-statement transactions.

solid answer

~40 s

`pool_mode` decides when PgBouncer hands a PostgreSQL server connection back to its pool. In `session` mode, the default, a client keeps one server connection until it disconnects, so it behaves like a direct connection, and `server_reset_query` (`DISCARD ALL` by default) cleans the connection before the next client gets it. In `transaction` mode the server goes back when the transaction ends, so a few dozen server connections can serve many mostly idle clients; an autocommit statement is its own transaction. `statement` mode releases after every query and drops a connection that opens a transaction block. The mode is set globally in `[pgbouncer]`, per database in `[databases]` and per user in `[users]`, and `SHOW POOLS` shows the mode each pool uses.

code

ini · 10 lines
ini
[databases]
ticketing = host=10.0.0.5 dbname=ticketing pool_mode=transaction
ticketing_session = host=10.0.0.5 dbname=ticketing pool_mode=session

[users]
migrator = pool_mode=session

[pgbouncer]
listen_port = 6432
pool_mode = session

go deeper

for a junior

Name the three values and the release moment of each: client disconnect, end of transaction, end of query. Say clearly that session is the default.

for a middle

Explain where the mode can be set and which wins: [users] over [databases] over the global value. Mention that server_reset_query only runs in session mode.

for a senior

Show how you would split one database into a transaction-pooled name for the application and a session-pooled name for migrations and listeners, and how you verify it with SHOW POOLS.

for a principal

Frame the mode as a contract with every client team: transaction pooling buys connection density only if all code keeps its state inside single transactions.

## What `pool_mode` decides PgBouncer is a lightweight, single-process connection pooler that speaks the PostgreSQL wire protocol. Applications connect to it (port **6432** by default) exactly as they would connect to PostgreSQL. PgBouncer keeps its own **server connections** to the real database and lends them to **client connections**. One **pool** exists for each (database, user) pair. `pool_mode` answers a single question: **at what moment does a borrowed server connection go back to its pool**, ready for another client? Everything else about a mode follows from that moment. ## The three modes | Mode | Server connection released | What the client can rely on | Typical fit | |---|---|---|---| | `session` (**default**) | when the client disconnects | full session semantics, as on a direct connection | long-lived application servers, migrations, `psql`, `LISTEN` consumers | | `transaction` | when the transaction ends (`COMMIT`, `ROLLBACK`, or the end of an autocommit statement) | only state that lives inside one transaction | many short-lived or mostly idle clients | | `statement` | when each query finishes | single autocommit statements; transaction blocks are refused | rare autocommit-only workloads | - **Session pooling** is the most polite mode. The gain is **reuse**: when a client disconnects, a warm server connection stays behind, so the next client skips PostgreSQL's per-connection process start-up and authentication. Before reuse PgBouncer runs `server_reset_query`, which defaults to `DISCARD ALL`, so the next client does not inherit temporary tables, prepared statements, `LISTEN` registrations or `SET` values. - **Transaction pooling** is **multiplexing**. A client holds a server connection only while it is inside a transaction and holds nothing between transactions. That is how a ticketing site's fleet of short-lived serverless functions can share a few dozen server connections during an on-sale spike. The price is that the client's next transaction may land on a different PostgreSQL backend, so session-level state does not follow it. `server_reset_query` is **not** run in this mode. - **Statement pooling** is the most aggressive mode. The server goes back after every query. If a query leaves the server inside a transaction block, PgBouncer drops the connection with the message `transaction blocks not allowed in statement pooling mode`. ## Where the mode is set PgBouncer resolves the mode for each pool from the most specific setting to the least: 1. `pool_mode` on the user's line in the `[users]` section; 2. `pool_mode` in the database's entry in the `[databases]` section; 3. the global `pool_mode` in `[pgbouncer]`, which defaults to `session`. ```ini [databases] ticketing = host=10.0.0.5 dbname=ticketing pool_mode=transaction ticketing_session = host=10.0.0.5 dbname=ticketing pool_mode=session [users] migrator = pool_mode=session [pgbouncer] listen_port = 6432 pool_mode = session ``` Exposing the same PostgreSQL database under two PgBouncer database names is the usual way to give the function fleet transaction pooling while migrations, `psql` sessions and notification listeners keep session pooling. A few more details are worth knowing: - **Replication connections** that pass through PgBouncer are always session-pooled, whatever the pool's mode says. - `pool_mode` is **not** among the settings that need a process restart, so a `RELOAD` on the admin console (or `SIGHUP`) picks it up. - On the admin console, the virtual database named `pgbouncer`, `SHOW POOLS` has a `pool_mode` column that shows what each pool really uses. That is the fastest way to catch a per-user override nobody remembered. ## What each mode means for the application Session mode needs nothing from the application. Transaction mode needs the application to keep every piece of session state inside one transaction. These are the features that do not survive it: - session-level `SET` of parameters PgBouncer does not track; - SQL-level `PREPARE` / `EXECUTE`; - `LISTEN`; - session-level advisory locks; - temporary tables and `WITH HOLD` cursors kept across transactions. Statement mode additionally forbids `BEGIN ... COMMIT` blocks. ## How to choose 1. Start from what each client does with its session, not from how many clients there are. 2. If clients are short-lived and keep their state inside single transactions, use `transaction` for them; this is where PgBouncer's big multiplexing wins come from. 3. Keep `session` for anything that needs session features, ideally on a separate PgBouncer database name rather than flipping the whole instance. 4. Treat `statement` as a special case for autocommit-only traffic. Why pooling helps at all, and how big a pool should be in general, are pooling-theory questions. The PgBouncer-specific answer here is the release moment, the `session` default, and the user-over-database-over-global precedence.

  • Why might you list the same PostgreSQL database twice in PgBouncer's [databases] section?
    So each name can carry its own `pool_mode`. The application connects to a transaction-pooled name for multiplexing, while migrations, `psql` and `LISTEN` consumers connect to a session-pooled name and keep full session semantics. A per-user `pool_mode` in `[users]` does the same for one role.
  • What does server_reset_query_always change in PgBouncer, and why is it a workaround rather than a fix?
    It runs `server_reset_query` (`DISCARD ALL` by default) in every pooling mode, not only in session mode. Under transaction pooling, clients then lose all session state after every transaction. The breakage becomes deterministic instead of random state leaking between clients, but session features still do not work.

A car-share desk: session mode lends you a car for your whole stay, transaction mode for each trip, and statement mode for each single street, which is why it refuses multi-stop journeys.

  • Transaction pooling is PgBouncer's default, since that is why people install it.
  • Session mode gives no benefit over connecting straight to PostgreSQL.
  • Statement mode is the safest mode because it releases servers fastest.
  • In transaction mode PgBouncer runs DISCARD ALL after every transaction.
  • pool_mode can only be set once, globally, in the [pgbouncer] section.
open as a page

In psql, which backslash meta-commands list and describe a database's objects, and what do the + and S modifiers and \x change?

level: juniorimportance: must knowfreq 72%

basics

~20 s

The \d family: \dt lists tables, \d name describes one relation, \l databases, \dn schemas, \du roles, \df functions. A + suffix adds detail such as size and comments, S includes system objects, and \x prints rows as field/value lines.

open as a page

In Barman, how do backup_method = rsync and backup_method = postgres differ, and how would you choose between them for a server?

level: middleimportance: must knowfreq 52%

basics

~20 s

backup_method = rsync, still the default, starts a backup through PostgreSQL's low-level API and copies files with rsync over SSH, offering parallel_jobs and hard-link incrementals. backup_method = postgres runs pg_basebackup over a replication connection, needs no SSH, and supports PostgreSQL 17+ block-level incrementals.

open as a page

In Patroni, how do ttl, loop_wait and retry_timeout bound the leader key, and what does the primary do when renewal fails?

level: middleimportance: must knowfreq 58%

basics

~20 s

Patroni's primary holds a leader key in the DCS that expires after ttl (30 s) and renews it every loop_wait (10 s), retrying for retry_timeout (10 s). If renewal fails it demotes PostgreSQL, timed to finish before the key expires.

open as a page

With Patroni's patronictl, when do you run switchover instead of failover, and what does each command require and risk?

level: middleimportance: must knowfreq 55%

basics

~20 s

patronictl switchover is for a healthy cluster: it cleanly demotes the current leader and promotes a chosen or best replica. patronictl failover is for an unhealthy one: it needs --candidate, works without a leader, and can lose data.

open as a page

Which EXPLAIN options should you run before pasting a PostgreSQL plan into PEV2, and why is FORMAT JSON a better input than text?

level: middleimportance: must knowfreq 36%

basics

~20 s

Run EXPLAIN (ANALYZE, VERBOSE, BUFFERS, FORMAT JSON), and add SETTINGS when planner settings differ. PEV2 needs ANALYZE for real times and rows, and JSON gives it every field as a named key instead of text it must reconstruct.

open as a page

With pgBackRest, how do full, diff and incr backups depend on each other, and how do repo1-retention-full and repo1-retention-diff decide what expire removes?

level: middleimportance: must knowfreq 58%

basics

~20 s

A pgBackRest full stands alone, a diff holds files changed since the last full, and an incr holds files changed since the last backup of any type. Expire removes the oldest fulls beyond repo1-retention-full, along with their diffs and incrs.

open as a page

Which PostgreSQL logging settings must be in place before pgBadger can produce a useful report, and which one should you leave off?

level: middleimportance: must knowfreq 48%

basics

~10 s

pgBadger needs a log_line_prefix with a time and process escape (or csvlog/jsonlog), log_min_duration_statement set, the checkpoint, connection, lock-wait, temp-file and autovacuum log settings on, English lc_messages, and log_statement left off.

open as a page

In PgBouncer, how do default_pool_size and max_client_conn differ, and how do you read SHOW POOLS when clients start queueing?

level: middleimportance: must knowfreq 61%

basics

~10 s

max_client_conn (default 100) caps client connections to PgBouncer; default_pool_size (default 20) caps server connections per database and user pair. In SHOW POOLS, rising cl_waiting and maxwait with no sv_idle mean every server is busy.

open as a page

When psql runs a migration file with -f and a statement in the middle fails, what happens by default, and how do ON_ERROR_STOP and --single-transaction change it?

level: middleimportance: must knowfreq 60%

basics

~20 s

By default psql prints the error, runs the rest of the file and exits 0. ON_ERROR_STOP=1 stops at the first error with exit status 3; -1 wraps the file in BEGIN/COMMIT, sending ROLLBACK instead when ON_ERROR_STOP catches a failure.

open as a page

In temBoard, what do the central UI, its repository and the per-instance agent each do, and why must the agent run on the PostgreSQL host?

level: middleimportance: must knowfreq 26%

basics

~20 s

The UI serves the console and keeps users, inventory and metric history in a PostgreSQL repository; each agent runs beside its instance, connects as superuser over the Unix socket, buffers probe results and answers the UI's signed HTTPS calls.

open as a page

With WAL-G on PostgreSQL, what do backup-push and wal-push each upload, how is each triggered, and why do you need both?

level: middleimportance: must knowfreq 55%

basics

~20 s

backup-push uploads a base backup of the data directory on a schedule; wal-push uploads each finished WAL segment, called by archive_command. A base backup is only consistent with the WAL written during it, and PITR needs the WAL after it.

open as a page

Using WAL-G, how do you restore a containerised PostgreSQL 18 from object storage with backup-fetch LATEST and wal-fetch, and what can silently go wrong?

level: middleimportance: must knowfreq 50%

basics

~10 s

Run wal-g backup-fetch into an empty data directory with LATEST, create recovery.signal, set restore_command = 'wal-g wal-fetch %f %p' and start PostgreSQL. A storage error from wal-fetch can end recovery early, so wrap it.

open as a page

With Barman, how do you restore the timetable server onto a new host to 09:39, just before a bad import ran at 09:40, and what does barman restore set up?

level: seniorimportance: must knowfreq 48%

basics

~20 s

Run barman restore with --remote-ssh-command to the new host, a backup that ended before 09:39 (or auto), --target-time with an explicit zone and usually --get-wal. Barman copies the files, writes restore_command and the target, creates recovery.signal and disables archive_command; you start PostgreSQL.

open as a page

With pgBackRest, how does restore --delta shorten rebuilding a PostgreSQL data directory that already holds most of its files, and when should you use it?

level: seniorimportance: must knowfreq 46%

basics

~20 s

pgBackRest restore --delta keeps existing files whose SHA-1 matches the backup manifest, restores only mismatched or missing files, and deletes files the backup lacks. Rebuilding a mostly intact data directory then copies a fraction of the data.

open as a page

When does temBoard beat a general metrics-and-dashboards stack, and when does it lose, for a two-person DBA team running 40 PostgreSQL instances for a public-library network?

level: seniorimportance: must knowfreq 28%

basics

~20 s

temBoard wins when a small team wants a ready-made PostgreSQL console where it can also act: terminate sessions, change settings, schedule maintenance. A metrics stack wins on custom alert rules, routing, long-term analysis and anything beyond PostgreSQL.

open as a page

What is Barman in a PostgreSQL backup setup, and what does running barman check against a newly configured server tell you?

level: juniorimportance: should knowfreq 45%

basics

~20 s

Barman is a backup manager whose one host keeps physical base backups and archived WAL for many PostgreSQL servers. barman check SERVER tests a server's connections, WAL shipping, directories and settings, printing OK or FAILED per line and exiting non-zero on failure.

open as a page

With pgBackRest, what is a stanza, and what steps take a new PostgreSQL cluster to its first backup with WAL archiving proven to work?

level: juniorimportance: should knowfreq 42%

basics

~20 s

A stanza is pgBackRest's named configuration for one PostgreSQL cluster: its data path, repositories and options. Set archive_command to pgbackrest archive-push, run stanza-create, prove archiving with check, then take the first backup, which is automatically a full.

open as a page

What does pgBadger do for a PostgreSQL server, what input does it read, and what does its report show?

level: juniorimportance: should knowfreq 32%

basics

~20 s

pgBadger is a standalone Perl script that parses PostgreSQL server log files offline and builds a self-contained HTML report of slowest and most frequent queries, errors, lock waits, temporary files, checkpoints, autovacuum activity, connections and sessions.

open as a page

What is temBoard, and which PostgreSQL administration jobs does its web console let a DBA team do from one place?

level: juniorimportance: should knowfreq 30%

basics

~10 s

temBoard is Dalibo's open-source, self-hosted web console for a fleet of PostgreSQL instances: a central UI plus one agent per instance, giving dashboards, session management, configuration editing, scheduled maintenance, statement history and threshold alerting.

open as a page

With WAL-G backing up a containerised PostgreSQL, how do you point it at an S3 bucket and choose compression and encryption?

level: juniorimportance: should knowfreq 32%

basics

~10 s

Set WALG_S3_PREFIX (s3://bucket/path) and AWS credentials, choose WALG_COMPRESSION_METHOD (lz4 by default; zstd, lzma, brotli or none) and encrypt client-side with WALG_LIBSODIUM_KEY or a PGP key, all where the PostgreSQL server process can read them.

open as a page

In Barman, how do retention_policy = REDUNDANCY 3 and RECOVERY WINDOW OF 7 DAYS differ, and what actually enforces them?

level: middleimportance: should knowfreq 38%

basics

~20 s

REDUNDANCY 3 keeps the three newest full backups. RECOVERY WINDOW OF 7 DAYS keeps the backups and WAL needed to recover to any moment of the last seven days, including the newest backup before the window. barman cron enforces both; unset means nothing is deleted.

open as a page

How does a load balancer such as HAProxy use Patroni's REST API health endpoints to send writes to the primary and reads to replicas?

level: middleimportance: should knowfreq 46%

basics

~20 s

HAProxy health-checks each node's Patroni REST port instead of PostgreSQL: GET /primary returns 200 only on the leader, GET /replica only on a running replica without noloadbalance, so each pool sends traffic only to nodes answering 200.

open as a page

In PEV2, what do the buffers and IO views reveal about a PostgreSQL plan node, and why can the IO view be unavailable?

level: middleimportance: should knowfreq 22%

basics

~20 s

PEV2 subtracts children's counts to show each node's own shared, temp and local blocks hit, read, dirtied and written. Its IO view needs per-node I/O times, which PostgreSQL records only with track_io_timing on, and it is off by default.

open as a page

With pgBackRest, what do the check and verify commands each prove about a stanza, and when would you run each?

level: middleimportance: should knowfreq 34%

basics

~20 s

pgBackRest check proves the live path works: it forces a WAL switch and waits for that segment to reach the repositories. verify proves stored data is intact: it rereads backups and archived WAL and compares sizes and checksums with the manifests.

open as a page

How does pgBadger's incremental mode turn a nightly cron run into daily and weekly reports without counting log lines twice?

level: middleimportance: should knowfreq 24%

basics

~20 s

With -I and a mandatory -O output directory, pgBadger stores each run's statistics as dated binary files, rebuilds that day's and week's HTML from them, and records the last parsed line in a LAST_PARSED file so later runs skip what was already counted.

open as a page

In psql, how does the \copy meta-command differ from the server-side COPY command, and when do you need \copy instead?

level: middleimportance: should knowfreq 56%

basics

~20 s

psql's \copy runs COPY ... FROM STDIN or TO STDOUT, and psql reads or writes the file on the client host with the client user's permissions. Server-side COPY uses the server's filesystem and needs superuser or pg_read_server_files / pg_write_server_files.

open as a page

During an incident, how do psql's \watch, \timing and \conninfo help you, and what exactly does each one show or measure?

level: middleimportance: should knowfreq 42%

basics

~20 s

\watch reruns the current or last query every 2 seconds until cancelled or a count or row floor stops it. \timing prints each statement's elapsed time measured by psql, network included. \conninfo shows the database, host, user and backend you are on.

open as a page

In temBoard's Activity view, how do you find the PostgreSQL session that is blocking others and end it, and what runs when you terminate it?

level: middleimportance: should knowfreq 22%

basics

~20 s

Open the Waiting tab to see the ungranted locks, then the Blocking tab for sessions holding what they want, usually an old idle-in-transaction one; Terminate makes the agent run pg_terminate_backend(pid), ending that session and rolling back its transaction.

open as a page

In WAL-G for PostgreSQL, what does wal-g delete retain FULL 7 --confirm keep and delete, and when can delete retain remove the wrong backups?

level: middleimportance: should knowfreq 42%

basics

~20 s

It keeps the seven newest full backups with their deltas and deletes older backups plus the WAL before the oldest one kept. Without --confirm it is a dry run; after pg_upgrade name-based ordering can delete the new backups.

open as a page

showing 1–30 of 53