PostgreSQL op Debian en Ubuntu installeren en instellen

PostgreSQL is op Debian en Ubuntu in twee minuten geïnstalleerd. De resterende twee uur verdwijnen meestal in de vraag waarom niemand kan inloggen. Dit artikel maakt de installatie af: pakketkeuze, eerste toegang, database en gebruiker, de toegangsregels in pg_hba.conf en een back-up waarvan u weet dat die zich werkelijk laat terugzetten.

Alle commando's draaien als root. Werkt u met een gewone gebruiker, zet er dan sudo voor. Het versienummer 17 in paden vervangt u door de versie die op uw systeem werkelijk geïnstalleerd is.

Welke PostgreSQL-versie in welke distributie zit

Het belangrijkste verschil tussen de vier gangbare systemen is de hoofdversie die uit de repository van de distributie komt. Die is vast gekoppeld aan de release en verandert gedurende de looptijd van de distributie niet meer.

Die spreiding heeft praktische gevolgen. Een dump uit Debian 13 laat zich niet zomaar in Ubuntu 22.04 inlezen. En wie op Ubuntu 22.04 werkt, moet weten dat PostgreSQL 14 volgens het versiebeleid van de PostgreSQL Global Development Group op 12 november 2026 uit de communityondersteuning valt. Ubuntu levert voor 22.04 weliswaar nog veiligheidsupdates binnen de LTS-cyclus, maar er stromen geen upstream-fixes meer door. Voor nieuwe projecten op 22.04 is dat een sterk argument om meteen naar de PGDG-repository te grijpen.

Wat er op uw systeem klaarstaat, laat een blik in de pakketdatabase zien, nog voordat u iets installeert:

apt update
apt-cache policy postgresql

De regel Kandidaat respectievelijk Candidate toont een versienummer zoals 17+283. Het getal vóór het plusteken is de hoofdversie van PostgreSQL, de rest is het versienummer van het Debian-metapakket.

Installeren vanuit het distributiepakket

Voor de meeste toepassingen is het distributiepakket de juiste keuze. Het is opgenomen in de veiligheidsupdates van de distributie, het werkt met de bibliotheken van het systeem en het levert bij een release-upgrade geen problemen op.

apt install -y postgresql postgresql-contrib

postgresql-contrib levert de meegeleverde extensies, waaronder pgcrypto, uuid-ossp en pg_stat_statements. Zonder dat pakket stranden veel applicaties later op een ERROR: could not open extension control file, en het zoeken naar de oorzaak duurt langer dan de installatie zelf.

Debian en Ubuntu maken bij de installatie automatisch een eerste cluster met de naam main aan en starten dat meteen. Cluster betekent hier een draaiende instantie met een eigen gegevensmap, een eigen poort en een eigen configuratie. Of dat gelukt is, blijkt niet uit de exitcode van apt, maar hieruit:

pg_lsclusters

In de kolom Status moet de uitvoer online tonen:

Ver Cluster Port Status Owner    Data directory              Log file
17  main    5432 online postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log

Staat daar down, start de dienst dan alsnog. service postgresql start werkt op alle vier de systemen, ook in containers zonder systemd. Op een gewone server doet systemctl start postgresql precies hetzelfde.

service postgresql start
pg_isready

pg_isready antwoordt met /var/run/postgresql:5432 - accepting connections en levert exitcode 0. Dat is het eerste harde bewijs dat de server bereikbaar is, en het laat zich in monitoringscripts hergebruiken.

Wanneer de PGDG-repository loont en hoe u die netjes toevoegt

De officiële repository onder apt.postgresql.org levert alle ondersteunde hoofdversies parallel voor trixie, bookworm, noble en jammy. Dat is de juiste keuze wanneer u een bepaalde hoofdversie nodig hebt omdat de applicatie die vereist, wanneer u extensies nodig hebt die Debian niet als pakket aanbiedt, of wanneer uw distributie naar een versie wijst die binnenkort uit de ondersteuning loopt.

