PostgreSQL pour DBA expérimentés

Formation DBAADM

Dalibo SCOP

24.09

29 août 2024

Sur ce document

Formation Formation DBAADM
Titre PostgreSQL pour DBA expérimentés
Révision 24.09
ISBN N/A
PDF https://dali.bo/dbaadm_pdf
EPUB https://dali.bo/dbaadm_epub
HTML https://dali.bo/dbaadm_html
Slides https://dali.bo/dbaadm_slides

Licence Creative Commons CC-BY-NC-SA

Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :

  • Paternité
  • Pas d’utilisation commerciale
  • Partage des conditions initiales à l’identique

Marques déposées

PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.

Versions de PostgreSQL couvertes

Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 12 à 16.

PostgreSQL : historique & communauté

PostgreSQL

Préambule

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

Au menu

  • Origines et historique du projet
  • Versions et feuille de route
  • Projets satellites
  • Sponsors et références
  • La communauté

Un peu d’histoire…

  • La licence
  • L’origine du nom
  • Les origines du projet
  • Les principes

Licence

  • Licence PostgreSQL
  • Droit, sans coûts de licence, de :
    • utiliser, copier, modifier, distribuer (et même revendre)
  • Reconnue par l’Open Source Initiative
  • Utilisée par un grand nombre de projets de l’écosystème

PostgreSQL ?!?!

  • 1985 : Michael Stonebraker recode Ingres
  • post « ingres » postingres postgres
  • postgres PostgreSQL

Principes fondateurs

  • Sécurité des données (ACID)
  • Respect des normes (ISO SQL)
  • Portabilité
  • Fonctionnalités intéressant le plus grand nombre
  • Performances
    • si pas de péril pour les données
  • Simplicité du code
  • Documentation

Origines

  • Années 1970 : Michael Stonebraker développe Ingres à Berkeley
  • 1985 : Postgres succède à Ingres
  • 1995 : Ajout du langage SQL
  • 1996 : Libération du code : Postgres devient PostgreSQL
  • 1996 : Création du PostgreSQL Global Development Group

Apparition de la communauté internationale

  • ~ 2000: Communauté japonaise (JPUG)
  • 2004 : PostgreSQLFr
  • 2006 : SPI
  • 2007 : Communauté italienne
  • 2008 : PostgreSQL Europe et US
  • 2009 : Boom des PGDay
  • 2011 : Postgres Community Association of Canada
  • 2017 : Community Guidelines
  • …et ça continue

Progression du code

  • 1,6 millions de lignes
    • dont 1/4 de commentaires
    • le reste surtout en C
  • Nombres de commit par mois :
Évolution du nombre de commit dans le dépôt PostgreSQL

Les versions de PostgreSQL

Quelle version utiliser ?

  • Historique
  • Numérotation
  • Mises à jour mineures et majeures
  • Les versions courantes
  • Quelle version en production ?
  • Les forks & dérivés

Historique

Versions & fonctionnalités

  • 1996 : v6.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 -> performances et fonctionnalités, organisation (commitfests)
  • 2010 : v9.0 -> réplication physique
  • 2016 : v9.6 -> parallélisation
  • 2017 : v10 -> réplication logique, partitionnement déclaratif
  • 2023 : v16 -> performances, fonctionnalités, administration…

Numérotation

  • Version récentes (10+)
    • X : version majeure (10, 11, … 16)
    • X.Y : version mineure (14.8, 15.3)
  • Avant la version 10 (toutes périmées !)
    • X.Y : version majeure (8.4, 9.6)
    • X.Y.Z : version mineure (9.6.24)

Mises à jour mineure

De M.m à M.m+n :

  • En général chaque trimestre
  • Et sans souci
    • Release notes
    • tests
    • mise à jour des binaires
    • redémarrage

Versions courantes

  • 1 version majeure par an
    • maintenue 5 ans
  • Dernières mises à jour mineures
  • Prochaine sortie de versions mineures prévue : 14 novembre 2024

Versions 9.4 à 11

  • jsonb
  • Row Level Security
  • Index BRIN, bloom
  • Fonctions OLAP
  • Parallélisation
  • SQL/MED : accès distants
  • Réplication logique
  • Partitionnement déclaratif
  • Réduction des inconvénients de MVCC
  • JIT
  • Index couvrants

Version 12

  • Octobre 2019 - Novembre 2024
  • Amélioration du partitionnement déclaratif
  • Amélioration des performances
    • sur la gestion des index
    • sur les CTE (option MATERIALIZED)
  • Colonnes générées
  • Nouvelles vues de visualisation de la progression des commandes
  • Refonte de la configuration de la réplication

Version 13

  • Septembre 2020 - Septembre 2025
  • Améliorations :
    • partitionnement déclaratif
    • réplication logique
  • Amélioration des performances :
    • index B-tree, objet statistique, tri et agrégat
  • Amélioration de l’autovacuum et du VACUUM :
    • gestion complète des tables en insertion seule
    • traitement parallélisé des index lors d’un VACUUM
  • Amélioration des sauvegardes :
    • génération d’un fichier manifeste, outil pg_verifybackup
  • Nouvelles vues de progression de commandes :
    • pg_stat_progress_basebackup, pg_stat_progress_analyze

Version 14

  • Septembre 2021 - Novembre 2026
  • Nouvelles vues système & améliorations
    • pg_stat_progress_copy, pg_stat_wal, pg_lock.waitstart, query_id
  • Lecture asynchrone des tables distantes
  • Paramétrage par défaut adapté aux machines plus récentes
  • Améliorations diverses :
    • réplications physique et logique
    • quelques facilités de syntaxe (triggers, tableaux en PL/pgSQL)
  • Performances :
    • connexions en lecture seule plus nombreuses
    • index…

Version 15

  • Octobre 2022 - Novembre 2027
  • Nombreuses améliorations incrémentales
    • dont en réplication logique
  • Commande MERGE
  • Performances :
    • DISTINCT parallélisable
    • pg_dump & sauvegardes, recovery, partitionnement
  • Changements notables :
    • public n’est plus accessible en écriture à tous
    • sauvegarde PITR exclusive disparaît

Version 16

  • Septembre 2023 - Novembre 2028
  • Plus de tris incrémentaux (DISTINCT…)
  • Réplication logique depuis un secondaire
  • Expressions régulières dans pg_hba.conf
  • Vues systèmes améliorées : pg_stat_io
  • Compression lz4 ou zstd pour pg_dump
  • Optimisation et améliorations diverses (parallélisation…)

Petit résumé

  • Versions 7.x :
    • fondations
    • durabilité
  • Versions 8.x :
    • fonctionnalités
    • performances
  • Versions 9.x :
    • réplication physique
    • extensibilité
  • Versions 10 à 16 :
    • réplication logique
    • parallélisation
    • partitionnement
  • … et la 17 approche

Quelle version utiliser en production ?

  • 12 et inférieures
    • Danger !
    • planifier une migration urgemment !
  • 12, 13, 14, 15, 16
    • mises à jour mineures uniquement
  • 16
    • nouvelles installations
    • nouveaux développements (ou future 17 ?)
  • Tableau comparatif des versions

Versions dérivées / Forks

Entre de nombreux autres :

  • Compatibilité Oracle :
    • EnterpriseDB
  • Data warehouse :
    • Greenplum, Netezza
  • Forks :
    • Amazon RedShift, Aurora, Neon…
    • attenton : support, extensions…
  • Extensions :
    • Citus
    • timescaledb
  • Packages avec des outils & support
  • Bases compatibles

Quelques projets satellites

PostgreSQL n’est que le moteur ! Besoin d’outils pour :

  • Administration
  • Sauvegarde
  • Supervision
  • Migration
  • SIG

Administration, Développement, Modélisation

Entre autres, dédiés ou pas :

  • Administration :
    • pgAdmin4
    • temBoard
  • Développement :
    • DBeaver
  • Modélisation :
    • pgModeler

Sauvegardes

Supervision

  • Nagios/Icinga2 :
    • check_pgactivity
    • check_postgres
  • Prometheus : postgres_exporter
  • PoWA

Audit

Migration

PostGIS

Logo Postgis
  • Projet indépendant, GPL, https://postgis.net/
  • Module spatial pour PostgreSQL
    • Extension pour types géométriques/géographiques & outils
    • La référence des bases de données spatiales
    • « quelles sont les routes qui coupent le Rhône ? »
    • « quelles sont les villes adjacentes à Toulouse ? »
    • « quels sont les restaurants situés à moins de 3 km de la Nationale 12 ? »

Sponsors & Références

Sponsors principaux

  • Sociétés se consacrant à PostgreSQL :
    • Crunchy Data (USA) : Tom Lane, Stephen Frost, Joe Conway…
    • EnterpriseDB (USA) : Bruce Momjian, Robert Haas, Dave Page…
    • 2nd Quadrant (R.U.) : Simon Riggs, Peter Eisentraut…
      • racheté par EDB
    • PostgresPro (Russie) : Oleg Bartunov, Alexander Korotkov
    • Cybertec (Autriche), Dalibo (France), Redpill Linpro (Suède), Credativ (Allemagne)…
  • Sociétés vendant un fork ou une extension :
    • Citusdata (Microsoft), Pivotal (VMWare), TimescaleDB

Autres sponsors

  • Autres sociétés :
    • VMWare, Rackspace, Heroku, Conova, Red Hat, Microsoft
    • NTT (streaming replication), Fujitsu, NEC
  • Cloud
    • nombreuses

Références

  • Météo France
  • IGN
  • RATP, SNCF
  • CNAF
  • MAIF, MSA
  • Le Bon Coin
  • Air France-KLM
  • Société Générale
  • Carrefour, Leclerc, Leroy Merlin
  • Instagram, Zalando, TripAdvisor
  • Yandex
  • CNES
  • …et plein d’autres

Le Bon Coin

  • Site de petites annonces
  • 4è site le plus consulté en France (2017)
  • 27 millions d’annonces en ligne, 800 000 nouvelles chaque jour
  • Instance PostgreSQL principale : 3 To de volume, 3 To de RAM
  • 20 serveurs secondaires

À la rencontre de la communauté

  • Cartographie du projet
  • Pourquoi participer
  • Comment participer

PostgreSQL, un projet mondial

Carte des hackers

PostgreSQL Core Team

Core team

Contributeurs

Contributeurs

Qui contribue du code ?

  • Principalement des personnes payées par leur société
  • 30 committers
    • Tom Lane
    • Andres Freund
    • Peter Eisentraut
    • Nikita Glukhov
    • Álvaro Herrera
    • Michael Paquier
    • Robert Haas
    • …et beaucoup d’autres
  • Commitfests : tous les 2 mois

Répartition des développeurs

Répartition des développeurs

Utilisateurs

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

Pourquoi participer

  • Rapidité des corrections de bugs
  • Préparer les migrations / tester les nouvelles versions
  • Augmenter la visibilité du projet
  • Créer un réseau d’entraide

Ressources web de la communauté

Documentation officielle

Serveurs francophones

Listes de discussions / Listes d’annonces

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

IRC

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

Wiki

L’avenir de PostgreSQL

  • PostgreSQL est devenu la base de données de référence
  • Grandes orientations :
    • réplication logique
    • meilleur parallélisme
    • gros volumes
  • Prochaine version, la 17
  • Stabilité économique
  • De plus en plus de (gros) clients
  • Le futur de PostgreSQL dépend de vous !

Conclusion

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

Bibliographie

  • Documentation officielle (préface)
  • Articles fondateurs de M. Stonebracker (1987)
  • Présentation du projet PostgreSQL (Guillaume Lelarge, 2008)
  • Looking back at PostgreSQL (J.M. Hellerstein, 2019)

Questions

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

Quiz

Découverte des fonctionnalités

PostgreSQL

Au menu

  • Fonctionnalités du moteur

  • Objets SQL

  • Connaître les différentes fonctionnalités et possibilités

  • Découvrir des exemples concrets

Fonctionnalités du moteur

  • Standard SQL
  • ACID : la gestion transactionnelle
  • Niveaux d’isolation
  • Journaux de transactions
  • Administration
  • Sauvegardes
  • Réplication
  • Supervision
  • Sécurité
  • Extensibilité

Respect du standard SQL

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

ACID

Gestion transactionnelle : la force des bases de données relationnelles :

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

MVCC

  • MultiVersion Concurrency Control
  • Le « noyau » de PostgreSQL
  • Garantit les propriétés ACID
  • Permet les accès concurrents 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

Transactions

  • Une transaction = ensemble atomique d’opérations
  • « Tout ou rien »
  • BEGIN obligatoire pour grouper des modifications
  • COMMIT pour valider
    • y compris le DDL
  • Perte des modifications si :
    • ROLLBACK / perte de la connexion / arrêt (brutal ou non) du serveur
  • SAVEPOINT pour sauvegarde des modifications d’une transaction à un instant t
  • Pas de transactions imbriquées

Niveaux d’isolation

  • 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
  • Nous pouvons spécifier le niveau d’isolation au démarrage d’une transaction
    • BEGIN ISOLATION LEVEL xxx;
  • Niveaux d’isolation supportés
    • read commited (défaut)
    • repeatable read
    • serializable

Fiabilité : journaux de transactions

  • Write Ahead Logs (WAL)
  • Chaque donnée est écrite 2 fois sur le disque !
  • Sécurité quasiment infaillible
  • Avantages :
    • WAL : écriture séquentielle
    • un seul sync sur le WAL
    • fichiers de données : en asynchrone
    • sauvegarde PITR et de la réplication fiables

Sauvegardes

  • Sauvegarde des fichiers à froid
    • outils système
  • Import/Export logique
    • pg_dump, pg_dumpall, pg_restore
  • Sauvegarde physique à chaud
    • pg_basebackup
    • sauvegarde PITR

Réplication

  • Réplication physique
    • instance complète
    • même architecture
  • Réplication logique (PG 10+)
    • table par table / colonne par colonne avec ou sans filtre (PG 15)
    • voire opération par opération
  • Asynchrones ou synchrone
  • Asymétriques

Extensibilité

  • Extensions
    • CREATE EXTENSION monextension ;
    • nombreuses : contrib, packagées… selon provenance
    • notion de confiance (v13+)
    • dont langages de procédures stockées !
  • Système des hooks
  • Background workers

Sécurité

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

Objets SQL

  • Instances
  • Objets globaux :
    • Bases
    • Rôles
    • Tablespaces
  • Objets locaux :
    • Schémas
    • Tables
    • Vues
    • Index
    • Routines

Organisation logique

Organisation logique d’une instance

Instances

  • Une instance
    • un répertoire de données
    • un port TCP
    • une configuration
    • plusieurs bases de données
  • Plusieurs instances possibles sur un serveur

Rôles

  • Utilisateurs / Groupes
    • Utilisateur : Permet de se connecter
  • Différents attributs et droits

Tablespaces

  • Répertoire physique contenant les fichiers de données de l’instance
  • Une base peut
    • se trouver sur un seul tablespace
    • être répartie sur plusieurs tablespaces
  • Permet de gérer l’espace disque et les performances
  • Pas de quota

Bases

  • Conteneur hermétique
  • Un rôle ne se connecte pas à une instance
    • il se connecte forcément à une base
  • Une fois connecté, il ne voit que les objets de cette base
    • contournement : foreign data wrappers, dblink

Schémas

  • Espace de noms
  • Sous-ensemble de la base
  • Non lié à un utilisateur
  • Résolution des objets : search_path
  • pg_catalog, information_schema
    • pour catalogues système (lecture seule !)

Tables

Par défaut, une table est :

  • Permanente
    • si temporaire, vivra le temps de la session (ou de la transaction)
  • Journalisée
    • si unlogged, perdue en cas de crash, pas de réplication
  • Non partitionnée
    • partitionnement possible par intervalle, valeur ou hachage

Vues

  • 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
  • Vues matérialisées
    • à rafraîchir à une certaine fréquence

Index

  • Algorithmes supportés
    • B-tree (par défaut)
    • Hash
    • GiST / SP-GiST
    • GIN
    • BRIN
    • Bloom
  • Type
    • Mono ou multicolonne
    • Partiel
    • Fonctionnel
    • Couvrant

Types de données

  • Types de base
    • natif : int, float
    • standard SQL : numeric, char, varchar, date, time, timestamp, bool
  • Type complexe
    • tableau
    • JSON (jsonb), XML
    • vecteur (données LLM, FTS)
  • Types métier
    • réseau, géométrique, etc.
  • Types créés par les utilisateurs
    • structure SQL, C, Domaine, Enum

Contraintes

  • 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 &&)

Colonnes à valeur générée

  • Valeur calculée à l’insertion
  • DEFAULT
  • Identité
    • GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
  • Expression
    • GENERATED ALWAYS AS ( generation_expr ) STORED

