administration et exploitation de vos bases de données

ISSN : 1960-3444ISBN : 978-2-409-01146-7

54 €

Pour plus d’informations :

PostgreSQLAdministration et exploitation de vos bases de données

Les chapitres du livre

L’administrateur de bases de données, le technicien d’exploitation et le développeur trouveront dans ce livre les informations indispensables pour exploiter au mieux toutes les possibilités de PostgreSQL (en version 10 au moment de la rédaction).Les premiers chapitres du livre couvrent l’installation de PostgreSQL sur Windows et GNU/Linux ainsi que la préparation de l’environnement d’exécution du serveur. L’auteur présente ensuite les applications clientes pouvant être utilisées, les différents paramètres de sécurité et les différents aspects de PostgreSQL concernant le support du langage SQL. Les chapitres qui suivent intro-duisent la programmation dans PostgreSQL et détaillent l’administration et l’exploitation (de la configuration du serveur aux différentes tâches d’exploitation, en passant par les sauvegardes). Un chapitre présente des outils annexes enrichissant l’utilisation de PostgreSQL. Enfin, l’auteur introduit le thème de la réplication des données entre différents serveurs, en étudiant la réplication physique et logique intégrée à PostgreSQL ainsi que l’outil Slony.Des éléments complémentaires sont en téléchargement sur le site www.editions-eni.fr.

Avant-propos • Installation • Initialisation du système de fichiers • Connexions • Définition des données • Program-mation • Exploitation • Outils • Réplication

Sébastien LARDIEREAprès avoir été développeur web, for-mateur et consultant indépendant, puis administrateur de bases de données PostgreSQL, Sébastien LARDIERE est aujourd’hui consultant PostgreSQL au sein de Loxodata, entreprise de conseil experte PostgreSQL. Son expertise sur PostgreSQL est reconnue et il la partage volontiers à travers les pages de ce livre pour le plus grand bénéfice des lecteurs.

Téléchargementwww.editions-eni.fr.fr

sur www.editions-eni.fr : b Les exemples de code SQL pour la création d’objets

(tables, fonctions…).b Des exemples de fichiers de configuration des outils

présentés.

PostgreSQL Administration

et exploitation de vos bases de données

4e édition

Nouvelle édition

Post

greS

QL

Adm

inis

trat

ion

et e

xplo

itatio

n de

vos

bas

es d

e do

nnée

s

Sébastien LARDIERE

1Table des matières

Avant-propos1. Préambule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

2. Présentation des projets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

3. Objectifs de cet ouvrage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

Chapitre 1Installation

1. Sources. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191.1 Téléchargement des sources. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201.2 Choix des options de compilation . . . . . . . . . . . . . . . . . . . . . . . . 211.3 Compilation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221.4 Étapes post-installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221.5 Intégration dans le système d’exploitation. . . . . . . . . . . . . . . . . 23

2. Linux : distributions Debian et Ubuntu . . . . . . . . . . . . . . . . . . . . . . . 242.1 Dépôt apt.postgresql.org . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252.2 Distributions RPM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

3. Installation dans un système MS-Windows. . . . . . . . . . . . . . . . . . . . 303.1 Téléchargement de l’installateur EnterpriseDB

pour MS-Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303.2 Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

Les éléments à télécharger sont disponibles à l'adresse suivante :http://www.editions-eni.fr

Saisissez la référence de l'ouvrage EP4POST dans la zone de recherche et validez. Cliquez sur le titre du livre puis sur le bouton de téléchargement.

lcroise

Tampon

2Administration et exploitation de vos bases de données

PostgreSQL

Chapitre 2Initialisation du système de fichiers

1. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45

2. Initialisation d’une instance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 462.1 Options de la commande . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47

2.1.1 Options essentielles. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 482.1.2 Choix du jeu de caractères . . . . . . . . . . . . . . . . . . . . . . . . 492.1.3 Réglages des paramètres locaux . . . . . . . . . . . . . . . . . . . . 51

2.2 Exécution de la commande . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 522.3 Création d’instances supplémentaires . . . . . . . . . . . . . . . . . . . . 54

3. Arrêt et démarrage du serveur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

4. Scripts Debian. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 574.1 Le script pg_lsclusters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 584.2 Le script pg_ctlcluster. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 584.3 Le script pg_createcluster. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 594.4 Le script pg_dropcluster . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 624.5 Le script pg_upgradecluster . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62

