Howto PostgreSQL

• Documentation : https://www.postgresql.org/docs/11/
• Rôle Ansible : https://forge.evolix.org/projects/ansible-roles/repository/show/postgresql
• The Internals of PostgreSQL : http://www.interdb.jp/pg/
• Statut de cette page : prod / bullseye

PostgreSQL est un système de gestion de base de données relationnelle et objet. PostgreSQL met l’accent sur le respect du standard SQL et l’intégrité des données, notamment avec le mécanisme des journaux de transactions (WAL : Write Ahead Logging) écrits sur le disque avant un enregistrement réel dans les fichiers de base de données.

Installation

# dpkg-reconfigure locales

# apt install postgresql

# /usr/lib/postgresql/13/bin/postgres -V
postgres (PostgreSQL) 13.11 (Debian 13.11-0+deb11u1)

# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Fri 2023-07-28 23:08:42 CEST; 54s ago
   Main PID: 523655 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 18776)
     Memory: 0B
        CPU: 0
     CGroup: /system.slice/postgresql.service

# pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
13  main    5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log

Note : il faut s’assurer d’avoir configuré sa locale système dpkg-reconfigure locales avant installation car l’initialisation des bases de données est faite avec la locale du système.

Installation via apt.postgresql.org

Le dépôt apt.postgresql.org permet d’installer des versions différentes de PostgreSQL, ainsi que plusieurs extensions. C’est maintenu par le PostgreSQL Global Development Group (PGDG) qui rassemble plusieurs développeurs Debian.

Ajouter le dépôt un fichier /etc/apt/sources.list.d/postgresql.list :

deb http://apt.postgresql.org/pub/repos/apt/ bullseye-pgdg main

Puis récupérer la clé GPG :

# wget -O /etc/apt/trusted.gpg.d/postgresql.asc https://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc
# dos2unix /etc/apt/trusted.gpg.d/postgresql.asc
# chmod 644 /etc/apt/trusted.gpg.d/postgresql.asc

On peut ainsi installer proprement PostgresSQL 16 - par exemple - en définissant les priorités via /etc/apt/preferences.d/postgresql :

Package: postgresql postgresql-client-common postgresql-common libpq5 libdbd-pg-perl ptop
Pin: release a=bullseye-pgdg
Pin-Priority: 999

Puis :

# apt install postgresql-11

# pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
11  main    5432 online postgres /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-main.log

Administration basique

Lister les requêtes actives

Pour PostgreSQL >= 9.2 :

# su - postgres
$ psql
psql (9.6.6)

postgres=# SELECT * FROM pg_stat_activity ORDER by query_start;
postgres=# SELECT pid, datname, usename, client_addr, query_start, query FROM pg_stat_activity WHERE state='active' ORDER by query_start;

Pour PostgreSQL < 9.2 :

# su - postgres
$ psql

postgres=# SELECT * FROM pg_stat_activity WHERE current_query!='<IDLE>';

psql

Voici quelques astuces utilisables en ligne de commande psql :

$ psql

postgres=# \x : passe du format ligne au format colonne
postgres=# \pset pager off : désactive le « less » même pour les résultats volumineux
postgres=# \conninfo : donne des informations sur la connexion
postgres=# \g : rejoue la dernière requête
postgres=# \timing on : affiche le temps d'exécution des requêtes
postgres=# \! commande : permet d’exécuter des commandes shell, ex: \! mkdir /home/foo/bar

pg_top

# apt install ptop
# su - postgres
$ pg_top

Commandes utiles :

<space> - update screen
A       - EXPLAIN ANALYZE (UPDATE/DELETE safe)
C       - toggle the use of color
E       - show execution plan (UPDATE/DELETE safe)
I       - show I/O statistics per process (Linux only)
L       - show locks held by a process
M       - sort by memory usage
P       - sort by CPU usage
R       - show user table statistics
Q       - show current query of a process
T       - sort by time
X       - show user index statistics
c       - toggle the display of process commands
e       - list errors generated by last "kill" or "renice" command
i       - toggle the displaying of idle processes
k       - kill processes; send a signal to a list of processes
          not avilable when connected to a remote database
o       - specify sort order (cpu, size, res, time, command)
          index stats (idx_scan, idx_tup_fetch, idx_tup_read)
          table stats (seq_scan, seq_tup_read, idx_scan, idx_tup_fetch,
                       n_tup_ins, n_tup_upd, n_tup_del)
          i/o stats (pid, rchar, wchar, syscr, syscw, reads, writes, cwrites, command)
s       - change number of seconds to delay between updates
t       - Toggle between cumulative or differential statistics when viewing
          user table or user index statistics.
u       - display processes for only one user (+ selects all users)

Configuration

Fichiers de configuration :

/etc/postgresql/
└── 9.6
    └── main
        ├── environment
        ├── pg_ctl.conf
        ├── pg_hba.conf
        ├── pg_ident.conf
        ├── postgresql.conf
        └── start.conf

La plupart des changements de configuration peuvent être pris en compte à chaud avec un reload.

On verra dans ce cas les changements dans les logs :

2016-12-20 17:02:58 CET [13555]: [2-1] user=,db= LOG:  received SIGHUP, reloading configuration files
2016-12-20 17:02:58 CET [13555]: [3-1] user=,db= LOG:  parameter "log_temp_files" changed to "6MB"

Instances PostgreSQL

Une surcouche Debian permet de gérer simplement plusieurs versions et plusieurs instances d’une même version de PostgreSQL. Cela permet entre autre de faciliter les migrations d’une version majeure à une autre.

Par défaut le script d’init contrôle toutes les instances actives. Pour contrôler seulement une instance en particulier :

# pg_ctlcluster <version> <cluster> start|stop|restart|reload|status|promote

A partir de Debian 9, on utilise plutôt systemctl pour contrôler les instances :

# systemctl start|stop|restart|reload|status postgresql@<version>-<cluster>.service

Pour lister les instances existantes :

# pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
10  main    5433 online postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log
11  main    5433 online postgres /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-main.log

Pour créer une nouvelle instance :

$ /usr/bin/pg_createcluster <version> <nom_cluster>

Le port d’écoute de postgres sera automatiquement incrémenté de 1 pour ne pas rentrer en conflit avec les autres instances. L’encodage utilisé par défaut pour l’instance est celui du système. Par exemple si le système est en en_US.UTF-8 par défaut, l’instance créée utilisera l’encodage en_US.UTF-8.

On peut remarquer que toute l’arborescence est organisée en fonction des versions et des instances. Cela permet de les rendre réellement indépendantes :

• configuration : /etc/postgresql/<version>/<instance>/
• journaux : /var/log/postgresql/postgresql-<version>-<instance>.log
• stockage : /var/lib/postgresql/<version>/<instance>/
• binaires et bibliothèques : /usr/lib/postgresql/<version>/<instance>/
• etc…

pgpass

Pour se connecter plus facilement à postgresql (avec psql mais aussi pgdump etc), on peut utiliser le fichier ~/.pgpass avec comme format :

hostname:port:database:username:password

Note : Ce fichier doit avoir des droits en 600 et on peux utiliser des wilcards ’*’ pour autoriser le ou les champs souhaités.

Fichier .pg_service.conf

Pour se connecter a différentes instances postgresql, en local ou distante, à la racine de l’utilisateur postgres, créer un fichier .pg_service.conf comme ceci :

[instance1]
host=ip_locale_ou_distante
port=5432
dbname=foo
user=role_pg
password=bar

Log des requêtes lentes / slow queries

