Étiquette : PostgreSQL

Mise en place d’une réplication synchrone entre deux bases PostgreSQL
Nous allons mettre en place une réplication synchrone entre deux bases PostgreSQL

On part du principe que PostgreSQL est installé sur les serveurs

Dans notre exemple il y aura deux serveurs
- 192.168.1.114 qui sera le Master
- 192.168.1.184 qui sera le Slave
Dans notre exemple PostgreSQL est en version 17.7

1 – Préparer le master

Toutes les commandes seront à écrire sur le serveur master
- Éditer le fichier /etc/postgresql/17/main/postgresql.conf
```
nano /etc/postgresql/17/main/postgresql.conf
```
- Modifier / créer / de-commenter les lignes suivantes
```
listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 1GB

# Pour la réplication synchrone :
synchronous_standby_names = 'node184'
synchronous_commit = on
```
(node184 sera le nom du standby que l’on paramétrera plus tard)
- Éditer le fichier /etc/postgresql/17/main/pg_hba.conf
```
nano /etc/postgresql/17/main/pg_hba.conf
```
- Rajouter sous host all all 0.0.0.0/0 md5
```
host    replication     replicator     192.168.1.184/32     md5
```
(Attention aux tabulations)

(192.168.1.184 est l’ip du slave selon la configuration)

(replicator est le nom de l’utilisateur qui fera la réplication et que l’on va créer à l’étape suivante)
- Créer l’utilisateur qui va servir pour la réplication
```
sudo -i -u postgres
psql
CREATE ROLE replicator WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'motdepasse';
```
(Penser à mettre un mot de passe fort pour l’utilisateur)
- Relancer PostgreSQL pour mettre à jour le nouveau paramétrage
```
systemctl reload postgresql
```
2 – Préparer le Slave

Toutes les commandes seront à écrire sur le serveur slave
- On stoppe PostgreSQL
```
systemctl stop postgresql
```
- on supprime les données sur le serveur
```
rm -rf /var/lib/postgresql/17/main/*
```
- on récupére la base du Master
```
pg_basebackup -h 192.168.1.114 -U replicator -D /var/lib/postgresql/17/main -Fp -Xs -P -R
```
(Attention le 192.168.1.114 est l’ip du master à adapter selon la configuration)

-R pour créer automatiquement le fichier standby.signal et postgresql.auto.conf (ces fichiers servent à dire au serveur qu’il n’est plus un serveur en lecture-écriture mais que l’écriture se fait exclusivement à partir du master)

-Xs pour copier les WAL necessaires
- On va vérifier le fichier /var/lib/postgresql/17/main/postgresql.auto.conf
```
nano /var/lib/postgresql/17/main/postgresql.auto.conf
```
- on devrait avoir une ligne comme ça dedans
```
primary_conninfo = 'user=replicator password=motdepasse channel_binding=prefer host=192.168.1.114 port=5432 sslmode=prefer sslnegotiation=postgres sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
```
(Bien vérifier le mot de passe et l’ip du master)
- Modifier la ligne pour ajouter application_name=node184 aprés port =5432
(node184 correspond au node que l’on a mis à l’étape 1 dans le fichier /etc/postgresql/17/main/postgresql.conf à la ligne synchronous_standby_names =)
- Changer les droits du fichier standby.signal pour les donner à postgres
```
chown postgres:postgres /var/lib/postgresql/17/main/standby.signal
chmod 600 /var/lib/postgresql/17/main/standby.signal
```
3 – Configuration de la réplication sur le Slave
- Éditer le fichier /etc/postgresql/17/main/postgresql.conf
- Ajouter / modifier / de-commenter
```
hot_standby = on
```
- Mettre au propre les droits du slave
```
chown -R postgres:postgres /var/lib/postgresql/17/main
chmod 700 /var/lib/postgresql/17/main
```
- Démarrer postgresql
```
systemctl start postgresql
```
4 – Vérification

4.1 Sur le master
```
sudo -i -u postgres
psql
SELECT application_name, state, sync_state FROM pg_stat_replication;
```
On devrait avoir
```
SHOW synchronous_standby_names;
SHOW synchronous_commit;
```
Tout est bien sur on

4.2 Sur PgAdmin (si installé)
- On insère une données sur le master
- La donnée est bien envoyées sur le slave
12 février 2026
Se connecter à une base PostgreSQL en ligne de commande
Pour se connecter à une base PostgreSQL en ligne de commande c’est très simple
- on passe en utilisateur qui est administrateur de PostgreSQL (postgres dans notre exemple)
```
sudo -i -u postgres
```
- on se mets en mode sql
```
psql
```
- on se connecte à la base
```
\c nomdelabase
```
- on peux taper toutes les commandes sql que l’on veux
11 février 2026
Mise en place d’une réplication asynchrone PostgreSQL
Nous allons mettre en place une réplication asynchrone entre deux serveurs PostgreSQL

