PostgreSQL Under the Hood: Server, Client Tools, and the Catalog That Ties Them Together

Most developers interact with PostgreSQL through an ORM or a connection string and never think about what’s actually running behind the scenes. “PostgreSQL” feels like one thing — you install it, you connect to it, you query it. But PostgreSQL is actually a system of cooperating parts: a server process, a set of standalone client tools, a wire protocol that ties them together, and an internal catalog that describes everything in your database. Each part has a distinct job.

Understanding these parts isn’t just an academic exercise. It directly explains how database migration tools like YugabyteDB Voyager work, why they depend on specific tools at specific versions, and why the installation process looks the way it does.

If you’ve ever wondered why migrating a database requires pg_dump and psql but not the PostgreSQL server itself — or why the installer checks for Java and Perl alongside PostgreSQL client tools — this post will connect the dots.

Part 1: PostgreSQL’s Architecture — The Foundation

It’s Not One Thing — It’s a Client-Server System

A common misconception is that “PostgreSQL” is a single monolithic program. It’s not. PostgreSQL is a client-server system with two completely separate halves that communicate over a network protocol.

The Server: Where Data Lives

The PostgreSQL server is the postgres process.
It's a multi-process daemon that:

Listens on a TCP port (default 5432) for incoming connections
Stores data on disk in a structured data directory (PGDATA)
Executes SQL queries by parsing, planning, and running them against its storage engine
Manages transactions with full ACID semantics using MVCC (Multi-Version Concurrency Control)
Handles replication — both physical (byte-level WAL shipping) and logical (row-level change streaming)
Maintains the system catalog — a set of internal tables (pg_class, pg_type, pg_proc, pg_constraint, pg_depend, and dozens more) that describe every object in every database

When people say “I’m running PostgreSQL 17,” they almost always mean the server.

The System Catalog: PostgreSQL’s Self-Description

Here’s something that trips people up at first: PostgreSQL uses its own tables to keep track of all the tables (and everything else) in your database. These are called the system catalog, and they’re just regular tables you can query.

Say you create a table called users. PostgreSQL doesn't just store the data

it also writes a row into pg_class saying "there's a table called users,"
rows into pg_attribute describing each column,
rows into pg_constraint for the primary key, and
rows into pg_depend recording that the primary key index depends on the table.
It's turtles all the way down.pg_class — every table, index, sequence, view, and materialized view

The catalog is how PostgreSQL knows what exists in a database. It’s also how external tools like pg_dump discover what to export — by querying these same catalog tables.

The Wire Protocol: How Clients Talk to Servers

PostgreSQL defines a binary wire protocol (sometimes called the “frontend/backend protocol”) that all client tools use to communicate with the server. This protocol handles:

Authentication (password, SCRAM-SHA-256, certificate, etc.)
Query submission and result retrieval
The COPY sub-protocol for high-speed bulk data transfer
Notification channels (LISTEN/NOTIFY)
Replication streaming (logical and physical)

Any program that speaks this protocol can connect to PostgreSQL. The official client tools (psql, pg_dump, pg_restore) use it through a C library called libpq. Third-party drivers like Go's pgx, Java's JDBC, Python's psycopg2, and dozens of others implement it independently.

This is critical for distributed databases like YugabyteDB: because YugabyteDB’s YSQL layer implements the same wire protocol, all PostgreSQL client tools work against YugabyteDB without modification.

The Client Tools: Standalone Utilities

PostgreSQL ships three primary command-line client tools. They’re installed via a separate, smaller package (postgresql-client-17 on Ubuntu, postgresql17 on CentOS) and do not require a local database server. You can install them on a machine with no database and use them to interact with servers across the network.

Part 2: Deep Dive into Each Tool

psql — The Interactive SQL Terminal

psql is PostgreSQL's interactive command-line client. You type SQL statements, and it sends them to the server and displays the results:

mydb=# SELECT schemaname, tablename, n_live_tup 
       FROM pg_stat_user_tables 
       ORDER BY n_live_tup DESC LIMIT 5;

 schemaname |   tablename    | n_live_tup
------------+----------------+------------
 public     | events         |   48210394
 public     | users          |    2847201
 public     | transactions   |    1203847
 public     | audit_log      |     584920
 public     | sessions       |     128401
(5 rows)

Under the hood, psql:

Establishes a TCP connection to the server using the wire protocol
Authenticates using the configured method
Sends each SQL statement as a Simple Query or Extended Query message
Receives RowDescription (column metadata) and DataRow (actual data) messages back
Formats and displays the results