Langages

  • Procédures & fonctions en différents langages
  • Par défaut : SQL, C et PL/pgSQL
  • Extensions officielles : Perl, Python
  • Mais aussi Java, Ruby, Javascript…
  • Intérêts : fonctionnalités, performances

Fonctions & procédures

  • Fonction
    • renvoie une ou plusieurs valeurs
    • SETOF ou TABLE pour plusieurs lignes
  • Procédure (v11+)
    • ne renvoie rien
    • peut gérer le transactionnel dans certains cas

Opérateurs

  • Dépend d’un ou deux types de données
  • Utilise une fonction prédéfinie :
CREATE OPERATOR //
  (FUNCTION=division0,
  LEFTARG=integer,
  RIGHTARG=integer);

Triggers

  • Opérations : INSERT, UPDATE, DELETE, TRUNCATE
  • Trigger sur :
    • une colonne, et/ou avec condition
    • une vue
    • DDL
  • Tables de transition
  • Effet sur :
    • l’ensemble de la requête (FOR STATEMENT)
    • chaque ligne impactée (FOR EACH ROW)
  • N’importe quel langage supporté

Questions

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

Quiz

Installation de PostgreSQL

PostgreSQL

Introduction

  • Pré-requis
  • Installation depuis les sources
  • Installation depuis les binaires
    • installation à partir des paquets
    • installation sous Windows
  • Premiers réglages
  • Mises à jours

Pré-requis minimaux pour une instance PostgreSQL

  • À peu près n’importe quel OS actuel
    • Linux (conseillé)
    • Unix propriétaires (dont macOS)
    • Windows
  • N’importe quelle machine
    • …selon les besoins
    • 64 bits conseillé
  • Stockage fiable
  • Pas d’antivirus !

Installation à partir des sources

Étapes :

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

Téléchargement

Phases de compilation/installation

  • Processus standard :
$ tar xvfj postgresql-<version>.tar.bz2
$ cd postgresql-<version>
$ ./configure         # beaucoup d'options !
$ make
$ sudo make install   # vers /usr/local/pgsql
$ cd contrib
$ make
$ sudo make install   # vers /usr/local/pgsql/…/

Options pour ./configure

  • Quelques options de configuration notables :
    • --prefix=répertoire
    • --with-pgport=port
    • --with-openssl
    • --enable-nls
    • --with-perl, --with-python
  • Pour les retrouver à postériori :
$ pg_config --configure

Tests de non régression

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

Création de l’utilisateur

  • Jamais root
  • Utilisateur dédié
    • propriétaire des répertoires et fichiers
    • lancer PostgreSQL
    • traditionnellement : postgres
  • 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
# Données :
export PGDATA=/usr/local/pgsql/data

Création du répertoire de données de l’instance

$ initdb -D /usr/local/pgsql/data
  • Une seule instance !
  • Options d’emplacement :
    • --data pour les fichiers de données
    • --waldir pour les journaux de transactions
  • Autres options :
    • --data-checksums : sommes de contrôle (conseillé !)
    • et : chemin des journaux, mot de passe, encodage…

Lancement et arrêt

  • Avec le script de l’OS (recommandé) ou pg_ctl :
systemctl [action] postgresql     # systemd
/etc/init.d/postgresql [action]   # SysV Init
service postgresql [action]       # idem
$ pg_ctl --pgdata /usr/local/pgsql/data --log logfile [action]
         --mode [smart|fast|immediate]
  • [action] dépend du besoin :
    • start / stop / restart
    • reload pour recharger la configuration
    • status
    • promote, logrotate, kill

Installation à partir des paquets Linux

  • Packages Debian
  • Packages RPM

Paquets Debian officiels

  • Nombreux paquets disponibles :
    • serveur, client, contrib, docs
    • extensions, outils
  • apt install postgresql-<version majeure>
    • installe les binaires
    • crée l’utilisateur postgres
    • exécute initdb
    • démarre le serveur

Paquets Debian : spécificités

  • Plusieurs versions majeures installables
  • Wrappers/scripts pour la gestion des différentes instances :
    • pg_lsclusters
    • pg_ctlcluster
      • ou : systemctl stop|start postgresql-15@main
    • pg_createcluster
    • etc.
  • Respect de la FHS
  • Configuration dans /etc/postgresql/

Paquets Debian communautaires

  • La communauté met des paquets Debian à disposition :
  • Synchrone avec le projet PostgreSQL
  • Ajout du dépôt dans /etc/apt/sources.list.d/pgdg.list
  • Utilisation chaudement conseillée

Paquets Red Hat communautaires : yum.postgresql.org

Paquets Red Hat communautaires : installation

sudo dnf install -y \
     https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql

sudo dnf install -y postgresql16-server
# dont : utilisateur postgres

sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl enable postgresql-16
sudo systemctl start postgresql-16

Paquets Red Hat communautaires : spécificités

  • Paquets séparés serveur, client, contrib
  • /usr/pgsql-XX/bin: binaires
  • Initialisation manuelle (postgresql-15-setup initdb)
    • vers : /var/lib/pgsql/XX/data
  • Gestion par systemd
  • Particularités :
    • plusieurs versions majeures installables
    • configuration : dans le répertoire de données

Utiliser PostgreSQL dans un conteneur

docker run --name pg16 -e POSTGRES_PASSWORD=mdpsuperfort -d postgres
docker exec -it pg16 -U postgres
  • Image officielle Docker Inc (pas PGDG !) : https://hub.docker.com/_/postgres
  • Très pratique pour le développement
  • Beaucoup de possibilités avancées avec docker-compose
  • Ne jamais lancer 2 conteneurs Docker sur un même PGDATA
  • Une base de données est-elle faite pour du docker ?
    • supervision système délicate

Installation sous Windows

  • Un seul installeur graphique disponible, proposé par EnterpriseDB
  • Ou archive des binaires

Installeur graphique

Installeur graphique - bienvenue

Industrialisation avec pglift

  • Déploiement et infrastructure as code :
    • ligne de commande
    • collections Ansible
  • Couverture fonctionnelle :
    • Sauvegardes avec pgBackRest
    • Supervision avec Prometheus
    • Administration avec temBoard
    • Analyse avec PoWA
    • Haute disponibilité avec Patroni
    • Intégration système avec systemd ou rsyslog

pglift : fichier de configuration

prefix: /srv # fichier /etc/pglift/settings.yaml
postgresql:
  auth:
    host: scram-sha-256
prometheus:
  execpath: /usr/bin/prometheus-postgres-exporter
pgbackrest:
  repository:
    mode: path
    path: /srv/pgsql-backups
powa: {}
systemd: {}
rsyslog: {}

pglift : exemples de commandes

  • Initialisation
pglift instance create main --pgbackrest-stanza=main
  • Modification de configuration
pglift pgconf -i main set log_connections=on
  • Sauvegarde physique
pglift instance backup main
  • Utilisation des outils de l’instance
pglift instance exec main -- psql
pglift instance exec main -- pgbackrest info

Premiers réglages

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

Sécurité

  • Politique d’accès :
    • pour l’utilisateur postgres système
    • pour le rôle postgres
  • Règles d’accès à l’instance dans pg_hba.conf

Configuration minimale

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

Précédence des paramètres

Ordre de précédence des paramètres

Configuration des connexions : accès au serveur

  • listen_addresses = '*' (systématique)
  • port = 5432
  • password_encryption = scram-sha-256 (v10+)

Configuration du nombre de connexions

  • max_connections = 100
  • 1 connexion = 1 processus serveur
  • Compromis entre
    • CPU / nombre requêtes actives / RAM / complexité
  • Danger si trop haut !
    • performances (même avec des connexions inactives)
    • risque de saturation
  • Possibilité de réserver quelques connexions pour l’administration

Configuration de la mémoire partagée

  • shared_buffers = (?)GB
    • 25 % de la RAM généralement
    • max 40 %
    • complémentaire du cache OS

Configuration : mémoire des processus

  • work_mem

    • par processus, voire nœud
    • valeur très dépendante de la charge et des requêtes
    • fichiers temporaires vs saturation RAM
  • × hash_mem_multiplier

  • maintenance_work_mem

  • Pas de limite stricte à la consommation mémoire des sessions

    • Augmenter prudemment & superviser

Configuration des journaux de transactions 1/2

fsync = on (si vous tenez à vos données)

Configuration des journaux de transactions 2/2

Niveaux de cache et fsync

Configuration des traces

  • Selon système/distribution :
    • log_destination
    • logging_collector
    • emplacement et nom différent pour postgresql-????.log
  • log_line_prefix à compléter :
    • log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '
  • lc_messages = C (anglais)

Configuration des tâches de fond

Laisser ces deux paramètres à on :

  • autovacuum
  • track_counts

Se faciliter la vie

Mise à jour

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

Recommandations

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

Mise à jour mineure

  • 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

Mise à jour majeure

  • 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
    • penser aux extensions et aux outils
  • Pour mettre à jour
    • mise à jour des binaires
    • et mise à jour/traitement des fichiers de données
  • 3 méthodes
    • dump/restore
    • réplication logique
    • pg_upgrade

Mise à jour majeure par dump/restore

  • Méthode historique
  • Simple et sans risque
    • mais d’autant plus longue que le volume de données est important
  • Outils :
    • pg_dumpall -g puis pg_dump
    • psql puis pg_restore

Mise à jour majeure par réplication logique

  • Possible entre versions 10 et supérieures
  • Bascule très rapide
  • Et retour possible

Mise à jour majeure par pg_upgrade

  • pg_upgrade : fourni avec PostgreSQL
  • Prérequis : pas de changement de format des fichiers entre versions
  • Nécessite les deux versions sur le même serveur
  • Support des serveurs PostgreSQL à migrer :
    • version minimale 9.2 pour pg_upgrade v15
    • version minimale 8.4 sinon

Mise à jour de l’OS

Si vous migrez aussi l’OS ou déplacez les fichiers d’une instance :

  • compatibilité architecture
  • compatibilité librairies
    • réindexation parfois nécessaire
    • ex : Debian 10 et glibc 2.28

Conclusion

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

Pour aller plus loin

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

Questions

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

Quiz

Travaux pratiques

Installation depuis les paquets binaires du PGDG

Installation à partir des sources (optionnel)

Travaux pratiques (solutions)

Installation de PostgreSQL depuis les paquets communautaires

Outils graphiques et console

PostgreSQL

Préambule

Les outils graphiques et console :

  • les outils graphiques d’administration
  • la console
  • les outils de contrôle de l’activité
  • les outils DDL
  • les outils de maintenance

Plan

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

Outils console de PostgreSQL

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

Outils : Gestion des bases

  • createdb : ajouter une nouvelle base de données
  • createuser : ajouter un nouveau compte utilisateur
  • dropdb : supprimer une base de données
  • dropuser : supprimer un compte utilisateur

Outils : Sauvegarde / Restauration

  • Sauvegarde logique, pour une instance
    • pg_dumpall : sauvegarder l’instance PostgreSQL
  • Sauvegarde logique, pour une base de données
    • pg_dump : sauvegarder une base de données
    • pg_restore : restaurer une base de données PostgreSQL
  • Sauvegarde physique :
    • pg_basebackup
    • pg_verifybackup

Outils : Maintenance

  • Maintenance des bases
    • vacuumdb : récupérer l’espace inutilisé, statistiques
    • clusterdb : réorganiser une table en fonction d’un index
    • reindexdb : réindexer

Outils : Maintenance de l’instance

  • initdb : création d’instance
  • pg_ctl : lancer, arrêter, relancer, promouvoir l’instance
  • pg_upgrade : migrations majeures
  • pg_config, pg_controldata : configuration

Autres outils en ligne de commande

  • pgbench pour des tests
  • Outils liés à la réplication/sauvegarde physique, aux tests, analyse…

Chaînes de connexion

Pour se connecter à une base :

  • paramètres propres aux outils
  • via la libpq
    • variables d’environnement
    • par chaînes clés/valeur
    • par chaînes URI
    • idem en Python,PHP,Perl
  • JDBC/.NET/ODBC ont des syntaxes spécifiques

Paramètres

Outils habituels, et très souvent :

$ psql -h serveur -d mabase -U nom -p 5432
Option Variable Valeur par défaut
-h HÔTE $PGHOST /tmp, /var/run/postgresql
-p PORT $PGPORT 5432
-U NOM $PGUSER nom de l’utilisateur OS
-d base $PGDATABASE nom de l’utilisateur PG
$PGOPTIONS options de connexions

Autres variables d’environnement

  • $PGAPPNAME
  • $PGSSLMODE
  • $PGPASSWORD

Chaînes libpq clés/valeur

psql "host=serveur1  user=jeanpierre  dbname=comptabilite"
psql -d "host=serveur1  port=5432  user=jeanpierre  dbname=comptabilite
     sslmode=require  application_name='chargement'
     options='-c work_mem=30MB' "

Chaînes URI

psql -d "postgresql://jeanpierre@serveur1:5432/comptabilite"
psql \
"postgres://jeanpierre@serveur1/comptabilite?sslmode=require\
&options=-c%20synchronous_commit%3Doff"
psql -d postgresql://serveur1/comptabilite?user=jeanpierre\&port=5432

Connexion avec choix automatique du serveur

psql "host=serveur1,serveur2,serveur3
      port=5432,5433,5434
      user=jeanpierre  dbname=comptabilite
      target_session_attrs=read-write
      load_balance_hosts=random"   # v16

Authentification d’un client (outils console)

  • En interactif (psql)
    • -W | --password
    • -w | --no-password
  • Variable $PGPASSWORD
  • À préférer : fichier .pgpass
    • chmod 600 .pgpass
    • nom_hote:port:database:nomutilisateur:motdepasse

La console psql

  • Un outil simple pour
    • les opérations courantes
    • les tâches de maintenance
    • l’exécution des scripts
    • les tests
postgres$ psql
  base=#

Obtenir de l’aide et quitter

  • Obtenir de l’aide sur les commandes internes psql
    • \?
  • Obtenir de l’aide sur les ordres SQL
    • \h <COMMANDE>
  • Quitter
    • \q ou ctrl-D
    • quit ou exit (v11)

Gestion de la connexion

  • Modifier le mot de passe d’un utilisateur
    • \password nomutilisateur
  • Quelle est la connexion courante ?
    • \conninfo
    • SELECT current_user,session_user,system_user;
  • Se connecter à une autre base :
    • \c ma base
    • \c mabase utilisateur serveur 5432

Catalogue système : objets utilisateurs

Lister :

  • les bases de données
    • \l , \l+
  • les tables
    • \d, \d+, \dt , \dt+
  • les index
    • \di, \di+
  • les schémas
    • \dn
  • les fonctions & procédures
    • \df[+]
  • etc…

Catalogue système : rôles et accès

  • Lister les rôles (utilisateurs et groupes)
    • \du[+]
  • Lister les droits d’accès
    • \dp
  • Lister les droits d’accès par défaut
    • \ddp
    • ALTER DEFAULT PRIVILEGES
  • Lister les configurations par rôle et par base
    • \drds

Visualiser le code des objets

  • Voir les vues ou les fonctions & procédures
    • \dv, \df
  • Code d’une vue
    • \sv
  • Code d’une procédure stockée
    • \sf

Configuration

  • Lister les paramètres correspondants au motif indiqué
    • \dconfig (v15+)

Exécuter des requêtes

  • Exécuter une requête :
SELECT * FROM pg_tables ;
SELECT * FROM pg_tables \g
SELECT * FROM pg_tables \gx   -- une ligne par champ
INSERT INTOVALUES (1) \; INSERT INTOVALUES (2) ; -- 1 transaction
  • Rappel des requêtes:
    • flèche vers le haut
    • \g
    • Ctrl-R suivi d’un extrait de texte représentatif

Afficher le résultat d’une requête

  • \x pour afficher un champ par ligne
  • Affichage par paginateur si l’écran ne suffit pas
  • Préférer less :
    • set PAGER='less -S'
  • Ou outil dédié : pspg
    • \setenv PAGER 'pspg'

Afficher les détails d’une requête

  • \gdesc
  • Afficher la liste des colonnes correspondant au résultat d’exécution d’une requête
    • noms
    • type de données

Exécuter le résultat d’une requête

  • Exécuter le résultat d’une requête
    • \gexec

Manipuler le tampon de requêtes

  • Éditer
    • dernière requête : \e
    • vue : \ev nom_vue
    • fonction PL/pgSQL : \ef nom_fonction
  • Historique :
    • \s

Entrées/sorties

  • Charger et exécuter un script SQL
    • \i fichier.sql
  • Rediriger la sortie dans un fichier
    • \o resultat.out
  • Écrire un texte sur la sortie standard
    • \echo "Texte…"

