Cet atelier a pour but de détailler l’industrialisation d’instances PostgreSQL avec pglift.

pglift est un outil qui permet de déployer et de gérer des instances PostgreSQL uniformisées. Les instances peuvent être prêtes pour la production dès leur déploiement, c’est-à-dire qu’elles sont installées avec une sauvegarde configurée et un endpoint de supervision accessible.

Pour les besoins de haute disponibilité, pglift est capable de déployer des instances en réplication avec patroni.

Pour effectuer des sauvegardes physiques, pglift prend en charge pgBackRest en mode local ou distant.

Par défaut, pglift se contente de déployer et de gérer PostgreSQL, les composants pris en charge sont optionnels, à activer dans sa configuration.

pglift expose l’ensemble de ses fonctionnalités dans une interface en ligne de commande.

[postgres@srv-pg1 ~]$ pglift

Usage: pglift [OPTIONS] COMMAND [ARGS]...

  Deploy production-ready instances of PostgreSQL

Options:
  -L, --log-level [DEBUG|INFO|WARNING|ERROR|CRITICAL]
                                  Set log threshold (default to INFO when
                                  logging to stderr or WARNING when logging to
                                  a file).
  -l, --log-file LOGFILE          Write logs to LOGFILE, instead of stderr.
  --interactive / --non-interactive
                                  Interactively prompt for confirmation when
                                  needed (the default), or automatically pick
                                  the default option for all choices.
  --version                       Show program version.
  --completion [bash|fish|zsh]    Output completion for specified shell and
                                  exit.
  --help                          Show this message and exit.

Commands:
  instance           Manage instances.
  pgconf             Manage configuration of a PostgreSQL instance.
  role               Manage roles.
  database           Manage databases.
  patroni            Handle Patroni service for an instance.
  postgres_exporter  Handle Prometheus postgres_exporter

La commande permet de créer et de gérer des instances PostgreSQL et les services associés. Elle permet également de créer des bases de données et des rôles dans une instance existante.

L’aide de chaque commande citées ci-dessus peut être affichée. Par exemple, pour l’aide de la commande instance :

[postgres@srv-pg1 ~]$ pglift instance --help

Usage: pglift instance [OPTIONS] COMMAND [ARGS]...

  Manage instances.

Options:
  --schema  Print the JSON schema of instance model and exit.
  --help    Show this message and exit.

Commands:
  alter       Alter PostgreSQL INSTANCE
  backups     List available backups for INSTANCE
  create      Initialize a PostgreSQL instance
  drop        Drop PostgreSQL INSTANCE
  env         Output environment variables suitable to handle to...
  exec        Execute command in the libpq environment for PostgreSQL...
  get         Get the description of PostgreSQL INSTANCE.
  list        List the available instances
  logs        Output PostgreSQL logs of INSTANCE.
  privileges  List privileges on INSTANCE's databases.
  promote     Promote standby PostgreSQL INSTANCE
  reload      Reload PostgreSQL INSTANCE
  restart     Restart PostgreSQL INSTANCE
  restore     Restore PostgreSQL INSTANCE
  shell       Start a shell with instance environment.
  start       Start PostgreSQL INSTANCE
  status      Check the status of instance and all satellite components.
  stop        Stop PostgreSQL INSTANCE
  upgrade     Upgrade INSTANCE using pg_upgrade

Il en va de même pour les sous-commandes, par exemple, pour l’aide de pglift instance alter :

[postgres@srv-pg1 ~]$ pglift instance alter --help
Usage: pglift instance alter [OPTIONS] [INSTANCE]

  Alter PostgreSQL INSTANCE

  INSTANCE identifies target instance as <version>/<name> where the <version>/
  prefix may be omitted if there is only one instance matching <name>.
  Required if there is more than one instance on system.

