Erreurs courantes de PostgreSQL et solutions avec exemples pratiques

Introduction

PostgreSQL est un système de gestion de base de données robuste et riche en fonctionnalités, mais comme tout système complexe, il génère des erreurs qui peuvent frustrer les développeurs et les opérateurs. Beaucoup d'erreurs ont des causes simples et des correctifs bien définis, mais sans approche systématique, elles peuvent consommer des heures d'investigation. Ce guide aborde les erreurs les plus courantes de PostgreSQL, explique pourquoi elles surviennent et fournit des correctifs pratiques, étape par étape, avec des commandes et des sorties attendues. Que vous soyez un développeur confronté à un message cryptique dans votre application ou un opérateur résolvant un problème en production, cet article vous aidera à diagnostiquer et à résoudre efficacement les problèmes. Nous couvrons les échecs de connexion et d'authentification, les erreurs SQL et de schéma, l'épuisement des ressources et la corruption des données, ainsi que les techniques de vérification et les stratégies de restauration.

Inventaire de la version et de l'environnement

Avant de plonger dans des erreurs spécifiques, établissez une image claire de votre environnement PostgreSQL. Connaître les versions exactes et la configuration évite les erreurs de diagnostic, car le comportement peut varier selon les versions.

Vérifier la version de PostgreSQL

Exécutez la commande suivante pour voir la version du serveur :

psql -U postgres -c 'SELECT version();'

Sortie attendue (exemple) :

version
-----------------------------------------------------------
 PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc...

Vérifier les fichiers de configuration

Localisez le fichier de configuration principal, postgresql.conf, et le fichier d'authentification client, pg_hba.conf. Généralement, ils se trouvent dans le répertoire de données (/var/lib/postgresql/data/ sur Debian/Ubuntu ou /var/lib/pgsql/data/ sur RHEL/CentOS). Vous pouvez trouver leur emplacement exact avec :

SHOW config_file;
SHOW hba_file;

Exemple de sortie :

config_file
------------------------------------------
 /etc/postgresql/14/main/postgresql.conf
(1 row)

 hba_file
-------------------------------------
 /etc/postgresql/14/main/pg_hba.conf
(1 row)

Vérifier la connectivité

Assurez-vous de pouvoir vous connecter localement avant de tenter des correctifs à distance :

psql -U postgres -h localhost -c 'SELECT 1;'

Si cela échoue, votre problème peut être lié au réseau ou à l'authentification, ce que nous couvrons ensuite.

Erreurs de connexion et d'authentification

Les problèmes de connexion sont parmi les plus fréquents. Ils se manifestent généralement par connection refused, no pg_hba.conf entry, ou password authentication failed. Nous abordons chacun avec les causes et les correctifs.

Cas 1 : « Connection refused »

Symptôme :

psql: error: could not connect to server: Connection refused
       Is the server running on host "localhost" and accepting
       TCP/IP connections on port 5432?

Cause : Le serveur PostgreSQL n'est pas en cours d'exécution, ou il n'écoute pas sur l'interface/le port attendu.

Diagnostic :

1. Vérifiez si le processus serveur est actif :

pg_isready

Exemple de sortie si le serveur est arrêté :

/tmp:5432 - no response

1. Vérifiez si le port est ouvert :

ss -tlnp | grep 5432

Aucune sortie signifie que rien n'est en écoute.

Correctif :

Démarrez le serveur en utilisant la commande de service appropriée :

sudo systemctl start postgresql

Ou, si vous utilisez pg_ctl :

pg_ctl -D /var/lib/postgresql/data start

Vérifiez à nouveau avec pg_isready ; vous devriez voir :

/tmp:5432 - accepting connections

Si le serveur ne démarre pas, consultez le fichier journal (généralement dans /var/log/postgresql/ ou le répertoire de données) pour les erreurs.

Cas 2 : « no pg_hba.conf entry for host »

Symptôme :

FATAL: no pg_hba.conf entry for host "192.168.1.100", user "appuser", database "appdb", SSL off

Cause : L'adresse IP du client, l'utilisateur ou la base de données n'est pas autorisé par les règles d'authentification dans pg_hba.conf.

Correctif :

Modifiez pg_hba.conf pour ajouter une entrée appropriée. Par exemple, pour autoriser l'authentification par mot de passe pour un utilisateur spécifique à partir d'un sous-réseau spécifique :

# TYPE  DATABASE USER     ADDRESS        METHOD
host    appdb    appuser  192.168.1.0/24 scram-sha-256

Après modification, rechargez la configuration :

sudo systemctl reload postgresql

Ou, en tant que superutilisateur :

SELECT pg_reload_conf();

Puis testez à nouveau la connexion.

