Industrialisation PostgreSQL avec pglift et Ansible

Atelier Industrialisation PostgreSQL

Dalibo & Contributors

Présentation

Introduction

Présentation de pglift

Permet de déployer et de gérer des instances PostgreSQL uniformisées
Instances prêtes pour la production dès leur déploiement
Capable de déployer des instances en réplication avec patroni
Prends en charge pgBackRest en mode local ou distant

CLI

L’ensemble de ces fonctionnalités sont exposées dans une interface en ligne de commande

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.

Ansible (Collection dalibo.pglift)

Fonctionnalités de pglift accessibles depuis la collection dalibo.pglift
Permet d’intégrer pglift dans un processus de déploiement automatisé ansible

Ansible (Collections dalibo.essential, dalibo.advanced, dalibo.extras)

Permet une installation et un déploiement automatisé de l’ensemble des éléments nécessaires à pglift
Chaque collection est spécifique à un thème :
- dalibo.essential : PostgreSQL, réplication, monitoring, sauvergarde
- dalibo.advanced : HA, performances, audit, ldap, pooler
- dalibo.extras : Tous les outils annexes (ETCD,rsyslog, logrotate, etc)

Installation et création d’instance avec pglift (CLI).

Pré-requis

Dépôts Powertools, EPEL, PGDG et Dalibo Labs
Utilisateur système postgres
Activer le lingering

Les machines suivantes sont utilisées pour cet atelier :

Serveur	OS	Rôle
srv-pg1	RockyLinux 8	Serveur de bases de données
srv-helper	RockyLinux 8	Serveur de sauvegarde et supervision

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

L’utilisateur est créé avant d’installer PostgreSQL pour avoir le contrôle sur le répertoire home utilisé.

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

Installation de PostgreSQL

Installer PostgreSQL 16 :

[root@srv-pg1 ~]# dnf install -y postgresql16 postgresql16-server \
postgresql16-contrib

Installation de pgBackrest

Installer pgBackRest :

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

Installation de pglift

pipx

Installer pglift avec pipx, en tant que postgres :

[root@srv-pg1 ~]# su - postgres << EOF
  pip3.9 install pipx
  ~/.local/bin/pipx install "pglift[cli]" --include-deps
  ~/.local/bin/pipx ensurepath
EOF

Ouvrir une nouvelle session est nécessaire pour que le binaire pglift soit dans le ${PATH} de l’utilisateur postgres

Configuration initiale

Fichier de configuration pglift :
- ~/.config/pglift/settings.yaml
- /etc/pglift/settings.yaml
Template de configuration PostgreSQL et pgBackRest
Installer la configuration de site : pglift site-configure install

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éploiement d’une instance (CLI)

[postgres@srv-pg1 ~]$ pglift instance create main \ 
--pgbackrest-stanza=main

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@~=#

Certaines commandes utilisées dans la suite de cet atelier nécessiteront que l’environnement de l’instance main soit chargé dans la session.

Installation et création d’instance avec pglift (Ansible).

Inventaire

Inventaire Ansible : ~/ansible/inventory

srv-pg1      ansible_user=dalibo ansible_port=2201 ansible_host=51.158.107.167
srv-helper1  ansible_user=dalibo ansible_port=2202 ansible_host=51.158.107.167

[database]
srv-pg1

[primary]
srv-pg1

[standby]
srv-pg1

[temboard]
srv-helper1

Collection

collections:
  - dalibo.pglift
  - community.general
  - dalibo.essential
  - dalibo.advanced
  - dalibo.extras

[dalibo@srv-helper1 ~/ansible]$ ansible-galaxy collection install -fr collections/requirements.yml

Installation de temBoard UI

Roles :
- dalibo.essential.temboard : Pour l’installation
- temboard : Pour la configuration
Playbook : temboard.yml

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

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

Installation de l’environnement

- name: Install Database Server
  hosts: database
  become: true
  roles:
    - dalibo.extras.repo_epel
    - dalibo.essential.repo_pgdg
    - dalibo.essential.repo_dalibo
    - dalibo.extras.accounts
    - dalibo.essential.postgresql
    - dalibo.essential.pgbackrest
    - dalibo.essential.temboard_agent
    - dalibo.essential.pglift

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

Déploiement d’une instance avec Ansible

Module dalibo.pglift.instance

Création de l’instance

---