Options:
  --port PORT                     TCP port the postgresql instance will be
                                  listening to. If unspecified, default to
                                  5432 unless a 'port' setting is found in
                                  'settings'.
  --data-checksums / --no-data-checksums
                                  Enable or disable data checksums. If
                                  unspecified, fall back to site settings
                                  choice.
  --state [started|stopped]       Runtime state.
  --powa-password TEXT            Password of PostgreSQL role for PoWA.
  --prometheus-port PORT          TCP port for the web interface and telemetry
                                  of Prometheus.
  --prometheus-password TEXT      Password of PostgreSQL role for Prometheus
                                  postgres_exporter.
  --patroni-restapi-connect-address CONNECT_ADDRESS
                                  IP address (or hostname) and port, to access
                                  the Patroni's REST API.
  --patroni-restapi-listen LISTEN
                                  IP address (or hostname) and port that
                                  Patroni will listen to for the REST API.
                                  Defaults to connect_address if not provided.
  --patroni-postgresql-connect-host CONNECT_HOST
                                  Host or IP address through which PostgreSQL
                                  is externally accessible.
  --patroni-postgresql-replication-ssl-cert CERT
                                  Client certificate.
  --patroni-postgresql-replication-ssl-key KEY
                                  Private key.
  --patroni-postgresql-replication-ssl-password TEXT
                                  Password for the private key.
  --patroni-postgresql-rewind-ssl-cert CERT
                                  Client certificate.
  --patroni-postgresql-rewind-ssl-key KEY
                                  Private key.
  --patroni-postgresql-rewind-ssl-password TEXT
                                  Password for the private key.
  --patroni-etcd-username USERNAME
                                  Username for basic authentication to etcd.
  --patroni-etcd-password TEXT    Password for basic authentication to etcd.
  --help                          Show this message and exit.

Les fonctionnalités de pglift sont également accessibles depuis la collection dalibo.pglift. Celle-ci fournit les modules ansible permettant d’intégrer les opérations de pglift dans un processus de déploiement automatisé déclaratif à l’aide d’ansible (infrastructure as code).

Des collections Ansible publiques sont également développées par Dalibo. Elles permettent une installation et un déploiement automatisés de l’ensemble des composants nécessaires au fonctionnement de pglift.

Ces collections sont un regroupement de fonctionnalités par thème :

• dalibo.essential : Pour PostgreSQL, la réplication, le monitoring et la sauvergarde
• dalibo.advanced : pour la HA, les performances, les audits, ldap, et les pooler de connexions
• dalibo.extras : Regroupe tous les outils annexes comme ETCD, rsyslog, logrotate, etc.

Les machines suivantes sont utilisées pour cet atelier :

L’ensemble des tâches seront effectuées sur serveur srv-pg1.

Activer le dépôt additionnel PowerTools :

[root@srv-pg1 ~]# dnf config-manager -y --set-enabled powertools

Installer le dépôt EPEL :

[root@srv-pg1 ~]# dnf install -y epel-release

Installer le dépôt PGDG de la communauté PostgreSQL :

[root@srv-pg1 ~]# dnf install -y \
https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/\
pgdg-redhat-repo-latest.noarch.rpm

Désactiver le module dnf postgresql afin de pouvoir installer les paquets PostgreSQL de la communauté :

[root@srv-pg1 ~]# dnf -y module disable postgresql

Installer le dépôt Dalibo Labs :

[root@srv-pg1 ~]# dnf -y install \
https://yum.dalibo.org/labs/dalibo-labs-4-1.noarch.rpm

Installer python 3.9 sur les serveurs PostgreSQL :

[root@srv-pg1 ~]# dnf -y install python39

Créer l’utilisateur postgres

[root@srv-pg1 ~]# useradd -U -d /home/postgres -s /bin/bash postgres

Créer le répertoire pour l’arborescence des données :

[root@srv-pg1 ~]# mkdir /pgdata && chown postgres:postgres /pgdata

Créer les répertoires de configuration de pglift dans ~/.config en tant que postgres :

[root@srv-pg1 ~]# su - postgres << EOF
mkdir -p ~/.config/pglift/postgresql
mkdir -p ~/.config/pglift/pgbackrest
EOF

Configurer le lingering sur l’utilisateur postgres, ce qui lui permettra de faire fonctionner des services système, au même titre que root :

[root@srv-pg1 ~]# loginctl enable-linger postgres

Le fichier de configuration principal de pglift déclare le fonctionnement de ses opérations. C’est un fichier au format YAML.

Une première version assez basique de cette configuration est à déposer dans le fichier ~/.config/pglift/settings.yaml

---
systemd: {}
postgresql:
  default_version: '16'
  auth:
    local: 'peer'
    host: 'scram-sha-256'
  surole:
    name: 'postgres'
  backuprole:
    name: 'backup'
  datadir: '/pgdata/{version}/{name}/data'
  logpath: '/pgdata/log/postgresql'
  dumps_directory: '/pgdata/backup/dumps/{version}-{name}'
  socket_directory: '/var/run/postgresql/'
  replrole: replication
pgbackrest:
  repository:
    path: '/pgdata/backup/pgbackrest'
    mode: 'path'