Gestion de l’environnement système

  • Chronométrer les requêtes
    • \timing on
  • Exécuter une commande OS
    • \! ls -l (sur le client !)
  • Changer de répertoire courant
    • \cd /tmp
  • Affecter la valeur d’une variable d’environnement (v15+)
    • \getenv toto PATH

Variables internes psql

  • Positionner des variables internes
    • \set NOMVAR nouvelle_valeur
  • Variables internes usuelles
    • ON_ERROR_STOP : on / off
    • ON_ERROR_ROLLBACK : on / off / interactive
    • ROW_COUNT : nombre de lignes renvoyées par la dernière requête (v11)
    • ERROR : true si dernière requête en erreur (v11)
  • Ne pas confondre avec SET (au niveau du serveur) !

Variables utilisateur psql

  • Définir une variable utilisateur
    • \set NOMVAR nouvelle_valeur
  • Invalider une variable
    • \unset NOMVAR
  • Stockage du résultat d’une requête :
    • si résultat est une valeur unique
    • Exemple :
    SELECT now() AS maintenant \gset
    SELECT :'maintenant' ;

Tests conditionnels

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

Personnaliser psql

  • Fichier de configuration ~/.psqlrc
    • voire ~/.psqlrc-X.Y ou ~/.psqlrc-X
    • ignoré avec -X
  • Exemple de .psqlrc  :
\set ON_ERROR_ROLLBACK interactive  -- paramétrage de session
\timing on
\set PROMPT1 '%M:%> %n@%/%R%#%x'    -- invite
\set cfg 'SHOW ALL ;'               -- requête utilisable avec :cfg
\set cls '\\! clear;'               -- nettoyer l'écran avec :cls

Écriture de scripts shell

  • Script SQL
  • Script Shell
  • Exemple sauvegarde

Exécuter un script SQL avec psql

  • Avec -c :
psql -c 'SELECT * FROM matable' -c 'SELECT fonction(123)' ;
  • Avec un script :
psql -f nom_fichier.sql
psql < nom_fichier.sql
  • Depuis psql :
    • \i nom_fichier.sql

Gestion des transactions

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

Écrire un script SQL

  • Attention à l’encodage des caractères
    • \encoding
    • SET client_encoding
  • Écriture des requêtes

Les blocs anonymes

  • Bloc procédural anonyme en PL/pgSQL :
DO $$
DECLARE r record;
BEGIN
    FOR r IN (SELECT schemaname, relname
              FROM pg_stat_user_tables
              WHERE coalesce(last_analyze, last_autoanalyze) IS NULL
              ) LOOP
        RAISE NOTICE 'Analyze %.%', r.schemaname, r.relname ;
        EXECUTE 'ANALYZE ' || quote_ident(r.schemaname)
                           || '.' || quote_ident(r.relname) ;
    END LOOP;
END$$;

Utiliser des variables

\set nom_table 'ma_table'
SELECT * FROM :"nom_table";

\set valeur_col1 'test'
SELECT * FROM :"nom_table" WHERE col1 = :'valeur_col1';
\prompt 'invite' nom_variable
\unset variable
psql -v VARIABLE=valeur

Gestion des erreurs

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

Formatage des résultats

  • Sortie simplifiée pour exploitation automatisée : -XAt
    • -t (--tuples-only)
    • -A (--no-align)
    • -X (--no-psqlrc)
    • séparateurs : -F (--field-separator) et -R (--record-separator)
  • Formats HTML ou CSV
    • -H | --html
    • --csv (à partir de la version 12)

Résultats en pivot (tableau croisé)

  • \crosstabview [colV [colH [colD [colonnedetriH]]]]
  • Exécute la requête en tampon
    • au moins 3 colonnes

Formatage dans les scripts SQL

  • Donner un titre au résultat de la requête
    • \pset title 'Résultat de la requête
  • Formater le résultat
    • \pset format html (ou csv…)
  • Diverses options peu utilisées

Scripts & Crontab

  • cron
    • Attention aux variables d’environnement !
  • Ou tout ordonnanceur

Exemple de script de sauvegarde

  • Sauvegarder une base et classer l’archive (squelette) :
#!/bin/bash
# Paramètre : la base
t=$(mktemp)                    # fichier temporaire
pg_dump -Fc "$1" > $t          # sauvegarde
d=$(eval date +%d%m%y-%H%M%S)  # date
mv $t /backup/"${1}_${d}.dump" # déplacement
exit 0
  • …et ajouter la gestion des erreurs !
  • …et les surveiller

Outils graphiques

  • Outils graphiques d’administration
    • temBoard
    • pgAdminIII et pgAdmin 4
    • pgmodeler

temBoard

temBoard - PostgreSQL Remote Control

  • Multi-instances
  • Surveillance OS / PostgreSQL
  • Suivi de l’activité
  • Gestion des performances de PostgreSQL
  • Configuration de chaque instance

temBoard - Vue parc

temBoard

temBoard - Tableau de bord

temBoard

temBoard - Activity

temBoard

temBoard - Supervision

temBoard

temBoard - Configuration

temBoard

temBoard - Maintenance

temBoard

pgAdmin 4

pgAdmin 4 : tableau de bord

pgAdmin 4

DBeaver

  • https://dbeaver.io
  • Version Community sous Licence Apache 2.0
  • Application Java Multiplateforme
  • Version web CloudBeaver aussi disponible
  • Supporte PostgreSQL (et ~ 80 autres SBGD)
  • Version Pro payante et plus complète

DBeaver : fenêtre principale

DBeaver

phpPgAdmin

phpPgAdmin

phpPgAdmin : fonctionnalités

  • Licence: GNU Public License
  • Application web, simple
    • consultation, édition
    • sauvegarde, export
  • Pérennité ?

AdminerEvo

Adminer

AdminerEvo : fonctionnalités

  • https://docs.adminerevo.org/
  • Application web pour utilisateurs
  • Basique mais simple & efficace
  • Et simple : 1 fichier PHP
  • Multibases, multilangues
  • Licence : Apache License ou GPL 2

pgModeler

PgModeler

pgModeler

  • Site officiel : https://pgmodeler.io/
  • Licence : GPLv3
  • Modélisation de base de données
  • Fonctionnalité d’import export
  • Comparaison de base

Conclusion

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

Questions

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

Quiz

Introduction à pgbench

Travaux pratiques

Travaux pratiques (solutions)

Tâches courantes

Introduction

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

Bases

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

Liste des bases

  • Catalogue système : pg_database
  • Commande psql :
    • \l
    • \l+

Modèle (template)

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

Création d’une base

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

Suppression d’une base

  • SQL : DROP DATABASE
    • droit nécessaire : SUPERUSER ou propriétaire de la base
    • prérequis : aucun utilisateur connecté sur la base
    • ou déconnexion forcée (v13)
  • Outil système : dropdb

Modification / configuration

  • ALTER DATABASE
    • pour modifier quelques méta-données
    • pour ajouter, modifier ou supprimer une configuration
  • Catalogue système pg_db_role_setting

Rôles

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

Utilisateurs et groupes

  • « Rôle » = utilisateurs et groupes
  • Ordres SQL
    • CREATE/DROP/ALTER USER
    • CREATE/DROP/ALTER GROUP

Liste des rôles

  • Catalogue système : pg_roles
  • Dans psql : \du

Création d’un rôle

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

Suppression d’un rôle

  • 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

Modification d’un rôle

  • ALTER ROLE
    • pour modifier quelques méta-données
    • pour ajouter, modifier ou supprimer une configuration
  • Catalogue système : pg_db_role_setting

Mot de passe

Le mot de passe ne concerne pas toutes les méthodes d’authentification

  • Par défaut : l’utilisateur n’a pas de mot de passe
    • donc pas de connexion possible
  • Modification :
ALTER ROLE u1 PASSWORD 'supersecret';  -- dangereux
  • Attention à ne pas l’afficher dans les traces
    • fournir un mot de passe déjà chiffré
    • utiliser \password ou createuser
    createuser --login --echo --pwprompt u1

Sécurité des mots de passe

password_encryption = "scram-sha-256"
  • SCRAM-SHA-256 : défaut (≥ v14)

  • MD5 : dépassé, mais défaut jusque v13 !

  • Sécurité :

    • date limite sur le mot de passe (pas le rôle)
    • pas de vérification de la force du mot de passe
    • pas de limite de tentatives échouées (tester fail2ban)
    • itérations SCRAM (v16)

Droits sur les objets

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

Droits sur les objets

  • Donner un droit :
    GRANT USAGE ON SCHEMA unschema TO utilisateur ;
    GRANT SELECT,DELETE,INSERT ON TABLE matable TO utilisateur ;
  • Retirer un droit :
    REVOKE UPDATE ON TABLE matable FROM utilisateur ;
  • Droits spécifiques pour chaque type d’objets :
    ALTER DEFAULT PRIVILEGES IN SCHEMA
    ALTER DEFAULT PRIVILEGES FOR ROLE
  • Avoir le droit de donner le droit :
   WITH GRANT OPTION
  • Groupe implicite : public
  • Schéma par défaut : public lisible par tous (≤ 14) !
   REVOKE ALL ON SCHEMA public FROM public ;

Afficher les droits

  • Colonne *acl sur les tables systèmes
    • datacl pour pg_database
    • relacl pour pg_class
  • Codage role1=xxxx/role2 (format aclitem)
    • role1 : rôle concerné par les droits
    • xxxx : droits parmi xxxx
    • role2 : rôle qui a donné les droits
  • Sous psql :
    • \dp et \z
    • df, \dconfig+ etc..

Droits sur les métadonnées

  • 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
    • peut être un utilisateur ou un groupe
  • REASSIGN OWNER / DROP OWNED

Droits plus globaux 1/2

  • Rôles systèmes d’administration
    • pg_signal_backend
    • pg_database_owner (14+)
    • pg_checkpoint (15+)
    • pg_reserved_connections (16+)
  • Rôles systèmes de supervision
    • pg_read_all_stats
    • pg_read_all_settings
    • pg_stat_scan_tables
    • pg_monitor

Droits plus globaux 2/2

  • Rôles d’accès aux fichiers
    • pg_read_server_files
    • pg_write_server_files
    • pg_execute_server_program
  • Rôles d’accès aux données (14+)
    • pg_read_all_data
    • pg_write_all_data
  • Autres
    • pg_create_subscription(16+)

Héritage des droits

  • 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

Changement de 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

Droits de connexion

  • 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)
  • Selon les 3 premières informations
    • impose une méthode d’authentification

Informations de connexion

Pour se connecter à une base, il faut :

  • 4 informations
    • socket Unix ou adresse/alias IP
    • n° de port
    • nom de la base
    • nom du rôle
  • Fournies explicitement
    • paramètres
    • environnement
  • ou implicitement
    • environnement
    • défauts

Configuration de l’authentification : pg_hba.conf

PostgreSQL utilise les informations de connexion pour choisir la méthode de connexion

  • Fichier de configuration : pg_hba.conf
  • Exemple :
local      DATABASE  USER  METHOD  [OPTIONS]
host       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
hostssl    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
hostnossl  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
  • Clauses include, include_if_exists, include_dir (v16)
  • Contrôle avec la vue pg_hba_file_rules

Exemple de pg_hba.conf

# TYPE  DATABASE         USER      ADDRESS           METHOD
# accès direct par la socket
local    all             all                         peer
# accès en local via réseau (localhost IPv6 et IPv4)
host      all            all      ::1/128            scram-sha-256
host      all            all      127.0.0.0/24       scram-sha-256
# accès distants
hostssl   erp            all      192.168.0.0/16     scram-sha-256
hostssl   logistique     all      192.168.30.0/24    scram-sha-256
hostnossl test           demo     192.168.74.150/32  scram-sha-256
# vieux client
hostssl   compta         durand   192.168.10.99/32   md5
# Connexions de réplication
local   replication      all                         peer
host    replication      all      192.168.74.5/32    scram-sha-256
host    replication      all      127.0.0.1/32       scram-sha-256
host    replication      all      ::1/128            scram-sha-256

pg_hba.conf : colonne type

  • 4 valeurs possibles
    • local
    • host
    • hostssl (ssl=on prérequis)
    • hostnossl

pg_hba.conf : colonne database

À quelle(s) base(s) autoriser la connexion ?

  • mabase
  • base1,base2,base3
  • sameuser / samerole
  • all
  • @fichier.txt (fichier avec plusieurs bases)
  • /db_[1-6], /erp.* (regex, v16+)
  • replication (pseudo-base pour réplication physique)

pg_hba.conf : colonne user

À quel(s) utilisateur(s) permettre la connexion ?

  • unrole
  • role1,role2,role3
  • all
  • +groupe (membres d’un rôle-groupe)
  • @fichier.txt (fichier avec plusieurs utilisateurs)
  • /user_.* (regex, v16+)

pg_hba.conf : colonne adresse IP

  • Uniquement pour host / hostssl / hostnossl
  • Adresse IPv4 ou IPv6 : format CIDR ou avec masque
    • 192.168.1.0/24
    • 192.168.1.1/32
    • 192.168.1.0 255.255.255.0
  • Nom d’hôte possible
    • mais coût recherche DNS

pg_hba.conf : colonne méthode

Quelle méthode d’authentification utiliser ?

  • Interne / externe
    • et options dans la dernière colonne
  • Le client peut en exiger une (v16)

pg_hba.conf : colonne options

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

pg_hba.conf : méthodes internes

  • trust : dangereux !
  • reject : pour interdire
  • password : en clair, à éviter !
  • md5 : déconseillée
  • scram-sha-256

pg_hba.conf : méthodes externes

  • Système externe : ldap, radius
  • Kerberos : gss, sspi
  • SSL : cert
  • OS : peer, pam, ident, bsd

Un (mauvais) exemple de pg_hba.conf

Un exemple:

TYPE    DATABASE  USER        ADDRESS         METHOD
local   all       postgres                    peer
local   web       web                         md5
local   sameuser  all                         peer
host    all       postgres    127.0.0.1/32    peer
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   scram-sha-256

à ne pas suivre…

Mapping : pg_ident.conf

  • Correspondance entre :
    • utilisateurs authentifiés (serveur, LDAP)
    • rôle de la base
  • Fichier pg_ident.conf :
# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
pgadmins        joe                     postgres
pgadmins        zoe                     postgres
  • Prise en compte des modifications : rechargement
  • Includes possibles
  • Contrôle : pg_ident_file_mapping (v15+)

Synchronisation des rôles

  • PostgreSQL externalise l’authentification uniquement
    • Le rôle, ses paramètres, ses héritages sont toujours dans PostgreSQL
  • ldap2pg synchronise les rôles depuis un annuaire LDAP
    • un rôle PostgreSQL absent de l’annuaire est supprimé
    • sait synchroniser les privilèges
    • configuration avancée en YAML
    • https://labs.dalibo.com/ldap2pg

Tâches de maintenance

  • Trois opérations essentielles 
    • VACUUM
    • ANALYZE
    • REINDEX
  • En arrière-plan : démon autovacuum (pour les deux premiers)
  • Optionnellement : automatisable par cron
  • Manuellement : VACUUM ANALYZE table (batchs, gros imports…)

Maintenance : VACUUM

  • VACUUM nomtable ;
    • cartographie les espaces libres pour une réutilisation (& autre maintenance)
    • utilisable en parallèle avec les autres opérations
    • et même automatisé
    • vue pg_stat_progress_vacuum
  • vacuumdb --echo

Maintenance : VACUUM FULL

  • VACUUM FULL nomtable ;
    • défragmente la table
    • réécriture (place nécessaire !)
    • verrou exclusif (ni lecture ni écriture !)
    • réindexation
    • utilisation exceptionnelle
    • vue pg_stat_progress_cluster (v12)

VACUUM vs VACUUM FULL

  • VACUUM
    • maintenance quotidienne
    • entre étapes d’un batch
    • l’autovacuum suffit généralement
  • VACUUM FULL
    • après de grosses modifications
    • exceptionnel

Maintenance : ANALYZE

  • Met à jour les statistiques sur les données pour l’optimiseur de requêtes
  • Géré par l’autovacuum
    • Parfois manuel : batch, ALTER TABLE, tables temporaires…
  • Échantillonnage :
    • default_statistics_target (défaut 100)
    • ALTER TABLE ma_table ALTER ma_colonne SET STATISTICS 500;
    • Attention au temps de planification !
  • Progression avec pg_stat_progress_analyze (v13)

