Blog de Norore
Geek en perdition dans un monde qui va trop vite

Restaurer des entrées dans une base de données PostgreSQL

25 Oct 2025

Vous est-il déjà arrivé que, malgré toutes les précautions et les garde-fous que vous ayez mis en place pour l’éviter, par un malheureux concours de circonstance, vous avez supprimé une donnée qui n’aurait pas dû l’être, dans votre base de données ? Si oui, une simple restauration de sauvegarde permet de corriger le problème. Mais qu’en est-il d’une base de données en production, avec une erreur de manipulation en fin de journée, et une sauvegarde remontant à la nuit précédente ?

Ayant été confronté-e à ce problème, et ayant dû chercher comment restaurer des données importantes sans devoir obliger tout un service à devoir ressaisir leurs entrées, je vous propose de trouver ici la procédure que j’ai mise en place et partagée avec mes collègues, en espérant que cela puisse dépanner quelqu’un dans la panique.

Récupération de la dernière sauvegarde

Dans un premier temps, récupérez une version récente de la dernière sauvegarde où se trouvait la donnée avant sa suppression. Il faut ensuite l’exporter dans un autre serveur PostgreSQL ou dans une nouvelle base, sans effacer celle en production.

À partir de là, si vous ne connaissez pas encore le schéma de la base de données, vous pouvez l’analyser à l’aide d’un outil tel que DBeaver Community. Celui-ci vous permet de visualiser les tables et les relations entre elles sous la forme d’un schéma visuel.

Sélection de toutes les données à restaurer

Commencez par préparer les différentes requêtes de sélection, l’ordre de sélection est important et vous devrez le respecter pour la restauration. L’exemple suivant correspond à la restauration d’une fiche d’une personne dans une application de gestion de la scolarité d’élèves, le nom et le prénom ayant été anonymisés.

SELECT * FROM individus WHERE nom='NOM' AND prenom='Prénom';
SELECT * FROM adresses WHERE idindividu=(SELECT id FROM individus WHERE nom='NOM' AND prenom='Prénom');
SELECT * FROM courriels WHERE idindividu=(SELECT id FROM individus WHERE nom='NOM' AND prenom='Prénom');
SELECT * FROM portables WHERE idindividu=(SELECT id FROM individus WHERE nom='NOM' AND prenom='Prénom');
 
SELECT * FROM etudiants WHERE idindividu=(SELECT id FROM individus WHERE nom='NOM' AND prenom='Prénom');
SELECT * FROM bourses WHERE idetudiant=(SELECT id FROM etudiants WHERE idindividu=(SELECT id FROM individus WHERE nom='NOM' AND prenom='Prénom'));
SELECT * FROM diplomes WHERE idetudiant=(SELECT id FROM etudiants WHERE idindividu=(SELECT id FROM individus WHERE nom='NOM' AND prenom='Prénom'));
SELECT * FROM enseignements_suivis WHERE iddiplome=(SELECT id FROM diplomes WHERE idetudiant=(SELECT id FROM etudiants WHERE idindividu=(SELECT id FROM individus WHERE nom='NOM' AND prenom='Prénom')));

Copier les données d’une base à une autre

Pour restaurer les données, il ne vaut mieux pas utiliser INSERT, mais COPY. COPY présente un double avantage dans ce cas de figure :

  • COPY ne devrait pas déclencher de TRIGGER, contrairement à INSERT ;
  • COPY permet d’insérer des données par paquets, contrairement à INSERT qui va le faire de ligne à ligne.

De plus, utiliser INSERT pourrait potentiellement générer un nouvel ID, voire le décalage d’un nouvel ID (table séquence), ce que l’on souhaite éviter dans le cadre d’une restauration partielle.

Il est possible de faire un COPY depuis la ligne de commande à partir d’un fichier au format CSV contenant les données à importer, mais il est également possible de chaîner les commandes pour extraire et copier d’une base de données à une autre. Pour cela, il faut prévoir un fichier .pgpass qui contient les informations de connexion aux deux bases de données :

# host:port:database:user:password
hostdistant:5432:pg_scolarite:scolarite:password
localhost:5432:pg_scolarite_20251009:postgres:postgres

Une fois le fichier créé, il faut l’exporter dans la variable d’environnement PGPASSFILE dans l’invite de commande (sh, bash…) :

export PGPASSFILE=".pgpass"

À partir de là, on peut reprendre les lignes d’extraction préparées et faire la copie entre les bases de données comme suit :

