Réplication en Streaming PostgreSQL

Introduction

Dans ce laboratoire, vous apprendrez à configurer la réplication en flux (streaming replication) de PostgreSQL. Ce mécanisme transfère et applique en continu les enregistrements du journal WAL (Write-Ahead Logging) d'un serveur primaire vers un serveur réplique, assurant ainsi une synchronisation des données quasi en temps réel.

Vous configurerez une réplique en flux, vérifierez le délai (lag) et l'état de la réplication, interrogerez les données de la réplique et arrêterez la réplication en toute sécurité. Cela implique de préparer le serveur primaire en modifiant postgresql.conf pour autoriser les connexions depuis la réplique, en définissant le wal_level sur replica, et en ajustant max_wal_senders.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/db_setup("Create New Database") postgresql/PostgreSQLGroup -.-> postgresql/db_access("Connect To Database") postgresql/PostgreSQLGroup -.-> postgresql/data_all("Select All Data") postgresql/PostgreSQLGroup -.-> postgresql/db_status("Check Database Status") postgresql/PostgreSQLGroup -.-> postgresql/db_drop("Drop Entire Database") subgraph Lab Skills postgresql/db_setup -.-> lab-550962{{"Réplication en Streaming PostgreSQL"}} postgresql/db_access -.-> lab-550962{{"Réplication en Streaming PostgreSQL"}} postgresql/data_all -.-> lab-550962{{"Réplication en Streaming PostgreSQL"}} postgresql/db_status -.-> lab-550962{{"Réplication en Streaming PostgreSQL"}} postgresql/db_drop -.-> lab-550962{{"Réplication en Streaming PostgreSQL"}} end

Configurer le serveur primaire

Dans cette étape, vous configurerez le serveur PostgreSQL primaire pour autoriser la réplication en flux (streaming replication). Cela implique de modifier les fichiers postgresql.conf et pg_hba.conf.

Se connecter au serveur PostgreSQL :

Ouvrez un terminal et connectez-vous au serveur PostgreSQL en tant qu'utilisateur postgres :
```
sudo -u postgres psql
```
Modifier postgresql.conf :

Quittez l'interpréteur psql en tapant \q et en appuyant sur Entrée.

Maintenant, ouvrez le fichier postgresql.conf en utilisant nano :
```
sudo nano /etc/postgresql/14/main/postgresql.conf
```
Localisez les paramètres suivants et modifiez-les comme suit :
- listen_addresses : Modifiez-le en '*' pour autoriser les connexions depuis n'importe quelle adresse. Pour les environnements de production, il est recommandé de limiter cela à l'adresse IP de la réplique.
```
listen_addresses = '*'
```
- wal_level : Définissez-le sur replica. Cela garantit que suffisamment d'informations sont écrites dans le WAL (Write-Ahead Logging) pour la réplication.
```
wal_level = replica
```
- max_wal_senders : Définissez-le sur 10. Cela spécifie le nombre maximal de connexions simultanées depuis les serveurs de secours (standby servers).
```
max_wal_senders = 10
```
Enregistrez les modifications et quittez nano.
Modifier pg_hba.conf :

Ouvrez le fichier pg_hba.conf :
```
sudo nano /etc/postgresql/14/main/pg_hba.conf
```
Ajoutez la ligne suivante à la fin du fichier pour autoriser le serveur réplique à se connecter pour la réplication. Puisqu'il s'agit d'une configuration sur une seule machine, nous utiliserons 127.0.0.1.
```
host    replication     all             127.0.0.1/32            trust
```
Enregistrez les modifications et quittez nano.
Redémarrer le serveur primaire :

Redémarrez le serveur PostgreSQL pour appliquer les modifications :
```
sudo pg_ctlcluster 14 main restart
```
Vous devriez voir une sortie similaire à :
```
Waiting for server to shut down.... done
server stopped
server starting
```

Configurer le serveur réplique

Dans cette étape, vous configurerez le serveur réplique pour recevoir en flux (stream) les enregistrements WAL du serveur primaire.

Arrêter le serveur primaire :

Arrêtez le serveur PostgreSQL primaire :
```
sudo pg_ctlcluster 14 main stop
```
Effectuer une sauvegarde de base (Base Backup) :

Effectuez une sauvegarde de base du répertoire de données du serveur primaire en utilisant pg_basebackup. Cela copiera les fichiers de données vers le répertoire de données de la réplique (~/project/replica_data).
```
sudo pg_basebackup -h localhost -p 5432 -U postgres -D ~/project/replica_data -X stream -P -v
```
Vous serez invité à entrer le mot de passe de l'utilisateur postgres. Entrez labex.
Créer un fichier standby.signal :

Créez un fichier standby.signal dans le répertoire de données de la réplique. Ce fichier indique à PostgreSQL que cette instance doit démarrer en tant que serveur de secours chaud (hot standby).
```
touch ~/project/replica_data/standby.signal
```
Démarrer le serveur réplique :