Maintenance : REINDEX

  • Lancer REINDEX régulièrement permet
    • de gagner de l’espace disque
    • d’améliorer les performances
    • de réparer un index corrompu/invalide
  • VACUUM ne provoque pas de réindexation
  • VACUUM FULL réindexe
  • Clause CONCURRENTLY (v12+)
  • Clause TABLESPACE (v14+)

Maintenance : CLUSTER

  • CLUSTER
    • alternative à VACUUM FULL
    • tri des données de la table suivant un index
  • Attention, CLUSTER nécessite près du double de l’espace disque utilisé pour stocker la table et ses index
  • Progression avec pg_stat_progress_cluster

Maintenance : automatisation

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

Maintenance : autovacuum

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

Maintenance : Script de REINDEX

  • Automatisation par cron
  • Recherche des index fragmentés
  • Si clé primaire ou contrainte unique :
    • REINDEX
  • Sinon :
    • CREATE INDEX CONCURRENTLY
  • Exemple

Sécurité

  • Ce qu’un utilisateur standard peut faire
    • et ne peut pas faire
  • Restreindre les droits
  • Chiffrement
  • Corruption de données

Droits par défaut

Un utilisateur standard peut :

  • CONNECT : accéder à toutes les bases de données
  • CREATE :
    • créer des objets dans le schéma public de toute base de données
    • révocation fréquente
    • … mais plus en v15+ !
  • SELECT : voir les données de ses tables
  • INSERT,UPDATE,DELETE,TRUNCATE : les modifier
  • TEMP : créer des objets temporaires
  • CREATE, USAGE ON LANGUAGE : créer des fonctions
  • EXECUTE : exécuter des fonctions définies par d’autres dans le schéma public

Droits par défaut (suite)

Un utilisateur standard peut aussi :

  • récupérer des informations sur l’instance
  • visualiser les sources des vues et des fonctions
  • Modifier des paramètres de la session :
    SET parametre TO valeur ;
    SET LOCAL parametre TO valeur ;
    SHOW parametre ;
    PGOPTIONS='-c param=valeur' psql
  • Vue : pg_settings
  • Dans psql : \dconfig

Droits par défaut (suite)

  • 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

Restreindre les droits

  • 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

Arrêter une requête ou une session

  • Annuler une requête
    • pg_cancel_backend (pid int)
  • Fermer une connexion
    • pg_terminate_backend(pid int, timeout bigint)
    • kill -SIGTERM pid, kill -15 pid (éviter)
  • Jamais kill -9 !!

Chiffrements

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

En cas de crash

  • Ça arrive
  • Redémarrage sans souci, en général
  • Quelques statistiques d’activité sont perdues
    • ANALYZE (voire VACUUM)
  • Et analyser la cause

Corruption de données

  • initdb --data-checksums
  • Détecte les corruptions silencieuses
  • Impact faible sur les performances
  • Fortement conseillé !

Conclusion

  • PostgreSQL demande peu de travail au quotidien
  • À l’installation :
    • veiller aux accès et aux droits
    • mettre la maintenance en place
  • Pour le reste, surveiller :
    • les scripts automatisés
    • le contenu des journaux applicatifs
  • Supervisez le serveur !

Pour aller plus loin

Questions

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

Quiz

Travaux pratiques

Traces maximales

Méthode d’authentification

Création des bases

Mots de passe

Rôles et permissions

Autorisation d’accès distant

VACUUM, VACUUM FULL, DELETE, TRUNCATE

Statistiques

Réindexation

Traces

Travaux pratiques (solutions)

PostgreSQL : Politique de sauvegarde

Datacenter victime d’un incendie, Strasbourg, mars 2021 (image ITBR67)

Introduction

  • Le pire peut arriver
  • Politique de sauvegarde

Au menu

  • Objectifs
  • Approche
  • Points d’attention

Définir une politique de sauvegarde

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

Objectifs

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

Différentes approches

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

RTO/RPO

La politique de sauvegarde découle du :

  • RPO (Recovery Point Objective) : Perte de Données Maximale Admissible
    • faible ou importante ?
  • RTO (Recovery Time Objective) : Durée Maximale d’Interruption Admissible
    • courte ou longue ?
RTO et RPO

Industrialisation

  • Évaluer les coûts humains et matériels
  • Intégrer les méthodes de sauvegardes avec le reste du SI
    • sauvegarde sur bande centrale
    • supervision
    • plan de continuité et de reprise d’activité

Documentation

  • Documenter les éléments clés de la politique :
    • perte de données
    • rétention
    • temps de référence
  • Documenter les processus de sauvegarde et restauration
  • Imposer des révisions régulières des procédures

Règle 3-2-1

  • 3 exemplaires des données
  • 2 sur différents médias
  • 1 hors site (et hors ligne)
  • Un RAID n’est pas une sauvegarde !
  • Le cloud n’est pas une solution magique !

Autres points d’attention

  • Sauvegarder les fichiers de configuration
  • Tester la restauration
    • De nombreuses catastrophes auraient pu être évitées avec un test
    • Estimation de la durée

Conclusion

  • Les techniques de sauvegarde de PostgreSQL sont :
    • complémentaires
    • automatisables
  • La maîtrise de ces techniques est indispensable pour assurer un service fiable.
  • Testez vos sauvegardes !

Quiz

PostgreSQL : Sauvegarde et restauration

PostgreSQL

Introduction

  • 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

Au menu

  • Sauvegardes logiques
  • Sauvegarde physique à froid des fichiers

Sauvegardes logiques

  • À chaud
  • Cohérente
  • Locale ou à distance
  • 2 outils
    • pg_dump
    • pg_dumpall
  • Pas d’impact sur les utilisateurs
    • sauf certaines opérations DDL
  • Jamais inclus :
    • tables systèmes
    • fichiers de configuration

pg_dump

  • Sauvegarde une base de données :
  pg_dump nombase > nombase.dump
  • Sauvegarde complète ou partielle

pg_dump - Format de sortie

Format Dump Restore
plain (SQL) pg_dump -Fp ou pg_dumpall psql
tar pg_dump -Ft pg_restore
custom pg_dump -Fc pg_restore
directory pg_dump -Fd pg_restore

Choix du format de sortie

  • Format plain (SQL)
    • restaurations partielles très difficiles (ou manuelles)
  • Parallélisation du dump
    • uniquement format directory
  • Utilisez les formats binaires
pg_dump -Fc
pg_dump -Fd
  • Et en compléments, les objets globaux
pg_dumpall -g

pg_dump - Compression

  • -z/--compress : compression par zlib
    • de 0 à 9 (défaut : 6)
  • (v16) mieux : zstd, lz4
pg_dump -Z1  # gzip
# v16+
pg_dump -Z gzip:3
pg_dump -Z lz4:'level=12'
pg_dump -Z zstd:'level=22,long'

pg_dump - Fichier ou sortie standard

  • -f : fichier où stocker la sauvegarde
  • sinon : sortie standard

pg_dump - Structure ou données ?

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

pg_dump - Sélection de sections

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

pg_dump - Sélection d’objets

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

pg_dump - Option de parallélisation

  • --jobs <nombre_de_threads>
  • format directory (-Fd) uniquement

pg_dump - Options diverses

  • --create (-C) : recréer la base

    • y compris paramétrage utilisateur sur base (>v11 et format plain)
    • inutile dans les autres formats
  • --no-owner : ignorer le propriétaire

  • --no-privileges : ignorer les droits

  • --no-tablespaces : ignorer les tablespaces

  • --inserts : remplacer COPY par INSERT

  • --rows-per-insert , --on-conflict-do-nothing

  • divers paramètres pour les tables partitionnées

  • -v : progression

pg_dumpall

  • Sauvegarde d’une instance complète
    • objets globaux (utilisateurs, tablespaces…)
    • toutes les bases de données
  • Format texte (SQL) uniquement

pg_dumpall - Fichier ou sortie standard

  • -f nomfichier.dmp
    • fichier de la sauvegarde
  • ou -f -
    • sortie standard

pg_dumpall - Sélection des objets

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

pg_dumpall - Exclure une base

  • --exclude-database (v12+)

pg_dumpall - Options diverses

  • Quelques options partagées avec pg_dump
  • Très peu utilisées

pg_dump/pg_dumpall - Options de connexions

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

Impact des privilèges

  • Les outils se comportent comme des clients pour PostgreSQL
  • Préférer un rôle superutilisateur
  • Sinon :
    • connexion à autoriser
    • le rôle doit pouvoir lire tous les objets à exporter (pg_read_all_data)

Traiter automatiquement la sortie

  • Pour compresser 1 fichier : pg_dump | bzip2
    • utile avec formats plain,tar,custom
  • Outils multithreads de compression, bien plus rapides :
    • pbzip2, pigz
    • xz, zstd

Objets binaires

  • 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

Extensions

  • Option --extension (-e)
    • uniquement si sélection/exclusion (-n/-N et/ou -t/-T)

Restauration d’une sauvegarde logique

  • psql
    • restauration de SQL (option -Fp) :
  • pg_restore
    • restauration binaire (-Ft/-Fc/-Fd)

psql

  • Client standard PostgreSQL
  • Capable d’exécuter des requêtes
    • donc de restaurer une sauvegarde texte (plain)
  • Très limité dans les options de restauration

psql - Options

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

pg_restore

  • Restaure uniquement les sauvegardes au format binaire
    • format autodétecté (-F inutile)
  • Nombreuses options très intéressantes
  • Restaure une base de données
    • complètement ou partiellement

pg_restore - Base de données

  • -d : base de données de connexion
  • -C (--create) :
    • connexion (-d) et CREATE DATABASE
    • connexion à la nouvelle base et exécute le SQL

pg_restore - Fichiers en entrée / sortie

  • Entrée : Fichier à restaurer en dernier argument de la ligne de commande
  • Sortie :
    • -f : fichier SQL ou liste (-l)
    • sortie standard : défaut si < v12, sinon -f -
  • Attention à ne pas écraser la sauvegarde !

pg_restore - Structure ou données ?

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

ou :

  • --section
    • pre-data
    • data
    • post-data

pg_restore - Sélection d’objets

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

pg_restore - Sélection avancée

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

pg_restore - Option de parallélisation

  • -j <nombre_de_threads>
    • formats custom ou directory

pg_restore - Options diverses

  • -O (--no-owner) : ignorer le propriétaire
  • -x (--no-privileges) : ignorer les droits
  • --no-comments : ignorer les commentaires
  • --no-tablespaces : ignorer le tablespace
  • -1 (--single-transaction) : pour tout restaurer en une seule transaction
  • -c (--clean) : pour détruire un objet avant de le restaurer

Autres considérations sur la sauvegarde logique

  • Versions des outils & version du serveur
  • Script de sauvegarde
  • Sauvegarder sans passer par un fichier
  • Statistiques et maintenance après import
  • Durée d’exécution d’une sauvegarde
  • Taille d’une sauvegarde

Versions des outils clients et version de l’instance

  • pg_dump : reconnaît les versions de PG antérieures
  • pg_restore
    • minimum la version du pg_dump utilisé
    • si possible celle du serveur cible
  • Pas de problème entre OS différents

Script de sauvegarde idéal

  • Objets globaux :
pg_dumpall -g
  • Chaque base :
pg_dump -Fc
pg_dump -Fd
  • Outils client >= v11
    • sinon reprendre les paramètres des rôles sur les bases :
    ALTER role xxx IN DATABASE xxx SET param=valeur;
  • Bien tester !

pg_back - Présentation

  • https://github.com/orgrim/pg_back
  • Type de sauvegardes : logiques (pg_dump)
  • Langage : bash (v1) / go (v2)
  • Licence : BSD (libre)
  • Type de stockage : local + export cloud
  • Planification : crontab
  • Unix/Linux (v1 & 2) / Windows (v2)
  • Compression : via pg_dump
  • Versions compatibles : toutes
  • Rétention : durée

Sauvegarde et restauration sans fichier intermédiaire

  • pg_dump -Fp | psql
  • pg_dump -Ft | pg_restore
  • pg_dump -Fc | pg_restore
  • Utilisation des options -h, -p, -d
  • Attention à la gestion des erreurs !

Statistiques et maintenance après import

  • Statistiques non sauvegardées
    • ANALYZE impérativement après une restauration !
  • Pour les performances :
    • VACUUM (ou VACUUM ANALYZE)
  • À plus long terme :
    • VACUUM FREEZE

Durée d’exécution

  • Difficile à chiffrer
  • Dépend de l’activité sur le serveur
  • Option -v
  • Suivre les COPY
    • vue pg_stat_progress_copy (v14+)

Taille d’une sauvegarde logique

  • Difficile à évaluer
  • Contenu des index non sauvegardé
    • donc sauvegarde plus petite
  • Objets binaires :
    • entre 2 et 4 fois plus gros
    • donc sauvegarde plus grosse

Avantages de la sauvegarde logique

  • Simple et rapide
  • Sans interruption de service
  • Indépendante de la version de PostgreSQL
  • Granularité de sélection à l’objet
  • Taille réduite
  • Ne conserve pas la fragmentation des tables et des index
  • Éventuellement depuis un serveur secondaire

Inconvénients de la sauvegarde logique

  • Durée : dépendante des données et de l’activité
  • Restauration : uniquement au démarrage de l’export
  • Efficace si < 200 Go
  • Plusieurs outils pour sauvegarder une instance complète
  • ANALYZE, VACUUM ANALYZE, VACUUM FREEZE après import

Sauvegarde physique à froid des fichiers

  • Instance arrêtée : sauvegarde cohérente
  • Ne pas oublier : journaux, tablespaces, configuration !
  • Outils : système, aucun spécifique à PostgreSQL
    • cp, tar
    • souvent : rsync en 2 étapes : à chaud puis à froid
    • snapshots SAN/LVM (attention à la cohérence)

Avantages des sauvegardes à froid

  • Simple
  • Rapide à la sauvegarde
  • Rapide à la restauration
  • Beaucoup d’outils disponibles

Inconvénients des sauvegardes à froid

  • 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
    • Réindexation si changement OS

Diminuer l’immobilisation

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

Sauvegarde à chaud des fichiers par snapshot de partition

  • Avec certains systèmes de fichiers
  • Avec LVM
  • Avec la majorité des SAN
  • Attention : cohérence entre partitions

Sauvegarde à chaud des fichiers avec PostgreSQL

  • PITR : Point In Time Recovery
    • nécessite d’avoir activé l’archivage des WAL
    • technique avancée, complexe à mettre en place et à maintenir
    • pas de coupure de service
    • outils dédiés (pgBackRest, barman)
  • pg_basebackup
    • sauvegarde ponctuelle

Recommandations générales

  • 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

Matrice

Simplicité Coupure Restauration Fragmentation
copie à froid facile longue rapide conservée
snapshot FS facile aucune rapide conservée
pg_dump facile aucune lente perdue
rsync + copie à froid moyen courte rapide conservée
PITR difficile aucune rapide conservée

Conclusion

  • 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

Questions

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

Quiz

Travaux pratiques

Sauvegardes logiques

Restaurations logiques

Sauvegarde et restauration partielle

(Optionnel) Sauvegarde et restauration par parties avec modification

(Optionnel) Sauvegardes d’objets isolés

Travaux pratiques (solutions)

Architecture & fichiers de PostgreSQL

PostgreSQL

Au menu

  • Rappels sur l’installation
  • Les processus
  • Les fichiers

Rappels sur l’installation

  • Plusieurs possibilités
    • paquets Linux précompilés
    • outils externes d’installation
    • code source
  • Chacun ses avantages et inconvénients
    • Dalibo recommande fortement les paquets précompilés

Paquets précompilés

  • Paquets Debian ou Red Hat suivant la distribution utilisée
  • Préférence forte pour ceux de la communauté
  • Installation du paquet
    • installation des binaires
    • création de l’utilisateur postgres
    • initialisation d’une instance (Debian seulement)
    • lancement du serveur (Debian seulement)
  • (Red Hat) Script de création de l’instance

Installons PostgreSQL

  • Prenons un moment pour
    • installer PostgreSQL
    • créer une instance
    • démarrer l’instance
  • Pas de configuration spécifique pour l’instant

Processus de PostgreSQL

Architecture de PostgreSQL

Introduction

  • PostgreSQL est :

    • multiprocessus (et non multithread)
    • à mémoire partagée
    • client-serveur

Processus d’arrière-plan

# ps f -e --format=pid,command | grep -E "postgres|postmaster"
  96122 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/
  96123  \_ postgres: logger
  96125  \_ postgres: checkpointer
  96126  \_ postgres: background writer
  96127  \_ postgres: walwriter
  96128  \_ postgres: autovacuum launcher
  96131  \_ postgres: logical replication launcher

(sous Rocky Linux 8)

