Kör en egen PostgreSQL-databasserver

Det här sätter du upp

En hanterad databastjänst ger dig en adress, ett användarnamn och ett lösenord, och sköter resten. Den här guiden bygger samma sak på din egen server: PostgreSQL 18 med en databas och en inloggning per app, inställningar anpassade efter servern, nattliga backuper, och åtkomst bara från de platser där dina appar körs.

PostgreSQL utvecklas av PostgreSQL Global Development Group, ett communityprojekt. Namnen PostgreSQL och Postgres och elefantlogotypen är registrerade varumärken som tillhör PostgreSQL Community Association of Canada. PostgreSQL är öppen källkod under PostgreSQL-licensen, en kort licens som liknar BSD och MIT. PostgreSQL kontaktar ingen tjänst på internet: på vår testserver öppnade den inga utgående anslutningar. Paketen kommer här från apt.postgresql.org, projektets eget paketförråd, som servern kontaktar vid varje apt update.

Varje steg nedan har körts på en nyinstallerad Melonslab VC-P Alloy (2 vCPU, 8 GB) med Debian 13:

• PostgreSQL 18.6 installerat från PostgreSQL-projektets paketförråd,
• en inloggning för en app som kan skapa tabeller i sin egen databas, men inte öppna en annan apps databas, skapa databaser eller läsa filer på servern,
• anslutningar via WireGuard och genom en SSH-tunnel, med port 5432 stängd på den publika adressen,
• i den publika varianten: ett certifikat från Let's Encrypt som klienten kontrollerar, anslutningar utan kryptering och fel lösenord nekade, en adress som inte står med i listan som får vänta förgäves, och en tvingad förnyelse av certifikatet som PostgreSQL tog i bruk utan omstart,
• en körning med pgbench före och efter justeringen,
• en nattlig dump återställd i en ny databas,
• en uppgradering av huvudversion från PostgreSQL 17 till 18 med pg_upgradecluster,
• allt fungerade igen efter en omstart av servern.

Efter en omstart, med inställningarna från steg 3, använde hela servern 437 MB minne. PostgreSQL:s egen cache tar mer allteftersom den fylls med din data, upp till de 2 GB som ställs in i steg 3.

Innan du börjar

Du behöver:

• en server med Debian 13, uppsatt som i Säkra en ny Debian-server, med ufw påslaget. Välj bort swap när du beställer, och använd zram om du vill ha swap,
• för privat åtkomst: WireGuard på servern med tunneladressen 10.8.0.1, eller bara SSH,
• för publik åtkomst: ett namn som pg.example.com med en A-post och en AAAA-post som pekar på servern, och de fasta adresserna till de maskiner som ska ansluta.

Körs din app på en annan server hos Melonslab läser du först Anslut två servrar: två servrar i samma nät kan inte nå varandra direkt.

Exemplen använder 203.0.113.10 för databasservern, 198.51.100.7 för appservern eller datorn som ansluter, och appdb och appuser för appens databas och inloggning. Byt ut dem mot dina egna genomgående.

1. Installera PostgreSQL 18

Debian 13 innehåller PostgreSQL 17 och behåller den så länge versionen av Debian lever. PostgreSQL-projektets eget paketförråd har 18 redan i dag, och varje ny huvudversion när den kommer, så att du själv bestämmer när du uppgraderar. Den här guiden använder det.

Som root lägger du till paketförrådet med skriptet som följer med Debians paket postgresql-common, och installerar sedan PostgreSQL 18:

apt update
apt install -y postgresql-common
/usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y
apt install -y postgresql-18
pg_lsclusters

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

Skriptet skriver /etc/apt/sources.list.d/pgdg.sources och paketförrådets signeringsnyckel. PostgreSQL startar direkt och lyssnar bara på 127.0.0.1 och ::1. Lösenord sparas som SCRAM-SHA-256-hashar, och datakontrollsummor är påslagna, båda som standard i PostgreSQL 18.