Cas 3 : « password authentication failed »

Symptôme :

FATAL: password authentication failed for user "appuser"

Cause : Mot de passe incorrect, mauvaise méthode d'authentification, ou le rôle n'existe pas.

Diagnostic :

Tout d'abord, vérifiez que le rôle existe :

SELECT rolname FROM pg_roles WHERE rolname='appuser';

Si aucune ligne n'est retournée, créez le rôle :

CREATE ROLE appuser LOGIN PASSWORD 'strongpassword';

Si le rôle existe, réinitialisez le mot de passe :

ALTER ROLE appuser WITH PASSWORD 'newstrongpassword';

Assurez-vous également que la méthode d'authentification dans pg_hba.conf correspond au chiffrement du mot de passe. Par exemple, si le mot de passe a été défini avec md5 mais que la méthode est scram-sha-256, vous devrez peut-être réinitialiser le mot de passe avec la nouvelle méthode ou ajuster le paramètre password_encryption. La valeur par défaut dans PostgreSQL 14+ est scram-sha-256.

Après modifications, rechargez et testez.

Cas 4 : « too many connections »

Symptôme :

FATAL: sorry, too many clients already

Cause : Le nombre de connexions simultanées a atteint max_connections.

Correctif :

Tout d'abord, vérifiez les connexions actuelles et la limite :

SHOW max_connections;
SELECT count(*) FROM pg_stat_activity;

Exemple :

max_connections
-----------------
 100
(1 row)

 count
-------
   100
(1 row)

Si le nombre est à la limite, vous pouvez soit augmenter max_connections (nécessite un redémarrage), soit, mieux, implémenter un pool de connexions. Pour augmenter, modifiez postgresql.conf :

max_connections = 200

Puis redémarrez :

sudo systemctl restart postgresql

Mais soyez prudent : augmenter max_connections consomme plus de mémoire. Envisagez d'utiliser un pooler comme PgBouncer.

Erreurs SQL et de schéma

Ces erreurs surviennent pendant l'exécution de la requête et indiquent souvent des fautes de frappe, des objets manquants ou des problèmes de permissions.

Cas 1 : « relation does not exist »

Symptôme :

ERROR: relation "users" does not exist
LINE 1: SELECT * FROM users;

Cause : La table n'est pas visible dans le schéma courant, ou le nom de la table est mal orthographié, ou l'utilisateur n'a pas les privilèges.

Correctif :

Vérifiez le chemin de recherche actuel :

SHOW search_path;

Exemple de sortie :

   search_path
-----------------
 "$user", public
(1 row)

Si votre table se trouve dans un schéma non par défaut, qualifiez soit le nom de la table (myschema.users), soit ajoutez le schéma à search_path :

SET search_path TO myschema, public;

Pour le rendre permanent pour un utilisateur :

ALTER ROLE appuser SET search_path TO myschema, public;

Vérifiez également que la table existe :

SELECT * FROM information_schema.tables WHERE table_name='users';

Cas 2 : « column does not exist »

Symptôme :

ERROR: column "emial" does not exist

Cause : Faute de frappe dans le nom de colonne ou colonne non créée.

Correctif :

Listez les colonnes de la table :

\d users

Ou :

SELECT column_name FROM information_schema.columns WHERE table_name='users';

Corrigez la requête ou ajoutez la colonne manquante avec ALTER TABLE ... ADD COLUMN.

Cas 3 : « syntax error at or near »

Symptôme :

ERROR: syntax error at or near "FROM"
LINE 2: FROM users;

Cause : La déclaration SQL est mal formée, souvent un mot-clé manquant ou une virgule mal placée.

Correctif :

Examinez attentivement la requête. Utilisez un client SQL avec coloration syntaxique pour repérer les problèmes. Pour les requêtes complexes, divisez-les en parties plus petites et testez incrémentalement.

Cas 4 : « permission denied »

Symptôme :

ERROR: permission denied for table users

Cause : Le rôle exécutant la requête ne possède pas les privilèges nécessaires.

Correctif :

Accordez les permissions requises. Par exemple, pour accorder SELECT sur une table :

GRANT SELECT ON users TO appuser;

Pour toutes les tables d'un schéma :

GRANT SELECT ON ALL TABLES IN SCHEMA public TO appuser;

Vérifiez avec :

SELECT * FROM information_schema.role_table_grants WHERE grantee='appuser' AND table_name='users';

Erreurs de ressources et de performance

L'épuisement des ressources peut paralyser une base de données. Les erreurs courantes incluent la mémoire partagée insuffisante, l'espace disque insuffisant et les délais de verrouillage.

Cas 1 : « out of shared memory »

Symptôme :

ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.