Chapitre 3Connexions

1. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

2. Sessions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

3. Clients . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 693.1 Les options de connexions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69

3.1.1 Variables d’environnement . . . . . . . . . . . . . . . . . . . . . . . . 713.1.2 Chaîne de connexion. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 713.1.3 Fichier de service . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 723.1.4 Hôtes Mutiples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 733.1.5 Fichier de mots de passe . . . . . . . . . . . . . . . . . . . . . . . . . . 74


3.2 Les outils Clients . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75

3.2.1 L’outil psql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75

3.2.2 Utilisation en mode interactif . . . . . . . . . . . . . . . . . . . . . 76

3.2.3 Utilisation en mode non interactif. . . . . . . . . . . . . . . . . . 80

3.2.4 Fichier de configuration . . . . . . . . . . . . . . . . . . . . . . . . . . 81

4. Droits d’accès . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82

4.1 Gestion des rôles : utilisateurs et groupes . . . . . . . . . . . . . . . . . . 82

4.1.1 Définition d’un rôle comme compte d’utilisateur. . . . . . 84

4.1.2 Définition d’un rôle comme groupe . . . . . . . . . . . . . . . . . 84

4.1.3 Appartenance à un groupe et héritage . . . . . . . . . . . . . . . 84

4.1.4 Utilisation des privilèges d’un groupe . . . . . . . . . . . . . . . 85

4.1.5 Modification d’un rôle . . . . . . . . . . . . . . . . . . . . . . . . . . . 85

4.1.6 Variables de sessions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86

4.1.7 Suppression d’un rôle . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86

4.1.8 Gestion de l’appartenance à un rôle . . . . . . . . . . . . . . . . . 87

4.2 Gratification et révocation des droits . . . . . . . . . . . . . . . . . . . . . 87

4.3 Définition des droits par défaut. . . . . . . . . . . . . . . . . . . . . . . . . . 90

4.4 Sécurité d’accès aux lignes de données . . . . . . . . . . . . . . . . . . . . 90

4.4.1 Activation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91

4.4.2 Création des règles d’accès . . . . . . . . . . . . . . . . . . . . . . . . 91

4.4.3 Exemple de création des règles d’accès . . . . . . . . . . . . . . 92

Chapitre 4Définition des données

1. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95

2. Les espaces de tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96

2.1 Modification d’un espace de tables . . . . . . . . . . . . . . . . . . . . . . . 97

2.2 Suppression d’un espace de tables . . . . . . . . . . . . . . . . . . . . . . . . 98


PostgreSQL

3. Les bases de données. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 983.1 Modification d’une base de données . . . . . . . . . . . . . . . . . . . . . 1013.2 Suppression d’une base de données. . . . . . . . . . . . . . . . . . . . . . 102

4. Les schémas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1034.1 Modification d’un schéma . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1054.2 Suppression d’un schéma. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106

5. Les tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1065.1 Attributs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108

5.1.1 Définition d'un attribut . . . . . . . . . . . . . . . . . . . . . . . . . 1085.1.2 Contraintes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108

5.2 Contraintes de tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1105.3 Exemples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1115.4 Modification d’une table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1135.5 Suppression d’une table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1175.6 Création d’une table depuis une requête . . . . . . . . . . . . . . . . . 1185.7 Partitionnement déclaratif. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119

6. Les vues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1236.1 Modification d'une vue . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1256.2 Suppression d'une vue . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1266.3 Vues matérialisées . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126

6.3.1 Création d’une vue matérialisée . . . . . . . . . . . . . . . . . . . 1266.3.2 Mise à jour des données d'une vue matérialisée . . . . . . 127

7. Le système de règles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1277.1 Suppression d’une règle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1287.2 Exemple. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128

8. L'héritage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129

9. Gestion de données externes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1309.1 Wrappers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131

9.1.1 Liste de wrappers disponibles . . . . . . . . . . . . . . . . . . . . . 1319.1.2 Création d'un wrapper . . . . . . . . . . . . . . . . . . . . . . . . . . 1329.1.3 Modification d'un wrapper . . . . . . . . . . . . . . . . . . . . . . . 1329.1.4 Suppression d'un wrapper. . . . . . . . . . . . . . . . . . . . . . . . 132