Installera uppdateringarna automatiskt

De automatiska uppdateringarna från säkerhetsguiden installerar bara paket från Debian. För att även få PostgreSQL:s bugg- och säkerhetsfixar skapar du /etc/apt/apt.conf.d/51unattended-upgrades-postgresql:

Unattended-Upgrade::Origins-Pattern {
    "origin=apt.postgresql.org";
};

unattended-upgrade --dry-run --debug 2>&1 | grep 'Allowed origins'

Listan slutar nu med origin=apt.postgresql.org. En uppdatering startar om PostgreSQL, så apparna tappar sina anslutningar en kort stund på morgonen när uppdateringarna körs. Varje huvudversion är ett eget paket, så det här flyttar dig aldrig från 18 till 19.

2. Skapa en databas och en inloggning för varje app

Ge varje app en egen inloggning och en egen databas, så att en läcka i en app inte öppnar de andra. Ta först fram ett långt lösenord:

openssl rand -base64 24

Skapa inloggningen och ge den en databas som den äger. createuser frågar efter lösenordet två gånger:

sudo -u postgres createuser --pwprompt appuser
sudo -u postgres createdb --owner=appuser appdb
sudo -u postgres psql -c "REVOKE CONNECT, TEMPORARY ON DATABASE appdb FROM PUBLIC"

Inloggningen är ingen superanvändare och kan inte skapa databaser eller andra inloggningar. Som ägare av appdb kan den skapa tabeller där, vilket appens migreringar behöver.

Som standard får alla inloggningar ansluta till alla databaser. Raden med REVOKE tar bort den rätten för alla utom ägaren. Sedan PostgreSQL 15 kan andra inloggningar inte heller skapa tabeller i en databas schema public. Vi kontrollerade båda med inloggningen för en annan app, shopuser:

$ psql -h 127.0.0.1 -U shopuser -d appdb
FATAL:  permission denied for database "appdb"
DETAIL:  User does not have CONNECT privilege.

$ psql -h 127.0.0.1 -U shopuser -d postgres -c 'create table t(i int)'
ERROR:  permission denied for schema public

Och appuser stoppas själv vid create database (permission denied to create database) och när den försöker läsa filer på servern med pg_read_file (permission denied for function pg_read_file).

Upprepa de tre kommandona för varje app, med dess egna namn.

3. Anpassa den efter servern

PostgreSQL:s standardinställningar är gjorda för att starta på vilken maskin som helst, med 128 MB till sin egen cache. Skapa /etc/postgresql/18/main/conf.d/tuning.conf för 2 vCPU och 8 GB:

# Sized for 2 vCPU and 8 GB of memory
max_connections = 100
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 16MB
maintenance_work_mem = 512MB
random_page_cost = 1.1

• shared_buffers är PostgreSQL:s egen cache. En fjärdedel av minnet är den vanliga utgångspunkten, eftersom resten används av systemets filcache, som PostgreSQL också läser via.
• effective_cache_size reserverar ingenting. Den talar om för planeraren hur stor del av databasen som troligen finns i minnet, filcachen inräknad.
• work_mem är vad en sortering eller join får använda innan den skriver till disk. En komplicerad fråga kan använda den flera gånger om, i varje anslutning, så håll den måttlig.
• maintenance_work_mem snabbar upp VACUUM och när index byggs.
• max_connections står kvar på standardvärdet 100. Varje anslutning är en egen process. Behöver dina appar fler är en anslutningspool som PgBouncer det vanliga svaret (ingår inte här).
• random_page_cost = 1.1 talar om för planeraren att slumpvisa läsningar är billiga på SSD-lagring.

Använd dem med en omstart, och kontrollera ett av värdena:

systemctl restart postgresql@18-main
sudo -u postgres psql -c "SHOW shared_buffers"

Vi mätte med pgbench, som följer med PostgreSQL, på en testdatabas på 1,5 GB: 16 klienter i 60 sekunder, med läsning och skrivning och med bara läsning.