De sleutel hoort in een eigen bestand, niet meer in de afgeschafte sleutelbos van apt-key. Debian 13 en Ubuntu 24.04 geven bovendien de voorkeur aan het deb822-formaat met de extensie .sources, dat overigens ook op Debian 12 en Ubuntu 22.04 werkt:

apt install -y curl ca-certificates
install -d /usr/share/postgresql-common/pgdg
curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

cat > /etc/apt/sources.list.d/pgdg.sources <<EOF
Types: deb
URIs: https://apt.postgresql.org/pub/repos/apt
Suites: $(. /etc/os-release && echo $VERSION_CODENAME)-pgdg
Components: main
Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
EOF

De regel met $(. /etc/os-release ...) vult automatisch trixie-pgdg, bookworm-pgdg, noble-pgdg of jammy-pgdg in. Daarna:

apt update
apt-cache policy postgresql-18

Het pakket postgresql-common levert voor hetzelfde doel een kant-en-klaar script mee, /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh. Dat is een alternatief voor de sleutel en de pgdg.sources hierboven, geen aanvulling. Voert u beide uit, dan legt het script daarnaast een /etc/apt/sources.list.d/pgdg.list aan, en waarschuwt apt daarna bij elke run: W: Target Packages (main/binary-amd64/Packages) is configured multiple times in /etc/apt/sources.list.d/pgdg.list:1 and /etc/apt/sources.list.d/pgdg.sources:1. Wie het script verkiest boven de handelingen hierboven, installeert vooraf ook gnupg, dus apt install -y curl ca-certificates gnupg. De oudere scriptversie op Ubuntu 22.04 importeert de sleutel namelijk nog via apt-key en breekt zonder gnupg af met E: gnupg, gnupg2 and gnupg1 do not seem to be installed, but one of them is required for this operation en exitcode 255. Op Debian 13, Debian 12 en Ubuntu 24.04 legt de nieuwere versie de sleutel direct als .asc weg en loopt het script zonder extra pakket door.

De valkuil: twee clusters, twee poorten

Installeert u op een systeem waarop het distributiepakket al staat een nieuwe hoofdversie, dan ontstaat er een tweede cluster. Dat krijgt niet poort 5432, maar de eerstvolgende vrije, dus 5433. Applicaties verbinden gewoon door met de oude versie, zonder dat een foutmelding daarop wijst. Dat is verreweg de meest voorkomende oorzaak van de zin "ik heb toch PostgreSQL 18 geïnstalleerd, maar SELECT version() toont 15".

apt install -y postgresql-18
pg_lsclusters

Nu staan daar twee regels met verschillende poorten. Wilt u de gegevens naar de nieuwe versie overzetten, dan is pg_upgradecluster het juiste gereedschap, geen handmatige dump. Het vereist dat beide serverpakketten geïnstalleerd zijn, en het laat het oude cluster gestopt staan in plaats van het te verwijderen:

pg_upgradecluster 15 main

Controleer daarna met pg_lsclusters welk cluster op poort 5432 zit, en test de applicatie voordat u het oude cluster met pg_dropcluster --stop 15 main definitief verwijdert. Dat commando wist de gegevensmap zonder te vragen.

De eerste toegang, en waarom root geen psql mag starten

Het klassieke struikelblok direct na de installatie:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "root" does not exist

Dat is geen fout, maar het verwachte gedrag. Debian en Ubuntu configureren lokale socketverbindingen met de methode peer. PostgreSQL vraagt daarbij aan de kernel onder welke systeemgebruiker de verbinding geopend is, en eist dat er een databaserol met dezelfde naam bestaat. Alleen de rol postgres bestaat, dus moet u eerst naar die systeemgebruiker wisselen.

su - postgres

Daarna start u psql zonder argumenten. De shell verlaat u met exit. Voor losse commando's vanuit een script is een wissel per commando praktischer:

runuser -u postgres -- psql -c "SELECT version();"

Wie sudo geïnstalleerd heeft, schrijft in plaats daarvan sudo -u postgres psql -c "SELECT version();". Beide varianten zijn gelijkwaardig. Bij sudo verschijnt vaak de waarschuwing could not change directory to "/root": Permission denied. Die heeft geen gevolgen, omdat de gebruiker postgres de werkmap van root niet mag betreden, terwijl het commando toch wordt uitgevoerd.

