143
Présentation de la formation PostgreSQL, administration alphorm.com™© Site : http://alphorm.com Blog : http://alphorm.com/blog Forum : http://alphorm.com/forum Noureddine DRISSI Expert consultant bases de données Contact : [email protected] formation

alphorm.com - Formation PostgreSQL administration

Embed Size (px)

DESCRIPTION

La formation complète est disponible ici: http://www.alphorm.com/tutoriel/formation-en-ligne-postgresql-administration PostgreSQL est un système de gestion de base de données relationnelle OpenSource, développé à l'origine par l'université de Berkeley. Avec sa grande maturité actuelle, il présente une très bonne alternative aux SGBDR commerciaux. Durant cette formation, notre expert base de données Noureddine DRISSI va vous accompagner pour installer et administrer au quotidien votre serveur de base de données PostgreSQL. Il vous expliquera aussi comment manipuler les données, comment gérer une base et vous apprendra à maîtriser les principales techniques et outils d'administration.

Citation preview

Page 1: alphorm.com - Formation PostgreSQL administration

Présentation de la formation

PostgreSQL, administration alphorm.com™©

Site : http://alphorm.comBlog : http://alphorm.com/blogForum : http://alphorm.com/forum

Noureddine DRISSI Expert consultant bases de donnéesContact : [email protected]

formation

Page 2: alphorm.com - Formation PostgreSQL administration

Le formateur

�Noureddine DRISSI

� Spécialisé dans la base de données

� 15 années d’expérience sur tous les SGBDR du marché (SQL Server, Oracle, PostgreSQL, Mysql, DB2)

� Issue de l’environnement bancaire

PostgreSQL, administration alphorm.com™©

[email protected]

Page 3: alphorm.com - Formation PostgreSQL administration

Connaissances préalables

�Connaissances de base du système d’exploitation Linux ou Windows

� Expérience professionnelle de SQL

� Expérience professionnelle des bases de données relationnelles

� Expérience en matière de conception de bases de données

PostgreSQL, administration alphorm.com™©

� Expérience en matière de conception de bases de données

Page 4: alphorm.com - Formation PostgreSQL administration

A propos de ce cours

�Objectifs

� Comprendre et maîtriser l’architecture de PostgreSQL;

� Administrer au quotidien PostgreSQL;

� Sauvegarder et restaurer des bases de données ou des instances PostgreSQL;

� Gérer la sécurité;

PostgreSQL, administration alphorm.com™©

� Gérer la sécurité;

� Analyser et comprendre PostgreSQL;

� Comprendre et optimiser l’architecture de PostgreSQL.

Page 5: alphorm.com - Formation PostgreSQL administration

Plan du cours

� Module 1: Présentation de PostgreSQL

� Module 2: Installation de PostgreSQL

� Module 3: Gestion des instances sous PostgreSQL

� Module 4: Les outils d’administration sous PostgreSQL

� Module 5: La gestion des

� Module 9: Sauvegarde et restauration

� Module 10: Administrer au quotidien PostgreSQL

PostgreSQL, administration alphorm.com™©

� Module 5: La gestion des transactions

� Module 6: Les logs PostgreSQL

� Module 7: Le cache de données PostgreSQL

� Module 8: Gestion de la sécurité

Page 6: alphorm.com - Formation PostgreSQL administration

Qu’est-ce qu’un SGBDR ?

� PostgreSQL est un Système de Gestion de Base de Données Relationnelle (SGBDR)

� PostgreSQL est chargé de :

� stocker les données,

� vérifier les contraintes d’intégrité définies,

� garantir la cohérence des données qu’il stocke, même en cas de panne (arrêt brutal) du système,

PostgreSQL, administration alphorm.com™©

du système,

� assurer les relations entre les données définies par les utilisateurs.

Page 7: alphorm.com - Formation PostgreSQL administration

Environnement du cours

SRVPOSTGRESQL01

5432

InstanceInstance

PostgreSQL, administration alphorm.com™©

5432

5433

CLIENT-SQL01 psql

InstanceInstance

Page 8: alphorm.com - Formation PostgreSQL administration

Introduction à la base Gescom

�Dans les ateliers pratique de ce cours, nous allons travailler avec une base de données modèle que l’on nommera GESCOM.

�Au fil des ateliers pratique, nous allons ajouter des fonctionnalités à la base de données GESCOM.