Processus d’arrière-plan (suite)

  • Les processus présents au démarrage :
    • Un processus père : postmaster
    • background writer
    • checkpointer
    • walwriter
    • autovacuum launcher
    • stats collector (avant v15)
    • logical replication launcher
  • et d’autres selon la configuration et le moment :
    • dont les background workers : parallélisation, extensions…

Processus par client (client backend)

  • Pour chaque client, nous avons un processus :
    • créé à la connexion
    • dédié au client…
    • …et qui dialogue avec lui
    • détruit à la déconnexion
  • Un processus gère une requête
    • peut être aidé par d’autres processus
  • Le nombre de processus est régi par les paramètres :
    • max_connections (défaut : 100) - connexions réservées
    • compromis nombre requêtes actives/nombre cœurs/complexité/mémoire

Gestion de la mémoire

Structure de la mémoire sous PostgreSQL

  • Zone de mémoire partagée :
    • shared buffers surtout
  • Zone de chaque processus
    • tris en mémoire (work_mem)

Fichiers

  • Une instance est composée de fichiers :
    • Répertoire de données
    • Fichiers de configuration
    • Fichier PID
    • Tablespaces
    • Statistiques
    • Fichiers de trace

Répertoire de données

postgres$ ls $PGDATA
base              pg_ident.conf  pg_stat      pg_xact
current_logfiles  pg_logical     pg_stat_tmp  postgresql.auto.conf
global            pg_multixact   pg_subtrans  postgresql.conf
log               pg_notify      pg_tblspc    postmaster.opts
pg_commit_ts      pg_replslot    pg_twophase  postmaster.pid
pg_dynshmem       pg_serial      PG_VERSION
pg_hba.conf       pg_snapshots   pg_wal
  • Une seule instance PostgreSQL doit y accéder !

Fichiers de configuration

  • postgresql.conf ( + fichiers inclus)
  • postgresql.auto.conf
  • pg_hba.conf ( + fichiers inclus (v16))
  • pg_ident.conf (idem)

Autres fichiers dans PGDATA

  • PG_VERSION : fichier contenant la version majeure de l’instance
  • postmaster.pid
    • nombreuses informations sur le processus père
    • fichier externe possible, paramètre external_pid_file
  • postmaster.opts

Fichiers de données

  • base/ : contient les fichiers de données
    • un sous-répertoire par base de données
    • pgsql_tmp : fichiers temporaires
  • global/ : contient les objets globaux à toute l’instance

Fichiers liés aux transactions

  • pg_wal/ : journaux de transactions
    • pg_xlog/ avant la v10
    • sous-répertoire archive_status
    • nom : timeline, journal, segment
    • ex : 00000002 00000142 000000FF
  • pg_xact/ : état des transactions
    • pg_clog/ avant la v10
  • mais aussi : pg_commit_ts/, pg_multixact/, pg_serial/ pg_snapshots/, pg_subtrans/, pg_twophase/
  • Ces fichiers sont vitaux !

Fichiers liés à la réplication

  • pg_logical/
  • pg_repslot/

Répertoire des tablespaces

  • pg_tblspc/ : tablespaces
    • si vraiment nécessaires
    • liens symboliques ou points de jonction
    • totalement optionnels

Fichiers des statistiques d’activité

Statistiques d’activité :

  • stats collector (≤v14) & extensions
  • pg_stat_tmp/ : temporaires
  • pg_stat/ : définitif

Autres répertoires

  • pg_dynshmem/
  • pg_notify/

Les fichiers de traces (journaux)

  • Fichiers texte traçant l’activité
  • Très paramétrables
  • Gestion des fichiers soit :
    • par PostgreSQL
    • délégués au système d’exploitation (syslog, eventlog)

Résumé

Architecture de PostgreSQL

Conclusion

  • PostgreSQL est complexe, avec de nombreux composants

  • Une bonne compréhension de cette architecture est la clé d’une bonne administration.

  • Pour aller (beaucoup) plus loin :

Questions

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

Quiz

Travaux pratiques

Processus

Fichiers

Travaux pratiques (solutions)

Configuration de PostgreSQL

PostgreSQL

Au menu

  • Les paramètres en lecture seule
  • Les différents fichiers de configuration
    • survol du contenu
  • Quelques paramétrages importants :
    • tablespaces
    • connexions
    • statistiques
    • optimiseur

Paramètres en lecture seule

  • Options de compilation ou lors d’initdb
  • Quasiment jamais modifiés
    • risque d’incompatabilité des fichiers, avec les outils
  • Tailles de bloc ou de fichier
    • block_size : 8 ko
    • wal_block_size : 8 ko
    • segment_size : 1 Go
    • wal_segment_size : 16 Mo (option --wal-segsize d’initdb en v11)

Fichiers de configuration

  • postgresql.conf
  • postgresql.auto.conf
  • pg_hba.conf
  • pg_ident.conf

postgresql.conf

Fichier principal de configuration :

  • Emplacement :
    • défaut/Red Hat & dérivés : répertoires des données (/var/lib/…)
    • Debian : /etc/postgresql/<version>/<nom>/postgresql.conf
  • Format clé = valeur
  • Sections, commentaires (redémarrage !)

Surcharge des paramètres de postgresql.conf

  • Inclusion externe : include, include_if_exists
  • Surcharge dans cet ordre :
    • ALTER SYSTEM SET … ( renseigne postgresql.auto.conf )
    • paramètres de pg_ctl
    • ALTER DATABASE | ROLE … SET paramètre = …
    • SET / SET LOCAL
  • Consulter :
    • SHOW
    • pg_settings
    • pg_file_settings

Survol de postgresql.conf

  • Emplacement de fichiers
  • Connections & authentification
  • Ressources (hors journaux de transactions)
  • Journaux de transactions
  • Réplication
  • Optimisation de requête
  • Traces
  • Statistiques d’activité
  • Autovacuum
  • Paramétrage client par défaut
  • Verrous
  • Compatibilité

pg_hba.conf et pg_ident.conf

  • Authentification multiple :
    • utilisateur / base / source de connexion
  • Fichiers :
    • pg_hba.conf (Host Based Authentication)
    • pg_ident.conf : si mécanisme externe d’authentification
    • paramètres : hba_file et ident_file

Tablespaces

  • Espace de stockage physique d’objets
    • et non logique !
  • Simple répertoire (hors de PGDATA) + lien symbolique
  • Pour :
    • répartir I/O et volumétrie
    • quotas (par le FS, mais pas en natif)
    • tri sur disque séparé
  • Utilisation selon des droits

Tablespaces : mise en place

CREATE TABLESPACE chaud LOCATION '/SSD/tbl/chaud';

CREATE DATABASE nom TABLESPACE 'chaud';

ALTER DATABASE nom SET default_tablespace TO 'chaud';

GRANT CREATE ON TABLESPACE chaud TO un_utilisateur ;

CREATE TABLE une_table (…) TABLESPACE chaud ;

ALTER TABLE une_table SET TABLESPACE chaud ;  -- verrou !

ALTER INDEX une_table_i_idx SET TABLESPACE chaud ; -- pas automatique

Tablespaces : configuration

  • default_tablespace
  • temp_tablespaces
  • Droits à ouvrir :
GRANT CREATE ON TABLESPACE  ssd_tri  TO  dupont ;
  • Performances :
    • seq_page_cost, random_page_cost
    • effective_io_concurrency, maintenance_io_concurrency
ALTER TABLESPACE chaud SET ( random_page_cost = 1 );
ALTER TABLESPACE chaud SET ( effective_io_concurrency   = 500,
                             maintenance_io_concurrency = 500 ) ;

Gestion des connexions

  • L’accès à la base se fait par un protocole réseau clairement défini :

    • sockets TCP (IPV4 ou IPV6)
    • sockets Unix (Unix uniquement)
  • Les demandes de connexion sont gérées par le postmaster.

  • Paramètres : port, listen_adresses, unix_socket_directories, unix_socket_group et unix_socket_permissions

TCP

  • Paramètres de keepalive TCP
    • tcp_keepalives_idle
    • tcp_keepalives_interval
    • tcp_keepalives_count
  • Paramètre de vérification de connexion
    • client_connection_check_interval (v14)

SSL

  • Paramètres SSL
    • ssl, ssl_ciphers, ssl_renegotiation_limit

Statistiques sur l’activité

  • (Ne pas confondre avec statistiques sur les données !)
  • Statistiques consultable par des vues systèmes
  • Paramètres :
    • track_activities, track_activity_query_size
    • track_counts, track_io_timing et track_functions
    • update_process_title
    • stats_temp_directory (< v15)

Statistiques d’activité collectées

  • Accès logiques (INSERT, SELECT…) par table et index
  • Accès physiques (blocs) par table, index et séquence
  • Activité du Background Writer
  • Activité par base
  • Liste des sessions et informations sur leur activité

Vues système

  • Supervision / métrologie

  • Diagnostiquer

  • Vues système :

    • pg_stat_user_*
    • pg_statio_user_*
    • pg_stat_activity : requêtes
    • pg_stat_bgwriter
    • pg_locks

Statistiques sur les données

  • Statistiques sur les données : pg_stats
    • collectées par échantillonnage (default_statistics_target)
    • ANALYZE table
    • table par table (et pour certains index)
    • colonne par colonne
    • pour de meilleurs plans d’exécution
  • Affiner :
    • Échantillonnage
    ALTER TABLE matable ALTER COLUMN macolonne SET statistics 300 ;
    • Statistiques multicolonnes sur demande
    CREATE STATISTICS nom ON champ1, champ2… FROM nom_table ;

Optimiseur

  • SQL est un langage déclaratif :

    • décrit le résultat attendu (projection, sélection, jointure, etc.)…
    • …mais pas comment l’obtenir
    • c’est le rôle de l’optimiseur

Optimisation par les coûts

  • L’optimiseur évalue les coûts respectifs des différents plans
  • Il calcule tous les plans possibles tant que c’est possible
  • Le coût de planification exhaustif est exponentiel par rapport au nombre de jointures de la requête
  • Il peut falloir d’autres stratégies
  • Paramètres principaux :
    • seq_page_cost, random_page_cost, cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost
    • parallel_setup_cost, parallel_tuple_cost
    • effective_cache_size

Paramètres supplémentaires de l’optimiseur (1)

  • Partitionnement
    • constraint_exclusion
    • enable_partition_pruning
  • Réordonne les tables
    • from_collapse_limit & join_collapse_limit (défaut : 8)
  • Requêtes préparées
    • plan_cache_mode
  • Curseurs
    • cursor_tuple_fraction
  • Mutualiser les entrées-sorties
    • synchronize_seqscans

Paramètres supplémentaires de l’optimiseur (2)

  • GEQO :
    • un optimiseur génétique
    • état initial, puis mutations aléatoires
    • rapide, mais non optimal
    • paramètres : geqo & geqo_threshold (12 tables)

Débogage de l’optimiseur

  • Permet de valider qu’on est en face d’un problème d’optimiseur.
  • Les paramètres sont assez grossiers :
    • défavoriser très fortement un type d’opération
    • pour du diagnostic, pas pour de la production

Conclusion

  • Nombreuses fonctionnalités
    • donc nombreux paramètres

Questions

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

Quiz

Travaux pratiques

Tablespace

Statistiques d’activités, tables et vues système

Statistiques sur les données

Travaux pratiques (solutions)

Mémoire et journalisation dans PostgreSQL

PostgreSQL

Au menu

La mémoire & PostgreSQL :

  • Mémoire partagée
  • Mémoire des processus
  • Les shared buffers & la gestion du cache
  • La journalisation

Rappel de l’architecture de PostgreSQL

Architecture de PostgreSQL

Mémoire partagée

  • L’implémentation dépend de l’OS
  • Quelles zones ?
  • Quelle taille ?

Zones de la mémoire partagée

  • shared_buffers
    • cache disque des fichiers de données
  • wal_buffers
    • cache disque des journaux de transactions
  • max_connections
    • 100… ou plus ?
  • track_activity_query_size
    • à monter
  • verrous
    • max_connections, max_locks_per_transaction
  • etc
  • Modification → redémarrage

Taille de la mémoire partagée

-- v15+
SHOW shared_memory_size ;
SHOW shared_memory_size_in_huge_pages ;

Mémoire par processus

  • work_mem
    • × hash_mem_multiplier (v 13)
  • maintenance_work_mem
    • autovacuum_work_mem
  • temp_buffers
  • Pas de limite stricte à la consommation mémoire d’une session !
    • ni à la consommation totale
  • Augmenter prudemment & superviser

Shared buffers

  • Shared buffers ou blocs de mémoire partagée
    • partage les blocs entre les processus
    • cache en lecture ET écriture
    • double emploi partiel avec le cache du système (voir effective_cache_size)
    • importants pour les performances !
  • Dimensionnement en première intention & avant tests :
    • shared_buffers = 25 % RAM généralement
    • si > 8 Go : Huge Pages, max_wal_size

Notions essentielles de gestion du cache

  • Buffer pin
  • Buffer dirty/clean
  • Compteur d’utilisation
  • Clocksweep

Ring buffer

But : ne pas purger le cache à cause :

  • des grandes tables
  • de certaines opérations
    • Seq Scan (lecture)
    • VACUUM (écritures)
    • COPY, CREATE TABLE AS SELECT…

Contenu du cache

2 extensions en « contrib » :

  • pg_buffercache
  • pg_prewarm

Synchronisation en arrière-plan

Pour synchroniser les blocs « dirty » :

  • Checkpointer essentiellement :
    • lors des checkpoints (surtout périodiques)
    • synchronise toutes les pages dirty
  • Background writer :
    • de façon anticipée, selon l’activité
    • une portion des pages
  • Backends
    • en dernière extrémité

Journalisation

  • Garantir la durabilité des données
  • Base encore cohérente après :
    • arrêt brutal des processus
    • crash machine
  • Écriture des modifications dans un journal avant les fichiers de données
  • WAL : Write Ahead Logging

Journaux de transaction (rappels)

Essentiellement :

  • pg_wal/ : journaux de transactions
    • sous-répertoire archive_status
    • nom : timeline, journal, segment
    • ex : 00000002 00000142 000000FF
  • pg_xact/ : état des transactions
  • Ces fichiers sont vitaux !

Checkpoint

  • « Point de reprise »
  • À partir d’où rejouer les journaux ?
  • Données écrites au moins au niveau du checkpoint
    • il peut durer
  • Processus checkpointer

Déclenchement & comportement des checkpoints - 1

  • Déclenchement périodique (idéal)
    • checkpoint_timeout
  • ou : Quantité de journaux
    • max_wal_size (pas un plafond !)
  • ou : CHECKPOINT
  • À la fin :
    • sync
    • recyclage des journaux
  • Espacer les checkpoints peut réduire leur volumétrie

Déclenchement & comportement des checkpoints - 2

  • Dilution des écritures
    • checkpoint_completion_target × durée moy. entre 2 checkpoints
  • Surveillance :
    • checkpoint_warning
    • log_checkpoints
    • Gardez de la place ! sinon crash…

Paramètres du background writer

Nettoyage selon l’activité, en plus du checkpointer :

  • bgwriter_delay
  • bgwriter_lru_maxpages
  • bgwriter_lru_multiplier
  • bgwriter_flush_after

WAL buffers : journalisation en mémoire

  • Mutualiser les écritures entre transactions
  • Un processus d’arrière plan : walwriter
  • Paramètres notables :
    • wal_buffers
    • wal_writer_flush_after
  • Fiabilité :
    • fsync = on
    • full_page_writes = on
    • sinon corruption !

Compression des journaux

  • wal_compression
    • compression des enregistrements
    • moins de journaux
    • un peu de CPU

Limiter le coût de la journalisation

  • synchronous_commit
    • perte potentielle de données validées
  • commit_delay / commit_siblings
  • Par session

Au-delà de la journalisation

  • Sauvegarde PITR
  • Réplication physique
    • par log shipping
    • par streaming

L’archivage des journaux

  • Repartir à partir :
    • d’une vieille sauvegarde
    • les journaux archivés
  • Sauvegarde à chaud
  • Sauvegarde en continu
  • Paramètres
    • wal_level, archive_mode
    • archive_command ou archive_library

Réplication

  • Log shipping : fichier par fichier
  • Streaming : entrée par entrée (en flux continu)
  • Serveurs secondaires très proches de la production, en lecture

Conclusion

Mémoire et journalisation :

  • complexe
  • critique
  • mais fiable
  • et le socle de nombreuses fonctionnalités évoluées

Questions

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

Quiz

Travaux pratiques

Mémoire partagée

Mémoire de tri

Cache disque de PostgreSQL

Journaux

Travaux pratiques (solutions)

Mécanique du moteur transactionnel & MVCC

PostgreSQL

Introduction