Deze drie query's laten zien waarmee u te maken hebt, en ze vormen bij elke foutanalyse de eerste stap:

runuser -u postgres -- psql -c "SHOW server_version;"
runuser -u postgres -- psql -c "SHOW config_file;"
runuser -u postgres -- psql -c "SHOW hba_file;"

Het laatste commando is bijzonder nuttig. Het noemt het pad dat de draaiende server werkelijk leest, doorgaans /etc/postgresql/17/main/pg_hba.conf. Bewerkt u een bestand en verandert er niets, dan hebt u vrijwel altijd de configuratie van een ander cluster voor u.

Binnen psql helpen de meta-commando's: \l toont de databases, \du de rollen, \dt de tabellen van de huidige database, \conninfo laat zien als wie en waarheen u verbonden bent, en \q beëindigt de sessie.

Database en gebruiker aanmaken

Maak voor elke applicatie een eigen rol en een eigen database aan. De volgorde is belangrijk, want de database moet meteen eigendom van die rol zijn.

runuser -u postgres -- psql -c "CREATE ROLE appuser LOGIN PASSWORD 'UwSterkeWachtwoord';"
runuser -u postgres -- psql -c "CREATE DATABASE appdb OWNER appuser;"

Sinds PostgreSQL 14 worden wachtwoorden standaard met scram-sha-256 opgeslagen, dat geldt dus op alle vier de hier behandelde systemen. Het wachtwoord belandt daarmee niet in leesbare vorm in de database, maar het staat wel in uw shell-history. Wilt u dat voorkomen, gebruik dan binnen psql in plaats daarvan \password appuser, dat er interactief om vraagt.

De valkuil sinds PostgreSQL 15: permission denied for schema public

Een gedrag dat veel oudere handleidingen nog niet kennen: vanaf PostgreSQL 15 mag niet meer elke gebruiker objecten in het schema public aanmaken. Getroffen zijn Debian 12, Debian 13 en Ubuntu 24.04. Alleen Ubuntu 22.04 met PostgreSQL 14 gedraagt zich nog volgens het oude patroon. De fout ziet er zo uit:

ERROR:  permission denied for schema public
LINE 1: CREATE TABLE klanten (id serial primary key);

De nette weg is die hierboven: de database is eigendom van de rol. Het schema public hoort sinds versie 15 bij de rol pg_database_owner, en de eigenaar van de betreffende database is daar impliciet lid van. Wie de database daarentegen zonder OWNER heeft aangemaakt, kent de rechten alsnog toe. Let erop dat deze opdracht in de betreffende database uitgevoerd moet worden, niet in postgres:

runuser -u postgres -- psql -d appdb -c "GRANT ALL ON SCHEMA public TO appuser;"

Bewijzen dat de rechten kloppen

Een CREATE ROLE zonder foutmelding betekent nog niet dat de applicatie kan inloggen. Het bewijs levert u door werkelijk via TCP in te loggen en daarna iets weg te schrijven. PGPASSWORD is hier alleen bedoeld om te testen, voor de dagelijkse praktijk zie verderop:

PGPASSWORD='UwSterkeWachtwoord' psql -h 127.0.0.1 -U appuser -d appdb -c "SELECT current_user, current_database();"
PGPASSWORD='UwSterkeWachtwoord' psql -h 127.0.0.1 -U appuser -d appdb -c "CREATE TABLE proef (id int);"

Lopen beide door, dan is de combinatie van rol, wachtwoord, database en schemarechten compleet. De tabel proef blijft bewust staan, want de back-uptest verderop heeft minstens één object in de database nodig, anders controleert die niets. Ter controle somt u de objecten op:

runuser -u postgres -- psql -c "\du"
runuser -u postgres -- psql -c "\l"

