PostgreSQL na Ubuntu: instalace, uživatelé a první databáze

PostgreSQL je robustní open-source databáze, která se hodí pro malé projekty i velké aplikace. Na serveru s Ubuntu nebo Debianem ji zprovozníte za pár minut. Tady je postup od instalace po první tabulku, včetně bezpečného nastavení přístupu.

Instalace

Distribuční balíček stačí pro většinu případů:

sudo apt update
sudo apt install -y postgresql postgresql-contrib

Ubuntu 24.04 tak nainstaluje PostgreSQL 16, Debian 12 verzi 15. Pokud potřebujete novější verzi, použijte oficiální repozitář PGDG:

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt install -y postgresql-17

Ověřte, že služba běží:

sudo systemctl status postgresql
pg_lsclusters

pg_lsclusters ukáže verzi, port (výchozí 5432), stav a cestu k datům.

První přihlášení

Instalace vytvoří systémového uživatele postgres a stejnojmennou databázovou roli. Lokálně se přihlašuje metodou peer – databáze věří tomu, pod jakým systémovým uživatelem jste.

sudo -u postgres psql

Jste v konzoli psql. Užitečné příkazy:

Příkaz	Co dělá
\l	seznam databází
\c nazev	připojení k databázi
\dt	tabulky v aktuálním schématu
\d tabulka	struktura tabulky
\du	role (uživatelé)
\x	přepnutí na svislý výpis řádků
\q	konec

Uživatel a databáze pro aplikaci

Aplikace by nikdy neměla používat superuživatele postgres. Vytvořte jí vlastní roli a databázi:

CREATE ROLE myapp WITH LOGIN PASSWORD 'silne-heslo-zde';
CREATE DATABASE myapp OWNER myapp;

Protože je myapp vlastníkem databáze, může v ní vytvářet tabulky. Od PostgreSQL 15 už běžní uživatelé nemohou automaticky vytvářet objekty ve schématu public cizí databáze – vlastnictví databáze je proto nejjednodušší cesta.

Připojení heslem přes TCP:

psql -h localhost -U myapp -d myapp

Connection string pro aplikaci vypadá takto:

postgresql://myapp:silne-heslo-zde@localhost:5432/myapp

První tabulka

CREATE TABLE notes (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title      TEXT NOT NULL,
  body       TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO notes (title, body) VALUES ('První poznámka', 'Funguje to.')
RETURNING id, created_at;

SELECT * FROM notes;

Pár tipů pro PostgreSQL:

• Pro automatické ID používejte GENERATED ALWAYS AS IDENTITY místo staršího SERIAL.
• Čas ukládejte jako TIMESTAMPTZ, ne TIMESTAMP – vyhnete se zmatkům s časovými pásmy.
• TEXT není v PostgreSQL pomalejší než VARCHAR(n); délkový limit dávejte jen tam, kde ho opravdu potřebujete.
• RETURNING vrátí hodnoty vložených řádků bez dalšího dotazu.

Vzdálený přístup (jen pokud ho potřebujete)

Ve výchozím stavu PostgreSQL naslouchá jen na localhost, což je bezpečné. Pokud se potřebujete připojit odjinud, upravte dva soubory (cesta podle verze, např. /etc/postgresql/16/main/).

postgresql.conf:

listen_addresses = '*'

pg_hba.conf – povolte jen konkrétní IP adresu a šifrované heslo:

# TYPE  DATABASE  USER   ADDRESS          METHOD
host    myapp     myapp  203.0.113.10/32  scram-sha-256

Restart a firewall:

sudo systemctl restart postgresql
sudo ufw allow from 203.0.113.10 to any port 5432 proto tcp

Nikdy neotevírejte port 5432 celému internetu. Bezpečnější alternativou je SSH tunel, který nevyžaduje žádné změny konfigurace:

ssh -L 5433:localhost:5432 uzivatel@server
# a lokálně:
psql -h localhost -p 5433 -U myapp -d myapp

Záloha a obnova

Logická záloha jedné databáze:

pg_dump -U myapp -h localhost -Fc myapp > myapp.dump

Formát -Fc (custom) je komprimovaný a umožňuje obnovit jen vybrané tabulky:

pg_restore -U myapp -h localhost -d myapp --clean myapp.dump

Zálohy spouštějte z cronu a pravidelně zkoušejte obnovu na jiném stroji – nevyzkoušená záloha není záloha.

Kontrola, že vše funguje

sudo -u postgres psql -c "SELECT version();"
psql -h localhost -U myapp -d myapp -c "SELECT count(*) FROM notes;"
sudo tail -n 50 /var/log/postgresql/postgresql-16-main.log

Pokud připojení selže s chybou password authentication failed, zkontrolujte pořadí řádků v pg_hba.conf – platí první řádek, který odpovídá. Po úpravě stačí sudo systemctl reload postgresql.

Máte-li server připojený v ColdHost AI přes SSH, můžete instalaci projít v režimu Server a DevOps: AI navrhne příkazy, vy každý schválíte kliknutím a výstup uvidí pro další krok.