1 – Prérequis

Il faut avoir installé PostgreSQL sur les serveurs

2 – Préambule

Dans notre exemple on va avoir deux serveurs
- Le 192.168.1.114 qui va être considéré comme le Master. C’est lui qui enverra ses données au Slave
- Le 192.168.1.184 qui va être considéré comme le Slave. C’est lui qui recevra les données du Master
3 – Préparation du Master

Toutes les commandes seront faites sur le Master

3.1 – Création de l’utilisateur qui fera la réplication
- on se connecte au master
```
sudo -i -u postgres
```
(L’utilisateur postgre est crée automatiquement lors de l’installation de PostgreSQL. Se reporter à l’article parlant de l’installation de PostgreSQL)
```
psql
```
- Créer l’utilisateur qui fera la réplication
```
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'motdepasseutilisateur';
```
3.2 – Modification des fichiers PostgreSQL
- Modifier le fichier /etc/postgresql/*/main/postgresql.conf
- De-commenter, ajouter ou modifier les lignes suivantes
```
listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
wal_keep_size = 512MB
```
- En option mais recommandé pour archiver les WAL
- De-commenter, ajouter ou modifier les lignes suivantes
```
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'
```
- Enregistrer le fichier /etc/postgresql/*/main/postgresql.conf
- Modifier le fichier /etc/postgresql/*/main/pg_hba.conf
- commenter
```
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
```
- Ajouter
```
host    replication     replicator     192.168.1.184/32     md5
```
(Attention, l’ip est l’ip du Slave, penser à la modifier si le Slave n’a pas l’ip 192.168.1.184)

(Attention aux tabulations)
- Sauvegarder le fichier
3.3 Redémarrer le service PostgreSQL
```
sudo systemctl restart postgresql
```
4 – Préparation du Slave

Toutes les commandes seront faites sur le Slave

4.1 Arrêter le service PostgreSQL
```
sudo systemctl stop postgresql
```
4.2 Supprimer toutes les données de l’instance (On va récupérer les données du master plus tard)
```
sudo rm -rf /var/lib/postgresql/17/main/*
```
4.3 Cloner le master
```
pg_basebackup -h 192.168.1.114 -U replicator -D /var/lib/postgresql/17/main -Fp -Xs -P
```
(Attention, l’ip est l’ip du Master, si 192.168.1.114 n’est pas l’ip du Master il faut bien mettre l’ip du Master)

(Le mot de passe de l’utilisateur replicator rentré à l’étape 3.1 est demandé)

5 – Activation du mode standby

Le mode standby permet de dire au serveur que ce n’est pas un serveur normal mais qu’il doit se connecter à un master pour avoir ses données

5.1 – Créer le fichier standby.signal
```
sudo -u postgres touch /var/lib/postgresql/17/main/standby.signal
```
5.2 – Configurer la connexion au master
- Modifier le fichier /var/lib/postgresql/*/main/postgresql.auto.conf
- ajouter
```
primary_conninfo = 'host=192.168.1.114 port=5432 user=replicator password=motdepasseutilisateur'
```
(Attention, l’ip est l’ip du Master, le mot de passe est le mot de passe rentré à l’étape 3.1)
- Enregistrer le fichier
6 – Démarrer le service PostgreSQL sur le Slave
```
sudo systemctl start postgresql@17-main
```
7 – Vérifications

On va faire trois vérifications

7.1 – Sur le master, rentrer en mode sql
```
sudo -i -u postgres
psql
```
- Écrire
```
SELECT pid, state, client_addr FROM pg_stat_replication;
```
Le retour dois être xxxx streaming ip_du_slave

Le Master est bien configuré

7.2 – Sur le Slave, rentrer en mode sql
```
sudo -i -u postgres
psql
```
- Écrire
```
SELECT pg_is_in_recovery();
```
- T pour true, le slave est bien configuré
7.3 – Sur PGAdmin (si installé)
- On crée un enregistrement sur la base Master
- On vérifie sur la base Slave
Les données sont bien renvoyées sur le Slave
10 février 2026
Mettre une colonne en AutoIncrement sur PGAdmin
Sur PGAdmin ce n’est pas aussi simple que sur PhpMyAdmin pour mettre une colonne en autoincrement

Pour faire ça il faut
- créer la colonne
- Lui mettre un nom
- Type de données en Integer
- Cocher Not Null
- Cliquer sur le petit stylo à gauche pour modifier
- Aller dans contraintes
- Type : mettre identité
- Dans Identité mettre : par défaut
Attention

Si on fais ça après avoir mis des données il faut aller dans « début » puis forcer le début à la dernière valeur rentrée+1

