PostgreSQL 17 : fiabiliser la réplication logique grâce au failover

Avant de commencer, assurez-vous de disposer des éléments suivants :

1. L'AWS CLI configurée avec vos clés d'accès et secrètes.
2. Un accès réseau aux instances RDS.
3. Un Security Group (SG) VPC existant autorisant votre IP à se connecter sur le port 5432.

1. Créer et configurer les parameter groups avec les paramètres requis.
2. Provisionner les instances RDS : créer les instances RDS PostgreSQL.
3. Mettre en place la réplication logique : configurer les slots, les publications et les subscriptions.
4. Simuler un failover et mettre à jour la subscription : promouvoir la read replica et rediriger la subscription vers le nouveau primaire.
5. Tester le flux de réplication pour garantir la cohérence des données.
6. Nettoyer les ressources.

1. Configurer les parameter groups

• Commencez par créer des parameter groups personnalisés pour le primaire, la read replica et la logical replica. Activez les paramètres suivants :

| Paramètre                    | Valeur    | Description                                                                                  |
|------------------------------|-----------|----------------------------------------------------------------------------------------------|
| rds.logical_replication      | 1         | Active la réplication logique sur l'instance primaire.                                       |
| hot_standby_feedback         | 1         | Évite les conflits de requêtes en envoyant un retour du standby vers le primaire.            |
| rds.logical_slot_sync_dbname | DB valide | Spécifie la base de données pour la synchronisation des slots logiques (par défaut : `postgres`). |
| synchronized_standby_slots   | Nom du slot | Nom du slot de réplication physique du standby qui doit rester synchronisé.                |
| sync_replication_slots       | 1         | Active la synchronisation automatique des slots de réplication.                              |

Créez le parameter group. Reproduisez les mêmes étapes pour la read replica et la logical replica.

aws rds create-db-parameter-group \
    --db-parameter-group-name pg-primary-group \
    --db-parameter-group-family postgres17 \
    --description "Logical replication setup with sync_replication_slots"

• Modifiez les paramètres. synchronized_standby_slots sera défini une fois la read replica créée :

aws rds modify-db-parameter-group \
    --db-parameter-group-name pg-primary-group \
    --parameters "ParameterName='rds.logical_replication',ParameterValue=logical,ApplyMethod=pending-reboot" \
                 "ParameterName='sync_replication_slots',ParameterValue=1,ApplyMethod=pending-reboot"

aws rds modify-db-parameter-group \
    --db-parameter-group-name pg-read-replica-group \
    --parameters "ParameterName='rds.logical_replication',ParameterValue=1,ApplyMethod=pending-reboot" \
                 "ParameterName='hot_standby_feedback',ParameterValue='1',ApplyMethod=pending-reboot" \
                 "ParameterName='rds.logical_slot_sync_dbname',ParameterValue='postgres',ApplyMethod=pending-reboot" \
                 "ParameterName='sync_replication_slots',ParameterValue=1,ApplyMethod=pending-reboot"

2. Provisionner les instances RDS

• Avec l'AWS CLI, créez l'instance primaire, la logical replica et la read replica. Pour les besoins du test, ces instances seront accessibles publiquement :

aws rds create-db-instance \
    --db-instance-identifier primary-instance \
    --engine postgres \
    --engine-version 17.1 \
    --allocated-storage 20 \
    --master-username postgres \
    --master-user-password ChangeM3 \
    --db-instance-class db.t3.medium \
    --vpc-security-group-ids sg-xxxxxx

Reproduisez l'opération pour la logical replica.

• Pour la read replica :

aws rds create-db-instance-read-replica \
    --db-instance-identifier read-replica \
    --source-db-instance-identifier primary-instance

• Une fois la read replica créée, récupérez le nom du slot physique sur l'instance primaire et mettez à jour le parameter group pg-primary-group :

psql -h $DB_PRI -U postgres -t -c "SELECT slot_name FROM pg_replication_slots where slot_type='physical'"

aws rds modify-db-parameter-group \
    --db-parameter-group-name pg-primary-group \
    --parameters "ParameterName='synchronized_standby_slots',ParameterValue='rds_us_east_1_db_dn2uyr2436rexq3u7gcdfzw4hy',ApplyMethod=pending-reboot"

