Utiliser Debian et dérivées

Debian 10 : Installer et utiliser PostgreSQL

Table des matières

Introduction
Installation
Paramétrer PostgreSQL
Utiliser postgresql
Allons plus loin avec une réplication Active Passive

Introduction

Dans cet article, nous allons voir comment installer et utiliser PostgreSQL sur Debian 10 !

Installation

Dans un premier temps, on s'assure que le système est à jour

Code BASH :

apt update
apt full-upgrade

On install ensuite le moteur de base de données :

Code BASH :

apt install postgresql postgresql-client

On peut aussi installer phppgadmin (Interface web de gestion de PostgreSQL) ou pgadmin3 (client lourd de gestion de PostgreSQL)
Par défaut, le nom d'utilisateur s'appelle postgres et la base de données par défaut postgres aussi.
Pour gérer le service, c'est avec systemd :

Code BASH :

systemctl status postgresql

Si le service n'est pas démarré, ni activer au démarrage, on active et on démarre ainsi :

Code BASH :

 systemctl enable --now postgresql

Paramétrer PostgreSQL

Fichier de configuration

Le fichier principal de postgresql est /etc/postgresql/11/main/postgresql.conf
On peut modifier quelques paramètres :

Code BASH :

vi /etc/postgresql/11/main/postgresql.conf

Par exemple, pour se connecter en dehors de la machine locale (autoriser les connexions de toutes les IP) :

Code BASH :

listen_addresses = '*'

Le fichier gérant les connexions des clients est /etc/postgresql/11/main/pg_hba.conf
On peut modifier quelques paramètres :

Code BASH :

vi /etc/postgresql/11/main/pg_hba.conf

Par exemple, autoriser les clients à se connecter depuis notre réseau local :

Code BASH :

host    all             all             192.168.21.0/24            md5

Après chaque modification, il ne faut pas oublier de redémarrer postgresql :

Code BASH :

systemctl restart postgresql

Définir un mot de passe à l'administrateur

Toutes les modifications doivent se faire avec l'utilisateur postgres. L'administrateur postgres s'appelle ... postgres
On se connecte (depuis root) à cet utilisateur :

Code BASH :

su - postgres

Puis on définit un mot de passe depuis le client SQL "psql" :

Code BASH :

psql -c "ALTER USER postgres WITH password 'monsupermotdepasse'"

Créer des utilisateurs

Pour créer des utilisateurs, depuis ce même compte postgres :

Code BASH :

createuser utilisateurbdd

Créer une base de données

Pour créer une base de donnée, depuis ce même compte postgres, et avec comme "propriétaire" notre utilisateur utilisateurbdd :

Code BASH :

createdb masuperbdd -O utilisateurbdd

Utiliser postgresql

Lister les bases de données

Pour lister les bases de données :

Code BASH :

psql -l

Exemple :

Code TEXT :

                                   Liste des bases de données
    Nom     |  Propriétaire  | Encodage | Collationnement | Type caract. |    Droits d'accès     
------------+----------------+----------+-----------------+--------------+-----------------------
 masuperbdd | utilisateurbdd | UTF8     | fr_FR.UTF-8     | fr_FR.UTF-8  | 
 postgres   | postgres       | UTF8     | fr_FR.UTF-8     | fr_FR.UTF-8  | 
 template0  | postgres       | UTF8     | fr_FR.UTF-8     | fr_FR.UTF-8  | =c/postgres          +
            |                |          |                 |              | postgres=CTc/postgres
 template1  | postgres       | UTF8     | fr_FR.UTF-8     | fr_FR.UTF-8  | =c/postgres          +
            |                |          |                 |              | postgres=CTc/postgres
(4 lignes)

Se connecter à une base de données

Pour se connecter à une base de données :

Code BASH :

psql masuperbdd

Commandes SQL de base

Après, on peut saisir n'importe quelle commande de type SQL :

Code SQL :

masuperbdd=# ALTER USER utilisateurbdd WITH password 'supermdp';
ALTER ROLE

Code SQL :

masuperbdd=# CREATE TABLE message ( id SERIAL PRIMARY KEY, text CHAR(64));
CREATE TABLE

Code SQL :

masuperbdd=# INSERT INTO message (text) VALUES ('Bonjour!');
INSERT 0 1

Code SQL :