PostgreSQL utilise un modèle appelé MVCC (Multi-Version Concurrency Control).

  • Gestion concurrente des transactions
  • Excellente concurrence
  • Impacts sur l’architecture

Au menu

  • Présentation de MVCC
  • Niveaux d’isolation
  • Implémentation de MVCC de PostgreSQL
  • Les verrous
  • Le mécanisme TOAST

Présentation de MVCC

  • MultiVersion Concurrency Control
  • Contrôle de Concurrence Multi-Version
  • Plusieurs versions du même enregistrement
  • Granularité : l’enregistrement (pas le champ !)

Alternative à MVCC : un seul enregistrement en base

  • Verrouillage en lecture et exclusif en écriture
  • Nombre de verrous ?
  • Contention ?
  • Cohérence ?
  • Annulation ?

Implémentation de MVCC par undo

  • MVCC par undo :
    • une version de l’enregistrement dans la table
    • sauvegarde des anciennes versions
    • l’adresse physique d’un enregistrement ne change pas
    • la lecture cohérente est complexe
    • l’undo est complexe à dimensionner… et parfois insuffisant
    • l’annulation est lente
  • Exemple : Oracle

L’implémentation MVCC de PostgreSQL

  • Copy On Write (duplication à l’écriture)
  • Une version d’enregistrement n’est jamais modifiée
  • Toute modification entraîne une nouvelle version
  • Pas d’undo : pas de contention, ROLLBACK instantané

Niveaux d’isolation

  • 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
  • Le niveau d’isolation au démarrage d’une transaction peut être spécifié :
    • BEGIN ISOLATION LEVEL xxx;

Niveau READ UNCOMMITTED

  • Non disponible sous PostgreSQL
    • si demandé, s’exécute en READ COMMITTED
  • Lecture de données modifiées par d’autres transactions non validées
  • Aussi appelé dirty reads
  • Dangereux
  • Pas de blocage entre les sessions

Niveau READ COMMITTED

  • Niveau d’isolation par défaut
  • La transaction ne lit que les données validées en base
  • Un ordre SQL s’exécute dans un instantané (les tables semblent figées sur la durée de l’ordre)
  • L’ordre suivant s’exécute dans un instantané différent

Niveau REPEATABLE READ

  • Instantané au début de la transaction
  • Ne voit donc plus les modifications des autres transactions
  • Voit toujours ses propres modifications
  • Peut entrer en conflit avec d’autres transactions si modification des mêmes enregistrements

Niveau SERIALIZABLE

  • Niveau d’isolation le plus élevé
  • Chaque transaction se croit seule sur la base
    • sinon annulation d’une transaction en cours
  • Avantages :
    • pas de « lectures fantômes »
    • évite des verrous, simplifie le développement
  • Inconvénients :
    • pouvoir rejouer les transactions annulées
    • toutes les transactions impliquées doivent être sérialisables

Structure d’un bloc

  • 1 bloc = 8 ko
  • ctid = (bloc, item dans le bloc)
Répartition des lignes au sein d’un bloc (schéma de la documentation officielle, licence PostgreSQL)

xmin & xmax

Table initiale :

xmin xmax Nom Solde
100 M. Durand 1500
100 Mme Martin 2200

xmin & xmax (suite)

BEGIN;
UPDATE soldes SET solde = solde - 200 WHERE nom = 'M. Durand';
xmin xmax Nom Solde
100 150 M. Durand 1500
100 Mme Martin 2200
150 M. Durand 1300

xmin & xmax (suite)

UPDATE soldes SET solde = solde + 200 WHERE nom = 'Mme Martin';
xmin xmax Nom Solde
100 150 M. Durand 1500
100 150 Mme Martin 2200
150 M. Durand 1300
150 Mme Martin 2400

xmin & xmax (suite)

xmin xmax Nom Solde
100 150 M. Durand 1500
100 150 Mme Martin 2200
150 M. Durand 1300
150 Mme Martin 2400
  • Comment est effectuée la suppression d’un enregistrement ?
  • Comment est effectuée l’annulation de la transaction 150 ?

CLOG

  • La CLOG (Commit Log) enregistre l’état des transactions.
  • Chaque transaction occupe 2 bits de CLOG
  • COMMIT ou ROLLBACK très rapide

Avantages du MVCC PostgreSQL

  • Avantages :
    • avantages classiques de MVCC (concurrence d’accès)
    • implémentation simple et performante
    • peu de sources de contention
    • verrouillage simple d’enregistrement
    • ROLLBACK instantané
    • données conservées aussi longtemps que nécessaire

Inconvénients du MVCC PostgreSQL

  • Nettoyage des enregistrements
    • VACUUM
    • automatisation : autovacuum
  • Tables plus volumineuses
  • Pas de visibilité dans les index
  • Colonnes supprimées impliquent reconstruction

Le wraparound (1)

  • Wraparound : bouclage du compteur de xmin/xmax
  • 32 bits ~ 4 milliards

Le wraparound (2)

Après 4 milliards de transactions :

Le wraparound (3)

Concrètement ?

  • VACUUM FREEZE
    • géré par l’autovacuum
    • au pire, d’office
    • potentiellement beaucoup d’écritures

Optimisations de MVCC

MVCC a été affiné au fil des versions :

  • Mise à jour HOT (Heap-Only Tuples)
    • si place dans le bloc
    • si aucune colonne indexée modifiée
  • Free Space Map
  • Visibility Map

Verrouillage et MVCC

La gestion des verrous est liée à l’implémentation de MVCC

  • Verrouillage d’objets en mémoire
  • Verrouillage d’objets sur disque
  • Paramètres

Le gestionnaire de verrous

PostgreSQL possède un gestionnaire de verrous

  • Verrous d’objet
  • Niveaux de verrouillage
  • Empilement des verrous
  • Deadlock
  • Vue pg_locks

Verrous sur enregistrement

  • Le gestionnaire de verrous possèdes des verrous sur enregistrements
    • transitoires
    • le temps de poser le xmax
  • Utilisation de verrous sur disque
    • pas de risque de pénurie
  • Les verrous entre transaction se font sur leurs ID

La vue pg_locks

  • pg_locks :
    • visualisation des verrous en place
    • tous types de verrous sur objets
  • Complexe à interpréter :
    • verrous sur enregistrements pas directement visibles

Verrous - Paramètres

  • Nombre :
    • max_locks_per_transaction (+ paramètres pour la sérialisation)
  • Durée :
    • lock_timeout (éviter l’empilement des verrous)
    • deadlock_timeout (défaut 1 s)
  • Trace :
    • log_lock_waits

Mécanisme TOAST

TOAST : The Oversized-Attribute Storage Technique

Que faire si une ligne dépasse d’un bloc ?

Principe du TOAST

  • Table de débordement pg_toast_XXX
    • masquée, transparente
  • Jusqu’à 1 Go par champ (déconseillé)
    • texte, JSON, binaire…
    • compression optionnelle
  • Politiques par champ
    • PLAIN/MAIN/EXTERNAL ou EXTENDED

TOAST et compression

  • pglz (zlib) : défauts
  • lz4 à préférer
    • généralement plus rapide
    • compression équivalente (à vérifier)
  • Mise en place :
default_toast_compression = lz4

ou :

ALTER TABLE t1 ALTER COLUMN c2 SET COMPRESSION lz4 ;

Conclusion

  • PostgreSQL dispose d’une implémentation MVCC complète, permettant :
    • que les lecteurs ne bloquent pas les écrivains
    • que les écrivains ne bloquent pas les lecteurs
    • que les verrous en mémoire soient d’un nombre limité
  • Cela impose par contre une mécanique un peu complexe, dont les parties visibles sont la commande VACUUM et le processus d’arrière-plan autovacuum.

Questions

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

Quiz

Travaux pratiques

Niveaux d’isolation READ COMMITTED et REPEATABLE READ

Niveau d’isolation SERIALIZABLE (Optionnel)

Effets de MVCC

Verrous

Travaux pratiques (solutions)

VACUUM et autovacuum

PostgreSQL

Au menu

  • Principe & fonctionnement du VACUUM
  • Options : VACUUM seul, ANALYZE, FULL, FREEZE
    • ne pas les confondre !
  • Suivi
  • Autovacuum
  • Paramétrages

VACUUM et autovacuum

  • VACUUM : nettoie d’abord les lignes mortes
  • Mais aussi d’autres opérations de maintenance
  • Lancement
    • manuel
    • par le démon autovacuum (seuils)

Fonctionnement de VACUUM

Phase 1/3 : recherche des enregistrements morts

Fonctionnement de VACUUM (suite)

Phase 2/3 : nettoyage des index

Fonctionnement de VACUUM (suite)

Phase 3/3 : suppression des enregistrements morts

NB : L’espace est rarement rendu à l’OS !

Les options de VACUUM

  • Quelle tâche ?
  • Comment améliorer les performances ?
  • Quelles options en cas d’urgence ?
  • Autres options

Tâches d’un VACUUM

Ne pas confondre :

  • VACUUM seul
    • nettoyage des lignes mortes, visibility map, hint bits
  • ANALYZE
    • statistiques sur les données
  • VACUUM (ANALYZE)
    • nettoyage & statistiques
  • VACUUM (FREEZE)
    • gel des lignes
    • parfois gênant ou long
  • VACUUM FULL
    • bloquant !
    • jamais lancé par l’autovacuum

Options de performance de VACUUM

  • Index :
    • PARALLEL (v13+)
  • Taille du buffer ring (v16+)
    • BUFFER_USAGE_LIMIT
    • vacuum_buffer_usage_limit (256 ko)
  • SKIP_DATABASE_STATS, ONLY_DATABASE_STATS (v16+)
  • Éviter les verrous
    • SKIP_LOCKED
    • SET lock_timeout = '1s'

Options pour un VACUUM en urgence

  • INDEX_CLEANUP off
  • PROCESS_TOAST off (v14+)
  • TRUNCATE off

Autres options de VACUUM

  • VERBOSE

  • Ponctuellement :

    • DISABLE_PAGE_SKIPPING

Suivi du VACUUM

  • pg_stat_activity ou top
  • La table est-elle suffisamment nettoyée ?
  • Vue pg_stat_user_tables
    • last_vacuum / last_autovacuum
    • last_analyze / last_autoanalyze
  • log_autovacuum_min_duration

Progression du VACUUM

  • Pour VACUUM simple / VACUUM FREEZE

    • vue pg_stat_progress_vacuum
    • blocs parcourus / nettoyés
    • nombre de passes dans l’index
  • Partie ANALYZE

    • pg_stat_progress_analyze (v13)
  • Manuel ou via autovacuum

  • Pour VACUUM FULL

    • vue pg_stat_progress_cluster (v12)

Autovacuum

  • Processus autovacuum
  • But : ne plus s’occuper de VACUUM
  • Suit l’activité
  • Seuil dépassé => worker dédié
  • Gère : VACUUM, ANALYZE, FREEZE
    • mais pas FULL

Paramétrage du déclenchement de l’autovacuum

  • autovacuum (on !)
  • autovacuum_naptime (1 min)
  • autovacuum_max_workers (3)
    • plusieurs workers simultanés sur une base
    • un seul par table

Déclenchement de l’autovacuum

Seuil de déclenchement =

threshold + scale factor × nb lignes de la table

Déclenchement de l’autovacuum (suite)

  • Pour VACUUM
    • autovacuum_vacuum_scale_factor (20 %)
    • autovacuum_vacuum_threshold (50)
    • (v13) autovacuum_vacuum_insert_threshold (1000)
    • (v13) autovacuum_vacuum_insert_scale_factor (20 %)
  • Pour ANALYZE
    • autovacuum_analyze_scale_factor (10 %)
    • autovacuum_analyze_threshold (50)
  • Adapter pour une grosse table :
   ALTER TABLE table_name SET (autovacuum_vacuum_scale_factor = 0.1);

Paramétrage de VACUUM & autovacuum

  • VACUUM vs autovacuum
  • Mémoire
  • Gestion des coûts
  • Gel des lignes

VACUUM vs autovacuum

VACUUM manuel autovacuum
Urgent Arrière-plan
Pas de limite Peu agressif
Paramètres Les mêmes + paramètres de surcharge

Mémoire

  • Quantité de mémoire allouable
    • maintenance_work_mem / autovacuum_work_mem
    • monté souvent à ½ à 1 Go
  • Impact
    • VACUUM
    • construction d’index

Bridage du VACUUM et de l’autovacuum

  • Pauses régulières après une certaine activité
  • Par bloc traité
    • vacuum_cost_page_hit/_miss/_dirty (1/ 10 ou 2 /20)
    • jusque total de : vacuum_cost_limit (200)
    • pause : vacuum_cost_delay (en manuel : 0 ms !)
  • Surcharge pour l’autovacuum
    • autovacuum_vacuum_cost_limit (identique)
    • autovacuum_vacuum_cost_delay (2 ms)
    • => débit en écriture max : ~40 Mo/s
  • Pour accélérer : augmenter la limite

Paramétrage du FREEZE (1)

Le but est de geler les numéros de transaction assez vite :

Paramétrage du FREEZE (2)

Quand le VACUUM gèle-t-il les lignes ?

  • « Âge » d’une table : age ( pgclass.relfrozenxid )
    • Les blocs nettoyés/gelés sont notés dans la visibility map
  • vacuum_freeze_min_age (50 Mtrx)
    • âge des lignes rencontrées à geler
  • vacuum_freeze_table_age (150 Mtrx)
    • agressif (toute la table)
  • Au plus tard, par l’autovacuum sur toute la table :
    • autovacuum_freeze_max_age (200 Mtrx)
  • Attention après un import massif/migration logique !
    • VACUUM FREEZE préventif en période de maintenance

Autres problèmes courants

L’autovacuum dure trop longtemps

  • Fréquence de passage ?
  • Débit ?
  • Nombre de workers ?
  • Taille vraiment trop grosse ?
Nombre de workers avant et après la réduction d’autovacuum_vacuum_cost_delay

Arrêter un VACUUM ?

  • Lancement manuel ou script
    • risque avec certains verrous
  • Autovacuum
    • interrompre s’il gêne
  • Exception : to prevent wraparound lent et bloquant
    • pg_cancel_backend + VACUUM FREEZE manuel

Ce qui peut bloquer le VACUUM FREEZE

  • Causes :
    • sessions idle in transaction sur une longue durée
    • slot de réplication en retard/oublié
    • transactions préparées oubliées
    • erreur à l’exécution du VACUUM
  • Conséquences :
    • processus autovacuum répétés
    • arrêt des transactions
    • mode single…
  • Supervision :
    • check_pg_activity : xmin, max_freeze_age
    • surveillez les traces !

Résumé des conseils sur l’autovacuum

“Vacuuming is like exercising.
If it hurts, you’re not doing it enough!”

(Robert Haas, PGConf.EU 2023, Prague, 13 décembre 2023)

Résumé des conseils sur l’autovacuum (1/2)

  • Laisser l’autovacuum faire son travail
  • Augmenter le débit autorisé
  • Surveiller last_(auto)analyze / last_(auto)vacuum
  • Nombre de workers
  • Grosses tables, par ex :
ALTER TABLE table_name SET (autovacuum_analyze_scale_factor = 0.01) ;
ALTER TABLE table_name SET (autovacuum_vacuum_threshold = 1000000) ;
  • Mais ne pas hésiter à planifier un vacuumdb quotidien

Résumé des conseils sur l’autovacuum (2/2)

  • Mode manuel
    • batchs / tables temporaires / tables à insertions seules (<v13)
    • si pressé !
  • Danger du FREEZE brutal après migration logique ou gros import
    • prévenir
  • VACUUM FULL : dernière extrémité

Conclusion

  • VACUUM fait de plus en plus de choses au fil des versions
  • Convient généralement
  • Paramétrage apparemment complexe
    • en fait relativement simple avec un peu d’habitude

Questions

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

Quiz

Travaux pratiques

Traiter la fragmentation

Détecter la fragmentation

Gestion de l’autovacuum

Travaux pratiques (solutions)

Partitionnement déclaratif (introduction)

  • Le partitionnement déclaratif apparaît avec PostgreSQL 10

  • Préférer PostgreSQL 13 ou plus récent

  • Ne plus utiliser l’ancien partitionnement par héritage.

Principe & intérêts du partitionnement

  • Faciliter la maintenance de gros volumes
    • VACUUM (FULL), réindexation, déplacements, sauvegarde logique…
  • Performances
    • parcours complet sur de plus petites tables
    • statistiques par partition plus précises
    • purge par partitions entières
    • pg_dump parallélisable
    • tablespaces différents (données froides/chaudes)
  • Attention à la maintenance sur le code