pglift substitue des variables à partir des caractéristiques de l’instance à déployer. Ainsi, les variables {version} et {name}, qui sont obligatoires pour l’option datadir, seront remplacées par la version PostgreSQL de l’instance et le nom qui est renseigné à sa création.

En plus de ce fichier, pglift supporte l’utilisation de templates de fichiers de configuration. Ces derniers peuvent être utilisés pour modifier globalement les paramètres associés aux instances qui seront ensuite déployées sur le nœud local.

Les templates suivants sont à déposer sur les nœuds PostgreSQL :

• ~/.config/pglift/postgresql/postgresql.conf : Configuration de l’instance PostgreSQL

listen_addresses = '*'
port = 5432
shared_buffers = 25%
effective_cache_size = 66%
random_page_cost = 1.5
wal_buffers = '8MB'
checkpoint_completion_target = 0.9
timezone = 'Europe/Paris'
cluster_name = {name}
unix_socket_directories = {settings.socket_directory}
logging_collector = true
log_directory = {settings.logpath}
shared_preload_libraries = 'pg_stat_statements'

• ~/.config/pglift/postgresql/pg_hba.conf : Configuration de l’authentification PostgreSQL

local   all             {surole}                        {auth.local}
local   all             {backuprole}                    {auth.local} map=backupmap
local   temboard        temboard                        {auth.local}
local   all             temboardagent                   {auth.local} map=temboardmap
local   all             bob                             scram-sha-256
host    replication     {replrole}      127.0.0.1/32    {auth.host}
host    all             all                0.0.0.0/0    {auth.host}

• ~/.config/pglift/postgresql/pg_ident.conf : Mapping des utilisateurs système avec des rôles PostgreSQL

# MAPNAME       SYSTEM-USERNAME PG-USERNAME
backupmap       {sysuser}       {backuprole}
temboardmap     {sysuser}       temboardagent

• ~/.config/pglift/pgbackrest/pgbackrest.conf : Configuration globale de pgBackRest

[global]
lock-path = {lockpath}
log-path = {logpath}
log-level-console = info

pglift substitue certaines variables dans les templates à partir de son paramétrage, ou bien des spécifications du système. Par exemple :

• shared_buffers = 25% : la valeur du paramètre sera transformée en 25% de la quantité totale de mémoire sur le serveur lors du déploiement.
• {surole} sera remplacé par le nom du super-utilisateur choisi (postgres par défaut)
• {auth.host} correspondra à la valeur postgresql.auth.host de la configuration de pglift.

Avant de pouvoir créer des instances, il faut préparer le système à accueillir des instances pglift selon la configuration actuelle. Pour ce faire, exécuter la commande pglift site-configure install

[postgres@srv-pg1 ~]$ pglift site-configure install
INFO     installed pglift-backup@.service systemd unit at 
         /home/postgres/.local/share/systemd/user/pglift-backup@.service
INFO     installed pglift-backup@.timer systemd unit at 
         /home/postgres/.local/share/systemd/user/pglift-backup@.timer
INFO     installed pglift-postgresql@.service systemd unit at
         /home/postgres/.local/share/systemd/user/pglift-postgresql@.service
INFO     creating base pgBackRest configuration directory: 
         /home/postgres/.local/share/pglift/etc/pgbackrest
INFO     installing base pgBackRest configuration
INFO     creating pgBackRest include directory
INFO     creating pgBackRest repository backups and archive directory:
         /pgdata/backup/pgbackrest
INFO     creating pgBackRest log directory: 
         /home/postgres/.local/share/pglift/log/pgbackrest
INFO     creating pgBackRest spool directory: 
         /home/postgres/.local/share/pglift/srv/pgbackrest/spool
INFO     creating PostgreSQL log directory: /pgdata/log/postgresql

Déployer une instance à l’aide de la ligne de commande pglift :

[postgres@srv-pg1 ~]$ pglift instance create main --pgbackrest-stanza=main
INFO     initializing PostgreSQL
INFO     configuring PostgreSQL authentication
INFO     configuring PostgreSQL
INFO     starting PostgreSQL 16/main
INFO     creating role 'backup'
INFO     configuring pgBackRest stanza 'main' for pg1-path=/pgdata/16/main/data
INFO     creating pgBackRest stanza main
INFO     checking pgBackRest configuration for stanza
INFO     creating instance dumps directory: /pgdata/backup/dumps/16-main

La commande pglift instance list permet de lister les instances :