- name: Deploy a standalone Instance
  hosts: primary
  become: true
  become_user: postgres
  tasks:
    - name: Create Instance
      dalibo.pglift.instance:
        name: main
        state: started
        version: 16
        port: 5432
        surole_password: Passw0rd
        pgbackrest:
          password: Passw0rd
          stanza: main-stz
        temboard:
          password: Passw0rd
        replrole_password: Passw0rd
        databases:
          - name: ws1

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

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

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

Création d’un secondaire en streaming réplication

---
- name: Deploy standby Instance
  hosts: standby
  become: true
  become_user: postgres
  tasks:
    - name: Creating standby Instance
      dalibo.pglift.instance:
        name: standby
        state: started
        version: 16
        port: 5433
        surole_password: Passw0rd
        pgbackrest:
          password: Passw0rd
          stanza: main-stz
        standby:
          primary_conninfo: "host=127.0.0.1 user=replication port=5432"
          password: Passw0rd
        temboard:
          password: Passw0rd
          port: 2346

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

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

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

Sauvegarde et Restauration pgbackrest avec pglift

Rappel Point In Time Recovery (PITR)

Point In Time Recovery
À chaud
En continu
Cohérente

Sauvegarde

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

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.

Restauration

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

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.

Manipulation d’instances avec pglift

Lister les instances

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

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     │
└────────┴─────────┴──────┴──────────────────────┴─────────────┘

Obtenir la descriptions des instances

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

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

Obtenir le statut d’une instance

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

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

Gestion de l’état

[postgres@srv-pg1 ~]$ pglift instance start main
[postgres@srv-pg1 ~]$ pglift instance stop main
[postgres@srv-pg1 ~]$ pglift instance restart main
[postgres@srv-pg1 ~]$ pglift instance reload main

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

Consulter les traces

[postgres@srv-pg1 ~]$ pglift instance logs 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

Gestion de la configuration de PostgreSQL

pglift peut manipuler la configuration des instances PostgreSQL avec la commande pglift pgconf
Opérations possibles : edit, remove, set, show

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

Modifier la configuration

[postgres@srv-pg1 ~]$ pglift pgconf -i main edit
[postgres@srv-pg1 ~]$ pglift pgconf -i main set

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

Afficher la configuration

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

Supprimer une configuration

[postgres@srv-pg1 ~]$ pglift pgconf -i main remove

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

Gestion du pg_hba.conf

pglift peut manipuler le pg_hba.conf avec la commande pglift pghba
Opérations possibles : add, remove

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

Maintenance des données

[postgres@srv-pg1 ~]$ pglift database -i main run "ANALYZE"
[postgres@srv-pg1 ~]$ pglift database -i main run -d postgres "VACUUM FULL"
[postgres@srv-pg1 ~]$ pglift database -i main run -x postgres "VACUUM"

Opérations sur les roles

[postgres@srv-pg1 ~]$ pglift role

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

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.

Opérations sur les bases de données

[postgres@srv-pg1 ~]$ pglift database

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

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.

Réplication

[postgres@srv-pg1 ~]$ pglift database create --stanby-for

tasks:
  - name: Creating standby Instance
    dalibo.pglift.instance:
      name: standby
      [...]
      standby:
        primary_conninfo: "host=X.X.X.X user=replication port=5432"
        password: Passw0rd

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.

Gestion de parc d’instances avec temBoard.

Présentation de temBoard

temBoard : supervision et administration de PostgreSQL
Un composant serveur, paquet temboard
Un agent pour les serveurs PostgreSQL, paquet temboard-agent

Enregistrement de l’instance

---

- name: Get instances gather facts
  hosts: database
  gather_facts: true

- name: Register instances in temBoard
  hosts: temboard
  connection: local
  gather_facts: true
  become: true
  tasks:
    - name: Register instances in temBoard
      become_user: temboard
      ansible.builtin.shell: "temboard register-instance {{ hostvars[item]['ansible_default_ipv4']['address'] }} 2345 -e default"
      loop: "{{ groups['primary'] }}"

    - name: Register instances in temBoard
      become_user: temboard
      ansible.builtin.shell: "temboard register-instance {{ hostvars[item]['ansible_default_ipv4']['address'] }} 2346 -e default"
      loop: "{{ groups['standby'] }}"

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

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

Visualisation des métriques

Vue Dashboard : Résumé des métriques
Vue Activity : Activité de l’instance en temps réel
Vue Monitoring : Afficher des métriques sous forme de graphes
Vue Status : Statut des sondes de supervision
Vue Maintenance :
- Informations sur l’utilisation des disques
- Possibilité de déclencher des opérations de maintenance (VACUUM [FULL], ANALYZE, REINDEX)
Vue Configuration : Modifier le paramétrage de l’instance

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.