Cause : La mémoire partagée allouée au suivi des verrous est épuisée. Cela peut se produire avec des transactions volumineuses qui touchent de nombreux objets.

Correctif :

Augmentez max_locks_per_transaction dans postgresql.conf. La valeur par défaut est 64. Une valeur plus élevée peut nécessiter un redémarrage. Par exemple :

max_locks_per_transaction = 128

Après modification, redémarrez PostgreSQL :

sudo systemctl restart postgresql

Pour surveiller l'utilisation des verrous :

SELECT count(*) FROM pg_locks;

Cas 2 : « canceling statement due to statement timeout »

Symptôme :

ERROR: canceling statement due to statement timeout

Cause : La déclaration a dépassé le paramètre statement_timeout.

Correctif :

Optimisez la requête pour qu'elle soit plus rapide ou augmentez le délai. Pour voir le paramètre actuel :

SHOW statement_timeout;

Pour augmenter pour la session :

SET statement_timeout = '5min';

Pour définir globalement dans postgresql.conf :

statement_timeout = '2min'

Puis rechargez :

sudo systemctl reload postgresql

Cas 3 : « deadlock detected »

Symptôme :

ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 678; blocked by process 12346.

Cause : Deux transactions ou plus s'attendent mutuellement, créant un cycle.

Correctif :

PostgreSQL résout automatiquement les interblocages en annulant l'une des transactions. L'application doit intercepter l'erreur et réessayer. Pour réduire les interblocages, assurez un ordre de verrouillage cohérent dans les transactions et gardez les transactions courtes.

Cas 4 : « could not write to file ... No space left on device »

Symptôme :

ERROR: could not write to file "pg_wal/...": No space left on device

Cause : Disque plein, probablement en raison de fichiers WAL volumineux, de journaux ou de croissance des données.

Correctif :

Vérifiez l'utilisation du disque :

df -h /var/lib/postgresql/data

Exemple de sortie :

Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1       100G  100G   0G  100% /var/lib/postgresql/data

Libérez de l'espace en supprimant les anciennes sauvegardes, en archivant les WAL ou en étendant le volume. En cas d'urgence, vous pouvez supprimer les anciens fichiers WAL uniquement si vous êtes sûr qu'ils ne sont pas nécessaires pour la réplication ou la récupération à un instant donné. Ne supprimez pas manuellement les fichiers sauf si nécessaire ; utilisez pg_archivecleanup ou configurez correctement archive_command.

Corruption des données et récupération

La corruption des données est rare mais grave. Elle peut être causée par des pannes matérielles, des arrêts incorrects ou des problèmes de système de fichiers.

Cas 1 : « invalid page in block »

Symptôme :

ERROR: invalid page in block 12345 of relation "users"

Cause : Une page de données est corrompue.

Diagnostic :

Exécutez une vérification de cohérence sur la table :

pg_dump -t users dbname > /tmp/users.sql

Si pg_dump échoue avec une erreur de corruption similaire, vous devrez peut-être restaurer à partir d'une sauvegarde.

Correctif :

Restaurez la table à partir d'une sauvegarde récente si disponible. Sinon, vous pouvez tenter de sauter les blocs corrompus en utilisant zero_damaged_pages (risqué ; les données de ces blocs sont perdues) :

# Dans postgresql.conf (temporairement, après sauvegarde)
zero_damaged_pages = on

Puis redémarrez et accédez à la table pour marquer les pages endommagées comme nulles. Sauvegardez immédiatement ce que vous pouvez puis restaurez à partir d'une source propre si possible.

Cas 2 : « could not read block ... read-only file system »

Symptôme :

ERROR: could not read block 12345 in file "base/...": Input/output error

Cause : Panne de stockage sous-jacente.

Correctif :

Consultez les journaux système (dmesg) pour les erreurs de disque. Si le disque est défaillant, remplacez-le et restaurez à partir de la sauvegarde. Assurez-vous d'avoir testé régulièrement les sauvegardes.

Vérification et diagnostics

Après avoir appliqué les correctifs, vérifiez que le problème est résolu et surveillez les régressions.

Vérifier l'état du serveur

Utilisez pg_isready comme vérification rapide :

pg_isready -h localhost -p 5432

Attendu : localhost:5432 - accepting connections

Tester l'exécution de la requête

Exécutez la requête qui échouait précédemment. Pour les problèmes de connexion, connectez-vous :

psql -U appuser -d appdb -c 'SELECT 1;'

Surveiller les journaux

Suivez le journal PostgreSQL pendant la reproduction :

tail -f /var/log/postgresql/postgresql-14-main.log

Recherchez les lignes ERROR ou FATAL.

Vérifier les sessions actives et les verrous