sudo -u postgres createdb bench
sudo -u postgres pgbench -i -s 100 bench
sudo -u postgres pgbench -c 16 -j 2 -T 60 bench
sudo -u postgres pgbench -S -c 16 -j 2 -T 60 bench
sudo -u postgres dropdb bench

Det är 5 % och 7 % mer. Testdatabasen får plats i minnet i båda fallen, så skillnaden är liten här. Kör varje test två gånger: vår första körning med skrivning direkt efter att testdata lästs in gav bara 646 tps med standardinställningarna, eftersom servern fortfarande skrev inläsningen till disk.

4. Välj hur apparna ansluter

Den säkraste databasen är en som ingen annan kan nå. Välj ett av två sätt:

• Private (tunnel), rekommenderas: port 5432 förblir stängd. Appservrar ansluter via WireGuard, och din egen dator via WireGuard eller en SSH-tunnel. Ingenting om databasen syns från internet.
• Public with TLS: port 5432 är öppen, men bara för adresserna du listar, bara med TLS och ett certifikat från Let's Encrypt som klienten kontrollerar, och bara med lösenord. Använd det när klienten inte kan köra WireGuard, till exempel en hostad appplattform med fasta utgående adresser.

5. Släpp in dina appar

listen_addresses = 'localhost,10.8.0.1'

[Unit]
After=wg-quick@wg0.service
Wants=wg-quick@wg0.service

host    appdb           appuser         10.8.0.2/32             scram-sha-256

ufw allow in on wg0 to any port 5432 proto tcp
systemctl daemon-reload
systemctl restart postgresql@18-main
ss -tlnp | grep 5432

LISTEN 0      200         10.8.0.1:5432      0.0.0.0:*    users:(("postgres",pid=15963,fd=8))
LISTEN 0      200        127.0.0.1:5432      0.0.0.0:*    users:(("postgres",pid=15963,fd=7))
LISTEN 0      200            [::1]:5432         [::]:*    users:(("postgres",pid=15963,fd=6))

psql "host=10.8.0.1 dbname=appdb user=appuser" -c "select current_user, inet_client_addr()"

 current_user | inet_client_addr
--------------+------------------
 appuser      | 10.8.0.2

ssh -N -L 5433:localhost:5432 root@203.0.113.10

psql "host=localhost port=5433 dbname=appdb user=appuser"

apt install -y certbot
ufw allow 80/tcp

#!/bin/sh
# Give PostgreSQL its own copy of the certificate, then reload it
set -e
dir=/etc/postgresql/ssl
install -d -m 750 -o root -g postgres "$dir"
install -m 644 -o root -g postgres "$RENEWED_LINEAGE/fullchain.pem" "$dir/server.crt"
install -m 640 -o root -g postgres "$RENEWED_LINEAGE/privkey.pem" "$dir/server.key"
systemctl reload postgresql

chmod 755 /usr/local/sbin/postgresql-cert-hook
certbot certonly --standalone -d pg.example.com --email you@example.com --agree-tos -n \
  --deploy-hook /usr/local/sbin/postgresql-cert-hook
ls -l /etc/postgresql/ssl

-rw-r--r-- 1 root postgres 4812 Oct  2 19:10 server.crt
-rw-r----- 1 root postgres  241 Oct  2 19:10 server.key

ssl_cert_file = '/etc/postgresql/ssl/server.crt'
ssl_key_file = '/etc/postgresql/ssl/server.key'

listen_addresses = '*'

hostssl appdb           appuser         198.51.100.7/32         scram-sha-256

ufw allow from 198.51.100.7 to any port 5432 proto tcp
systemctl restart postgresql@18-main

psql "host=pg.example.com dbname=appdb user=appuser sslmode=verify-full sslrootcert=system" \
  -c "select ssl, version from pg_stat_ssl where pid = pg_backend_pid()"

 ssl | version
-----+---------
 t   | TLSv1.3

