PostgreSQL 17: più affidabilità grazie al failover della replica logica

Prima di iniziare, assicurarsi di avere:

1. AWS CLI configurata con access key e secret key.
2. Connettività verso le istanze RDS.
3. Un VPC Security Group (SG) esistente che consenta al proprio IP di connettersi sulla porta 5432.

1. Creare e configurare i parameter group con i parametri richiesti.
2. Effettuare il provisioning delle istanze RDS: creare le istanze RDS PostgreSQL.
3. Configurare la replica logica: impostare slot, publication e subscription.
4. Simulare un failover e aggiornare la subscription: promuovere la read replica e ripuntare la subscription al nuovo primary.
5. Testare il flusso di replica per verificare la coerenza dei dati.
6. Eliminare le risorse.

1. Configurare i Parameter Group

• Per prima cosa, creare parameter group personalizzati per il primary, la read replica e la logical replica. Abilitare i parametri seguenti:

| Parametro                    | Valore    | Descrizione                                                                    |
|------------------------------|-----------|--------------------------------------------------------------------------------|
| rds.logical_replication      | 1         | Abilita la replica logica sull'istanza primary.                                |
| hot_standby_feedback         | 1         | Previene i conflitti sulle query inviando feedback dallo standby al primary.   |
| rds.logical_slot_sync_dbname | DB valido | Specifica il database per la sincronizzazione degli slot logici (default: `postgres`). |
| synchronized_standby_slots   | Nome slot | Nome dello slot di replica fisica dello standby da mantenere sincronizzato.    |
| sync_replication_slots       | 1         | Abilita la sincronizzazione automatica degli slot di replica.                  |

Creare il parameter group. Ripetere gli stessi passaggi per la read replica e 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"

• Modificare i parametri. synchronized_standby_slots verrà impostato dopo la creazione della read replica:

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. Provisioning delle istanze RDS

• Tramite AWS CLI, creare l'istanza primary, la logical replica e la read replica. A scopo di test, queste istanze saranno accessibili pubblicamente:

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

Ripetere la stessa procedura per la logical replica.

• Per la read replica:

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

• Una volta creata la read replica, recuperare il nome dello slot fisico dall'istanza primary e aggiornare il 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"

• A questo punto, associare i parameter group alle rispettive istanze ed eseguire il reboot.
• Verificare che i parametri siano impostati correttamente. Devono esserlo affinché la replica logica continui a funzionare dopo il 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. Configurare la replica logica

Sull'istanza primary:

• Creare la tabella di origine, popolarla con dati di esempio e creare la publication:

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

• Creare una tabella identica e una subscription con failover = true sulla logical replica. L'operazione genererà uno slot di replica logica nell'istanza primary.

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);

• Verificare che la subscription sia attiva e che i dati siano stati copiati:

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

• Nell'istanza read-replica dovrebbe comparire uno slot di replica logica:

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. Simulare il failover e aggiornare la subscription

Per simulare un failover:

• Promuovere la read replica a istanza standalone:

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

• Aggiornare la subscription sulla logical replica in modo che punti al nuovo primary:

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

Con sync_replication_slots abilitato, lo slot di replica logica viene preservato durante il failover, consentendo al subscriber (logical replica) di continuare a replicare senza interruzioni e senza necessità di risincronizzazione.

• Inserire nuove righe nella read replica promossa per validare la replica:

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

• Verificare che la logical replica contenga le righe aggiornate:

SELECT COUNT(*) FROM reptab1;  -- Should reflect the new data

Per evitare costi superflui, eliminare tutte le istanze RDS e i parameter group:

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