Fiche mémo PostgreSQL

Architecture rapide

Postmaster → backends (un par session) → shared memory (shared buffers, WAL buffers) + processus auxiliaires (checkpointer, background writer, WAL writer, autovacuum).

WAL : journal écrit en premier → données écrites ensuite → récupération sur crash.

Configuration clés

shared_buffers = 25% RAM (serveur dédié) ou moins. Nécessite redémarrage.

work_mem = 4 MB par opération de tri/hash (multiplicatif avec concurrence).

maintenance_work_mem = 64 MB (VACUUM, CREATE INDEX). Pas multiplicatif (un seul à la fois).

effective_cache_size = RAM totale (utilisé par planificateur, non alloué).

autovacuum = toujours on.

ALTER SYSTEM SET shared_buffers = '512MB';
SELECT pg_reload_conf();
-- Les paramètres non-SUPER USER changent immédiatement pour les nouvelles sessions.
-- shared_buffers nécessite un restart.

Authentification (pg_hba.conf)

Format : type | database | user | address | method

local   all             all                                     trust
host    postgres        postgres        127.0.0.1/32            scram-sha-256
host    all             app             192.0.2.0/24            scram-sha-256

Première ligne correspondante gagne. Ordre important.

Méthodes : trust (dangereux), scram-sha-256 (standard), md5 (deprecated), password (non sécurisé), peer (Unix socket local), cert (SSL).

Rechargement :

SELECT pg_reload_conf();

Rôles et privilèges

CREATE ROLE app_user LOGIN PASSWORD 'motdepasse-fort';
CREATE ROLE app_group NOLOGIN;
GRANT app_group TO app_user;

GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.mytable TO app_user;
REVOKE ALL ON public.mytable FROM PUBLIC;

PG 15+ : utilisateurs normaux n'ont plus CREATE sur public par défaut. Créer des schémas per-user.

Maintenance

VACUUM my_table;                    -- nettoie versions mortes
ANALYZE my_table;                   -- met à jour stats
VACUUM ANALYZE my_table;            -- les deux

-- Vérifier wraparound XID
SELECT datname, age(datfrozenxid) FROM pg_database;  -- < 50M = safe

VACUUM FULL : lent, verrou exclusif. À éviter.

Autovacuum : toujours actif.

Index

CREATE INDEX idx_nom ON clients(nom);        -- B-tree (défaut)
CREATE INDEX idx_location ON geo USING GIN(location);  -- GIN

Types : B-tree (standard), Hash (=), GiST (géométrie), GIN (arrays/JSON), BRIN (très compact).

EXPLAIN / Plans de requête

EXPLAIN SELECT * FROM clients WHERE id = 5;
-- Affiche plan estimé : coût, rows estimées, type scan

EXPLAIN ANALYZE SELECT * FROM clients WHERE id = 5;
-- Affiche AUSSI temps réel, rows réelles
-- ATTENTION : exécute réellement la requête
-- Pour INSERT/UPDATE/DELETE : encadrer par ROLLBACK
BEGIN;
EXPLAIN ANALYZE INSERT INTO audit (msg) VALUES ('test');
ROLLBACK;

Lire : Seq Scan (lent), Index Scan (rapide si index bon).

Transactions et isolation

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
  -- snapshot pris ici
  SELECT * FROM clients;
  -- autres SELECT voient le même snapshot
COMMIT;

Niveaux : Read Committed (défaut), Repeatable Read (snapshot), Serializable (sériel strict).

Erreur 40001 (serialization conflict) → retry.

Sauvegarde et restauration

pg_dump (logique, SQL)

Attention, effet notable. Cette commande peut écrire ou écraser un fichier. Lire le contexte, les droits et l’effet attendu indiqués après le bloc ; ne jamais l’exécuter sur un système réel sans validation, d’abord dans un environnement de test.

pg_dump -Fc -f mydb.dump mydbpg_dump -s mydb > schema_only.sql  # schéma seulementpg_dump -a -t mytable mydb         # données table

Formats : -Fc (custom, compressé), -Ft (tar), -Fp (SQL texte).

pg_restore

