Étiquette : SQL

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
Mettre en place une réplication entre plusieurs bases de données MariaDB
1 – Préambule

Nous allons mettre en place une réplication entre deux bases de données mariadb, ce qui signifie que ce qui se passe sur une base sera automatiquement renvoyé sur l’autre base

Attention : il faut prévoir un maître et un ou des esclaves

Le maître sera celui qui envoie les données, le ou les esclaves seront ceux qui recevront les données

Un cas précis serait de mettre une base en écriture et une ou plusieurs bases en lecture

Cela permettrai de rediriger le trafic entre les utilisateurs qui vont écrire dans la base (faire des commandes, etc…) et les utilisateurs qui vont lire la base (voir leurs infos, voir leurs commandes, etc…)

Cela permet, à terme, une scalabilité horizontale d’une application

Dans notre exemple nous allons avoir le « Master » qui aura l’ip 192.168.1.169 et un seul « Slave » qui aura l’ip 192.168.1.91

Notre base de données sera nommée test et aura une seule table nommée test

2 – Préparation du master

Toutes les commandes seront à faire sur le serveur « Maître »

2.1 – Modifier la configuration de MariaDB

Éditer le fichier /etc/mysql/mariadb.conf.d/50-server.cnf
```
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
```
Repérer la ligne bind-address, et mettre à la place
```
bind-address = 0.0.0.0
server-id = 1
log_bin = mariadb-bin
binlog_format = ROW
```
Enregistrer le fichier

Redémarrer MariaDB
```
sudo systemctl restart mariadb
```
2.2 – Création de l’utilisateur qui va servir pour la réplication

Se connecter à mariaBD
```
sudo mysql -u root
```
Créer l’utilisateur
```
CREATE USER 'nomutilisateurreplication'@'192.168.1.%' IDENTIFIED BY 'motdepasseutilisateur';
```
Changer le nomutilisateurreplication par le nom désiré et le motdepasseutilisateur par le mot de passe désiré

Donner les accès à l’utilisateur pour qu’il puisse répliquer
```
GRANT REPLICATION SLAVE ON *.* TO 'nomutilisateurreplication'@'192.168.1.%';
FLUSH PRIVILEGES;
```
2.3 – Verrouillage des tables et récupération des infos Binlog
```
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
```
Noter les valeurs dans un coin

2.4 – Faire une sauvegarde de la base master pour pouvoir la réinjecter dans le slave
```
mysqldump -u root -p --all-databases --master-data=2 > dump.sql
```
Récupérer le fichier dump.sql sur le poste – voir tuto SCP si nécessaire

Déverrouiller le master
```
UNLOCK TABLES;
```
3 – Préparation du slave

Toutes les commandes seront à faire sur le serveur « Esclave »

3.1 – Éditer le fichier /etc/mysql/mariadb.conf.d/50-server.cnf
```
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
```
Après bind-adress rajouter
```
server-id = 2
relay_log = relay-bin
```
Enregistrer le fichier

Redémarrer MariaDB
```
sudo systemctl restart mariadb
```
3.2 – Importer le fichier dump crée à l’étape 2.4

Voir le tuto sur SCP si nécessaire pour mettre le fichier sur le serveur esclave
```
mysql -u root -p < dump.sql
```
3.3 – Configurer la réplication

Se connecter sur mariaDB
```
mysql -u root -p
```
Passer la commande suivante en modifiant les valeurs que l’on a mises tout au long de ce tuto
```
CHANGE MASTER TO
  MASTER_HOST='ipdumaster',
  MASTER_USER='nomutilisateurreplication',
  MASTER_PASSWORD='motdepasseutilisateur',
  MASTER_LOG_FILE='nomfichieretape2.3',
  MASTER_LOG_POS=positionetape2.3;
```
Démarrer l’esclave
```
START SLAVE;
```
3.4 – Vérification
```
SHOW SLAVE STATUS\G
```
Vérifier surtout
- Slave_IO_Running : Yes
- Slave_SQL_Running : Yes
- Seconds_Behind_Master : 0 (ou proche)
4 – Tester une insertion dans la base pour voir si ça réplique bien

