informatique:db:postgresql [Zionetrix]

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
====== PostgreSQL ======

===== Créé un utilisateur et sa base =====

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