Réplication Hybride : Synchroniser Postgresql Prod Vers Postgre Et Sqlite Locaux

Le Défi de l'Hétérogénéité PostgreSQL ↔ SQLite

Une **réplication native** directe entre PostgreSQL (SGBD Client-Serveur) et SQLite (base de données embarquée) est impossible en raison de leurs architectures fondamentalement différentes. Nous utilisons donc des méthodes de **synchronisation asymétrique** :

• **PostgreSQL → PostgreSQL** : Utilisation de la Réplication Logique (temps réel).
• **PostgreSQL → SQLite** : Utilisation de scripts d'Export/Import (périodique/snapshot).

Stratégie 1 : Réplication Partielle (Ex. : Table `articles`)

Idéale lorsque vous souhaitez synchroniser uniquement un sous-ensemble de données (comme les articles ou les configurations) vers tous les environnements.

1. Réplication PostgreSQL vers PostgreSQL

Nous utilisons la **Réplication Logique** de PostgreSQL en spécifiant la table à répliquer.

A. Serveur Source (Prod) : Créer la Publication

-- Activez wal_level = logical dans postgresql.conf et redémarrez PG.

-- Créez la publication pour la table "articles"
CREATE PUBLICATION articles_pub FOR TABLE articles;

-- Créez un utilisateur de réplication dédié
CREATE USER user_repl WITH REPLICATION ENCRYPTED PASSWORD 'VOTRE_MDP_FORT';

B. Serveurs Cibles (Backup/Pre-Prod) : Créer la Subscription

-- Sur les serveurs cibles (schéma 'articles' doit exister)
CREATE SUBSCRIPTION articles_sub
CONNECTION 'host=IP_PROD port=5432 user=user_repl password=VOTRE_MDP_FORT dbname=NOM_DB_PROD'
PUBLICATION articles_pub
WITH (copy_data = true); 

Les changements sur la table `articles` sont appliqués en temps réel.

2. Synchronisation PostgreSQL vers SQLite

Nous utilisons un script pour exporter les données de la table ciblée, les adapter et les injecter dans le fichier SQLite local.

A. Script d'Export/Import (Partiel)

#!/bin/bash
TABLE_NAME="articles"
SQLITE_FILE="articles_dev.sqlite3"
TEMP_FILE="temp_dump.sql"

# 1. Dump des données de la table avec un format compatible INSERT
pg_dump -t $TABLE_NAME -d $DB_NAME --data-only --inserts --column-inserts > $TEMP_FILE

# 2. Nettoyage de la syntaxe pour SQLite (conversion des booléens true/false en 't'/'f')
sed -i '' 's/true/\'t\'/g' $TEMP_FILE
sed -i '' 's/false/\'f\'/g' $TEMP_FILE

# 3. Importation dans le fichier SQLite
sqlite3 $SQLITE_FILE "DELETE FROM $TABLE_NAME;"
sqlite3 $SQLITE_FILE ".read $TEMP_FILE"

rm $TEMP_FILE
echo "Synchronisation de $TABLE_NAME terminée."

Les développeurs exécutent ce script pour obtenir un **snapshot** à jour de la table `articles`.

Stratégie 2 : Réplication Complète de Toute la Base de Données

Cette approche réplique l'intégralité du schéma et des données. Elle est plus simple à maintenir au niveau du *setup*, mais peut introduire plus de complexité avec les types de données SQLite.

1. Réplication PostgreSQL vers PostgreSQL (BD Complète)

Nous modifions la Réplication Logique pour inclure toutes les tables.

A. Serveur Source (Prod) : Publication Complète

-- Créez une publication pour TOUTES les tables de la base de données
CREATE PUBLICATION full_db_pub FOR ALL TABLES;

B. Serveurs Cibles (Backup/Pre-Prod) : Subscription Complète

-- L'abonnement tire tout le schéma et les données initialement
CREATE SUBSCRIPTION full_db_sub
CONNECTION 'host=IP_PROD port=5432 user=user_repl password=VOTRE_MDP_FORT dbname=NOM_DB_PROD'
PUBLICATION full_db_pub
WITH (copy_data = true); 

Ceci assure une copie miroir complète et en temps réel de votre base de données de production.

2. Synchronisation PostgreSQL vers SQLite (BD Complète)

L'exportation doit inclure les commandes de création de table, ainsi que des conversions de type plus agressives.

A. Script de Synchronisation Complet

#!/bin/bash
SQLITE_FILE="data/full_db_dev.sqlite3"
TEMP_FILE="temp_full_db_dump.sql"

echo "⚙️ Démarrage de la synchronisation COMPLETE de PG vers SQLite..."

# 1. Exporter le Schéma et les Données (incluant --clean pour DROP/CREATE TABLE)
pg_dump -d $DB_NAME --clean --inserts --column-inserts > $TEMP_FILE

# 2. Adaptation de la syntaxe pour la BD Complète
# Remplacer les clés auto-incrémentées PostgreSQL par SQLite
sed -i '' 's/ SERIAL / INTEGER PRIMARY KEY /g' $TEMP_FILE
sed -i '' 's/ BIGSERIAL / INTEGER PRIMARY KEY /g' $TEMP_FILE
# Nettoyage des commandes PG incompatibles et remplacement des booléens... (voir script détaillé dans la réponse précédente)

# 3. Importer le schéma et les données (crée le fichier SQLite)
rm -f $SQLITE_FILE
sqlite3 $SQLITE_FILE ".read $TEMP_FILE"

rm $TEMP_FILE
echo "🎉 Synchronisation COMPLETE de la BD terminée !"

⚠️ Adaptation des Types : La conversion complète (JSONB, UUID, etc.) doit être gérée soit par ce script (en convertissant tout en TEXT) soit par un outil de migration plus intelligent. Testez rigoureusement votre base de données locale après une réplication complète pour éviter les erreurs de type de données.