9.2 Serveurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1339.2.1 Création d'un serveur . . . . . . . . . . . . . . . . . . . . . . . . . . . 1339.2.2 Modification d'un serveur . . . . . . . . . . . . . . . . . . . . . . . . 1339.2.3 Suppression d'un serveur. . . . . . . . . . . . . . . . . . . . . . . . . 133

9.3 Utilisateurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1349.3.1 Création d'un utilisateur . . . . . . . . . . . . . . . . . . . . . . . . . 1349.3.2 Modification d'un utilisateur . . . . . . . . . . . . . . . . . . . . . 1349.3.3 Suppression d'un utilisateur . . . . . . . . . . . . . . . . . . . . . . 134

9.4 Tables étrangères . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1359.4.1 Création d'une table étrangère . . . . . . . . . . . . . . . . . . . . 1359.4.2 Modification d'une table étrangère. . . . . . . . . . . . . . . . . 1359.4.3 Suppression d'une table étrangère . . . . . . . . . . . . . . . . . 135

10. Les index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13610.1 Création d'un index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13710.2 Les différents types d'index . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13910.3 Modification d’un index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14010.4 Suppression d’un index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14010.5 Exemples d'index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14110.6 Les statistiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142

11. Séquences et attribut d’identité. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14311.1 Création d’une séquence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14511.2 Modification d’une séquence . . . . . . . . . . . . . . . . . . . . . . . . . . . 14511.3 Suppression d’une séquence. . . . . . . . . . . . . . . . . . . . . . . . . . . . 145

12. Types de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14612.1 Type de données numériques . . . . . . . . . . . . . . . . . . . . . . . . . . 14612.2 Type de données « caractères ». . . . . . . . . . . . . . . . . . . . . . . . . . 14612.3 Type de données de dates et d’heures . . . . . . . . . . . . . . . . . . . . 14712.4 Type de données « plages de valeurs » . . . . . . . . . . . . . . . . . . . . 14712.5 Types de données divers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14812.6 Tableau de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148


PostgreSQL

13. Domaines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14913.1 Création d'un domaine. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14913.2 Modification d'un domaine . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15013.3 Suppression d'un domaine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150

14. Recherche textuelle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150

15. Extensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15115.1 Création d'une extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15215.2 Modification d'une extension . . . . . . . . . . . . . . . . . . . . . . . . . . 15415.3 Suppression d'une extension . . . . . . . . . . . . . . . . . . . . . . . . . . . 15415.4 Gestion du code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154

15.4.1 Le fichier control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15515.4.2 Le script SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15615.4.3 Installation de l'extension . . . . . . . . . . . . . . . . . . . . . . . . 157

16. Opérateurs et fonctions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15916.1 Opérateurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159

16.1.1 Opérateurs de comparaison . . . . . . . . . . . . . . . . . . . . . . 15916.1.2 Opérateurs mathématiques . . . . . . . . . . . . . . . . . . . . . . 16116.1.3 Opérateurs de sous-requêtes . . . . . . . . . . . . . . . . . . . . . . 16116.1.4 Autres opérateurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16216.1.5 Expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162

16.2 Fonctions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16316.2.1 Fonctions sur les nombres. . . . . . . . . . . . . . . . . . . . . . . . 16316.2.2 Fonctions sur les chaînes de caractères . . . . . . . . . . . . . 16416.2.3 Fonctions sur les dates . . . . . . . . . . . . . . . . . . . . . . . . . . 16716.2.4 Fonctions de manipulation des séquences . . . . . . . . . . . 17016.2.5 Fonctions d’agrégat . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17016.2.6 Fonctions fenêtrées . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17216.2.7 Fonctions de manipulation des tableaux . . . . . . . . . . . . 17216.2.8 Fonctions de manipulation de données JSON. . . . . . . . 17416.2.9 Fonctions de manipulation de données XML . . . . . . . . 177


17. Manipulation des données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18117.1 Insertion de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181

17.1.1 L’ordre INSERT ... INTO . . . . . . . . . . . . . . . . . . . . . . . . 18117.1.2 Gestion des conflits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18217.1.3 L’ordre COPY. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183

17.2 Lecture de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18517.2.1 L’ordre SELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18517.2.2 L’ordre COPY. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191

17.3 Mise à jour des données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19217.4 Suppression de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193

17.4.1 L’ordre DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19317.4.2 L’ordre TRUNCATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194

