Installer et gérer PostgreSQL sur Linux

Installer et gérer PostgreSQL sur Linux

Cet article explique comment installer et gérer PostgreSQL 15 sur Debian/Ubuntu Linux, y compris l'extension pgvector et les environnements Docker, pour les administrateurs qui travaillent avec des applications telles que PixelProza.

Prérequis

- Serveur Linux avec Debian 11/12 ou Ubuntu 20.04/22.04/24.04 - Accès `sudo` au serveur - PostgreSQL 15 (spécifiquement pour le support pgvector) - Optionnel : Docker et Docker Compose pour les environnements conteneurisés - Connaissances de base du client `psql` et des commandes SQL

Étapes

1. Installez PostgreSQL via apt

Exécutez la mise à jour du gestionnaire de paquets et installez PostgreSQL 15 avec les paquets contrib :

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

Les paquets contrib contiennent des fonctionnalités supplémentaires telles que `pg_dump`, `pg_restore` et le support des index et extensions avancés. Le service démarre automatiquement après l'installation et se configure pour démarrer au lancement du système.

2. Ouvrez l'invite psql

L'installation crée par défaut un utilisateur système `postgres`. Basculez vers cet utilisateur et ouvrez l'invite de base de données :

sudo -i -u postgres
psql

Vous êtes maintenant dans le shell PostgreSQL. Quittez-le temporairement avec `\q` si vous souhaitez d'abord exécuter d'autres commandes.

3. Créez une base de données et un utilisateur

Créez une base de données et un utilisateur dédiés pour votre application. Remplacez `pixelproza` par le nom utilisé par votre application :

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

Quittez l'invite psql avec `\q` et revenez à votre utilisateur normal avec `exit`.

4. Installez l'extension pgvector

pgvector 0.5.0+ est requis pour la fonctionnalité de recherche vectorielle. Installez le paquet :

sudo apt install postgresql-15-pgvector

Cette extension ajoute le type `vector` et des méthodes d'index telles que HNSW et IVFFLAT à PostgreSQL 15.

5. Activez pgvector pour votre base de données

Rouvrez l'invite psql et activez l'extension dans la base de données :

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

Exécutez ensuite la commande SQL :

CREATE EXTENSION IF NOT EXISTS vector;

Sans cette étape, vous ne pouvez pas créer de colonnes vectorielles ni utiliser d'index vectoriels.

6. Créez une table avec une colonne vectorielle

Créez une table contenant une colonne d'embedding. Cet exemple montre une table `knowledge_items` telle qu'utilisée dans PixelProza :

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

La dimension vectorielle (1536) doit correspondre à la dimension de sortie de votre modèle d'embedding.

7. Créez des index vectoriels

Créez un index HNSW pour des recherches rapides et précises :

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

Créez également un index IVFFLAT pour les ensembles de données plus volumineux :

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

HNSW offre de meilleures performances sur les ensembles de données plus petits ; IVFFLAT passe mieux à l'échelle avec des millions de lignes.

8. Configurez l'accès réseau (optionnel)

Par défaut, PostgreSQL n'écoute que sur localhost. Pour les connexions depuis d'autres hôtes, modifiez le fichier de configuration :

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

Recherchez la ligne `#listen_addresses = 'localhost'` et modifiez-la en :

listen_addresses = '*'

Modifiez ensuite le fichier d'authentification :

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

Ajoutez cette ligne pour l'accès depuis votre serveur d'applications :

host pixelproza pixelproza 192.168.1.0/24 md5

Remplacez `192.168.1.0/24` par le sous-réseau de vos serveurs d'applications. Redémarrez PostgreSQL :

sudo systemctl restart postgresql

La sécurité est cruciale ici : les ports ouverts doivent être protégés par des pare-feu ou une isolation réseau.

Vérification

Vérifiez que l'installation fonctionne correctement. Connectez-vous à la base de données et testez la fonctionnalité vectorielle :

psql -U pixelproza -h localhost -d pixelproza

Exécutez ensuite :

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

La première requête doit retourner une ligne avec `vector` comme nom d'extension. La deuxième requête confirme que la table existe et est accessible.

Problèmes fréquents

1. Impossible de se connecter depuis un autre hôte

Vérifiez que `listen_addresses` est correctement configuré et que `pg_hba.conf` contient la bonne règle d'hôte. N'oubliez pas de redémarrer PostgreSQL après les modifications :

sudo systemctl restart postgresql

Testez la connexion avec `psql -h -U pixelproza -d pixelproza`.

2. L'extension pgvector ne peut pas être activée

Le message d'erreur `extension "vector" is not available` signifie que le paquet n'est pas correctement installé. Vérifiez que vous utilisez PostgreSQL 15 et installez le paquet approprié :

sudo apt install postgresql-15-pgvector

3. L'index vectoriel n'est pas utilisé dans les requêtes

Forcez l'utilisation de l'index avec une indication explicite dans votre requête :

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

Vérifiez avec `EXPLAIN` que l'index est réellement utilisé.

Environnements Docker

Dans un environnement Docker comme PixelProza, vous utilisez l'image `pgvector/pgvector:pg15`. Celle-ci contient PostgreSQL 15 avec pgvector préinstallé. Gérez la base de données via la couche conteneur :

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

Consulter les journaux :

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

Faire des sauvegardes depuis un conteneur :

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

Cette approche simplifie l'installation et la scalabilité, mais nécessite une connaissance de Docker et de la configuration `docker compose.yml`.