Installer et utiliser PostgreSQL sur Debian 10



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)
Cette page a été vue 1373 fois