17.5 Les CTE et le mot-clé WITH . . . . . . . . . . . . . . . . . . . . . . . . . . . 19417.6 Les transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196

17.6.1 Niveaux d’isolation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19717.6.2 Points de sauvegarde . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19917.6.3 Verrous d'interblocage. . . . . . . . . . . . . . . . . . . . . . . . . . . 200

Chapitre 5Programmation

1. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201

2. Procédures stockées . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2012.1 SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202

2.1.1 Volatilité. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2062.1.2 Coûts d'appel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206

2.2 PL/pgSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2072.2.1 Structure d’une fonction. . . . . . . . . . . . . . . . . . . . . . . . . 2072.2.2 Exemple de fonction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208

3. Déclencheurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2093.1 Code PL/pgSQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210

3.1.1 Exemple . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2113.1.2 Suppression de déclencheurs. . . . . . . . . . . . . . . . . . . . . . 212


PostgreSQL

3.1.3 Déclencheur sur événement . . . . . . . . . . . . . . . . . . . . . . 2123.1.4 Création d’un déclencheur sur événement . . . . . . . . . . 213

3.2 Traitements asynchrones. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213

4. Contrôle de fonctions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2144.1 Profileur de fonctions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2144.2 Intallation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2144.3 Profilage de fonctions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2164.4 Contrôle de fonction PL/pgSQL . . . . . . . . . . . . . . . . . . . . . . . . 219

4.4.1 Installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2194.4.2 Vérification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220

Chapitre 6Exploitation

1. Exécution d’une instance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2211.1 Définition des fichiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2221.2 Nom des fichiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223

2. Administration du serveur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2242.1 Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224

2.1.1 Connexions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2262.1.2 Mémoire . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2262.1.3 Collecteurs de statistiques . . . . . . . . . . . . . . . . . . . . . . . 2282.1.4 Options des outils de nettoyage . . . . . . . . . . . . . . . . . . . 2282.1.5 Logs d’activités . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2302.1.6 Fichiers d'écriture des transactions. . . . . . . . . . . . . . . . . 2342.1.7 Réplication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2372.1.8 Performances des requêtes . . . . . . . . . . . . . . . . . . . . . . . 2402.1.9 Chargement des modules . . . . . . . . . . . . . . . . . . . . . . . . 2422.1.10 Autres options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2422.1.11 Gestion des modifications de la configuration . . . . . . . 243

2.2 Catalogue système . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2452.3 Fonctions utiles à l’exploitation . . . . . . . . . . . . . . . . . . . . . . . . 250


3. Gestionnaire de connexions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2523.1 Pgpool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253

3.1.1 Installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2533.1.2 Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2543.1.3 Utilisation de PgPool . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2553.1.4 Configuration et démarrage . . . . . . . . . . . . . . . . . . . . . . 256

3.2 pgBouncer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2563.2.1 Installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2563.2.2 Configuration des connexions . . . . . . . . . . . . . . . . . . . . 2573.2.3 Configuration de l'instance. . . . . . . . . . . . . . . . . . . . . . . 2583.2.4 Démarrage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2603.2.5 Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260

4. Sauvegardes et restauration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2604.1 Sauvegardes logiques avec pg_dump et pg_dumpall . . . . . . . . 261

4.1.1 pg_dump . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2614.1.2 pg_dumpall . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2644.1.3 Choix de l’outil . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266

4.2 Restauration logique avec pg_restore et psql . . . . . . . . . . . . . . 2684.2.1 pg_restore. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2684.2.2 psql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2704.2.3 Exemples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270

4.3 Sauvegardes physiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2714.3.1 Sauvegarde à froid . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2714.3.2 Sauvegarde au fil de l’eau . . . . . . . . . . . . . . . . . . . . . . . . 2714.3.3 Restauration d’une sauvegarde au fil de l’eau . . . . . . . . 2764.3.4 L’outil pgBackRest. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2784.3.5 L’outil pgBarMan. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282

5. Exploitation et tâches de maintenance . . . . . . . . . . . . . . . . . . . . . . . 2835.1 Analyse d’une requête avec EXPLAIN. . . . . . . . . . . . . . . . . . . . 2845.2 Collecte des statistiques avec ANALYZE . . . . . . . . . . . . . . . . . 2875.3 Nettoyage des tables avec VACUUM . . . . . . . . . . . . . . . . . . . . 2885.4 Automatisation avec AUTOVACUUM . . . . . . . . . . . . . . . . . . 2895.5 Maintenance des index avec REINDEX . . . . . . . . . . . . . . . . . . 290


