• 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 !

• Projet horizontal & décentralisé
• La « biodiversité » est une force
• Le meilleur SGBD du marché ?

• Projets satellites
• Comparatifs
• Communauté
• Avenir

• Connaître les logiciels connexes
• Exploiter toute la puissance du SGBD
• Participer !

• Administration
• Supervision et monitoring
• Migrations
• SIG
• Modélisation

• Site officiel : http://www.pgadmin.org/
• Version : 2.0
• Licence : PostgreSQL
• Gestion graphique de l'administration des bases
• Éditeur de requêtes
• Supervision

• Site officieux : https://www.openscg.com/bigsql/pgadmin3/
• Version : 1.23
• Licence : PostgreSQL
• Gestion graphique de l'administration des bases
• Éditeur de requêtes / EXPLAIN graphique
• Gestion de Slony

• Site officiel : http://phppgadmin.sourceforge.net/
• Version : 5.1
• Licence : GPL
• Gestion graphique de l'administration des bases
• Interface web
• Mais ne semble plus maintenu
  • pas de nouvelles versions depuis avril 2013
  • pas de commit depuis avril 2016
• Utiliser plutôt pgAdmin IV sur un serveur web

• Site officiel : http://projects.dalibo.org/pgbadger/
• Version : 9.2
• Licence : PostgreSQL
• Analyse des traces de durée d'exécution des requêtes
• Analyse des traces du VACUUM, des connexions, des checkpoints
• Compatible syslog, stderr, csvlog

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

• Site officiel : http://dalibo.github.io/powa/
• Version : 3.1
• Licence : PostgreSQL
• Surveillance de l'activité SQL
• Développé par Dalibo
• Dépôt GitHub
  • archiveur : https://github.com/dalibo/powa-archivist
  • UI web : https://github.com/dalibo/powa-web

• Site officiel : http://ora2pg.darold.net/
• Version : 18.2
• Licence : GPL
• Migration de la structure d'une base Oracle...
• ... des procédures stockées et des données

• Site officiel : http://dalibo.github.io/sqlserver2pgsql/
• Version : sans
• Licence : GPL v3
• Convertit un schéma SQL Server en un schéma PostgreSQL
• Produit en option un job Pentaho Data Integrator (Kettle) pour migrer toutes les données de SQL Server vers PostgreSQL

• Site officiel : https://github.com/dalibo/db2topg
• Version : sans
• Licence : GPL v3
• Convertit une base DB2 en une base PostgreSQL

• Site officiel : http://postgis.net/
• Version : 2.4
• Licence : BSD
• Module spatial pour PostgreSQL
• Conforme aux spécifications de l'OpenGIS Consortium
• Compatible avec MapServer

• Site officiel : http://pgmodeler.com.br/
• Version : 0.9
• Licence : GPLv3
• Modélisation de base de données
• Fonctionnalité d'import export

• Pas de SGBD universel
• S'inspirer des concurrents plutôt que de les combattre
• PostgreSQL vs.
  • MySQL
  • SQL Server
  • Oracle
  • Informix
  • NoSQL
• Attention aux benchmarks !

• Différents moteurs
  • MyISAM ou InnoDB ?
• Points forts de PostgreSQL
  • DDL transactionnel
• Points faibles de PostgreSQL
  • lenteur du SELECT count(*) (amélioration en 9.2)
• Quel avenir pour MySQL ?

• Des performances difficile à battre sous Windows
  • ... mais PostgreSQL reste plus efficace sous Linux
• Intégration à l'écosystème Microsoft
  • avantage, excellents outils graphiques (Studio)
  • inconvénient, peu de choix dans les outils
• Points faibles de PostgreSQL
  • un partitionnement perfectible (amélioration en 10)
  • pas de vues matérialisées (amélioration en 9.3)

• PostgreSQL est le SGBD le plus proche d'Oracle
• Points forts de PostgreSQL
  • respect des standards
  • DDL transactionnel
  • pas de gestion et de coût de licence