certbot renew --force-renewal

6. Kontrollera vad andra ser

psql "host=203.0.113.10 dbname=appdb user=appuser connect_timeout=10"

psql: error: connection to server at "203.0.113.10", port 5432 failed: timeout expired

FATAL:  no pg_hba.conf entry for host "10.8.0.2", user "appuser", database "shopdb", SSL encryption

psql "host=pg.example.com dbname=appdb user=appuser sslmode=disable"

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

FATAL:  password authentication failed for user "appuser"

server certificate for "pg.example.com" (and 1 other name) does not match host name "203.0.113.10"

psql: error: connection to server at "203.0.113.10", port 5432 failed: timeout expired

7. Ta backup varje natt

pg_dump skriver en konsekvent kopia av en databas medan apparna fortsätter att använda den. pg_dumpall --globals-only lägger till inloggningarna och deras lösenordshashar, som databasdumparna inte tar med. Skapa /usr/local/sbin/pg-backup:

#!/bin/sh
# Dump the roles and every database, and keep 14 days of dumps
set -e
umask 077
dir=/var/backups/postgresql
day=$(date +%F)
pg_dumpall --globals-only > "$dir/globals-$day.sql"
for db in $(psql -Atc 'SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate'); do
    pg_dump --format=custom --file="$dir/$db-$day.dump" "$db"
done
find "$dir" -type f -mtime +13 -delete

chmod 755 /usr/local/sbin/pg-backup
install -d -m 700 -o postgres -g postgres /var/backups/postgresql

Bara postgres och root kan läsa dumparna, eftersom de innehåller all din data och lösenordshasharna. Kör skriptet som användaren postgres varje natt med en systemd-tjänst och en timer. Skapa /etc/systemd/system/pg-backup.service:

[Unit]
Description=Dump PostgreSQL databases

[Service]
Type=oneshot
User=postgres
ExecStart=/usr/local/sbin/pg-backup

Och /etc/systemd/system/pg-backup.timer:

[Unit]
Description=Dump PostgreSQL databases every night

[Timer]
OnCalendar=*-*-* 02:30
Persistent=true

[Install]
WantedBy=timers.target

Persistent=true kör en missad backup när servern är igång igen, om den var avstängd klockan 02.30. Slå på timern och kör backupen en gång nu:

systemctl daemon-reload
systemctl enable --now pg-backup.timer
systemctl start pg-backup.service
ls -l /var/backups/postgresql

-rw------- 1 postgres postgres 7887 Oct  2 19:15 appdb-2026-10-02.dump
-rw------- 1 postgres postgres 1062 Oct  2 19:15 globals-2026-10-02.sql
-rw------- 1 postgres postgres 1106 Oct  2 19:15 postgres-2026-10-02.dump
-rw------- 1 postgres postgres 1075 Oct  2 19:15 shopdb-2026-10-02.dump

Testa en återställning. En backup du aldrig har återställt är bara en förhoppning. Återställ i en ny databas, bredvid den riktiga:

sudo -u postgres createdb --owner=appuser appdb_restore
sudo -u postgres pg_restore --dbname=appdb_restore --no-owner --role=appuser \
  /var/backups/postgresql/appdb-2026-10-02.dump
sudo -u postgres psql -d appdb_restore -c "select count(*) from notes"
sudo -u postgres dropdb appdb_restore

Räkna rader i en av dina egna tabeller. Vår testtabell, notes, kom tillbaka med alla sina 1 000 rader, ägd av appuser. För att bygga upp en hel server igen kör du först filen globals med psql, så att inloggningarna finns, och återställer sedan varje databas.

Dumparna ligger fortfarande på samma server. Kopiera /var/backups/postgresql till en annan plats varje natt, till exempel med restic.

En nattlig dump kan förlora upp till ett dygns ändringar. PostgreSQL kan också arkivera sin write-ahead-logg löpande, vilket låter dig återställa till vilken tidpunkt som helst, med verktyg som pgBackRest. Det har vi inte testat här.

