• Quelle histoire !
  • parmi les plus vieux logiciels libres
  • et les plus sophistiqués
• Souvent cité comme exemple
  • qualité du code
  • indépendance des développeurs
  • réactivité de la communauté

1. Origines et historique du projet
2. Versions et feuille de route
3. Concepts de base
4. Fonctionnalités
5. Sponsors et références

• Comprendre les origines du projet
• Revoir les principes fondamentaux
• Découvrir des exemples concrets

• La licence
• L'origine du nom
• Les origines du projet
• Les principes
• La philosophie

• Licence PostgreSQL
  • BSD / MIT
  • http://www.postgresql.org/about/licence/
• Droit de
  • utiliser, copier, modifier, distribuer sans coût de licence
• Reconnu par l'Open Source Initiative
  • http://opensource.org/licenses/PostgreSQL
• Utilisé par un grand nombre de projets de l'écosystème

• Michael Stonebraker recode Ingres
• post « ingres » => postingres => postgres
• postgres => PostgreSQL

• Sécurité des données (ACID)
• Respect des normes (ISO SQL)
• Fonctionnalités
• Performances
• Simplicité du code

• Années 1970 : Ingres est développé à Berkeley
• 1985 : Postgres succède à Ingres
• 1995 : Ajout du langage SQL.
• 1996 : Postgres devient PostgreSQL
• 1996 : Création du PostgreSQL Global Development Group

Apparitions de la communauté internationale

• ~ 2000: Communauté japonaise
• 2004 : Communauté francophone
• 2006 : SPI
• 2007 : Communauté italienne
• 2008 : PostgreSQL Europe et US
• 2009 : Boom des PGDay

• Versions obsolètes : 9.2 et antérieures
• Versions actuelles : de 9.3 à 10
• Version en cours de développement : 11
• Versions dérivées

• 1996 : v1.0 -> première version publiée
• 2003 : v7.4 -> première version réellement stable
• 2005 : v8.0 -> arrivée sur Windows
• 2008 : v8.3 -> performance et fonctionnalités
• 2010 : v9.0 -> réplication intégrée
• 2016 : v9.6 -> parallélisation
• 2017 : v10 -> réplication logique

• Avant la version 10
  • X.Y : version majeure (8.4, 9.6)
  • X.Y.Z : version mineure (9.6.4)
• Après la version 10
  • X : version majeure (10, 11)
  • X.Y : version mineure (10.1)

• Dernières releases (9 novembre 2017) :
  • version 9.3.20
  • version 9.4.15
  • version 9.5.10
  • version 9.6.6
  • version 10.1
• Prochaine sortie, 8 février 2018