[postgres@srv-pg1 ~]$ pglift instance list
+--------------------------------------------------------+
| name | version | port | datadir              | status  |
+------+---------+------+----------------------+---------+
| main | 16      | 5432 | /pgdata/16/main/data | running |
+------+---------+------+----------------------+---------+

Pour faciliter la connexion à l’instance, ou même l’administration des composants satellites qui lui sont associés, il est très utile de charger les variables d’environnement de l’instance. La commande pglift instance env main permet de les afficher :

[postgres@srv-pg1 ~]$ pglift instance env main
PATH=/usr/pgsql-16/bin:/home/postgres/.local/bin:/home/postgres/bin:
>/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/sbin:/opt/pglift/bin:/usr/pgsql-16/bin
PGBACKREST_CONFIG_PATH=/home/postgres/.local/share/pglift/etc/pgbackrest
PGBACKREST_STANZA=main
PGDATA=/pgdata/16/main/data
PGHOST=/var/run/postgresql
PGPASSFILE=/home/postgres/.pgpass
PGPORT=5432
PGUSER=postgres
PSQLRC=/pgdata/16/main/data/.psqlrc
PSQL_HISTORY=/pgdata/16/main/data/.psql_history

Exporter le résultat de cette commande permet de charger les variables d’environnement de l’instance main sur la session courante.

[postgres@srv-pg1 ~]$ export $(pglift instance env main)

Il est également possible de démarrer un nouveau shell avec les variables d’environnement de l’instance exportées :

[postgres@srv-pg1 ~]$ pglift instance shell main

L’instance est alors accessible via psql sans option :

[postgres@srv-pg1 ~]$ psql
psql (16.4)
Type "help" for help.

[16/main] postgres@~=#

Sur le poste faisant office de nœud de contrôle Ansible (srv-helper1), créer un répertoire pour les ressources Ansible dans le $HOME de l’utilisateur courant.

Déposer ensuite dans ce répertoire, un inventaire Ansible ~/ansible/inventory comprenant les serveurs de l’atelier.

Installer les collections requises pour déployer et manipuler pglift à travers Ansible, avec ansible-galaxy :

Exécuter le playbook temboard.yml sur l’inventaire établi afin de déployer l’interface temBoard:

[dalibo@srv-helper1 ~/ansible]$ ansible-playbook -i inventory temboard.yml

Installer l’ensemble des éléments nécessaires au fonctionnement de pglift, de PostgreSQL et des outils satellites avec le playbook postgresql.yml

L’instance peut être déployée par Ansible, via le module dalibo.pglift.instance. Pour cela, les serveurs doivent être accessibles depuis le poste local sur un compte utilisateur sudoer. Le compte utilisateur dalibo est utilisé à cet effet sur les machines de l’atelier.

Exécuter le playbook instance_standalone.yml sur l’inventaire établi :

[dalibo@srv-helper1 ~/ansible]$ ansible-playbook -i inventory \ 
instance_standalone.yml

Exécuter le playbook standby_instance.yml sur l’inventaire établi :

[dalibo@srv-helper1 ~/ansible]$ ansible-playbook -i inventory standby_instance.yml

PITR est l’acronyme de Point In Time Recovery, autrement dit restauration à un point dans le temps.

C’est une sauvegarde à chaud et surtout en continu. Là où une sauvegarde logique du type pg_dump se fait au mieux une fois toutes les 24 h, la sauvegarde PITR se fait en continu grâce à l’archivage des journaux de transactions. De ce fait, ce type de sauvegarde diminue très fortement la fenêtre de perte de données.

Bien que la sauvegarde se fasse à chaud, le rejeu des journaux de transactions après sa restauration permet de retrouver un état cohérent.

Initialiser une base de données ws1:

[postgres@srv-pg1 ~]$ pglift database -i main create ws1
INFO     creating 'ws1' database in 16/main 

Effectuer une première sauvegarde de l’instance :

[postgres@srv-pg1 ~]$ pglift instance backup main
INFO     backing up instance 16/main with pgBackRest

Lister les sauvegardes disponibles :

[postgres@srv-pg1 ~]$ pglift instance backups main
                        Available backups for instance 16/main
+------------------+---------+-----------+---------------------------+---------------------------+------+---------------+
| label            | size    | repo_size | date_start                | date_stop                 | type | databases     |
+------------------+---------+-----------+---------------------------+---------------------------+------+---------------+
│ 20240118-084757F │ 30.6 MB │ 4.1 MB    │ 2024-01-18 08:47:57+00:00 │ 2024-01-18 08:48:03+00:00 │ full │ postgres, ws1 │
+------------------+---------+-----------+---------------------------+---------------------------+------+---------------+