�Cette base de données servira de fil conducteur pour l’ensemble des démonstration (sauvegarde, transfert,

PostgreSQL, administration alphorm.com™©

�Cette base de données servira de fil conducteur pour l’ensemble des démonstration (sauvegarde, transfert, fichiers data, réplication, optimisation, etc.)

Page 9: alphorm.com - Formation PostgreSQL administration

Introduction à la base Gescom

Base: GESCOM

Schéma: FACT

InstanceInstance

Tables Vues

PostgreSQL, administration alphorm.com™©

Indexes séquences

Page 10: alphorm.com - Formation PostgreSQL administration

Introduction au modèle de données

PostgreSQL, administration alphorm.com™©

Page 11: alphorm.com - Formation PostgreSQL administration

GO

PostgreSQL, administration alphorm.com™©

GO

Page 12: alphorm.com - Formation PostgreSQL administration

Présentation de PostgreSQL

PostgreSQL, administration alphorm.com™©

Site : http://alphorm.comBlog : http://alphorm.com/blogForum : http://alphorm.com/forum

Noureddine DRISSI Expert consultant bases de donnéesContact : [email protected]

PostgreSQL

Page 13: alphorm.com - Formation PostgreSQL administration

Présentation de PostgreSQL

• Présentation

� Système de gestion de bases de données

� Fondé sur POSTGRES

� Supporte une grande partie du standard SQL

� Licence libérale

PostgreSQL, administration alphorm.com™©

� Programme écrit à l'université de Californie à Berkeley

� Base de données Open Source de référence

� Modèle client/serveur

Page 14: alphorm.com - Formation PostgreSQL administration

Présentation de PostgreSQL

� Les caractéristiques

� SGBDR

� Supporte le langage SQL

� Open source

� Respect de la norme ACID

PostgreSQL, administration alphorm.com™©

� Fonctionnalités

• respect de la norme SQL92, requêtes complexes ;

• clés étrangères ;

• plusieurs langages procéduraux, triggers, procédures et fonctions ;

• Déclencheurs, vues ;

Page 15: alphorm.com - Formation PostgreSQL administration

Présentation de PostgreSQL

� Les versions les plus importantes

� 1995: V 1.0 le code devient modifiable (évolution de la licence)

� 2005: V 8.0 natif Windows, savepoints, PITR, tablespaces, prêt pour l'entreprise

� 2010: V 9.2 réplication, scalabilité

PostgreSQL, administration alphorm.com™©

Page 16: alphorm.com - Formation PostgreSQL administration

Présentation de PostgreSQL

� La communauté PostgreSQL

� Le site pgFoundry.org

• Héberge de nombreux projets développés par des équipes indépendantes

- connecteurs pour les différents langages ;

- langages procéduraux ;

outils d'aide à l'administration ;

PostgreSQL, administration alphorm.com™©

- outils d'aide à l'administration ;

- logiciels pour la haute disponibilité (réplication, gestion des connexions, etc.).

Page 17: alphorm.com - Formation PostgreSQL administration

Présentation de PostgreSQL

• Les atouts

� Un projet de grande ampleur

� Un SGBD complet

� Une communauté réactive et internationale

� Un large panel de projets complémentaires

PostgreSQL, administration alphorm.com™©

� Une solution stable, ouverte et performante

� Licence BSD

� Coût nul

� Code source disponible

� Aucune contrainte de redistribution

Page 18: alphorm.com - Formation PostgreSQL administration

Présentation de PostgreSQL

�PostgreSQL VS les autres SGBDR

PostgreSQL Mysql Oracle MSSQL

Tablespace NON

Partitionnement Emulé INNODB

Vues matérialisées

PostgreSQL, administration alphorm.com™©

Vues matérialisées Emulé NON

Norme ACID INNODB

Intégrité référentielles

INNODB

Langage Procédural Plusieurs

Curseurs Limité

Page 19: alphorm.com - Formation PostgreSQL administration

Présentation de PostgreSQL

�Conclusion

� SGBDR performant

� Montée en puissance et en charge progressive

� Gestion des gros volumes de données

� Gestion des performances

PostgreSQL, administration alphorm.com™©

• Tuning matériel

• Tuning logiciel

• Optimiseur de requêtes

Page 20: alphorm.com - Formation PostgreSQL administration

Prochain module :

Installation de PostgreSQL

PostgreSQL, administration alphorm.com™©

Installation de PostgreSQL

Page 21: alphorm.com - Formation PostgreSQL administration

Installation de PostgreSQL

PostgreSQL, administration alphorm.com™©

Site : http://alphorm.comBlog : http://alphorm.com/blogForum : http://alphorm.com/forum

Noureddine DRISSI Expert consultant bases de donnéesContact : [email protected]

Windows

Page 22: alphorm.com - Formation PostgreSQL administration

PostgreSQL, administration alphorm.com™©

Page 23: alphorm.com - Formation PostgreSQL administration

Installation de PostgreSQL

� Les étapes d’installation

� Téléchargement des sources à partir du site http://www.postgresql.org

� Installation du logiciel PostgreSQL

� Automatisation du démarrage du service PostgreSQL

PostgreSQL, administration alphorm.com™©

Page 24: alphorm.com - Formation PostgreSQL administration

Prochain module :

Gestion des instances sous

PostgreSQL, administration alphorm.com™©

Gestion des instances sous PostgreSQL

Page 25: alphorm.com - Formation PostgreSQL administration

Installation de PostgreSQL

PostgreSQL, administration alphorm.com™©

Site : http://alphorm.comBlog : http://alphorm.com/blogForum : http://alphorm.com/forum

Noureddine DRISSI Expert consultant bases de donnéesContact : [email protected]

Page 26: alphorm.com - Formation PostgreSQL administration

Installation de PostgreSQL

� Les étapes d’installation

� Téléchargement des sources à partir du site http://www.postgresql.org

� Préparation du système d’exploitation

� Installation du logiciel PostgreSQL

� Initialisation des fichiers de l’instance

� Configuration

PostgreSQL, administration alphorm.com™©

� Configuration

� Installation et configuration des scripts de démarrage sous Unix ou services sous Windows

Page 27: alphorm.com - Formation PostgreSQL administration

Installation de PostgreSQL

• Les types d’installation

� Installation à partir des paquetages « rpm » (Linux)

• Les paquetages nécessaires

- Postgresql-server*

- Postgresql*

- Postgresql-libs*

PostgreSQL, administration alphorm.com™©

- Postgresql-libs*

� Installation à partir des binaires (Linux)

• Pré-requis

- make, GCC, gzip, tar

� Installation à partir des exécutables (WINDOWS)

Page 28: alphorm.com - Formation PostgreSQL administration

Prochain module :

Gestion des instances sous

PostgreSQL, administration alphorm.com™©

Gestion des instances sous PostgreSQL

Page 29: alphorm.com - Formation PostgreSQL administration

Gestion des instances sous PostgreSQL

PostgreSQL, administration alphorm.com™©

Site : http://alphorm.comBlog : http://alphorm.com/blogForum : http://alphorm.com/forum

Noureddine DRISSI Expert consultant bases de donnéesContact : [email protected]

Notion d’instance

Page 30: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

�Une instance PostgreSQL c’est quoi ?

� Un cache mémoire

� Des processus

� Une arborescence physiques (FS, répertoires)

� Un ensemble de bases de données

PostgreSQL, administration alphorm.com™©

Page 31: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

�Représentation schématique d’une instance

PostgreSQL, administration alphorm.com™©

Page 32: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

� Les processus PostgreSQL

� Le processus postmaster

• Le Processus de gestion des journaux applicatifs (optionnel)

• Le processus de collecte des statistiques (optionnel)

• Le processus d'écriture en tâche de fond

PostgreSQL, administration alphorm.com™©

• Le processus d’écriture des journaux de transactions

• Le Processus de maintenance des tables (optionnel)

• Le processus d'archivage (optionnel)

• Les processus de communication client/serveur

Page 33: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQLGestion d’une instance PostgreSQL

�� Le processus Le processus postmasterpostmaster

�� Père de tous les autres processusPère de tous les autres processus

�� Ecoute toutes les connexions entrantesEcoute toutes les connexions entrantes

�� Ecoute par la socket ou le port TCP/IP (par défaut 5432)Ecoute par la socket ou le port TCP/IP (par défaut 5432)

�� Lance les différents services nécessaires à PostgreSQLLance les différents services nécessaires à PostgreSQL

•• le processus de gestion des journaux applicatifs (si activé) ;le processus de gestion des journaux applicatifs (si activé) ;

PostgreSQL, administration alphorm.com™©

•• le processus de gestion des journaux applicatifs (si activé) ;le processus de gestion des journaux applicatifs (si activé) ;

•• le processus de collecte des statistiques (si activé) ;le processus de collecte des statistiques (si activé) ;

•• le « autovacuum launcher » (si activé) ;le « autovacuum launcher » (si activé) ;

•• le processus d'écriture en tâche de fond ;le processus d'écriture en tâche de fond ;

•• le processsus d'écriture des journaux de transactionsle processsus d'écriture des journaux de transactions

Page 34: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

�� Le processus Le processus bgwriterbgwriter

�� Ecriture des blocs modifiés en mémoire cache dans les fichiers de Ecriture des blocs modifiés en mémoire cache dans les fichiers de donnéesdonnées

�� Processus d’arrière planProcessus d’arrière plan

�� Déclenché par un checkpointDéclenché par un checkpoint

�� checkpoint_timeoutcheckpoint_timeout indique la durée maximale sans CHECKPOINTindique la durée maximale sans CHECKPOINT

PostgreSQL, administration alphorm.com™©

�� checkpoint_timeoutcheckpoint_timeout indique la durée maximale sans CHECKPOINTindique la durée maximale sans CHECKPOINT

�� checkpoint_segmentscheckpoint_segments indique le nombre maximum de journaux de indique le nombre maximum de journaux de transactions utilisés sans CHECKPOINTtransactions utilisés sans CHECKPOINT

Page 35: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

�� Le « Le « walwal writerwriter processprocess »»

�� Apparu en 8.3Apparu en 8.3

�� Enregistre les modifications dans les journaux de transactions WALEnregistre les modifications dans les journaux de transactions WAL

�� Paramètres d’optimisationParamètres d’optimisation

�� wal_bufferwal_buffer

PostgreSQL, administration alphorm.com™©

�� fsyncfsync

�� ……

Page 36: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

�� Le processus de collecte des statistiquesLe processus de collecte des statistiques

�� Activé par défaut en 8.3 (Activé par défaut en 8.3 (track_activitiestrack_activities, , track_countstrack_counts))

�� Récupère les informations des processus Récupère les informations des processus postgrespostgres

�� Statistiques sur Statistiques sur

•• le nombre de lignes lues, insérées, modifiées et suppriméesle nombre de lignes lues, insérées, modifiées et supprimées

PostgreSQL, administration alphorm.com™©

•• le nombre de blocs disque lus ou écritsle nombre de blocs disque lus ou écrits

�� Informations récupérées via un port UDP configuré en mode non Informations récupérées via un port UDP configuré en mode non bloquantbloquant

Page 37: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

�� Le processus de maintenance des tablesLe processus de maintenance des tables

�� Procède au nettoyage des tablesProcède au nettoyage des tables

�� Depuis la version 8.3Depuis la version 8.3

•• autovacuumautovacuum launcherlauncher

•• autovacuumautovacuum workerworker

PostgreSQL, administration alphorm.com™©

�� Exécuté au démarrage du serveurExécuté au démarrage du serveur

�� Traces disponibles pour ce processusTraces disponibles pour ce processus

Page 38: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

�� Le processus d’archivageLe processus d’archivage

�� Désactivé par défaut (archive_mode)Désactivé par défaut (archive_mode)

�� Gère l'archivage des journaux de transactionsGère l'archivage des journaux de transactions

�� Pas de consommation mémoire importantePas de consommation mémoire importante

�� Traces disponibles pour ce processusTraces disponibles pour ce processus

PostgreSQL, administration alphorm.com™©

Page 39: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

�� Le processus des journaux applicatifsLe processus des journaux applicatifs

�� Disponible depuis la version 8.0Disponible depuis la version 8.0

�� Activé par défautActivé par défaut

�� Apparaît sous le nom de « Apparaît sous le nom de « logger processlogger process »»

�� Exécuté au lancement du serveur PostgreSQLExécuté au lancement du serveur PostgreSQL

PostgreSQL, administration alphorm.com™©

Page 40: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

�� Les processus de communication client/serveurLes processus de communication client/serveur

�� Ce sont les plus nombreux (limitation avec max_connections)Ce sont les plus nombreux (limitation avec max_connections)

�� Gère la communication entre le client et le serveurGère la communication entre le client et le serveur

�� Démarré par le processus «Démarré par le processus « postmasterpostmaster » après authentification de » après authentification de l’utilisateurl’utilisateur

�� Traite les requêtes des clientsTraite les requêtes des clients

PostgreSQL, administration alphorm.com™©

�� Traite les requêtes des clientsTraite les requêtes des clients

�� Utilise la mémoire partagée et la mémoire dédiéeUtilise la mémoire partagée et la mémoire dédiée

Page 41: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

��Arborescence physique d’une instanceArborescence physique d’une instance

PostgreSQL, administration alphorm.com™©

Page 42: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

�� Fonction de chaque répertoireFonction de chaque répertoire

�� basebase répertoire associé à l'espace de tables pg_defaultrépertoire associé à l'espace de tables pg_default

�� base/pgsql_tmpbase/pgsql_tmp fichiers temporaires pour certaines opérationsfichiers temporaires pour certaines opérations

�� globalglobal tables système (espace de tables pg_global)tables système (espace de tables pg_global)

�� pg_clogpg_clog données relatives au statut de validation des transactionsdonnées relatives au statut de validation des transactions

PostgreSQL, administration alphorm.com™©

�� pg_logpg_log journaux d'activitéjournaux d'activité

�� pg_multixactpg_multixact données relatives au statut des transactions multiplesdonnées relatives au statut des transactions multiples

�� pg_subtranspg_subtrans données relatives au statut des transactions imbriquéesdonnées relatives au statut des transactions imbriquées

�� pg_tblspcpg_tblspc contient un lien symbolique pour chaque espace de tablescontient un lien symbolique pour chaque espace de tables

�� pg_twophasepg_twophase données pour les transactions préparéesdonnées pour les transactions préparées

�� pg_xlogpg_xlog journaux binaires (WAL)journaux binaires (WAL)

Page 43: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

�� La racine de l’instance La racine de l’instance $PGDATA$PGDATA

�� Contient tous les répertoires de l’instanceContient tous les répertoires de l’instance

�� Contient les fichiers de configuration de l’instanceContient les fichiers de configuration de l’instance

•• PG_VERSION PG_VERSION version de PostgreSQL associée à l’instanceversion de PostgreSQL associée à l’instance

•• pg_hba.confpg_hba.conf configuration de l'authentificationconfiguration de l'authentification

PostgreSQL, administration alphorm.com™©

•• pg_ident.confpg_ident.conf configuration de l'authentification identconfiguration de l'authentification ident

•• postgresql.confpostgresql.conf configuration de l'instanceconfiguration de l'instance

•• postmaster.optspostmaster.opts options de démarrage de postmasteroptions de démarrage de postmaster

•• postmaster.pidpostmaster.pid PID du processus postmasterPID du processus postmaster

Page 44: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

��Configuration de l’instanceConfiguration de l’instance

�� Se fait dans le fichier « Se fait dans le fichier « postgresql.confpostgresql.conf »»

•• A la racine de chaque instance $PGDATAA la racine de chaque instance $PGDATA

•• Toujours faire une sauvegarde du fichier avant modificationToujours faire une sauvegarde du fichier avant modification

PostgreSQL, administration alphorm.com™©

Page 45: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

��Représentation de l’architecture logique et physiqueReprésentation de l’architecture logique et physique

PostgreSQL, administration alphorm.com™©

Page 46: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

��Configuration de l’instanceConfiguration de l’instance

�� Se fait dans le fichier « Se fait dans le fichier « postgresql.confpostgresql.conf »»

•• A la racine de chaque instance $PGDATAA la racine de chaque instance $PGDATA

•• Toujours faire une sauvegarde du fichier avant modificationToujours faire une sauvegarde du fichier avant modification

PostgreSQL, administration alphorm.com™©

Page 47: alphorm.com - Formation PostgreSQL administration

Prochain module :

Les outils d’administration sous

PostgreSQL, administration alphorm.com™©

Les outils d’administration sous PostgreSQL

Page 48: alphorm.com - Formation PostgreSQL administration

Gestion des instances sous PostgreSQL

PostgreSQL, administration alphorm.com™©

Site : http://alphorm.comBlog : http://alphorm.com/blogForum : http://alphorm.com/forum

Noureddine DRISSI Expert consultant bases de donnéesContact : [email protected]

Les tablespaces

Page 49: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

�Les espaces de stockage ou de stockage ou tablespacetablespace

��Répertoire d’un système de fichier, dans lequel Répertoire d’un système de fichier, dans lequel PostgreSQLPostgreSQL écrit les fichiers des tables et des indexesécrit les fichiers des tables et des indexes

��UtilisationUtilisation

•• Extension de l’espace de stockage existantExtension de l’espace de stockage existant

PostgreSQL, administration alphorm.com™©

•• Répartition des objets à fort accès sur des disques différentsRépartition des objets à fort accès sur des disques différents

•• Amélioration des performances pour les I/OAmélioration des performances pour les I/O

Page 50: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

��Caractéristiques des espaces de tablesCaractéristiques des espaces de tables

�� N’est pas spécifique à une base de donnéesN’est pas spécifique à une base de données

�� Outil pour l’administrateur afin d’optimiser le stockage Outil pour l’administrateur afin d’optimiser le stockage sur disquesur disque

�� Gestion fine des permissions sur les espaces de tablesGestion fine des permissions sur les espaces de tables

PostgreSQL, administration alphorm.com™©

�� Maîtrise de la répartition des fichiers DATA, en fonction Maîtrise de la répartition des fichiers DATA, en fonction des bases de données et des rôles utilisésdes bases de données et des rôles utilisés

Page 51: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

•• Mise en oeuvre des espaces de tableMise en oeuvre des espaces de table

�� Au niveau de l’OSAu niveau de l’OS

•• Création du nouveau répertoire de stockageCréation du nouveau répertoire de stockage

•• Modification du propriétaire du répertoireModification du propriétaire du répertoire

•• Attribution des privilèges sur le répertoireAttribution des privilèges sur le répertoire

PostgreSQL, administration alphorm.com™©

•• Attribution des privilèges sur le répertoireAttribution des privilèges sur le répertoire

�� Au niveau de PostgreSQLAu niveau de PostgreSQL

•• Création du tablespace avec la commande DDLCréation du tablespace avec la commande DDL

•• Vérification de la création du tablespaceVérification de la création du tablespace

Page 52: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

•• Création des espaces de tableCréation des espaces de table

�� SyntaxeSyntaxe

�� ExempleExemple

CREATE TABLESPACE <nom> LOCATION <dir>

# CREATE TABLESPACE app LOCATION ‘/u101/pgsql/data’

PostgreSQL, administration alphorm.com™©

# CREATE TABLESPACE app LOCATION ‘/u101/pgsql/data’

Page 53: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

•• Suppression des espaces de tableSuppression des espaces de table

�� Le tablespace doit être vide (pas de table ni d’index)Le tablespace doit être vide (pas de table ni d’index)

�� Suppression du tablespace via la commande DDLSuppression du tablespace via la commande DDL

�� Le répertoire de stockage peut être supprimé Le répertoire de stockage peut être supprimé physiquement avec les commandes de l’OSphysiquement avec les commandes de l’OS

PostgreSQL, administration alphorm.com™©

�� SyntaxeSyntaxe

DROP TABLESPACE [ IF EXISTS] <nom>

Page 54: alphorm.com - Formation PostgreSQL administration

Gestion d’une instance PostgreSQL

•• Modifier des espaces de tableModifier des espaces de table

�� Renommer le tablespaceRenommer le tablespace

�� Changer le propriétaireChanger le propriétaire

�� SyntaxeSyntaxe

ALTER TABLESPACE nomtblspc RENAME nomtblspc2;

PostgreSQL, administration alphorm.com™©

ALTER TABLESPACE nomtblspc RENAME nomtblspc2;ALTER TABLESPACE nomtblspc OWNER nomROLE;

Page 55: alphorm.com - Formation PostgreSQL administration

Gestion des instances sous PostgreSQL

PostgreSQL, administration alphorm.com™©

Site : http://alphorm.comBlog : http://alphorm.com/blogForum : http://alphorm.com/forum

Noureddine DRISSI Expert consultant bases de donnéesContact : [email protected]

Initialisation d’une instance

Page 56: alphorm.com - Formation PostgreSQL administration

Initialisation d'une instance

��Configuration des ressources du noyauConfiguration des ressources du noyau�� PostgreSQLPostgreSQL utilise les ressources IPC de l’OS pour la gestion de la utilise les ressources IPC de l’OS pour la gestion de la

mémoire vivemémoire vive

�� La taille maximale d’un segment de mémoire partagée et positionné La taille maximale d’un segment de mémoire partagée et positionné par défaut à 32 Mopar défaut à 32 Mo

�� Le nombre de pages est positionné par défaut à 2097152 pagesLe nombre de pages est positionné par défaut à 2097152 pages

PostgreSQL, administration alphorm.com™©

�� Nécessité de modifier les paramètres par défautNécessité de modifier les paramètres par défaut

•• shmmaxshmmax taille maximale d’un segment de mémoire partagéetaille maximale d’un segment de mémoire partagée

•• shmallshmall nombre maximale de pages de mémoire partagée (OS)nombre maximale de pages de mémoire partagée (OS)

Page 57: alphorm.com - Formation PostgreSQL administration

Initialisation d'une instance

�� initdbinitdb�� SyntaxeSyntaxe

�� ExempleExemple

initdb -E <codage> --locale=<locale> -D <dir>

$ initdb -E UTF8 -D $PGDATA

PostgreSQL, administration alphorm.com™©

Page 58: alphorm.com - Formation PostgreSQL administration

Initialisation d'une instance

�� initdbinitdb�� SyntaxeSyntaxe

�� ExempleExemple

initdb -E <codage> --locale=<locale> -D <dir>

$ initdb -E UTF8 -D $PGDATA

PostgreSQL, administration alphorm.com™©

Page 59: alphorm.com - Formation PostgreSQL administration

Initialisation d'une instance

��Création d’une instanceCréation d’une instance�� Utilisation du programmeUtilisation du programme initdbinitdb

•• Les optionsLes options

•• --D <dir>D <dir> indique le répertoire de stockage des donnéesindique le répertoire de stockage des données

•• --U <utilisateur>U <utilisateur> indique le nom du super userindique le nom du super user

PostgreSQL, administration alphorm.com™©

•• --E <encodage>E <encodage> indique le jeux de caractères sélectionnéindique le jeux de caractères sélectionné

•• --locale=<locale>locale=<locale> initialise les paramètres locauxinitialise les paramètres locaux

Page 60: alphorm.com - Formation PostgreSQL administration

Initialisation d'une instance

��Démarrage et arrêt d’une instanceDémarrage et arrêt d’une instance�� Utilisation du programme Utilisation du programme pg_ctlpg_ctl

•• --D <dir>D <dir> indique le répertoire de l’instanceindique le répertoire de l’instance

•• --l <journal de trace>l <journal de trace> fichier de log de démarrage de l’instancefichier de log de démarrage de l’instance

•• start, stop, restartstart, stop, restart et et reloadreload permettent de démarrer, d’arrêter, permettent de démarrer, d’arrêter, de redémarrer et de recharger le serveurde redémarrer et de recharger le serveur

PostgreSQL, administration alphorm.com™©

de redémarrer et de recharger le serveurde redémarrer et de recharger le serveur

Page 61: alphorm.com - Formation PostgreSQL administration

Initialisation d'une instance

��pg_ctlpg_ctl�� SyntaxeSyntaxe

�� ExempleExemple

pg_ctl -D $PGDATA start | stop | restart -l <journal trace>

pg_ctl -D /var/lib/pgsql/data stop

PostgreSQL, administration alphorm.com™©

pg_ctl -D /var/lib/pgsql/data start

Page 62: alphorm.com - Formation PostgreSQL administration

Initialisation d'une instance

��pg_ctlpg_ctl�� SyntaxeSyntaxe

�� ExempleExemple

pg_ctl -D $PGDATA start | stop | restart -l <journal trace>

pg_ctl -D /var/lib/pgsql/data start

PostgreSQL, administration alphorm.com™©

Page 63: alphorm.com - Formation PostgreSQL administration

Initialisation d'une instance

��Démarrage et arrêt d’une instanceDémarrage et arrêt d’une instance�� Utilisation du script de démarrage (SysV) LinuxUtilisation du script de démarrage (SysV) Linux

�� WindowsWindows

/etc/init.d/postgresql stop | start | restart | reload

PostgreSQL, administration alphorm.com™©

Page 64: alphorm.com - Formation PostgreSQL administration

Initialisation d'une instance

��Suppression d’une instanceSuppression d’une instance�� LinuxLinux

•• Arrêtez le processus postmaster PostgreSQLArrêtez le processus postmaster PostgreSQL

•• Supprimer tous les fichiers et répertoires à la racine de Supprimer tous les fichiers et répertoires à la racine de l’instance ($PGDATA)l’instance ($PGDATA)

�� WindowsWindows

PostgreSQL, administration alphorm.com™©

�� WindowsWindows

•• Arrêtez le service PostgreSQLArrêtez le service PostgreSQL

•• Supprimer tous les fichiers et répertoire à la racine de l’instance Supprimer tous les fichiers et répertoire à la racine de l’instance ($PGDATA)($PGDATA)

Page 65: alphorm.com - Formation PostgreSQL administration

Initialisation d'une instance

��Suppression d’une instanceSuppression d’une instance�� LinuxLinux

•• Arrêtez le processus postmaster PostgreSQLArrêtez le processus postmaster PostgreSQL

•• Supprimer tous les fichiers et répertoires à la racine de Supprimer tous les fichiers et répertoires à la racine de l’instance ($PGDATA)l’instance ($PGDATA)

�� WindowsWindows

PostgreSQL, administration alphorm.com™©

�� WindowsWindows

•• Arrêtez le service PostgreSQLArrêtez le service PostgreSQL

•• Supprimer tous les fichiers et répertoire à la racine de l’instance Supprimer tous les fichiers et répertoire à la racine de l’instance ($PGDATA)($PGDATA)

Page 66: alphorm.com - Formation PostgreSQL administration

Gestion des instances sous PostgreSQL

PostgreSQL, administration alphorm.com™©

Site : http://alphorm.comBlog : http://alphorm.com/blogForum : http://alphorm.com/forum

Noureddine DRISSI Expert consultant bases de donnéesContact : [email protected]

Sécurisation d’une instance

Page 67: alphorm.com - Formation PostgreSQL administration

Sécurisation d'une instance

��Gérée par Gérée par

�� LLe fichier « e fichier « pg_hba.confpg_hba.conf »»

�� Configure la manière d’authentifier les connexionsConfigure la manière d’authentifier les connexions

�� Par défaut autorise toutes les connexions localesPar défaut autorise toutes les connexions locales

Nécessaire de modifier ce fichier après l’installationNécessaire de modifier ce fichier après l’installation

PostgreSQL, administration alphorm.com™©

�� Nécessaire de modifier ce fichier après l’installationNécessaire de modifier ce fichier après l’installation

�� Les objets rôlesLes objets rôles

�� Pour la connexion et la sécurité au niveau des objetsPour la connexion et la sécurité au niveau des objets

Page 68: alphorm.com - Formation PostgreSQL administration

Sécurisation d'une instance

��Configuration de « Configuration de « pg_hba.confpg_hba.conf » »

�� Format d’une ligne d’authentificationFormat d’une ligne d’authentification

�� Type Type | | Base Base | | User User | | Adresse Adresse | | Méthode Méthode | | OptionOption

•• Colonne « Type »Colonne « Type »

- local : connexion par une socket du domaine Unix (la colonne

PostgreSQL, administration alphorm.com™©

- local : connexion par une socket du domaine Unix (la colonne adresse est vide) ;

- host : connexion chiffrée ou non par une socket TCP/IP ;

- hostssl : connexion chiffrée par une socket TCP/IP ;

- hostnossl : connexion non chiffrée par une socket TCP/IP.

Page 69: alphorm.com - Formation PostgreSQL administration

Sécurisation d'une instance

��Configuration de « Configuration de « pg_hba.confpg_hba.conf » »

�� Format d’une ligne d’authentificationFormat d’une ligne d’authentification

�� Type Type | | Base Base | | User User | | Adresse Adresse | | Méthode Méthode | | OptionOption

•• Colonne « Base »Colonne « Base »

- all ;

PostgreSQL, administration alphorm.com™©

- all ;

- samerole, sameuser ;

- une liste de ces éléments séparés par une virgule.

Page 70: alphorm.com - Formation PostgreSQL administration

Sécurisation d'une instance

��Configuration de « Configuration de « pg_hba.confpg_hba.conf » »

�� Format d’une ligne d’authentificationFormat d’une ligne d’authentification

�� Type Type | | Base Base | | User User | | Adresse Adresse | | Méthode Méthode | | OptionOption

•• Colonne « User »Colonne « User »

- all ;

PostgreSQL, administration alphorm.com™©

- all ;

- le nom d'un groupe (rôle) précédé du caractère « + » ;

- une liste de ces éléments séparés par une virgule.

Page 71: alphorm.com - Formation PostgreSQL administration

Sécurisation d'une instance

��Configuration de « Configuration de « pg_hba.confpg_hba.conf » »

�� Format d’une ligne d’authentificationFormat d’une ligne d’authentification

�� Type Type | | Base Base | | User User | | Adresse Adresse | | Méthode Méthode | | OptionOption

•• Colonne « Adresse »Colonne « Adresse »

-- IP d'origine de la connexion au format CIDRIP d'origine de la connexion au format CIDR

PostgreSQL, administration alphorm.com™©

-- IP d'origine de la connexion au format CIDRIP d'origine de la connexion au format CIDR

-- Doit être vide lorsque le type est « local »Doit être vide lorsque le type est « local »

Page 72: alphorm.com - Formation PostgreSQL administration

Sécurisation d'une instance

��Configuration de « Configuration de « pg_hba.confpg_hba.conf » »

�� Format d’une ligne d’authentificationFormat d’une ligne d’authentification

�� Type Type | | Base Base | | User User | | Adresse Adresse | | Méthode Méthode | | OptionOption

•• Colonne « Méthode »Colonne « Méthode »

- trust : autoriser sans même vérifier le mot de passe ;

PostgreSQL, administration alphorm.com™©

- trust : autoriser sans même vérifier le mot de passe ;

- reject : rejeter la connexion ;

- md5 : vérifier le mot de passe fourni (ne plus utiliser crypt ou password) ;

- ident : utiliser le protocole IDENT pour vérifier l'utilisateur ;

- krb5, pam ou ldap : authentifications spécifiques.

Page 73: alphorm.com - Formation PostgreSQL administration

Sécurisation d'une instance

��Configuration de « Configuration de « pg_hba.confpg_hba.conf » »

�� Format d’une ligne d’authentificationFormat d’une ligne d’authentification

�� Type Type | | Base Base | | User User | | Adresse Adresse | | Méthode Méthode | [| [OptionOption ]]

•• Colonne « Méthode »Colonne « Méthode »

- trust : autoriser sans même vérifier le mot de passe ;

PostgreSQL, administration alphorm.com™©

- trust : autoriser sans même vérifier le mot de passe ;

- reject : rejeter la connexion ;

- md5 : vérifier le mot de passe fourni (ne plus utiliser crypt ou password) ;

- ident : utiliser le protocole IDENT pour vérifier l'utilisateur ;

- krb5, pam ou ldap : authentifications spécifiques.

Page 74: alphorm.com - Formation PostgreSQL administration

Sécurisation d'une instance

��Les sessions Les sessions

�� Connexion des clients via une socket UNIXConnexion des clients via une socket UNIX

�� Port d’écoute par défaut: 5432Port d’écoute par défaut: 5432

�� Quatre informations nécessaires pour ouvrir une sessionQuatre informations nécessaires pour ouvrir une session

Le nom/IP de la machine qui héberge l’instanceLe nom/IP de la machine qui héberge l’instance

PostgreSQL, administration alphorm.com™©

�� Le nom/IP de la machine qui héberge l’instanceLe nom/IP de la machine qui héberge l’instance

�� Le nom de la baseLe nom de la base

�� Le nom d’un rôleLe nom d’un rôle

�� Un mot de passe (si configuré dans « pg_hba.conf »Un mot de passe (si configuré dans « pg_hba.conf »

Page 75: alphorm.com - Formation PostgreSQL administration

Prochain module :

Les outils d’administration sous

PostgreSQL, administration alphorm.com™©

Les outils d’administration sous PostgreSQL

Page 76: alphorm.com - Formation PostgreSQL administration

Les outils d’administration sous PostgreSQL

PostgreSQL, administration alphorm.com™©

Site : http://alphorm.comBlog : http://alphorm.com/blogForum : http://alphorm.com/forum

Noureddine DRISSI Expert consultant bases de donnéesContact : [email protected]

sous PostgreSQL

Page 77: alphorm.com - Formation PostgreSQL administration

Les outils d’administration

��Mode ligne de commandeMode ligne de commande

�� createdb, dropdbcreatedb, dropdb

�� createuser, dropusercreateuser, dropuser

�� reindexdb, vacuumdbreindexdb, vacuumdb

pg_dump, pg_dumpall, pg_restorepg_dump, pg_dumpall, pg_restore

PostgreSQL, administration alphorm.com™©

�� pg_dump, pg_dumpall, pg_restorepg_dump, pg_dumpall, pg_restore

�� initdb, clusterdb, postmaster, pg_ctlinitdb, clusterdb, postmaster, pg_ctl

�� psqlpsql, Droplang, pg_config, pg_resetxlog, pg_controldata, , Droplang, pg_config, pg_resetxlog, pg_controldata, createlang, ipccleancreatelang, ipcclean

Page 78: alphorm.com - Formation PostgreSQL administration

Les outils d’administration

��Mode ligne de commandeMode ligne de commande

�� L’interpréteur de commande « L’interpréteur de commande « psqlpsql »»

�� Les principaux paramètresLes principaux paramètres

� -h <machine>

� -p <port>

PostgreSQL, administration alphorm.com™©

-p <port>

� -d <nom base de données>

� -U <utilisateur>

Page 79: alphorm.com - Formation PostgreSQL administration

Les outils d’administration

��Mode ligne de commandeMode ligne de commande

�� L’interpréteur de commande « L’interpréteur de commande « psqlpsql »»

�� Les variables utilisées par « psql » (.bash_profile)Les variables utilisées par « psql » (.bash_profile)

� $PGDATA

� $PGHOST

PostgreSQL, administration alphorm.com™©

$PGHOST

� $PGDATABASE

� $PGPORT

� $PGUSER

Page 80: alphorm.com - Formation PostgreSQL administration

Les outils d’administration

��GraphiqueGraphique�� pgAdmin IIIpgAdmin III

PostgreSQL, administration alphorm.com™©

Page 81: alphorm.com - Formation PostgreSQL administration

Les outils d’administration

��GraphiqueGraphique�� phpPgAdminphpPgAdmin

PostgreSQL, administration alphorm.com™©

Page 82: alphorm.com - Formation PostgreSQL administration

Prochain module :

La gestion des transactions sous

PostgreSQL, administration alphorm.com™©

La gestion des transactions sous PostgreSQL

Page 83: alphorm.com - Formation PostgreSQL administration

La gestion des transactions sous PostgreSQL

PostgreSQL, administration alphorm.com™©

Site : http://alphorm.comBlog : http://alphorm.com/blogForum : http://alphorm.com/forum

Noureddine DRISSI Expert consultant bases de donnéesContact : [email protected]

sous PostgreSQL

Page 84: alphorm.com - Formation PostgreSQL administration

La gestion des transactions

�� TransactionsTransactions

�� Conforme à la norme ACIDConforme à la norme ACID

•• AtomicitéAtomicité toutes les instructions de la transaction sont validées ou aucune ne toutes les instructions de la transaction sont validées ou aucune ne l'estl'est

•• CohérenceCohérence chaque transaction validée garantit un état cohérent de la base de chaque transaction validée garantit un état cohérent de la base de donnéesdonnées

•• IsolationIsolation les modifications d'une transaction sont invisibles aux autres les modifications d'une transaction sont invisibles aux autres transactionstransactions

PostgreSQL, administration alphorm.com™©

transactionstransactions

•• DurabilitéDurabilité si la transaction est validée, les modifications engendrées sont si la transaction est validée, les modifications engendrées sont pérennespérennes

Page 85: alphorm.com - Formation PostgreSQL administration

La gestion des transactions

�� TransactionsTransactions

�� Par défautPar défaut

•• aautocommitutocommit

�� Une transactionUne transaction

•• Démarrage d’une transaction avec Démarrage d’une transaction avec BEGINBEGIN

PostgreSQL, administration alphorm.com™©

•• COMMIT COMMIT permet de valider une transactionpermet de valider une transaction

•• ROLLBACK ROLLBACK permet d’annuler une transactionpermet d’annuler une transaction

Page 86: alphorm.com - Formation PostgreSQL administration

La gestion des transactions

�� TransactionsTransactions

�� SpécificitésSpécificités

•• Les instructions LDD sont prises en charge par les transactionsLes instructions LDD sont prises en charge par les transactions

•• La fermeture d'une session provoque un rollback impliciteLa fermeture d'une session provoque un rollback implicite

PostgreSQL, administration alphorm.com™©

Page 87: alphorm.com - Formation PostgreSQL administration

La gestion des transactions

�� TransactionsTransactions

�� IsolationIsolation

•• Le standard SQL Le standard SQL

-- read uncommitedread uncommited

-- read commitedread commited

PostgreSQL, administration alphorm.com™©

-- repeatable read repeatable read

-- serializableserializable

Page 88: alphorm.com - Formation PostgreSQL administration

La gestion des transactions

�� TransactionsTransactions

�� IsolationIsolation

�� Modification du niveau d’isolationModification du niveau d’isolation

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }

PostgreSQL, administration alphorm.com™©

Page 89: alphorm.com - Formation PostgreSQL administration

La gestion des transactions

�� Les verrousLes verrous

�� Verrous implicitesVerrous implicites

�� Verrous expliciteVerrous explicite

•• Avec l'instruction Avec l'instruction locklock

•• En utilisant les clauses En utilisant les clauses for updatefor update et et for sharefor share de l'instruction selectde l'instruction select

•• Les verrous ne peuvent être posés que dans le cadre d'une transaction Les verrous ne peuvent être posés que dans le cadre d'une transaction

PostgreSQL, administration alphorm.com™©

•• Les verrous ne peuvent être posés que dans le cadre d'une transaction Les verrous ne peuvent être posés que dans le cadre d'une transaction commencée commencée avec begin.

•• Les verrous sont libérés lorsque une transaction est terminéeLes verrous sont libérés lorsque une transaction est terminée

•• La liste des verrous actifs est consultable dans la vue système La liste des verrous actifs est consultable dans la vue système pg_lockspg_locks

Page 90: alphorm.com - Formation PostgreSQL administration

Prochain module :

Les logs PostgreSQL

PostgreSQL, administration alphorm.com™©

Les logs PostgreSQL

Page 91: alphorm.com - Formation PostgreSQL administration

Gestion de la sécurité sous PostgreSQL

PostgreSQL, administration alphorm.com™©

Site : http://alphorm.comBlog : http://alphorm.com/blogForum : http://alphorm.com/forum

Noureddine DRISSI Expert consultant bases de donnéesContact : [email protected]

PostgreSQL

Page 92: alphorm.com - Formation PostgreSQL administration

Gestion de la sécurité

��Gestion des droits d’accèsGestion des droits d’accès

�� Utilise la notion de rôleUtilise la notion de rôle

�� Peut représenter un utilisateur ou un groupePeut représenter un utilisateur ou un groupe

•• Etre propriétaire d'objets de l'instance (bases de données, Etre propriétaire d'objets de l'instance (bases de données, tables, etc.) tables, etc.)

•• Se voir octroyer des droits d'accès à des objets ou des droit Se voir octroyer des droits d'accès à des objets ou des droit

PostgreSQL, administration alphorm.com™©

•• Se voir octroyer des droits d'accès à des objets ou des droit Se voir octroyer des droits d'accès à des objets ou des droit systèmesystème

•• Etre membre d'un autre rôleEtre membre d'un autre rôle

•• Se voir attribuer un mot de passeSe voir attribuer un mot de passe

•• Non lié au utilisateurs du système d’exploitationNon lié au utilisateurs du système d’exploitation

Page 93: alphorm.com - Formation PostgreSQL administration

Gestion de la sécurité

��Création des rôlesCréation des rôles

�� SyntaxeSyntaxe

CREATE ROLE nom_role [ [ WITH ] [ SUPERUSER ] [ CREATEDB ]

[ CREATEROLE ] [ LOGIN ]

[ ENCRYPTED PASSWORD 'mot_de_passe' ]

PostgreSQL, administration alphorm.com™©

[ IN ROLE nom_role [, ...] ]

[ ROLE nom_role [, ...] ] ]

Page 94: alphorm.com - Formation PostgreSQL administration

Gestion de la sécurité

�� Suppression des rôlesSuppression des rôles

�� SyntaxeSyntaxe

DROP ROLE [ IF EXISTS ] nom

PostgreSQL, administration alphorm.com™©

Page 95: alphorm.com - Formation PostgreSQL administration

Gestion de la sécurité

�� Les rôles groupesLes rôles groupes

�� Modélise la notion de groupe d’utilisateursModélise la notion de groupe d’utilisateurs

�� Suffit de créer un rôle sans mot de passeSuffit de créer un rôle sans mot de passe

�� Sans l’attribut LOGINSans l’attribut LOGIN

PostgreSQL, administration alphorm.com™©

Page 96: alphorm.com - Formation PostgreSQL administration

Gestion de la sécurité

�� Les droitsLes droits

�� Basé sur les rôles et utilise les instructions SQLBasé sur les rôles et utilise les instructions SQL

�� GRANT et REVOKEGRANT et REVOKE

•• Octroyer ou révoquer des droits d'accès sur des objetsOctroyer ou révoquer des droits d'accès sur des objets

•• Ajouter ou retirer des rôles (utilisateurs) à d'autres rôles Ajouter ou retirer des rôles (utilisateurs) à d'autres rôles (groupes)(groupes)

PostgreSQL, administration alphorm.com™©

(groupes)(groupes)

Page 97: alphorm.com - Formation PostgreSQL administration

Gestion de la sécurité

�� Les droitsLes droits

�� Les attributs des rôles:Les attributs des rôles:

• LOGIN / NOLOGIN ;

• SUPERUSER / NOSUPERUSER ;

• CREATEDB / NOCREATEDB ;

PostgreSQL, administration alphorm.com™©

• CREATEROLE / NOCREATEROLE ;

• PASSWORD.

�� Associés aux rôles lors de leur création (CREATE ROLE) ou avec Associés aux rôles lors de leur création (CREATE ROLE) ou avec l'instruction ALTER ROLEl'instruction ALTER ROLE

�� Les rôles qui disposent de l'attribut SUPERUSER contournent le Les rôles qui disposent de l'attribut SUPERUSER contournent le système des privilèges (c'estsystème des privilèges (c'est--àà--dire qu'ils ont tous les privilèges)dire qu'ils ont tous les privilèges)

Page 98: alphorm.com - Formation PostgreSQL administration

Gestion de la sécurité

��Droits, objetsDroits, objets

�� Les privilèges octroyés dépendent du type d’objetLes privilèges octroyés dépendent du type d’objet

•• tables : SELECT | INSERT | UPDATE | DELETE | REFERENCES | tables : SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGERTRIGGER

•• séquences : USAGE | SELECT | UPDATEséquences : USAGE | SELECT | UPDATE

•• base de données : CREATE | CONNECT | TEMPORARYbase de données : CREATE | CONNECT | TEMPORARY

PostgreSQL, administration alphorm.com™©

•• base de données : CREATE | CONNECT | TEMPORARYbase de données : CREATE | CONNECT | TEMPORARY

•• fonctions : EXECUTEfonctions : EXECUTE

•• langage : USAGElangage : USAGE

•• schémas : CREATE | USAGEschémas : CREATE | USAGE

•• espaces de tables : CREATEespaces de tables : CREATE

Page 99: alphorm.com - Formation PostgreSQL administration

Gestion de la sécurité

��Droits, objetsDroits, objets

�� ALL représente tous les privilèges et PUBLIC tous les rôlesALL représente tous les privilèges et PUBLIC tous les rôles

�� L'option WITH GRANT OPTION autorise le ou les rôles cibles à L'option WITH GRANT OPTION autorise le ou les rôles cibles à octroyer ces privilègesoctroyer ces privilèges

PostgreSQL, administration alphorm.com™©

Page 100: alphorm.com - Formation PostgreSQL administration

Gestion de la sécurité

��Droits, objetsDroits, objets

�� ALL représente tous les privilèges et PUBLIC tous les rôlesALL représente tous les privilèges et PUBLIC tous les rôles

�� L'option WITH GRANT OPTION autorise le ou les rôles cibles à L'option WITH GRANT OPTION autorise le ou les rôles cibles à octroyer ces privilègesoctroyer ces privilèges

PostgreSQL, administration alphorm.com™©

Page 101: alphorm.com - Formation PostgreSQL administration

Gestion de la sécurité

��Droits, objetsDroits, objets

�� Révocation des droits avec REVOKERévocation des droits avec REVOKE

�� SyntaxeSyntaxe

REVOKE [ GRANT OPTION FOR ]

{ privilège [,...] | ALL } ON [ type_objet ] nom_objet [, ...]

PostgreSQL, administration alphorm.com™©

FROM { nom_role | PUBLIC } [, ...] [ CASCADE | RESTRICT ]

Page 102: alphorm.com - Formation PostgreSQL administration

Gestion de la sécurité

��Droits, objetsDroits, objets

�� Bonnes pratiquesBonnes pratiques

•• Associer une base de données à un rôleAssocier une base de données à un rôle

•• Associer une base de données à une applicationAssocier une base de données à une application

•• Un schéma est associé à un rôle utilisateurUn schéma est associé à un rôle utilisateur

PostgreSQL, administration alphorm.com™©

•• Les privilèges objets sont donnés à des rôles groupes et non à Les privilèges objets sont donnés à des rôles groupes et non à des rôles LOGINdes rôles LOGIN

Page 103: alphorm.com - Formation PostgreSQL administration

Prochain module :

Sauvegarde restauration sous

PostgreSQL, administration alphorm.com™©

Sauvegarde restauration sous PostgreSQL

Page 104: alphorm.com - Formation PostgreSQL administration

Les logs PostgreSQL

PostgreSQL, administration alphorm.com™©

Site : http://alphorm.comBlog : http://alphorm.com/blogForum : http://alphorm.com/forum

Noureddine DRISSI Expert consultant bases de donnéesContact : [email protected]

Les logs d’activités

Page 105: alphorm.com - Formation PostgreSQL administration

Gestion des logs

�� Les logs d’activitésLes logs d’activités

�� Enregistre les évènements pendant l’exécution du serveurEnregistre les évènements pendant l’exécution du serveur

�� Méthode d’enregistrementMéthode d’enregistrement

•• fsync, stderr, syslog, eventlog (Windows)fsync, stderr, syslog, eventlog (Windows)

�� Niveau de traceNiveau de trace

PostgreSQL, administration alphorm.com™©

•• Différentes nature de messageDifférentes nature de message

•• Possibilité d’ajuster le niveau de tracePossibilité d’ajuster le niveau de trace

Page 106: alphorm.com - Formation PostgreSQL administration

Gestion des logs

�� Les logs d’activitésLes logs d’activités

�� Ou tracer ?Ou tracer ?

•• log_destination (string)log_destination (string) destination des logs (syslog, stderr, etc.)destination des logs (syslog, stderr, etc.)

•• logging_collector (boolean)logging_collector (boolean) autorise la capture et la redirection autorise la capture et la redirection des messages envoyés à stderrdes messages envoyés à stderr

•• log_directorylog_directory (string)(string) détermine le répertoire dans lequel les détermine le répertoire dans lequel les

PostgreSQL, administration alphorm.com™©

•• log_directorylog_directory (string)(string) détermine le répertoire dans lequel les détermine le répertoire dans lequel les fichiers de trace sont créésfichiers de trace sont créés

•• log_filename (string)log_filename (string) indique les noms des journaux applicatifs indique les noms des journaux applicatifs crééscréés

•• ……

Page 107: alphorm.com - Formation PostgreSQL administration

Gestion des logs

�� Les logs d’activitésLes logs d’activités

�� Quand tracer ?Quand tracer ?

•• client_min_messages (enum) client_min_messages (enum) contrôle les niveaux de message contrôle les niveaux de message envoyés au clientenvoyés au client

•• log_min_messages (enum) log_min_messages (enum) contrôle les niveaux de message contrôle les niveaux de message écrits dans les traces du serveurécrits dans les traces du serveur

PostgreSQL, administration alphorm.com™©

écrits dans les traces du serveurécrits dans les traces du serveur

•• log_error_verbosity (enum) log_error_verbosity (enum) contrôle le niveau de détail des contrôle le niveau de détail des traces de chaque messagetraces de chaque message

•• log_min_error_statement (enum) log_min_error_statement (enum) contrôle si l'instruction SQL à contrôle si l'instruction SQL à l'origine d'une erreur doit être enregistrée dans les traces du l'origine d'une erreur doit être enregistrée dans les traces du serveurserveur

•• ……

Page 108: alphorm.com - Formation PostgreSQL administration

Gestion des logs

�� Les logs d’activitésLes logs d’activités

�� Que tracer ?Que tracer ?

•• log_checkpoints (boolean)log_checkpoints (boolean) trace les points de vérification dans trace les points de vérification dans les journaux applicatifsles journaux applicatifs

•• log_connections (boolean)log_connections (boolean) trace chaque tentative de connexion trace chaque tentative de connexion sur le serveur, ainsi que la réussite de l'authentification du sur le serveur, ainsi que la réussite de l'authentification du clientclient

PostgreSQL, administration alphorm.com™©

sur le serveur, ainsi que la réussite de l'authentification du sur le serveur, ainsi que la réussite de l'authentification du clientclient

•• log_duration (boolean)log_duration (boolean) indique le temps de chaque requête indique le temps de chaque requête noté par log_statementnoté par log_statement

•• log_statement (boolean)log_statement (boolean) indique la nature des requête a tracerindique la nature des requête a tracer

•• ……

Page 109: alphorm.com - Formation PostgreSQL administration

Gestion des logs

�� Les logs d’activitésLes logs d’activités

�� Paramètres du fichier de traceParamètres du fichier de trace

•• log_rotation_age (entier)log_rotation_age (entier) indique le temps de vie d’un fichier de indique le temps de vie d’un fichier de traces d’activitéstraces d’activités

•• log_rotation_size (entier)log_rotation_size (entier) indique la taille maximale d’un fichier indique la taille maximale d’un fichier de tracesde traces

PostgreSQL, administration alphorm.com™©

de tracesde traces

•• ……

Page 110: alphorm.com - Formation PostgreSQL administration

La gestion des transactions sous PostgreSQL

PostgreSQL, administration alphorm.com™©

Site : http://alphorm.comBlog : http://alphorm.com/blogForum : http://alphorm.com/forum

Noureddine DRISSI Expert consultant bases de donnéesContact : [email protected]

sous PostgreSQL

Page 111: alphorm.com - Formation PostgreSQL administration

Gestion des logs

�� Les logs binaires ou WALLes logs binaires ou WAL

�� Mécanisme d’écriture des transactionsMécanisme d’écriture des transactions

•• Chaque transaction est écrite dans un fichier binaire, avant que Chaque transaction est écrite dans un fichier binaire, avant que les données ne soient réellement écrites dans les fichiers de les données ne soient réellement écrites dans les fichiers de données et d’indexdonnées et d’index

•• Mécanisme qui garantit l’écriture des donnéesMécanisme qui garantit l’écriture des données

PostgreSQL, administration alphorm.com™©

Mécanisme qui garantit l’écriture des donnéesMécanisme qui garantit l’écriture des données

•• Permet d’obtenir de meilleures performances, tout en Permet d’obtenir de meilleures performances, tout en garantissant l’écriture des donnéesgarantissant l’écriture des données

Page 112: alphorm.com - Formation PostgreSQL administration

Gestion des logs

�� Les logs binaires ou WALLes logs binaires ou WAL

�� Schéma du mécanisme d’écriture des transactionsSchéma du mécanisme d’écriture des transactions

PostgreSQL, administration alphorm.com™©

Page 113: alphorm.com - Formation PostgreSQL administration

Gestion des logs

�� Les logs binaires ou WALLes logs binaires ou WAL

�� CaractéristiquesCaractéristiques

•• Chaque segment WAL a une taille de 16 Mo Chaque segment WAL a une taille de 16 Mo

•• Chaque segment est divisé en pages de 8 Ko Chaque segment est divisé en pages de 8 Ko

•• Les fichiers sont nommés suivant un nombre qui est toujours Les fichiers sont nommés suivant un nombre qui est toujours incrémenté et qui commence à 000000010000000000000000incrémenté et qui commence à 000000010000000000000000

PostgreSQL, administration alphorm.com™©

incrémenté et qui commence à 000000010000000000000000incrémenté et qui commence à 000000010000000000000000

•• Les segments WAL sont stockés dans le répertoire Les segments WAL sont stockés dans le répertoire pg_xlogpg_xlog

•• Le serveur peut en créer plusieurs si nécessaireLe serveur peut en créer plusieurs si nécessaire

Page 114: alphorm.com - Formation PostgreSQL administration

Gestion des logs

�� Les logs binaires ou WALLes logs binaires ou WAL

�� Paramètres de configurationParamètres de configuration

•• fsync(boolean)fsync(boolean) permet de forcer l’écriture des fichiers de traces permet de forcer l’écriture des fichiers de traces binairesbinaires

•• checkpoint_segment(entier)checkpoint_segment(entier) indique l’écart maximum entre indique l’écart maximum entre deux points de contrôledeux points de contrôle

PostgreSQL, administration alphorm.com™©

deux points de contrôledeux points de contrôle

•• checkpoint_timeout(entier)checkpoint_timeout(entier) indique le temps maximal, en indique le temps maximal, en seconde, entre deux points de contrôleseconde, entre deux points de contrôle

•• checkpoint_warning(entier)checkpoint_warning(entier) déclenche l’envoi de messages déclenche l’envoi de messages dans les traces d’activités lorsque les points de contrôles sont dans les traces d’activités lorsque les points de contrôles sont trop fréquentstrop fréquents

•• wal_bufferwal_buffer cache des logs binairescache des logs binaires

Page 115: alphorm.com - Formation PostgreSQL administration

Prochain module :

Les logs PostgreSQL

PostgreSQL, administration alphorm.com™©

Les logs PostgreSQL

Page 116: alphorm.com - Formation PostgreSQL administration

Le cache de donnéesPostgreSQL

PostgreSQL, administration alphorm.com™©

Site : http://alphorm.comBlog : http://alphorm.com/blogForum : http://alphorm.com/forum

Noureddine DRISSI Expert consultant bases de donnéesContact : [email protected]

PostgreSQL

Page 117: alphorm.com - Formation PostgreSQL administration

Le cache de données

�� Le cache PostgreSQLLe cache PostgreSQL

�� shared_buffersshared_buffers zone de travail principale partagéezone de travail principale partagée

�� wal_bufferswal_buffers cache pour les transactions WAL partagécache pour les transactions WAL partagé

�� temp_bufferstemp_buffers cache pour les tables temporaire localecache pour les tables temporaire locale

�� work_memwork_mem cache pour les opérations de triscache pour les opérations de tris

PostgreSQL, administration alphorm.com™©

�� maintenance_work_memmaintenance_work_mem cache pour les opérations de maintenancecache pour les opérations de maintenance

Page 118: alphorm.com - Formation PostgreSQL administration

Le cache de données

�� Le cache PostgreSQLLe cache PostgreSQL

PostgreSQL, administration alphorm.com™©

Page 119: alphorm.com - Formation PostgreSQL administration

Prochain module :

Gestion de la sécurité sous

PostgreSQL, administration alphorm.com™©

Gestion de la sécurité sous PostgreSQL

Page 120: alphorm.com - Formation PostgreSQL administration

Sauvegarde et Restauration sous PostgreSQL

PostgreSQL, administration alphorm.com™©

Site : http://alphorm.comBlog : http://alphorm.com/blogForum : http://alphorm.com/forum

Noureddine DRISSI Expert consultant bases de donnéesContact : [email protected]

Import Export

Page 121: alphorm.com - Formation PostgreSQL administration

Sauvegarde et restauration

�� Trois stratégies de sauvegardes possiblesTrois stratégies de sauvegardes possibles

�� Export à partir d'une base de données ou une instance active en Export à partir d'une base de données ou une instance active en utilisant le programme utilisant le programme pg_dumppg_dump et et pg_dumpallpg_dumpall

�� Sauvegarde des fichiers de l'instance lorsqu'elle est arrêtée Sauvegarde des fichiers de l'instance lorsqu'elle est arrêtée (sauvegarde à froid)(sauvegarde à froid)

�� Sauvegarde à chaud en utilisant des journaux des transactionsSauvegarde à chaud en utilisant des journaux des transactions

PostgreSQL, administration alphorm.com™©

Page 122: alphorm.com - Formation PostgreSQL administration

Sauvegarde et restauration

�� Export avec Export avec pg_dumppg_dump

�� Permet de transférer rapidement des données entre bases de Permet de transférer rapidement des données entre bases de donnéesdonnées

�� Supporte trois types de format, dont le SQLSupporte trois types de format, dont le SQL

�� Utilise les mêmes options de connexion que Utilise les mêmes options de connexion que psqlpsql

�� Simple d'emploi..Simple d'emploi..

PostgreSQL, administration alphorm.com™©

�� Simple d'emploi..Simple d'emploi..

Page 123: alphorm.com - Formation PostgreSQL administration

Sauvegarde et restauration

�� Export avec Export avec pg_dumppg_dump

�� Trois types de formats Trois types de formats

•• Format texte Format texte SQLSQL (par défaut), les données seront restaurées (par défaut), les données seront restaurées avec psqlavec psql

•• Format Format tartar, les données seront restaurées avec pg_restore, les données seront restaurées avec pg_restore

•• Format Format dumpdump spécifique, restauration avec pg_restorespécifique, restauration avec pg_restore

PostgreSQL, administration alphorm.com™©

•• Format Format dumpdump spécifique, restauration avec pg_restorespécifique, restauration avec pg_restore

Page 124: alphorm.com - Formation PostgreSQL administration

Sauvegarde et restauration

�� Export avec Export avec pg_dumpallpg_dumpall

�� Permet Permet d’exporter toutes les bases de données d’une instanced’exporter toutes les bases de données d’une instance

�� Un seul type de format d’export SQLUn seul type de format d’export SQL

PostgreSQL, administration alphorm.com™©

Page 125: alphorm.com - Formation PostgreSQL administration

Sauvegarde et Restauration sous PostgreSQL

PostgreSQL, administration alphorm.com™©

Site : http://alphorm.comBlog : http://alphorm.com/blogForum : http://alphorm.com/forum

Noureddine DRISSI Expert consultant bases de donnéesContact : [email protected]

Sauvegarde à froid

Page 126: alphorm.com - Formation PostgreSQL administration

Sauvegarde et restauration

�� Sauvegarde à froidSauvegarde à froid

�� Arrêt de l’instanceArrêt de l’instance

�� Sauvegarde de la racine de l’instance $PGDATA au niveau systèmeSauvegarde de la racine de l’instance $PGDATA au niveau système

�� Sauvegarde des répertoires des espaces de tables au niveau système Sauvegarde des répertoires des espaces de tables au niveau système

PostgreSQL, administration alphorm.com™©

Page 127: alphorm.com - Formation PostgreSQL administration

Sauvegarde et restauration

��Restauration à partir d’une sauvegarde à froidRestauration à partir d’une sauvegarde à froid

�� Arrêt de l’instanceArrêt de l’instance

�� Suppression de tous les répertoires et fichiers de l’instanceSuppression de tous les répertoires et fichiers de l’instance

•• $PGDATA$PGDATA

•• Espaces de tableEspaces de table

PostgreSQL, administration alphorm.com™©

�� Restaurer les fichiers au niveau systèmeRestaurer les fichiers au niveau système

�� Démarrer l’instanceDémarrer l’instance

Page 128: alphorm.com - Formation PostgreSQL administration

Sauvegarde et Restauration sous PostgreSQL

PostgreSQL, administration alphorm.com™©

Site : http://alphorm.comBlog : http://alphorm.com/blogForum : http://alphorm.com/forum

Noureddine DRISSI Expert consultant bases de donnéesContact : [email protected]

Sauvegarde à chaud

Page 129: alphorm.com - Formation PostgreSQL administration

Sauvegarde et restauration

�� Sauvegarde à chaudSauvegarde à chaud

�� PréPré--requisrequis

•• Activer l'archivage des journaux des transactionsActiver l'archivage des journaux des transactions

- wal_level = archive

- archive_mode = on

PostgreSQL, administration alphorm.com™©

- archive_command = ‘cp %p /archives/%f’

•• Exécuter régulièrement une sauvegarde complète de l'instance.Exécuter régulièrement une sauvegarde complète de l'instance.

Page 130: alphorm.com - Formation PostgreSQL administration

Sauvegarde et restauration

�� Sauvegarde à chaudSauvegarde à chaud

�� Mise en oeuvreMise en oeuvre

•• Exécution de la function SQL Exécution de la function SQL pg_start_backuppg_start_backup

•• Sauvegarde des fichiers de l'instances à l'exception du Sauvegarde des fichiers de l'instances à l'exception du répertoire pg_xlogrépertoire pg_xlog

•• Exécution de la fonction Exécution de la fonction pg_stop_backuppg_stop_backup

PostgreSQL, administration alphorm.com™©

•• Exécution de la fonction Exécution de la fonction pg_stop_backuppg_stop_backup

- Une étiquette est conservée dans le fichier $PGDATA/backup_label.

- Un fichier $PGDATA/pg_xlog/000000010000x.xxxxxx.backup est créé pour indiquer le dernier journal utilisé avant la sauvegarde.

Page 131: alphorm.com - Formation PostgreSQL administration

Sauvegarde et restauration

�� Sauvegarde à chaudSauvegarde à chaud

�� RestaurationRestauration

• Si disponibles, mettre de côté les journaux du répertoire $PGDATA/pg_xlog

• Supprimer tous les fichiers et répertoires de l'instance et ses espaces de tables

• Restaurer la sauvegarde complète en prenant garde à rétablir les permissions

• Supprimer les fichiers dans $PGDATA/pg_xlog ou recréer ce répertoire

PostgreSQL, administration alphorm.com™©

• S'assurer que le répertoire $PGDATA/pg_xlog/archive_status existe

• Recopier les journaux éventuellement mis de côté dans $PGDATA/pg_xlog

• Créer un fichier recovery.conf dans $PGDATA à partir de recovery.conf.sample

• Démarrer PostgreSQL en s'assurant que les utilisateurs ne pourront se connecter

Page 132: alphorm.com - Formation PostgreSQL administration

Sauvegarde et restauration

�� Sauvegarde à chaudSauvegarde à chaud

�� RestaurationRestauration

•• Le fichier Le fichier recovery.confrecovery.conf

- restore_command = 'cp /archive/%f %p'

- recovery_target_time = '2008-04-23 11:20:55 EST'

PostgreSQL, administration alphorm.com™©

Page 133: alphorm.com - Formation PostgreSQL administration

Sauvegarde et restauration

�� Les étapes de notre TPLes étapes de notre TP�� Je met l’instance en mode ARCHIVELOGJe met l’instance en mode ARCHIVELOG

�� Je lance une transaction Je lance une transaction T1 T1 insertinsert

�� Je démarre Le mode sauvegarde à chaud sous Je démarre Le mode sauvegarde à chaud sous PostgreSQLPostgreSQL select select pg_start_backuppg_start_backup()()

�� Je réalise la sauvegarde au niveau du système d’exploitation Je réalise la sauvegarde au niveau du système d’exploitation tar tar

�� J’arrête le mode sauvegarde à chaud J’arrête le mode sauvegarde à chaud select select pg_stop_backuppg_stop_backup()()

Je lance une transaction Je lance une transaction T2 T2 insertinsert

PostgreSQL, administration alphorm.com™©

�� Je lance une transaction Je lance une transaction T2 T2 insertinsert

�� Je bascule vers un nouveau fichier journal Je bascule vers un nouveau fichier journal select select pg_switch_xlogpg_switch_xlog

�� Je lance une transaction Je lance une transaction T3 T3 insertinsert

�� Je Je bascule vers un nouveau fichier bascule vers un nouveau fichier journal journal select select pg_switch_xlogpg_switch_xlog

�� Je lance une transaction Je lance une transaction T4 T4 insertinsert

�� Je Je bascule vers un nouveau fichier bascule vers un nouveau fichier journal journal select select pg_switch_xlogpg_switch_xlog

Page 134: alphorm.com - Formation PostgreSQL administration

Prochain module :

Administrer au quotidien PostgreSQL

PostgreSQL, administration alphorm.com™©

Administrer au quotidien PostgreSQL

Page 135: alphorm.com - Formation PostgreSQL administration

Administrer au quotidien PostgreSQL

PostgreSQL, administration alphorm.com™©

Site : http://alphorm.comBlog : http://alphorm.com/blogForum : http://alphorm.com/forum

Noureddine DRISSI Expert consultant bases de donnéesContact : [email protected]

Les bases de données

Page 136: alphorm.com - Formation PostgreSQL administration

Administrer au quotidien

��Pour chaque instance il existe les basesPour chaque instance il existe les bases

�� postgrespostgres

�� template0template0

�� template1template1

•• Les relations (tables, indexes, etc.) sont stockées dans les bases Les relations (tables, indexes, etc.) sont stockées dans les bases de donnéesde données

PostgreSQL, administration alphorm.com™©

de donnéesde données

•• Pour chaque base, il existe au mois le schéma Pour chaque base, il existe au mois le schéma publicpublic et les et les catalogues (catalogues (information_schemainformation_schema et et pg_catalogpg_catalog))

Page 137: alphorm.com - Formation PostgreSQL administration

Administrer au quotidien

�� Les relationsLes relations

�� Désigne des objets (tables, indexes, sequences)Désigne des objets (tables, indexes, sequences)

•• Les données de chaque relation sont stockées dans un fichier Les données de chaque relation sont stockées dans un fichier identifié par son OIDidentifié par son OID

-- $PGDATA/base/oid_bdd/filenode_relation$PGDATA/base/oid_bdd/filenode_relation

•• Exception pour la base de données postgresException pour la base de données postgres

PostgreSQL, administration alphorm.com™©

•• Exception pour la base de données postgresException pour la base de données postgres

-- $PGDATA/global$PGDATA/global

Page 138: alphorm.com - Formation PostgreSQL administration

Administrer au quotidien

��Création des bases de donnéesCréation des bases de données

�� SyntaxeSyntaxe

CREATE DATABASE nom

[ [ WITH ] [ OWNER [=] utilisateur ] [ TEMPLATE [=] modèle ]

[ ENCODING [=] encodage ] [ TABLESPACE [=] espace_de_tables ]

PostgreSQL, administration alphorm.com™©

�� Le propriétaire par défaut est l'utilisateur connectéLe propriétaire par défaut est l'utilisateur connecté

�� La base de données modèle par défaut est La base de données modèle par défaut est template1template1

�� L'encodage par défaut est celui de l'instanceL'encodage par défaut est celui de l'instance

�� L'espace de tables par défaut est L'espace de tables par défaut est pg_defaultpg_default

�� Disposer du droit createdb pour pouvoir créer une baseDisposer du droit createdb pour pouvoir créer une base

Page 139: alphorm.com - Formation PostgreSQL administration

Administrer au quotidien

�� Suppression des bases de donnéesSuppression des bases de données

�� SyntaxeSyntaxe

DROP DATABASE [ IF EXISTS] nom

�� Aucune connexion sur la base de données à supprimerAucune connexion sur la base de données à supprimer

Seul l’administrateur ou le propriétaire peut supprimer une base de donnéesSeul l’administrateur ou le propriétaire peut supprimer une base de données

PostgreSQL, administration alphorm.com™©

�� Seul l’administrateur ou le propriétaire peut supprimer une base de donnéesSeul l’administrateur ou le propriétaire peut supprimer une base de données

Page 140: alphorm.com - Formation PostgreSQL administration

Administrer au quotidien

�� Les schémasLes schémas

�� Espace de nom logiqueEspace de nom logique

�� Permet d’avoir plusieurs objets portant le même nom dans une Permet d’avoir plusieurs objets portant le même nom dans une même base de donnéesmême base de données

�� Les objets sont accessible en préfixant le nom de l’objet par le nom Les objets sont accessible en préfixant le nom de l’objet par le nom du schéma « schéma.objet »du schéma « schéma.objet »

PostgreSQL, administration alphorm.com™©

�� La variable La variable search_pathsearch_path est utilisée pour la recherche dans les est utilisée pour la recherche dans les schémasschémas

Page 141: alphorm.com - Formation PostgreSQL administration

Administrer au quotidien

��Création des schémasCréation des schémas

�� SyntaxeSyntaxe

CREATE SCHEMA nom [ AUTHORIZATION utilisateur ]

Le propriétaire par défaut est l'utilisateur connectéLe propriétaire par défaut est l'utilisateur connecté

PostgreSQL, administration alphorm.com™©

�� Le propriétaire par défaut est l'utilisateur connectéLe propriétaire par défaut est l'utilisateur connecté

�� Pour créer un schémaPour créer un schéma

•• Être administrateurÊtre administrateur

•• Avoir le privilège Avoir le privilège createcreate sur la base de données ciblesur la base de données cible

Page 142: alphorm.com - Formation PostgreSQL administration

Administrer au quotidien

�� Suppression des schémasSuppression des schémas

�� SyntaxeSyntaxe

DROP SCHEMA [ IF EXISTS ] nom [ CASCADE ]

�� Seul l’administrateur ou le propriétaire peu supprimer un schémaSeul l’administrateur ou le propriétaire peu supprimer un schéma

L’option CASCADE permet de supprimer un schéma qui possède des objetsL’option CASCADE permet de supprimer un schéma qui possède des objets

PostgreSQL, administration alphorm.com™©

�� L’option CASCADE permet de supprimer un schéma qui possède des objetsL’option CASCADE permet de supprimer un schéma qui possède des objets

Page 143: alphorm.com - Formation PostgreSQL administration

Administrer au quotidien

�� Suppression des schémasSuppression des schémas

�� SyntaxeSyntaxe

DROP SCHEMA [ IF EXISTS ] nom [ CASCADE ]

�� Seul l’administrateur ou le propriétaire peu supprimer un schémaSeul l’administrateur ou le propriétaire peu supprimer un schéma

L’option CASCADE permet de supprimer un schéma qui possède des objetsL’option CASCADE permet de supprimer un schéma qui possède des objets

PostgreSQL, administration alphorm.com™©

�� L’option CASCADE permet de supprimer un schéma qui possède des objetsL’option CASCADE permet de supprimer un schéma qui possède des objets