SELECT pid, state, query FROM pg_stat_activity WHERE state <> 'idle';

Pour les attentes de verrous :

SELECT * FROM pg_locks WHERE NOT granted;

Vérifier les changements de configuration

Après avoir modifié postgresql.conf, confirmez que le paramètre a pris effet :

SHOW max_connections;

Modes d'échec et récupération

Même avec des correctifs soignés, les choses peuvent mal tourner. Préparez des stratégies de restauration pour chaque changement.

Changements de configuration

Sauvegardez toujours les fichiers de configuration avant de les modifier :

sudo cp postgresql.conf postgresql.conf.bak
sudo cp pg_hba.conf pg_hba.conf.bak

Si un changement cause des problèmes, restaurez la sauvegarde et rechargez ou redémarrez.

Altérations de base de données

Pour les changements de schéma, utilisez le DDL transactionnel lorsque possible :

BEGIN;
ALTER TABLE users ADD COLUMN email TEXT;
-- vérifier
COMMIT;

Si une erreur survient, ROLLBACK annule les changements de manière atomique.

Restauration à partir de sauvegarde

Testez régulièrement les sauvegardes. Une procédure de récupération fiable est :

• Arrêtez la base de données (si possible).
• Restaurez la sauvegarde en utilisant pg_restore ou psql.
• Rejouez les archives WAL si vous utilisez la récupération à un instant donné.
• Vérifiez l'intégrité des données puis redémarrez.

Exemple de restauration à partir d'un dump au format personnalisé :

pg_restore -U postgres -d targetdb /backups/db.dump

Pièges et erreurs courants

Évitez ces erreurs courantes lors du dépannage de PostgreSQL.

1. Faire des changements de configuration sans sauvegarde. Gardez toujours une copie de postgresql.conf et pg_hba.conf avant de les modifier. Une erreur de syntaxe peut empêcher le serveur de démarrer. Par exemple, si vous ajoutez accidentellement une ligne vide dans un bloc de paramètres, PostgreSQL échouera à analyser le fichier et refusera de démarrer. Gardez une sauvegarde et testez les changements dans un environnement de préproduction d'abord.

1. Ignorer les fichiers journaux. Les journaux PostgreSQL contiennent souvent la cause exacte d'une erreur. Par exemple, si l'authentification échoue, le journal peut montrer l'IP du client, l'utilisateur et la base de données, ce qui aide à identifier une entrée pg_hba.conf manquante. Vérifiez toujours les journaux avant de deviner.

1. Compter sur max_connections au lieu du pooling. Augmenter max_connections peut entraîner des erreurs OOM en raison de l'utilisation de la mémoire par connexion. Par exemple, définir max_connections à 500 sur un serveur de 2 Go de RAM peut épuiser la mémoire. Utilisez un pooler de connexions comme PgBouncer pour multiplexer les connexions et garder max_connections bas (par exemple, 100).

1. Supprimer manuellement les fichiers WAL. Ne supprimez jamais les fichiers WAL sans comprendre les implications pour la réplication et la sauvegarde. Cela peut casser la réplication en continu et la récupération à un instant donné. Utilisez toujours pg_archivecleanup ou ajustez les politiques de gestion des archives.

1. Ne pas tester les sauvegardes. Une sauvegarde qui n'a jamais été restaurée n'est pas une sauvegarde. Pratiquez la restauration à partir des sauvegardes régulièrement pour vous assurer qu'elles sont valides et réduire le temps de récupération. Par exemple, une erreur de bloc corrompu ne peut être entièrement résolue que si vous avez une sauvegarde propre à partir de laquelle restaurer.

Liste de contrôle des opérations

Utilisez cette liste de contrôle pour rationaliser le dépannage et la maintenance de PostgreSQL.

Chaque étape doit avoir un propriétaire clair. Désignez un administrateur de base de données (DBA) ou un ingénieur de garde comme propriétaire responsable de l'exécution de la liste de contrôle. La liste de contrôle doit être examinée mensuellement pour intégrer de nouveaux incidents et améliorer les runbooks.

Conclusion

Les erreurs PostgreSQL peuvent être intimidantes, mais avec une approche méthodique, la plupart sont résolues rapidement. Ce guide a couvert les problèmes fréquents de connexion, d'authentification, de SQL, de schéma et de gestion des ressources, ainsi que les scénarios de corruption des données. Commencez toujours par inventorier votre environnement, reproduisez l'erreur, appliquez un correctif ciblé et vérifiez. Utilisez la liste de contrôle fournie pour des opérations cohérentes et gardez des plans de restauration prêts. En maîtrisant ces erreurs courantes, vous réduirez les temps d'arrêt et améliorerez la fiabilité de vos déploiements PostgreSQL.