Il n’y a pas de fichier de log séparé pour logger les requêtes lentes, elles sont mises dans les logs principaux.

Il faut définir la variable log_min_duration_statement, par exemple à 1s.

Gestion des utilisateurs et permissions

PostgreSQL permet de lier un utilisateur Unix à un utilisateur PostgreSQL. C’est le cas pour l’utilisateur postgres (superadmin PostgreSQL), qui est lié à l’utilisateur Unix postgres.

Ainsi pour passer superadmin PostgreSQL :

# sudo -u postgres psql
psql (9.6.6, server 9.6.6)
Type "help" for help.

postgres=#

À noter que l’utilisateur Unix postgres a tous les droits sur les fichiers de configuration, les journaux, etc… sur le système.

Par défaut, un utilisateur Unix foo sera automatiquement lié à son compte PostgreSQL foo si il existe. C’est très pratique dans le cas de multiples comptes web sur la machine, on évite ainsi de stocker un mot de passe dans un fichier de paramètre de connexion.

La gestion des permissions se fait dans le fichier de configuration pg_hba.conf de l’instance en question.

Si on préfère passer par un mot de passe pour s’authentifier, il faut changer la ligne suivante dans le fichier pg_hba.conf :

- local   all             all                                     peer
+ local   all             all                                     password

Bien s’assurer que les utilisateurs PostgreSQL ont un mot de passe de défini avant !

Pour plus de détails on pourra consulter la documentation.

Lister les utilisateurs (ou rôles)

$ su - postgres
$ psql
=# SELECT * FROM pg_user;

On peut utiliser aussi la commande courte PostgreSQL \du.

Créer un utilisateur et une base de données

Note : Par défaut la base sera créée avec l’encodage du système. Si le système est en UTF-8 par défaut, la base créée sera en UTF-8. On peut spécifier un encodage alternatif avec l’option -E.

• En mode shell avec l’utilisateur Unix postgres :

$ su - postgres
$ apg -n1 -m15
$ createuser -P <user>
$ createdb -O <user> <database>

• En mode SQL avec l’utilisateur postgres

$ su - postgres
postgres@:~$ psql 
postgres=# CREATE ROLE <user> WITH LOGIN PASSWORD '<password>'
postgres=# CREATE DATABASE <database> OWNER <user>;

Créer un utilisateur avec des droits en lecture seule sur une ou plusieurs bases

$ su - postgres
$ apg -n1 -m15
$ createuser -P $user_ro

Puis, ajouter les droits :

$ psql -d <database>
=# GRANT CONNECT ON DATABASE <database> TO <user_ro>;
=# GRANT USAGE ON SCHEMA public TO <user_ro>;
=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user_ro>;
=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO <user_ro>;

Note : Le schéma public est celui par défaut à la création de la base, si aucun schéma n’a été spécifié.

Attention : Par défaut, tout utilisateur peut créer des tables sur le schéma public d’une base.

Si on veut être sûr que l’utilisateur en lecture seule ne créera pas de table (par exemple Metabase, qui prend parfois certaines libertés), il faut l’interdire pour tout le monde, puis l’autoriser au cas par cas :

$ psql -d <database>
=# REVOKE ALL ON schema public FROM public;
=# GRANT ALL ON schema public TO <db_adm_user>;

Pour lister les droits, on peut utiliser \l et la colonne Droits d'accès.

Pour supprimer cet utilisateur “user_ro” :

$ psql -d <database>
=# REVOKE CONNECT ON DATABASE <database> FROM <user_ro>;
=# REVOKE USAGE ON SCHEMA public FROM <user_ro>;
=# REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM <user_ro>;
=# ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM <user_ro>;

$ dropuser $user_ro

Créer un utilisateur avec le droit de créer et supprimer des bases de données

$ createuser -P <user>
postgres@:~$ psql 
postgres=# ALTER USER <user> CREATEDB;

Créer un utilisateur avec les droits superuser

$ createuser -P <user>
postgres@:~$ psql 
postgres=# ALTER USER <user> SUPERUSER;

Changer le mot de passe d’un utilisateur

$ su - postgres
$ psql -c "ALTER USER <login> WITH PASSWORD '<pass>'"

Supprimer un utilisateur

$ su - postgres
$ dropuser <login>

Optimisation

La configuration par défaut est faite pour s’adapter à toutes sortes de machines, elle n’est donc pas adaptée en terme de performances. Nous allons voir ici quelques paramètres qui peuvent améliorer les performances de PostgreSQL. Vous pouvez utilisez le site PgTune pour avoir une idée des paramètres à utiliser en fonction de vos ressources.

• shared_buffers : Ce paramètre détermine la quantité de mémoire dédiée à PostgreSQL pour mettre en cache les données. Il est recommandé de mettre 1/4 de la quantité de RAM total de votre machine.

• max_connections : Détermine le nombre maximum de clients connectés simultanément. Ce paramètre est très important pour certains paramètres car les ressources mémoires qui sont ou seront allouées par client, il est donc suggérer d’utiliser le maximum de mémoire possible. work_mem x nb_clients = maxRAM. Éviter de dépasser les 300. Dans de tel cas il faut préférer l’utilisation d’un multiplexeur de connexions comme PgBouncer.

• work_mem : Le work_mem permet d’éviter lors de grosses opérations, de swapper les données sur le disque pour les traiter. Attention il est rattaché au nombres de clients ! Par exemple un work_mem de 50Mo avec 30 utilisateurs, cela donne 1.5Go, il est donc facile de faire passer la machine en out-of-memory… On le définit généralement à 8 voir 16 Mo.

• effective_cache_size : Ce paramètre détermine combien de mémoire devrait être disponible pour l’OS et les mémoire tampons de PostgreSQL, ce n’est pas une allocation. Cette valeur est seulement utilisé par le planificateur de requêtes de PostgreSQL pour savoir si les requêtes vont tenir en RAM ou pas. Il est recommandé de mettre 75% de la RAM pour un serveur dédié à PostgreSQL.

• checkpoint_segments : PostgreSQL (dans les versions antérieures à 9.6) écrit les transactions effectuées dans un fichier de log (nommé WAL, pour Write Ahead Log). Un segment est en fait un fichier, qui fait 16Mo par défaut. Un checkpoint est fait à chaque checkpoint_segment, par défaut 3, donc 3x16 = 48Mo, ce qui peut être un goulot d’étranglement de faire des checkpoints tous les 48Mo. Il est recommandé de le mettre entre 8 (faible écritures sur les bases) et 64 (beaucoup d’écritures). 30 est une bonne valeur pour un usage standard.

• checkpoint_completion_target : À partir de PostgreSQL 8.3, les checkpoint sont écrits pendant que le système commence à travailler sur le prochain checkpoint. Par défaut la valeur est de 0.5 (50%), et donc PostgreSQL s’occupe de finir le checkpoint quand le suivant est fini à 50%. Il est recommandé de fixer 90%.

• autovacuum : PostgreSQL réalise de nombreuses maintenances pour nettoyer la base de données. C’est une mauvaise idée de le de désactiver complètement, mais cela peut-être utile de le désactiver temporairement, notamment en cas de forte charge.

• wal_buffers : La valeur par défaut est bien trop petite (64Ko), il est recommandé de la passer de 1Mo à 16Mo.

• maintenance_work_mem : voir http://blog.guillaume.lelarge.info/index.php/post/2015/07/14/Comment-quantifier-le-maintenance_work_mem

• random_page_cost : coût d’accès aux disques : à mettre à 1.1 si vous avez un disque SSD ou 1.5 pour des disques durs SAS.

