# Architecture & fichiers de PostgreSQL > Architecture interne, processus, mémoire, fichiers et installation de PostgreSQL. --- Index LLMS : [llms.txt](/fr/llms.txt) --- ## 1/ Architecture & fichiers de PostgreSQL {#dba2-ch1} **Figure p17-1.** Illustration extraite du PDF\, page 17\. ![Illustration extraite du PDF\, page 17](/images/dba2/manual/p017-01.png) ### 1.1 AU MENU - Rappels sur l’installation - Les processus - Les fichiers Le présent module vise à donner un premier aperçu global du fonctionnement interne de PostgreSQL. Après quelques rappels sur l’installation, nous verrons essentiellement les processus et les fichiers utilisés. ### 1.2 RAPPELS SUR L’INSTALLATION - Plusieurs possibilités - paquets Linux précompilés - outils externes d’installation - code source - Chacun ses avantages et inconvénients - Dalibo recommande fortement les paquets précompilés Nous recommandons très fortement l’utilisation des paquets Linux précompilés. Dans certains cas, il ne sera pas possible de faire autrement que de passer par des outils externes, comme l’installeur d’EntrepriseDB sous Windows. #### 1.2.1 Paquets précompilés - Paquets Debian ou Red Hat suivant la distribution utilisée - Préférence forte pour ceux de la communauté - Installation du paquet - installation des binaires - création de l’utilisateur postgres - initialisation d’une instance (Debian seulement) - lancement du serveur (Debian seulement) - (Red Hat) Script de création de l’instance Debian et Red Hat fournissent des paquets précompilés adaptés à leur distribution. Dalibo recommande d’installer les paquets de la communauté, ces derniers étant bien plus à jour que ceux des distributions. L’installation d’un paquet provoque la création d’un utilisateur système nommé postgres et l’installation des binaires. Suivant les distributions, l’emplacement des binaires change. Habituellement, tout est placé dans `/usr/pgsql-` pour les distributions Red Hat et dans `/usr/lib/postgresql/` pour les distributions Debian. Dans le cas d’une distribution Debian, une instance est immédiatement créée. Par défaut, elle se trouve dans `/var/lib/postgresql//main`. Elle est ensuite démarrée. Dans le cas d’une distribution Red Hat, aucune instance n’est créée automatiquement. Il faudra utiliser un script (dont le nom dépend de la version de la distribution) pour créer l’instance, puis nous pourrons utiliser le script de démarrage pour lancer le serveur. #### 1.2.2 Installons PostgreSQL - Prenons un moment pour - installer PostgreSQL - créer une instance - démarrer l’instance - Pas de configuration spécifique pour l’instant L’annexe ci-dessous décrit l’installation de PostgreSQL sans configuration particulière pour suivre le reste de la formation. ### 1.3 PROCESSUS DE POSTGRESQL **Figure p21-1.** Illustration extraite du PDF\, page 21\. ![Illustration extraite du PDF\, page 21](/images/dba2/manual/p021-01.png) #### 1.3.1 Introduction - PostgreSQL est: @ or) - multiprocessus (et non multithread) - à mémoire partagée - client-serveur L’architecture PostgreSQL est une architecture multiprocessus et non multithread. Cela signifie que chaque processus de PostgreSQL s’exécute dans un contexte mémoire isolé, et que la communication entre ces processus repose sur des mécanismes systèmes inter-processus: sémaphores, zones de mémoire partagée, sockets. Ceci s’oppose à l’architecture multithread, où l’ensemble du moteur s’exécute dans un seul processus, avec plusieurs threads (contextes) d’exécution, où tout est partagé par défaut. Le principal avantage de cette architecture multiprocessus est la stabilité: un processus, en cas de problème, ne corrompt que sa mémoire (ou la mémoire partagée), le plantage d’un processus n’affecte pas directement les autres. Son principal défaut est une allocation statique des ressources de mémoire partagée: elles ne sont pas redimensionnables à chaud. Tous les processus de PostgreSQL accèdent à une zone de « mémoire partagée ». Cette zone contient les informations devant être partagées entre les clients, comme un cache de données, ou des informations sur l’état de chaque session par exemple. PostgreSQL utilise une architecture client-serveur. Nous ne nous connectons à PostgreSQL qu’à travers d’un protocole bien défini, nous n’accédons jamais aux fichiers de données. #### 1.3.2 Processus d’arrière-plan ```text # ps f -e --format=pid,command | grep -E "postgres|postmaster" 96122 /usr/pgsql-17/bin/postmaster -D /var/lib/pgsql/15/data/ 96123 \_ postgres: logger 96127 \_ postgres: io worker 1 96128 \_ postgres: io worker 2 96129 \_ postgres: io worker 3 96130 \_ postgres: checkpointer 96131 \_ postgres: background writer 96132 \_ postgres: walwriter 96133 \_ postgres: autovacuum launcher 96135 \_ postgres: logical replication launcher ``` _(sous Rocky Linux 8)_ Nous constatons que plusieurs processus sont présents dès le démarrage de PostgreSQL. Nous allons les détailler. NB: sur Debian, le postmaster est nommé _postgres_ comme ses processus fils; sous Windows les noms des processus sont par défaut moins verbeux. #### 1.3.3 Processus d’arrière-plan (suite) - Les processus présents au démarrage: - Un processus père: `postmaster` - `background writer` - `checkpointer` - `walwriter` - `autovacuum launcher` - `stats collector` (avant v15) - `io worker` (à partir v18) - `logical replication launcher` - et d’autres selon la configuration et le moment: - dont les _background workers_: parallélisation, extensions… Le `postmaster` est responsable de la supervision des autres processus, ainsi que de la prise en compte des connexions entrantes. Le `background writer` et le `checkpointer` s’occupent d’effectuer les écritures en arrière plan, évitant ainsi aux sessions des utilisateurs de le faire. Le `walwriter` écrit le journal de transactions de façon anticipée, afin de limiter le travail de l’opération `COMMIT`. L’ `autovacuum launcher` pilote les opérations d’« autovacuum ». Avant la version 15, le `stats collector` collecte les statistiques d’activité du serveur. À partir de la version 15, ces informations sont conservées en mémoire jusqu’à l’arrêt du serveur où elles sont stockées sur disque jusqu’au prochain démarrage. À partir de la version 18, les `io workers` s’occupent des lectures asynchrones pour certaines opérations. Leur nombre est configurable. À priori, cette méthode est à conserver. Il reste possible de revenir à l’ancien comportement de PostgreSQL, sans `io worker`, ou d’utiliser l’API `io_uring`. Le `logical replication launcher` est un processus dédié à la réplication logique. Des processus supplémentaires peuvent apparaître, comme un `walsender` dans le cas où la base est le serveur primaire du cluster de réplication, un `logger` si PostgreSQL doit gérer lui-même les fichiers de traces (par défaut sous Red Hat, mais pas sous Debian), ou un `archiver` si l’instance est paramétrée pour générer des archives de ses journaux de transactions. Ces différents processus seront étudiées en détail dans d’autres modules de formation. Aucun de ces processus ne traite de requête pour le compte des utilisateurs. Ce sont des processus d’arrière-plan effectuant des tâches de maintenance. Il existe aussi les _background workers_ (processus d’arrière-plan), lancés par PostgreSQL, mais aussi par des extensions tierces. Par exemple, la parallélisation des requêtes se base sur la création temporaire de _background workers_ épaulant le processus principal de la requête. La réplication logique utilise des _background workers_ à plus longue durée de vie. De nombreuses extensions en utilisent pour des raisons très diverses. Le paramètre `max_worker_processes` régule le nombre de ces _workers_. Ne descendez pas en-dessous du défaut (8). Il faudra même parfois monter plus haut. ### 1.4 PROCESSUS PAR CLIENT (CLIENT BACKEND) - Pour chaque client, nous avons un processus: - créé à la connexion - créé à la connexion - dédié au client… - …et qui dialogue avec lui - détruit à la déconnexion - Un processus gère une requête - peut être aidé par d’autres processus - Le nombre de processus est régi par les paramètres: - `max_connections` (défaut: 100) - connexions réservées - compromis nombre requêtes actives/nombre cœurs/complexité/mémoire Pour chaque nouvelle session à l’instance, le processus `postmaster` crée un processus fils qui s’occupe de gérer cette session. Il y a donc un processus dédié à chaque connexion cliente, et ce processus est détruit à fin de cette connexion. Ce processus dit _backend_ reçoit les ordres SQL, les interprète, exécute les requêtes, trie les données, et enfin retourne les résultats. Dans certains cas, il peut demander le lancement d’autres processus pour l’aider dans l’exécution d’une requête en lecture seule (parallélisme). Le dialogue entre le client et ce processus respecte un protocole réseau bien défini. Le client n’a jamais accès aux données par un autre moyen que par ce protocole. Le nombre maximum de connexions à l’instance simultanées, actives ou non, est limité par le paramètre `max_connections`. Le défaut est 100. Certaines connexions sont réservées. Les administrateurs doivent pouvoir se connecter à l’instance même si la limite est atteinte. Quelques connexions sont donc réservées aux superutilisateurs (paramètre `superuser_reserved_connections`, à 3 par défaut). On peut aussi octroyer le rôle `pg_use_reserved_connections` à certains utilisateurs pour leur garantir l’accès à un nombre de connexions réservées, à définir avec le paramètre `reserved_connections` (vide par défaut), cela à partir de PostreSQL 16. Attention : modifier un de ces paramètres impose un redémarrage complet de l’instance, puisqu’ils ont un impact sur la taille de la mémoire partagée entre les processus PostgreSQL. Il faut donc réfléchir au bon dimensionnement avant la mise en production. La valeur 100 pour `max_connections` est généralement suffisante. Il peut être intéressant de la diminuer pour se permettre de monter `work_mem` et autoriser plus de mémoire de tri. Il est possible de monter `max_connections` pour qu’un plus grand nombre de clients puisse se connecter en même temps. Il s’agit aussi d’arbitrer entre le nombre de requêtes à exécuter à un instant t, le nombre de CPU disponibles, la complexité des requêtes, et le nombre de processus que peut gérer l’OS. Ce dimensionnement est encore compliqué par le parallélisme et la limitation de la bande passante des disques. Intercaler un « pooler » entre les clients et l’instance peut se justifier dans certains cas: - connexions/déconnexions très fréquentes (la connexion a un coût); - centaines, voire milliers, de connexions généralement inactives. Le plus réputé est PgBouncer, mais il est aussi souvent inclus dans des serveurs d’application (par exemple Tomcat). ### 1.5 GESTION DE LA MÉMOIRE **Structure de la mémoire sous PostgreSQL** - Zone de mémoire partagée: - _shared buffers_ surtout - … - Zone de chaque processus - tris en mémoire (`work_mem`) - … La gestion de la mémoire dans PostgreSQL mérite un module de formation à lui tout seul. Pour le moment, bornons-nous à la séparer en deux parties: la mémoire partagée et celle attribuée à chacun des nombreux processus. La mémoire partagée stocke principalement le cache des données de PostgreSQL (_shared buffers_, paramètre `shared_buffers`), et d’autres zones plus petites: cache des journaux de transactions, données de sessions, les verrous, etc. La mémoire propre à chaque processus sert notamment aux tris en mémoire (définie en premier lieu par le paramètre `work_mem`), au cache de tables temporaires, etc. ### 1.6 FICHIERS - Une instance est composée de fichiers: - Répertoire de données - Fichiers de configuration - Fichier PID - Tablespaces - Statistiques - Fichiers de trace Une instance est composée des éléments suivants: #### Le répertoire de données: Il contient les fichiers obligatoires au bon fonctionnement de l’instance : fichiers de données, journaux de transaction…. #### Les fichiers de configuration: Selon la distribution, ils sont stockés dans le répertoire de données(Red Hat et dérivés comme CentOS ou Rocky Linux), ou dans `/etc/postgresql` (Debian et dérivés). #### Un fichier PID: Il permet de savoir si une instance est démarrée ou non, et donc à empêcher un second jeu de processus d’y accéder. Le paramètre `external_pid_file` permet d’indiquer un emplacement où PostgreSQL créera un second fichier de PID, généralement à l’extérieur de son répertoire de données. #### Des tablespaces: Ils sont totalement optionnels. Ce sont des espaces de stockage supplémentaires, stockés habituellement dans d’autres systèmes de fichiers. #### Le fichier de statistiques d’exécution: Généralement dans `pg_stat_tmp/`. #### Les fichiers de trace: Typiquement, des fichiers avec une variante du nom `postgresql.log`, souvent datés. Ils sont par défaut dans le répertoire de l’instance, sous `log/`. Sur Debian, ils sont redirigés vers la sortie d’erreur du système. Ils peuvent être redirigés vers un autre mécanisme du système d’exploitation (syslog sous Unix, journal des événements sous Windows), #### 1.6.1 Répertoire de données **Exemple p29-1.** Exemple extrait du PDF\, page 29\. ```console postgres$ ls $PGDATA base pg_ident.conf pg_stat pg_xact current_logfiles pg_logical pg_stat_tmp postgresql.auto.conf global pg_multixact pg_subtrans postgresql.conf log pg_notify pg_tblspc postmaster.opts pg_commit_ts pg_replslot pg_twophase postmaster.pid pg_dynshmem pg_serial PG_VERSION pg_hba.conf pg_snapshots pg_wal ``` - Une seule instance PostgreSQL doit y accéder! Le répertoire de données est souvent appelé PGDATA, du nom de la variable d’environnement que l’on peut faire pointer vers lui pour simplifier l’utilisation de nombreux utilitaires PostgreSQL. Il est possible aussi de le connaître, une fois connecté à une base de l’instance, en interrogeant le paramètre `data_directory`. **Exemple p29-2.** Exemple extrait du PDF\, page 29\. ```sql SHOW data_directory; ``` **Exemple p29-3.** Exemple extrait du PDF\, page 29\. ```text data_directory ``` **Exemple p29-4.** Exemple extrait du PDF\, page 29\. ```text /var/lib/pgsql/15/data ``` Ce répertoire ne doit être utilisé que par une seule instance (processus) à la fois! PostgreSQL vérifie au démarrage qu’aucune autre instance du même serveur n’utilise les fichiers indiqués, mais cette protection n’est pas absolue, notamment avec des accès depuis des systèmes différents (ou depuis un conteneur comme docker). Faites donc bien attention de ne lancer PostgreSQL qu’une seule fois sur un répertoire de données. Il est recommandé de ne jamais créer ce répertoire PGDATA à la racine d’un point de montage, quelque soit le système d’exploitation et le système de fichiers utilisé. Si un point de montage est dédié à l’utilisation de PostgreSQL, positionnez-le toujours dans un sous-répertoire, voire deux niveaux en dessous du point de montage.(par exemple `//`). Voir à ce propos le chapitre _Use of Secondary File Systems_ dans la documentation officielle: https: //www.postgresql.org/docs/current/creating-cluster.html. Vous pouvez trouver une description de tous les fichiers et répertoires dans la documentation officielle[^1]. [^1]: https://www.postgresql.org/docs/current/static/storage-file-layout.html #### 1.6.2 Fichiers de configuration - `postgresql.conf` (+ fichiers inclus) - `postgresql.auto.conf` - `pg_hba.conf` (+ fichiers inclus (v16)) - `pg_ident.conf` (idem) Les fichiers de configuration sont de simples fichiers textes. Habituellement, ce sont les suivants. `postgresql.conf` contient une liste de paramètres, sous la forme `paramètre=valeur`. Tous les paramètres sont modifiables (et présents) dans ce fichier. Selon la configuration, il peut inclure d’autres fichiers, mais ce n’est pas le cas par défaut. Sous Debian, il est sous `/etc`. `postgresql.auto.conf` stocke les paramètres de configuration fixés en utilisant la commande `ALTER SYSTEM`. Il surcharge donc `postgresql.conf`. Comme pour `postgresql.conf`, sa modification impose de recharger la configuration ou redémarrer l’instance. Il est techniquement possible, mais déconseillé, de le modifier à la main. `postgresql.auto.conf` est toujours dans le répertoire des données, même si `postgresql.conf` est ailleurs. `pg_hba.conf` contient les règles d’authentification à la base selon leur identité, la base, la provenance, etc. `pg_ident.conf` est plus rarement utilisé. Il complète `pg_hba.conf`, par exemple pour rapprocher des utilisateurs système ou propres à PostgreSQL. Ces deux derniers fichiers peuvent eux-mêmes inclure d’autres fichiers (depuis PostgreSQL 16). Leur utilisation est décrite dans notre première formation[^2]. #### 1.6.3 Autres fichiers dans PGDATA - `PG_VERSION`: fichier contenant la version majeure de l’instance - `postmaster.pid` - nombreuses informations sur le processus père - fichier externe possible, paramètre `external_pid_file` - `postmaster.opts` `PG_VERSION` est un fichier. Il contient en texte lisible la version majeure devant être utilisée pour accéder au répertoire (par exemple `15`). On trouve ces fichiers `PG_VERSION` à de nombreux endroits de l’arborescence de PostgreSQL, par exemple dans chaque répertoire de base du répertoire `PGDATA/base/` ou à la racine de chaque tablespace. [^2]: https://dali.bo/f_html Le fichier `postmaster.pid` est créé au démarrage de PostgreSQL. PostgreSQL y indique le PID du processus père sur la première ligne, l’emplacement du répertoire des données sur la deuxième ligne et la date et l’heure du lancement de postmaster sur la troisième ligne ainsi que beaucoup d’autres informations. Par exemple: **Exemple p31-1.** Exemple extrait du PDF\, page 31\. ```text ~$ cat /var/lib/postgresql/15/data/postmaster.pid 7771 /var/lib/postgresql/15/data 1503584802 5432 /tmp localhost 5432001 54919263 ready $ ps -HFC postgres UID PID SZ RSS PSR STIME TIME CMD pos 7771 0 42486 16536 3 16:26 00:00 /usr/local/pgsql/bin/postgres -D /var/lib/postgresql/15/data pos 7773 0 42486 4656 0 16:26 00:00 postgres: checkpointer pos 7774 0 42486 5044 1 16:26 00:00 postgres: background writer pos 7775 0 42486 8224 1 16:26 00:00 postgres: walwriter pos 7776 0 42850 5640 1 16:26 00:00 postgres: autovacuum launcher pos 7777 0 42559 3684 0 16:26 00:00 postgres: logical replication launcher $ ipcs -p |grep 7771 54919263 postgres 7771 10640 $ ipcs | grep 54919263 0x0052e2c1 54919263 postgres 600 56 6 ``` Le processus père de cette instance PostgreSQL a comme PID le 7771. Ce processus a bien réclamé une sémaphore d’identifiant 54919263. Cette sémaphore correspond à des segments de mémoire partagée pour un total de 56 octets. Le répertoire de données se trouve bien dans `/var/lib/postgresql/15/data`. Le fichier `postmaster.pid` est supprimé lors de l’arrêt de PostgreSQL. Cependant, ce n’est pas le cas après un arrêt brutal. Dans ce genre de cas, PostgreSQL détecte le fichier et indique qu’il va malgré tout essayer de se lancer s’il ne trouve pas de processus en cours d’exécution avec ce PID. Un fichier supplémentaire peut être créé ailleurs grâce au paramètre `external_pid_file`, c’est notamment le défaut sous Debian: **Exemple p31-2.** Exemple extrait du PDF\, page 31\. ```ini external_pid_file = '/var/run/postgresql/15-main.pid' ``` Par contre, ce fichier ne contient que le PID du processus père. Quant au fichier `postmaster.opts`, il contient les arguments en ligne de commande correspondant au dernier lancement de PostgreSQL. Il n’est jamais supprimé. Par exemple: **Exemple p31-3.** Exemple extrait du PDF\, page 31\. ```text $ cat $PGDATA/postmaster.opts /usr/local/pgsql/bin/postgres "-D" "/var/lib/postgresql/15/data" ``` #### 1.6.4 Fichiers de données - `base/`: contient les fichiers de données - un sous-répertoire par base de données - `pgsql_tmp`: fichiers temporaires - `global/`: contient les objets globaux à toute l’instance `base/` contient les fichiers de données (tables, index, vues matérialisées, séquences). Il contient un sous-répertoire par base, le nom du répertoire étant l’OID de la base dans `pg_database`. Dans ces répertoires, nous trouvons un ou plusieurs fichiers par objet à stocker. Ils sont nommés ainsi: - Le nom de base du fichier correspond à l’attribut `relfilenode` de l’objet stocké, dans la table `pg_class` (une table, un index…). Il peut changer dans la vie de l’objet (par exemple lors d’un `VACUUM FULL`, un `TRUNCATE` …) - Si le nom est suffixé par un «. » suivi d’un chiffre, il s’agit d’un fichier d’extension de l’objet: un objet est découpé en fichiers de 1 Go maximum. - Si le nom est suffixé par `_fsm`, il s’agit du fichier stockant la _Free Space Map_ (liste des blocs réutilisables). - Si le nom est suffixé par `_vm`, il s’agit du fichier stockant la _Visibility Map_ (liste des blocs intégralement visibles, et donc ne nécessitant pas de traitement par `VACUUM`). Un fichier `base/1247/14356.1` est donc le second segment de l’objet ayant comme `relfilenode` 14356 dans le catalogue `pg_class`, dans la base d’OID 1247 dans la table `pg_database`. Savoir identifier cette correspondance ne sert que dans des cas de récupération de base très endommagée. Vous n’aurez jamais, durant une exploitation normale, besoin d’obtenir cette correspondance. Si, par exemple, vous avez besoin de connaître la taille de la table `test` dans une base, il vous suffit d’exécuter la fonction `pg_table_size()`. En voici un exemple complet: **Exemple p32-1.** Exemple extrait du PDF\, page 32\. ```sql CREATE TABLE test (id integer); INSERT INTO test SELECT generate_series(1, 5000000); SELECT pg_table_size('test'); ``` **Exemple p32-2.** Exemple extrait du PDF\, page 32\. ```text pg_table_size ``` **Exemple p32-3.** Exemple extrait du PDF\, page 32\. ```text 181305344 ``` Depuis la ligne de commande, il existe un utilitaire nommé `oid2name`, dont le but est de faire la liaison entre le nom de fichier et le nom de l’objet PostgreSQL. Il a besoin de se connecter à la base: **Exemple p32-4.** Exemple extrait du PDF\, page 32\. ```text $pwd ``` **Exemple p32-5.** Exemple extrait du PDF\, page 32\. ```text /var/lib/pgsql/15/data/base/16388 ``` **Exemple p32-6.** Exemple extrait du PDF\, page 32\. ```sql $ /usr/pgsql-17/bin/oid2name -f 16477 -d employes From database "employes": ``` **Exemple p32-7.** Exemple extrait du PDF\, page 32\. ```text Filenode Table Name ``` **Exemple p33-1.** Exemple extrait du PDF\, page 33\. ```text 16477 employes_big_pkey ``` Le répertoire `base` peut aussi contenir un répertoire `pgsql_tmp`. Ce répertoire contient des fichiers temporaires utilisés pour stocker les résultats d’un tri ou d’un hachage. La fonction `pg_ls_tmpdir()` affiche le contenu de ce répertoire, ce qui permet de suivre la consommation de fichiers temporaires. Si nous demandons au sein d’une première session: **Exemple p33-2.** Exemple extrait du PDF\, page 33\. ```sql SELECT * FROM generate_series(1,1e9) ORDER BY random() LIMIT 1 ; ``` alors nous pourrons suivre les fichiers temporaires depuis une autre session: **Exemple p33-3.** Exemple extrait du PDF\, page 33\. ```sql SELECT * FROM pg_ls_tmpdir() ; ``` **Tableau p33-1.** Tableau extrait du PDF\, page 33\. ```text name | size | modification -------------------+------------+------------------------ pgsql_tmp12851.16 | 1073741824 | 2020-09-02 15:43:27+02 pgsql_tmp12851.11 | 1073741824 | 2020-09-02 15:42:32+02 pgsql_tmp12851.7 | 1073741824 | 2020-09-02 15:41:49+02 pgsql_tmp12851.5 | 1073741824 | 2020-09-02 15:41:29+02 pgsql_tmp12851.9 | 1073741824 | 2020-09-02 15:42:11+02 pgsql_tmp12851.0 | 1073741824 | 2020-09-02 15:40:36+02 pgsql_tmp12851.14 | 1073741824 | 2020-09-02 15:43:06+02 pgsql_tmp12851.4 | 1073741824 | 2020-09-02 15:41:19+02 pgsql_tmp12851.13 | 1073741824 | 2020-09-02 15:42:54+02 pgsql_tmp12851.3 | 1073741824 | 2020-09-02 15:41:09+02 pgsql_tmp12851.1 | 1073741824 | 2020-09-02 15:40:47+02 pgsql_tmp12851.15 | 1073741824 | 2020-09-02 15:43:17+02 pgsql_tmp12851.2 | 1073741824 | 2020-09-02 15:40:58+02 pgsql_tmp12851.8 | 1073741824 | 2020-09-02 15:42:00+02 pgsql_tmp12851.12 | 1073741824 | 2020-09-02 15:42:43+02 pgsql_tmp12851.10 | 1073741824 | 2020-09-02 15:42:21+02 pgsql_tmp12851.6 | 1073741824 | 2020-09-02 15:41:39+02 pgsql_tmp12851.17 | 546168976 | 2020-09-02 15:43:32+02 ``` Le répertoire `global/` contient notamment les objets globaux à toute une instance, comme la table des bases de données, celle des rôles ou celle des tablespaces ainsi que leurs index. #### 1.6.5 Fichiers liés aux transactions - `pg_wal/`: journaux de transactions - sous-répertoire `archive_status` - nom: _timeline_, journal, segment - ex: `00000002 00000142 000000FF` - créés, initialisés à zéro, recyclés - `pg_xact/`: état des transactions - mais aussi: `pg_commit_ts/`, `pg_multixact/`, `pg_serial/` \ `pg_snapshots/`, `pg_subtrans/`, `pg_twophase/` - **Ces fichiers sont vitaux!** Le répertoire `pg_wal` contient les journaux de transactions. Ces journaux garantissent la durabilité des données dans la base, en traçant toute modification devant être effectuée **AVANT** de l’effectuer réellement en base. Les fichiers contenus dans `pg_wal` ne doivent **jamais** être effacés manuellement. Ces fichiers sont cruciaux au bon fonctionnement de la base. PostgreSQL gère leur création et suppression. S’ils sont toujours présents, c’est que PostgreSQL en a besoin. Par défaut, les fichiers des journaux font tous 16 Mo. Ils ont des noms sur 24 caractères, comme par exemple: **Exemple p34-1.** Exemple extrait du PDF\, page 34\. ```text $ ls -l total 2359320 ``` **Exemple p34-2.** Exemple extrait du PDF\, page 34\. ```text ... -rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001420000007C -rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001420000007D -rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001420000007E -rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001420000007F -rw------- 1 postgres postgres 33554432 Mar 26 16:28 000000020000014300000000 -rw------- 1 postgres postgres 33554432 Mar 26 16:28 000000020000014300000001 -rw------- 1 postgres postgres 33554432 Mar 26 16:28 000000020000014300000002 -rw------- 1 postgres postgres 33554432 Mar 26 16:28 000000020000014300000003 ... -rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001430000001D -rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001430000001E -rw------- 1 postgres postgres 33554432 Mar 26 16:28 00000002000001430000001F -rw------- 1 postgres postgres 33554432 Mar 26 16:28 000000020000014300000020 -rw------- 1 postgres postgres 33554432 Mar 26 16:25 000000020000014300000021 -rw------- 1 postgres postgres 33554432 Mar 26 16:25 000000020000014300000022 -rw------- 1 postgres postgres 33554432 Mar 26 16:25 000000020000014300000023 -rw------- 1 postgres postgres 33554432 Mar 26 16:25 000000020000014300000024 drwx------ 2 postgres postgres 16384 Mar 26 16:28 archive_status ``` La première partie d’un nom de fichier (ici `00000002`) correspond à la _timeline_ (« ligne de temps »), qui ne s’incrémente que lors d’une restauration de sauvegarde ou une bascule entre serveurs primaire et secondaire. La deuxième partie (ici `00000142` puis `00000143`) correspond au numéro de journal à proprement parler, soit un ensemble de fichiers représentant 4 Go. La dernière partie correspond au numéro du segment au sein de ce journal. Selon la taille du segment fixée à l’initialisation, il peut aller de `00000000` à `000000FF` (256 segments de 16 Mo, configuration par défaut, soit 4 Go), à `00000FFF` (4096 segments de 1 Mo), ou à `0000007F` (128 segments de 32 Mo, exemple ci-dessus), etc. Une fois ce maximum atteint, le numéro de journal au centre est incrémenté et les numéros de segments reprennent à zéro. L’ordre d’écriture des journaux est numérique (en hexadécimal), et leur archivage doit suivre cet ordre. Il ne faut pas se fier à la date des fichiers pour le tri: pour des raisons de performances, PostgreSQL recycle généralement les fichiers en les renommant. Dans l’exemple ci-dessus, le dernier journal écrit est `000000020000014300000020` et non `000000020000014300000024`. Toujours pour des raisons de performance liées aux métadata (voir cette explication d’Andres Freund[^3]), les nouveaux journaux sont initialisés par des zéros avant d’être utilisés. Recyclage et initialisation peuvent être désactivés en passant les paramètres `wal_recycle` et `wal_init_zero` à `off`, ce qui n’est conseillé que sur certains systèmes de fichiers comme ZFS ou btrfs. Dans le cadre d’un archivage PITR et/ou d’une réplication par _log shipping_, le sous-répertoire `pg_wal/archive_status` indique l’état des journaux dans le contexte de l’archivage. Les fichiers `.ready` indiquent les journaux restant à archiver (normalement peu nombreux), les `.done` ceux déjà archivés. Il est possible de connaître facilement le contenu de ce répertoire en utilisant la fonction `pg_ls_archive_statusdir()`: **Exemple p35-1.** Exemple extrait du PDF\, page 35\. ```sql SELECT * FROM pg_ls_archive_statusdir() ORDER BY 1 ; ``` **Tableau p35-1.** Tableau extrait du PDF\, page 35\. ```text name | size | modification --------------------------------+------+------------------------ 000000010000000000000067.done | 0 | 2020-09-02 15:52:57+02 000000010000000000000068.done | 0 | 2020-09-02 15:52:57+02 000000010000000000000069.done | 0 | 2020-09-02 15:52:58+02 00000001000000000000006A.ready | 0 | 2020-09-02 15:53:53+02 00000001000000000000006B.ready | 0 | 2020-09-02 15:53:53+02 00000001000000000000006C.ready | 0 | 2020-09-02 15:53:54+02 00000001000000000000006D.ready | 0 | 2020-09-02 15:53:54+02 00000001000000000000006E.ready | 0 | 2020-09-02 15:53:54+02 00000001000000000000006F.ready | 0 | 2020-09-02 15:53:54+02 000000010000000000000070.ready | 0 | 2020-09-02 15:53:55+02 000000010000000000000071.ready | 0 | 2020-09-02 15:53:55+02 ``` Le répertoire `pg_xact` contient l’état de toutes les transactions passées ou présentes sur la base (validées, annulées, en sous-transaction ou en cours), comme nous le détaillerons dans le module « Mécanique du moteur transactionnel ». Les fichiers contenus dans le répertoire `pg_xact` ne doivent **jamais** être effacés. Ils sont cruciaux au bon fonctionnement de la base. [^3]: https://www.postgresql.org/message-id/4xaez4hh3cwkclr77tnkdba6wisueu32vza44iugozai5hklyb%405epf6zpo66aa D’autres répertoires contiennent des fichiers essentiels à la gestion des transactions: - `pg_commit_ts` contient l’horodatage de la validation de chaque transaction; - `pg_multixact` est utilisé dans l’implémentation des verrous partagés (`SELECT … FOR SHARE`); - `pg_serial` est utilisé dans l’implémentation de SSI (`Serializable Snapshot Isolation`); - `pg_snapshots` est utilisé pour stocker les snapshots exportés de transactions; - `pg_subtrans` stocke l’imbrication des transactions lors de sous-transactions (les `SAVEPOINTS`); - `pg_twophase` est utilisé pour l’implémentation du _Two-Phase Commit_, aussi appelé - `transaction préparée`, `2PC`, ou transaction `XA` dans le monde Java par exemple. Si vous mettez la main sur de très vieilles versions, il faut savoir que jusqu’en 9.6, `pg_wal` s’appelait `pg_xlog` et `pg_xact` s’appelait `pg_clog`. Le suffixe `log` ne veut pas dire que le contenu du dossier n’est que de la trace… #### 1.6.6 Fichiers liés à la réplication - `pg_logical/` - `pg_repslot/` `pg_logical` contient des informations sur la réplication logique. `pg_replslot` contient des informations sur les slots de réplications, qui sont un moyen de fiabiliser la réplication physique ou logique. Sans réplication en place, ces répertoires sont quasi-vides. Là encore, il ne faut pas toucher à leur contenu. #### 1.6.7 Répertoire des tablespaces - `pg_tblspc/`: tablespaces - si vraiment nécessaires - liens symboliques ou points de jonction - totalement optionnels Dans PGDATA, le sous-répertoire `pg_tblspc` contient les _tablespaces_, c’est-à-dire des espaces de stockage. #### 1.6.7.1 Sous Linux Sous Linux, ce sont des liens symboliques vers un simple répertoire extérieur à PGDATA. Chaque lien symbolique a comme nom l’OID du tablespace (table système `pg_tablespace`). PostgreSQL y crée un répertoire lié aux versions de PostgreSQL et du catalogue, et y place les fichiers de données. **Exemple p37-1.** Exemple extrait du PDF\, page 37\. ```console postgres=# \db+ ``` **Tableau p37-1.** Tableau extrait du PDF\, page 37\. ```text Liste des tablespaces Nom | Propriétaire | Emplacement | … | Taille |… ------------+--------------+-----------------------+---+---------+- froid | postgres | /mnt/hdd/pg | | 3576 kB | pg_default | postgres | | | 6536 MB | pg_global | postgres | | | 587 kB | ``` **Exemple p37-2.** Exemple extrait du PDF\, page 37\. ```console sudo ls -R /mnt/hdd/pg ``` **Exemple p37-3.** Exemple extrait du PDF\, page 37\. ```text /mnt/hdd/pg: PG_15_202209061 ``` **Exemple p37-4.** Exemple extrait du PDF\, page 37\. ```text /mnt/hdd/pg/PG_15_202209061: 5 ``` **Exemple p37-5.** Exemple extrait du PDF\, page 37\. ```text /mnt/hdd/pg/PG_15_202209061/5: 142532 142532_fsm 142532_vm ``` #### 1.6.7.2 Sous Windows Sous Windows, les liens sont à proprement parler des _Reparse Points_ (ou _Junction Points_): **Exemple p37-6.** Exemple extrait du PDF\, page 37\. ```console postgres=# \db ``` **Exemple p37-7.** Exemple extrait du PDF\, page 37\. ```text Liste des tablespaces Nom | Propriétaire | Emplacement ------------+--------------+------------- pg_default | postgres | pg_global | postgres | tbl1 | postgres | T:\TBL1 ``` **Exemple p37-8.** Exemple extrait du PDF\, page 37\. ```console PS P:\PGDATA13> dir 'pg_tblspc/*' | ?{$_.LinkType} | select FullName,LinkType,Target ``` **Exemple p37-9.** Exemple extrait du PDF\, page 37\. ```text FullName LinkType Target -------- -------- ------ P:\PGDATA13\pg_tblspc\105921 Junction {T:\TBL1} ``` Par défaut, `pg_tblspc/` est vide. N’existent alors que les tablespaces `pg_global` (sous-répertoire `global/` des objets globaux à l’instance) et `pg_default` (soit `base/`). La création d’autres tablespaces est totalement optionnelle. Leur utilité et leur gestion seront abordés plus loin. #### 1.6.8 Fichiers des statistiques d’activité - Statistiques d’activité: - `stats collector` (≤v14) & extensions - `pg_stat_tmp/`: temporaires - `pg_stat/`: définitif `pg_stat_tmp` est, jusqu’en version 15, le répertoire par défaut de stockage des statistiques d’activité de PostgreSQL, comme les entrées-sorties ou les opérations de modifications sur les tables. Ces fichiers pouvant générer une grande quantité d’entrées-sorties, l’emplacement du répertoire peut être modifié avec le paramètre `stats_temp_directory`. Par exemple, Debian place ce paramètre par défaut en `tmpfs`: **Exemple p38-1.** Exemple extrait du PDF\, page 38\. ```sql -- jusque v14 SHOW stats_temp_directory; ``` **Exemple p38-2.** Exemple extrait du PDF\, page 38\. ```text stats_temp_directory ----------------------------------------- /var/run/postgresql/14-main.pg_stat_tmp ``` À l’arrêt, ces statistiques sont sauvegardées dans le répertoire `pg_stat/` (quelle que soit la version de PostgreSQL). PostgreSQL gérant celles-ci en mémoire partagée à partir de la version 15, le collecteur n’existe plus, ni le paramètre `stats_temp_directory`. Mais un répertoire `pg_stat_tmp` (emplacement non configurable) est encore utilisé par des extensions comme `pg_stat_statements` ou `pg_stat_kcache`. #### 1.6.9 Autres répertoires **Figure p38-1.** Illustration extraite du PDF\, page 38\. ![Illustration extraite du PDF\, page 38](/images/dba2/manual/p038-01.png) `pg_dynshmem` est utilisé par les extensions utilisant de la mémoire partagée dynamique. `pg_notify` est utilisé par le mécanisme de gestion de notification de PostgreSQL (`LISTEN` et `NOTIFY`) qui permet de passer des messages de notification entre sessions. #### 1.6.10 Les fichiers de traces (journaux) - Fichiers texte traçant l’activité - Très paramétrables - Gestion des fichiers soit: - par PostgreSQL - délégués au système d’exploitation (_syslog_, _eventlog_) Le paramétrage des journaux est très fin. Leur configuration est le sujet est évoquée dans notre première formation[^4]. Si `logging_collector` est activé, c’est-à-dire que PostgreSQL collecte lui-même ses traces, l’emplacement de ces journaux se paramètre grâce aux paramètres `log_directory`, le répertoire où les stocker, et `log_filename`, le nom de fichier à utiliser, ce nom pouvant utiliser des échappements comme `%d` pour le jour de la date, par exemple. Les droits attribués au fichier sont précisés par le paramètre `log_file_mode`. Un exemple pour `log_filename` avec date et heure serait: **Exemple p39-1.** Exemple extrait du PDF\, page 39\. ```ini log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' ``` La liste des échappements pour le paramètre `log_filename` est disponible dans la page de manuel de la fonction `strftime` sur la plupart des plateformes de type UNIX. [^4]: https://dali.bo/h1_html ### 1.7 RÉSUMÉ **Figure p40-1.** Illustration extraite du PDF\, page 40\. ![Illustration extraite du PDF\, page 40](/images/dba2/manual/p040-01.png) Ce schéma ne soit à présent plus avoir aucun secret pour vous. ### 1.8 CONCLUSION - PostgreSQL est complexe, avec de nombreux composants - Une bonne compréhension de cette architecture est la clé d’une bonne administration. - Pour aller (beaucoup) plus loin: - | **Figure p41-1.** Illustration extraite du PDF\, page 41\. ![Illustration extraite du PDF\, page 41](/images/dba2/manual/p041-01.png) #### 1.8.1 Questions s.) é)| N’hésitez pas, c’est le moment! ### 1.9 QUIZ **https://dali.bo/m1_quiz** ### 1.10 INSTALLATION DE POSTGRESQL DEPUIS LES PAQUETS COMMUNAUTAIRES L’installation est détaillée ici pour Rocky Linux 8 et 9 (similaire à Red Hat et à d’autres variantes comme Oracle Linux et Fedora), et Debian/Ubuntu. Elle ne dure que quelques minutes. #### 1.10.1 Sur Rocky Linux 8, 9 ou 10 **ATTENTION**: Red Hat, CentOS, Rocky Linux fournissent souvent par défaut des versions de PostgreSQL qui ne sont plus supportées. Ne jamais installer les packages `postgresql`, `postgresql-client` et `postgresql-server`! L’utilisation des dépôts du PGDG est fortement conseillée. #### Installation du dépôt communautaire: Les dépôts de la communauté sont sur https://yum.postgresql.org/. Les commandes qui suivent sont inspirées de celles générées par l’assistant sur https://www.postgresql.org/download/linux/redhat/. Il n’y a guère que les informations de version qui diffèrent en fonction de: - la version majeure de PostgreSQL (ici la 18); - la distribution (ici Rocky Linux 9); - l’architecture (ici x86_64, la plus courante). Les commandes sont à lancer sous **root**: - `# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm` **Exemple p43-1.** Exemple extrait du PDF\, page 43\. ```text # dnf -qy module disable postgresql ``` #### Installation de PostgreSQL 18 (client, serveur, librairies, extensions): **Exemple p43-2.** Exemple extrait du PDF\, page 43\. ```text # dnf install -y postgresql18-server postgresql18-contrib ``` Les outils clients et les librairies nécessaires seront automatiquement installés. Une fonctionnalité avancée optionnelle, le JIT (_Just In Time compilation_), nécessite un paquet séparé. - `# dnf install postgresql18-llvmjit` #### Création d’une première instance: C’est optionnel, mais il est conseillé de déclarer `PGSETUP_INITDB_OPTIONS`, notamment pour forcer les traces en anglais. Depuis la version 18, les sommes de contrôle sont automatiquement activées. **Exemple p44-1.** Exemple extrait du PDF\, page 44\. ```text # export PGSETUP_INITDB_OPTIONS='--lc-messages=C' ``` **Exemple p44-2.** Exemple extrait du PDF\, page 44\. ```text # /usr/pgsql-18/bin/postgresql-18-setup initdb # cat /var/lib/pgsql/18/initdb.log ``` Ce dernier fichier permet de vérifier que tout s’est bien passé et doit finir par: **Exemple p44-3.** Exemple extrait du PDF\, page 44\. ```text Success. You can now start the database server using: ``` **Exemple p44-4.** Exemple extrait du PDF\, page 44\. ```text /usr/pgsql-18/bin/pg_ctl -D /var/lib/pgsql/18/data/ -l logfile start ``` #### Chemins: **Tableau p44-1.** Tableau extrait du PDF\, page 44\. |**Objet**|**Chemin**| |---|---| |Binaires|`/usr/pgsql-18/bin`| |Répertoire de l’utilisateur**postgres**|`/var/lib/pgsql`| |`PGDATA` par défaut|`/var/lib/pgsql/18/data`| |Fichiers de configuration|dans `PGDATA/`| |Traces|dans `PGDATA/log`| #### Configuration: Modifier `postgresql.conf` est facultatif pour un premier lancement. #### Commandes d’administration habituelles: Démarrage, arrêt, statut, rechargement à chaud de la configuration, redémarrage: - `# systemctl start postgresql-18` - `# systemctl stop postgresql-18` - `# systemctl status postgresql-18` - `# systemctl reload postgresql-18` - `# systemctl restart postgresql-18` #### Test rapide de bon fonctionnement et connexion à psql: **Exemple p44-5.** Exemple extrait du PDF\, page 44\. ```text # systemctl --all |grep postgres ``` **Exemple p44-6.** Exemple extrait du PDF\, page 44\. ```text # sudo -iu postgres psql ``` #### Démarrage de l’instance au lancement du système d’exploitation: **Exemple p44-7.** Exemple extrait du PDF\, page 44\. ```text # systemctl enable postgresql-18 ``` #### Ouverture du _firewall_ pour le port 5432: Voir si le _firewall_ est actif: **Exemple p44-8.** Exemple extrait du PDF\, page 44\. ```text # systemctl status firewalld ``` Si c’est le cas, autoriser un accès extérieur: - `# firewall-cmd --zone=public --add-port=5432/tcp --permanent` - `# firewall-cmd --reload` - `# firewall-cmd --list-all` (Rappelons que `listen_addresses` doit être également modifié dans `postgresql.conf`.) #### Création d’autres instances: Si des instances de _versions majeures différentes_ doivent être installées, il faut d’abord installer les binaires pour chacune (adapter le numéro dans `dnf install …`) et appeler le script d’installation de chaque version. l’instance par défaut de chaque version vivra dans un sous-répertoire numéroté de `/var/lib/pgsql` automatiquement créé à l’installation. Il faudra juste modifier les ports dans les `postgresql.conf` pour que les instances puissent tourner simultanément. Si plusieurs instances d’une _même version majeure_ (forcément de la même version mineure) doivent cohabiter sur le même serveur, il faut les installer dans des `PGDATA` différents. - Ne pas utiliser de tiret dans le nom d’une instance (problèmes potentiels avec systemd). - Respecter les normes et conventions de l’OS: placer les instances dans un nouveau sousrépertoire de `/var/lib/pgsqsl/18/` (ou l’équivalent pour d’autres versions majeures). Pour créer une seconde instance, nommée par exemple **infocentre**: - Création du fichier service de la deuxième instance: - `# cp /lib/systemd/system/postgresql-18.service \ /etc/systemd/system/postgresql-18-infocentre.service` - Modification de ce dernier fichier avec le nouveau chemin: **Exemple p45-1.** Exemple extrait du PDF\, page 45\. ```ini Environment=PGDATA=/var/lib/pgsql/18/infocentre ``` - Option 1: création d’une nouvelle instance vierge: **Exemple p45-2.** Exemple extrait du PDF\, page 45\. ```text # export PGSETUP_INITDB_OPTIONS='--lc-messages=C' ``` **Exemple p45-3.** Exemple extrait du PDF\, page 45\. ```text # /usr/pgsql-18/bin/postgresql-18-setup initdb postgresql-18-infocentre ``` - Option 2: restauration d’une sauvegarde: la procédure dépend de votre outil. - Adaptation de `/var/lib/pgsql/18/infocentre/postgresql.conf` (port surtout). - Commandes de maintenance de cette instance: - `# systemctl [start|stop|reload|status] postgresql-18-infocentre # systemctl [enable|disable] postgresql-18-infocentre` - Ouvrir le nouveau port dans le firewall au besoin. #### 1.10.2 Sur Debian / Ubuntu L’installation ne varie quasiment pas selon les versions de Debian et Ubuntu. Ce qui suit a été testé sur Debian 10 à 13 et Ubuntu LTS 22.04 et 24.04. Sauf précision, tout est à effectuer en tant qu’utilisateur **root**. Référence: https://apt.postgresql.org/ #### Installation du dépôt communautaire: L’installation des dépôts du PGDG est prévue dans le paquet Debian `postgresql-common`: **Exemple p46-1.** Exemple extrait du PDF\, page 46\. ```text # apt update ``` **Exemple p46-2.** Exemple extrait du PDF\, page 46\. ```text # apt install -y gnupg2 postgresql-common ``` **Exemple p46-3.** Exemple extrait du PDF\, page 46\. ```text # /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh ``` Ce dernier ordre créera le fichier du dépôt `/etc/apt/sources.list.d/pgdg.list` adapté à la distribution en place. #### Installation de PostgreSQL 18: La méthode la plus propre consiste à modifier la configuration par défaut avant l’installation: Dans `/etc/postgresql-common/createcluster.conf`, paramétrer au moins les traces en anglais. Depuis la version 18, les sommes de contrôle sont automatiquement activées. **Exemple p46-4.** Exemple extrait du PDF\, page 46\. ```ini initdb_options = '--lc-messages=C' ``` Puis installer les paquets serveur et clients et leurs dépendances: **Exemple p46-5.** Exemple extrait du PDF\, page 46\. ```text # apt install postgresql-18 postgresql-client-18 ``` La première instance est automatiquement créée, avec un nom (`main` par défaut‘), déclarée comme service à lancer au démarrage du système, puis démarrée. **Exemple p46-6.** Exemple extrait du PDF\, page 46\. ```text $ pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 18 main 5432 online postgres /var/lib/postgresql/18/main ``` - `/var/log/postgresql/postgresql-18-main.log` - `$ systemctl status postgresql@18-main` - `●postgresql@18-main.service - PostgreSQL Cluster 18-main Loaded: loaded (/usr/lib/systemd/system/postgresql@.service; enabled-runtime;` - `preset: enabled)` **Exemple p46-7.** Exemple extrait du PDF\, page 46\. ```text Active: active (running) since Fri 2026-01-30 15:18:43 UTC; 2min 13s ago ``` L’utilisateur système **postgres** peut y accéder immédiatement. #### Chemins: **Tableau p46-1.** Tableau extrait du PDF\, page 46\. |**Objet**|**Chemin**| |---|---| |Binaires|`/usr/lib/postgresql/18/bin/`| **Tableau p47-1.** Tableau extrait du PDF\, page 47\. |**Objet**|**Chemin**| |---|---| |Répertoire de l’utilisateur**postgres**|`/var/lib/postgresql`| |PGDATA de l’instance par défaut|`/var/lib/postgresql/18/main`| |Fichiers de configuration|dans `/etc/postgresql/18/main/`| |Traces|dans `/var/log/postgresql/`| #### Configuration Modifier `postgresql.conf` est facultatif pour un premier essai. #### Démarrage/arrêt de l’instance, rechargement de configuration: Debian fournit ses propres outils, qui demandent en paramètre la version et le nom de l’instance: **Exemple p47-1.** Exemple extrait du PDF\, page 47\. ```text # pg_ctlcluster 18 main [start|stop|reload|status|restart] ``` #### Démarrage de l’instance avec le serveur: C’est en place par défaut, et modifiable dans `/etc/postgresql/18/main/start.conf`. #### Ouverture du firewall: Debian et Ubuntu n’installent pas de firewall par défaut. #### Statut des instances du serveur: **Exemple p47-2.** Exemple extrait du PDF\, page 47\. ```text # pg_lsclusters ``` **Exemple p47-3.** Exemple extrait du PDF\, page 47\. ```text Ver Cluster Port Status Owner Data directory Log file 18 main 5432 online postgres /var/lib/postgresql/18/main ``` `/var/log/postgresql/postgresql-18-main.log` #### Test rapide de bon fonctionnement et connexion à psql: **Exemple p47-4.** Exemple extrait du PDF\, page 47\. ```text # systemctl --all |grep postgres ``` **Exemple p47-5.** Exemple extrait du PDF\, page 47\. ```text # sudo -iu postgres psql ``` #### Destruction d’une instance: **Exemple p47-6.** Exemple extrait du PDF\, page 47\. ```text # pg_dropcluster 18 main ``` #### Création d’autres instances: Ce qui suit est valable pour remplacer l’instance par défaut par une autre, par exemple pour activer les sommes de contrôles (_checksums_) avant la version v18: - optionnellement, `/etc/postgresql-common/createcluster.conf` permet de mettre en place tout d’entrée les messages en anglais, le format des traces, l’emplacement séparé pour les journaux ou les _checksums_ pour une version inférieure à 18: **Exemple p47-7.** Exemple extrait du PDF\, page 47\. ```ini initdb_options = '--data-checksums --lc-messages=C' log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ' waldir = '/var/lib/postgresql/wal/%v/%c/pg_wal' ``` - créer une instance: - `# pg_createcluster 18 infocentre` Il est également possible de préciser certains paramètres du fichier `postgresql.conf`, voire les chemins des fichiers (il est conseillé de conserver les chemins par défaut): - `# pg_createcluster 18 infocentre \` - `--port=12345 \` - `--datadir=/PGDATA/18/infocentre \` - `--pgoption shared_buffers='8GB' --pgoption work_mem='50MB' \` - `---data-checksums --waldir=/ssd/postgresql/18/infocentre/journaux` - adapter au besoin `/etc/postgresql/18/infocentre/postgresql.conf`; - démarrage: - `# pg_ctlcluster 18 infocentre start` #### 1.10.3 Accès à l’instance depuis le serveur même (toutes distributions) Par défaut, l’instance n’est accessible que par l’utilisateur système **postgres**, qui n’a pas de mot de passe. Un détour par `sudo` est nécessaire: **Exemple p49-1.** Exemple extrait du PDF\, page 49\. ```console $ sudo -iu postgres psql psql (18.1) Type "help" for help. postgres=# ``` Ce qui suit permet la connexion directement depuis un utilisateur du système: Pour des tests (pas en production!), il suffit de passer à `trust` le type de la connexion en local dans le `pg_hba.conf`: **Exemple p49-2.** Exemple extrait du PDF\, page 49\. ```ini local all postgres trust ``` La connexion en tant qu’utilisateur `postgres` (ou tout autre) n’est alors plus sécurisée: **Exemple p49-3.** Exemple extrait du PDF\, page 49\. ```console dalibo:~$ psql -U postgres psql (18.1) Type "help" for help. postgres=# ``` Une authentification par mot de passe est plus sécurisée: - dans `pg_hba.conf`, paramétrer une authentification par mot de passe pour les accès depuis `localhost` (déjà en place sous Debian): **Exemple p49-4.** Exemple extrait du PDF\, page 49\. ```ini # IPv4 local connections: host all all 127.0.0.1/32 scram-sha-256 # IPv6 local connections: host all all ::1/128 scram-sha-256 ``` (Ne pas oublier de recharger la configuration en cas de modification.) - ajouter un mot de passe à l’utilisateur `postgres` de l’instance: **Exemple p49-5.** Exemple extrait du PDF\, page 49\. ```console dalibo:~$ sudo -iu postgres psql psql (18.1) Type "help" for help. postgres=# \password Enter new password for user "postgres": Enter it again: postgres=# quit ``` **Exemple p49-6.** Exemple extrait du PDF\, page 49\. ```console dalibo:~$ psql -h localhost -U postgres Password for user postgres: psql (18.1) Type "help" for help. postgres=# ``` - Pour se connecter sans taper le mot de passe à une instance, un fichier `.pgpass` dans le répertoire personnel doit contenir les informations sur cette connexion: **Exemple p50-1.** Exemple extrait du PDF\, page 50\. ```text localhost:5432:*:postgres:motdepassetrèslong ``` Ce fichier doit être protégé des autres utilisateurs: - `$ chmod 600 ~/.pgpass` - Pour n’avoir à taper que `psql`, on peut définir ces variables d’environnement dans la session voire dans `~/.bashrc`: **Exemple p50-2.** Exemple extrait du PDF\, page 50\. ```console export PGUSER=postgres export PGDATABASE=postgres export PGHOST=localhost ``` #### Rappels: - en cas de problème, consulter les traces (dans `/var/lib/pgsql/18/data/log` ou `/var/log/postgresql/`); - toute modification de `pg_hba.conf` ou `postgresql.conf` impliquant de recharger la configuration peut être réalisée par une de ces trois méthodes en fonction du système: **Exemple p50-3.** Exemple extrait du PDF\, page 50\. ```text root:~# systemctl reload postgresql-18 ``` **Exemple p50-4.** Exemple extrait du PDF\, page 50\. ```text root:~# pg_ctlcluster 18 main reload ``` **Exemple p50-5.** Exemple extrait du PDF\, page 50\. ```text postgres:~$ psql -c 'SELECT pg_reload_conf()' ``` ### 1.11 TRAVAUX PRATIQUES La version en ligne des solutions de ces TP est disponible sur https://dali.bo/m1_solutions. #### 1.11.1 Processus **But**: voir quelques processus de PostgreSQL Si ce n’est pas déjà fait, démarrer l’instance PostgreSQL. Lister les processus du serveur PostgreSQL. Qu’observe-t-on? Se connecter à l’instance PostgreSQL. Dans un autre terminal lister de nouveau les processus du serveur PostgreSQL. Qu’observe-t-on? Créer une nouvelle base de données nommée **b0**. Se connecter à la base de données **b0** et créer une table `t1` avec une colonne `id` de type `integer`. Insérer 10 millions de lignes dans la table `t1` avec: **Exemple p51-1.** Exemple extrait du PDF\, page 51\. ```sql INSERT INTO t1 SELECT generate_series(1, 10000000) ; ``` Dans un autre terminal lister de nouveau les processus du serveur PostgreSQL. Qu’observe-t-on? Configurer la valeur du paramètre `max_connections` à `15`. Redémarrer l’instance PostgreSQL. Vérifier que la modification de la valeur du paramètre `max_connections` a été prise en compte. Se connecter 15 fois à l’instance PostgreSQL sans fermer les sessions, par exemple en lançant plusieurs fois: **Exemple p52-1.** Exemple extrait du PDF\, page 52\. ```console psql -c 'SELECT pg_sleep(1000)' & ``` Se connecter une seizième fois à l’instance PostgreSQL. Qu’observe-t-on? Configurer la valeur du paramètre `max_connections` à sa valeur initiale. #### 1.11.2 Fichiers **But**: voir les fichiers de PostgreSQL Aller dans le répertoire des données de l’instance PostgreSQL. Lister les fichiers. Aller dans le répertoire `base`. Lister les fichiers. À quelle base est lié chaque répertoire présent dans le répertoire `base`? (Voir l’oid de la base dans `pg_database` ou l’utilitaire en ligne de commande `oid2name`) Créer une nouvelle base de données nommée **b1**. Qu’observe-t-on dans le répertoire `base`? Se connecter à la base de données **b1**. Créer une table `t1` avec une colonne `id` de type `integer`. Récupérer le chemin vers le fichier correspondant à la table `t1` (il existe une fonction `pg_relation_filepath`). Regarder la taille du fichier correspondant à la table `t1`. Pourquoi est-il vide? Insérer une ligne dans la table `t1`. Quelle taille fait le fichier de la table `t1`? Insérer 500 lignes dans la table `t1` avec `generate_series`. Quelle taille fait le fichier de la table `t1`? Pourquoi cette taille pour simplement 501 entiers de 4 octets chacun? ### 1.12 TRAVAUX PRATIQUES (SOLUTIONS) #### 1.12.1 Processus Si ce n’est pas déjà fait, démarrer l’instance PostgreSQL. #### Sous Rocky Linux, CentOS ou Red Hat en tant qu’utilisateur **root**: **Exemple p54-1.** Exemple extrait du PDF\, page 54\. ```text # systemctl start postgresql-15 ``` Lister les processus du serveur PostgreSQL. Qu’observe-t-on? #### En tant qu’utilisateur **postgres**: **Exemple p54-2.** Exemple extrait du PDF\, page 54\. ```text $ ps -o pid,cmd fx PID CMD 27886 -bash 28666 \_ ps -o pid,cmd fx 27814 /usr/pgsql-17/bin/postmaster -D /var/lib/pgsql/15/data/ 27815 \_ postgres: logger 27816 \_ postgres: checkpointer 27817 \_ postgres: background writer 27819 \_ postgres: walwriter 27820 \_ postgres: autovacuum launcher 27821 \_ postgres: logical replication launcher ``` Se connecter à l’instance PostgreSQL. **Exemple p54-3.** Exemple extrait du PDF\, page 54\. ```console $ psql postgres psql (15.0) Type "help" for help. ``` ```console postgres=# ``` Dans un autre terminal lister de nouveau les processus du serveur PostgreSQL. Qu’observe-t-on? **Exemple p54-4.** Exemple extrait du PDF\, page 54\. ```text $ ps -o pid,cmd fx PID CMD 28746 -bash 28779 \_ psql 27886 -bash 28781 \_ ps -o pid,cmd fx 27814 /usr/pgsql-17/bin/postmaster -D /var/lib/pgsql/15/data/ 27815 \_ postgres: logger 27816 \_ postgres: checkpointer 27817 \_ postgres: background writer 27819 \_ postgres: walwriter 27820 \_ postgres: autovacuum launcher 27821 \_ postgres: logical replication launcher 28780 \_ postgres: postgres postgres [local] idle ``` Il y a un nouveau processus (ici PID 28780) qui va gérer l’exécution des requêtes du client psql. Créer une nouvelle base de données nommée **b0**. Depuis le shell, en tant que **postgres**: **Exemple p55-1.** Exemple extrait du PDF\, page 55\. ```text $ createdb b0 ``` Alternativement, depuis la session déjà ouverte dans `psql`: ```sql CREATE DATABASE b0; ``` Se connecter à la base de données **b0** et créer une table `t1` avec une colonne `id` de type `integer`. Pour se connecter depuis le shell: **Exemple p55-2.** Exemple extrait du PDF\, page 55\. ```console psqlb0 ``` ou depuis la session `psql`: **Exemple p55-3.** Exemple extrait du PDF\, page 55\. ```text \cb0 ``` Création de la table: **Exemple p55-4.** Exemple extrait du PDF\, page 55\. ```sql CREATE TABLE t1 (id integer); ``` Insérer 10 millions de lignes dans la table `t1` avec: **Exemple p55-5.** Exemple extrait du PDF\, page 55\. ```sql INSERT INTO t1 SELECT generate_series(1, 10000000) ; ``` **Exemple p55-6.** Exemple extrait du PDF\, page 55\. ```sql INSERT INTO t1 SELECT generate_series(1, 10000000) ; ``` ```text INSERT 0 10000000 ``` Dans un autre terminal lister de nouveau les processus du serveur PostgreSQL. Qu’observe-t-on? **Exemple p55-7.** Exemple extrait du PDF\, page 55\. ```text $ ps -o pid,cmd fx PID CMD 28746 -bash 28779 \_ psql 27886 -bash 28781 \_ ps -o pid,cmd fx 27814 /usr/pgsql-17/bin/postmaster -D /var/lib/pgsql/15/data/ 27815 \_ postgres: logger 27816 \_ postgres: checkpointer 27817 \_ postgres: background writer 27819 \_ postgres: walwriter 27820 \_ postgres: autovacuum launcher 27821 \_ postgres: logical replication launcher 28780 \_ postgres: postgres postgres [local] INSERT ``` Le processus serveur exécute l’ `INSERT`, ce qui se voit au niveau du nom du processus. Seul est affiché le dernier ordre SQL (_ie_ le mot `INSERT` et non pas la requête complète). Configurer la valeur du paramètre `max_connections` à `15`. La première méthode est d’ouvrir le fichier de configuration `postgresql.conf` et de modifier la valeur du paramètre `max_connections`: **Exemple p56-1.** Exemple extrait du PDF\, page 56\. ```ini max_connections = 15 ``` La seconde méthode se fait directement depuis psql en tant que superutilisateur: **Exemple p56-2.** Exemple extrait du PDF\, page 56\. ```sql ALTER SYSTEM SET max_connections TO 15 ; ``` ce dernier ordre fera apparaître une ligne dans le fichier `/var/lib/pgsql/15/data/postgresql.auto.conf`. Dans les deux cas, la prise en compte n’est pas automatique. Pour ce paramètre, il faut redémarrer l’instance PostgreSQL. Redémarrer l’instance PostgreSQL. En tant qu’utilisateur **root**: **Exemple p56-3.** Exemple extrait du PDF\, page 56\. ```text # systemctl restart postgresql-15 ``` Vérifier que la modification de la valeur du paramètre `max_connections` a été prise en compte. **Exemple p56-4.** Exemple extrait du PDF\, page 56\. ```console postgres=# SHOW max_connections ; ``` **Exemple p56-5.** Exemple extrait du PDF\, page 56\. ```text max_connections ``` **Exemple p56-6.** Exemple extrait du PDF\, page 56\. ```text 15 ``` Se connecter 15 fois à l’instance PostgreSQL sans fermer les sessions, par exemple en lançant plusieurs fois: **Exemple p56-7.** Exemple extrait du PDF\, page 56\. ```console psql -c 'SELECT pg_sleep(1000)' & ``` Il est possible de le faire manuellement ou de l’automatiser avec ce petit script shell: **Exemple p56-8.** Exemple extrait du PDF\, page 56\. ```text $ for i in $(seq 1 15); do psql -c "SELECT pg_sleep(1000);" postgres & done [1] 998 [2] 999 ... [15] 1012 ``` Se connecter une seizième fois à l’instance PostgreSQL. Qu’observe-t-on? **Exemple p56-9.** Exemple extrait du PDF\, page 56\. ```console $ psql postgres psql: FATAL: sorry, too many clients already ``` Il est impossible de se connecter une fois que le nombre de connexions a atteint sa limite configurée avec `max_connections`. Il faut donc attendre que les utilisateurs se déconnectent pour accéder de nouveau au serveur. Configurer la valeur du paramètre `max_connections` à sa valeur initiale. Si le fichier de configuration `postgresql.conf` avait été modifié, restaurer la valeur du paramètre `max_connections` à 100. Si `ALTER SYSTEM` a été utilisée, il faudrait pouvoir entrer dans psql: **Exemple p57-1.** Exemple extrait du PDF\, page 57\. ```sql ALTER SYSTEM RESET max_connections ; ``` Mais cela ne fonctionne pas si toutes les connexions réservées à l’utilisateur ont été consommées (paramètre `superuser_reserved_connections`, par défaut à 3). Dans ce cas, il n’y a plus qu’à aller dans le fichier de configuration `/var/lib/pgsql/15/data/postgresql.auto.conf`, pour supprimer la ligne avec le paramètre `max_connections` avant de redémarrer l’instance PostgreSQL. Il est déconseillé de modifier `postgresql.auto.conf` à la main, mais pour le TP, nous nous permettons quelques libertés. Toutefois si l’instance est démarrée et qu’il est encore possible de s’y connecter, le plus propre est ceci: **Exemple p57-2.** Exemple extrait du PDF\, page 57\. ```sql ALTER SYSTEM RESET max_connections ; ``` Puis redémarrer PostgreSQL: toutes les connexions en cours vont être coupées. **Exemple p57-3.** Exemple extrait du PDF\, page 57\. ```text # systemctl restart postgresql-15 FATAL: terminating connection due to administrator command server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. [...] FATAL: terminating connection due to administrator command server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost ``` Il est à présent possible de se reconnecter. Vérifier que cela a été pris en compte: **Exemple p57-4.** Exemple extrait du PDF\, page 57\. ```console postgres=# SHOW max_connections ; ``` **Exemple p57-5.** Exemple extrait du PDF\, page 57\. ```text max_connections ``` **Exemple p57-6.** Exemple extrait du PDF\, page 57\. ```text 100 ``` #### 1.12.2 Fichiers Aller dans le répertoire des données de l’instance PostgreSQL. Lister les fichiers. #### En tant qu’utilisateur système **postgres**: **Exemple p58-1.** Exemple extrait du PDF\, page 58\. ```console echo $PGDATA ``` **Exemple p58-2.** Exemple extrait du PDF\, page 58\. ```text /var/lib/pgsql/15/data ``` ```shell $ cd $PGDATA ``` **Exemple p58-3.** Exemple extrait du PDF\, page 58\. ```text $ ls -al total 68 drwx------. 7 postgres postgres 59 Nov 4 09:55 base -rw-------. 1 postgres postgres 30 Nov 4 10:38 current_logfiles drwx------. 2 postgres postgres 4096 Nov 4 10:38 global drwx------. 2 postgres postgres 58 Nov 4 07:58 log drwx------. 2 postgres postgres 6 Nov 3 14:11 pg_commit_ts drwx------. 2 postgres postgres 6 Nov 3 14:11 pg_dynshmem -rw-------. 1 postgres postgres 4658 Nov 4 09:50 pg_hba.conf -rw-------. 1 postgres postgres 1685 Nov 3 14:16 pg_ident.conf drwx------. 4 postgres postgres 68 Nov 4 10:38 pg_logical drwx------. 4 postgres postgres 36 Nov 3 14:11 pg_multixact drwx------. 2 postgres postgres 6 Nov 3 14:11 pg_notify drwx------. 2 postgres postgres 6 Nov 3 14:11 pg_replslot drwx------. 2 postgres postgres 6 Nov 3 14:11 pg_serial drwx------. 2 postgres postgres 6 Nov 3 14:11 pg_snapshots drwx------. 2 postgres postgres 6 Nov 4 10:38 pg_stat drwx------. 2 postgres postgres 35 Nov 4 10:38 pg_stat_tmp drwx------. 2 postgres postgres 18 Nov 3 14:11 pg_subtrans drwx------. 2 postgres postgres 6 Nov 3 14:11 pg_tblspc drwx------. 2 postgres postgres 6 Nov 3 14:11 pg_twophase -rw-------. 1 postgres postgres 3 Nov 3 14:11 PG_VERSION drwx------. 3 postgres postgres 92 Nov 4 09:55 pg_wal drwx------. 2 postgres postgres 18 Nov 3 14:11 pg_xact -rw-------. 1 postgres postgres 88 Nov 3 14:11 postgresql.auto.conf -rw-------. 1 postgres postgres 29475 Nov 4 09:36 postgresql.conf -rw-------. 1 postgres postgres 58 Nov 4 10:38 postmaster.opts -rw-------. 1 postgres postgres 104 Nov 4 10:38 postmaster.pid ``` Aller dans le répertoire `base`. Lister les fichiers. **Exemple p58-4.** Exemple extrait du PDF\, page 58\. ```text $cdbase ``` **Exemple p58-5.** Exemple extrait du PDF\, page 58\. ```text $ ls -al total 60 drwx------ 8 postgres postgres 78 Nov 4 16:21 . drwx------ 20 postgres postgres 4096 Nov 4 15:33 .. drwx------. 2 postgres postgres 8192 Nov 4 10:38 1 drwx------. 2 postgres postgres 8192 Nov 4 09:50 16404 drwx------. 2 postgres postgres 8192 Nov 3 14:11 4 drwx------. 2 postgres postgres 8192 Nov 4 10:39 5 drwx------ 2 postgres postgres 6 Nov 3 15:58 pgsql_tmp ``` À quelle base est lié chaque répertoire présent dans le répertoire `base`? (Voir l’oid de la base dans `pg_database` ou l’utilitaire en ligne de commande `oid2name`) Chaque répertoire correspond à une base de données. Le numéro indiqué est un identifiant système (OID). Il existe deux moyens pour récupérer cette information: - directement dans le catalogue système `pg_database`: **Exemple p59-1.** Exemple extrait du PDF\, page 59\. ```text $ psql postgres ``` **Exemple p59-2.** Exemple extrait du PDF\, page 59\. ```console psql (15.0) Type "help" for help. ``` **Exemple p59-3.** Exemple extrait du PDF\, page 59\. ```console postgres=# SELECT oid, datname FROM pg_database ORDER BY oid::text; ``` **Exemple p59-4.** Exemple extrait du PDF\, page 59\. ```text oid | datname -------+----------- 1 | template1 16404 | b0 4 | template0 5 | postgres ``` - avec l’outil `oid2name` (à installer au besoin via le paquet `postgresql15-contrib`): **Exemple p59-5.** Exemple extrait du PDF\, page 59\. ```text $ /usr/pgsql-17/bin/oid2name All databases: ``` **Exemple p59-6.** Exemple extrait du PDF\, page 59\. ```text Oid Database Name Tablespace ---------------------------------- 16404 b0 pg_default 5 postgres pg_default 4 template0 pg_default 1 template1 pg_default ``` Donc ici, le répertoire `1` correspond à la base `template1`, et le répertoire `5` à la base `postgres` (ces nombres peuvent changer suivant l’installation). Créer une nouvelle base de données nommée **b1**. Qu’observe-t-on dans le répertoire `base`? **Exemple p59-7.** Exemple extrait du PDF\, page 59\. ```text $ createdb b1 ``` **Exemple p59-8.** Exemple extrait du PDF\, page 59\. ```text $ ls -al total 60 drwx------ 8 postgres postgres 78 Nov 4 16:21 . drwx------ 20 postgres postgres 4096 Nov 4 15:33 .. drwx------. 2 postgres postgres 8192 Nov 4 10:38 1 drwx------. 2 postgres postgres 8192 Nov 4 09:50 16404 drwx------. 2 postgres postgres 8192 Nov 4 09:55 16405 drwx------. 2 postgres postgres 8192 Nov 3 14:11 4 drwx------. 2 postgres postgres 8192 Nov 4 10:39 5 drwx------ 2 postgres postgres 6 Nov 3 15:58 pgsql_tmp ``` Un nouveau sous-répertoire est apparu, nommé `16405`. Il correspond bien à la base `b1` d’après `oid2name`. Se connecter à la base de données **b1**. Créer une table `t1` avec une colonne `id` de type `integer`. **Exemple p60-1.** Exemple extrait du PDF\, page 60\. ```console $ psql b1 psql (15.0) Type "help" for help. ``` **Exemple p60-2.** Exemple extrait du PDF\, page 60\. ```console b1=# CREATE TABLE t1(id integer); ``` ```text CREATE TABLE ``` Récupérer le chemin vers le fichier correspondant à la table `t1` (il existe une fonction `pg_relation_filepath`). La fonction a pour définition: **Exemple p60-3.** Exemple extrait du PDF\, page 60\. ```console b1=# \df pg_relation_filepath ``` **Exemple p60-4.** Exemple extrait du PDF\, page 60\. ```text List of functions Schema | Name | Result data type | Argument data types | Type ------------+----------------------+------------------+---------------------+------ pg_catalog | pg_relation_filepath | text | regclass | func ``` L’argument `regclass` peut être l’OID de la table, ou son nom. L’emplacement du fichier sur le disque est donc: **Exemple p60-5.** Exemple extrait du PDF\, page 60\. ```console b1=# SELECT current_setting('data_directory') || '/' || pg_relation_filepath('t1') AS chemin; ``` **Exemple p60-6.** Exemple extrait du PDF\, page 60\. ```text chemin ``` **Exemple p60-7.** Exemple extrait du PDF\, page 60\. ```text /var/lib/pgsql/15/data/base/16405/16406 ``` Regarder la taille du fichier correspondant à la table `t1`. Pourquoi est-il vide? **Exemple p60-8.** Exemple extrait du PDF\, page 60\. ```text $ ls -l /var/lib/pgsql/15/data/base/16393/16398 ``` **Exemple p60-9.** Exemple extrait du PDF\, page 60\. ```text -rw-------. 1 postgres postgres 0 Nov 4 10:42 ``` - `/var/lib/pgsql/15/data/base/16405/16406` La table vient d’être créée. Aucune donnée n’a encore été ajoutée. Les métadonnées se trouvent dans d’autres tables (des catalogues systèmes). Donc il est logique que le fichier soit vide. Insérer une ligne dans la table `t1`. Quelle taille fait le fichier de la table `t1`? **Exemple p60-10.** Exemple extrait du PDF\, page 60\. ```console b1=# INSERT INTO t1 VALUES (1); INSERT 0 1 ``` `$ ls -l /var/lib/pgsql/15/data/base/16393/16398 -rw-------. 1 postgres postgres 8192 Nov 4 12:40` `/var/lib/pgsql/15/data/base/16405/16406` Il fait à présent 8 ko. En fait, PostgreSQL travaille par blocs de 8 ko. Si une ligne ne peut être placée dans un espace libre d’un bloc existant, un bloc entier est ajouté à la table. Vous pouvez consulter le fichier avec la commande `hexdump -x ` (faites un `CHECKPOINT` avant pour être sûr qu’il soit écrit sur le disque). Insérer 500 lignes dans la table `t1` avec `generate_series`. Quelle taille fait le fichier de la table `t1`? **Exemple p61-1.** Exemple extrait du PDF\, page 61\. ```console b1=# INSERT INTO t1 SELECT generate_series(1, 500); INSERT 0 500 ``` **Exemple p61-2.** Exemple extrait du PDF\, page 61\. ```text $ ls -l /var/lib/pgsql/15/data/base/16393/16398 ``` - `-rw-------. 1 postgres postgres 24576 Nov 4 12:41` - `/var/lib/pgsql/15/data/base/16405/16406` Le fichier fait maintenant 24 ko, soit 3 blocs de 8 ko. Pourquoi cette taille pour simplement 501 entiers de 4 octets chacun? Nous avons enregistré 501 entiers dans la table. Un entier de type `int4` prend 4 octets. Donc nous avons 2004 octets de données utilisateurs. Et pourtant, nous arrivons à un fichier de 24 ko. En fait, PostgreSQL enregistre aussi dans chaque bloc des informations systèmes en plus des données utilisateurs. Chaque bloc contient un en-tête, des pointeurs, et l’ensemble des lignes du bloc. Chaque ligne contient les colonnes utilisateurs mais aussi des colonnes système. La requête suivante permet d’en savoir plus sur les colonnes présentes dans la table: **Exemple p61-3.** Exemple extrait du PDF\, page 61\. ```console b1=# SELECT CASE WHEN attnum<0 THEN 'systeme' ELSE 'utilisateur' END AS type, attname, attnum, typname, typlen, sum(typlen) OVER (PARTITION BY attnum<0) AS longueur_tot FROM pg_attribute a JOIN pg_type t ON t.oid=a.atttypid WHERE attrelid ='t1'::regclass ORDER BY attnum; ``` **Tableau p61-1.** Tableau extrait du PDF\, page 61\. ```text type | attname | attnum | typname | typlen | longueur_tot -------------+----------+--------+---------+--------+-------------- systeme | tableoid | -6 | oid | 4 | 26 systeme | cmax | -5 | cid | 4 | 26 systeme | xmax | -4 | xid | 4 | 26 systeme | cmin | -3 | cid | 4 | 26 systeme | xmin | -2 | xid | 4 | 26 systeme | ctid | -1 | tid | 6 | 26 utilisateur | id | 1 | int4 | 4 | 4 ``` Vous pouvez voir ces colonnes système en les appelant explicitement: **Exemple p61-4.** Exemple extrait du PDF\, page 61\. ```sql SELECT cmin, cmax, xmin, xmax, ctid, * FROM t1 ; ``` L’en-tête de chaque ligne pèse 26 octets dans le cas général. Dans notre cas très particulier avec une seule petite colonne, c’est très défavorable mais ce n’est généralement pas le cas. Avec 501 lignes de 26+4 octets, nous obtenons 15 ko. Chaque bloc possède quelques informations de maintenance: nous dépassons alors 16 ko, ce qui explique pourquoi nous en sommes à 24 ko (3 blocs). --- _Source : [manuel complet PDF](/downloads/dalibo/manuals/dba2-handout.pdf), révision 26.09, pages PDF 17-62. Les travaux pratiques et leurs solutions proviennent exclusivement de la version complète._ Pour poursuivre la lecture, voir [la mécanique du moteur transactionnel et le MVCC](/fr/dba2/04-mecanique-mvcc/#dba2-ch4).