Installation de PostgreSQL
Installation
Lors de l’installation de PostgreSQL, plusieurs éléments sont fournis :
- PostgreSQL : le serveur de base de données (le SGBD)
- PSQL : c’est un logiciel client spécifique à PostgreSQL qui permet de communiquer en ligne de commande avec le serveur (je vous vois transpirer mais ne vous inquiétez pas).
- pgAdmin : c’est également un logiciel client spécifique à PostgreSQL. Il apporte une interface graphique pour une utilisation simplifiée.
- Des exécutables permettant d’effectuer des traitements sur des données de la base.
Pour installer PostgreSQL, rendez-vous à cette adresse et téléchargez la dernière version (9.6 à la rédaction de cet article, 13 lors de la mise à jour de l’article) :
https://www.postgresql.org/download/
Sachez que la gestion des versions de PostgreSQL fonctionne de la façon suivante :
[version majeure].[version mineure] :- Version majeure de PostgreSQL (impliquant de grands changements dans la structuration de la base de donnée et donc de plus ou moins gros traitements lors du passage à une nouvelle version majeure).
- Version mineure : apportant quelques améliorations sur les versions mineures précédentes mais sans modification de la structuration de la base de données.
Suivez les instructions d’installation (en conservant les paramétrages de base qui seront parfait pour notre utilisation).
A la fin de l’installation, l’utilitaire vous proposera d’utiliser « Stack Builder » pour installer des compléments, acceptez et choisissez d’installer Postgis, nous en aurons besoin pour la gestion des données géographiques.
Pensez à retenir les paramétrages indiqués lors de l’installation notamment le n° du port (5432
par défaut) et le mot de passe du superutilisateur « postgres » (vous pouvez définir le mot de passe suivant : « postgres » pour plus de simplicité, nous verrons comment le changer plus tard).
Maintenant que PostgreSQL est installé, regardons de plus près l’installation qui se trouve dans : C:\Program Files\PostgreSQL\X.X
(avec X.X le numéro de version).
Répertoires et éléments
Voici les différents éléments de votre serveur :
/bin
: contient de nombreux outils nécessaires à l’exécution de PostgreSQL. On y trouve également des exécutables permettant de faire certains traitement particulier sur nos données (par exemple : pg_dump.exe permettant de « dumper » c’est à dire de sauvegarder la base)./lib
: contient les librairies de PostgreSQL, se sont des bibliothèques de fonctions utiles./data
: c’est le cœur de votre serveur :/base
: contient vos données/pg_log
: ce sont les logs. Référez-vous à ces fichiers lorsque votre serveur ne réagit pas correctement./pg_hba.conf
: fichier de gestion des autorisations d’authentification à votre serveur./postgresql.conf
: fichier de configuration de votre serveur.
Sachez qu’il est possible de stocker vos données ailleurs que dans le répertoire de PostgreSQL, sur un disque dur annexe par exemple. C’est simplement l’emplacement du dossier /data qui sera modifié. Nous verrons comment le faire en fin d’article.
pg_hba.conf
L’accès aux bases de données PostgreSQL se fait avec la
notion de rôles et de rôles groupes. Il existe cependant un niveau de sécurité
supplémentaire : le fichier pg_hba.conf
.
Ce fichier contient la liste des adresses IP autorisées à contacter l’instance PostgreSQL.
Voici comment quelques éléments pour le gérer correctement.
- Chaque ligne commençant par un
#
est une ligne de commentaire, PostgreSQL ne la prendra donc pas en compte. - La configuration se fait de la façon suivante : on rajoute une ligne pour autoriser une (ou plusieurs) machine(s) cliente(s) puis on relance la base PostgreSQL (ou on recharge la configuration).
- La configuration par défaut n’autorise que la machine sur laquelle est installée PostgreSQL à contacter le serveur, toutes les autres sont interdites d’accès.
Voici le contenu d’une ligne :
# TYPE DATABASE USER ADDRESS METHOD host ma_base mon_user 127.0.0.1/32 md5
Cette ligne est composée des éléments suivants :
host
: méthode de contact de la base de donnéesma_base
: base pouvant être contactée par la machine (all
pour toutes les bases)mon_user
: utilisateur autorisé à être utilisé par la machine (all
pour n’importe quel utilisateur)127.0.0.1/32
: adresse IP et masque de la machine cliente (qui va contacter PostgreSQL)md5
: méthode d’authentification à utiliser
Certains de ces éléments méritent que l’on s’y attarde un peu.
TYPE
Voici les différents types de contact :
local
: connexion qui utilise les sockets du domaine Unix (connexion en local).host
: connexion par TCP/IP (la plupart des connexion utilise ce type).hostssl
: connexions par TCP/IP qui utilisent le chiffrement SSL.hostnossl
: connexion par TCP/IP qui n’utilisent pas SSL.hostgssenc
: connexion par TCP/IP qui utilisent le chiffrement GSSAPI.hostnogssenc
: connexion par TCP/IP qui n’utilisent pas GSSAPI.
Attention, si vous spécifiez des connexions host
, vous devez vérifier dans la configuration de PostgreSQL (voir partie suivante) que le serveur écoute les connexions TCP/IP distantes.
DATABASE
Vous pouvez préciser ici la base ou les bases qui peut/peuvent être contactée(s) :
ma_base
: Accès à une seule base.ma_base,ma_base2
: Accès à deux bases.all
: Accès à toutes les bases.
USER
Vous pouvez préciser ici l’utilisateur ou les utilisateurs qui peuvent être utilisée(s) :
mon_user
: Une seul user peut se connecter.mon_user1,mon_user2
: Deux users peuvent se connecter.all
: Tous les users peuvent se connecter.
ADDRESS
L’adresse peut être une adresse IP accompagnée de son masque ou un nom de domaine.
L’adresse IP est toujours accompagnée de son masque (valeur qui suit le slash). Le masque représente le nombre de bits de l’adresse IP de la machine cliente devant correspondre à celle mentionnée pour autoriser l’accès. Vous pouvez retenir ceci :
148.3.12.21/32
: l’adresse doit correspondre entièrement :148.3.12.21
148.3.12.0/24
: Seuls les trois premiers octets doivent correspondre :148.3.12.xxx
148.3.0.0/16
: Seuls les deux premiers octets doivent correspondre :148.3.xxx.xxx
148.0.0.0/8
: Seul le premier octet doit correspondre :148.xxx.xxx.xxx
0.0.0.0/0
: Aucun octet n’est obligé de correspondre, tout est autorisé :xxx.xxx.xxx.xxx
Ses éléments sont valables pour les IPv6, le masque devant simplement être adapté.
Vous pouvez également écrire le masque en entier sous la forme suivante :
148.3.12.21
255.255.255.255
pour une correspondance totale148.3.12.0
255.255.255.0
pour les trois premiers octets.148.3.0.0
255.255.0.0
pour les deux premiers octets.148.0.0.0
255.0.0.0
pour le premier octet.0.0.0.0
0.0.0.0
pour aucun octet.
Les noms de domaines peuvent être complets ou tronqués :
www.arthurbazin.com
: ciblera www.arthurbazin.com.arthurbazin.com
: ciblera tous les sous domaine de arthurbazin.comarthurbazin.com
: ciblera uniquement arthurbazin.com
Là encore vous pouvez autoriser une ou plusieurs adresses en les séparant par une virgule.
METHOD
Voici différentes méthodes d’authentification (il en existe d’autres listées dans la documentation officielle) :
trust
: toutes les connexions sont autorisées, il n’y a pas besoin de mot de passe.reject
: aucune connexion n’est acceptée, tout est rejeté.password
: le client doit fournir un mot de passe non chiffré (donc en clair sur le réseau).scram-sha-256
: le client doit fournir un mot de passe chiffré avec la méthode SCRAM-SHA-256.md5
: le client doit fournir un mot de passe doublement chiffré avec la méthode MD5 ou SCRAM-SHA-256.ident
: le serveur récupère le nom de l’utilisateur en contactant le serveur d’identification sur le poste client. Uniquement pour les connexions TCP/IP.peer
: idem queident
mais pour les connexions locale (effectuée depuis la machine du serveur).ldap
: utilisation d’un serveur LDAP.cert
: utilisation d’un certificat client SSL.pam
: utilisation du module d’authentification PAM fournit par le système.
postgresql.conf
Ce fichier contient la configuration du serveur. Certains paramètres sont définis par défaut et peuvent ainsi être modifiés grâce à ce fichier. Vous trouverez ainsi une liste de paramètres se présentant sous la forme suivante : nom_du_paramètre = valeur
.
Tout comme dans le fichier pg_hba.conf, les commentaires commencent par un #
. Ainsi, certains paramètres sont présents mais non actif car commentés.
Pour vous aider à optimiser la configuration, je vous invite à vous rendre sur le site PGTune qui vous calcule la configuration optimale selon l’environnement dans lequel s’exécutera PostgreSQL.
En parallèle, voici les principaux paramètres et certaines valeurs recommandées.
listen_adresses
Définit les adresses IP à écouter. Vous pouvez ainsi définir les valeurs suivantes :
*
: toutes les IP.148.3.12.21
: une seule IP.148.3.12.21,148.3.12.22
: plusieurs IP.
Recommandé : tout dépend de votre besoin. Pour un maximum de sécurité, ne renseignez que les adresses IP devant contacter le serveur. Sinon vous pouvez autoriser toutes les adresses et gérer la liste dans le fichier pg_hba.conf.
port
Définit le port sur lequel PostgreSQL peut être contacté. Vous pouvez indiquer n’importe quel port disponible sur le serveur. Changer le port par défaut apporte un léger gain de sécurité car il faut connaitre le port pour se connecter au serveur.
Défaut : port = 5432
Recommandé : un port différent comme 5433 ou 5434 ou un autre.
max_connections
Définit le nombre connexions simultanées à la base de données. Attention, un client peut utiliser plusieurs connexions simultanées pour accélérer le temps de réponse du serveur.
Défaut : max_connections = 100
Recommandé : max_connections = 100
superuser_reserved_connections
Définit le nombre de connexions simultanées réservées pour le (ou les) super utilisateur(s).
Défaut : superuser_reserved_connections = 3
Recommandé : superuser_reserved_connections = 3
shared_buffers
Définit la quantité de mémoire vive allouée à PostgreSQL. N’hésitez pas à augmenter ce paramètre. Comptez environ 75% de la mémoire vive disponible (mémoire vive disponible = mémoire vive total – mémoire vive utilisée par le système – mémoire vive utilisée par les différents logiciels).
Défaut : shared_buffers = 128MB
Recommandé : shared_buffers = dépend de votre machine
work_mem
Définit la quantité de mémoire vive allouée aux opérations de tri internes (order by, distinct…). Vous pouvez l’augmenter légèrement pour améliorer les performances.
Défaut : work_mem = 4MB
Recommandé : work_mem = 16MB
maintenance_work_mem
Définit la quantité de mémoire vive allouée aux opérations de maintenance (VACUUM, CREATE INDEX…). Cette valeur peut être largement augmentée car seule une de ces opérations peut être exécutée à la fois dans une session. Attention, les autovacuum utiliseront également cet quantité d’espace et ce, autant de fois que de processus d’autovacuum peuvent être lancés en même temps (voire paramètre autovacuum_max_workers).
Défaut : maintenance_work_mem = 64MB
Recommandé : maintenance_work_mem = 512MB
autovacuum_work_mem
Définit la quantité de mémoire vive allouée pour chaque processus d’autovacuum. Ceci permet d’avoir une valeur de maintenance_work_mem plus importante sans pour autant allouer trop de ressource aux autovacuum.
Défaut : autovacuum_work_mem = -1
(on utilise la valeur de maintenance_work_mem)
Recommandé : autovacuum_work_mem = 128MB
wal_level
Définit la quantité d’information écrite dans les journaux de transaction (wal). Ces journaux permettent de récupérer les données lors d’un crash du serveur PostgreSQL. Plusieurs niveaux existent :
Minimal
: ne conserve que les informations nécessaires pour survivre à un arrêt brutal ou immédiat du serveur.Replica
: minimal + quelques enregistrements pour permettre l’archivage des journaux de transactions et l’exécution des requêtes en attente.Logical
:replica
+ des informations pour extraire les modifications logiques.Archive
=replica
Hot_standby
=replica
Défaut : wal_level = minimal
Recommandé : wal_level = replica
wal_buffers
Définit la quantité de mémoire vive utilisée pour les données des journaux de transactions qui n’ont pas été écrites sur le disque.
Défaut : wal_buffers = -1
(= 1/32ème de shared_buffers)
Recommandé : wal_buffers =
au moins 2 MB
(si 1/32ème de shared_buffer < 2MB, sinon -1)
log_destination
Destination des fichiers log (format de sortie en quelque sorte). Vous pouvez choisir parmi :
stderr
: sortie par défaut (créé un fichier .log)csvlog
: sortie au format CSV.syslog
: sortie dans les journaux système.Eventlog
: (Windows uniquement) sortie dans les journaux d’évènement Windows.
logging_collector
Active le « collecteur de traces » qui capture toutes les traces envoyées vers stderr
et les envoi dans un fichier. En d’autres termes : l’activation de cette option permet d’avoir des fichiers de log.
log_directory
Répertoire d’enregistrement des logs. Vous pouvez soit utiliser un chemin relatif au répertoire PG_DATA soit un chemin absolut (C:\mon\chemin).
Défaut : log_directory = 'pg_log'
Recommandé : L’emplacement qui vous plait mais la valeur par défaut permet de conserver tous les éléments au même endroit.
log_filename
Nom des fichiers de log. Vous pouvez ajouter la date et l’heure via les paramètres suivants :
%H
: heure (au format 24h) – Exemple :12
%M
: minute – Exemple :24
%S
: seconde – Exemple :46
%R
: heure : minute – Exemple :12 : 24
%T
: heure : minute : seconde – Exemple :12 : 24 : 46
%y
: année (4 chiffres) – Exemple :2016
%Y
: année (2 chiffres) – Exemple :16
%m
: mois – Exemple :07
%d
: jour – Exemple :14
%F
: année – mois – jour – Exemple :2016 - 07 - 14
Le nom du fichier est un motif strftime. Vous pourrez trouver d’autres échappements « % » dans ceux listés par la spécification de strftime par l’Open Group.
Si le format de sortie est le CSV alors le format .csv est automatiquement ajouté au nom du journal.
Exemple : log_filename = 'Postgresql - %Y-%m-%d.log'
log_rotation_age
Détermine la durée de vie maximal (en minutes) d’un fichier de log. Passé ce délai, un nouveau journal (fichier) est créé.
La valeur « 0 » (zéro) désactive la limite de durée de vie.
Recommandé : log_rotation_age = 1d
log_rotation_size
Détermine la taille maximal d’un fichier de log. Passé cette taille, un nouveau journal (fichier) est créé.
La valeur « 0 » (zéro) désactive la limite de taille.
Recommandé : log_rotation_size = 3MB
log_truncate_on_rotation
Active l’écrasement des fichiers de log plutôt que la création d’un nouveau fichier. L’écrasement ne se produit que pour des fichiers dont le nom existe déjà et uniquement lorsqu’il s’agit d’une limite de temps (log_rotation_age).
En utilisant le paramètre log_filename = 'mon_log_%H.log'
et
le paramètre log_truncate_on_rotation = on
, il est possible de créer un
fichier de log par heure écrasés toutes les 24 h.
client_min_messages
Définit le niveau de verbosité du serveur avec les clients (et donc les utilisateurs). De DEBUG5
: une vraie pipelette à ERROR
: extrêmement peu loquace. Ce sont les messages qui seront renvoyés aux utilisateurs dans leur client.
log_min_messages
Définit le niveau de verbosité du log serveur. De DEBUG5
: une vraie pipelette à PANIC
: extrêmement peu loquace.
log_min_error_statement
Définit à partir de quel niveau le log doit contenir
l’instruction SQL à l’origine de la ligne de log. Utilisez PANIC
pour
désactiver le traçage des instructions échouées.
log_min_duration_statement
Définit la durée minimale en milliseconde d’une instruction
SQL terminée pour qu’elle soit loguée. Ceci permet de tracer les requêtes non
optimisées des applications. Utilisez la valeur 0
(zéro) pour
tracer toutes les requêtes et -1
pour n’en tracer aucune.
log_connections
Logue chaque tentative de connexion au serveur.
log_disconnections
Logue chaque déconnexion du serveur.
log_duration
Logue la durée de toute instruction exécutée. Le texte des requêtes ne sera pas logué (sauf si log_min_duration_statement est défini et que sa valeur est dépassée).
log_line_prefix
Définit le préfixe utilisé pour chaque ligne de code.
Recommandé : log_line_prefix = '%t - bdd=%d user=%u app=%a client=%h - '
log_error_verbosity
Définit la quantité de détails écrit pour chaque ligne de log.
Recommandé : log_error_verbosity = verbose
log_statement
Définit les instruction SQL à tracer :
none
: aucuneddl
: commande de définition : CREATE, ALTER, DROPmod
: commande de données : INSERT, UPDATE, DELETE, TRUNCATE, COPY FROMall
: toutes les commandes
Recommandé : log_statement = 'none'
search_path
Spécifie le schéma dans lequel les objets seront recherchés par défaut lorsque aucun préfixe n’est spécifié dans la requête. Par défaut, le serveur recherche d’abord dans le schéma dont le nom est égal au rôle de connexion utilisé puis dans le schéma « public ».
include_dir
Permet d’inclure un répertoire de configuration supplémentaire. Tous les fichiers de ce répertoire qui finissent par « .conf » seront ainsi inclus comme s’ils faisaient parti du fichier principal.
include
Permet d’inclure un fichier de configuration supplémentaire comme s’ils faisait parti du fichier principal.
Placer ses données sur un autre disque
Les données stockées dans PostgreSQL sont situées dans le répertoire /data du répertoire d’installation de PostgreSQL. Si vous souhaitez dissocier l’emplacement du logiciel de celui des données, il faut déplacer ce répertoire.
Stoppez le service PostgreSQL afin d’arrêter tous les processus le concernant.
Déplacer le répertoire « data » à l’emplacement désiré et vérifiez que l’utilisateur qui lance le service PostgreSQL a bien les droit de lecture/écriture sur les répertoires.
Éditez le registre et plus précisément la clé suivante : \HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\postgresql-x64-13\ImagePath
Attention, il faudra peut être changer le nom du service PostgreSQL indiqué ici selon la version que vous utilisez.
Modifiez la localisation du répertoire des données situé après l’option -D : "C:\Program Files\PostgreSQL\13\bin\pg_ctl.exe" runservice -N "postgresql-x64-13" -D "C:\Program Files\PostgreSQL\13\data" -w
Ouvrez les services Windows et assurez vous que le « chemin d’accès des fichiers exécutables » du service PostgreSQL contienne le nouveau chemin d’accès au répertoire data.
Démarrez le service PostgreSQL.
Voila, c’est fait.
Vérifiez l’emplacement du répertoire de données.
sudo -u postgres psql postgres=# SHOW data_directory;
En général les données se trouvent ici (la version peut être différente) : /var/lib/postgresql/13/main
Stoppez le service PostgreSQL afin d’arrêter tous les processus le concernant et vérifiez qu’il est bien stoppé.
sudo systemctl stop postgresql sudo systemctl status postgresql
Copiez le répertoire « data » à l’emplacement désiré en conservant les droits (option -a) :
sudo cp -av /var/lib/postgresql/13/main /new/repertoire/13/main
Sauvegardez le répertoire d’origine (pour pouvoir revenir en arrière si besoin) :
mv /var/lib/postgresql/13/main /var/lib/postgresql/13/main.backup
Créez un lien symbolique dans l’ancien répertoire vers votre nouvel emplacement :
ln -s /new/repertoire/pgsql/13/main /var/lib/postgresql/13/main
Démarrez le service PostgreSQL.
Voila, c’est fait.
Votre serveur est maintenant près et configuré. Nous pouvons maintenant nous intéresser au contenu et voir comment s’y connecter et sa structuration.
Sommaire général
Voici le sommaire général du cours :
Cet article vous a plu ?
N'hésitez pas à le partager, il interessera surement certains de vos contacts.
Les thèmes suivants contiennent des articles en lien avec celui-ci, allez faire un tour :
BDDPostgreSQL tuto