• Article intéressant : https://pgdash.io/blog/scaling-postgres.html

Modifier un paramètre

Pour changer la valeur d’une directive, il y a besoin de vérifier qu’elle peut être modifiable à chaud.

Vérifier les paramètres actuels:

SELECT * from pg_settings;

Puis appliquer la nouvelle valeur:

UPDATE pg_settings SET setting = 5000 WHERE name = 'log_min_duration_statement';

Administration

Toutes les commandes d’administration doivent être exécutées depuis l’utilisateur Unix postgres.

• Se conmecter avec un utilisateur à une base psql en local :

postgres:~$ psql -h 127.0.0.1 -U USER -W -d DB

• Suppression d’une base de données :

$ dropdb <base>

• Sélectionner une base de données (l’équivalent de use dans le monde MySQL) :

=# \c <base>

• Lister les bases de données :

$ psql -l

ou :

=# \l

ou encore :

=# SELECT * FROM pg_database;

• Lister les colonnes d’une table

=# \d+ <table>

• Lister les indexes :

=# \di

• Lister les triggers :

=# SELECT * FROM pg_trigger;

• Lister les triggers avec la définition SQL, la procédure exécutée, le langage et le code de la procédure :

=# SELECT t.tgname AS nom_du_trigger, pg_get_triggerdef( t.oid ) AS definition_du_trigger,
     p.proname AS procedure_executee, l.lanname AS langage_procedure, p.prosrc AS code_de_la_procedure
     FROM pg_trigger t JOIN pg_proc p ON p.oid = t.tgfoid JOIN pg_language l ON l.oid = p.prolang;

• Lister les sequences d’une base :

=# \ds

ou avec une requête SQL :

=# SELECT relname FROM pg_class WHERE relkind = 'S';

• Faire en sorte qu’un utilisateur hérite des droits d’un autre utilisateur (à vérifier) :

=# GRANT admin TO jdoe;

(Il faut que jdoe ait été créé avec l’option INHERIT (c’est le cas par défaut))

Si on doit créer un utilisateur qui dois avoir access à une base de données, mais dont il n’est pas le propriétaire, il faut que celui-ci hérite des droits du propriétaire. A faire avec tous les utilisateurs de toutes les bases dont ce rôle / utilisateur postgresql doit avoir accès.

• Donner les droits à un utilisateur existant de créer des bases de données (à vérifier) :

=# ALTER USER jdoe CREATEDB;

• Obtenir la taille d’une base ou des tables :

=# SELECT pg_size_pretty(pg_database_size('DB'));
=# SELECT pg_size_pretty(pg_relation_size('TABLE'));
=# SELECT pg_size_pretty(pg_total_relation_size('TABLE')); // taille avec les indexes
=# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC

Attention, pg_class semble ne pas se mettre à jour dynamiquement. Il faut apparemment faire un VACUUM ANALYZE pour forcer la mise à jour.

• Obtenir la taille de toutes les tables, par ordre de grandeur, dans le schema ‘public’ :

select
  table_name,
  pg_size_pretty(pg_total_relation_size(quote_ident(table_name))),
  pg_total_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'public'
order by 3 desc;

• Si il y a d’autres schema que ‘public’, on peux utilisé ceci pour lister les tables dans tous les schemas :

select table_schema, table_name, pg_relation_size(table_schema||'.'||table_name) from information_schema.tables order by 3;

Ou

pour une base

=# \l+

pour les tables de la base foo

=# \c foo
foo=# \d+

• Changer le propriétaire d’une sequence :

foo=# alter sequence bar_seq owner to nomrole;

On peux aussi le faire sur toutes les sequences d’une base avec un boucle en bash comme ceci :

postgres@:~$ for db in mabase ; do for seq in $(psql -qAt -c "SELECT relname FROM pg_class WHERE relkind = 'S';" $db) ; do psql -c "alter sequence \"$seq\" owner to $db" $db; done; done

• Terminer une session en cours (équivalent de KILL de MySQL) :

=# SELECT pg_terminate_backend(<pid>);

• Afficher (ou masquer) combien de temps prend une requête :

=# \timing

• Lister les connections en idle qui n’ont effectué aucune requête depuis plus d’un jour :

SELECT pid, query FROM pg_stat_activity WHERE state_change < now() -
interval '1d' and state = 'idle' ;

• Tuer toutes les requêtes en idle en cas de saturation :

ps aux|grep postgres|grep idle|awk '//{print $2}'|while read pid;do kill $pid;done

• Lister tous les schémas d’une base et leurs propriétaires :

=# \dn

• Lister les tables de tous les schémas :

=# \dt

ou si on veux également lister les tables en incluant les schémas interne à PostgreSQL :

=# \dt *.*

• Lister les requêtes en cours (équivalent de show full processlist dans mysql) :

=# SELECT * FROM pg_stat_activity;

Ajouter un langage

On peut installer un “langage” (PL/PGSL, PL/Perl, PL/TCP) pour une base. Exemple :

# su postgres
$ createlang plpgsql production

On peut voir tous les langages disponibles via :

postgres=# select * from pg_pltemplate;
 tmplname  | tmpltrusted | tmpldbacreate |      tmplhandler      |   tmplvalidator   |   tmpllibrary    | tmplacl
-----------+-------------+---------------+-----------------------+-------------------+------------------+---------
 plpgsql   | t           | t             | plpgsql_call_handler  | plpgsql_validator | $libdir/plpgsql  |
 pltcl     | t           | t             | pltcl_call_handler    |                   | $libdir/pltcl    |
 pltclu    | f           | f             | pltclu_call_handler   |                   | $libdir/pltcl    |
 plperl    | t           | t             | plperl_call_handler   | plperl_validator  | $libdir/plperl   |
 plperlu   | f           | f             | plperl_call_handler   | plperl_validator  | $libdir/plperl   |
 plpythonu | f           | f             | plpython_call_handler |                   | $libdir/plpython |
(6 rows)

Sauvegardes et restaurations des données de PostgreSQL

Deux principales possibilités existent pour sauvegarder les bases de données PostgreSQL :

• sauvegarde SQL : simple à mettre en place, mais lent, impacte les requêtes en cours (locks) et consomme beaucoup de place ;
• sauvegarde du datadir : complètement transparent pour les connexions actives, synchro uniquement des fichiers modifiés par rapport à la dernière sauvegarde, mais plus complexe à mettre en place (gestion des WAL).

Sauvegarde SQL

• Sauvegarde d’une seule base (Attention : ne sauvegarde ni les tablespaces, ni les roles.) :

$ pg_dump <base> > dump.sql

Pour sauvegarder seulement les tablespaces et les roles :

$ pg_dumpall --globals-only > dump-roles-tablespaces.sql

• Sauvegarde de toutes les bases :

$ pg_dumpall > dump.sql

• Sauvegarder seulement certaines tables d’une base de données :

$ pg_dump --table=<table1> -t <table2> <base>

• Sauvegarder tout une base de données sauf certaines tables :

$ pg_dump --exclude-table=<table1> -T <table2> <base>

• À moins de vouloir restaurer le contenu sur un autre moteur de base de données, il est préférable de faire des dumps au format custom, qui permet la compression, un processus de restauration sur plusieurs threads, et de sélectionner finement quels éléments restaurer lors de la restauration :

$ pg_dump --format=custom <base> > dump.sql

• Pour compresser directement le dump

$ pg_dumpall | gzip > dump.sql.gz

• Si on veux un dump au format binaire, pour le restaurer ensuite avec pg_restore, mais qu’il ai les requêtes DROP TABLE, DROP INDEX, etc…