Partitionnement déclaratif

  • Table partitionnée
    • structure uniquement
    • index/contraintes répercutés sur les partitions
  • Partitions :
    • 1 partition = 1 table classique, utilisable directement
    • clé de partitionnement (inclue dans PK/UK)
    • partition par défaut
    • sous-partitions possibles
    • FDW comme partitions possible
    • attacher/détacher une partition

Partitionnement par liste

Partitionnement par liste

Partitionnement par liste : implémentation

CREATE TABLE t1(c1 integer, c2 text) PARTITION BY LIST (c1) ;

CREATE TABLE t1_a PARTITION OF t1 FOR VALUES IN (1, 2, 3) ;
CREATE TABLE t1_b PARTITION OF t1 FOR VALUES IN (4, 5) ;

Partitionnement par intervalle

Partitionnement par intervalle

Partitionnement par intervalle : implémentation

CREATE TABLE logs ( d timestamptz, contenu text) PARTITION BY RANGE (d) ;

CREATE TABLE logs_201901 PARTITION OF logs
                        FOR VALUES FROM ('2019-01-01') TO ('2019-02-01');
CREATE TABLE logs_201902 PARTITION OF logs
                        FOR VALUES FROM ('2019-02-01') TO ('2019-03-01');

CREATE TABLE logs_201912 PARTITION OF logs
                        FOR VALUES FROM ('2019-12-01') TO ('2020-01-01');

CREATE TABLE logs_autres PARTITION OF logs
                        DEFAULT ;               -- pour ne rien perdre

Partitionnement par hachage

Partitionnement par hachage

Partitionnement par hachage : implémentation

  • Hachage des valeurs
  • Répartition homogène
  • Indiquer un modulo et un reste
CREATE TABLE t3(c1 integer, c2 text) PARTITION BY HASH (c1);

CREATE TABLE t3_a PARTITION OF t3 FOR VALUES WITH (modulus 3,remainder 0);
CREATE TABLE t3_b PARTITION OF t3 FOR VALUES WITH (modulus 3,remainder 1);
CREATE TABLE t3_c PARTITION OF t3 FOR VALUES WITH (modulus 3,remainder 2);

Performances & partitionnement

  • Insertions via la table principale
    • quasi aucun impact
  • Lecture depuis la table principale
    • attention à la clé
  • Purge
    • simple DROP ou DETACH
  • Trop de partitions
    • attention au temps de planification

Attacher/détacher une partition

ALTER TABLE logs ATTACH PARTITION logs_archives
FOR VALUES FROM (MINVALUE) TO ('2019-01-01') ;
  • Vérification du respect de la contrainte
    • parcours complet de la table: lent + verrou !
ALTER TABLE logs DETACH PARTITION logs_archives ;
  • Rapide… mais verrou

Supprimer une partition

DROP TABLE logs_2018 ;

Limitations principales du partitionnement déclaratif

  • Temps de planification ! Max ~ 100 partitions
  • Création non automatique
  • Pas d’héritage multiple, schéma fixe
  • Partitions distantes sans propagation d’index
  • Limitations avant PostgreSQL 13/14

Conclusion

  • Préférer une version récente de PostgreSQL
  • Pour plus de détails sur le partitionnement

Quiz

Sauvegarde physique à chaud et PITR

PostgreSQL

Introduction

  • Sauvegarde traditionnelle
    • sauvegarde pg_dump à chaud
    • sauvegarde des fichiers à froid
  • Insuffisant pour les grosses bases
    • long à sauvegarder
    • encore plus long à restaurer
  • Perte de données potentiellement importante
    • car impossible de réaliser fréquemment une sauvegarde
  • Une solution : la sauvegarde PITR

Au menu

  • Mettre en place la sauvegarde PITR
    • sauvegarde : manuelle, ou avec pg_basebackup
    • archivage : manuel, ou avec pg_receivewal
  • Restaurer une sauvegarde PITR
  • Des outils

PITR

  • Point In Time Recovery
  • À chaud
  • En continu
  • Cohérente

Principes

  • Les journaux de transactions contiennent toutes les modifications
  • Il faut les archiver
    • …et avoir une image des fichiers à un instant t
  • La restauration se fait en restaurant cette image
    • …et en rejouant les journaux
    • dans l’ordre
    • entièrement
    • ou partiellement (ie jusqu’à un certain moment)

Avantages

  • Sauvegarde à chaud
  • Rejeu d’un grand nombre de journaux
  • Moins de perte de données

Inconvénients

  • Sauvegarde de l’instance complète
  • Nécessite un grand espace de stockage (données + journaux)
  • Risque d’accumulation des journaux
    • dans pg_wal en cas d’échec d’archivage… avec arrêt si il est plein !
    • dans le dépôt d’archivage si échec des sauvegardes
  • Restauration de l’instance complète
  • Impossible de changer d’architecture (même OS conseillé)
  • Plus complexe

Copie physique à chaud ponctuelle avec pg_basebackup

  • Réalise les différentes étapes d’une sauvegarde
    • via 1 ou 2 connexions de réplication + slots de réplication
    • base backup + journaux nécessaires
  • Copie intégrale,
    • image de la base à la fin du backup
  • Pas de copie incrémentale
  • Configuration : streaming (rôle, droits, slots)
$ pg_basebackup --format=tar --wal-method=stream \
 --checkpoint=fast --progress -h 127.0.0.1 -U sauve \
 -D /var/lib/postgresql/backups/

Sauvegarde PITR

2 étapes :

  • Archivage des journaux de transactions
    • archivage interne
    • ou outil pg_receivewal
  • Sauvegarde des fichiers
    • pg_basebackup
    • ou manuellement (outils de copie classiques)

Méthodes d’archivage

  • Deux méthodes :
    • processus interne archiver
    • outil pg_receivewal (flux de réplication)

Choix du répertoire d’archivage

  • À faire quelle que soit la méthode d’archivage
  • Attention aux droits d’écriture dans le répertoire
    • la commande configurée pour la copie doit pouvoir écrire dedans
    • et potentiellement y lire

Processus archiver : configuration

  • configuration (postgresql.conf)
    • wal_level = replica
    • archive_mode = on (ou always)
    • archive_command = '… une commande …'
    • ou : archive_library = '… une bibliothèque …' (v15+)
    • archive_timeout = '… min'
  • Ne pas oublier de forcer l’écriture de l’archive sur disque
  • Code retour de l’archivage entre 0 (ok) et 125

Processus archiver : lancement

  • Redémarrage de PostgreSQL
    • si modification de wal_level et/ou archive_mode
  • ou rechargement de la configuration

Processus archiver : supervision

  • Vue pg_stat_archiver
  • pg_wal/archive_status/
  • Taille de pg_wal
    • si saturation : Arrêt !
  • Traces

pg_receivewal

  • Archivage via le protocole de réplication
  • Enregistre en local les journaux de transactions
  • Permet de faire de l’archivage PITR
    • toujours au plus près du primaire
  • Slots de réplication obligatoires

pg_receivewal - configuration serveur

  • postgresql.conf :
# configuration  par défaut
max_wal_senders = 10
max_replication_slots = 10
  • pg_hba.conf :
host  replication  repli_user  192.168.0.0/24  scram-sha-256
  • Utilisateur de réplication :
CREATE ROLE repli_user LOGIN REPLICATION PASSWORD 'supersecret'

pg_receivewal - redémarrage du serveur

  • Redémarrage de PostgreSQL
  • Slot de réplication
SELECT pg_create_physical_replication_slot('archivage');

pg_receivewal - lancement de l’outil

  • Exemple de lancement
pg_receivewal -D /data/archives -S archivage
  • Journaux créés en temps réel dans le répertoire de stockage
  • Mise en place d’un script de démarrage
  • S’il n’arrive pas à joindre le serveur primaire
    • Au démarrage de l’outil : pg_receivewal s’arrête
    • En cours d’exécution : pg_receivewal tente de se reconnecter
  • Nombreuses options

Avantages et inconvénients

  • Méthode archiver
    • simple à mettre en place
    • perte au maximum d’un journal de transactions
  • Méthode pg_receivewal
    • mise en place plus complexe
    • perte minimale voire nulle

Sauvegarde PITR manuelle

  • 3 étapes :
    • fonction de démarrage
    • copie des fichiers par outil externe
    • fonction d’arrêt
  • Exclusive : simple… & obsolète ! (< v15)
  • Concurrente : plus complexe à scripter
  • Aucun impact pour les utilisateurs ; pas de verrou
  • Préférer des outils dédiés qu’un script maison

Sauvegarde manuelle - 1/3 : pg_backup_start

SELECT pg_backup_start (

  • un_label : texte
  • fast : forcer un checkpoint ?

)

Sauvegarde manuelle - 2/3 : copie des fichiers

  • Cas courant : snapshot
    • cohérence ? redondance ?
  • Sauvegarde des fichiers à chaud
    • répertoire principal des données
    • tablespaces
  • Copie forcément incohérente (la restauration des journaux corrigera)
  • rsync et autres outils
  • Ignorer :
    • postmaster.pid, log, pg_wal, pg_replslot et quelques autres
  • Ne pas oublier : configuration !

Sauvegarde manuelle - 3/3 : pg_backup_stop

Ne pas oublier !!

SELECT * FROM pg_backup_stop (

  • true : attente de l’archivage

)

Sauvegarde de base à chaud : pg_basebackup

Outil de sauvegarde pouvant aussi servir au sauvegarde basique

  • Backup de base ici sans les journaux :
$ pg_basebackup --format=tar --wal-method=none \
 --checkpoint=fast --progress -h 127.0.0.1 -U sauve \
 -D /var/lib/postgresql/backups/

Fréquence de la sauvegarde de base

  • Dépend des besoins
  • De tous les jours à tous les mois
  • Plus elles sont espacées, plus la restauration est longue
    • et plus le risque d’un journal corrompu ou absent est important

Suivi de la sauvegarde de base

  • Vue pg_stat_progress_basebackup
    • à partir de la v13

Restaurer une sauvegarde PITR

  • Une procédure relativement simple
  • Mais qui doit être effectuée rigoureusement

Restaurer une sauvegarde PITR (1/5)

  • S’il s’agit du même serveur
    • arrêter PostgreSQL
    • supprimer le répertoire des données
    • supprimer les tablespaces

Restaurer une sauvegarde PITR (2/5)

  • Restaurer les fichiers de la sauvegarde
  • Supprimer les fichiers compris dans le répertoire pg_wal restauré
    • ou mieux, ne pas les avoir inclus dans la sauvegarde initialement
  • Restaurer le dernier journal de transactions connu (si disponible)

Restaurer une sauvegarde PITR (3/5)

  • Indiquer qu’on est en restauration

  • Commande de restauration

    • restore_command = '… une commande …'
    • dans postgresql.[auto.]conf

Restaurer une sauvegarde PITR (4/5)

  • Jusqu’où restaurer :
    • recovery_target_name, recovery_target_time
    • recovery_target_xid, recovery_target_lsn
    • recovery_target_inclusive
  • Le backup de base doit être antérieur !
  • Suivi de timeline :
    • recovery_target_timeline : latest ?
  • Et on fait quoi ?
    • recovery_target_action : pause
    • pg_wal_replay_resume pour ouvrir immédiatement
    • ou modifier & redémarrer

Restaurer une sauvegarde PITR (5/5)

  • Démarrer PostgreSQL
  • Rejeu des journaux
  • Vérifier que le point de cohérence est atteint !

Restauration PITR : durée

  • Durée dépendante du nombre de journaux
    • rejeu séquentiel des WAL
  • Accéléré en version 15 (prefetch)

Restauration PITR : différentes timelines

  • Fin de recovery => changement de timeline :
    • l’historique des données prend une autre voie
    • le nom des WAL change
    • fichiers .history
  • Permet plusieurs restaurations PITR à partir du même basebackup
  • Choix :recovery_target_timeline
    • défaut : latest

Restauration PITR : illustration des timelines

Les timelines

Après la restauration

  • Bien vérifier que l’archivage a repris
    • et que les archives des journaux sont complètes
  • Ne pas hésiter à reprendre une sauvegarde complète
  • Bien vérifier que les secondaires ont suivi

Pour aller plus loin

  • Limiter la volumétrie des journaux sauvegardés
  • Quels sont les outils PITR ?

Réduire le nombre de journaux sauvegardés

Volumétrie archivée en fonction de checkpoint_timeout

  • Monter
    • checkpoint_timeout
    • max_wal_size

Compresser les journaux de transactions

  • wal_compression
    • moins de journaux
    • un peu plus de CPU
    • à activer
  • Outils de compression standards : gzip, bzip2, lzma
    • attention à ne pas ralentir l’archivage

Outils de sauvegarde PITR dédiés

  • Se faciliter la vie avec différents outils
    • pgBackRest
    • barman
    • pitrery (< v15, déprécié)
  • Fournissent :
    • un outil pour les backups, les purges…
    • une commande pour l’archivage

pgBackRest

  • Gère la sauvegarde et la restauration
    • pull ou push, multidépôts
    • mono- ou multiserveur
  • Indépendant des commandes système
    • protocole dédié
  • Sauvegardes complètes, différentielles ou incrémentales
  • Multithread, sauvegarde depuis un secondaire, archivage asynchrone…
  • Projet mature

barman

  • Gère la sauvegarde et la restauration
    • mode pull
    • multiserveurs
  • Une seule commande (barman)
  • Et de nombreuses actions
    • list-server, backup, list-backup, recover
  • Spécificité : gestion de pg_receivewal

pitrery

  • Projet en fin de vie, non compatible v15+
  • Gère la sauvegarde et la restauration
    • mode push
    • mono-serveur
  • Multi-commandes
    • archive_wal
    • pitrery
    • restore_wal

Conclusion

  • Une sauvegarde
    • fiable
    • éprouvée
    • rapide
    • continue
  • Mais
    • plus complexe à mettre en place que pg_dump
    • qui restaure toute l’instance

Questions

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

Quiz

Travaux pratiques

pg_basebackup : sauvegarde ponctuelle & restauration

pg_basebackup : sauvegarde ponctuelle & restauration des journaux suivants

Travaux pratiques (solutions)

Supervision

PostgreSQL

Introduction

  • Deux types de supervision
    • occasionnelle
    • automatique
  • Superviser PostgreSQL et le système
  • Pour PostgreSQL, statistiques et traces
  • Politique de supervision
  • Supervision de PostgreSQL
  • Traces : configuration & analyses
  • Statistiques d’activité

Politique de supervision

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

Objectifs de la supervision

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

Acteurs concernés

  • 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

Exemples d’indicateurs - système d’exploitation

  • Charge CPU
  • Entrées/sorties disque
  • Espace disque
  • Sur-activité et non-activité du serveur
  • Temps de réponse
  • Outils Unix habituels :
    • top, atop, free, df, vmstat, sar, iotop

Exemples d’indicateurs - base de données

  • Nombre de connexions
  • Requêtes lentes et/ou fréquentes
  • Ratio d’utilisation du cache
  • Verrous
  • Volumétries

Supervision de PostgreSQL

  • Supervision occasionnelle
    • sur incident…
  • Supervision automatique
    • remonter des informations rapidement
    • archiver, suivre les tendances

Informations internes

  • PostgreSQL propose :
    • statistiques d’activité
    • traces
  • …mais rien pour les historiser

Outils externes

  • Pour conserver les informations
  • …et exécuter automatiquement des actions
    • graphiques (Munin, Zabbix…)
    • envoi d’alertes (Nagios, tail_n_mail)

check_pgactivity

  • Script de monitoring PostgreSQL pour Nagios
    • nombreuses sondes spécifiques à PostgreSQL
    • nombreuses métriques remontées
  • Développé au départ par Dalibo
    • utilisable indépendamment
  • https://github.com/OPMDG/check_pgactivity

check_postgres

Traces

  • Configuration
    • traces peu fournies par défaut
  • Récupération
    • des problèmes importants
    • des requêtes lentes/fréquentes
  • Outils externes de classement

Configuration des traces : principes

  • Où tracer ?
  • Quel niveau de traces ?
  • Tracer les requêtes 
    • durée, fichiers temporaires…
  • Tracer certains comportements
    • erreurs

Événements exceptionnels tracés

  • Crash de PostgreSQL :
PANIC:  could not write to file "pg_wal/xlogtemp.9109":
        No space left on device
  • Rechargement de la configuration :
LOG:  received SIGHUP, reloading configuration files
  • Envoi immédiat d’une alerte
  • Outil : tail_n_mail

Où tracer ?

Gestion des traces