• Associez ensuite les parameter groups aux instances correspondantes, puis redémarrez-les.
• Vérifiez que les paramètres sont correctement définis. Ils doivent l'être pour que la réplication logique continue de fonctionner après un failover :

psql -h $DB_PRI -U postgres -c "SELECT name,setting FROM pg_settings WHERE name IN ('wal_level','rds.logical_replication','sync_replication_slots','synchronized_standby_slots')"
:' output
            name            |                   setting
----------------------------+---------------------------------------------
 rds.logical_replication    | on
 sync_replication_slots     | on
 synchronized_standby_slots | rds_us_east_1_db_dn2uyr2436rexq3u7gcdfzw4hy
 wal_level                  | logical
(4 rows)
'

# rds.logical_slot_sync_dbname is not available in pg_settings
# make sure to update it if your database is not "postgres."
psql -h $DB_RR -U postgres -c "SELECT name,setting FROM pg_settings WHERE name IN ('wal_level','rds.logical_replication','sync_replication_slots','hot_standby_feedback','rds.logical_slot_sync_dbname')"
:' output
          name           | setting
-------------------------+---------
 hot_standby_feedback    | on
 rds.logical_replication | on
 sync_replication_slots  | on
 wal_level               | logical
(4 rows)
'

3. Mettre en place la réplication logique

Sur l'instance primaire :

• Créez la table source, alimentez-la avec des données de test et créez la publication :

CREATE TABLE reptab1 (slno int primary key);
INSERT INTO reptab1 VALUES (generate_series(1,1000));
CREATE PUBLICATION testpub FOR TABLE reptab1;

• Créez une table identique et une subscription avec failover = true sur la logical replica. Un slot de réplication logique sera alors créé sur l'instance primaire.

CREATE TABLE reptab1 (slno int primary key);
CREATE SUBSCRIPTION testsub CONNECTION 'host=<primary-endpoint> port=5432 dbname=postgres user=postgres password=ChangeM3' PUBLICATION testpub WITH (failover = true);

• Vérifiez que la subscription est active et que les données ont bien été copiées :

SELECT subname, subenabled FROM pg_subscription;
SELECT count(*) from reptab1;

• Un slot de réplication logique doit apparaître sur l'instance read-replica :

psql -h $DB_RR -U postgres -c "select slot_name, slot_type, active, failover, synced from pg_replication_slots;"
:'output
 slot_name | slot_type | active | failover | synced
-----------+-----------+--------+----------+--------
 testsub   | logical   | f      | t        | t
(1 row)
'

4. Simuler un failover et mettre à jour la subscription

Pour simuler un failover :

• Promouvez la read replica en instance autonome :

aws rds promote-read-replica \
   --db-instance-identifier read-replica

• Mettez à jour la subscription sur la logical replica pour qu'elle pointe vers le nouveau primaire :

ALTER SUBSCRIPTION testsub CONNECTION 'host=<read-replica-endpoint> port=5432 dbname=postgres user=postgres password=ChangeM3';

Avec sync_replication_slots activé, le slot de réplication logique est conservé pendant le failover : l'abonné (la logical replica) poursuit ainsi la réplication sans accroc, sans aucune resynchronisation.

• Insérez de nouvelles lignes dans la read replica promue pour valider la réplication :

INSERT INTO reptab1 VALUES (generate_series(1001,2000));

• Vérifiez que la logical replica contient bien les nouvelles lignes :

SELECT COUNT(*) FROM reptab1;  -- Doit refléter les nouvelles données

Pour éviter des coûts inutiles, supprimez l'ensemble des instances RDS et des parameter groups :

aws rds delete-db-instance --db-instance-identifier primary-instance --skip-final-snapshot
aws rds delete-db-instance --db-instance-identifier read-replica --skip-final-snapshot
aws rds delete-db-instance --db-instance-identifier logical-replica --skip-final-snapshot

aws rds delete-db-parameter-group --db-parameter-group-name pg-primary-group
aws rds delete-db-parameter-group --db-parameter-group-name pg-read-replica-group
aws rds delete-db-parameter-group --db-parameter-group-name pg-logical-replica-group