# pg_dump --clean --format=custom <base> > dump.sql

Restauration SQL

Restauration de données au format texte SQL

La base doit exister, sinon il faut la créer avec createdb -O <owner> <db> :

$ psql -U <user> -W -d <base> < dump.sql

Attention: si on restaure de cette manière il faut vérifier que les droits de la base, des tables, des séquences et des vues, soient bien avec le propriétaire de la base, si ce c’est pas le cas on peut modifier les droits comme ceci :

• changement des droits pour toutes les tables d’une base :

# for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" <base>` ; do psql -c "alter table \"$tbl\" owner to <user>" <base>; done

• changement des droits pour toutes les séquences d’une base :

# for tbl in $(psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" "${base:?}") ; do echo psql -c "alter sequence \"${tbl}\" owner to \"${user:?}\"" "${base:?}"; done

• Changement des droits pour toutes les vues d’une base :

# for view in $(psql -qAt -c "SELECT table_name FROM information_schema.views WHERE table_schema = 'public';" "${base:?}") ; do echo psql -c "ALTER VIEW ${view} OWNER TO \"${user:?}\";" "${base:?}"; done

• Changement des droits de toutes les fonctions d’une base : À faire à la main. Il est possible d’obtenir la liste des fonctions avec leur propriétaire avec \df+.

Restauration de données au format custom :

$ pg_restore -F c all.dump

• Restauration de données d’une base au format custom :

• Pour supprimer la base et la re-créer à partir de la sauvegarde :

# dropdb <base>
# pg_restore -C -d <base> base.dump

Avec -C, les données sont toujours restaurées dans le nom de la base qui apparaît dans le fichier de sauvegarde.

• Pour réinjecter les données de la sauvegarde sans supprimer la base au préalable :

# pg_restore --clean -d <base> -U <user> -W --role=<user> base.dump

–clean nettoie (supprime) les objets de la base de données avant de les créer.

–role Indique un nom de rôle utilisé pour la restauration. Cette option fait que pg_restore exécute un SET ROLE nom_rôle après connexion à la base de données.

Important : il est nécessaire de faire un ANALYZE après une restauration de tables ou bases de données afin de mettre à jour les statistiques de PostgreSQL. Ces statistiques sont utilisées par PostgreSQL pour déterminer la manière la plus optimum de requêter la base en fonction de la répartition des valeurs des champs.

Optimisations pour la restauration :

• paralléliser la restauration : pg_restore -j <nb de cœurs CPU> (ne pas compter les cœurs hyperthreadés) ;
• augmenter autant que possible le _maintenance_work_mem_ (attention, il sera multiplié par le nombre de processus utilisés pour la restauration). Dans tous les cas, ne pas dépasser les 2 Go. Peut être fait dans la conf ou dans une session ;
• Mettre la directive synchronous_commit à off.

Sauvegarde du datadir

Doc de référence : http://www.postgresql.org/docs/9.6/static/continuous-archiving.html

Le principe est le suivant :