psql also supports meta-commands (like \dt to list tables, \d+ tablename to describe a table) which are syntactic sugar — they translate into catalog queries like SELECT * FROM pg_catalog.pg_tables WHERE schemaname = 'public'.

Think of psql as a scalpel — precise, manual, one command at a time. You can do anything with it, but you have to know exactly what to ask.

pg_dump — The Full-Database Exporter

pg_dump is not an interactive tool. It's an automated export engine that reads an entire database and produces a structured backup. When you run:

pg_dump --host=myserver --dbname=mydb --format=directory --jobs=4 --file=./dump/

Here’s what actually happens at the protocol level:

Step 1: Catalog Discovery

pg_dump connects to the PostgreSQL server — using the exact same wire protocol that psql uses — and reads the system catalog to build a complete picture of the database. Under the hood it's running SQL, just like psql would. In fact, if you traced the network traffic, you'd see the same kind of SELECT statements flowing over the wire.

So what’s the difference?
You could open psql and manually query pg_class to find all tables, then query pg_attribute for their columns, then pg_constraint for their keys, then pg_depend for dependency ordering, and so on. But you'd need to know exactly which catalog tables to query, how to join them, how to interpret the results (what does relkind = 'p' mean? what about contype = 'f'?), and how all of this changed between PostgreSQL 10 and 17.

pg_dump has all that knowledge baked in — roughly 15,000 lines of C code that have been maintained and updated with every PostgreSQL release for over two decades. It knows that PostgreSQL 10 added identity columns, that 12 added generated columns, that 15 added security invoker views, and adjusts its catalog queries accordingly.

The distinction isn’t what protocol they speak (it’s the same) — it’s what they do with it. psql is a blank canvas: you write the SQL, you interpret the results. pg_dump is a specialist: it knows exactly which questions to ask the catalog to reconstruct your entire database.

Step 2: Dependency-Ordered DDL Generation

Here’s where pg_dump really earns its keep. After reading the catalog, it doesn't just spit out CREATE TABLE statements in random order. It uses the dependency graph from pg_depend to perform a topological sort, ensuring everything is created in the right sequence:

-- 1. Types first (because tables reference them)
CREATE TYPE public.status_enum AS ENUM ('active', 'inactive', 'suspended');

-- 2. Tables next (because indexes and FKs reference them)
CREATE TABLE public.users (
    id integer NOT NULL,
    name text,
    status public.status_enum DEFAULT 'active',
    created_at timestamp with time zone DEFAULT now()
);

-- 3. Sequences with ownership
CREATE SEQUENCE public.users_id_seq
    AS integer START WITH 1 INCREMENT BY 1 ...;
ALTER SEQUENCE public.users_id_seq OWNED BY public.users.id;

-- 4. Indexes after their tables exist
CREATE INDEX idx_users_status ON public.users USING btree (status);

-- 5. Foreign keys last (may reference other tables)
ALTER TABLE ONLY public.orders
    ADD CONSTRAINT orders_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);

If you tried to run these in the wrong order — say, creating the foreign key before the users table exists — it would fail. With a handful of tables this is easy to sort out manually. With a production database containing thousands of tables, hundreds of types, cross-schema foreign keys, and circular dependencies involving functions and triggers, getting the order right is a genuinely hard graph problem. pg_dump handles 200+ object types and gets this right across all PostgreSQL versions.

Step 3: Parallel Data Export

When using --format=directory --jobs=N, pg_dump opens N parallel connections to the database, all pinned to the same transaction snapshot. This ensures that even though different tables are being dumped by different connections at different times, they all see the same consistent state of the data.

Each connection uses the COPY protocol — PostgreSQL's high-speed bulk transfer mechanism — to stream table data:

COPY public.users TO STDOUT WITH (FORMAT text);

The COPY protocol is significantly faster than row-by-row SELECT because it bypasses the normal query result formatting and streams raw tuples directly over the wire.

The output is a directory structure:

./dump/
  toc.dat              ← Binary table-of-contents manifest
  3456.dat             ← Data for table "users" (COPY format)
  3457.dat             ← Data for table "orders"
  3458.dat             ← Data for table "events"
  ...

Think of pg_dump as a bulldozer — it grabs everything at once, correctly, consistently, and fast.

pg_restore — The Database Loader

pg_restore reads dump files produced by pg_dump and loads them back into a database. It can:

Restore an entire dump or selected objects
Reorder operations for parallel loading
Handle the toc.dat manifest to understand what's in the dump

pg_restore --host=target-server --dbname=targetdb --jobs=4 ./dump/