Utiliser Debian et dérivées

Debian 10 : Installer et utiliser PostgreSQL

Cet article a été mis à jour, vous consultez ici une archive de cet article!
Table des matières


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.
Mon serveur principal est 192.168.21.168
Le secondaire est 192.168.21.152

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