• Juillet 2009 - juillet 2014 (cette version n'est plus maintenue)
• Fonctions Window (clause OVER)
• CTE (vues non persistantes) et requêtes récursives
• Infrastructure SQL/MED (données externes)
• Paramètres par défaut et nombre variant de paramètres pour les fonctions
• Restauration parallélisée d'une sauvegarde
• Droits sur les colonnes

• Septembre 2010 - septembre 2015
• Hot Standby + Streaming Replication
• Contraintes d'exclusion
• Améliorations pour l'EXPLAIN
• Contrainte UNIQUE différable
• Droits par défaut, GRANT ALL
• Triggers sur colonne, et clause WHEN

• Septembre 2011 - septembre 2016
• Réplication synchrone
• Supervision et administration plus aisée de la réplication
• Gestion des extensions
• Support des tables distantes via SQL/MED
• Support des labels de sécurité
• Support des tables non journalisées

• Septembre 2012 - septembre 2017
• Réplication en cascade
  • pg_basebackup utilisable sur un esclave
  • réplication synchrone en mémoire seulement sur l'esclave
• Axé performances
  • amélioration de la scalabilité (lecture et écriture)
  • parcours d'index seuls
• Support de la méthode d'accès SP-GiST pour les index
• Support des types d'intervalles de valeurs
• Support du type de données JSON

• Septembre 2013 - septembre 2018 (?)
• Meilleure gestion de la mémoire partagée
• Support de la clause LATERAL dans un SELECT
• 4 To maxi au lieu de 2 Go pour les Large Objects
• COPY FREEZE
• Vues en mise à jour
• Vues matérialisées

• décembre 2014 - décembre 2019 (?)
• amélioration des index GIN (taille réduite et meilleures performances)
• nouveau type JSONB
• rafraîchissement sans verrou des vues matérialisées
• possibilité pour une instance répliquée de mémoriser la position des instances secondaires (replication slots)
• décodage logique (première briques pour la réplication logique intégrée)

• Janvier 2016 - Janvier 2021 (?)
• Row Level Security
• Index BRIN
• INSERT ... ON CONFLICT { UPDATE | IGNORE }
• SKIP LOCKED
• SQL/MED
  • import de schéma, héritage
• Supervision
  • amélioration de pg_stat_statements, ajout de pg_stat_ssl
• fonctions OLAP (GROUPING SETS, CUBE et ROLLUP)

• Septembre 2016 - Septembre 2021 (?)
• Parallélisation
  • parcours séquentiel, jointure, agrégation
• SQL/MED
  • tri distant, jointures impliquant deux tables distantes
• Réplication synchrone
• MVCC
  • VACUUM FREEZE, CHECKPOINT, ancien snapshot
• Maintenance

• Septembre 2017 - Septembre 2022 (?)
• Meilleure parallélisation
  • parcours d'index, jointure MergeJoin, sous-requêtes corrélées
• Réplication logique
• Partitionnement

• Versions 7
  • fondations
  • durabilité
• Versions 8
  • fonctionnalités
  • performances
• Versions 9
  • réplication physique
  • extensibilité
• Versions 10
  • réplication logique
  • parallélisation

• 9.2 et inférieures
  • Danger !
• 9.3
  • planifier une migration rapidement
• 9.4, 9.5 et 9.6
  • mise à jour uniquement
• 10
  • nouvelles installations et nouveaux développements

• Compatibilité Oracle
  • EnterpriseDB Postgres Plus
• Data warehouse
  • Greenplum
  • Netezza
  • Amazon RedShift

• ACID
• MVCC
• Transactions
• Journaux de transactions

• Atomicité (Atomic)
• Cohérence (Consistent)
• Isolation (Isolated)
• Durabilité (Durable)

• Le « noyau » de PostgreSQL
• Garantit ACID
• Permet les écritures concurrentes sur la même table

• Une lecture ne bloque pas une écriture
• Une écriture ne bloque pas une lecture
• Une écriture ne bloque pas les autres écritures...
• ...sauf pour la mise à jour de la même ligne.

• Intimement liées à ACID et MVCC :
• Une transaction est un ensemble d'opérations atomique
• Le résultat d'une transaction est « tout ou rien »
• SAVEPOINT disponible pour sauvegarde des modifications d'une transaction à un instant t

• Chaque transaction (et donc session) est isolée à un certain point :
  • elle ne voit pas les opérations des autres
  • elle s'exécute indépendamment des autres
• On peut spécifier le niveau d'isolation au démarrage d'une transaction:
  • BEGIN ISOLATION LEVEL xxx;
• Niveaux d'isolation supportés
  • read commited
  • repeatable read
  • serializable

• Chaque donnée est écrite 2 fois sur le disque !
• Sécurité quasiment infaillible
• Comparable à la journalisation des systèmes de fichiers

• Un seul sync sur le fichier de transactions
• Le fichier de transactions est écrit de manière séquentielle
• Les fichiers de données sont écrits de façon asynchrone
• Point In Time Recovery
• Réplication (WAL shipping)

• Développement
• Sécurité
• Le « catalogue » d'objets SQL

• PostgreSQL est une plate-forme de développement !
• 15 langages de procédures stockées
• Interfaces natives pour ODBC, JDBC, C, PHP, Perl, etc.
• API ouverte et documentée
• Un nouveau langage peut être ajouté sans recompilation de PostgreSQL

Création de types de données et

• de leurs fonctions
• de leurs opérateurs
• de leurs règles
• de leurs aggrégats
• de leurs méthodes d'indexations

• Fichier pg_hba.conf
• Filtrage IP
• Authentification interne (MD5, SCRAM-SHA-256)
• Authentification externe (identd, LDAP, Kerberos, ...)
• Support natif de SSL

• Excellent support du SQL ISO
• Objets SQL
  • tables, vues, séquences, triggers
• Opérations
  • jointures, sous-requêtes, requêtes CTE, requêtes de fenêtrage, etc.
• Unicode et plus de 50 encodages

• Espace de noms
• Concept différent des schémas d'Oracle
• Sous-ensemble de la base

• Masquer la complexité
  • structure : interface cohérente vers les données, même si les tables évoluent
  • sécurité : contrôler l'accès aux données de manière sélective
• Améliorations en 9.3 et 9.4
  • vues matérialisées
  • vues automatiquement modifiables

Les algorithmes suivants sont supportés :

• B-tree (par défaut)
• GiST / SP-GiST
• Hash
• GIN (version 8.2)
• BRIN (version 9.5)

• CHECK : prix > 0
• NOT NULL : id_client NOT NULL
• Unicité : id_client UNIQUE
• Clés primaires : UNIQUE NOT NULL ==> PRIMARY KEY (id_client)
• Clés étrangères : produit_id REFERENCES produits(id_produit)
• EXCLUDE : EXCLUDE USING gist (room WITH =, during WITH &&)

• Types créés par les utilisateurs
• Permettent de créer un nouveau type à partir d'un type de base
• En lui ajoutant des contraintes supplémentaires.

• Types créés par les utilisateurs
• Permettent de définir une liste ordonnée de valeurs de type chaîne de caractère pour ce type

• Opérations: INSERT, COPY, UPDATE, DELETE
• 8.4, trigger TRUNCATE
• 9.0, trigger pour une colonne, et/ou avec condition
• 9.1, trigger sur vue
• 9.3, trigger DDL
• 10, tables de transition
• Effet sur :
  • l'ensemble de la requête (FOR STATEMENT)
  • chaque ligne impactée (FOR EACH ROW)

• Sponsors
• Références
  • françaises
  • et internationales

• NTT (Streaming Replication)
• Crunchy Data Solutions (Tom Lane, Stephen Frost, Joe Conway, Greg Smith)
• Microsoft Skype Division (projet skytools)
• EnterpriseDB (Bruce Momjian, Dave Page...)
• 2nd Quadrant (Simon Riggs...)
• VMWare (Heikki Linnakangas)
• Dalibo
• Fujitsu
• Red Hat
• Sun Microsystems (avant le rachat par Oracle)

• Yahoo
• Météo France
• RATP
• CNAF
• Le Bon Coin
• Instagram
• Zalando
• TripAdvisor

Site de petites annonces :

• Base transactionnelle de 6 To
• 4è site le plus consulté en France (2017)
• 800 000 nouvelles annonces par jour
• 4 serveurs PostgreSQL en réplication
  • 160 cœurs par serveur
  • 2 To de RAM
  • 10 To de stockage flash

• Un projet de grande ampleur
• Un SGBD complet
• Souplesse, extensibilité
• De belles références
• Une solution stable, ouverte, performante et éprouvée

• Documentation officielle (préface)
• Articles fondateurs de M. Stonebracker
• Présentation du projet PostgreSQL

N'hésitez pas, c'est le moment !

• Installation depuis les sources
• Installation depuis les binaires
  • installation à partir de packages
  • installation sous windows
• Premiers réglages
• Mises à jours

Étapes :

• Téléchargement
• Vérification des pré-requis
• Compilation
• Installation

• Disponible via :
  • HTTP
  • FTP
• Télécharger le fichier postgresql-X.Y.Z.tar.bz2

• Processus standard :

$ tar xvfj postgresql-<version>.tar.bz2
$ cd postgresql-<version>
$ ./configure
$ make
$ make install

• Quelques options de configuration notables:
  • --prefix=répertoire
  • --with-pgport=port
  • --with-openssl
  • --enable-nls
  • --with-perl
• Pour retrouver les options de compilation a postériori

$ pg_config --configure

• Exécution de tests unitaires
• Permet de vérifier l'état des exécutables construits
• Action check de la commande make

$ make check

• Ajout d'un utilisateur
  • lancera PostgreSQL
  • sera le propriétaire des répertoires et fichiers
• Variables d'environnement

export PATH=/usr/local/pgsql/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LD_LIBRARY_PATH
export MANPATH=$MANPATH:/usr/local/pgsql/share/man
export PGDATA=/usr/local/pgsql/data

• Outil initdb
• Création du répertoire

$ initdb --data /usr/local/pgsql/data

• Options d'emplacement
  • --data pour les fichiers de données
  • --waldir pour les journaux de transactions
• Option sécurité
  • --pwprompt pour configurer immédiatement le mot de passe de l'utilisateur postgres
  • --data-checksums pour ajouter des sommes de contrôle sur les fichier de données

• Script de démarrage

# /etc/init.d/postgresql [action]

• Outil pg_ctl

$ pg_ctl --pgdata /usr/local/pgsql/data --log logfile [action]

• [action] dépend du besoin
  • start pour démarrer
  • stop pour arrêter
  • reload pour recharger la configuration
  • restart pour redémarrer

• Packages Debian
• Packages RPM

• Différents paquets disponibles
  • serveur, client, contrib, docs
  • et les extensions, outils
• apt-get install postgresql-<version majeure>
  • installe les binaires
  • crée l'utilisateur « postgres »
  • exécute initdb
  • démarre le serveur
• Particularités
  • wrappers/scripts pour la gestion des différentes instances
  • plusieurs versions majeures installables
  • respect de la FHS

• La communauté met des paquets Debian à disposition :
  • http://wiki.postgresql.org/wiki/Apt
• Synchrone avec le projet PostgreSQL
• Ajout du dépôt dans /etc/apt/sources.list.d/pgdg.list :
  • deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main

• Différents paquets disponibles
  • serveur, client, contrib, docs
  • et les extensions, outils
• yum install postgresqlxx-server
  • installe les binaires
  • crée l'utilisateur « postgres »
• Opérations manuelles
  • initdb
  • lancement du serveur
• Particularités
  • fichiers de configuration des instances
  • plusieurs versions majeures installables
  • respect du stockage PostgreSQL

• La communauté met des paquets RedHat à disposition :
  • http://yum.postgresql.org
• Synchrone avec le projet PostgreSQL
• Ajout du dépôt grâce aux paquets RPM disponibles

• Disponible pour les différentes versions NT de Windows (2003 server, 2008 server, 2012 server, etc).
• Deux installeurs graphiques disponibles
  • EnterpriseDB
  • BigSQL
• Ou archive des binaires

• Sécurité
• Configuration minimale
• Démarrage
• Test de connexion

• Politique de mot de passe
  • pour l'utilisateur postgres système
  • pour le rôle postgres
• Règles d'accès à l'instance dans pg_hba.conf

• Fichier postgresql.conf
• Configuration du moteur
• Plus de 200 paramètres
• Quelques paramètres essentiels

• listen_addresses = '*'
• port = 5432
• max_connections = 100

• shared_buffers
• work_mem
• maintenance_work_mem

• Paramètre : fsync

• log_destination
• logging_collector
• log_line_prefix
• lc_messages

• autovacuum
• stats collector

• Recommandations
• Mise à jour mineure
• Mise à jour majeure

• Les « Release Notes »
• Intégrité des données
• Bien redémarrer le serveur !

• Méthode
  • arrêter PostgreSQL
  • mettre à jour les binaires
  • redémarrer PostgreSQL
• Pas besoin de s'occuper des données, sauf cas exceptionnel
  • bien lire les « Release Notes » pour s'en assurer

• Bien lire les « Release Notes »
• Bien tester l'application avec la nouvelle version
  • rechercher les régressions en terme de fonctionnalités et de performances
• Pour mettre à jour
  • mise à jour des binaires
  • et mise à jour/traitement des fichiers de données
• 3 méthodes
  • dump/restore
  • Slony
  • pg_upgrade

• Méthode historique
• Simple et sans risque
  • mais d'autant plus longue que le volume de données est important
• Outils pg_dump (ou pg_dumpall) et pg_restore

• Nécessite d'utiliser l'outil de réplication Slony
• Permet un retour en arrière immédiat sans perte de données

• Outil développé par la communauté depuis la version 8.4
  • et fourni avec PostgreSQL
• Prend comme prérequis que le format de stockage des fichiers de données utilisateurs ne change pas entre versions
• Nécessite les deux versions sur le même serveur

• L'installation est simple....
• ... mais elle doit être soigneusement préparée
• Préférer les paquets officiels.
• Attention aux données lors d'une mise à jour !

• Documentation officielle, chapitre Installation
• Documentation Dalibo, pour l'installation sur Windows

N'hésitez pas, c'est le moment !

Les outils graphiques et console :

• les outils graphiques d'administration ;
• la console ;
• les outils de contrôle de l'activité ;
• les outils DDL ;
• le précompilateur ;
• les outils de maintenance.

• Outils en ligne de commande de PostgreSQL
• Réaliser des scripts
• Outils graphiques

• Plusieurs outils PostgreSQL en ligne de commande existent
  • une console interactive
  • outils de maintenance
  • des outils de sauvegardes/restauration
  • outils de gestion des bases

• createdb: ajouter une nouvelle base de données
• createlang: ajouter un langage de procédures à une base (<v10)
• createuser: ajouter un nouveau compte utilisateur
• dropdb: supprimer une base de données
• droplang: supprimer un langage de procédures (<v10)
• dropuser: supprimer un compte utilisateur

• Pour une instance
  • pg_dumpall: sauvegarder l'instance PostgreSQL
• Pour une base de données
  • pg_dump: sauvegarder une base de données
  • pg_restore: restaurer une base de données PostgreSQL

• Maintenance des bases
  • vacuumdb: récupérer l'espace inutilisé (VACUUM FULL) et/ou mettre à jour les statistiques de l'optimiseur (ANALYZE)
  • reindexdb: réindexer une base de données PostgreSQL
  • clusterdb: réorganiser une table en fonction d'un index
• Maintenance de l'instance
  • pg_ctl: lancer, arrêter, relancer, promouvoir le serveur PostgreSQL

• Ces outils s'appuient sur:
  • des options en ligne de commande
  • des variables d'environnement
  • des valeurs par défaut

• En interactif
  • -W | --password
  • -w | --no-password
• Variable $PGPASSWORD
• Fichier .pgpass
  • chmod 600 .pgpass
  • nom_hote:port:database:nomutilisateur:motdepasse

• Un outil simple pour
  • les opérations courantes,
  • les tâches de maintenance,
  • les tests.

postgres$ psql
  base=#

• Obtenir de l'aide sur les commandes internes psql
  • \? [motif]
• Obtenir de l'aide sur les ordres SQL
  • \h [motif]
• Quitter
  • \q

• Spécifier le jeu de caractère
  • \encoding [ENCODING]
• Modifier le mot de passe d'un utilisateur
  • \password [USERNAME]
• Obtenir des informations sur la connexion courante:
  • \conninfo
• Se connecter à une autre base
  • \connect [DBNAME|- USER|- HOST|- PORT|-]
  • \c [DBNAME|- USER|- HOST|- PORT|-]

• Chronométrer les requêtes
  • \timing
• Exécuter une commande OS
  • \! [COMMAND]
• Changer de répertoire courant
  • \cd [DIR]

• Lister les bases de données
  • \l
• Lister les schémas
  • \dn
• Lister uniquement les tables|index|séquences|vues|vues matérialisées [systèmes]
  • \d{t|i|s|v|m}[S][+] [motif]
• Lister les fonctions
  • \df[+] [motif]
• Lister des fonctions d’agrégats
  • \da [motif]

• Lister les rôles
  • \du[+]
• Lister les droits d'accès
  • \dp
• Lister les droits d'accès par défaut
  • \ddp
• Lister les configurations par rôle et par base
  • \drds

• Lister les tablespaces
  • \db
• Lister les extensions
  • \dx

• Type, domaines et opérateurs
  • \dD[S+]
  • \dT[S+]
  • \do [motif]
• Lister les objets FTS
  • \dF [motif]
  • \dFd [motif]
  • \dFt [motif]
  • \dFp [motif]
• Conversions
  • \dc [motif]
  • \dC [motif]

• Code d'une vue
  • \sv
• Code d'une procédure stockée
  • \sf

• Exécuter une requête
  • terminer une requête par ;
  • ou par \g
  • ou encore par \gx
• Rappel des requêtes:
  • flèche vers le haut
  • ctrl-R suivi d'un extrait de texte représentatif

• Exécuter le résultat d'une requête
  • \gexec
• Apparaît en 9.6

• Éditer
  • dernière requête : \e
  • vue : \ev nom_vue
  • fonction PL/pgSQL : \ef nom_fonction
• Exécuter le contenu du tampon
  • \g [FICHIER]
• Afficher le tampon
  • \p
• Sauvegarder le contenu du tampon
  • \w [FICHIER]
• Supprimer le contenu du tampon
  • \r

• Charger et exécuter un script SQL
  • \i FICHIER
• Rediriger la sortie dans un fichier
  • \o FICHIER
• Écrire un texte sur la sortie standard
  • \echo texte...
• Écrire un texte dans le fichier
  • \qecho texte...

• Positionner des variables internes
  • \set [NOM [VALEUR]]
• Invalider une variable interne
  • \unset NOM
• Variables internes usuelles
  • ON_ERROR_STOP: on ou off
  • ON_ERROR_ROLLBACK: on, off ou interactive
  • AUTOCOMMIT: on ou off
• Liste des variables: http://docs.postgresql.fr/current/app-psql.html#app-psql-variables

• \if
• \elif
• \else
• \endif

• psql est personnalisable
• Au démarrage, psql lit dans le ${HOME}
  • .psqlrc-X.Y
  • .psqlrc-X
  • .psqlrc
• .psqlrc contient des méta-commandes \set
  • \set ON_ERROR_ROLLBACK interactive

• Script SQL
• Script Shell
• Exemple sauvegarde

• Exécuter un seul ordre SQL
  • -c "ordre SQL"
• Spécifier un script SQL en ligne de commande
  • -f nom_fichier.sql
• Possible de les spécifier plusieurs fois
  • exécutés dans l'ordre d'apparition
  • à partir de la version 9.6
• Charger et exécuter un script SQL depuis psql
  • \i nom_fichier.sql

• psql est en mode auto-commit par défaut
  • variable AUTOCOMMIT
• Ouvrir une transaction explicitement
  • BEGIN;
• Terminer une transaction
  • COMMIT;
• Ouvrir une transaction implicitement
  • option -1 ou --single-transaction

• Attention à l'encodage des caractères
  • \encoding
  • SET client_encoding
• Écriture des requêtes
• Écrire du code procédural avec DO

• Positionner des variables

  \set nom_table 'ma_table'
  SELECT * FROM :"nom_table";
  \set valeur_col1 'test'
  SELECT * FROM :"nom_table" WHERE col1 = :'valeur_col1';

• Demander la valeur d'une variable à l'utilisateur
  • \prompt 'invite' nom_variable
• Retirer la référence à une variable
  • \unset variable

• Ignorer les erreurs dans une transaction
  • ON_ERROR_ROLLBACK
• Gérer des erreurs SQL en shell
  • ON_ERROR_STOP

• Afficher des résultats non alignés
  • -A | --no-align
• Afficher uniquement les lignes
  • -t | --tuples-only
• Utiliser le format étendu
  • -x | --expanded
• Utiliser une sortie au format HTML
  • -H | --html
• Positionner un attribut de tableau HTML
  • -T TEXT | --table-attr TEXT

• Modifier le séparateur de colonnes
  • -F CHAINE | --field-separator CHAINE
• Forcer un octet 0x00 comme séparateur de colonnes
  • -z | --field-separator-zero
• Modifier le séparateur de lignes
  • -R CHAINE | --record-separator CHAINE
• Forcer un octet 0x00 comme séparateur de lignes
  • -0 | --record-separator-zero

• \crosstabview [ colV [ colH [ colD [ sortcolH ] ] ] ]
• Exécute le contenu du tampon de requête
  • la requête doit renvoyer au moins 3 colonnes
• Affiche le résultat dans une grille croisée
  • colV, en-tête vertical
  • colH, en-tête horizontal
  • colD, contenu à afficher dans le tableau
  • sortColH, colonne de tri pour l'en-tête horizontal

• La commande pset
  • \pset option [ valeur ]
• Activer le mode étendu
  • \pset expanded on
• Donner un titre au résultat de la requête
  • \pset title 'Résultat de la requête'
• Formater le résultat en HTML
  • \pset format html

• Attention aux variables d'environnement
• Configuration
  • crontab -e

Sauvegarder une base et classer l'archive

#!/bin/bash

t=`mktemp`
pg_dump $1 | gzip > $t
d=`eval date +%d%m%y-%H%M%S`
mv $t /backup/${1}_${d}.dump.gz
exit 0

• Outils graphiques d'administration
  • temBoard
  • pgAdminIII et pgAdmin 4
  • phpPgAdmin

• Adresse: https://github.com/dalibo/temboard
• Version: 1.0
• Licence: PostgreSQL
• Notes: Serveur sur Linux, client web

• Multi-instances
• Surveillance OS / PostgreSQL
• Suivi de l'activité
• Configuration de chaque instance

• Adresse: http://www.pgadmin.org
• Version: 1.20.0
• Licence: PostgreSQL
• Notes: Multiplateforme, multilangue

• Installeurs Windows et Mac
• Paquets RPM et Debian/Ubuntu

• Connexion possible sur plusieurs serveurs
• Édition des fichiers de configuration locaux
• Permet de gérer toutes les bases d'un même serveur
• Maintenance des bases de données (vacuum, analyze, reindex)
• Visualisation des verrous
• Visualisation des journaux applicatifs
• Débogueur PL/pgsql

• Sauvegarde et restauration d'une base
• Gestion des rôles
• Création/modification/suppression de tous les objets PostgreSQL
• Possibilité de voir/cacher les objets systèmes
• Éditeur de requête

• Adresse: http://www.pgadmin.org
• Version: 1.1.0
• Licence: PostgreSQL
• Notes: Multiplateforme, multilangue

• pgAdmin III
  • existe depuis longtemps
  • stabilisé
  • mais en fin de vie
• pgAdmin IV
  • très jeune
  • complexe à installer
  • le seul à être maintenu

• Adresse: http://phppgadmin.sourceforge.net/
• Version: 5.1.0
• Licence: GNU Public License

• Notes: Multiplateforme

• Mais ne semble plus maintenu

• Application web déportée
• Création, maintenance de bases de données
• Import et export de données
• Exécution de commandes SQL et de scripts (upload)
• Gestion des tablespaces
• Gestion des utilisateurs et des droits
• Gestion des connexions
• Support multi-lingue (31 langues supportés)
• Support des différentes opérations de maintenance

• Gestion des tables, vues, index, séquences
• Gestion des contraintes, fonctions, triggers, règles,
• Gestions des types, agrégats, domaines
• Visualisation des verrous
• Visualisation des opérateurs, classes d'opérateurs et conversions entre encodages
• Visualisation des languages et conversions de type
• Configuration de l'autovacuum
• Configuration de la recherche plein texte

• Les outils en ligne de commande sont « rustiques » mais puissants
• Ils peuvent être remplacés par des outils graphiques
• En cas de problème, il est essentiel de les maîtriser.

N'hésitez pas, c'est le moment !

• Gestion des bases
• Gestion des rôles
• Gestion des droits
• Tâches du DBA
• Sécurité

• Liste des bases
• Modèle (Template)
• Création
• Suppression
• Modification / configuration

• Catalogue système pg_database
• Commande \l dans psql

• Toute création de base se fait à partir d'un modèle
• Par défaut, template1 est utilisée
• Permet de personnaliser sa création de base
• Mais il est aussi possible d'utiliser une autre base

• SQL : CREATE DATABASE
  • droit nécessaire: SUPERUSER ou CREATEDB
  • prérequis: base inexistante
• Outil système : createdb

• SQL : DROP DATABASE
  • droit nécessaire: SUPERUSER ou propriétaire de la base
  • prérequis: aucun utilisateur connecté sur la base, base existante
• Outil système : dropdb

• ALTER DATABASE
  • pour modifier quelques méta-données ;
  • pour ajouter, modifier ou supprimer une configuration.

• Liste des rôles
• Création
• Suppression
• Modification
• Utilisateur/groupe
• Gestion des mots de passe

• Rôles à partir de la 8.1
• Utilisateurs et de groupes avant... mais aussi après
• Ordres SQL
  • CREATE/DROP/ALTER USER
  • CREATE/DROP/ALTER GROUP

• Catalogue système pg_roles
• Commande \du dans psql

• SQL : CREATE ROLE
  • droit nécessaire: SUPERUSER ou CREATEROLE
  • prérequis: utilisateur inexistant
• Outil système : createuser
  • attribut LOGIN par défaut

• SQL : DROP ROLE
  • droit nécessaire: SUPERUSER ou CREATEROLE
  • prérequis: rôle existant, rôle ne possédant pas d'objet
• Outil système : dropuser

• ALTER ROLE
  • pour modifier quelques méta-données ;
  • pour ajouter, modifier ou supprimer une configuration.

• Toujours mettre un mot de passe
• Attention aux traces
• Mot de passe chiffré à privilégier
• Mais
  • Pas de vérification de la faiblesse du mot de passe
  • Pas de date limite de validité du rôle (le mot de passe peut avoir une date limite de validité)

• Droits sur les objets
• Droits sur les méta-données
• Héritage des droits
• Changement de rôle

• Donner un droit : GRANT
• Retirer un droit : REVOKE
• Droits spécifiques pour chaque type d'objets
• Avoir le droit de donner le droit : WITH GRANT OPTION

• Seul le propriétaire peut changer la structure d'un objet
  • le renommer
  • le changer de schéma ou de tablespace
  • lui ajouter/retirer des colonnes
• Un seul propriétaire
  • mais qui peut être un groupe

• Rôles systèmes d'administration
  • pg_signal_backend (9.6+)
• Rôles systèmes de supervision
  • pg_read_all_stats (10+)
  • pg_read_all_settings (10+)
  • pg_stat_scan_tables (10+)
  • pg_monitor (10+)

• Créer un rôle sans droit de connexion
• Donner les droits à ce rôle
• Placer les utilisateurs concernés comme membre de ce rôle

• Rôle par défaut
  • celui de la connexion
• Rôle emprunté :
  • après un SET ROLE
  • pour tout rôle dont il est membre

• Lors d'une connexion, indication :
  • de l'hôte (socket Unix ou alias/adresse IP)
  • du nom de la base de données
  • du nom du rôle
  • du mot de passe (parfois optionnel)
• Suivant les trois premières informations
  • impose une méthode d'authentification

• Quatre informations :
  • socket Unix ou adresse/alias IP
  • numéro de port
  • nom de la base
  • nom du rôle
• Fournies explicitement ou implicitement

• PostgreSQL utilise les informations de connexion pour sélectionner la méthode
• Fichier de configuration: pg_hba.conf
• Se présente sous la forme d'un tableau
  • 4 colonnes d'informations
  • 1 colonne indiquant la méthode à appliquer
  • 1 colonne optionnelle d'options

• 4 valeurs possibles
  • local
  • host
  • hostssl
  • hostnossl
• hostssl nécessite d'avoir activé ssl dans postgresql.conf

• Nom de la base
• Plusieurs bases (séparées par des virgules)
• Nom d'un fichier contenant la liste des bases (précédé par une arobase)
• Mais aussi
  • all (pour toutes les bases)
  • sameuser, samerole (pour la base de même nom que le rôle)
  • replication (pour les connexions de réplication)

• Nom du rôle
• Nom d'un groupe (précédé par un signe plus)
• Plusieurs rôles (séparés par des virgules)
• Nom d'un fichier contenant la liste des rôles (précédé par une arobase)
• Mais aussi
  • all (pour tous les rôles)

• Uniquement dans le cas d'une connexion host, hostssl et hostnossl
• Soit l'adresse IP et le masque réseau
• Soit l'adresse au format CIDR
• Pas possible d'utiliser un nom DNS avant la 9.1

• Précise la méthode d'authentification à utiliser
• Deux types de méthodes
  • internes
  • externes
• Possibilité d'ajouter des options dans une dernière colonne

• Dépend de la méthode d'authentification
• Méthode externe : option map

• trust
• reject
• password
• md5

• ldap, radius, cert
• gss, sspi
• ident, peer, pam
• bsd

• crypt depuis la version 8.4
• krb5 depuis la version 9.3

Un exemple:

TYPE    DATABASE  USER        CIDR-ADDRESS    METHOD
local   all       postgres                    ident
local   web       web                         md5
local   sameuser  all                         ident
host    all       postgres    127.0.0.1/32    ident
host    all       all         127.0.0.1/32    md5
host    all       all         89.192.0.3/8    md5
hostssl recherche recherche   89.192.0.4/32   md5

à ne pas suivre…

• Maintenance
• Sauvegarde
• Supervision

• Trois opérations essentielles
  • VACUUM
  • ANALYZE
  • REINDEX
• Automatisable par cron
• ... et par autovacuum (pour les deux premiers)

• Lutter contre la fragmentation
• VACUUM
  • cartographie les espaces libres pour une prochaine réutilisation
  • utilisable en parallèle avec les autres opérations
  • vue pg_stat_progress_vacuum en 9.6
• VACUUM FULL
  • défragmente la table
  • verrou exclusif

• Met à jour les statistiques sur les données
• Utilisées par l'optimiseur de requêtes
• Échantillonnage global : default_statistics_target
• Échantillonnage par colonne possible

• Lancer REINDEX régulièrement permet
  • de gagner de l'espace disque
  • d'améliorer les performances
  • de réparer un index corrompu
• VACUUM ne provoque pas de réindexation
• VACUUM FULL réindexe

• CLUSTER, alternative à VACUUM FULL
• Plus rapide que VACUUM FULL suivi de REINDEX avant la 9.0
• Attention, CLUSTER nécessite près du double de l'espace disque utilisé pour stocker la table et ses index

• Automatisation des tâches de maintenance
• Cron sous Unix
• Tâches planifiées sous Windows

• Automatisation par cron
  • simple, voire simpliste
• Processus autovacuum
  • VACUUM/ANALYZE si nécessaire
  • Nombreux paramètres
  • Nécessite la récupération des statistiques d'activité

• Deux outils historiques
  • pg_dump
  • pg_dumpall
• Sauvegarde automatisable par cron

• Sauvegarde une base
  • À chaud
  • Cohérente
  • Complète ou partielle
• Exemple:

pg_dump -f b1.dump b1

• Sauvegarde l'instance complète
  • définition des utilisateurs et tablespaces
  • contenu de chaque base
• Comme pg_dump, c'est :
  • À chaud
  • Cohérent

• PostgreSQL fournit deux canaux d'information
  • les statistiques d'activité
  • les traces
• Mais ne fait pas d'historisation sur les statistiques
• Donc, il est nécessaire de mettre en place un outil externe

• Activer le collecteur de statistiques
• Vues statistiques
• Outils disponibles

• Bien configurer les traces
• Outils :
  • tail_n_mail
  • pgBadger

• Ce qu'un utilisateur standard peut faire
  • et ne peut pas faire
• Restreindre les droits
• Chiffrement
• Corruption silencieuse

• Un utilisateur standard peut
  • accéder à toutes les bases de données
  • créer des objets dans le schéma PUBLIC de toute base de données
  • créer des objets temporaires
  • modifier les paramètres de la session
  • créer des fonctions
  • exécuter des fonctions définies par d'autres dans le schéma PUBLIC
  • récupérer des informations sur l'instance
  • visualiser le source des vues et des fonctions

• Un utilisateur standard ne peut pas
  • créer une base
  • créer un rôle
  • accéder au contenu des objets créés par d'autres
  • modifier le contenu d'objets créés par d'autres

• Sur les connexions
  • pg_hba.conf
• Sur les objets
  • GRANT / REVOKE
  • SECURITY LABEL
• Sur les fonctions
  • SECURITY DEFINER
  • LEAKPROOF
• Sur les vues
  • security_barrier
  • WITH CHECK OPTION

• Connexions
  • SSL
  • avec ou sans certificats serveur et/ou client
• Données disques
  • pas en natif
  • pgcrypto

• Pas de sommes de contrôle dans les fichiers de données
  • sauf à partir de la 9.3
• S'active avec initdb
• Plus de sécurité
  • mais une diminution des performances

PostgreSQL demande peu de travail au quotidien.

À l'installation, certaines tâches doivent être automatisées, par exemple la sauvegarde, les VACUUM.

Pour le reste, il s'agit surtout de surveiller la bonne exécution des scripts automatisés et le contenu des journaux applicatifs.

• Documentation officielle, chapitre Planifier les tâches de maintenance
• Opérations de maintenance sous PostgreSQL
• Total security in a PostgreSQL database
• Managing rights in PostgreSQL, from the basics to SE PostgreSQL

N'hésitez pas, c'est le moment !

• Opération essentielle pour la sécurisation des données
• PostgreSQL propose différentes solutions
  • de sauvegarde à froid ou à chaud, mais cohérentes
  • des méthodes de restauration partielle ou complète

• Politique de sauvegarde
• Sauvegarde logique
• Sauvegarde physique

• Pourquoi établir une politique ?
• Que sauvegarder ?
• À quelle fréquence sauvegarder les données ?
• Quels supports ?
• Quels outils ?
• Vérifier la restauration des sauvegardes

• Sécuriser les données
• Mettre à jour le moteur de données
• Dupliquer une base de données de production
• Archiver les données

• Sauvegarde à froid des fichiers (ou physique)
• Sauvegarde à chaud en SQL (ou logique)
• Sauvegarde à chaud des fichiers (PITR)

• RPO (Recovery Point Objective) : Perte de Données Maximale Admissible
• RTO (Recovery Time Objective) : Durée Maximale d'Interruption Admissible
• => Permettent de définir la politique de sauvegarde/restauration

• À chaud
• Cohérente
• Locale ou à distance
• 2 outils
  • pg_dump
  • pg_dumpall

• Sauvegarde une base de données
• Sauvegarde complète ou partielle

• -F
  • p : plain, SQL
  • t : tar
  • c : custom (spécifique PostgreSQL)
  • d : directory

• -Z : de 0 à 9

• -f : fichier où est stockée la sauvegarde
• sans -f, sur la sortie standard

• --schema-only ou -s : uniquement la structure
• --data-only ou -a : uniquement les données

• --section
  • pre-data, la définition des objets (hors contraintes et index)
  • data, les données
  • post-data, la définition des contraintes et index

• -n <schema> : uniquement ce schéma
• -N <schema> : tous les schémas sauf celui-là
• -t <table> : uniquement cette table
• -T <table> : toutes les tables sauf celle-là
• En option
  • possibilité d'en mettre plusieurs
  • exclure les données avec --exclude-table-data=<table>
  • avoir une erreur si l'objet est inconnu avec--strict-names

• -j <nombre_de_threads>

• -O : ignorer le propriétaire
• -x : ignorer les droits
• --no-tablespaces : ignorer les tablespaces
• --inserts : remplacer COPY par INSERT
• -v : pour voir la progression

• Sauvegarde d'une instance complète
  • Objets globaux
  • Bases de données

• -F
  • p : plain, SQL

• -f : fichier où est stockée la sauvegarde
• sans -f, sur la sortie standard

• -g : tous les objets globaux
• -r : uniquement les rôles
• -t : uniquement les tablespaces
• --no-role-passwords : pour ne pas sauvegarder les mots de passe
  • permet de ne pas être superutilisateur

• quelques options partagées avec pg_dump
• très peu utilisées

• -h / $PGHOST / socket Unix
• -p / $PGPORT / 5432
• -U / $PGUSER / utilisateur du système
• $PGPASSWORD
• .pgpass

• Pour compresser : pg_dump | bzip2
• Il existe des outils multi-thread de compression, bien plus rapides:
  • pbzip2
  • pigz

• Deux types dans PostgreSQL : bytea et Large Objects
• Option -b
  • uniquement si utilisation des options -n/-N et/ou -t/-T
• Option --no-blobs
  • pour ne pas sauvegarder les Large Objects
• Option bytea_output
  • escape
  • hex

• Sauvegarde texte (option p) : psql
• Sauvegarde binaire (options t, c ou d) : pg_restore

• client standard PostgreSQL
• capable d'exécuter des requêtes
• donc capable de restaurer une sauvegarde au format texte
• très limité dans les options de restauration

• -f pour indiquer le fichier contenant la sauvegarde
  • sans option -f, lit l'entrée standard
• -1 pour tout restaurer en une seule transaction
• -e pour afficher les ordres SQL exécutés
• ON_ERROR_ROLLBACK/ON_ERROR_STOP

• restaure uniquement les sauvegardes au format binaire
  • donc tar, custom ou directory
  • format autodétecté (-F inutile, même si présent)
• nombreuses options très intéressantes
• restaure une base de données
  • complètement ou partiellement

• Fichier à restaurer en dernier argument de la ligne de commande
• Attention à -f (fichier en sortie)

• -s : uniquement la structure
• -a : uniquement les données

• --section
  • pre-data, la définition des objets (hors contraintes et index)
  • data, les données
  • post-data, la définition des contraintes et index

• -n <schema> : uniquement ce schéma
• -N <schema> : tous les schémas sauf ce schéma
• -t <table> : cette relation
• -T <trigger> : ce trigger
• -I <index> : cet index
• -P <fonction> : cette fonction
• En option
  • possibilité d'en mettre plusieurs
  • --strict-names, pour avoir une erreur si l'objet est inconnu

• -l : récupération de la liste des objets
• -L <liste_objets> : restauration uniquement des objets listés dans ce fichier

• -j <nombre_de_threads>

• -O : ignorer le propriétaire
• -x : ignorer les droits
• --no-tablespaces : ignorer le tablespace
• -1 pour tout restaurer en une seule transaction
• -c : pour détruire un objet avant de le restaurer

• Script de sauvegarde
• Sauvegarder sans passer par un fichier
• Gestion des statistiques sur les données
• Durée d'exécution d'une sauvegarde
• Taille d'une sauvegarde

• pg_dumpall -g
• suivi d'un appel à pg_dump -Fc pour chaque base

• Type de sauvegardes: logiques (pg_dump)
• Langage: bash
• Licence: BSD (libre)
• Type de stockage: local
• Planification: crontab
• OS: Unix/Linux
• Compression: gzip
• Versions compatibles: toutes
• Rétention: durée en jour

• pg_dump | pg_restore
• Utilisation des options -h et -p

• Ne fait pas partie de la sauvegarde
• Donc, ANALYZE après une restauration

• Difficile à chiffrer
• Dépend de l'activité sur le serveur

• Difficile à évaluer
• Le contenu des index n'est pas sauvegardé
  • Donc sauvegarde plus petite
• Les objets binaires prennent plus place
  • Entre 2 et 4 fois plus gros
  • Donc sauvegarde plus grosse

• À froid
• Donc cohérente
• Beaucoup d'outils
  • aucun spécifique à PostgreSQL
• Attention à ne pas oublier les tablespaces
• Possibilité de la faire à chaud (PITR)
  • nécessite d'avoir activé l'archivage des WAL
  • technique avancée, complexe à mettre en place et à maintenir
  • pas de coupure de service

• Rapide à la sauvegarde
• Rapide à la restauration
• Beaucoup d'outils disponibles

• Arrêt de la production
• Sauvegarde de l'instance complète (donc aucune granularité)
• Restauration de l'instance complète
• Conservation de la fragmentation
• Impossible de changer d'architecture

• Utilisation de rsync
• Une fois avant l'arrêt
• Une fois après

• Avec certains systèmes de fichiers
• Avec LVM
• Avec la majorité des SAN

• Prendre le temps de bien choisir sa méthode
• Bien la tester
• Bien tester la restauration
• Et tester régulièrement !
• Ne pas oublier de sauvegarder les fichiers de configuration

• Plusieurs solutions pour la sauvegarde et la restauration
• Sauvegarde/Restauration complète ou partielle
• Toutes cohérentes
• La plupart à chaud
• Méthode de sauvegarde avancée : PITR

N'hésitez pas, c'est le moment !

• Deux types de supervision
  • occasionnelle
  • automatique
• Superviser PostgreSQL et le système
• Pour PostgreSQL, statistiques et traces

• Politique de supervision
• Supervision occasionnelle
• Supervision automatique

• Pour quoi ?
• Pour qui ?
• Quels critères ?
• Quels outils

• Améliorer les performances
• Améliorer l'applicatif
• Anticiper/prévenir les incidents
• Réagir vite en cas de crash

• Développeur
  • correction et optimisation de requêtes
• Administrateur de bases de données
  • surveillance, performance, mise à jour
• Administrateur système
  • surveillance, qualité de service

• Charge CPU
• Entrées/sorties disque
• Espace disque
• Sur-activité et non-activité du serveur
• Temps de réponse

• Nombre de connexions
• Requêtes lentes et/ou fréquentes
• Ratio d'utilisation du cache

• Supervision occasionnelle : pour les cas où il est possible d'intervenir immédiatement
• Supervision automatique
  • permet de remonter des informations rapidement
  • permet de conserver les informations

• PostgreSQL propose deux canaux d'informations :
  • les statistiques
  • les traces
• Mais rien pour les conserver, les historiser

• Nécessaire pour conserver les informations
• ... et exécuter automatiquement des actions dessus
  • Génération de graphiques (Munin, Zabbix, OPM)
  • Envoi d'alertes (Nagios, tail_n_mail)

• Script de monitoring PostgreSQL pour Nagios ou MRTG
  • nombreuses sondes spécifiques
  • nombreuses données de performance remontées
• http://bucardo.org/wiki/Check_postgres

• Script de monitoring PostgreSQL pour Nagios
  • nombreuses sondes spécifiques à PostgreSQL
  • reprend la plupart des sondes de check_postgres, en corrigeant certaines
  • davantage de données de performance remontées
• Peut être utilisé en remplacement de check_postgres
• Développé par Dalibo pour les besoins de l'outil OPM
  • mais peut être utilisé indépendamment
• https://github.com/OPMDG/check_pgactivity

• Configuration
• Récupération
  • des problèmes importants
  • des requêtes lentes/fréquentes
• Outils externes de classement

• Où tracer ?
• Quel niveau de traces ?
• Tracer les requêtes
• Tracer certains comportements

• log_destination
• logging_collector
  • log_directory, log_filename, log_file_mode
  • log_rotation_age, log_rotation_size, log_truncate_on_rotation
• syslog (Unix)
• eventlog (Windows)

• log_min_messages
• log_min_error_statement
• log_error_verbosity

• log_min_duration_statement
• log_statement
• log_duration

• log_connections, log_disconnections
• log_autovacuum_min_duration
• log_checkpoints
• log_lock_waits
• log_temp_files

• log_line_prefix
• lc_messages
• log_timezone

• Durée d'exécution
• Messages PANIC
• Rechargement de la configuration
• Fichiers temporaires

• log_statement et log_duration
• log_min_duration_statement

Exemple:

LOG:  duration: 112.615 ms  statement: select  * from t1 where c1=4;

• Outils : pgBadger, pgfouine, pgsi, etc.

• Exemple:

PANIC:  could not write to file "pg_wal/xlogtemp.9109": 
        No space left on device

• Envoi immédiat d'une alerte
• Outils : tail_n_mail

• Exemple :

LOG:  received SIGHUP, reloading configuration files

• Envoi d'une alerte pour s'assurer que cette configuration est voulue
• Outils : tail_n_mail

• Exemple :

LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp9894.0", 
      size 26927104

• Envoi d'une alerte sur un problème potentiel de performances

• Beaucoup d'outils existent
• Deux types :
  • en temps réel
  • rétro-analyse
• Nous verrons les plus connus/intéressants :
• pgBadger
  • logwatch
  • tail_n_mail

• Script Perl
• Traite les journaux applicatifs
• Recherche des informations
  • sur les requêtes et leur durée d'exécution
  • sur les connexions et sessions
  • sur les checkpoints
  • sur l'autovacuum
  • sur les verrous
  • etc.
• Génération d'un rapport HTML très détaillé

• Configuration minimale
  • log_destination, log_line_prefix et lc_messages
• Configuration de base
  • log_connections, log_disconnections
  • log_checkpoints, log_lock_waits, log_temp_files
  • log_autovacuum_min_duration
• Configuration temporaire
  • log_min_duration_statement

• --outfile
• --begin, --end
• --dbname, --dbuser, --dbclient, --appname

• Outil externe écrit en Perl
  • http://sourceforge.net/projects/logwatch/
  • Licence MIT
• À exécuter périodiquement
• Analyse le contenu des journaux applicatifs
• Envoie un mail s'il détecte certains motifs

• Pas de configuration disponible par défaut pour PostgreSQL
• Dépôt git Dalibo
  • https://github.com/dalibo/pgsql_logwatch/
  • à installer manuellement

• --logfile, les fichiers à traiter
• --service, filtre sur le service unique à traiter
• --detail, niveau de détails du rapport
• --print, pour afficher le rapport sur la sortie standard
• --save, pour sauvegarder le rapport dans un fichier
• --mailto, pour envoyer le rapport par mail

• Outil externe écrit en Perl
• À exécuter périodiquement
• Analyse le contenu des journaux applicatifs
• Envoie un mail s'il détecte certains motifs

EMAIL: astreinte@dalibo.com
MAILSUBJECT: HOST Postgres fatal errors (FILE)
FILE: /var/log/postgresql-%Y-%m-%d.log
INCLUDE: PANIC:
INCLUDE: FATAL:
EXCLUDE: database ".+" does not exist
INCLUDE: temporary file
INCLUDE: reloading configuration files

Exemple:

[1] Between lines 123005 and 147976, occurs 39 times.
First:  Jan  1 00:00:01 rojogrande postgres[4306]
Last:   Jan  1 10:30:00 rojogrande postgres[16854]
Statement: user=root,db=rojogrande 
        FATAL: password authentication failed for user "root"

• Configuration
• Liste des vues statistiques
• Outils externes de classement

• Tracer l'activité
  • track_activities
• S'assurer que les requêtes ne sont pas tronquées
  • track_activity_query_size

• track_counts
• track_io_timing
• track_functions

• stats_temp_directory
  • fichier écrit toutes les 500 ms avant la 8.4
  • seulement quand nécessaire à partir de la 8.4

• Sur l'activité
• sur l'instance
• sur les bases
• sur les tables
• sur les index
• sur les fonctions

• Ratio de lecture du cache (souvent appelé hitratio)
• Retard de réplication
• Nombre de transactions par seconde

• Beaucoup d'outils existent pour exploiter les statistiques
• Les plus connus/intéressants sont :
  • Munin
  • Nagios
  • Zabbix
  • OPM
  • pg_stat_statements
  • PoWA

• Scripts Perl
• Sondes PostgreSQL incluses depuis la 1.4
• Récupère les statistiques toutes les cinq minutes
• Crée des pages HTML statiques et des fichiers PNG
  • donc des graphes

• Outil GPL, sur http://www.nagios.org/
• Sondes dédiées à PostgreSQL : check_postgres et check_pgactivity

• Outil GPL, sur http://www.zabbix.com/
• 2.0
• Sonde check_postgres.pl
• Template pg-monz
  • http://pg-monz.github.io/pg_monz/index-en.html

• Open PostgreSQL Monitoring
• Suite de supervision lancée par Dalibo en septembre 2014
• Projet indépendant mené par OPMDG (OPM Development Group)
• Licence PostgreSQL
• http://opm.io/

• Module contrib de PostgreSQL
• Récupère et stocke des statistiques d'exécution des requêtes
• Les requêtes sont normalisées
• Pas d'historisation

• PostgreSQL Workload Analyzer
• Effectue des captures des statistiques collectées par pg_stat_statements
• Fournit une interface graphique pour observer en temps réel l'activité des requêtes
• Licence PostgreSQL
• https://github.com/dalibo/powa-archivist
• https://github.com/dalibo/powa-web

• Un système est pérenne s'il est bien supervisé
• Supervision automatique
  • configuration des traces
  • configuration des statistiques
  • mise en place d'outils d'historisation