psql -qtAXc "COPY (SELECT * FROM individus WHERE nom='NOM' AND prenom='Prénom') TO STDOUT CSV HEADER" -U postgres -h localhost -p 5432 pg_scolarite_20251009 \
| psql -qtAXc "COPY individus FROM STDIN CSV HEADER" -U scolarite -h hostdistant -p 5432 pg_scolarite                                     
psql -qtAXc "COPY (SELECT * FROM adresses WHERE idindividu=(SELECT id FROM individus WHERE nom='NOM' AND prenom='Prénom')) TO STDOUT CSV HEADER" -U postgres -h localhost -p 5432 pg_scolarite_20251009 \
| psql -qtAXc "COPY adresses FROM STDIN CSV HEADER" -U scolarite -h hostdistant -p 5432 pg_scolarite
psql -qtAXc "COPY (SELECT * FROM courriels WHERE idindividu=(SELECT id FROM individus WHERE nom='NOM' AND prenom='Prénom')) TO STDOUT CSV HEADER" -U postgres -h localhost -p 5432 pg_scolarite_20251009 \
| psql -qtAXc "COPY courriels FROM STDIN CSV HEADER" -U scolarite -h hostdistant -p 5432 pg_scolarite      
psql -qtAXc "COPY (SELECT * FROM portables WHERE idindividu=(SELECT id FROM individus WHERE nom='NOM' AND prenom='Prénom')) TO STDOUT CSV HEADER" -U postgres -h localhost -p 5432 pg_scolarite_20251009 \
| psql -qtAXc "COPY portables FROM STDIN CSV HEADER" -U scolarite -h hostdistant -p 5432 pg_scolarite
 
psql -qtAXc "COPY (SELECT * FROM etudiants WHERE idindividu=(SELECT id FROM individus WHERE nom='NOM' AND prenom='Prénom')) TO STDOUT CSV HEADER" -U postgres -h localhost -p 5432 pg_scolarite_20251009 \
| psql -qtAXc "COPY etudiants FROM STDIN CSV HEADER" -U scolarite -h hostdistant -p 5432 pg_scolarite
psql -qtAXc "COPY (SELECT * FROM bourses WHERE idetudiant=(SELECT id FROM etudiants WHERE idindividu=(SELECT id FROM individus WHERE nom='NOM' AND prenom='Prénom'))) TO STDOUT CSV HEADER" \
-U postgres -h localhost -p 5432 \
pg_scolarite_20251009 | psql -qtAXc "COPY bourses FROM STDIN CSV HEADER" -U scolarite -h hostdistant -p 5432 pg_scolarite                                                                                                                           
psql -qtAXc "COPY (SELECT * FROM diplomes WHERE idetudiant=(SELECT id FROM etudiants WHERE idindividu=(SELECT id FROM individus WHERE nom='NOM' AND prenom='Prénom'))) TO STDOUT CSV HEADER" \
-U postgres -h localhost -p 5432 pg_scolarite_20251009 \
| psql -qtAXc "COPY diplomes FROM STDIN CSV HEADER" -U scolarite -h hostdistant -p 5432 pg_scolarite                                                                                                                          
psql -qtAXc "COPY (SELECT * FROM enseignements_suivis WHERE iddiplome=(SELECT id FROM diplomes WHERE idetudiant=(SELECT id FROM etudiants WHERE idindividu=(SELECT id FROM individus WHERE nom='NOM' AND prenom='Prénom')))) TO STDOUT CSV HEADER" \
-U postgres -h localhost -p 5432 pg_scolarite_20251009 \
| psql -qtAXc "COPY enseignements_suivis FROM STDIN CSV HEADER" -U scolarite -h hostdistant -p 5432 pg_scolarite

La première commande psql copie les données de la sauvegarde pg_scolarite_20251009, depuis localhost, pour la table précisée, au format CSV dans STDOUT (directement dans la sortie du terminal) tandis que la seconde récupère les données au format CSV depuis le STDOUT et les copie dans la table idoine dans la base pg_scolarite sur le serveur hostdistant.

Notez que si une donnée existe déjà, un message d’information sera affiché dans le terminal et la donnée ne sera pas copiée dans la seconde base.

À savoir

Contrairement à ce qui a été affirmé plus haut, COPY a également des liens avec des triggers :

COPY FROM appelle tous les triggers et contraintes de vérification sur la table de destination, mais pas les règles.

Donc si on a des règles qui se déclenchent par TRIGGER au moment de l’exécution d’un INSERT, elles ne seront pas appliquées au moment de la copie. Mais on aura un message d’erreur si on souhaite copier une donnée alors qu’une autre existe déjà pour les contraintes exigées sur la table.

Lorsque vous copiez les données, faites bien attention à l’ordre d’insertion des données en fonction des dépendances entre les tables pour éviter un conflit ou une erreur au niveau de votre application.

Améliorations possibles

Il doit être possible d’utiliser BEGIN au moment de faire la copie dans la base de données pour pouvoir revenir en arrière en cas de problème, je ne l’ai pas du tout testé, je vous laisse donc libres d’améliorer la procédure.

Pour aller plus loin