Postgres na Debianu – pracovní příručka

Co dělat hned po apt install postgresql, kde se co konfiguruje a co dělat, když to v sobotu večer nezvedne na 5432. Praktické, ne vyčerpávající – od založení databáze po backup. Vychází z toho, jak Postgres běhá na jerzy.cz.

Slonik, oficiální maskot PostgreSQL

Instalace

sudo apt install postgresql postgresql-contrib

Na Debianu 13 dostaneš major verzi 17. Balíček postgresql-contrib přidá sadu rozšíření, která dřív nebo později chtít budeš (pg_stat_statements, hstore, pg_trgm). Stojí to ~5 MB navíc, neváhej.

Co se po instalaci stane:

  • Vytvoří se systémový uživatel postgres.
  • Spustí se cluster main na portu 5432.
  • Data jsou v /var/lib/postgresql/17/main/.
  • Logy v /var/log/postgresql/postgresql-17-main.log.
  • Konfigurace v /etc/postgresql/17/main/.

Ověření, že to běží:

sudo systemctl status postgresql
pg_lsclusters

pg_lsclusters ti řekne, jaké clustery máš, na kterém portu, kdo je vlastník a kde má data. Pokud máš souběžně víc verzí Postgresu, ukáže všechny – Debian to umí.

První kontakt

sudo -u postgres psql

postgres je výchozí superuser bez hesla, autentizuje se přes metodu peer proti lokálnímu socketu (= systémový uživatel se musí jmenovat stejně jako role). Jakmile jsi v psql, párkrát se ti budou hodit meta-příkazy:

\l        seznam databází
\du       seznam rolí
\dt       tabulky aktuální DB
\d nazev  detail objektu
\c db     přepnout databázi
\q        ven

Vytvoření role a databáze

V Postgresu je role synonymum uživatele. Rozdíl mezi CREATE USER a CREATE ROLE je jen ten, že USER automaticky dostane atribut LOGIN. Pro běžnou aplikační roli:

CREATE ROLE jirka LOGIN PASSWORD 'tajne';
CREATE DATABASE jirka_app OWNER jirka ENCODING 'UTF8';

Vlastník databáze v ní může všechno. Pokud máš víc rolí, které mají jen číst, použij GRANT:

CREATE ROLE jirka_ro LOGIN PASSWORD 'tajne_ro';
GRANT CONNECT ON DATABASE jirka_app TO jirka_ro;
\c jirka_app
GRANT USAGE ON SCHEMA public TO jirka_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO jirka_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO jirka_ro;

ALTER DEFAULT PRIVILEGES je věc, na kterou se zapomíná: bez ní budou tabulky vytvořené v budoucnu pro read-only roli opět neviditelné.

Od Postgresu 15 nemá public schema implicitní CREATE pro PUBLIC. Pokud chceš, aby aplikační role mohla v public zakládat tabulky, dej jí to explicitně: GRANT CREATE ON SCHEMA public TO jirka;.

Připojení a autentizace

psql -U jirka -d jirka_app -h localhost se zeptá na heslo. Bez -h jde přes Unix socket, kde výchozí metoda je peer. Veškerá pravidla „kdo se kam smí přihlásit a jak“ žijí v /etc/postgresql/17/main/pg_hba.conf. Vypadá to takhle:

# TYPE  DATABASE  USER  ADDRESS         METHOD
local   all       all                   peer
host    all       all   127.0.0.1/32    scram-sha-256
host    all       all   ::1/128         scram-sha-256

Postgres jde řádky shora dolů a první match vyhrává – pořadí důležité. Po editaci stačí:

sudo systemctl reload postgresql

reload zabere pro pg_hba.conf i pro postgresql.conf. restart je nutný jen pro pár parametrů jako shared_buffers nebo listen_addresses.

Pokud chceš povolit připojení po síti, v postgresql.conf:

listen_addresses = '*'

A v pg_hba.conf přidej řádek s konkrétní sítí. Ne 0.0.0.0/0, ledaže to máš schválně, máš firewall a víš co děláš.

Zálohy

Per-databázový dump v komprimovaném binárním formátu, který umí pg_restore rozbalit selektivně:

sudo -u postgres pg_dump -Fc jirka_app > jirka_app.dump

Cluster-wide zálohu (včetně rolí, hesel, tablespaců) udělá pg_dumpall:

sudo -u postgres pg_dumpall > cluster.sql

pg_dumpall produkuje plain SQL, ne binární formát. Pro vlastní data je pg_dump -Fc rychlejší a flexibilnější – proto se v praxi často spojuje oboje: pg_dumpall --globals-only pro role a oprávnění, pg_dump -Fc pro každou DB zvlášť.

Restore custom dumpu:

sudo -u postgres createdb jirka_app
sudo -u postgres pg_restore -d jirka_app jirka_app.dump

pg_restore sám databázi nezakládá (pokud nepoužiješ -C). Z .sql souboru obnovíš jednoduše psql -d jirka_app < cluster.sql.

Údržba

Autovacuum běží od verze 8.3. Pro běžný provoz nemusíš ručně dělat nic. Když přesto potřebuješ:

VACUUM ANALYZE;     -- celá DB, refresh i statistik
VACUUM FULL nazev;  -- block-level kompakce, exclusive lock
REINDEX TABLE nazev;

VACUUM FULL tabulku během práce zamkne – na produkci radši ne, leda v okně.

Logy běží v /var/log/postgresql/. Default je úroveň LOG, což je celkem ukecané. Pokud chceš detaily k pomalým dotazům, v postgresql.conf:

log_min_duration_statement = 500   # ms

Cokoli pomalejšího než půl sekundy se loguje s textem dotazu.

Když to nejede

Tři kontroly v tomhle pořadí:

  1. sudo systemctl status postgresql – sedí to vůbec na portu?
  2. sudo tail -n 100 /var/log/postgresql/postgresql-17-main.log – co řeknou logy?
  3. sudo -u postgres psql – projde aspoň lokální connect?

Nejčastější příčiny, co máš čekat:

  • disk full – Postgres rád zhasne. df -h /var/lib/postgresql.
  • WAL segments – pokud archivace nestíhá, naplní pg_wal a cluster se zastaví.
  • OOM killer – dmesg | grep -i oom. Sniž shared_buffers nebo work_mem.
  • pg_hba syntax – po editaci se cluster nemusí spustit. Logy ti řeknou řádek.

Co tady není

Replikace (streaming i logická), point-in-time recovery, tuning pro produkci, vector workloads s pgvector. Každé z toho stojí za vlastní zápis. Tady stačí základ, ať máš databázi v provozu a víš, kam šáhnout, když přestane.