What Runs When You Connect to PostgreSQL?

A PostgreSQL connection has a server process behind it. That process belongs to a running cluster, shares work with background processes, and has a PID we can inspect from SQL or Linux.

My first four PostgreSQL lessons connected those pieces. These are selected lab inspection steps; the results described are expected behavior, not a transcript of my original output.

Confirm which cluster answered

In PostgreSQL, a database cluster means a collection of databases managed by one server instance. It does not necessarily mean several machines. initdb creates its initial files; pg_ctl starts the server using that data directory. Creating the directory and running the server are separate steps. The initdb documentation describes what initialization creates.

The lesson used its own directory and port so experiments had a clear target. To inspect an already-running disposable lab with the course's connection settings:

psql -h /tmp -p 5440 -U postgres -d lab

Here /tmp names a Unix socket directory. 5440 selects the lab's socket, postgres is the database role, and lab is the database. Inside psql, check which server answered:

select
  current_database() as database,
  current_setting('data_directory') as data_directory,
  current_setting('cluster_name') as cluster_name,
  pg_backend_pid() as backend_pid;

These examples assume the lab's superuser connection. The directory is the server's path, not the client's working directory. The cluster name is a configured label; the path and connection settings help confirm which instance is actually in use.

Put the backend PID in the prompt

The PID belongs to the backend serving this connection. It is not psql's own process ID. Putting it in the prompt makes two terminals easier to tell apart:

\set PROMPT1 '%n@%/ pid=%p %R%# '
\timing on
\x auto

The prompt shows the role, database, and backend PID. \timing reports elapsed time measured by psql; it is not a pure server execution measurement. \x auto switches wide results to an expanded display. These are client commands, not SQL sent to the server. psql's reference documents the prompt and display options.

Available extensions and installed extensions

Extensions add another useful distinction: software available on the server is not always enabled in the current database. This query checks both:

select
  name,
  default_version,
  installed_version
from
  pg_available_extensions
where
  name in ('pageinspect', 'pg_stat_statements')
order by
  name;

A listed extension with a null installed_version is available but not installed in this database. No row means it is not listed as available. In the completed lab, lesson 3 enabled both: pageinspect provides page inspection functions, while pg_stat_statements collects query statistics. The latter also needs shared_preload_libraries configured before server startup. CREATE EXTENSION alone does not meet that requirement. pg_stat_statements setup explains why.

Match SQL activity to Linux processes

To see the processes doing the work, inspect the server's activity view:

select
  pid,
  backend_type,
  state,
  wait_event_type,
  wait_event
from
  pg_stat_activity
order by
  backend_type,
  pid;

Find the PID from the prompt. Its row should say client backend and active while this query runs. Other rows describe shared work, such as the checkpointer and WAL writer. An Activity wait often means a background process is waiting for work. It is not, by itself, evidence of a blocked query. Process types depend on the configuration and current work. pg_stat_activity defines those fields.

The supervisor, often called the postmaster, starts the backend. After connection setup, psql talks directly to that backend:

sequenceDiagram
  accTitle: PostgreSQL connection handoff
  accDescr: psql connects to the supervisor, which starts a backend process. psql then sends SQL directly to the backend and receives results.
  participant P as psql
  participant S as Supervisor
  participant B as Backend
  P->>S: Connect
  S->>B: Start process
  P->>B: SQL
  B-->>P: Results

PostgreSQL's architecture overview describes that handoff.

Keep psql open. In a second terminal on the server host, set PGLAB to the existing lab's parent directory, then compare Linux's process list. Its primary directory must be the data_directory checked above:

ps -o pid,ppid,etime,cmd \
  --ppid "$(head -n 1 "$PGLAB/primary/postmaster.pid")"

The PID file's first line identifies the supervisor. --ppid lists its children, so the backend PID should appear again. This shell command must run on the server host with access to that file; a remote psql client cannot inspect the server's processes this way.

Matching that PID joins three views of the same connection: the psql prompt, a SQL activity row, and an operating-system process. That gives a concrete starting point for investigating a slow or waiting session. These inspection commands create no fixture; \q closes psql and its connection while leaving the shared server running.

Based on completed PostgreSQL Systems (postgres-legacy) lessons 1–4: the disposable cluster, psql habits, inspection extensions, and the process model.