Peupler la base de données ws1 à l’aide de pgbench :

[postgres@srv-pg1 ~]$ /usr/pgsql-16/bin/pgbench -i ws1
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.07 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.18 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 0.10 s, vacuum 0.02 s, primary keys 0.05 s).

Récupérer la date et l’heure à partir de PostgreSQL, elle sera utile pour la restauration :

[postgres@srv-pg1 ~]$ psql -Atc "select current_timestamp"
2024-01-18 08:48:43.91572+01

Effectuer une nouvelle sauvegarde de l’instance :

[postgres@srv-pg1 ~]$ pglift instance backup main
INFO     backing up instance 16/main with pgBackRest

Lister à nouveau les sauvegardes disponibles :

[postgres@srv-pg1 ~]$ pglift instance backups main
                        Available backups for instance 16/main
+-----------------------------------+---------+-----------+---------------------------+---------------------------+------+---------------+
| label                             | size    | repo_size | date_start                | date_stop                 | type | databases     |
+-----------------------------------+---------+-----------+---------------------------+---------------------------+------+---------------+
│ 20240118-084757F_20240118-085536I │ 46.5 MB │ 4.9 MB    │ 2024-01-18 08:55:36+00:00 │ 2024-01-18 08:55:38+00:00 │ incr │ postgres, ws1 │
│ 20240118-084757F                  │ 30.6 MB │ 4.1 MB    │ 2024-01-18 08:47:57+00:00 │ 2024-01-18 08:48:03+00:00 │ full │ postgres, ws1 │
+-----------------------------------+---------+-----------+---------------------------+---------------------------+------+---------------+

Comme on peut le voir dans la liste de sauvegardes, la seconde est de type incrémentielle.

Supprimer la base de données ws1:

[postgres@srv-pg1 ~]$ pglift database -i main drop ws1
INFO     dropping 'ws1' database

Lister les bases de données :

[postgres@srv-pg1 ~]$ pglift database list main
+-----------+----------+--------+-----+-------+-----------------------+--------+---------------------------+------------------+
| name      | owner    | encod. | col.| ctype | acls                  | size   | description               | tablespace       |
+-----------+----------+--------+-----+-------+-----------------------+--------+---------------------------+------------------+
│ postgres  │ postgres │ UTF8   │ C   │ C     │                       │ 7.6 MB │ default administrative    │ name: pg_default │
│           │          │        │     │       │                       │        │ connection database       │ location:        │
│           │          │        │     │       │                       │        │                           │ size: 37.9 MB    │
│ template1 │ postgres │ UTF8   │ C   │ C     │ =c/postgres,          │ 7.6 MB │ default template for new  │ name: pg_default │
│           │          │        │     │       │ postgres=CTc/postgres │        │ databases                 │ location:        │
│           │          │        │     │       │                       │        │                           │ size: 37.9 MB    │
+-----------+----------+--------+-----+-------+-----------------------+--------+---------------------------+------------------+

La base de données ws1 a bien été supprimée.

Pour restaurer une instance, il est nécessaire de l’arrêter :

[postgres@srv-pg1 ~]$ pglift instance stop main
INFO     stopping PostgreSQL 16-main

Restaurer la première sauvegarde à l’aide de la date et heure récupérées après l’exécution de pgbench :

[postgres@srv-pg1 ~]$ pglift instance restore main --date '2024-01-18 08:48:43'
INFO     restoring instance 16/main with pgBackRest

Démarrer l’instance et lister les tables de la base ws1:

[postgres@srv-pg1 ~]$ pglift instance start main
INFO     starting PostgreSQL 16-main  
[postgres@srv-pg1 ~]$ psql ws1
psql (16.6)
Type "help" for help.

ws1=# \dt
              List of relations
 Schema |       Name       | Type  |  Owner   
--------+------------------+-------+----------
 public | pgbench_accounts | table | postgres
 public | pgbench_branches | table | postgres
 public | pgbench_history  | table | postgres
 public | pgbench_tellers  | table | postgres
(4 rows)

L’instance a bien été restaurée avec la base de données ws1 après l’import de données par pgbench.

Il est également possible de restaurer l’instance à l’aide du label.

Arrêter et restaurer l’instance à l’aide de la première sauvegarde:

[postgres@srv-pg1 ~]$ pglift instance stop main
INFO     stopping PostgreSQL 16-main
[postgres@srv-pg1 ~]$ pglift instance restore main --label 20240118-084757F
INFO     restoring instance 16/main with pgBackRest 

Démarrer l’instance et lister les tables de la base ws1:

[postgres@srv-pg1 ~]$ pglift instance start main
INFO     starting PostgreSQL 16-main 

[postgres@srv-pg1 ~]$ psql ws1
psql (16.6)
Type "help" for help.

ws1=# \dt
Did not find any relations

Les tables créées par pgbench ne sont pas présente dans cette sauvegarde restaurée.

La commande pglift instance list permet de lister l’ensemble des instances managées par pglift sur le serveur.

[postgres@srv-pg1 ~]$ pglift instance list
┏━━━━━━━━┳━━━━━━━━━┳━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┓
┃ name   ┃ version ┃ port ┃ datadir              ┃ status      ┃
┡━━━━━━━━╇━━━━━━━━━╇━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━┩
│ main   │ 16      │ 5432 │ /pgdata/16/main/data │ running     │
└────────┴─────────┴──────┴──────────────────────┴─────────────┘

La commande pglift instance get permet d’obtenir la description d’une instance. On peut notamment y voir les locales, l’encodage, si un redémarrage est nécessaire pour prendre en compte une modification de configuration ou encore la présence de slots de réplication.

[postgres@srv-pg1 ~]$ pglift instance get main
name    version  port  data_checksums  locale  encoding  pending_restart  replication_slots 
main    16       5432  False           C       UTF8      False

La commande pglift instance status permet d’obtenir le statut d’une instance ainsi que des composants satellites qui sont utilisés par le socle (Temboard, postgres_exporter, patroni, etc) lorsque ceux-ci sont activés dans la configuration.

[postgres@srv-pg1 ~]$ pglift instance status main
PostgreSQL: not running

La commande pglift instance start permet de démarrer l’instance via le service.

[postgres@srv-pg1 ~]$ pglift instance start main
INFO     starting PostgreSQL cluster 16/main

La commande pglift instance stop permet de stopper l’instance via le service.

[postgres@srv-pg1 ~]$ pglift instance stop main
INFO     stopping PostgreSQL cluster 16/main

Les deux actions peuvent être combinées pour effectuer un redémarrage avec la commande pglift instance restart.

[postgres@srv-pg1 ~]$ pglift instance restart main
INFO     restarting instance 16/main

La commande pglift instance reload permet de recharger la configuration de l’instance.

[postgres@srv-pg1 ~]$ pglift instance reload main
INFO     reloading PostgreSQL configuration for 16/main

Pour consulter les traces d’une instance, qui se trouvent dans le répertoire /pgdata/log/postgresql, il est possible de passer par la commande pglift plutôt que d’ouvrir manuellement le fichier.

[postgres@srv-pg1 ~]$ pglift instance logs main

La configuration PostgreSQL des instances créées par pglift est construite à partir de plusieurs sources :

• le fichier postgresql.conf présent dans le répertoire de données de l’instance.
• le fichier template postgresql.conf de la configuration de site.
• Tous les éléments de configuration que les composants satellites pourraient définir (par exemple, pgBackRest définirait archive_command, etc).
• Les éléments de configuration fournis par l’utilisateur.

Ce processus s’applique lors de la création de l’instance, mais aussi à chaque fois que l’instance est mise à jour via pglift.

pglift peut manipuler la configuration des instances PostgreSQL avec la commande pglift pgconf.

Les options sont :

[postgres@srv-pg1 ~]$ pglift pgconf --help
Usage: pglift pgconf [OPTIONS] COMMAND [ARGS]...

  Manage configuration of a PostgreSQL instance.

Options:
  -i, --instance <version>/<name>
                                  Instance identifier; the <version>/ prefix
                                  may be omitted if there's only one instance
                                  matching <name>. Required if there is more
                                  than one instance on system.
  --help                          Show this message and exit.

Commands:
  edit    Edit managed configuration.
  remove  Remove configuration items.
  set     Set configuration items.
  show    Show configuration (all parameters or specified ones).

La commande pglift pgconf edit permet de modifier directement le fichier de configuration en ouvrant un éditeur de texte avec le contenu du postgresql.conf.

Pour modifier spécifiquement certains paramètres, il est également possible de passer par la commande pglift pgconf set. Il est possible de lui passer un ou plusieurs paramètres sous la forme paramètre=valeur.

