La SS2L de référence

pour les services, les formations et

les solutions Open Source

Formations

Administration PostgreSQL

Administration de bases
de données

BY-NC-SA
 Plan

● L'administrateur de base de données

●
Formations
Introduction à PostgreSQL
● Sécurité et comptes utilisateurs
● Administration et maintenance
● Optimisations, performances et haute disponibilité

Administration de bases
de données

BY-NC-SA
 L'administrateur de base de données

● Rôle
● Mission
● Tâches

3
BY-NC-SA
 L'administrateur SGBD
Rôle

● Evidement variable suivant structure

● D'une manière générale, interface entre
– Développeurs applicatif
– Admins systèmes
– Direction opérationnelle
● Responsabilité importante, directement liées à la valeur des données
pour la structure.

4
BY-NC-SA
 L'administrateur SGBD
Mission

● Assurer l'intégrité des données

● Etre capable de restaurer les données en cas de crash
● Etre capable de gérer de manière proactive les montées en charge
● Veiller à la sécurité des accès au SGBD

5
BY-NC-SA
 L'administrateur SGBD
Tâches

● Gestion des droits des utilisateurs

● Backup et restauration
● Mise en place d'outils d'analyse des fichiers logs
● Analyse des performances et mesure correctives

6
BY-NC-SA
 Introduction à PostgreSQL

● Présentation globale et historique

● Notion de base et définitions
● Héritage de tables
● Architecture de PostgreSQL
● Arrêt et démarrage des services

7
BY-NC-SA
 Introduction à PostgreSQL
Présentation globale et historique

● Site officiel: [Link]

● Version stable: 8.1.x (8.2 en phase de béta)
● SGBD écrit en C
● Forte communauté d'utilisateurs
● Capable de gérer des volumes de données important
● Pour une présentation de la 'préhistoire':
– [Link]
● SGBD fortement conforme aux specs SQL ISO:
– Cf: [Link]
● SGBD extensible
8
BY-NC-SA
 Introduction à PostgreSQL
Notion de base et définitions

● Instance
● Cluster
● Espace logique
● Schéma
● Base
● Table

9
BY-NC-SA
 Introduction à PostgreSQL
Héritage de tables

● Permet de réutiliser structure de table dans table(s) filles

● Intérressant pour optimiser perf sur SELECT

Cf: [Link]

10
BY-NC-SA
 Introduction à PostgreSQL
Architecture de PostgreSQL

● De la query SQL au résultat

– Cf: [Link]
● Les messages et code d'execution
[Link]
– Cf: [Link]

● GEQO : Genetic Query Optimizer

– Cf: [Link]
● Filesystem et Cluster
– Cf: [Link]

11
BY-NC-SA
 Introduction à PostgreSQL
Arrêt et démarrage des services

● User PostgreSQL
– Cf: [Link]
● Cluster de données: initdb
– Cf: [Link]
● Les commandes pg_ctl et postmaster
– Cf:[Link]
● Stopper le serveur, et signaux POSIX:
– Cf: [Link]

12
BY-NC-SA
 Introduction à PostgreSQL
Arrêt et démarrage des services

● User PostgreSQL
– Cf: [Link]
● Cluster de données: initdb
– Cf: [Link]
● Les commandes pg_ctl et postmaster
– Cf:[Link]
● Stopper le serveur, et signaux POSIX:
– Cf: [Link]

13
BY-NC-SA
 Sécurité et comptes utilisateurs

● Configuration et installation sécurisées

● Gestion des rôles de la base de donnée
● Les 'groupes' et héritages de droits
● Droit des objets et attribution des rôles

14
BY-NC-SA
 Sécurité et comptes utilisateurs
Configuration et installation sécurisées

● Authentification
– pg_hba.conf
– Méthodes d'authentification
● Trust
● Md5
● Ident
● Cf:
[Link]
● Utilisation de SSL
– Echange de clés vi a OpenSSL, Cf:
[Link]
– Tunneling SSH, Cf: [Link]
15
BY-NC-SA
 Sécurité et comptes utilisateurs
Gestion des rôles de la base de donnée

● Création et suppression de rôle

– CREATE ROLE
– DROP ROLE
– Cf: [Link]
[Link]#DATABASE-ROLES
● Attributs des rôles
– Login
– Superuser
– Database creation
– Role creation
– Password
16
BY-NC-SA
 Sécurité et comptes utilisateurs
Les 'groupes' et héritage de droits

● Notion abstraite de groupe

