PostgreSQL installeren en beheren op Linux

PostgreSQL installeren en beheren op Linux

Dit artikel legt uit hoe je PostgreSQL 15 installeert en beheert op Debian/Ubuntu Linux, inclusief pgvector-extensie en Docker-omgevingen, voor beheerders die werken met applicaties zoals PixelProza.

Vereisten

- Linux-server met Debian 11/12 of Ubuntu 20.04/22.04/24.04 - `sudo`-toegang tot de server - PostgreSQL 15 (specifiek voor pgvector-ondersteuning) - Optioneel: Docker en Docker Compose voor containeromgevingen - Basiskennis van de `psql`-client en SQL-commando's

Stappen

1. Installeer PostgreSQL via apt

Voer de pakketbeheerder-update uit en installeer PostgreSQL 15 met de contrib-pakketten:

sudo apt update
sudo apt install postgresql-15 postgresql-contrib-15

De contrib-pakketten bevatten extra functionaliteit zoals `pg_dump`, `pg_restore` en ondersteuning voor geavanceerde indexen en extensies. De service start automatisch na installatie en configureert zichzelf om bij het opstarten van het systeem te beginnen.

2. Open de psql-prompt

De installatie maakt standaard een systeemgebruiker `postgres` aan. Schakel over naar deze gebruiker en open de database-prompt:

sudo -i -u postgres
psql

Je bevindt je nu in de PostgreSQL-shell. Verlaat deze tijdelijk met `\q` als je eerst andere commando's wilt uitvoeren.

3. Maak een database en gebruiker aan

Maak een dedicated database en gebruiker voor je applicatie. Vervang `pixelproza` door de naam die jouw applicatie gebruikt:

CREATE USER pixelproza WITH PASSWORD 'kies_een_sterk_wachtwoord';
CREATE DATABASE pixelproza OWNER pixelproza;
GRANT ALL PRIVILEGES ON DATABASE pixelproza TO pixelproza;

Verlaat de psql-prompt met `\q` en keer terug naar je normale gebruiker met `exit`.

4. Installeer de pgvector-extensie

pgvector 0.5.0+ is vereist voor vectorzoekfunctionaliteit. Installeer het pakket:

sudo apt install postgresql-15-pgvector

Deze extensie voegt het type `vector` en indexmethodes zoals HNSW en IVFFLAT toe aan PostgreSQL 15.

5. Schakel pgvector in voor je database

Open opnieuw de psql-prompt en activeer de extensie in de database:

sudo -i -u postgres
psql -U postgres -d pixelproza

Voer dan het SQL-commando uit:

CREATE EXTENSION IF NOT EXISTS vector;

Zonder deze stap kun je geen vectorcolommen aanmaken of vectorindexen gebruiken.

6. Maak een tabel met een vector-kolom

Maak een tabel die een embedding-kolom bevat. Dit voorbeeld toont een `knowledge_items`-tabel zoals gebruikt in PixelProza:

CREATE TABLE knowledge_items (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    embedding vector(1536)
);

De vector-dimensie (1536) moet overeenkomen met de outputdimensie van je embedding-model.

7. Maak vector-indexen aan

Maak een HNSW-index voor snelle, accurate zoekopdrachten:

CREATE INDEX ix_knowledge_embedding_hnsw ON knowledge_items 
USING hnsw (embedding vector_cosine_ops) 
WITH (m=16, ef_construction=64);

Maak daarnaast een IVFFLAT-index voor grotere datasets:

CREATE INDEX idx_knowledge_embedding ON knowledge_items 
USING ivfflat (embedding vector_cosine_ops) 
WITH (lists=100);

HNSW biedt betere prestaties bij kleinere datasets; IVFFLAT schaalt beter bij miljoenen rijen.

8. Configureer netwerktoegang (optioneel)

Standaard luistert PostgreSQL alleen op localhost. Voor verbindingen vanaf andere hosts, bewerk het configuratiebestand:

sudo nano /etc/postgresql/15/main/postgresql.conf

Zoek de regel `#listen_addresses = 'localhost'` en wijzig deze naar:

listen_addresses = '*'

Bewerk daarna het authenticatiebestand:

sudo nano /etc/postgresql/15/main/pg_hba.conf

Voeg deze regel toe voor toegang vanaf je applicatieserver:

host pixelproza pixelproza 192.168.1.0/24 md5

Vervang `192.168.1.0/24` door het subnet van je applicatieservers. Start PostgreSQL opnieuw:

sudo systemctl restart postgresql

Beveiliging is hierbij cruciaal: openstaande poorten moeten worden beschermd met firewalls of netwerkisolatie.

Verificatie

Controleer of de installatie correct werkt. Maak verbinding met de database en test de vector-functionaliteit:

psql -U pixelproza -h localhost -d pixelproza

Voer dan uit:

SELECT * FROM pg_extension WHERE extname = 'vector';
SELECT * FROM knowledge_items LIMIT 1;

De eerste query moet een rij retourneren met `vector` als extensienaam. De tweede query bevestigt dat de tabel bestaat en toegankelijk is.

Veelgestelde problemen

1. Kan geen verbinding maken vanaf een andere host

Controleer of `listen_addresses` correct is ingesteld en of `pg_hba.conf` de juiste host-regel bevat. Vergeet niet om PostgreSQL opnieuw te starten na wijzigingen:

sudo systemctl restart postgresql

Test de verbinding met `psql -h -U pixelproza -d pixelproza`.

2. pgvector-extensie kan niet worden ingeschakeld

De foutmelding `extension "vector" is not available` betekent dat het pakket niet correct is geïnstalleerd. Controleer of je PostgreSQL 15 gebruikt en installeer het juiste pakket:

sudo apt install postgresql-15-pgvector

3. Vector-index wordt niet gebruikt in queries

Forceer het gebruik van de index met een expliciete hint in je query:

SET enable_seqscan = off;
SELECT * FROM knowledge_items 
ORDER BY embedding <=> '[0.1, 0.2, ...]' 
LIMIT 10;

Controleer met `EXPLAIN` of de index daadwerkelijk wordt gebruikt.

Docker-omgevingen

In een Docker-omgeving zoals PixelProza gebruik je de `pgvector/pgvector:pg15`-image. Deze bevat PostgreSQL 15 met pgvector voorgeïnstalleerd. Beheer de database via de containerlaag:

docker compose up -d
docker exec -it postgres-db psql -U pixelproza -d pixelproza

Logs bekijken:

docker compose logs -f pixelproza-api
docker compose logs -f postgres-db

Back-ups maken vanuit een container:

docker exec postgres-db pg_dump -U pixelproza -d pixelproza > backup.sql

Deze aanpak vereenvoudigt installatie en schaalbaarheid, maar vereist kennis van Docker en de `docker compose.yml`-configuratie.