masuperbdd=# SELECT * FROM message;
 id |                               text                               
----+------------------------------------------------------------------
  1 | Bonjour!                                                        
(1 ligne)

Pour supprimer une table :

Code SQL :

DROP TABLE message;

Quitter la console Postgres

Et pour quitter :

Code SQL :

\q

Supprimer une base de données

Pour supprimer une base de données :

Code SQL :

dropdb masuperbdd

Allons plus loin avec une réplication Active Passive

Installer dans un premier temps un deuxième postgres comme indiqué ci-dessus sur une autre machine. Ne pas le configurer.
Mon serveur principal est 192.168.21.168
Le secondaire est 192.168.21.152
Il est recommandé de disposer du même système d'exploitation et de la même version de postgresql.

Configurer le maître

Dans un premier temps, on prépare le maitre, et on édite le fichier de configuration postgresql.conf :

Code BASH :

vi /etc/postgresql/11/main/postgresql.conf

Dé-commenter et modifier ces options (je n'explique pas le rôle de ces options, le fichier de config est suffisamment commenté et documenté)

Code BASH :

listen_addresses = '*'
wal_level = replica
synchronous_commit = on
max_wal_senders = 10
wal_keep_segments = 10
synchronous_standby_names = '*'

Il faut aussi modifier pg_hba.conf :

Code BASH :

vi /etc/postgresql/11/main/pg_hba.conf

Renseigner ces 3 lignes avec 127.0.0.1, ainsi que les 2 IP de vos deux serveurs :

Code BASH :

host    replication     rep_user         127.0.0.1/32            trust
host    replication     rep_user         192.168.21.168/32            trust
host    replication     rep_user         192.168.21.152/32            trust

Vous aurez remarqué le "rep_user", ça sera notre utilisateur dédié à la réplication !
On va donc créer cet utilisateur :

Code BASH :

su - postgres
createuser --replication -P rep_user
exit

Nous avons enfin besoin de créer le répertoire archive et l'attribuer à postgres :

Code BASH :

mkdir /var/lib/postgresql/11/archive
chown postgres /var/lib/postgresql/11/archive

Et on redémarre le serveur postgres :

Code BASH :

systemctl restart postgresql

Configurer l'esclave

Sur l'esclave on arrête postgres :

Code BASH :

systemctl stop postgresql

On supprime les données créées sur cet hôte :

Code BASH :

rm -rf /var/lib/postgresql/11/main/*

On se connecte en postgres :

Code BASH :

 su - postgres

On récupère un backup du maitre :

Code BASH :

pg_basebackup -R -h 192.168.21.168 -U rep_user -D /var/lib/postgresql/11/main -P

Code TEXT :

31505/31505 Ko (100%), 1/1 tablespace

On se déconnecte de postgres pour revenir root :

Code BASH :

exit

On édite ensuite le fichier postgresql.conf :

Code BASH :

vi /etc/postgresql/11/main/postgresql.conf

Décommenter ou modifier ces options afin de permettre la réception des requêtes en provenance du maitre :

Code BASH :

listen_addresses = '*'
hot_standby = on

Ensuite, il nous reste plus qu'à configurer le fichier recovery.conf :

Code BASH :

vi /var/lib/postgresql/11/main/recovery.conf

Ajouter à la fin de la ligne "primary_conninfo" ceci : application_name=192.168.21.152 (ou le nom d'hôte de la machine). Ce qui donne :

Code BASH :

primary_conninfo = 'user=rep_user passfile=''/var/lib/postgresql/.pgpass'' host=192.168.21.168 port=5432 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any application_name=192.168.21.152'

Il ne reste plus qu'à démarrer postgresql sur ce serveur :

Code BASH :

systemctl start postgresql

Vérifier que ça fonctionne

Rien de plus simple, on retourne sur le maître et on exécute la commande SQL suivante depuis la session postgres :

Code BASH :

psql -c "SELECT usename, application_name, client_addr, state, sync_priority, sync_state FROM pg_stat_replication;"

On constate que la réplication est bien active (streaming) et que le status est "sync" :

Code BASH :

 usename  | application_name |  client_addr   |   state   | sync_priority | sync_state 
----------+------------------+----------------+-----------+---------------+------------
 rep_user | 192.168.21.152   | 192.168.21.152 | streaming |             1 | sync
(1 ligne)