Par défaut : quand on coche l’autoincrement, PGAdmin mets le début à 1 et donc on se retrouve avec une erreur d’intégrité si on a déjà des données
10 février 2026
Installation PGAdmin sur un serveur Ubuntu
1 – Prérequis

Il faut avoir installé PostgreSQL sur le serveur avant l’installation de PGAdmin

2 – Préparation du serveur
- Installation de curl, ca-certificates et gnupg
```
sudo apt install curl ca-certificates gnupg -y
```
- Ajout de la clé PGP
```
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub \
  | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg
```
- Ajout du dépôt PGAdmin
```
echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/plucky pgadmin4 main" | sudo tee /etc/apt/sources.list.d/pgadmin4.list
```
- Mettre à jour les sources
```
sudo apt update
```
3 – Installation de PGAdmin

Deux solutions

Une version desktop et une version web

S’il y a une interface graphique sur le serveur et que l’on veux utiliser directement PGAdmin sur le serveur alors on va utiliser la version desktop.

Si on veux utiliser PGAdmin sur un autre poste, on va utiliser la version Web
- Installation de la version desktop
```
sudo apt install pgadmin4-desktop -y
```
- Installation de la version web
```
sudo apt install pgadmin4-web -y
```
Dans la suite de ce tuto, on va installer la version web
- Lancement du script de configuration de PGAdmin
```
sudo PGADMIN_PLATFORM_TYPE=debian /usr/pgadmin4/bin/setup-web.sh
```
- Mettre l’adresse mail de l’utilisateur initial de pgadmin
- Le mot de passe de l’utilisateur
- Lancement du paramétrage de base de Apache pour PGAdmin, faire Y, puis Y pour confirmer le redémarrage de Apache
4 – Paramétrage de PGAdmin

Par défaut PGAdmin n’autorise que les connexions localhost, il faut donc que l’on modifie cela
- Éditer le fichier /etc/apache2/conf-available/pgadmin4.conf
```
sudo nano /etc/apache2/conf-available/pgadmin4.conf
```
Remplacer la partie
```
Require local
```
par
```
Require all granted
```
- Redémarrer Apache
```
sudo systemctl restart apache2
```
- Test de connexion
On se connecte via http://ipduserveur/pgadmin4
8 février 2026
Installation PostgreSQL sur Ubuntu
On va installer une base PostgreSQL sur notre système Ubuntu

1 – Mettre à jour le système
```
sudo apt update
sudo apt full-upgrade
```
2 – Installer PostgreSQL depuis les dépôts Ubuntu
```
sudo apt install postgresql -y
```
- Normalement PostgreSQL est activé automatiquement par l’installation mais rien n’empêche de le vérifier
```
sudo systemctl status postgresql
```
- Si ce n’est pas activé on peux l’activer via
```
sudo systemctl enable postgresql
sudo systemctl start postegresql
```
3 – Tester la connexion et création de son premier utilisateur et base de données
```
sudo -i -u postgres
psql
```
- Tant que l’on est dans postgresql, on peux crée un utilisateur, cela évitera de laisser l’utilisateur par défaut
```
CREATE USER utilisateur WITH PASSWORD 'motdepasse';
```
Bien entendu on mets l’utilisateur et le mot de passe que l’on veux
- On va aussi créer une base de données pour tester
```
CREATE DATABASE nombasededonnees OWNER utilisateur;
```
Bien entendu on mets le nom de la base de données que l’on veux et on récupére l’utilisateur que l’on viens de créer
- on donne tous les droits à l’utilisateur pour cette base de données
```
GRANT ALL PRIVILEGES ON DATABASE nombasededonnees TO utilisateur;
```
Bien remplacer le nom de la base de données et de l’utilisateur
- on peux quitter PostgreSQL et revenir au bash
```
\q
exit
```
/q quitte postgresql

exit nous fait revenir à notre utilisateur bash de base

4 – Bonus

si on veux autoriser les connexions autres que localhost
- Éditer le fichier /etc/postgresql/*/main/postgresql.conf
- Décommenter la ligne listen_adresses puis modifier pour mettre = ‘*’ au lieu de =’localhost’
- Sauvegarder le fichier
- Éditer le fichier /etc/postgresql/*/main/pg_hba.conf
- Ajouter, sous
```
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
```
la ligne
```
host    all             all             0.0.0.0/0               md5
```
(Attention aux tabulations)
- redémarrer le service postgresql
```
sudo systemctl restart postgresql
```
5 – La suite

On peux, par la suite installer PGAdmin sur le serveur
8 février 2026

Étiquette : PostgreSQL

Mise en place d’une réplication synchrone entre deux bases PostgreSQL

Se connecter à une base PostgreSQL en ligne de commande

Mise en place d’une réplication asynchrone PostgreSQL

Mettre une colonne en AutoIncrement sur PGAdmin

Installation PGAdmin sur un serveur Ubuntu

Installation PostgreSQL sur Ubuntu