Démarrez le serveur réplique, en le pointant vers le répertoire ~/project/replica_data :
```
sudo pg_ctlcluster 14 main start -D ~/project/replica_data
```

Vérifier l'état de la réplication et créer une table de test

Dans cette étape, vous vérifierez l'état de la réplication et créerez une table de test sur le serveur primaire pour vérifier la réplication.

Se connecter au serveur primaire :

Connectez-vous au serveur PostgreSQL primaire :
```
sudo -u postgres psql
```
Vérifier l'état de la réplication :

Exécutez la requête SQL suivante pour afficher l'état de la réplication :
```
SELECT
    pid,
    client_addr,
    state,
    sync_state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS write_lag,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag
FROM pg_stat_replication;
```
Cette requête affiche des informations sur la connexion de réplication. Un write_lag et un replay_lag proches de 0 indiquent que la réplique est synchronisée.

Créer une table de test :

Créez une table nommée test_table :

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

Insérez des données dans la table :

INSERT INTO test_table (name) VALUES ('Replica Test Data 1');
INSERT INTO test_table (name) VALUES ('Replica Test Data 2');

Quittez psql sur le serveur primaire :

\q

Interroger les données du serveur réplique

Dans cette étape, vous vous connecterez au serveur réplique et interrogerez la test_table pour vérifier que les données ont été répliquées.

Se connecter au serveur réplique :

Connectez-vous au serveur PostgreSQL réplique :
```
sudo -u postgres psql -h localhost -p 5432 -d postgres -W
```
Vous serez invité à entrer le mot de passe de l'utilisateur postgres. Entrez labex.
Interroger la test_table :

Exécutez la requête suivante pour sélectionner toutes les données de la test_table :
```
SELECT * FROM test_table;
```
Vous devriez voir les données que vous avez insérées sur le serveur primaire :
```
 id |       name
----+-----------------------
  1 | Replica Test Data 1
  2 | Replica Test Data 2
(2 rows)
```
Si vous voyez les données, la réplication fonctionne correctement.
Insérer plus de données sur le serveur primaire :

Retournez au terminal connecté au serveur primaire et insérez une nouvelle ligne :
```
sudo -u postgres psql -c "INSERT INTO test_table (name) VALUES ('Replica Test Data 3');"
```
Interroger à nouveau le serveur réplique :

Retournez au terminal connecté au serveur réplique et exécutez à nouveau la requête select :
```
SELECT * FROM test_table;
```
Vous devriez maintenant voir les nouvelles données :
```
 id |       name
----+-----------------------
  1 | Replica Test Data 1
  2 | Replica Test Data 2
  3 | Replica Test Data 3
(3 rows)
```
Quitter le serveur réplique :

Quittez psql sur le serveur réplique :
```
\q
```

Arrêter la réplication en toute sécurité

Dans cette étape, vous arrêterez la configuration de la réplication en toute sécurité.

Arrêter le serveur réplique :

Arrêtez le serveur réplique :
```
sudo pg_ctlcluster 14 main stop -D ~/project/replica_data
```
Arrêter le serveur primaire :

Arrêtez le serveur primaire :
```
sudo pg_ctlcluster 14 main stop
```
Nettoyage (facultatif) :

Si vous souhaitez supprimer la configuration de la réplication, vous pouvez supprimer le répertoire de données de la réplique. Soyez très prudent lorsque vous faites cela, car cela supprimera toutes les données sur la réplique.
```
sudo rm -rf ~/project/replica_data
```
De plus, rétablissez les modifications apportées à postgresql.conf et pg_hba.conf sur le serveur primaire si vous n'avez plus besoin de la réplication.
- Rétablir postgresql.conf :
```
sudo nano /etc/postgresql/14/main/postgresql.conf
```
  Remplacez wal_level par minimal.
```
wal_level = minimal
```
  Remplacez max_wal_senders par 0.
```
max_wal_senders = 0
```
  Remplacez listen_addresses par localhost.
```
listen_addresses = 'localhost'
```
  Enregistrez les modifications et quittez l'éditeur.
- Rétablir pg_hba.conf :
```
sudo nano /etc/postgresql/14/main/pg_hba.conf
```
  Supprimez la ligne que vous avez ajoutée pour la réplication.
  
  Enregistrez les modifications et quittez l'éditeur.
- Redémarrer le serveur primaire :
  
  Redémarrez le serveur primaire pour appliquer les modifications de configuration.
```
sudo pg_ctlcluster 14 main restart
```

Résumé

Dans ce labo, vous avez configuré une réplique en streaming (streaming replica) d'une base de données PostgreSQL. Cela impliquait de modifier les fichiers postgresql.conf et pg_hba.conf sur le serveur primaire, de prendre une sauvegarde de base (base backup), de configurer le serveur réplique et de vérifier que la réplication fonctionnait correctement. Vous avez également appris à arrêter la configuration de la réplication en toute sécurité.