Nog een woord over de tekencodering: stond de landinstelling van het systeem bij het aanmaken van het cluster niet op UTF-8, dan kan de sjabloondatabase SQL_ASCII zijn. Een CREATE DATABASE ... ENCODING 'UTF8' strandt dan op ERROR: new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII). De uitweg is TEMPLATE template0 bij het aanmaken, de nette oplossing is een systeem met een UTF-8-landinstelling.

pg_hba.conf begrijpen, de meest voorkomende foutbron

Het bestand pg_hba.conf (host-based authentication) beslist nog vóór elke wachtwoordcontrole of een verbinding er eigenlijk wel in mag. Het wordt van boven naar beneden gelezen, en de eerste passende regel wint. Past er geen enkele, dan wordt de verbinding geweigerd. Een ruimhartige regel verderop helpt u niet als een strengere regel daarboven eerst aanslaat. Dat is met afstand de meest voorkomende configuratiefout.

De toestand bij levering ziet er op Debian en Ubuntu zo uit:

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

Zo staat het op de vier hier behandelde systemen. Op oudere versies, bijvoorbeeld Debian 11 met PostgreSQL 13, staat in de twee host-regels nog md5 in plaats van scram-sha-256. Inloggen via TCP werkt in beide gevallen, maar op md5 moet u niet meer vertrouwen.

De kolommen betekenen het volgende: local staat voor de unix-socket, host voor TCP met of zonder TLS, hostssl uitsluitend voor TLS-verbindingen. Daarna volgen database, rol, netwerkbereik in CIDR-notatie en de methode. Vier methoden zijn van belang. peer controleert de systeemgebruiker en werkt alleen via de socket. scram-sha-256 is de moderne wachtwoordmethode en de juiste keuze voor alles wat over TCP loopt. md5 is afgeschaft en hoort in nieuwe configuraties niet meer voor te komen. trust laat iedereen zonder controle binnen en heeft op een bereikbare server niets te zoeken.

De foutmeldingen letterlijk

Wie ze eenmaal uit elkaar kan houden, hoeft veel minder te gokken:

FATAL:  Peer authentication failed for user "appuser"

U bent via de socket verbonden, maar uw systeemgebruiker heet anders dan de rol. Wissel van gebruiker of verbind via -h 127.0.0.1, zodat de host-regel aanslaat.

FATAL:  no pg_hba.conf entry for host "198.51.100.4", user "appuser", database "appdb", no encryption

De server is bereikbaar, maar geen enkele regel past op deze combinatie van bronadres, rol en database. Er ontbreekt een regel, of het netwerk in de bestaande regel dekt het adres niet af.

FATAL:  password authentication failed for user "appuser"

De regel slaat aan, het wachtwoord klopt niet. Vaak omdat de rol zonder LOGIN is aangemaakt of omdat het wachtwoord nog uit een eerdere installatie stamt.

psql: error: connection to server at "203.0.113.10", port 5432 failed: Connection refused

Hier is pg_hba.conf nooit in beeld geweest. Of de server draait niet, of hij luistert niet op dat adres, of een firewall blokkeert. Daarover zo meteen meer.

Wijzigingen controleren voordat u herlaadt

PostgreSQL biedt een systeemview die de ingelezen regels toont, inclusief regelnummer en syntaxfouten. Die beantwoordt de vraag welke regel de server werkelijk ziet, in plaats van welke u denkt geschreven te hebben:

runuser -u postgres -- psql -c "SELECT line_number, type, database, user_name, address, auth_method FROM pg_hba_file_rules;"

Wijzigingen in pg_hba.conf vragen geen herstart, opnieuw inlezen volstaat en verbreekt geen bestaande verbindingen:

runuser -u postgres -- psql -c "SELECT pg_reload_conf();"

Als alternatief service postgresql reload. Een echte herstart is alleen nodig wanneer u parameters als listen_addresses, port of shared_buffers hebt gewijzigd.

Toegang van buitenaf openstellen

Standaard luistert PostgreSQL alleen op localhost. Dat is een goede standaardinstelling, en u zou die alleen moeten opgeven wanneer het echt nodig is. Twee dingen moeten samenkomen: de server moet op het adres luisteren, en pg_hba.conf moet de bron toestaan. Ontbreekt het eerste, dan krijgt u Connection refused, ontbreekt het tweede, dan krijgt u no pg_hba.conf entry.