● Héritage de droits
● RESET ROLE
● Les privilèges non héritable
– LOGIN
– SUPERUSER
– CREATEDB
– CREATEROLE

Cf: [Link]

17
BY-NC-SA
 Sécurité et comptes utilisateurs
Droit des objets et attribution des rôles

● GRANT
● REVOKE
● Privilèges
– SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, TRIGGER,
CREATE, TEMPORARY, EXECUTE, et USAGE
– Cf: [Link]
● Modification du propriétaire avec ALTER

Cf: [Link]

18
BY-NC-SA
 Administration et maintenance

● Tâches de maintenance
● Sauvegardes et restauration
● Templates
● Les fichiers de log
● Monitoring de l'activité du SGBD

19
BY-NC-SA
 Administration et maintenance
Tâches de maintenance

● Objectifs
– Optimisation de l'espace disque
– Optimisation des performances des requêtes
● Commandes
– VACUUM
– VACUUM FULL
– ANALYZE
● Le daemon auto-vacuum

Cf: [Link]

20
BY-NC-SA
 Administration et maintenance
Sauvegardes et restauration

● Dumps
– pg_dump
– pg_dumpall
– Cf: [Link]
● Faire un backup du filesystem (cluster)
– Cf: [Link]
● Approche incrémentale avec WAL:
– Cf: [Link]

21
BY-NC-SA
 Administration et maintenance
Les templates

● Notion de template
● Les templates 'systèmes':
– template0
– template1
● Création d'une base à partir d'un template

Cf: [Link]

22
BY-NC-SA
 Administration et maintenance
Les fichiers de log

● Redirection des logs

– /dev/null
– Fichier dans /var
– Via Syslog
● Log Rotate

Cf: [Link]

23
BY-NC-SA
 Administration et maintenance
Monitoring de l'activité du SGBD

● Outil 'classiques' de l'admin système

● Surveillance de l'activité de la base
– Le gestionnaire de statistique
– Cf: [Link]
● Surveillance de l'espace disque
– [Link]

24
BY-NC-SA
 Optimisations, performances
et haute-disponibilité

● Le planificateur de requêtes
● Paramètres du serveur
● Options de compilation
● Les espaces logiques
● Les index partiels
● Plusieurs serveurs sur une même machine
● Mise en place de Slony

25
BY-NC-SA
 Optimisations, performances
et haute-disponibilité
Le planificateur de requêtes

● Présentation et enjeux
● Commandes
– EXPLAIN
– ANALYZE
– Cf: [Link]
[Link]#USING-EXPLAIN
● Statistiques
– pg_stats
– Cf: [Link]

26
BY-NC-SA
 Optimisations, performances
et haute-disponibilité
Paramètres du serveur

● Passage de paramètres
– Paramètre de postmaster
– Variable d'environnement PG_OPTIONS
– SET
– SHOW
– Cf: [Link]
[Link]#CONFIG-SETTING
● Gestion des connexions
– [Link]
● Gestion des ressources
– Cf:[Link]

27
BY-NC-SA
 Optimisations, performances
et haute-disponibilité
Options de compilation

● Options disponible lors du configure

– ./configure –help
– Cf: [Link]
● Optimisation via GCC

28
BY-NC-SA
 Optimisations, performances
et haute-disponibilité
Les espaces logiques

● Décomposition de l'espace de stockage sur plusieurs entrées du

filesystem
– Eviter saturation partition du cluster initial
– Optimiser accès certaines données
● Création et suppression de TABLESPACE
● Assignation d'une table à un TABLESPACE
● Modification des liens symbolique des TABLESPACE

Cf: [Link]

29
BY-NC-SA
 Optimisations, performances
et haute-disponibilité
Les index partiels

● Concept d'index partiel

● Syntaxe conditionnelle
● Applications et gains de performance

Cf:[Link]

30
BY-NC-SA
 Optimisations, performances
et haute-disponibilité
Plusieurs serveurs sur une même machine

● Pré requis:
– Avoir des ports différents
– Avoir cluster différents

31
BY-NC-SA
 Optimisations, performances
et haute-disponibilité
Mise en place de Slony

● Slony: système de réplication maitre/esclave pour PostgreSQL

● Fonctionnement asynchrone via le réseau
● Version courante: 1.2.0
● Cf: [Link]
● Ne prend pas en compte les modification de schéma

32
BY-NC-SA
 Formations

Merci de votre attention

Administration de bases
de données

LINAGORA
27 rue de Berri - 75008 PARIS
Tél. : 01 58 18 68 28 || Fax : 01 58 18 68 29
BY-NC-SA