Et sur le slave

5 – Bonus

Pour rajouter d’autres slave, on reprends au 2.3 pour retrouver le fichier et la position

Et dans l’étape 3.1 on mets un server-id à 3, ou 4, ou 5, etc…
8 février 2026
Installation de phpMyAdmin sur Ubuntu
1 – Les prérequis

Avant d’installer phpMyAdmin il y a plusieurs prérequis
- Avoir installé apache2 et php
- Avoir installé une base de données sql (par exemple mariadb)
2 – Mise à jour des sources
```
sudo apt update
```
3 – Installation phpMyAdmin et des modules php nécessaires
```
sudo apt install phpmyadmin php-zip php-gd php-json php-curl libapache2-mod-php
```
y
- Choisir apache2 en appuyant sur espace pour mettre une croix dans la case apache 2, puis tab pour aller sur ok, puis entrée
- Bien vérifier que la croix est bien mise avant de faire entrée sinon l’installation ne choisira pas apache2 et il faudra tout recommencer
- Yes pour lancer la configuration de phpMyAdmin
- Mettre un mot de passe pour phpMyAdmin et le confirmer
- phpMyAdmin est installé
4 – Paramétrage

phpMyAdmin fournis, par défaut un paramétrage fonctionnel, donc on va activer ce paramétrage par défaut
```
sudo ln -s /etc/phpmyadmin/apache.conf /etc/apache2/conf-available/phpmyadmin.conf
sudo a2enconf phpmyadmin.conf
sudo systemctl reload apache2.service
```
5 – Test

On se connecte sur http://ipduserveur/phpmyadmin

6 – Bonus

Si on a cette erreur à la connexion à phpMyAdmin

Cela signifie que root n’a pas le droit de se connecter à phpMyAdmin.

On va lister les utilisateurs de la base
```
sudo mysql -u root
SELECT user, host, plugin FROM mysql.user;
```
On voit bien que l’utilisateur est phpmyadmin et non root qui peux se connecter à phpMyAdmin

On vérifie les accès de phpmyadmin
```
SHOW GRANTS FOR 'phpmyadmin'@'localhost';
```
Les seuls accès qu’il a c’est sur sa base, on va donc lui donner tous les accès possibles
```
GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
```
On resteste en remettant la commande Show Grants

notre utilisateur a bien tous les privilèges. On peux tester la connexion

Tout est fonctionnel, on peux utiliser phpMyAdmin avec mariadb
7 février 2026
Installation MariaBD sur un serveur Ubuntu
1 – Mettre à jour les sources
```
sudo apt update
```
2 – Installation de mariadb
```
sudo apt install mariadb-server
```
y

3 – Activation du démarrage automatique de MariaDB
```
sudo systemctl enable mariadb
sudo systemctl start mariadb
```
On peux vérifier le bon démarrage
```
sudo systemctl status mariadb
```
4 – Exécution du script de sécurisation pour sécuriser au minimum l’installation de mariadb
```
sudo mariadb-secure-installation
```
- Le mot de passe que l’on veux pour le root de mariadb
- N pour non
- On peux mettre Y si on change changer le mot de passe du root, sinon N vu que l’on a déjà mis un mot de passe root à l’étape 1
- Y pour supprimer les utilisateurs anonymes crées par défaut à l’installation
- Y si on va se servir du root uniquement en local (plus que conseillé)
- Y pour supprimer la base de test créée pendant l’installation de mariadb
- Y pour recharger les privilèges mis au propre
5 – Test de connexion

On va tester de se connecter à mariadb pour voir si tout est correct
```
sudo mariadb -u root -p
show databases;
```
On peux quitter mariadb avec
```
exit;
```
6 – Relance du service mariadb
```
sudo systemctl restart mariadb
```
7 – La suite

On peux installer phpMyAdmin si on le souhaite
7 février 2026