De configuratie staat in /etc/postgresql/17/main/postgresql.conf. Debian levert daarvoor het gereedschap pg_conftool mee, dat het bestand betrouwbaarder bewerkt dan zoeken en vervangen in een editor:

pg_conftool 17 main show listen_addresses
pg_conftool 17 main set listen_addresses '10.0.0.5,127.0.0.1'

Vul concrete adressen in en gebruik geen *. Op een server met een publiek en een intern adres bindt u zo alleen aan het interne netwerk. Daarna vult u in pg_hba.conf een regel aan, zo strak mogelijk afgebakend:

host    appdb    appuser    10.0.0.0/24    scram-sha-256

Na een herstart met service postgresql restart controleert u eerst waarop het proces werkelijk luistert. ss -lntp | grep 5432 toont de gebonden adressen. Staat daar alleen 127.0.0.1:5432, dan is de wijziging niet aangeslagen, meestal omdat het om een tweede cluster ging of omdat een bestand onder conf.d de waarde overschrijft.

De firewall heeft eveneens een regel nodig, en wel een met een bronopgave. Een zonder meer opengezette poort 5432 op het internet wordt binnen enkele uren gescand:

ufw allow from 10.0.0.0/24 to any port 5432 proto tcp

Een eerlijk advies: in de meeste gevallen is het beter om de poort helemaal niet te openen. Een SSH-tunnel met ssh -L 5432:127.0.0.1:5432 gebruiker@server volstaat ruimschoots voor onderhoudstoegang. Voor permanente verbindingen tussen meerdere servers is een WireGuard-netwerk de nettere keuze, omdat de database dan nog steeds alleen op een privéadres luistert. Debian en Ubuntu activeren TLS overigens standaard met een zelfondertekend certificaat, daarom werkt sslmode=require meteen. Echte bescherming tegen een aanvaller in de verbinding biedt echter pas sslmode=verify-full met een certificaat dat de client vertrouwt.

Back-up met pg_dump, en het bewijs dat die deugt

Voor losse databases is het custom-formaat de beste keuze. Het is gecomprimeerd, het laat zich selectief terugzetten en het kan parallel ingelezen worden:

runuser -u postgres -- pg_dump -Fc -d appdb -f /var/lib/postgresql/appdb.dump

Een punt dat vaak over het hoofd wordt gezien: pg_dump back-upt geen rollen en geen wachtwoorden. Die liggen clusterbreed opgeslagen en moeten apart geback-upt worden, anders ontbreken na een herstel precies die gebruikers die de applicatie nodig heeft:

runuser -u postgres -- pg_dumpall --globals-only -f /var/lib/postgresql/globals.sql

Wanneer de versies niet bij elkaar passen

pg_dump: error: server version: 17.5; pg_dump version: 15.10
pg_dump: error: aborting because of server version mismatch

De regel luidt: pg_dump mag nieuwer zijn dan de server, nooit ouder. Op Debian en Ubuntu is dat eenvoudig op te lossen, omdat /usr/bin/pg_dump slechts een wrapper is die de passende programmaversie kiest. Installeer het pakket postgresql-client-18, dan staat de nieuwere versie klaar. En met de Debian-uitbreiding --cluster dwingt u de wrapper gericht naar een bepaald cluster, hier naar versie 17, cluster main:

pg_dump --version
runuser -u postgres -- pg_dump --cluster 17/main -Fc -d appdb -f /var/lib/postgresql/appdb.dump

Wachtwoorden buiten het script houden

Voor automatische back-ups hoort het wachtwoord in een .pgpass in het formaat host:port:database:gebruiker:wachtwoord. PostgreSQL negeert het bestand zonder enige melding wanneer de rechten te ruim staan. Even belangrijk is in wiens thuismap het staat, want gelezen wordt altijd het bestand van de gebruiker onder wie het commando werkelijk draait. Een ~/.pgpass als root blijft zonder effect zolang de back-up zoals in dit artikel via runuser -u postgres loopt. Dan telt de thuismap van postgres:

touch /var/lib/postgresql/.pgpass
chown postgres:postgres /var/lib/postgresql/.pgpass
chmod 0600 /var/lib/postgresql/.pgpass

Leeg blijft het bestand eveneens zonder effect. Vul één regel per verbinding in, bijvoorbeeld 127.0.0.1:5432:appdb:appuser:UwSterkeWachtwoord. Draait uw back-uptaak in plaats daarvan direct als root zonder gebruikerswissel, dan hoort hetzelfde bestand in /root/.pgpass.

De back-up controleren

Een back-upbestand dat nog nooit is teruggezet, is niet meer dan een aanname. De test kost een minuut. Bekijk eerst de inhoudsopgave, lees die daarna in een wegwerpdatabase in en tel de tabellen:

runuser -u postgres -- pg_restore -l /var/lib/postgresql/appdb.dump | head -n 20
runuser -u postgres -- createdb appdb_restore_test
runuser -u postgres -- pg_restore -d appdb_restore_test /var/lib/postgresql/appdb.dump
runuser -u postgres -- psql -d appdb_restore_test -c "\dt"
runuser -u postgres -- dropdb appdb_restore_test

Toont \dt dezelfde tabellen als in het origineel, hier dus minstens de tabel proef, dan is de back-up bruikbaar. Meldt het commando daarentegen Did not find any relations., dan was de database in de back-up leeg en bewijst de test niets. In appdb ruimt u de proeftabel daarna weer op met runuser -u postgres -- psql -d appdb -c "DROP TABLE proef;". Voor de dagelijkse praktijk volstaat een regel in /etc/cron.d die beide bestanden met de datum in de naam wegschrijft en oudere opruimt. Belangrijk is dat de bestanden daarna de server verlaten. Een back-up op dezelfde schijf helpt tegen een per ongeluk uitgevoerde DROP TABLE, niet tegen uitval van de hardware.

Wanneer het cluster niet start

Start de dienst niet, dan vertelt de dienststatus u meestal alleen dat er iets is mislukt. De eigenlijke oorzaak staat in het logbestand van het cluster:

tail -n 30 /var/log/postgresql/postgresql-*-main.log

Eén regel mag u daarbij gerust overslaan. De melding FATAL: role "root" does not exist uit het eerste gedeelte komt meestal van pg_isready: dat gereedschap zet zijn verbindingspoging op met de ingelogde systeemgebruiker, dus als root, en de server noteert de onbekende rol. De retourwaarde blijft desondanks 0, de uitvoer luidt accepting connections, en het cluster is volkomen in orde.

Op systemen met systemd levert journalctl -u postgresql@17-main --no-pager -n 50 dezelfde regels. Let op de unit met versienummer: postgresql.service is slechts een omhulsel dat alle clusters start, en die meldt ook succes wanneer een afzonderlijk cluster is mislukt. Daarom is pg_lsclusters de betrouwbaardere controle.

Drie meldingen dekken de meeste gevallen af. could not bind IPv4 address "0.0.0.0": Address already in use betekent dat een ander cluster de poort bezet houdt, zie het gedeelte over de twee clusters. Een melding No space left on device bij het schrijven van de postmaster.pid betekent domweg een volle schijf, wat u met df -h bevestigt. En fouten over ongeldige rechten op de gegevensmap treden op na onbezonnen chmod- of chown-acties: /var/lib/postgresql/17/main moet eigendom zijn van de gebruiker postgres en modus 0700 hebben.

Tot slot nog een opmerking over het dagelijks gebruik: PostgreSQL draait in de basisinstelling conservatief en benut het werkgeheugen van een server bij lange na niet. Voordat u aan shared_buffers en work_mem gaat draaien, activeert u pg_stat_statements uit het contrib-pakket en kijkt u welke query's werkelijk tijd kosten. In de praktijk zit het knelpunt bijna altijd bij een ontbrekende index, niet bij de geheugenparameters.