[postgres@srv-pg1 ~]$ pglift pgconf -i main set max_connections=200 \ 
log_connections=on

INFO     configuring PostgreSQL
INFO     instance 16/main needs reload due to parameter changes: log_connections
INFO     reloading PostgreSQL configuration for 16/main
WARNING  instance 16/main needs restart due to parameter changes: max_connections
> PostgreSQL needs to be restarted; restart now? [y/n] (n): y
log_connections: off -> on
max_connections: 100 -> 200 

La commande pglift pgconf show permet d’afficher la configuration de l’instance.

[postgres@srv-pg1 ~]$ pglift pgconf -i main show max_connections
max_connections = 200

La suppression de paramètre est réalisée avec la commande pglift pgconf remove.

[postgres@srv-pg1 ~]$ pglift pgconf -i main remove max_connections
INFO     configuring PostgreSQL
WARNING  instance 16/main needs restart due to parameter changes: max_connections
> PostgreSQL needs to be restarted; restart now? [y/n] (n): y
max_connections: 300 -> None

La commande pglift pghba permet de gérer les entrées du fichier pg_hba.conf d’une instance PostgreSQL.

Pour ajouter une entrée :

[postgres@srv-pg1 ~]$ pglift pghba add --conntype host --database all \
--user all --address 192.168.10.100/32 --method scram-sha-256
INFO     entry added to pg_hba.conf

Pour supprimer une entrée :

$ pglift pghba remove --conntype host --database all --user all \
--address 192.168.10.100/32 --method scram-sha-256
INFO     entry removed from pg_hba.conf

Les opérations de maintenance sur les données sont réalisées grâce à la commande pglift database run. Celle‑ci permet d’exécuter une commande SQL sur une base de données spécifiée. L’instance cible doit également être précisée.

La manipulation des rôles avec pglift est faite avec la commande pglift role.

Voici les options :

[postgres@srv-pg1 ~]$ pglift role --help
Usage: pglift role [OPTIONS] COMMAND [ARGS]...

  Manage roles.

Options:
  -i, --instance <version>/<name>
                                  Instance identifier; the <version>/ prefix
                                  may be omitted if there's only one instance
                                  matching <name>. Required if there is more
                                  than one instance on system.
  --schema                        Print the JSON schema of role model and
                                  exit.
  --help                          Show this message and exit.

Commands:
  alter        Alter a role in a PostgreSQL instance
  create       Create a role in a PostgreSQL instance
  drop         Drop a role
  get          Get the description of a role
  list         List roles in instance
  privileges   List privileges of a role.
  set-profile  Set profile (read-only, read-write) for a specific role...

Pour créer une rôle :

[postgres@srv-pg1 ~]$ pglift role create bob --login --replication --password

Pour modifier un rôle :

[postgres@srv-pg1 ~]$ pglift role alter bob --superuser

Pour supprimer un rôle :

[postgres@srv-pg1 ~]$ pglift role drop bob

Pour lister les rôles :

[postgres@srv-pg1 ~]$ pglift role list

Il est également possible de créer, modifier et supprimer un rôle avec le module dalibo.pglift.role de la collection Ansible dalibo.pglift.

Voici un exemple :

tasks:
  - name: my role
    dalibo.pglift.role:
      instance: main
      name: dba
      pgpass: true
      login: true
      connection_limit: 10
      valid_until: '2025-01-01T00:00'
      memberships:
        - role: pg_read_all_stats

Cette tâche va créer un rôle dba sur l’instance main. Le mot de passe de ce rôle sera stocké dans le pgpass et il aura l’arribut LOGIN. Une limite de connexion et une date de validité pour le mot de passe sont également définies. Ce rôle appartiendra au groupe pg_read_all_stats.

La manipulation des bases de données avec pglift est faite avec la commande pglift database.

Voici les options :

[postgres@srv-pg1 ~]$ pglift database --help
Usage: pglift database [OPTIONS] COMMAND [ARGS]...

  Manage databases.

Options:
  -i, --instance <version>/<name>
                                  Instance identifier; the <version>/ prefix
                                  may be omitted if there's only one instance
                                  matching <name>. Required if there is more
                                  than one instance on system.
  --schema                        Print the JSON schema of database model and
                                  exit.
  --help                          Show this message and exit.