pg_restore -d newdb -C --clean mydb.dumppg_restore -l mydb.dump | grep TABLE        # lister

• Contexte : dump en format custom -Fc ; base cible newdb existe ou sera créée.
• Droits : utilisateur postgres ou rôle avec droits CREATE.
• Effet : restaure les données et schéma depuis mydb.dump dans newdb ; -C --clean crée la base et nettoie avant.
• Vérification : psql -U postgres -d newdb -c '\dt' liste les tables restaurées.
• Retour arrière : DROP DATABASE newdb; pour nettoyer.

pg_basebackup (physique, pour PITR)

pg_basebackup -D /backup -Ft -X stream

• Contexte : PostgreSQL tourne, répertoire /backup vide ou inexistant.
• Droits : utilisateur postgres ou rôle avec droit REPLICATION ; sudo pour /backup.
• Effet : copie les fichiers de données et WAL vers /backup en format tar ; -X stream inclut les WAL en flux.
• Vérification : ls -la /backup montre les fichiers de sauvegarde, tar -tf /backup/base.tar.gz | head énumère.
• Retour arrière : rm -r /backup efface la sauvegarde (conserver l'original d'abord).

Prérequis : rôle REPLICATION, max_wal_senders >= 2.

PITR (Point-In-Time Recovery)

postgresql.conf :
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /backup/wal/%f && cp %p /backup/wal/%f'

Restauration à moment précis :

Attention, effet notable. Cette commande a un effet notable. Lire le contexte, les droits et l’effet attendu indiqués après le bloc ; ne jamais l’exécuter sur un système réel sans validation, d’abord dans un environnement de test.

tar -xzf /backup/full.tar.gz -C /var/lib/postgresql/data# Dans postgresql.conf :#   restore_command = 'cp /backup/wal/%f %p'#   recovery_target_time = '2026-10-18 14:30:00'touch /var/lib/postgresql/data/recovery.signalpg_ctl start

• Contexte : dossier de sauvegarde, postgresql.conf à éditer, système arrêté.
• Droits : utilisateur postgres pour tous les fichiers ; sudo pour pg_ctl.
• Effet : PostgreSQL démarre en mode récupération, relit les WAL jusqu'au moment cible.
• Vérification : psql -U postgres conecte à la nouvelle base de temps récupérée.
• Retour arrière : restaurer à partir de la sauvegarde précédente ; conserver le dossier de données nouveau à titre d'archive si la récupération n'est pas satisfaisante.

Réplication (principes)

Streaming : WAL continu Primaire → Standby.

synchronous_commit = local      # défaut (rapide)
synchronous_commit = remote_apply  # attendre standby
synchronous_standby_names = '*'

Async vs Sync : async = rapide + risque perte ; sync = sûr + lent.

Promotion standby :

SELECT pg_promote();

Failover automatique : outils externes (Patroni, etcd).

psql courant

psql -h localhost -p 5432 -U postgres -d mydb

• Contexte : PostgreSQL tourne ; accès réseau vers le serveur.
• Droits : aucun droit système ; accès au rôle postgres ou rôle nommé.
• Effet : ouvre un interpréteur SQL connecté à la base mydb sur localhost:5432.
• Vérification : affiche le prompt mydb=# ou mydb=>.
• Retour arrière : \q quitte la session.

Méta-commandes :

\l                    -- bases
\c mydb               -- connecter
\dt                   -- tables
\d mytable            -- structure
\d mytable+           -- détails
\dn                   -- schémas
\du                   -- rôles
\di                   -- index
\df                   -- fonctions
\dx                   -- extensions
\timing               -- temps d'exécution
\x                    -- mode étendu
\watch 5              -- relancer toutes 5s
\q                    -- quitter

Avant de configurer

1. Matériel (RAM, disques, CPU) ?
2. Charge (OLTP = transactions, OLAP = gros scans) ?
3. RTO / RPO requis (temps de récupération / perte acceptable) ?
4. Sauvegarde : pg_dump seule ? pg_basebackup + WAL ?
5. Réplication : asynchrone (rapide) ou synchrone (sûr) ?
6. Monitoring : quoi alerter ?