• indiquer à PostgreSQL qu’on commence une sauvegarde. Il va notamment faire un checkpoint dans son WAL courant ;
• faire un rsync du datadir, en excluant les WAL (pg_xlog/*) ;
• indiquer à PostgreSQL la fin de la sauvegarde, afin qu’il termine son WAL courant (pour qu’il puisse être copié par archive_command).

Note : à partir de PostgreSQL 10, pg_xlog se nomme pg_wall

Il faut en parallèle activer l’archivage des WAL dans la configuration :

wal_level = 'archive'  # ou plus (hot_standby)
archive_command = 'rsync %p backup.example.net:/backup/…/archives/%f'

Ainsi, dès qu’un WAL est marqué comme complété (pg_xlog/archive_status/*.ready), il est copié sur le serveur de backup conformément à archive_command. Si la copie a réussi, le .ready est renommé en .done.

postgres$ psql -c "SELECT pg_start_backup('evobackup')"
# rsync -a --delete --exclude /pg_xlog/* --exclude /postmaster.* ~postgres/9.4/main/ backup.example.com:/backup/…/base/
postgres$ psql -c "SELECT pg_stop_backup()"

Restauration du datadir

S’assurer que PostgreSQL est éteint, et restaurer le datadir :

# rsync -a --exclude /pg_xlog/* backup.example.com:/backup/…/base/ ~postgres/9.4/main/

Note : à partir de PostgreSQL 10, pg_xlog se nomme pg_wall

Attention à ne surtout pas effacer le contenu de pg_xlog/, qui peut contenir le dernier WAL courant (donc pas encore copié sur le serveur de backup).

Créer un fichier recovery.conf à la racine du datadir avec le contenu suivant, puis démarrer PostgreSQL :

restore_command = 'rsync backup.example.net:/backup/…/archives/%f %p'

PostgreSQL va rejouer tous les WAL, exactement de la même manière qu’il le fait en cas de crash, puis se mettra à accepter les connexions.

Il est possible de rejouer les WAL jusqu’à une certaine date (recovery_target_time) ou un certain identifiant de transaction (recovery_target_xid).

Mise à jour

La mise à jour de PostgreSQL d’une version majeure à une autre implique une mise à jour du datadir.

Dans Debian, chaque version de PostgreSQL a son propre paquet (postgresql-9.3, postgresql-9.4, etc…). La mise à jour doit donc forcément se faire de manière explicite par un apt install du paquet en question. Il n’est pas obligatoire de faire chaque mise à jour intermédiaire pour arriver à celle voulue (i.e. on peut passer de la 9.1 à la 9.6 sans devoir passer par la 9.2, 9.3, etc…).

Procédure à suivre pour la mise à jour

Dans la procédure suivante, on suppose que l’on met à jour un cluster en version 9.3 vers la version 9.6. Le cluster s’appelle main (nom par défaut).

• Installer les paquets :

# apt install postgresql-9.6 postgresql-contrib-9.6 postgresql-client-9.6

• supprimer le cluster créé par défaut à l’installation :

# pg_dropcluster 9.6 main --stop

• lancer la mise à jour du cluster. En résumé, pg_upgrade va interdire toute connexion (autre que postgres) sur le cluster à mettre à jour, faire un rsync du datadir (option -m upgrade) avec des hardlinks, pour sauver de la place et du temps (option -k) et intervertir le port de connexion des 2 clusters puis réautoriser les connexions au cluster :

# pg_upgradecluster 9.3 main -m upgrade -k

• si tout s’est bien passé, on peut voir le nouveau cluster et s’y connecter :

# pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.3 main    5433 down   postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
# sudo -u postgres psql
psql (9.6.5)

• on peut alors supprimer l’ancien cluster ainsi que les paquets :

# pg_dropcluster 9.3 main
# apt remove postgresql-9.3 postgresql-client-9.3 postgresql-contrib-9.3

Monitoring

Munin

On peut utiliser les plugins standards suivants :

postgres_bgwriter        postgres_connections_db  postgres_xlog
postgres_checkpoints     postgres_users

Nagios

Vérification via le check historique:

$ /usr/lib/nagios/plugins/check_pgsql -H localhost -l nagios -p PASSWORD

OK - database template1 (0.011915 sec.)|time=0.011915s;2.000000;8.000000;0.000000

On préfererra utiliser le check pgactivity : https://github.com/OPMDG/check_pgactivity

En Debian 10, on installe le paquet check-pgactivity

Ce check permet de monitorer différents point, comme les process idle in transaction, la réplication logique et streaming, etc…

Plomberie

WAL

• The Internals of PostgreSQL Chapter 9 WAL : http://www.interdb.jp/pg/pgsql09.html
• Article de Dalibo (ancien mais presque encore exact) : https://www.dalibo.org/glmf108_postgresql_et_ses_journaux_de_transactions

Une transaction est un ensemble de requêtes SQL qui sont jouées d’un seul bloc. Pour créer une transaction, on utilise BEGIN et COMMIT au début et à la fin du groupe d’instructions ; on peut aussi utiliser ROLLBACK pour annuler une transaction en cours.

En fait, PostgreSQL traite aussi les instructions SQL simples comme une transaction, et considére donc que tout ce qui est écrit est une transaction.

Quand une transaction est jouée, PostgreSQL ne l’écrit pas directement dans les fichiers de bases de données, il l’écrit d’abord dans un journal de transactions situé dans le répertoire pg_xlog/ (ou pg_wal/ depuis PostgreSQL 10) puis s’assure que l’OS l’écrit réellement sur le disque (sous Linux c’est en général avec la fonction système fsync()).

Les données sont écrites dans les fichiers de bases de données lors de checkpoints réguliers : si les journaux de transactions dépassent 48 Mo ou alors si cela fait plus de 5 minutes (ces valeurs sont réglages). C’est également fait lors d’un arrêt de PostgreSQL ou de l’exécution de la commande CHECKPOINT.

On appelle WAL (Write Ahead Logging) l’ensemble du mécanisme ou alors plus simplement les journaux de transaction.

Par défaut, il y a 8 fichiers de 16 Mo qui tournent et s’effacent tout seul :

# ls -l /var/lib/postgresql/9.6/main/pg_xlog/
total 131084
-rw------- 1 postgres postgres 16777216 janv. 27 19:48 000000010000000300000013
-rw------- 1 postgres postgres 16777216 janv.  8 18:01 000000010000000300000014
-rw------- 1 postgres postgres 16777216 janv. 11 11:40 000000010000000300000015
-rw------- 1 postgres postgres 16777216 janv. 15 14:14 000000010000000300000016
-rw------- 1 postgres postgres 16777216 janv. 18 08:43 000000010000000300000017
-rw------- 1 postgres postgres 16777216 janv. 22 06:42 000000010000000300000018
-rw------- 1 postgres postgres 16777216 janv. 24 19:38 000000010000000300000019
-rw------- 1 postgres postgres 16777216 janv. 26 10:35 00000001000000030000001A
drwx------ 2 postgres postgres     4096 mai    2  2012 archive_status

On remarque que les fichiers de WAL sont numérotés et qu’ils sont réalloués pour préparer les prochaines transactions.

On peut d’ailleurs voir le fichier utilisé et la position ainsi :

postgres=# SELECT pg_xlogfile_name(pg_current_xlog_location());
     pg_xlogfile_name     
--------------------------
 000000010000000300000013
(1 row)

postgres=# SELECT pg_current_xlog_location();
 pg_current_xlog_location 
--------------------------
 3/138BE620
(1 row)

Ce mécanisme de WAL est à la base de PostgreSQL, et il sert notamment pour la sauvegarde ou la réplication.

Pour la sauvegarde on peut ainsi envoyer automatiquement un fichier de WAL dès qu’il est fermé avec l’option archive_command = 'cp %p /tmp/pg_xlog_archives/%f'.

VACUUM et ANALYZE

VACUUM permet de nettoyer les données obsolètes (lignes d’une table qui ont été modifiées par un UPDATE par exemple) que PostgreSQL n’efface pas volontairement du disque puisque d’autres requêtes plus anciennes peuvent encore y accéder.

ANALYZE permet à PostgreSQL de collecter et mettre à jour ses statistiques sur la répartition des données en base afin que le planificateur de requêtes ait des données les plus précises possible.

ANALYZE sur toutes les tables d’une base de données :

=# ANALYZE;

Ces 2 opérations peuvent être exécutées en un seul coup :

=# VACUUM ANALYZE;

Par défaut, PostgreSQL lance au démarrage le processus autovacuum launcher process qui se charge d’exécuter des VACUUM et ANALYZE à intervalles réguliers. Les VACUUM et ANALYZE sont exécutés seulement si la quantité de données à nettoyer/réanalyser (en fait le nombre de DELETE et UPDATE que la table a reçu depuis son dernier passage) dépasse un certain seuil configurable.

VACUUM n’efface pas réellement les données sur le disque, il marque leurs emplacements comme libre de sorte que PostgreSQL puisse écrire dessus lorsqu’il aura à nouveau besoin d’écrire des données. Pour le forcer à libérer l’espace disque au niveau du système de fichiers, il faut exécuter un VACUUM FULL. Cette opération n’est pas faite par le démon autovacuum car elle requière de locker les tables en lecture et écriture (aucune requête n’est possible durant un VACUUM FULL). Le cas où l’on aurait besoin de lancer une telle opération serait après une grosse suppression de données (DELETE, TRUNCATE) et où on aurait besoin de récupérer de l’espace disque au niveau du système de fichiers pour autre chose. Dans les autres cas, il n’y a généralement pas d’utilité à lancer un VACUUM FULL.

Faire un VACUUM FULL sur toutes les bases de données :

$ vacuumdb -a -f -v

Benchmark

pgbench est un outil intégré à PostgreSQL (dans Debian, il est dans le paquet postgresql-contrib), il permet de réaliser des tests, et d’avoir les résultats en transactions par secondes (tps). Plus d’inos sur la doc officielle. Il est recommandé de réaliser 3 fois le bench pour avoir une meilleure précision. Un petit script PHP permet d’automatiser les benchs.

# apt install php7.0-cli postgresql-contrib
$ wget http://pastebin.com/download.php?i=hD570TgL -O /tmp/script.php
$ su postgres
$ createdb -O postgres -E UNICODE pgbench
$ /usr/lib/postgresql/9.6/bin/pgbench -i pgbench -s 50

Exemple avant optimisation

$ php -f script.php
Shared Memory Max is 3,072.00Mb
PostgreSQL is using 10.57Mb
Testing: 5 clients with 5 transactions (5 samples)
  TPS Min 543.218461; Max: 666.471168; Avg: 590.9413884; Dev: 48.7702658268
  CTPS Min: 702.760443; Max: 926.406285; Avg: 788.2504666; Dev: 89.6348872298
Testing: 5 clients with 10 transactions (5 samples)
  TPS Min 132.604187; Max: 808.211428; Avg: 644.4933226; Dev: 288.384840931
  CTPS Min: 136.534056; Max: 973.652951; Avg: 765.8912782; Dev: 355.324132181
Testing: 5 clients with 50 transactions (5 samples)
  TPS Min 915.127422; Max: 1042.774615; Avg: 987.7306472; Dev: 47.8906556447
  CTPS Min: 951.916780; Max: 1090.940827; Avg: 1030.9061366; Dev: 52.042571476
Testing: 10 clients with 5 transactions (5 samples)
  TPS Min 598.802395; Max: 720.990930; Avg: 677.7113778; Dev: 48.4403117914
  CTPS Min: 797.143039; Max: 1033.399471; Avg: 944.3966004; Dev: 90.8906145724
Testing: 10 clients with 10 transactions (5 samples)
  TPS Min 665.552975; Max: 935.007620; Avg: 799.1810664; Dev: 126.329870233
  CTPS Min: 774.287462; Max: 1157.742402; Avg: 964.7010562; Dev: 179.888588017
Testing: 10 clients with 50 transactions (5 samples)
  TPS Min 26.782745; Max: 1040.286120; Avg: 549.5094934; Dev: 486.121295095
  CTPS Min: 26.812935; Max: 1086.554969; Avg: 570.8430692; Dev: 506.835600619
pg_benchmark executed 30 tests in about 39 seconds

Exemple après optimisation

Shared Memory Max is 3,072.00Mb
PostgreSQL is using 10.57Mb
Testing: 5 clients with 5 transactions (5 samples)
  TPS Min 668.878425; Max: 716.147698; Avg: 692.1704534; Dev: 18.0051424274
  CTPS Min: 958.662474; Max: 1050.199538; Avg: 1002.8300562; Dev: 34.217782456
Testing: 5 clients with 10 transactions (5 samples)
  TPS Min 793.550025; Max: 989.922588; Avg: 909.7281046; Dev: 73.6959734873
  CTPS Min: 974.601875; Max: 1265.086152; Avg: 1146.0283372; Dev: 112.187073149
Testing: 5 clients with 50 transactions (5 samples)
  TPS Min 1051.069358; Max: 1226.031092; Avg: 1126.4953152; Dev: 63.4721625573
  CTPS Min: 1105.519638; Max: 1297.420728; Avg: 1186.3068702; Dev: 69.9807788556
Testing: 10 clients with 5 transactions (5 samples)
  TPS Min 622.091721; Max: 781.677480; Avg: 729.4070334; Dev: 64.9725527355
  CTPS Min: 846.596681; Max: 1169.235086; Avg: 1062.2111624; Dev: 131.355197786
Testing: 10 clients with 10 transactions (5 samples)
  TPS Min 945.930607; Max: 1084.363479; Avg: 997.8187306; Dev: 57.4096186704
  CTPS Min: 1182.886005; Max: 1401.993635; Avg: 1264.4000858; Dev: 91.9101739593
Testing: 10 clients with 50 transactions (5 samples)
  TPS Min 1210.334319; Max: 1467.351431; Avg: 1341.577537; Dev: 98.2366159289
  CTPS Min: 1277.331705; Max: 1563.379401; Avg: 1422.1924606; Dev: 109.288692112
pg_benchmark executed 30 tests in about 5 seconds

Outils

PgBouncer

PgBouncer, de même que PgPool-II, permet de multiplexer plusieurs connexions à PostgreSQL en une seule : PgBouncer va recevoir les multiples connexions des clients et les envoyer à PostgreSQL à travers un pool de connexions qu’il maintient de manière persistente avec le serveur. L’intérêt principal est d’offrir un gain de performance puisque, avec PostgreSQL, une nouvelle connexion signifie un fork d’un nouveau processus, ce qui coûteux pour le système.

Les autres avantages sont notamment la possibilité de gérer la répartition des requêtes vers plusieurs serveurs PostgreSQL en réplication ou le redémarrage d’un serveur PostgreSQL sans coupure (les requêtes seront alors mises en file d’attente jusqu’à ce que le serveur soit à nouveau opérationnel).

Dans une infrastructure multi-serveurs, il peut être installé soit sur chacun des frontaux web à la manière d’un HAProxy, soit sur les serveurs de bases de données dépendamment de ce que l’on recherche.

Installation :

# apt install pgbouncer

Augmentation du nombre de fichiers ouverts maximum :

# echo "ulimit -n 65536" >>/etc/default/pgbouncer

Note : en Stretch PgBouncer n’a toujours pas d’unité systemd donc le fichier /etc/default/pgbouncer est toujours pris en compte.

La configuration se fait ensuite dans le fichier /etc/pgbouncer/pgbouncer.ini :

[databases]
# La base foodb est héberger sur le serveur accessible sur 10.0.0.32
foodb = host=10.0.0.32
[…]

[pgbouncer]
# À décommenter si pgbouncer doit être accessible depuis d'autres machines
#listen_addr = *
#listen_addr = "x.y.z.t,127.0.0.1"

# Utilisateurs qui auront accès à la pseudo-base pgbouncer
admin_users = pgbpostgres
stats_users = pgbstats

# La connexion au serveur redevient libre lorsque le client termine une transaction
# Autres valeurs possibles : session (lorsque le client ferme la session), statement (lorsque la requête se termine)
pool_mode = transaction

# Nombre maximum de connexions entrantes
max_client_conn = 5000

# Nombre de connexion maintenues avec le serveur
default_pool_size = 20

log_connections = 0
log_disconnections = 0

Il est également nécessaire de lister les utilisateurs pouvant se connecter, puisque l’authentification se fera désormais au niveau de PgBouncer. Dans le fichier /etc/pgbouncer/userlist.txt :

"pgbpostgres" "some_password"
"pgbstats" "some_password"
"jdoe" "some_password"
[…]

Une fois démarré, PgBouncer écoute sur le port TCP/6432.

Une pseudo-base spécifique à PgBouncer permet de faire des opérations d’administration et de récupérer des statistiques intéressantes :

psql -p 6432 -U pgbpostgres pgbouncer
pgbouncer=# show help;

Il existe un plugin Munin pour PgBouncer : https://github.com/munin-monitoring/contrib/blob/master/plugins/postgresql/pgbouncer_

barman

• Documentation : http://docs.pgbarman.org/release/2.4/

barman est un outil pour gérer les sauvegardes et les restaurations de données en se basant sur les log de transactions (WAL) de PostgreSQL.

Barman s’installe généralement sur un serveur de sauvegardes :

# apt install barman barman-cli

Le client PostgreSQL sur le serveur Barman doit être disponible dans la même version que sur le serveur PostgreSQL, exemple pour un serveur en 9.2 :

apt install postgresql-client-9.2

Pour chaque serveur PostgreSQL à sauvegarder, on crée un fichier dans /etc/barman.d/ :

[foo]
description =  "foo.example.net 9.6/main PostgreSQL instance"

ssh_command = ssh postgres@foo.example.net
conninfo = host=foo.example.net user=barman dbname=postgres

backup_method = rsync
reuse_backup = link

archiver = on

last_backup_maximum_age = 1 DAY

Il faut ensuite autoriser barman à se connecter en SSH et PostgreSQL sur la machine à sauvegarder.

Dans la configuration de PostgreSQL, rajouter les directives suivantes :

wal_level = 'replica' # ou plus haut
archive_mode = on
archive_command = 'rsync -a %p barman@foo.example.com:foo/incoming/%f'

Une fois en place, voici quelques commandes utilisables :

# barman list-server
foo00 - foo00 9.2/main PostgreSQL instance
# barman status foo00
Server foo00:
        Description: foo00 9.2/main PostgreSQL instance
        Active: True
        Disabled: False
        PostgreSQL version: 9.2.24
        Cluster state: in production
        pgespresso extension: Not available
        Current data size: 287.8 GiB
        PostgreSQL Data directory: /var/lib/postgresql/9.2/main
        Current WAL segment: 0000000100002CEF00000083
        PostgreSQL 'archive_command' setting: rsync -a %p barman@foo00.example.com:/var/lib/barman/foo00/incoming/%f
        Last archived WAL: No WAL segment shipped yet
        Retention policies: not enforced
        No. of available backups: 1
        First available backup: 20180723T174316
        Last available backup: 20180723T174316
        Minimum redundancy requirements: satisfied (1/0)
# barman list-backup foo
foo00 20180723T174316 - Mon Jul 23 21:47:34 2018 - Size: 413.2 GiB - WAL Size: 89.9 GiB

Il est ensuite possible de surveiller l’état des backup avec nagios avec la commande nagios suivante:

command[check_barman]=/usr/local/lib/monitoringctl/alerts_wrapper --name barman sudo -u barman /usr/bin/barman check --nagios all
# ou si alerts_wrapper est absent :
command[check_barman]=sudo -u barman /usr/bin/barman check --nagios all

qui nécessite les droits sudo suivants:

nagios          ALL = (barman) NOPASSWD: /usr/bin/barman check --nagios all

pgbadger

PgBadger permet d’analyser des logs PostgreSQL et de générer une page HTML représentant les résultats sous forme de graphes et tableau de données.

Avant tout, il faut définir le log_min_duration_statement dans la configuration de PostgreSQL à une valeur raisonnable (suffisamment de requêtes doivent être loguées sans pour autant impacter les performances). S’assurer également que PostgreSQL ajoute suffisamment d’information de contexte sur chaque ligne de log écrite : log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '. On peut également loguer des informations supplémentaires comme les connexions/déconnexions, fichiers temporaires, etc…

En résumé voici ce à quoi la configuration de PostgreSQL devrait ressembler :

log_min_duration_statement = 1s
log_checkpoints = on
log_lock_waits = on
log_temp_files = 5MB
log_autovacuum_min_duration = 1s
log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '
lc_messages = 'C'

Installation :

# apt install pgbadger

Exécution :

# /usr/bin/pgbadger -I -q /var/log/postgresql/postgresql.log -O /var/www/pg_reports/

Plusieurs autres options peuvent être spécifiées, voir l’aide de la commande pour plus d’informations.

On peux mettre l’analyse des logs avec pgBadger dans la crontab comme ceci, avec un umask pour avoir les droits corrects pour nginx ou Apache :

0 4 * * * umask 022 ; /usr/bin/pgbadger -I -q /var/log/postgresql/postgresql-9.6-main.log -O /var/www/pg_reports/

On peux également exclure des requêtes avec les options suivantes :

• --exclude-query toute requête correspondant à l’expression régulière donnée sera exclue du rapport, exemple : "^(VACUUM|COMMIT)"
• --exclude-file chemin du fichier qui contient toutes les regex vers utiliser pour exclure les requêtes du rapport. Une expression régulière par ligne.

Vérifier aussi le logrotate des logs de posgresql, si on veux avoir un rapport journalier ou hebdomadaire.

PhpPgadmin / PgAdmin III

PhpPgadmin et PgAdmin III sont des clients web (pour le premier) et lourd (pour le second) pour interagir avec des bases de données PostgreSQL.

Installation :

# apt install phppgadmin
# apt install pgadmin3

Sur Debian Bookworm, il faut activer les backports pour installer phppgadmin.

Configuration de PhpPgAdmin avec Nginx

On installe les paquets en question :

# apt install nginx php-fpm

On configure un vhost en y rajoutant dans un server block la configuration suivante :

    location /phppgadmin-12345 {
        alias /usr/share/phppgadmin/;
        index index.php;

        location ~ \.php$ {
            fastcgi_pass   unix:/run/php/php8.2-fpm.sock;
            include snippets/fastcgi-php.conf;
            fastcgi_param SCRIPT_FILENAME $request_filename;
        }
    }

Configuration de PhpPgAdmin avec Apache

Dans le vhost souhaité, puor ajouter un alias pour PhpPgAdmin :

    Alias /phppgadmin /usr/share/phppgadmin
    <Directory /usr/share/phppgadmin>
        Require all denied
        Include /etc/apache2/ipaddr_whitelist.conf
    </Directory>

pg_stat_statements

pg_stat_statements est une extension PostgreSQL permettant de collecter des statistiques sur les requêtes reçues.

Installation :

# apt install postgresql-contrib-9.6

Activation :

shared_preload_libraries = 'pg_stat_statements'

On a maintenant accès à une vue contenant des informations utiles :

bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

Activation du support des larges pages (Hugepages)

Le support des Hugepages permet une optimisation de postgresql et du kernel, pour les requêtes avec un besoin important de mémoire.

Dans un premier temps il faut vérifier si le kernel supporte les Hugepages :

# cat /proc/meminfo | grep -i huge

AnonHugePages:    198656 kB
ShmemHugePages:        0 kB
HugePages_Total:       0
HugePages_Free:       85
HugePages_Rsvd:       16
HugePages_Surp:        0
Hugepagesize:       2048 kB

On voit que la taille d’une page est défini à 2048kB (2MB), mais que le nombre total des Hugepages (HugePages_Total) est à 0, les Hugepages sont donc désactivé pour le moment.

La définition du nombre total des Hugepages se défini avec la variable vm.nr_hugepages, pour savoir quelle valeur on doit définir à cette variable, il faut récupéré la valeur VmPeak du processus de PostgreSQL et de divisé cette valeur par la valeur du Hugepagesize :

# grep ^VmPeak /proc/1971/status
VmPeak:	17356400 kB

On divise donc VmPeak/Hugepagesize (17356400/2048=8474) ce qui donne la valeur de 8474 pour vm.nr_hugepages.

On peux utiliser ce script qui permet d’une manière plus rapide d’avoir cette information :

#!/bin/bash
pid=`head -1 /var/lib/postgresql/9.6/main/postmaster.pid`
echo "Pid:            $pid"
peak=`grep ^VmPeak /proc/$pid/status | awk '{ print $2 }'`
echo "VmPeak:            $peak kB"
hps=`grep ^Hugepagesize /proc/meminfo | awk '{ print $2 }'`
echo "Hugepagesize:   $hps kB"
hp=$((peak/hps))
echo Set Huge Pages:     $hp

On défini donc vm.nr_hugepages à 8474 :

# sysctl -w vm.nr_hugepages=8474

Il faut ensuite définir un ulimit illimité pour les memlock, on défini les memlock en unlimited si c’est un serveur PostgeSQL dédié comme ceci :

# vim /etc/security/limits.conf
*      soft    memlock         unlimited
*      hard    memlock         unlimited

Si le serveur, n’est pas un serveur SQL dédié, on créer un groupe hugepages et on ajoute l’utilisateur postgres à ce groupe :

# groupadd hugepages
# gpasswd -a postgres hugepages

Et on ajoute le groupe hugepages dans le fichier /etc/security/limits.conf :

# vim /etc/security/limits.conf
@hugepages      soft    memlock         unlimited
@hugepages      hard    memlock         unlimited

On spécifie l’ID du groupe pour la variable vm.hugetlb_shm_group, il faut donc définir soit l’ID du groupe hugepages, soit l’ID du groupe postgres selon la configuration choisi :

# sysctl -w vm.hugetlb_shm_group=119

On peux ensuite rendre la configuration persistante en créant le fichier /etc/sysctl.d/z-pgsql-tuning.conf :

# vim /etc/sysctl.d/z-pgsql-tuning.conf
vm.nr_hugepages = 8474
vm.hugetlb_shm_group = 119

On vérifie que l’utilisateur postgres est bien illimité pour les memlock (max locked memory) :

postgres@exotismes-sql10:~$ ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 513312
max locked memory       (kbytes, -l) unlimited
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 513312
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

Et enfin, on active la variable huge_page dans /etc/postgresql/9.6/main/postgresql.conf :

# vim /etc/postgresql/9.6/main/postgresql.conf

huge_pages = on

Réplication

Plusieurs solutions de réplication plus ou moins avancées existent avec PostgreSQL :

• Streaming Replication ou Physique : les données sont transférées immédiatement par un processus dédié (walsender) dans une connexion réseau établie avec le réplica. Contrairement aux autres solutions, cela nécessite une légère charge supplémentaire par réplica sur le maître pour faire tourner le processus walsender. En général ce système est couplé à l’envoi des WAL car si le réplica est trop en retard par rapport au master, il va lire les WAL jusqu’à avoir rattrapé son retard puis basculera tout seul sur la streaming replication
• Logique : les données sont répliquées au niveau des objets par un système de publication/abonnement
• PITR, Point In Time Recovery : copie des logs de transaction (WAL) sur un serveur distant pour archivage. Ils peuvent ensuite être rejoués jusqu’à un point précis en cas de perte de données par exemple.
• Warm Standby : les WAL sont copiés sous forme d’archive sur un second serveur sur lequel tourne un PostgreSQL en mode recovery constant. Chaque segment reçu est rejoué par PostgreSQL. Il est alors prêt à prendre le relais en cas de panne sur le serveur maître.
• Hot Standby : le principe est le même que pour le Warm Standby, mais le réplica peut être interrogé en lecture. Il y a néanmois une légère différence perpétuelle entre le master et le réplica car le WAL est transféré seulement lorsque l’archive a fini d’être écrite.
• Slony : système de réplication basé sur l’ajout de triggers sur chaque table à répliquer. Cela nécessite une gestion assez complexe mais c’était la seule façon d’avoir une réplication immédiate avant l’arrivée de la Streaming Replication. Cela reste la seule solution pour avoir une réplication au niveau des tables et non de la base entière (par exemple si vous voulez répliquer une table d’un serveur A vers un serveur B, et répliquer une autre table du serveur B vers A).

Pour plus de détails sur ces solutions, voir ce post sur dba.stackexange.com. Pour d’autres types de solutions pour avoir de la haute disponibilité, PostgreSQL a une page sur cela dans leur documentation.

Note : l’expédition des logs entre des serveurs pgsql nécessite qu’ils soient à la même version majeure.

Streaming Réplication

Voir /HowtoPostgreSQL/ReplicationPhysique.

Réplication Logique

Voir /HowtoPostgreSQL/ReplicationLogique.

Slony

Voir /HowtoPostgreSQL/Slony.

Utilisation

Voir /HowtoPostgreSQL/Utilisation.

FAQ

J’ai lancé pg_top mais je n’ai aucun résultat

Vous utilisez une version non compatible avec votre base, essayez avec une version du paquet ptop.

Rendre PostgreSQL moins susceptible d’être killer par l’OMkiller

Voici une unité systemd avec des ajustements sur les variables d’environnement pour rendre PostgreSQL moins killable par l’OMkiller

# systemd service for managing all PostgreSQL clusters on the system. This
# service is actually a systemd target, but we are using a service since
# targets cannot be reloaded.

[Unit]
Description=PostgreSQL RDBMS

[Service]
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_MASTER_OOM_SCORE_ADJ=-1000
Environment=PG_CHILD_OOM_SCORE_ADJ=0
Type=oneshot
ExecStart=/bin/true
ExecReload=/bin/true
RemainAfterExit=on

[Install]
WantedBy=multi-user.target

Activer module pgcrypto

Dans le shell psql :

hostname=# drop extension pgcrypto;
hostname=# create extension pgcrypto;

Pour vérifier :

hostname=# select pg_get_functiondef(to_regproc('gen_random_uuid'));
                    pg_get_functiondef
-----------------------------------------------------------
 CREATE OR REPLACE FUNCTION public.gen_random_uuid()      +
  RETURNS uuid                                            +
  LANGUAGE c                                              +
  PARALLEL SAFE                                           +
 AS '$libdir/pgcrypto', $function$pg_random_uuid$function$+

(1 row)

Activer module postgis

Il faut en premier installé les paquets suivants, exemple sur une instance postgresql 11 :

# apt install postgis postgresql-11-postgis-2.5 postgresql-11-postgis-2.5-scripts

Ensuite il faut faire un CREATE EXTENSION dans la base de données ou l’on veux utilisé postgis.

ATTENTION : il ne faut PAS créer l’extention dans la base de donnée nommée postgres

foo=# CREATE EXTENSION IF NOT EXISTS postgis;

Pour activé le support de rasterisation :

foo=# CREATE EXTENSION IF NOT EXISTS postgis_raster;

Activé topology :

foo=# CREATE EXTENSION IF NOT EXISTS postgis_topology;

Commande COPY

https://docs.postgresql.fr/current/sql-copy.html

Voici comment extraire des lignes d’une table et les réinjecter avec la commande COPY.

On se connecte avec l’utilisateur postgres :

# sudo -u postgres psql

=# COPY (SELECT * FROM TABLE1 WHERE id > 42) TO '/var/lib/postgresql/TABLE1-42.copy';

=# COPY TABLE2  FROM '/var/lib/postgresql/TABLE1-42.copy';

Erreur de dump sur réplica en streaming (Error : canceling statement due to conflict with recovery)

Lors d’un dump avec l’utilitaire pg_dump sur une réplication streaming, il se peux qu’on ai des erreurs du type :

Error : canceling statement due to conflict with recovery ou en français : ERREUR: annulation de la requête à cause d’un conflit avec la restauration

Dans le cas où on utilise un slot physique de réplication, il faut augmenté la valeur de max_standby_streaming_delay et bien vérifier que cette valeurs soit supérieure a celle mis sur le primaire.

Dans le cas où on fait de la réplication physique des WAL, il faut augmenté la valeur de max_standby_archive_delay sur le réplica, et bien vérifier que cette valeurs soit supérieure a celle mis sur le primaire.

Erreur “L’utilisateur « foo » n’a pas de secret SCRAM valide (User”foo” does not have a valid SCRAM verifier)

A partir de PostgreSQL 15 les mots de passe passent de md5 à scram-sha-256 par défaut.

Ainsi si l’on crée un mot de passe, il sera hashé en scram-sha-256.

Si l’on veut changer cela, il faut modifier le paramètre dans postgresql.conf :

password_encryption = md5

Et pour autoriser les connexions avec un mot de passe en md5 il faut faire des changements de ce type dans pg_hba.conf :

-host    all             all             127.0.0.1/32            scram-sha-256
+host    all             all             127.0.0.1/32            md5

Notons bien que md5 dans pg_hba.conf autorise à la fois les mots de passe en md5 ET scram-sha-256.

On voudra souvent faire ça que l’on ré-injecte des utilisateurs avec des mots de passe en md5, notamment issus de PostgreSQL avec des versions inférieures à 15.

Si l’on veut « transformer » un mot de passe en scram-sha-256, il faut retaper le mot de passe :

=# set password_encryption = 'scram-sha-256';
=# alter user foo with password 'PASSWORD';

Notons que l’on peut vérifier le hash d’un mot de passe en faisant :

=# select passwd from pg_shadow where username='foo';
                             passwd
-----------------------------------------------------------
 SCRAM-SHA-256$4096:W6otodlsF4iP***************************

Trouver le nom des bases de données dans le datadir PostgreSQL

Dans le datadir de postgresql dans /var/lib/postgresql/<version>/<instance>/base/ les repertoire des bases ne sont pas identifié par leurs noms, mais par leur OID (Object Identifier Types).

Pour connaitre l’OID de chaque base on peux exécuter cette requête dans le shell postgresql, et regarder la 1ere colonne “oid” :

postgres=# SELECT oid,* from pg_database;

Vérifier si les connexions sont chiffrées

SELECT datname, usename, client_addr, ssl FROM pg_stat_ssl JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid;

https://www.postgresql.org/docs/15/monitoring-stats.html#MONITORING-PG-STAT-SSL-VIEW