Commands:
  alter       Alter a database in a PostgreSQL instance
  create      Create a database in a PostgreSQL instance
  drop        Drop a database
  dump        Dump a database
  dumps       List the database dumps
  get         Get the description of a database
  list        List databases (all or specified ones)
  privileges  List privileges on a database.
  restore     Restore a database dump
  run         Run given command on databases of a PostgreSQL instance

Pour créer une base de données :

[postgres@srv-pg1 ~]$ pglift database create db01 --schema s1 --owner bob

Pour modifier une base de données :

[postgres@srv-pg1 ~]$ pglift database alter db01 --owner alice

Pour supprimer une base de données :

[postgres@srv-pg1 ~]$ pglift database drop db01

Pour lister les base de données :

[postgres@srv-pg1 ~]$ pglift database list

Il est également possible de créer, modifier et supprimer une base de données avec le module dalibo.pglift.database de la collection Ansible dalibo.pglift.

Voici un exemple :

tasks:
- name: my database
  dalibo.pglift.database:
    instance: main
    name: myapp
    owner: dba

Cette tâche va créer une base de données myapp sur l’instance main et le propriétaire de cette base de données sera dba.

La création d’une instance répliquée est réalisée avec la commande pglift instance create et l’option --stanby-for.

Avant de créer l’instance secondaire, il est nécessaire de s’assurer que l’instance primaire autorise les connexions de réplication depuis le serveur hébergeant l’instance secondaire.

Pour créer l’instance secondaire, exécuter la commande suivante :

[postgres@srv-pg1 ~]$ pglift instance create standby \
--standby-for 'host=X.X.X.X user=replication port=5432' --standby-password Passw0rd

Il est également possible de passer par le module dalibo.pglift.instance de la collection Ansible dalibo.pglift.

tasks:
  - name: Creating standby Instance
    dalibo.pglift.instance:
      name: standby
      state: started
      version: 16
      port: 5433
      surole_password: Passw0rd
      replrole_password: Passw0rd
      pgbackrest:
        password: Passw0rd
        stanza: main-stz
      standby:
        primary_conninfo: "host=X.X.X.X user=replication port=5432"
        password: Passw0rd

Les paramètres standby.primary_conninfo et standby.password permettent de définir respectivement la chaîne de connexion au primaire et le mot de passe de connexion.

temBoard est un outil de supervision et d’administration de parc d’instances PostgreSQL. Il est constitué d’un composant serveur et d’un agent conçu pour fonctionner sur les serveurs PostgreSQL.

Dans cet atelier, un serveur temBoard fonctionnera sur le serveur srv-helper. Une instance supervisée et un agent temBoard associé seront déployés sur srv-pg1 à l’aide de pglift.

Exécuter le playbook temboard_register.yml afin de réaliser l’enregistrement de l’instance primaire et secondaire dans l’interface temBoard:

[dalibo@srv-helper1 ~/ansible]$ ansible-playbook -i inventory temboard_register.yml

L’interface web de temBoard présente un certain nombre d’indicateurs sur l’instance enregistrée disponible depuis : https://srv-helper:8888

La vue Dashboard offre une vue d’ensemble des métriques de supervision de l’instance. On y retrouve les informations provenant de sondes qui surveillent les éléments suivants :

• utilisation du CPU, de la mémoire, du disque
• utilisation du cache disque (cache hit ratio)
• nombre de connexions établies, session en attente
• nombre de transactions par secondes (tps)
• génération de fichiers WAL
• génération de fichiers temporaires
• fragmentation (bloat) dans les tables et index.

La vue Activity permet de visualiser l’activité de l’instance en temps réel, notamment obtenir des informations sur les processus en cours, leur état, et les ordres SQL qu’ils exécutent. Il est possible de filtrer les sessions bloquantes (blocking) ou en attente (waiting) via les onglets respectifs.

La vue Monitoring permet de choisir des métriques à afficher sous forme de graphe, sur une plage de temps personnalisable.

La vue Status liste l’ensemble des sondes avec leur état actuel. Cliquer sur une sonde permet d’afficher le graphe correspondant.

La vue Maintenance établit une liste des bases de données de l’instance, avec des informations sur l’occupation d’espace disque et les taux de fragmentation associés. Les informations sur l’utilisation du disque sont alors détaillées par schéma, et des boutons apparaissent en haut de la page pour déclencher des opérations courantes de maintenance :

• VACUUM
• VACUUM FULL
• ANALYZE
• REINDEX

La vue Configuration permet la configuration de l’instance depuis temBoard. Pour trouver un paramètre, il est possible de le rechercher ou bien de le retrouver dans les différentes catégories.