Configuration de la destination des traces

  • log_destination :
    • stderr / csvlog / jsonlog (v15)
    • syslog / eventlog
  • logging_collector : géré par PostgreSQL (Red Hat)
    • log_directory, log_filename, log_file_mode
    • log_rotation_age, log_rotation_size, log_truncate_on_rotation
  • Sinon : si off, penser à logrotate (Debian)
  • syslog (Unix)
    • syslog_facility, syslog_ident
    • syslog_sequence_numbers, syslog_split_messages
  • eventlog (Windows) : event_source

Niveau des traces

  • log_min_messages
    • défaut : panic / fatal / log / error / warning
  • log_min_error_statement
    • défaut : error (ou warning)
  • log_error_verbosity
    • default / terse / verbose

Tracer les requêtes et leur durée

  • Toutes les requêtes :
    • log_min_duration_statement (ex : 1s)
    • ou log_statement + log_duration
  • Extrait aléatoire :
    • log_transaction_sample_rate
    • log_statement_sample_rate + log_min_duration_sample

Configuration : tracer certains comportements

  • log_connections, log_disconnections
  • log_autovacuum_min_duration
  • log_checkpoints
  • log_lock_waits (mini 1s)
  • log_recovery_conflict_waits (v14+)

Repérer les fichiers temporaires

  • Exemple :
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp9894.0",
      size 26927104
  • log_temp_files à activer !
  • Alerte : problème potentiel de performances

Configuration : divers

  • log_line_prefix
    • Conseillé : %t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h
  • lc_messages = C
  • log_timezone = 'Europe/Paris'

Outils d’analyse des traces

  • Beaucoup d’outils existent
    • en temps réel / rétro-analyse
    • généralistes / spécifiques PostgreSQL
  • Exemples :
    • pgBadger
    • logwatch
    • tail_n_mail

pgBadger

  • Site officiel : https://pgbadger.darold.net/
  • 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

pgBadger : exemple de rapport

Capture pgBadger

Utiliser pgBadger

  • Script Perl
  • Traite les journaux applicatifs
  • Recherche des informations
    • sur les requêtes (normalisées) 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é

Configurer PostgreSQL pour pgBadger

  • Minimum :
    • log_destination
    • log_line_prefix
    • lc_messages=C
  • Base :
    • log_connections, log_disconnections
    • log_checkpoints
    • log_lock_waits
    • log_temp_files
    • log_autovacuum_min_duration
  • Pour un audit :
    • log_min_duration_statement = 0 (attention !)

Options de pgBadger

  • Génération :
pgbadger-o rapport.html postgresql-Mon.log postgresql-Tue.log …
  • Très nombreuses options, dont :
    • --outfile
    • --prefix
    • --begin / --end
    • --dbname, --dbuser, --dbclient, --appname
    • --jobs

pgBadger : exemple 1

Exemple pgBadger : statistiques globales

pgBadger : exemple 2

Exemple pgBadger : requêtes

pgBadger : exemple 3

Exemple pgBadger : fichiers temporaires

pgBadger : exemple 4

Exemple pgBadger : journaux de transaction

pgBadger : exemple 5

Exemple pgBadger 5 : requêtes consommatrices en temps

logwatch

  • Outil externe écrit en Perl
  • À exécuter périodiquement
  • Analyse le contenu des journaux applicatifs
  • Envoie un mail s’il détecte certains motifs
  • Exemple :
/usr/sbin/logwatch --detail Med --service postgresql --range All

tail_n_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

Configurer tail_n_mail

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

tail_n_mail : exemple

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"

Statistiques d’activité

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

Statistiques d’activité - configuration 1

  • Tracer l’activité :
    • track_activities = on
  • S’assurer que les requêtes ne sont pas tronquées :
    • track_activity_query_size = 10000 ou +
  • Récupérer l’identifiant de requête
    • compute_query_id = on

Statistiques d’activité - configuration 2

  • track_counts = on
  • track_io_timing = on
  • track_functions = off / pl / all

Statistiques d’activité - configuration 3

  • stats_temp_directory (<v15)
    • répertoire contenant les fichiers temporaires des statistiques
    • copié vers pg_stat lors d’un arrêt propre
    • à monter sur du tmpfs

Statistiques d’activité : perte

  • Les statistiques d’activité sont perdues en cas de crash ou restauration
    • ANALYZE (voire VACUUM)

Informations intéressantes à récupérer

Sur :

  • l’activité
  • l’instance
  • les bases
  • les tables
  • les index
  • les fonctions

Nombre de connexions par base

SELECT datname, numbackends FROM pg_stat_database;
SELECT datname, count(*) FROM pg_stat_activity
  WHERE datname IS NOT NULL
  GROUP BY datname;
Nombre de connexions par base

Taille des bases

SELECT datname, pg_database_size(oid) FROM pg_database;
Taille des bases

Nombre de verrous

SELECT d.datname, count(*) FROM pg_locks l
JOIN pg_database d ON l.database=d.oid
GROUP BY d.datname ORDER BY d.datname;
Nombre de verrous

Et un grand nombre d’autres informations

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

Outils

munin

  • Scripts Perl
  • Sondes PostgreSQL incluses
  • Récupère les statistiques toutes les 5 min
  • Crée des pages HTML statiques et des fichiers PNG
    • donc des graphes

Nagios

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

Outils - Zabbix

Outils - pg_stat_statements

  • 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

Outils - PoWA

PoWA

Conclusion

  • 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

Questions

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

Quiz

Travaux pratiques

Analyse de traces avec pgBadger

But : Analyser un journal de traces avec pgBadger

Travaux pratiques (solutions)

PostgreSQL : Gestion d’un sinistre

PostgreSQL

Introduction

  • Une bonne politique de sauvegardes est cruciale
    • mais elle n’empêche pas les incidents
  • Il faut être prêt à y faire face

Au menu

  • Anticiper les désastres
  • Réagir aux désastres
  • Rechercher l’origine du problème
  • Outils utiles
  • Cas type de désastres

Anticiper les désastres

  • Un désastre peut toujours survenir
  • Il faut savoir le détecter le plus tôt possible
    • et s’être préparé à y répondre

Documentation

  • Documentation complète et à jour
    • emplacement et fréquence des sauvegardes
    • emplacement des traces
    • procédures et scripts d’exploitation
  • Sauvegarder et versionner la documentation

Procédures et scripts

  • Procédures détaillées de restauration / PRA
    • préparer des scripts / utiliser des outils
    • minimiser le nombre d’actions manuelles
  • Tester les procédures régulièrement
    • bases de test, développement…
    • s’assurer que chacun les maîtrise
  • Sauvegarder et versionner les scripts

Supervision et historisation

  • Tout doit être supervisé
    • réseau, matériel, système, logiciels…
    • les niveaux d’alerte doivent être significatifs
  • Les métriques importantes doivent être historisées
    • cela permet de retrouver le moment où le problème est apparu
    • quand cela a un sens, faire des graphes

Automatisation

  • Des outils existent
    • PAF (Pacemaker), patroni, repmgr…
  • Automatiser une bascule est complexe
    • cela peut mener à davantage d’incidents
    • voire à des désastres (split brain)

Réagir aux désastres

  • Savoir identifier un problème majeur
  • Bons réflexes
  • Mauvais réflexes

Symptômes d’un désastre

  • Crash de l’instance
  • Résultats de requêtes erronnés
  • Messages d’erreurs dans les traces
  • Dégradation importante des temps d’exécution
  • Processus manquants
    • ou en court d’exécution depuis trop longtemps

Bons réflexes 1

  • Garder la tête froide
  • Répartir les tâches clairement
  • Minimiser les canaux de communication
  • Garder des notes de chaque action entreprise

Bons réflexes 2

  • Se prémunir contre une aggravation du problème
    • couper les accès applicatifs
  • Si une corruption est suspectée
    • arrêter immédiatement l’instance
    • faire une sauvegarde immédiate des fichiers
    • travailler sur une copie

Bons réflexes 3

  • Déterminer le moment de démarrage du désastre
  • Adopter une vision générale plutôt que focalisée sur un détail
  • Remettre en cause chaque élément de l’architecture
    • aussi stable (et/ou coûteux/complexe) soit-il
  • Éliminer en priorité les causes possibles côté hardware, système
  • Isoler le comportement précis du problème
    • identifier les requêtes / tables / index impliqués

Bons réflexes 4

  • En cas de défaillance matérielle
    • s’assurer de corriger sur du hardware sain et non affecté !
    • baies partagées…

Bons réflexes 5

  • Communiquer, ne pas rester isolé
  • Demander de l’aide si le problème est trop complexe
    • autres équipes
    • support
    • forums
    • listes

Bons réflexes 6

  • Dérouler les procédures comme prévu
  • En cas de situation non prévue, s’arrêter pour faire le point
    • ne pas hésiter à remettre en cause l’analyse
    • ou la procédure elle-même

Bons réflexes 7

  • En cas de bug avéré
    • tenter de le cerner et de le reproduire au mieux
    • le signaler à la communauté de préférence (configuration, comment reproduire)

Bons réflexes 8

  • Après correction
  • Tester complètement l’intégrité des données
    • pour détecter tous les problèmes
  • Validation avec export logique complet
pg_dumpall > /dev/null
  • Ou physique
pg_basebackup
  • Reconstruction dans une autre instance (vérification de cohérence)
pg_dumpall | psql -h autre serveur

Mauvais réflexes 1

  • Paniquer
  • Prendre une décision hâtive
    • exemple, supprimer des fichiers du répertoire pg_wal
  • Lancer une commande sans la comprendre, par exemple :
    • pg_resetwal
    • l’extension pg_surgery
    • DANGER, dernier espoir

Mauvais réflexes 2

  • Arrêter le diagnostic quand les symptômes disparaissent
  • Ne pas pousser l’analyse jusqu’au bout

Mauvais réflexes 3

  • Ne pas documenter
    • le résultat de l’investigation
    • les actions effectuées

Rechercher l’origine du problème

  • Quelques pistes de recherche pour cerner le problème
  • Liste non exhaustive

Prérequis

  • Avant de commencer à creuser
    • référencer les symptômes
    • identifier au mieux l’instant de démarrage du problème

Recherche d’historique

  • Ces symptômes ont-ils déjà été rencontrés dans le passé ?
  • Ces symptômes ont-ils déjà été rencontrés par d’autres ?
  • Attention à ne pas prendre les informations trouvées pour argent comptant !

Matériel

  • Vérifier le système disque (SAN, carte RAID, disques)
  • Un fsync est-il bien honoré de l’OS au disque ? (batteries !)
  • Rechercher toute erreur matérielle
  • Firmwares pas à jour
    • ou récemment mis à jour
  • Matériel récemment changé

Virtualisation

  • Mutualisation excessive
  • Configuration du stockage virtualisé
  • Rechercher les erreurs aussi niveau superviseur
  • Mises à jour non appliquées
    • ou appliquées récemment
  • Modifications de configuration récentes

Système d’exploitation 1

  • Erreurs dans les traces
  • Mises à jour système non appliquées
  • Modifications de configuration récentes

Système d’exploitation 2

  • Opération d’IO impossible
    • FS plein ?
    • FS monté en lecture seule ?
  • Tester l’écriture sur PGDATA
  • Tester la lecture sur PGDATA

Système d’exploitation 3

  • Consommation excessive des ressources
    • OOM killer (overcommit !)
  • Après un crash, vérifier les processus actifs
    • ne pas tenter de redémarrer si des processus persistent
  • Outils : sar, atop

PostgreSQL

  • Relever les erreurs dans les traces
    • ou messages inhabituels
  • Vérifier les mises à jour mineures

Paramétrage de PostgreSQL : écriture des fichiers

  • La désactivation de certains paramètres est dangereuse
    • fsync
    • full_page_write

Paramétrage de PostgreSQL : les sommes de contrôle

  • Activez les checksums !
    • initdb --data-checksums
    • pg_checksums --enable (à posteriori, v12)
  • Détecte les corruptions silencieuses
  • Impact faible sur les performances
  • Vérification lors de pg_basebackup (v11)

Erreur de manipulation

  • Traces système, traces PostgreSQL
  • Revue des dernières manipulations effectuées
  • Historique des commandes
  • Danger : kill -9, rm -rf, rsync, find … -exec

Outils

  • Quelques outils peuvent aider
    • à diagnostiquer la nature du problème
    • à valider la correction apportée
    • à appliquer un contournement
  • ATTENTION
    • certains de ces outils peuvent corrompre les données !

Outils - pg_controldata

  • Fournit des informations de contrôle sur l’instance
  • Ne nécessite pas que l’instance soit démarrée

Outils - export/import de données

  • pg_dump
  • pg_dumpall
  • COPY
  • psql / pg_restore
    • --section=pre-data / data / post-data

Outils - pageinspect

  • Extension
  • Vision du contenu d’un bloc
  • Sans le dictionnaire, donc sans décodage des données
  • Affichage brut
  • Utilisé surtout en debug, ou dans les cas de corruption
  • Fonctions de décodage pour les tables, les index (B-tree, hash, GIN, GiST), FSM
  • Nécessite de connaître le code de PostgreSQL

Outils - pg_resetwal

  • Efface les WAL courants
  • Permet à l’instance de démarrer en cas de corruption d’un WAL
    • comme si elle était dans un état cohérent
    • …ce qui n’est pas le cas
  • Cet outil est dangereux et mène à des corruptions !!!
  • Pour récupérer ce qu’on peut, et réimporter ailleurs

Outils - Extension pg_surgery

  • Extension apparue en v14
  • Collection de fonctions permettant de modifier le statut des tuples d’une relation
  • Extrêmement dangereuse

Outils - Vérification d’intégrité

  • À froid : pg_checksums (à froid, v11)
  • Lors d’une sauvegarde : pg_basebackup (v11)
  • amcheck : pure vérification
    • v10 : 2 fonctions pour l’intégrité des index
    • v11 : vérification de la cohérence avec la table (probabiliste)
    • v14 : ajout d’un outil pg_amcheck

Cas type de désastres

  • Les cas suivants sont assez rares
  • Ils nécessitent généralement une restauration
  • Certaines manipulations à haut risque sont possibles
    • mais complètement déconseillées !

Avertissement

  • Privilégier une solution fiable (restauration, bascule)
  • Les actions listées ici sont parfois destructrices
  • La plupart peuvent (et vont) provoquer des incohérences
  • Travailler sur une copie

Corruption de blocs dans des index

  • Messages d’erreur lors des accès par l’index ; requêtes incohérentes
  • Données différentes entre un indexscan et un seqscan
  • Supprimer et recréer l’index : REINDEX

Corruption de blocs dans des tables 1

ERROR: invalid page header in block 32570 of relation base/16390/2663
ERROR: could not read block 32570 of relation base/16390/2663:
       read only 0 of 8192 bytes
  • Cas plus problématique
  • Restauration probablement nécessaire

Corruption de blocs dans des tables 2

SET zero_damaged_pages = true ;
VACUUM FULL tablecorrompue ;
  • Des données vont certainement être perdues !

Corruption de blocs dans des tables 3

  • Si la corruption est importante, l’accès au bloc peut faire crasher l’instance
  • Il est tout de même possible de réinitialiser le bloc
    • identifier le fichier à l’aide de pg_relation_filepath()
    • trouver le bloc avec ctid / pageinspect
    • réinitialiser le bloc avec dd
    • il faut vraiment ne pas avoir d’autre choix

Corruption des WAL 1

  • Situés dans le répertoire pg_wal
  • Les WAL sont nécessaires au recovery
  • Démarrage impossible s’ils sont corrompus ou manquants
  • Si les fichiers WAL ont été archivés, les récupérer
  • Sinon, la restauration est la seule solution viable

Corruption des WAL 2

  • pg_resetwal permet de forcer le démarrage
  • ATTENTION !!!
    • cela va provoquer des pertes de données
    • des corruptions de données sont également probables
    • ce n’est pas une action corrective !

Corruption du fichier de contrôle

  • Fichier global/pg_control
  • Contient les informations liées au dernier checkpoint
  • Sans lui, l’instance ne peut pas démarrer
  • Recréation avec pg_resetwal… parfois
  • Restauration nécessaire

Corruption du CLOG

  • Fichiers dans pg_xact
  • Statut des différentes transactions
  • Son altération risque de causer des incohérences

Corruption du catalogue système

  • Le catalogue contient la définition du schéma
  • Sans lui, les données sont inaccessibles
  • Situation très délicate…

Conclusion

  • Les désastres peuvent arriver
  • Il faut s’y être préparé
  • Faites des sauvegardes !
    • et testez-les

Quiz

Travaux pratiques

Corruption d’un bloc de données

Corruption d’un bloc de données et incohérences

Travaux pratiques (solutions)


  1. La trace se retrouve encore dans le nom de la librairie C pour les clients, la libpq.↩︎