• Points faibles de PostgreSQL
  • manque certains objets (synonymes, packages)
  • un parallélisme perfectible (amélioration en 9.6)
  • un partitionnement perfectible (amélioration en 10)
  • pas de vues matérialisées (amélioration en 9.3)
  • lenteur du SELECT count(*) (amélioration en 9.2)
  • pas d'équivalent de RAC
• Deux produits à présent dans la même catégorie

• Informix est un lointain cousin de PostgreSQL
• Points forts de PostgreSQL
  • un vrai CREATER USER
  • pas de double quote (") pour les chaînes
• Points faibles de PostgreSQL
  • pas de synonym

• 4 technologies majeures dans le monde NoSQL
  • stockage clé->valeur (Redis, Apache Cassandra, Riak, MongoDB)
  • stockage documents (Apache CouchDB, MongoDB)
  • stockage colonnes (Apache Hbase, Google BigTable)
  • stockage graphes (Neo4j)
• PostgreSQL réunit le monde relationnel et le monde NoSQL
  • stockage clé->valeur : hstore
  • stockage documents : xml, json et jsonb (plus performant que MongoDB)
  • procédure stockée en Javascript : PL/V8
  • stockage colonnes : cstore_fdw

• Cartographie du projet
• Pourquoi participer
• Comment participer

• Vous !
• Le succès d'un logiciel libre dépend de ses utilisateurs.

• Rapidité des corrections de bugs
• Préparer les migrations
• Augmenter la visibilité du projet
• Créer un réseau d'entraide

• Site officiel : http://www.postgresql.org/
• La doc : http://www.postgresql.org/docs/
• Actualité : http://planet.postgresql.org/
• Des extensions : http://pgxn.org/

• Site officiel : http://www.postgresql.fr/
• La doc : http://docs.postgresql.fr/
• Forum : http://forums.postgresql.fr/
• Actualité : http://planete.postgresql.fr/
• Wiki : http://wiki.postgresql.fr/

• pgsql-announce
• pgsql-general
• pgsql-admin
• pgsql-sql
• pgsql-performance
• pgsql-fr-generale
• pgsql-advocacy

• Réseau Freenode
• IRC anglophone
  • #postgresql
  • #postgresql-eu
• IRC francophone
  • #postgresqlfr

• http://wiki.postgresql.org/
• http://wiki.postgresql.org/wiki/Fran%C3%A7ais

• PostgreSQL 10 est sortie en septembre 2017
• Grandes orientations :
  • réplication logique
  • meilleur parallélisme
  • gros volumes
• Prochaine version, la 11
• Stabilité économique
• Le futur de PostgreSQL dépend de vous !

• Beaucoup de projets complémentaires
• Une communauté active
• Concurrent solide face aux SGBD propriétaires
• De nombreuses conversions en cours vers PostgreSQL

• Organisation du projet PostgreSQL, Guillaume Lelarge, 2010

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 !

• Attention au vocabulaire !
• Identifier le besoin
• Keep It Simple...

• Connaître les principaux outils de réplication
• Identifier les différences entre les solutions proposées
• Choisir le système le mieux adapté à votre besoin

• Cluster
• Réplication
  • synchrone / asynchrone
  • symétrique / asymétrique
  • diffusion des modifications

• Dans la terminologie PostgreSQL
  • groupe autonome de bases de données
  • i.e. une instance
• Dans la terminologie haute-disponibilité et/ou réplication
  • groupe de serveurs

• Asymétrique
  • écritures sur un serveur maître unique
  • lectures sur le maître et/ou les esclaves
• Asynchrone
  • les écritures sur les esclaves sont différées
  • perte de données possible en cas de crash du maître
• Quelques exemples
  • streaming replication, Slony, Londiste, Bucardo

• Symétrique
  • écritures sur les différents maîtres
  • besoin d'un gestionnaire de conflits
  • lectures sur les différents maîtres
• Asynchrone
  • les écritures sur les esclaves sont différées
  • perte de données possible en cas de crash du maître

• Asymétrique
  • écritures sur un serveur maître unique
  • lectures sur le maître et/ou les esclaves
• Synchrone
  • les écritures sur les esclaves sont immédiates
  • le client sait si sa commande a réussi sur l'esclave

• Symétrique
  • écritures sur les différents maîtres
  • besoin d'un gestionnaire de conflits
  • lectures sur les différents maîtres
• Synchrone
  • les écritures sur les esclaves sont immédiates
  • le client sait si sa commande a réussi sur l'esclave
  • risque important de lenteur

• 4 types de récupération des informations de réplication
• Par requêtes
  • diffusion de la requête
• Par triggers
  • diffusion des données résultant de l'opération
• Par journaux, physique
  • diffusion des blocs disques modifiés
• Par journaux, logique
  • extraction et diffusion des données résultant de l'opération depuis les journaux

• Réplication
  • asymétrique
  • asynchrone ou synchrone
• Esclaves
  • non disponibles (Warm Standby)
  • disponibles en lecture seule (Hot Standby, à partir de la 9.0)
  • cascade possible à partir de la 9.2
  • retard possible à partir de la 9.4

• But
  • envoyer les journaux de transactions à un esclave
• Première solution disponible (dès 2006)
• Gros inconvénient
  • il est possible de perdre un journal de transactions entier

• But
  • avoir un retard moins important sur le maître
• Rejouer les enregistrements de transactions du maître par paquets
  • paquets plus petits qu'un journal de transactions
• Solution idéalement couplée au Hot Standby

• Intégré à PostgreSQL depuis 2006
• Serveur de secours en cas de panne
• L'esclave est identique au maitre
  • à quelques transactions près

• Évolution du Warm Standby apparue en 9.0
• Basé sur le même mécanisme
• Le serveur secondaire est ouvert aux connexions
  • et aux requêtes en lecture seule
• Différentes configurations
  • asynchrone ou synchrone
  • application immédiate ou retardée

• Réplique les changements
  • sur une seule base de données
  • d’un ensemble de tables défini
• Uniquement INSERT / UPDATE / DELETE
  • pas les DDL, ni les TRUNCATE

• Création d'une publication sur un serveur
• Souscription d'un autre serveur à cette publication

• Non répliqués :
  • Schémas
  • Séquences
  • Large objects
• Pas de publication des tables parentes du partitionnement
• Ne convient pas comme fail-over

• Un très large choix !
• Quel solution choisir ?
  • pgPool
  • Slony / Bucardo / Londiste
  • Postgres-XC

• Projet libre (BSD)
• Synchrone / Symétrique
• Réplication des requêtes SQL
• Site web

• Réplication basée sur les requêtes
• Capable de récupérer certaines fonctions non déterministes

• pgPool est à l'origine un pooler de connexions
• Configuration propre
• Plus ou moins transparent pour les applications
• Réplication de toutes les requêtes
  • y compris le DDL

• pgPool est un SPOF
  • SPOF => Single Point Of Failure
• Réplication basée sur la réplication des requêtes SQL
  • obligation de passer par pgpool
• Prise en main et fonctionnement complexe
• Authentification en mode réplication
  • pas de md5
  • pas de méthodes externes (LDAP, Radius, etc.)
• Effet « couteau suisse »

• Base de données de secours
• Répartition de charge en lecture

• Projet libre (BSD)
• Asynchrone / Asymétrique
• Diffusion des résultats (triggers)
• Site web

• Réplication de tables sélectionnées
• Procédures de bascule
  • switchover / switchback
  • failover / failback

• Réplication basée sur des triggers
• Démons externes, écrits en C
• Le maître est un provider
• Les esclaves sont des subscribers

• Choix des tables à répliquer
• Indépendance des versions de PostgreSQL
• Technique de propagation des DDL
• Robustesse

• Le réseau doit être fiable : peu de lag, pas ou peu de coupures
• Supervision délicate
• Modifications de schémas complexes

• Clusters en cascade
• Réplications complexes
• Infocentre (many to one)
• Bases spécialisées (recherche plein texte, traitements lourds, etc)

• Projet libre (BSD)
• Asynchrone / Asymétrique
• Réplication des résultats
• Site web

• Réplication de tables sélectionnées
• Procédures de bascule
  • switchover / switchback
  • failover / failback

• Réplication basée sur des triggers
• Démons externes, écrits en Python
• Utilise un autre outil provenant des Skytools : PgQ
• 1 maître / N esclaves

• PgQ est robuste, fiable et flexible
• Pas de groupes de réplication, mais des tables appartenant à différentes queues

• Technique de propagation des DDL basique
• Très peu de fonctionnalités.

• Clusters en cascade
• Réplications complexes
• Infocentre (many to one)
• Bases spécialisées (recherche plein texte, traitements lourds, etc)

• Projet libre (BSD)
• Asynchrone / Symétrique ...
• ... ou Asynchrone / Asymétrique
• Réplication des résultats (dits deltas)
• Site web

• Réplication maître-maitre ou maître-esclave
• Répartition des écritures
• Failover manuel
• Plusieurs méthodes de résolution des conflits

• Réplication basée sur des triggers
• Démons externes, écrits en Perl
• Maître / maître (1 seul couple)
  • ou maître / esclave(s)

• Basé sur un(des) set(s) de réplication et non sur un(des) schéma(s)
• Simplicité d'utilisation
• Résolution standard des conflits

• Aucune technique de propagation des DDL
  • (en cours de développement)
• Limité à deux nœuds en mode multi-maîtres
• Le réseau doit être fiable
  • peu de retard, pas ou peu de coupures
• Sous Linux/Unix uniquement
• Un seul développeur sur le projet
• Manque de fiabilité de l'​outil

• Cluster maître/maître simple
• Base de données de secours

• Projet libre (Licence PostgreSQL)
• Réplication multi-maîtres synchrone
• Réplication des résultats
• Ancien site web
• Projet renommé en postgres-x2

• Réplication multi-maîtres synchrone
• Distribution des données entre serveurs
  • partitionnement horizontal
• Répartition de charge
• Haute-disponibilité

• Global Transaction Manager
  • gestion des XID et conflits
• Coordinator
  • Catalogue / Répartiteur
• Datanode
  • Stockage

• Installation relativement aisée et bien documentée
• Intégration au cœur de PostgreSQL
• Ajout d'ordres DDL spécifiques

• Version dérivée de PostgreSQL
  • basée sur la version 9.3
• Chaque ordre SQL doit être porté
• Fonctionnalités manquantes
  • triggers, SQL/MED, savepoints, SSI
  • contraintes globales, détection globale des deadlock

• Très grandes bases transactionnelles sous forte charge
• Bases spécialisées
• Peu de cas d'utilisation connus en production

• Postgres-R
• PGCluster
• replicator
• cybercluster

• RAID
• DRBD
• SAN Mirroring
• À prendre évidemment en compte...

• Obligatoire
• Fiabilité d'un serveur.
• RAID 1 ou RAID 10
• Lectures plus rapides
  • dépend du nombre de disques impliqués

• Simple / synchrone / Bien documenté
• Lent / Esclave inaccessible / Linux uniquement

• Comparable à DRBD
• Solution intégrée
• Manque de transparence

Points essentiels :

• bien définir son besoin
• identifier tous les SPOF
• superviser son cluster
• tester régulièrement les procédures de Failover ( Loi de Murphy...)

• Doc officielle : Chapitre 25
• Hors série « Haute-disponibilité avec PostgreSQL » dans GNU/Linux France Magazine
• Article de blog « Hot Standby : Un exemple concret »

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