PostgreSQL

What it is for

PostgreSQL 16 (from the official PostgreSQL apt repository) is the lake’s relational memory. It never holds lake data, only bookkeeping.

Database	Owner role	Holds	Created by
ingest	ingest_app	The registry: vendors, projects, folders, files, events, views, keys, audit	step 14
nessie	nessie	The Iceberg catalog’s version store	step 05
keycloak	keycloak	Users, roles, sessions	step 13
serving	postgres	Schema api served by PostgREST (older path)	step 02

Other roles: web_anon (no login; what PostgREST uses for anonymous callers, with no access to published data), authenticator (PostgREST’s login), lake_writer (Trino’s writer into serving, step 08).

Where it lives

Service	postgresql (Ubuntu’s standard unit)
Port	5432, listen_addresses = 'localhost'
Tuning file	/etc/postgresql/16/main/conf.d/lake.conf (written by step 02)
Data	/var/lib/postgresql/16/main

Tuning written by step 02 assumes about 128 GB of RAM: max_connections = 200, shared_buffers = 8GB, effective_cache_size = 24GB, work_mem = 64MB. Use roughly 25 % of RAM for shared_buffers elsewhere.

Check it

systemctl is-active postgresql
sudo -u postgres psql -c '\l'          # the databases
sudo -u postgres psql -d ingest -c '\dt'

How to use it

sudo -u postgres psql -d ingest                    # a shell in the registry (as the superuser)

Useful read-only questions:

-- files by status
select status, count(*) from files group by status order by 2 desc;
-- newest events
select ts, actor, action, detail from file_events order by id desc limit 20;
-- who is quiet: the folders with no upload in 30 days
select f.id, f.code from folders f
 where not exists (select 1 from files x where x.folder_id = f.id and x.uploaded_at > now() - interval '30 days');
-- size of each table
select relname, pg_size_pretty(pg_total_relation_size(relid)) from pg_catalog.pg_statio_user_tables order by pg_total_relation_size(relid) desc;

(Column names are from infra/sql/003-files.sql and 002-projects.sql; \d files shows them on the server.)

Change the structure only through a numbered migration, never by typing ALTER TABLE on the server.

Back up (important)

Losing nessie loses the catalog (the lake’s table list), and losing ingest loses the registry. Back up at least:

sudo -u postgres pg_dump -Fc ingest   > ingest-$(date +%F).dump
sudo -u postgres pg_dump -Fc nessie   > nessie-$(date +%F).dump
sudo -u postgres pg_dump -Fc keycloak > keycloak-$(date +%F).dump

Restore to a spare database with pg_restore -d <db> <file>. A scheduled backup is not set up yet; add one before production.

Things that go wrong

Symptom	Likely cause
Services cannot log in after a password change	The password also lives in each service’s config: re-run that service’s install step
too many connections	Many API/worker processes; check select count(*) from pg_stat_activity
A long audit search	The API times each one out (5 s); nothing to fix on the server
Passwords with /, + or = break connection strings	Generate with openssl rand -hex, not base64