Différences

Ci-dessous, les différences entre deux révisions de la page.

--- informatique:db:postgresql [2012/10/29 15:49] – créée bn8
+++ informatique:db:postgresql [2024/06/03 10:09] (Version actuelle) – ajout procédure resynchronisation bn8
@@ Ligne 1: / Ligne 1: @@
-====== MySQL ======
+====== PostgreSQL ======
 ===== Créé un utilisateur et sa base =====
-<code>CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypwd';
+<code sql>CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypwd';
 CREATE DATABASE mydb OWNER myuser;</code>
+===== Modifier le mot de passe d'un utilisateur =====
+<code sql>ALTER USER username WITH PASSWORD 'secret';</code>
+===== Mise en place d'un réplication =====
+<note tip>Tester sur PostgreSQL 15 en Debian Bookworm.</note>
+Sur le serveur maître (pré-installé et alimenté, IP ''10.0.0.50'' dans l'exemple suivant) :
+  - éditer le fichier ''/etc/postgresql/15/main/postgresql.conf'' :
+    - ajuster le paramètre ''listen_addresses'' pour qu'il écoute sur une adresse accessible depuis le serveur esclave (ou ''*'' pour ''0.0.0.0'')
+    - mettre le paramètre ''wal_level'' à ''replica''
+    - mettre le paramètre ''max_wal_senders'' à ''3'' par exemple (nombre maximum de connexion simultanées de serveurs esclaves)
+    - pour une connexion synchrone, ajuster également :
+      - ''synchronous_commit = on''
+      - ''synchronous_standby_names = * ''
+  - Créer un utilisateur dédié à la réplication : ''sudo -u postgres createuser --replication -P replication''
+  - Autoriser les connexions de cette utilisateur depuis la machine esclave en ajoutant la ligne suivante dans le fichier ''/etc/postgresql/15/main/pg_hba.conf'' : ''host    replication     rep_user        10.0.0.51/32            scram-sha-256''
+  - Redémarrer PostgreSQL : ''systemctl restart postgresql''
+  - Ajuster le parefeu pour accepter les connexions PostgreSQL (port TCP 5432 classiquement) depuis le serveur esclave
+Sur le serveur esclave (pré-installé, peut importe la base, elle va être supprimée, IP ''10.0.0.51'' dans l'exemple suivant) :
+  - Ajuster le parefeu pour accepter les connexions sortantes PostgreSQL (port TCP 5432 classiquement) vers le serveur maître
+  - Stopper PostgreSQL : ''systemctl stop postgresql''
+  - Supprimer les données de la base actuelle : ''rm -rf /var/lib/postgresql/15/*''
+  - Restaurer les données depuis l'ancien serveur : ''time sudo -u postgres pg_basebackup -R -h 10.0.0.50 -U replication -D /var/lib/postgresql/15/main/ -P''
+  - Éditer le fichier ''/etc/postgresql/15/main/postgresql.conf'' et ajouter ''hot_standby = on''
+  - Redémarrer PostgreSQL : ''systemctl start postgresql''
+<note tip>Pour vérifier que le serveur esclave est bien démarré et synchronisé, utiliser les requêtes SQL suivantes :
+  * depuis le serveur maître : <code sql>SELECT usename, application_name, client_addr, state, sync_priority, sync_state FROM pg_stat_replication;</code>
+  * depuis le serveur esclave : <code sql>SELECT * FROM pg_stat_wal_receiver;</code>
+</note>
+==== Resynchronisation ====
+<code bash>
+# Retrieve remote host configuration
+PGDIR=/var/lib/postgresql/15/main
+PGUSER=$(grep primary_conninfo $PGDIR/postgresql.auto.conf |sed 's/.*user=\([^ ]\+\) .*/\1/')
+PGPASSWORD=$(grep primary_conninfo $PGDIR/postgresql.auto.conf |sed 's/.* password=\([^ ]\+\) .*/\1/')
+PGHOST=$(grep primary_conninfo $PGDIR/postgresql.auto.conf |sed 's/.* host=\([^ ]\+\) .*/\1/')
+cat << EOF
+Remote host: $PGHOST
+Username: $PGUSER
+Password: $PGPASSWORD
+EOF
+# Start resync
+systemctl stop postgresql
+rm -rf $PGDIR/*
+time sudo -u postgres pg_basebackup -h $PGHOST -U $PGUSER -D $PGDIR/ -R --progress
+systemctl start postgresql
+</code>