8. Uppdatera PostgreSQL

Mindre versioner, som 18.6 till 18.7, rättar buggar och säkerhetsproblem och ändrar ingenting i datafilerna. De installeras med apt upgrade, eller av sig själva om du ställde in det i steg 1. Paketet startar om PostgreSQL. 18.6 var den senaste versionen när vi testade, så vi har inte kört någon sådan uppdatering.

Huvudversioner kommer en gång om året, och var och en får stöd i fem år: 18 till november 2030. En ny huvudversion ändrar dataformatet, så data måste flyttas till ett nytt kluster. Debians pg_upgradecluster gör det, och behåller det gamla klustret tills du tar bort det. Vi testade från 17 till 18, med samma steg som du använder från 18 till 19.

Ta en backup först, och installera sedan den nya versionen:

systemctl start pg-backup.service
apt install -y postgresql-19
pg_lsclusters

På vår server skapades inget tomt kluster när en andra version installerades. Visar pg_lsclusters ändå ett 19 main tar du först bort det tomma klustret med pg_dropcluster 19 main --stop. Uppgradera sedan:

pg_upgradecluster 18 main
pg_lsclusters

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

Som standard kopieras data med en dump och en återställning, så databasen är nere medan det pågår: 14 sekunder för vårt lilla testkluster, längre för ditt. Det nya klustret tar över port 5432, och det gamla stoppas på en annan port.

Det nya klustret får din postgresql.conf och pg_hba.conf, men inte filerna i conf.d: i vårt test startade det nya klustret med shared_buffers tillbaka på 128 MB. Kopiera över dem och starta om:

cp /etc/postgresql/18/main/conf.d/*.conf /etc/postgresql/19/main/conf.d/
systemctl restart postgresql@19-main
sudo -u postgres psql -c "SHOW shared_buffers"

Drop-in-filen för WireGuard, certifikatets hook och backupskriptet fungerar som de är för alla versioner. När dina appar fungerar tar du bort det gamla klustret och dess paket:

pg_dropcluster 18 main
apt purge -y postgresql-18 postgresql-client-18

Felsökning

no pg_hba.conf entry for host "...", user "appuser", database "appdb", no encryption. Klienten anslöt utan TLS, och raden för den är hostssl. Lägg till sslmode=verify-full sslrootcert=system i dess anslutningsinställningar.

no pg_hba.conf entry for host "...", ... SSL encryption. Det finns ingen rad för den kombinationen av adress, inloggning och databas. Kontrollera alla tre i pg_hba.conf, och ladda om med systemctl reload postgresql när du har ändrat den.

permission denied for database "appdb" med User does not have CONNECT privilege. Inloggningen äger inte databasen, och steg 2 tog bort rätten att ansluta för alla andra. Ska den ha åtkomst kör du GRANT CONNECT ON DATABASE appdb TO otheruser som postgres.

timeout expired, eller klienten väntar i flera minuter. ufw kastar anslutningen. Kontrollera att klientens adress, IPv4 eller IPv6, har en egen regel med ufw allow from, eller att den kommer via tunneln.

server certificate for "pg.example.com" ... does not match host name. Klienten ansluter med IP-adressen. Använd namnet som certifikatet utfärdades för.

Inställningarna är tillbaka på standardvärdena efter en uppgradering av huvudversion. pg_upgradecluster kopierar inte conf.d. Kopiera filerna som i steg 8.

WireGuard-enheter får Connection refused efter en omstart, och loggen visar could not create listen socket for "10.8.0.1". PostgreSQL startade före WireGuard. Lägg till drop-in-filen från steg 5 och kör systemctl daemon-reload och systemctl restart postgresql@18-main.

sudo: unable to resolve host. Serverns eget namn saknas i /etc/hosts. Kommandot körs ändå. Lägg till namnet, som hostname visar det, på raden 127.0.1.1 i /etc/hosts för att bli av med meddelandet.