PostgreSQL

5.6 Organisation des tables avec CLUSTER . . . . . . . . . . . . . . . . . . 291

6. Test de l’installation avec pgbench . . . . . . . . . . . . . . . . . . . . . . . . . . 292

7. Exploitation des traces d’activités avec pgBadger . . . . . . . . . . . . . . 2937.1 Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2937.2 Analyse des fichiers de traces. . . . . . . . . . . . . . . . . . . . . . . . . . . 293

Chapitre 7Outils

1. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295

2. L’outil graphique : pgAdmin III. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295

3. L’outil Dbeaver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296

4. L’outil SQLTabs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298

5. L’outil PGWatch2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302

Chapitre 8Réplication

1. Réplication en flux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3031.1 Initialisation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3041.2 Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305

1.2.1 Connexion au serveur « standby » . . . . . . . . . . . . . . . . . 3061.2.2 Créneau de réplication . . . . . . . . . . . . . . . . . . . . . . . . . . 3061.2.3 Démarrage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306

1.3 Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307

2. Réplication en cascade . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308

3. Changement de topologie . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308

4. Réplication synchrone . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309

5. Réplication logique intégrée. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310


6. Réplication logique avec Slony . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3116.1 Installation de Slony. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3126.2 Configuration du groupe de serveurs . . . . . . . . . . . . . . . . . . . . 3136.3 Initialisation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3136.4 Démarrage du programme slon . . . . . . . . . . . . . . . . . . . . . . . . . 3146.5 Création du jeu de tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3156.6 Modification du schéma. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316

7. Évolution des solutions de réplication . . . . . . . . . . . . . . . . . . . . . . . 317

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319

Chapitre 4Définition des données

Définition des données1. IntroductionLe langage de définition des données s’appuie sur trois ordres : CREATE,ALTER et DROP déclinés pour chaque objet de la base de données. Chaqueobjet peut correspondre soit à un objet physique, donc à des fichiers dans lessystèmes de fichiers, soit à un objet logique, et donc seulement une définitionstockée dans le catalogue de la base de données.

L’exécution de ces ordres implique nécessairement un verrouillage exclusif desobjets concernés. Ce verrouillage étant exclusif, aucun autre verrou en lectureou en écriture ne peut être maintenu. Ceci ne pose pas de problème lors de lacréation d'un objet puisqu'aucune session concurrente ne peut l'avoir réclamémais lors de la suppression d'un objet, il faut attendre que tous les verrousexistants soient relâchés. En ce qui concerne une base de données par exemple,il ne doit pas y avoir de sessions connectées à cette base.

L'impact le plus important concerne la modification d'un objet, car le verrouest conservé durant tout le temps d’exécution de la commande, et ceci peutdonc avoir un impact sur l’exécution de requêtes concurrentes.

Les ordres de définition des données doivent donc être exécutés avec précau-tion si possible en choisissant une fenêtre de temps opportune.

lcroise

Tampon

© E

dit

ions

EN

I -

All r

ights

rese

rved


PostgreSQL

2. Les espaces de tablesUn espace de tables est un répertoire d’un système de fichiers, dans lequelPostgreSQL écrit les fichiers des tables et des index. Par défaut, PostgreSQLdispose d’un espace de tables situé dans le répertoire du groupe de bases dedonnées. Il est possible de créer d’autres espaces de tables, permettant ainsi àl’administrateur de choisir l’emplacement du stockage d’une table ou d’unindex.

Plusieurs motifs peuvent amener un administrateur à créer un espace detables :

– La partition ne dispose plus de suffisamment d’espace libre. Les espaces detables permettent dans ce cas d’utiliser plusieurs disques durs différentspour un même groupe de base de données, sans utiliser de systèmes devolumes logiques, comme LVM dans les systèmes GNU/Linux.

– L’utilisation des tables et des index provoque une saturation des écritures etdes lectures du sous-système disque où est situé l’espace de tables pardéfaut. Même sur des systèmes performants, la montée en charge d’uneapplication peut amener l’administrateur à créer d’autres espaces de tables,sur des sous-systèmes disques différents. Ainsi, les écritures et les lecturesde fichiers de tables et d’index sont réparties sur plusieurs supports phy-siques, améliorant les performances.

