Table des matières

PostgreSQL

Créé un utilisateur et sa base

CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypwd';
CREATE DATABASE mydb OWNER myuser;

Modifier le mot de passe d'un utilisateur

ALTER USER username WITH PASSWORD 'secret';

Mise en place d'un réplication

Tester sur PostgreSQL 15 en Debian Bookworm.

Sur le serveur maître (pré-installé et alimenté, IP 10.0.0.50 dans l'exemple suivant) :

  1. éditer le fichier /etc/postgresql/15/main/postgresql.conf :
    1. ajuster le paramètre listen_addresses pour qu'il écoute sur une adresse accessible depuis le serveur esclave (ou * pour 0.0.0.0)
    2. mettre le paramètre wal_level à replica
    3. mettre le paramètre max_wal_senders à 3 par exemple (nombre maximum de connexion simultanées de serveurs esclaves)
    4. pour une connexion synchrone, ajuster également :
      1. synchronous_commit = on
      2. synchronous_standby_names = *
  2. Créer un utilisateur dédié à la réplication : sudo -u postgres createuser –replication -P replication
  3. 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
  4. Redémarrer PostgreSQL : systemctl restart postgresql
  5. 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) :

  1. Ajuster le parefeu pour accepter les connexions sortantes PostgreSQL (port TCP 5432 classiquement) vers le serveur maître
  2. Stopper PostgreSQL : systemctl stop postgresql
  3. Supprimer les données de la base actuelle : rm -rf /var/lib/postgresql/15/*
  4. 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
  5. Éditer le fichier /etc/postgresql/15/main/postgresql.conf et ajouter hot_standby = on
  6. Redémarrer PostgreSQL : systemctl start postgresql
Pour vérifier que le serveur esclave est bien démarré et synchronisé, utiliser les requêtes SQL suivantes :
  • depuis le serveur maître : 
    SELECT usename, application_name, client_addr, state, sync_priority, sync_state FROM pg_stat_replication;
  • depuis le serveur esclave : 
    SELECT * FROM pg_stat_wal_receiver;

Resynchronisation

# 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