Un espace de tables est donc un outil, utilisable par l’administrateur duserveur de bases de données, permettant d’intervenir sur l’emplacementphysique du stockage des tables et des index. L’administrateur peut doncchoisir, table par table et index par index, quelle que soit la base de données,l’emplacement d’un fichier, optimisant ainsi les volumes utilisés, les écritureset les lectures.

Un espace de tables n’est pas spécifique à une base de données. Il fait partied’un groupe de bases de données, et est, par défaut, utilisable depuis toutes lesbases de données. Une gestion fine des permissions sur les espaces de tablespermet à l’administrateur de maîtriser la répartition des fichiers, en fonctiondes bases de données et des rôles utilisés.

97Définition des donnéesChapitre 4

La première étape, avant d’initialiser l’espace de tables depuis PostgreSQL, estde créer un répertoire pour cet usage. Selon les besoins, il peut s’agir d’unrépertoire sur un nouveau sous-système disques ou sur une partition d’unsous-système disques déjà présent. Les étapes suivantes permettent d’initiali-ser ce répertoire et de positionner les droits nécessaires pour que l’utilisateurpostgres soit le propriétaire :

[root]# mkdir -p /data2/postgres/tblspc2/ [root]# chown postgres:postgres /data2/postgres/tblspc2/ [root]# chmod 700 /data2/postgres/tblspc2/

Si le répertoire existe déjà, il doit être vide et appartenir à l’utilisateur dusystème d’exploitation qui exécute l’instance de PostgreSQL.

Une fois le répertoire créé, il suffit de l’enregistrer dans l’instance de PostgreSQL,en lui donnant un nom qui permet de l’identifier. Le synopsis de la commandeest le suivant :

postgres= # CREATE TABLESPACE nomtblspc [ OWNER nomrole ] LOCATION 'repertoire';

Par défaut, l’espace de tables ainsi créé appartient à l’utilisateur qui exécute lacommande. Seul un super-utilisateur peut créer un espace de tables, mais ilpeut transmettre l’appartenance à un autre utilisateur par l’intermédiaire del’option OWNER.

Une fois que l’espace de tables existe, il peut être utilisé au moment de lacréation ou de la modification des tables et des index.

2.1 Modification d’un espace de tables

Il est possible de modifier un espace de tables existant. Deux paramètres sontmodifiables : le nom et le propriétaire. La commande ALTER TABLESPACEpermet de faire ces deux modifications, comme dans les synopsis suivants :

postgres=# ALTER TABLESPACE nomtblspc1 RENAME TO nomtblspc2 ; postgres=# ALTER TABLESPACE nomtblspc2 OWNER TO nomrole ;

© E

dit

ions

EN

I -

All r

ights

rese

rved


PostgreSQL

En fonction des caractéristiques du sous-système disques utilisés par l'espacede table, il est possible de modifier des constantes de coût seq_page_costet random_page_cost du planificateur de requêtes, modifiant le coût delecture d'une page sur disque :

postgres=# ALTER TABLESPACE nomtblspc2 set (seq_page_cost = 3, random_page_cost = 1 ) ; postgres=# ALTER TABLESPACE nomtblspc2 reset (seq_page_cost) ;

Le sens de ces constantes est présenté au chapitre Exploitation.

2.2 Suppression d’un espace de tables

La suppression d’un espace de tables est possible s’il n’existe plus aucune tableni aucun index dans cet espace de tables, y compris dans une base de donnéesautre que celle de la connexion courante.

Avant de supprimer l’espace de tables, il faut avoir déplacé dans un autreespace de tables tous les objets contenus, y compris ceux qui n’appartiennentpas à la base de données courante. Une fois que l’espace de tables est vide,l’ordre DROP TABLESPACE permet cette suppression, comme le montre lesynopsis suivant :

postgres=# DROP TABLESPACE [ IF EXISTS ] nomtblspc ;

Une fois que l’espace de tables est supprimé dans l’instance de PostgreSQL, lerépertoire du système de fichiers n’est plus utile et peut à son tour être suppri-mé. L'option IF EXISTS permet de ne pas provoquer d'erreur lors de lasuppression si l'espace de table n'existe pas.

3. Les bases de donnéesDans une instance de PostgreSQL, une base de données est un conteneur. Ellecontient des schémas, des tables, des index et tous les objets utiles à une appli-cation. Elle accueille aussi les connexions depuis les applications clientes. Eneffet, lorsqu’une connexion est ouverte sur une base de données particulière,il n’est pas possible d’utiliser directement des objets créés dans d’autres basesde données.

99Définition des donnéesChapitre 4

Il est donc important de répartir correctement les objets et données des appli-cations dans les bases de données, notamment en utilisant la notion de sché-ma. La création d’une base de données peut s’effectuer avec l’ordre CREATEDATABASE ou avec la commande du système d’exploitation createdb.

Quelques paramètres permettent de personnaliser la création d’une base dedonnées :

– Pour créer une base de données, il est nécessaire d’être superutilisateur oud’avoir le privilège CREATEDB. En revanche, il est possible de transmettrel’appartenance à un utilisateur non privilégié, avec l’option OWNER. L’optionde la commande createdb est -O ou --owner.

– La base de données template1 sert de modèle par défaut pour la créationd’une autre base de données. Pour chaque base créée avec ce modèle, unecopie de template1 est faite, pour que tous les objets créés danstemplate1 sont dupliqués dans la nouvelle base. La base template0fonctionne de la même façon mais il n’est pas possible de créer des objetsdans cette base-modèle : template0 reste une base vierge. Il est possible dechoisir la base de données servant de modèle avec l’option TEMPLATE. Il estbien sûr possible de choisir n’importe quelle base de données modèle exis-tante. L’option de la commande createdb est -T ou --template.

– Un paramètre important lors de la création d’une base de données est lechoix du jeu de caractères. Il détermine la façon dont les données serontstockées dans les tables et les index. Le jeu de caractères par défaut est déter-miné à la création du groupe de base de données, mais il est possible de pré-ciser un autre jeu de caractères avec l’option ENCODING. Il n’est pas possiblede modifier cette option une fois la base de données créée. Pour choisir unautre encodage que celui par défaut, il est nécessaire d'utiliser la base dedonnées modèle template0 étant donné que les autres bases de donnéesmodèles peuvent contenir des données incompatibles avec le nouvel enco-dage. L’option de la commande createdb est -E ou --encoding.

– Les paramètres LC_COLLATE et LC_CTYPE ont un impact sur la localisa-tion des tris des chaînes de caractères, dans les index ou lors d'utilisation dela clause ORDER BY d'une requête.

© E

dit

ions

EN

I -

All r

ights

rese

rved


PostgreSQL

– L’espace de tables par défaut est celui qui a été créé à l’initialisation dugroupe de bases de données. Il est possible de créer d’autres espaces de tableset de l’associer avec une base de données, avec l’option TABLESPACE. Maisce choix n’est qu’un paramètre par défaut pour la création des tables etdes index, qui peut ne pas être suivi. L’option de la commande createdbest -D ou --location.

– Le dernier paramètre, CONNECTION LIMIT, permet de contrôler le nombrede connexions entrantes qui, par défaut, est illimité.

Le synopsis suivant montre l’ordre SQL permettant de créer une base dedonnées :

postgres=# CREATE DATABASE nom [ [ WITH ] [ OWNER [=] role ] [ TEMPLATE [=] modele ] [ ENCODING [=] codage ] [ LC_COLLATE [=]lc_collate ] [ LC_CTYPE [=] lc_ctype ] [ TABLESPACE [=] espacetable ] [ CONNECTION LIMIT [=] limite_connexion ] ]

La commande ci-dessous permet de créer une base de données, appeléeclients, avec le jeu de caractères UTF8 :

postgres=# CREATE DATABASE clients OWNER sebl ENCODING 'UTF8';

Le synopsis suivant montre la commande du système d’exploitation :

[postgres]# createdb [-D tablespace|--tablespace=tablespace] [-E encodage|--encoding=encodage] [--lc-collate=locale] [--lc-ctype=locale] [-O owner|--owner=owner] [-T template|--template=template] [nombase] [description]

La création de la base clients peut donc s’écrire comme ceci :

[postgres]# createdb -O sebl clients

Il est possible avec la commande createdb de se connecter à un serveurdistant, en utilisant les mêmes options que la commande psql. Par exemple,la commande suivante crée une base de données sur un serveur PostgreSQLdistant :

[root]# createdb -E UTF8 -h 192.168.0.3 -p 5432 -U postgres clients

administration et exploitation de vos bases de données

Documents