PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 11.22 » Langage SQL » Fonctions et opérateurs » Fonctions d'administration système

9.26. Fonctions d'administration système

Les fonctions décrites dans cette section sont utilisées pour contrôler et superviser une installation PostgreSQL.

9.26.1. Fonctions pour le paramétrage

Le Tableau 9.77 affiche les fonctions disponibles pour consulter et modifier les paramètres de configuration en exécution.

Tableau 9.77. Fonctions agissant sur les paramètres de configuration

NomType de retourDescription
current_setting (nom_paramètre [, missing_ok ]) textvaleur courante du paramètre
set_config (nom_paramètre, nouvelle_valeur, est_local) textconfigure le paramètre et renvoie la nouvelle valeur

La fonction current_setting renvoie la valeur courante du paramètre nom_paramètre. Elle correspond à la commande SQL SHOW. Par exemple :

SELECT current_setting('datestyle');

 current_setting
-----------------
ISO, MDY
(1 row)

Si le paramètre setting_name n'existe pas, current_setting renvoie une erreur, sauf si missing_ok vaut true.

set_config positionne le paramètre nom_paramètre à nouvelle_valeur. Si est_local vaut true, la nouvelle valeur s'applique uniquement à la transaction en cours. Si la nouvelle valeur doit s'appliquer à la session en cours, on utilise false. La fonction correspond à la commande SQL SET. Par exemple :

SELECT set_config('log_statement_stats', 'off', false);

 set_config
------------
off
(1 row)

9.26.2. Fonctions d'envoi de signal du serveur

Les fonctions présentées dans le Tableau 9.78 envoient des signaux de contrôle aux autres processus serveur. L'utilisation de ces fonctions est restreinte aux superutilisateurs par défaut, mais un accès peut être fourni à d'autres utilisateurs avec une commande GRANT, sauf dans certains cas spécifiquement notés.

Tableau 9.78. Fonctions d'envoi de signal au serveur

NomType de retourDescription
pg_cancel_backend (pid int) booleanAnnule la requête courante d'un processus serveur. Ceci est également autorisé si le rôle appelant est membre du rôle possédant le processus serveur annulé ou si le rôle appelant s'est vu donné le droit pg_signal_backend. Cependant, seuls les superutilisateurs peuvent annuler des processus serveurs possédés par des superutilisateurs.
pg_reload_conf() booleanImpose le rechargement des fichiers de configuration par les processus serveur
pg_rotate_logfile() booleanImpose une rotation du journal des traces du serveur
pg_terminate_backend(pid int) booleanTermine un processus serveur. Ceci est également autorisé si le rôle appelant est membre du rôle possédant le processus serveur terminé ou si le rôle appelant s'est vu donné le droit pg_signal_backend. Cependant, seuls les superutilisateurs peuvent terminer des processus serveurs possédés par des superutilisateurs.

Ces fonctions renvoient true en cas de succès, false en cas d'échec.

pg_cancel_backend et pg_terminate_backend envoie un signal (respectivement SIGINT ou SIGTERM) au processus serveur identifié par l'ID du processus. L'identifiant du processus serveur actif peut être trouvé dans la colonne pid dans la vue pg_stat_activity ou en listant les processus postgres sur le serveur avec ps sur Unix ou le Gestionnaire des tâches sur Windows. Le rôle d'un processus serveur actif est récupérable à partir de la colonne usename de la vue pg_stat_activity.

pg_reload_conf envoie un signal SIGHUP au serveur, ce qui impose le rechargement des fichiers de configuration par tous les processus serveur.

pg_rotate_logfile signale au gestionnaire de journaux de trace de basculer immédiatement vers un nouveau fichier de sortie. Cela ne fonctionne que lorsque le collecteur de traces interne est actif, puisqu'il n'y a pas de sous-processus de gestion des fichiers journaux dans le cas contraire.

9.26.3. Fonctions de contrôle de la sauvegarde

Les fonctions présentées dans le Tableau 9.79 aident à l'exécution de sauvegardes à chaud. Ces fonctions ne peuvent pas être exécutées lors d'une restauration (sauf pg_start_backup en version non exclusive, pg_stop_backup en version non exclusive, pg_is_in_backup, pg_backup_start_time et pg_wal_lsn_diff).

Tableau 9.79. Fonctions de contrôle de la sauvegarde

NomType de retourDescription
pg_create_restore_point(name text) pg_lsnCrée un point nommé pour réaliser une restauration (fonction restreinte aux superutilisateurs par défaut, mais d'autres utilisateurs peuvent se voir donner le droit EXECUTE pour exécuter cette fonction).
pg_current_wal_flush_lsn() pg_lsnRécupère l'emplacement actuel de vidage des journaux de transactions
pg_current_wal_insert_lsn() textRécupération de l'emplacement d'insertion du journal de transactions courant
pg_current_wal_lsn() pg_lsnRécupération de l'emplacement d'écriture du journal de transactions courant
pg_start_backup(label text [, fast boolean [, exclusive boolean ]]) pg_lsnPréparation de la sauvegarde à chaud (restreint aux superutilisateurs par défaut, mais d'autres utilisateurs peuvent se voir donner le droit d'exécuter cette fonction)
pg_stop_backup() pg_lsnTermine la sauvegarde exclusive en ligne (restreinte aux superutilisateurs par défaut, mais d'autres utilisateurs peuvent se voir donner le droit d'exécution de cette fonction)
pg_stop_backup(exclusive boolean [, wait_for_archive boolean ]) setof recordTermine la sauvegarde en ligne, exclusive ou non (restreinte aux superutilisateurs par défaut, mais d'autres utilisateurs peuvent se voir donner le droit d'exécuter cette fonction)
pg_is_in_backup()boolVrai si une sauvegarde exclusive en ligne est toujours en cours.
pg_backup_start_time() timestamp with time zoneRécupère l'horodatage du début de la sauvegarde exclusive en ligne en progrès.
pg_switch_wal()pg_lsnPassage forcé à un nouveau journal de transactions (restreint aux superutilisateurs par défaut, mas d'autres utilisateurs peuvent se voir donner le droit d'exécuter cette fonction)
pg_walfile_name(lsn pg_lsn) pg_lsnConversion de la chaîne décrivant l'emplacement du journal de transactions en nom de fichier
pg_walfile_name_offset(lsn pg_lsn) pg_lsn, integerConversion de la chaîne décrivant l'emplacement du journal de transactions en nom de fichier et décalage en octets dans le fichier
pg_wal_lsn_diff(lsn pg_lsn, lsn pg_lsn) numericCalcule la différence entre deux emplacements dans les journaux de transactions

pg_start_backup accepte un label arbitraire, défini par l'utilisateur pour la sauvegarde. (Typiquement, ce sera le nom sous lequel le fichier de sauvegarde sera enregistré.) Lorsqu'elle est utilisée en mode exclusif, la fonction écrit un fichier nommé backup_label et, s'il existe des liens dans le répertoire pg_tblspc/, un fichier de correspondance des tablespaces (tablespace_map) dans le répertoire principal des données de l'instance, exécute un checkpoint, puis renvoie l'emplacement du début de sauvegarde au niveau des journaux de transactions sous la forme d'un champ texte. L'utilisateur peut ignorer le résultat. Cette donnée est fournie dans le cas où elle pourrait être utile. Lors de l'utilisation du mode non exclusif, le contenu de ces fichiers est renvoyé par la fonction pg_stop_backup, et doit être enregistré dans la sauvegarde par celui qui a exécuté la fonction.

postgres=# select pg_start_backup('le_label_ici');
 pg_start_backup
-----------------
 0/D4445B8
(1 row)

Il existe un second paramètre booléen optionnel. Si true, il précise l'exécution de pg_start_backup aussi rapidement que possible. Cela force un point de retournement immédiat qui causera un pic dans les opérations d'entrées/sorties, ralentissant toutes les requêtes exécutées en parallèle.

Dans une sauvegarde exclusive, pg_stop_backup supprime le fichier label et, s'il existe, le fichier tablespace_map créés par la fonction pg_start_backup. Lors d'une sauvegarde non exclusive, le contenu des fichiers backup_label et tablespace_map est renvoyé comme résultat de la fonction et doit être écrit dans des fichiers de la sauvegarde, mais pas dans le répertoire des données. Il y a un deuxième argument optionnel de type boolean. Si faux, pg_stop_backup rendra la main immédiatement après que la sauvegarde soit complétée sans attendre l'archivage de WAL. Ce comportement n'est utile que pour les logiciels de sauvegarde qui surveillent indépendamment l'archivage des WAL. Sinon, les WAL nécessaires pour rendre cette sauvegarde cohérente pourraient être manquants et rendre la sauvegarde inutile. Quand ce paramètre est configuré à true, pg_stop_backup attendra que le journal de transactions soit archivé lorsque l'archivage est activé. Sur le serveur standby, cela signifie qu'il attendra seulement quand archive_mode = always. Si l'activité en écriture est basse sur le primaire, il pourrait être utile d'exécuter la fonction pg_switch_wal sur le primaire pour déclencher un changement immédiat de journal de transactions.

Lorsqu'elle est exécutée sur un serveur primaire, cette fonction crée aussi un fichier d'historique des sauvegardes dans le répertoire des journaux de transactions. Ce fichier contient le label passé à pg_start_backup, les emplacements de début et de fin des journaux de transactions correspondant à la sauvegarde et les heures de début et de fin de la sauvegarde. La valeur de retour est l'emplacement du journal de la transaction de fin de sauvegarde (de peu d'intérêt, là encore). Après notification de l'emplacement de fin, le point d'insertion courant du journal de transactions est automatiquement avancé au prochain journal de transactions, de façon à ce que le journal de transactions de fin de sauvegarde puisse être archivé immédiatement pour terminer la sauvegarde.

pg_switch_wal bascule sur le prochain journal de transactions, ce qui permet d'archiver le journal courant (en supposant que l'archivage continu soit utilisé). La fonction retourne l'emplacement de la transaction finale + 1 dans le journal ainsi terminé. S'il n'y a pas eu d'activité dans les journaux de transactions depuis le dernier changement de journal, pg_switch_wal ne fait rien et renvoie l'emplacement de fin du journal de transactions en cours.

pg_create_restore_point crée un enregistrement dans les journaux de transactions, pouvant être utilisé comme une cible de restauration, et renvoie l'emplacement correspondant dans les journaux de transactions. Le nom donné peut ensuite être utilisé avec recovery_target_name pour spécifier la fin de la restauration. Évitez de créer plusieurs points de restauration ayant le même nom car la restauration s'arrêtera au premier nom qui correspond à la cible de restauration.

pg_current_wal_lsn affiche la position d'écriture du journal de transactions en cours dans le même format que celui utilisé dans les fonctions ci-dessus. De façon similaire, pg_current_wal_insert_lsn affiche le point d'insertion dans le journal de transactions courant et pg_current_wal_flush_lsn affiche le point de vidage des journaux de transactions. Le point d'insertion est la fin « logique » du journal de transactions à tout instant alors que l'emplacement d'écriture est la fin de ce qui a déjà été écrit à partir des tampons internes du serveur et l'emplaceent de viage est l'emplacement garanti comme étant écrit sur un stockage durable. La position d'écriture est la fin de ce qui peut être examiné extérieurement au serveur. C'est habituellement l'information nécessaire à qui souhaite archiver des journaux de transactions partiels. Les points d'insertion et de vidage ne sont donnés que pour des raisons de débogage du serveur. Il s'agit là d'opérations de lecture seule qui ne nécessitent pas de droits superutilisateur.

pg_walfile_name_offset peut être utilisée pour extraire le nom du journal de transactions correspondant et le décalage en octets à partir du résultat de n'importe quelle fonction ci-dessus. Par exemple :

postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
        file_name         | file_offset
--------------------------+-------------
 00000001000000000000000D |     4039624
(1 row)

De façon similaire, pg_walfile_name n'extrait que le nom du journal de la transaction. Quand la position dans le journal de la transaction donnée est exactement sur une limite de journal, les deux fonctions renvoient le nom du journal précédent. C'est généralement le comportement souhaité pour gérer l'archivage des journaux, car le fichier précédent est le dernier à devoir être archivé.

pg_wal_lsn_diff calcule la différence en octets entre deux emplacements dans les journaux de transactions. Cette fonction peut être utilisée avec pg_stat_replication ou avec les fonctions indiquées dans Tableau 9.79 pour obtenir le retard de la réplication.

Pour les détails sur le bon usage de ces fonctions, voir la Section 25.3.

9.26.4. Fonctions de contrôle de la restauration

Les fonctions affichées dans Tableau 9.80 fournissent des informations sur le statut actuel du serveur en attente. Ces fonctions peuvent être utilisées lors d'une restauration mais aussi lors d'un fonctionnement normal.

Tableau 9.80. Fonctions d'information sur la restauration

NomType du retourDescription
pg_is_in_recovery() boolTrue si la restauration est en cours.
pg_last_wal_receive_lsn() pg_lsnRécupère l'emplacement de la dernière transaction reçue et synchronisée sur disque par la réplication en flux. Lorsque cette dernière est en cours d'exécution, l'emplacement aura une progression monotone. Si la restauration a terminé, elle deviendra statique et aura comme valeur celui du dernier enregistrement de transaction reçu et synchronisé sur disque lors de la restauration. Si la réplication en flux est désactivé ou si elle n'a pas encore commencé, la fonction renvoie NULL.
pg_last_wal_replay_lsn() pg_lsnRécupère l'emplacement du dernier enregistrement WAL rejoué lors de la restauration. Si la restauration est toujours en cours, cela va augmenter progressivement. Si la restauration s'est terminée, alors cette valeur restera statique et dépendera du dernier enregistrement WAL reçu et synchronisé sur disque lors de cette restauration. Quand le serveur a été lancé sans restauration de flux, la valeur renvoyée par la fonction sera NULL.
pg_last_xact_replay_timestamp() timestamp with time zoneRécupère la date et l'heure de la dernière transaction rejouée pendant la restauration. C'est l'heure à laquelle l'enregistrement du journal pour cette transaction a été généré sur le serveur principal, que la transaction soit validée ou annulée. Si aucune transaction n'a été rejouée pendant la restauration, cette fonction renvoie NULL. Sinon, si la restauration est toujours en cours, cette valeur augmentera continuellement. Si la restauration s'est terminée, alors cette valeur restera statique et indiquera la valeur correspondant à la dernière transaction rejouée pendant la restauration. Quand le serveur a été démarré normalement (autrement dit, sans restauration), cette fonction renvoie NULL.

Les fonctions affichées dans Tableau 9.81 contrôlent la progression de la restauration. Ces fonctions sont seulement exécutables pendant la restauration.

Tableau 9.81. Fonctions de contrôle de la restauration

NomType de la valeur de retourDescription
pg_is_wal_replay_paused() boolTrue si la restauration est en pause.
pg_wal_replay_pause() voidMet en pause immédiatement (restreint aux superutilisateurs par défaut, mais d'autres utilisateurs peuvent se voir donner le droit d'exécuter cette fonction).
pg_wal_replay_resume() voidRelance la restauration si elle a été mise en pause (restreint aux superutilisateurs par défaut, mais d'autres utilisateurs peuvent se voir donner le droit d'exécuter cette fonction).

Quand la restauration est en pause, aucune modification de la base n'est appliquée. Si le serveur se trouve en Hot Standby, toutes les nouvelles requêtes verront la même image cohérente de la base et aucun conflit de requêtes ne sera rapporté jusqu'à la remise en route de la restauration.

Si la réplication en flux est désactivée, l'état pause peut continuer indéfiniment sans problème. Si elle est activée, les enregistrements des journaux continueront à être reçus, ce qui peut éventuellement finir par remplir l'espace disque disponible, suivant la durée de la pause, le taux de génération des journaux et l'espace disque disponible.

9.26.5. Fonctions de synchronisation des images de base

PostgreSQL permet aux sessions de la base de synchroniser leur vue de la base (appelée aussi image ou snapshot). Le snapshot détermine les données visibles pour la transaction qui utilise le snapshot. Les snapshots synchronisés sont nécessaires quand deux sessions ou plus ont besoin de voir un contenu identique dans la base. Si deux sessions commencent leur transactions indépendamment, il existe toujours une possibilité pour qu'une troisième transaction enregistre des données entre l'exécution des deux commandes START TRANSACTION, ce qui aurait pour conséquence qu'une des transactions verrait les effets de cet enregistrement et pas la deuxième.

Pour résoudre ce problème, PostgreSQL permet à une transaction d'exporter le snapshot qu'elle utilise. Aussi longtemps que la transaction reste ouverte, les autres transactions peuvent importer son snapshot et ont ainsi la garantie qu'elles voient exactement les mêmes données que la transaction qui a fait l'export. Notez cependant que toute modification réalisée par une de ses transactions restera invisible aux autres transactions, ce qui est le comportement standard des transactions non validées. Donc les transactions sont synchronisées pour ce qui concernent les données pré-existantes, mais agissent normalement pour les modifications qu'elles font.

Les snapshots sont exportés avec la fonction pg_export_snapshot, montrée dansTableau 9.82, et importés avec la commande SET TRANSACTION.

Tableau 9.82. Fonction de synchronisation de snapshot

NomType renvoyéDescription
pg_export_snapshot() textSauvegarde le snapshot actuel et renvoie son identifiant

La fonction pg_export_snapshot sauvegarde le snapshot courant et renvoie une chaîne de type text identifiant le snapshot. Cette chaîne doit être passée (en dehors de la base de données) aux clients qui souhaitent importer le snapshot. Ce dernier est disponible en import jusqu'à la fin de la transaction qui l'a exporté. Une transaction peut exporter plus d'un snapshot si nécessaire. Notez que ceci n'est utile que dans le mode d'isolation READ COMMITTED car, dans le mode REPEATABLE READ et les niveaux d'isolation plus importants, les transactions utilisent le même snapshot tout au long de leur vie. Une fois qu'une transaction a exporté des snapshots, il ne peut plus être préparé avec PREPARE TRANSACTION.

Voir SET TRANSACTION pour des détails sur l'utilisation d'un snapshot exporté.

9.26.6. Fonctions de réplication

Les fonctions décrites dans le Tableau 9.83 permettent de contrôler et interagir avec les fonctionnalités de réplication. Voir Section 26.2.5, Section 26.2.6, et Chapitre 50 pour des informations sur les fonctionnalités sous-jacentes. L'utilisation des fonctions sur l'origine de la réplication est restreinte aux superutilisateurs. L'utilisation des fonctions sur les slots de réplication est restreinte aux superutilisateurs et aux utilisateurs ayant l'attribut REPLICATION.

La plupart de ces fonctions ont des commandes équivalentes dans le protocole de réplication ; voir Section 53.6.

Les fonctions décrites dans les Section 9.26.3, Section 9.26.4, et Section 9.26.5 concernent aussi la réplication.

Tableau 9.83. Fonctions SQL pour la réplication

FonctionType renvoyéDescription
pg_create_physical_replication_slot(nom_slot name [, immediately_reserve boolean, temporary boolean]) (nom_slot name, lsn pg_lsn) Crée un slot physique de réplication nommé nom_slot. Le deuxième paramètre, optionnel, indique si le LSN pour ce slot de réplication doit être réservé immédiatement. Dans le cas contraire, le LSN est réservé à la première connexion à partir d'un client de réplication en flux. Les modifications du flux à partir d'un slot physique est seulement disponible avec le protocole de réplication en flux -- voir Section 53.6. Le troisième argument, temporary, est facultatif. Quand positionné à true, il spécifique que le slot ne devraiment pas être stockée de manière permanente sur disque, et n'est destiné qu'à être utilisé par la session courante. Les slots temporaires sont également relâchés lors de n'importe quelle erreur. Cette fonction correspond à la commande du protocole de réplication CREATE_REPLICATION_SLOT ... PHYSICAL.
pg_drop_replication_slot(nom_slot name) void Supprime le slot physique ou logique de réplication nommé nom_slot. Identique à la commande DROP_REPLICATION_SLOT du protocole de réplication. Pour les slots logiques, elle doit être appelée quand on est connecté à la même base que celle où le slot a été crée.
pg_create_logical_replication_slot(nom_slot name, plugin name [, temporary boolean]) (nom_slot name, lsn pg_lsn) Crée un nouveau slot logique de réplication nommé nom_slot en utilisant le plugin de sortie nommé plugin. Le troisième paramètre facultatif, , temporary, quand positionné à true, spécifie que le slot ne devrait pas être stocké de manière permanente sur le disque et n'est destiné à être utilisé que pa la session courante. Les slots temporaires sont également relâchés à la moindre erreur. Un appel à cette fonction a le même effet que la commande CREATE_REPLICATION_SLOT ... LOGICAL du protocole de réplication.
pg_logical_slot_get_changes(nom_slot name, jusqu_au_lsn pg_lsn, jusqu_au_n_changements int, VARIADIC options text[]) (lsn pg_lsn, xid xid, data text) Renvoie les changements dans le slot nom_slot, en commençant à partir du premier changement non consommé. Si jusqu_au_lsn et jusqu_au_n_changements sont NULL, le décodage logique continuera jusqu'à la fin des WAL présents. Si jusqu_au_lsn est différent de NULL, le décodage incluera seulement les transactions dont la validation a précédé le LSN indiqué. Si jusqu_au_n_changements est différent de NULL, le décodate s'arrêtera quand le nombre de lignes produites par le décodage excède la valeur indiquée. Néanmoins, notez que le nombre réel de lignes renvoyées peut être plus grand car la limite n'est vérifiée qu'après l'ajout des lignes produites lors du décodage de chaque nouvelle validation de transaction.
pg_logical_slot_peek_changes(nom_slot name, jusqu_au_lsn pg_lsn, jusqu_au_n_changements int, VARIADIC options text[]) (lsn text, xid xid, data text) Se comporte exactement comme la fonction pg_logical_slot_get_changes(), sauf que les changements ne sont pas consommés ; c'est-à-dire que les changements seront de nouveau renvoyés lors des prochains appels.
pg_replication_slot_advance(nom_slot name, jusquau_lsn pg_lsn) (nom_slot name, dernier_lsn pg_lsn) bool Avance à la position confirmée courante d'un slot de réplication nommé nom_slot. Le slot ne sera pas déplacé en arrière et il ne sera pas déplacé après l'emplacement d'insertion actuel. Renvoie le nom du slot et la position réelle à laquelle il a été avancé. L'information du slot mis à jour est écrite au checkpoint suivant si l'avancement a eu lieu. Dans le cas d'un crash, le slot pourrait retourner à une position précédente.
pg_replication_origin_create(node_name text) oid Créé une origine de réplication avec le nom externe indiqué, et renvoie l'id interne qui lui a été assigné.
pg_replication_origin_drop(node_name text) void Supprime une origine de réplication créée antérieurement, y compris tous les rejeux associés en cours.
pg_replication_origin_oid(node_name text) oid Recherche une origine de réplication par son nom et renvoie son identifiant interne. S'il n'existe pas d'origine de réplication correspondante, NULL est levée.
pg_replication_origin_session_setup(node_name text) void Marque la session courante comme rejouant à partir de l'origine indiquée, permettant de suivre la progression du rejeu. Utilisez pg_replication_origin_session_reset pour annuler. Peut seulement être utilisée si aucune origine précédente n'est configurée.
pg_replication_origin_session_reset() void Annule les effets de pg_replication_origin_session_setup.
pg_replication_origin_session_is_setup() bool Indique si une origine de réplication a été configurée dans la session courante.
pg_replication_origin_session_progress(flush bool) pg_lsn Renvoie la position du rejeu pour l'origine de réplication configurée dans la session courante. Le paramètre flush indique si la transaction locale correspondante sera garantie avoir été écrite sur disque ou pas.
pg_replication_origin_xact_setup(origin_lsn pg_lsn, origin_timestamp timestamptz) void Marque la transaction courante comme rejouant une transaction qui a été validée au LSN et à l'horodatage indiqués. Peut seulement être appelé lorsqu'une origine de réplication a été antérieurement configurée en utilisant pg_replication_origin_session_setup().
pg_replication_origin_xact_reset(origin_lsn pg_lsn, origin_timestamp timestamptz) void Annule les effets de pg_replication_origin_xact_setup().
pg_replication_origin_advance(node_name text, lsn pg_lsn) void Positionne l'avancement de la réplication pour le nœud indiqué à la position donnée. Ceci est principalement utile pour positionner la position initiale ou une nouvelle position après des modifications dans la configuration ou équivalent. Soyez conscient qu'un usage non réfléchi de cette fonction peut entraîner des données répliquées incohérentes.
pg_replication_origin_progress(node_name text, flush bool) pg_lsn Renvoie la position du rejeu pour l'origine de réplication indiquée. Le paramètre flush détermine si la transaction locale correspondante sera garantie avoir été écrite sur disque ou pas.
pg_logical_slot_get_binary_changes(nom_slot name, jusqu_au_lsn pg_lsn, jusqu_au_n_changements int, VARIADIC options text[]) (lsn pg_lsn, xid xid, data bytea) Se comporte comme la fonction pg_logical_slot_get_changes(), sauf que les changements sont renvoyées avec le type de données bytea.
pg_logical_slot_peek_binary_changes(nom_slot name, jusquau_lsn pg_lsn, upto_nchanges int, VARIADIC options text[]) (lsn pg_lsn, xid xid, data bytea) Se comporte exactement comme la fonction pg_logical_slot_get_changes(), sauf que les changements sont renvoyées avec le type de données bytea et qu'ils ne sont pas consommés ; c'est-à-dire que les changements seront de nouveau renvoyés lors des prochains appels.
pg_logical_emit_message(transactional bool, prefix text, content text) pg_lsn Émet un message texte de décodage logique. Cette fonction peut être utilisée pour passer des messages génériques aux plugins de décodage logique via les journaux de transactions. Le paramètre transactional précise si le message doit faire partie de la transaction en cours ou s'il doit être écrit immédiatement et décodé dès que le décodage logique lit l'enregistrement. Le paramètre prefix est un préfixe texte utilisé par les plugins de décodage logique pour reconnaître facilement les messages les intéressants. Le paramètre content est le texte du message.
pg_logical_emit_message(transactional bool, prefix text, content bytea) pg_lsn Émet un message binaire de décodage logique. Cette fonction peut être utilisée pour passer des messages génériques aux plugins de décodage logique via les journaux de transactions. Le paramètre transactional précise si le message doit faire partie de la transaction en cours ou s'il doit être écrit immédiatement et décodé dès que le décodage logique lit l'enregistrement. Le paramètre prefix est un préfixe texte utilisé par les plugins de décodage logique pour reconnaître facilement les messages les intéressants. Le paramètre content est le contenu binaire du message.

9.26.7. Fonctions de gestion des objets du serveur

Les fonctions présentées dans le Tableau 9.84 calculent l'utilisation de l'espace disque par les objets de la base de données.

Tableau 9.84. Fonctions de calcul de la taille des objets de la base de données

NomCode de retourDescription
pg_column_size(any) intNombre d'octets utilisés pour stocker une valeur particulière (éventuellement compressée)
pg_database_size(oid) bigintEspace disque utilisé par la base de données d'OID indiqué
pg_database_size(name) bigintEspace disque utilisé par la base de données de nom indiqué
pg_indexes_size(regclass) bigint Espace disque total utilisé par les index attachés à la table dont l'OID ou le nom est indiqué
pg_relation_size(relation regclass, fork text) bigint Espace disque utilisé par le fork indiqué, 'main', 'fsm', 'vm' ou 'init', d'une table ou index d'OID ou de nom indiqué.
pg_relation_size(relation regclass) bigint Raccourci pour pg_relation_size(..., 'main')
pg_size_bytes(text) bigint Convertit une taille dans un format lisible par un humain avec des unités de taille en nombre d'octets
pg_size_pretty(bigint) text Convertit la taille en octets (entier sur 64 bits) en un format lisible par l'homme et avec une unité
pg_size_pretty(numeric) text Convertit la taille en octets (type numeric) en un format lisible par l'homme et avec une unité
pg_table_size(regclass) bigint Espace disque utilisé par la table spécifiée, en excluant les index (mais en incluant les données TOAST, la carte des espaces libres et la carte de visibilité)
pg_tablespace_size(oid) bigintEspace disque utilisé par le tablespace ayant cet OID
pg_tablespace_size(name) bigintEspace disque utilisé par le tablespace ayant ce nom
pg_total_relation_size(regclass) bigint Espace disque total utilisé par la table spécifiée, en incluant toutes les données TOAST et les index

pg_column_size affiche l'espace utilisé pour stocker toute valeur individuelle.

pg_total_relation_size accepte en argument l'OID ou le nom d'une table ou d'une table TOAST. Elle renvoie l'espace disque total utilisé par cette table, incluant les index associés. Cette fonction est équivalente à pg_table_size + pg_indexes_size.

pg_table_size accepte en argument l'OID ou le nom d'une table et renvoie l'espace disque nécessaire pour cette table, à l'exclusion des index (espace des données TOAST, carte des espaces libres et carte de visibilité inclus.)

pg_indexes_size accepte en argument l'OID ou le nom d'une table et renvoie l'espace disque total utilisé par tous les index attachés à cette table.

pg_database_size et pg_tablespace_size acceptent l'OID ou le nom d'une base de données ou d'un tablespace et renvoient l'espace disque total utilisé. Pour utiliser pg_database_size, vous devez avoir la permission CONNECT sur la base de données spécifiée (qui est accordée par défaut), ou être un membre du rôle pg_read_all_stats. Pour utiliser pg_tablespace_size, vous devez avoir la permission CREATE sur le tablespace spécifié, ou être un membre de pg_read_all_stats à moins que cela ne soit le tablespace par défaut de la base courante.

pg_relation_size accepte l'OID ou le nom d'une table, d'un index ou de la partie TOAST d'une table. Elle renvoie la taille sur disque d'un des éléments de cet objet en octets. (Notez que, dans la plupart des cas, il est plus agréable d'utiliser les fonctions de haut niveau telles que pg_total_relation_size ou pg_table_size, qui additionnent les tailles de chaque partie.) Avec un seul argument, cette fonction renvoie la taille de la partie principale (le HEAP) de la relation. Le deuxième argument permet d'indiquer la partie à examiner :

  • 'main' renvoie la taille de la partie principale (HEAP) de la relation.

  • 'fsm' renvoie la taille de la partie Free Space Map (voir Section 69.3) associée à cette relation.

  • 'vm' renvoie la taille de la partie Visibility Map (voir Section 69.4) associée à cette relation.

  • 'init' renvoie la taille de la partie initialisation, si elle existe, associée à la relation.

pg_size_pretty peut être utilisé pour formater le résultat d'une des autres fonctions de façon interprétable par l'utilisateur, en utilisant bytes, kB, MB, GB ou TB suivant le cas.

pg_size_bytes peut être utilisé pour obtenir la taille en octets à partir d'une chaîne dans un format lisible par un humain. L'entrée doit avoir des unités bytes, kB, MB, GB ou TB, et est analysée sans faire attention à la casse. Si aucune unité n'est indiquée, l'unité du nombre sera des octets.

Note

Les unités kB, MB, GB et TB utilisées par les fonctions pg_size_pretty et pg_size_bytes sont définies avec des puissances de 2 plutôt que des puissances de 10. Donc, 1kB est 1024 octets, 1MB est 10242 = 1048576 octets, et ainsi de suite.

Les fonctions ci-dessus qui opèrent sur des tables ou des index acceptent un argument regclass, qui est simplement l'OID de la table ou de l'index dans le catalogue système pg_class. Vous n'avez pas à rechercher l'OID manuellement. Néanmoins, le convertisseur de type de données regclass fera ce travail pour vous. Écrivez simplement le nom de la table entre guillements simples pour qu'il ressemble à une constante littérale. Pour compatibilité avec la gestion des noms SQL standards, la chaîne sera convertie en minuscule sauf si elle est entourée de guillemets doubles.

Si un OID qui ne représente pas un objet existant est passé en tant qu'argument à une des fonctions ci-dessus, NULL est renvoyé.

Les fonctions affichées dans Tableau 9.85 facilitent l'identification des fichiers associées aux objets de la base de données.

Tableau 9.85. Fonctions de récupération de l'emplacement des objets de la base de données

NomType en retourDescription
pg_relation_filenode(relation regclass) oid Numéro filenode de la relation indiquée
pg_relation_filepath(relation regclass) text Chemin et nom du fichier pour la relation indiquée
pg_filenode_relation(tablespace oid, filenode oid) regclass Trouve la relation associée au tablespace et au numéro de fichier indiqués

pg_relation_filenode accepte l'OID ou le nom d'une table, d'un index, d'une séquence ou d'une table TOAST. Elle renvoie le numéro « filenode » qui lui est affecté. Ce numéro est le composant de base du nom de fichier utilisé par la relation (voir Section 69.1 pour plus d'informations). Pour la plupart des tables, le résultat est identique à pg_class.relfilenode mais pour certains catalogues système, relfilenode vaut zéro et cette fonction doit être utilisée pour obtenir la bonne valeur. La fonction renvoie NULL si l'objet qui lui est fourni est une relation qui n'a pas de stockage, par exemple une vue.

pg_relation_filepath est similaire à pg_relation_filenode mais elle renvoie le chemin complet vers le fichier (relatif au répertoire des données de l'instance, PGDATA) de la relation.

pg_filenode_relation est l'inverse de pg_relation_filenode. Avec l'OID du « tablespace » et le numéro de fichier (« filenode »), elle renvoie l'OID de la relation associée. Pour une table dans le tablespace par défaut de la base de données, le tablespace peut être spécifié avec le nombre 0.

Tableau 9.86 liste les fonctions utilisées pour gérer les collations.

Tableau 9.86. Fonctions de gestion des collations

NomType retournéDescription
pg_collation_actual_version(oid) textRetourne la version courante de la collation depuis le système d'exploitation
pg_import_system_collations(schema regnamespace) integerImporte les collations du système d'exploitation

pg_import_system_collations ajoute les collationnements au catalogue système pg_collation suivant les locales trouvées dans le système d'exploitation. Si la version est différente de la valeur dans pg_collation.collversion, alors les objets dépendants de la collation pourraient nécessiter d'être reconstruits. Voir aussi ALTER COLLATION.

pg_import_system_collations remplit le catalogue système pg_collation avec les collations basées sur toutes les locales qu'elle trouve sur le système d'exploitation. C'est ce qu' initdb utilise; voir Section 23.2.2 pour plus de détails. Si d'autres locales sont plus tard installées sur le système d'exploitation, cette fonction peut être appelée de nouveau pour ajouter les collations pour les nouvelles locales. Les locales correspondant aux entrées existantes dans pg_collation seront ignorées. (Mais les objets de collation qui ne sont plus présents dans le système d'exoploitation ne sont pas supprimées par cette fonction.) Le paramètre schema serat typiquement pg_catalog, mais ce n'est pas requis ; les collationnements pourraient aussi être installés dans d'autres schémas. La fonction renvoie le nombre des nouveaux objets de collation qu'il crée. L'utilisation de cette fonction est restreinte aux super-utilisateurs.

9.26.8. Fonctions de maintenance des index

Tableau 9.87 indique les fonctions disponibles pour les tâches de maintenance des index. Ces fonctions ne peuvent pas être exécutées en mode de restauration. L'utilisation de ces fonctions est restreinte aux superutilisateurs et au propriétaire de l'index indiqué

Tableau 9.87. Fonctions de maintenance des index

NomType en retourDescription
brin_summarize_new_values(index regclass) integerRésume les pages des intervalles non résumés
brin_summarize_range(index regclass, blockNumber bigint) integerRésume les intervalles de page couvrant les blocs spécifiés, s'ils ne sont pas déjà résumé
brin_desummarize_range(index regclass, blockNumber bigint) integerSupprime le résumé de l'intervalle de page couvrant les blocs spécifiés, s'ils sont résumés
gin_clean_pending_list(index regclass) bigintDéplace les entrées de la liste d'attente GIN dans la structure principale de l'index

brin_summarize_new_values reçoit comme argument l'OID ou le nom d'un index BRIN et inspecte l'index pour trouver les pages d'intervalles dans la table de base qui ne sont actuellement pas résumées dans l'index ; pour tous ces intervalles, elle crée une nouvelle ligne de résumé dans l'index en parcourant les pages de la table. Elle renvoie le nombre de nouvelles pages des intervalles résumés qui ont été insérées dans l'index. brin_summarize_range fait la même chose, sauf qu'il ne résume que les intervalles couverts par les numéro de blocs fournis.

gin_clean_pending_list accepte l'OID ou le nom d'un index GIN et nettoie la liste d'attente de l'index spécifié en déplaçant les enregistrements qui y sont dans la structure de données principale de GIN. Elle renvoie le nombre de blocs supprimés dans la liste d'attente. Notez que si l'index indiqué est un index GIN construit avec l'option fastupdate désactivée, le nettoyage n'a pas lieu et la valeur de retour est 0 parce que l'index n'a pas de liste d'attente. Merci de voir Section 66.4.1 et Section 66.5 pour des détails sur la liste d'attente et l'option fastupdate.

9.26.9. Fonctions génériques d'accès aux fichiers

Les fonctions présentées dans le Tableau 9.88 fournissent un accès natif aux fichiers situés sur le serveur. Seuls les fichiers contenus dans le répertoire du cluster et ceux du répertoire log_directory sont accessibles sauf si l'utilisateur dispose des droits du rôle pg_read_server_files. On utilise un chemin relatif pour les fichiers contenus dans le répertoire du cluster et un chemin correspondant à la configuration du paramètre log_directory pour les journaux de trace.

Notez que donner aux utilisateurs le droit EXECUTE sur la fonction pg_read_file() ou les autres fonctions du même type leur permet de lire tout fichier sur le serveur que le moteur de base de données peut lire. Ces lectures contournent les vérifications de droit à l'intérieur de la base. Cela signifie que, parmi d'autres, un utilisateur disposant de ce droit peut lire le contenu de la table pg_authid où les informations d'authentification sont contenues, ainsi que tout autre fichier dans la base de données. De ce fait, une grande attention doit être portée lors de l'attribut de ce droit d'accès à ces fonctions.

Tableau 9.88. Fonctions d'accès générique aux fichiers

NomCode de retourDescription
pg_ls_dir(nom_répertoire text [, missing_ok boolean, include_dot_dirs boolean]) setof textListe le contenu d'un répertoire. Restreint aux superutilisateurs par défaut mais d'autres utilisateurs peuvent se voir donner le droit EXECUTE pour exécuter la fonction.
pg_ls_logdir() setof record Liste les nom, taille et heure de dernière modification des fichiers dans le répertoire de traces. L'accès est accordé aux membres du rôle pg_monitor et peut être accordé à d'autres rôles non super-utilisateur. Les fichiers commençant par un point, les répertoires et les autres fichiers spéciaux ne sont pas affichés.
pg_ls_waldir() setof record Liste les nom, taille et heure de dernière modification des fichiers dans le répertoire de WAL. L'accès est accordé aux membres du rôle pg_monitor et peut être accordé à d'autres rôles non super-utilisateur. Les fichiers commençant par un point, les répertoires et les autres fichiers spéciaux ne sont pas affichés.
pg_read_file(filename text [, offset bigint, length bigint [, missing_ok boolean] ]) textRenvoie le contenu d'un fichier texte. Restreint aux superutilisateurs par défaut mais d'autres utilisateurs peuvent se voir donner le droit EXECUTE pour exécuter la fonction.
pg_read_binary_file(filename text [, offset bigint, length bigint [, missing_ok boolean] ]) byteaRenvoie le contenu d'un fichier. Restreint aux superutilisateurs par défaut mais d'autres utilisateurs peuvent se voir donner le droit EXECUTE pour exécuter la fonction.
pg_stat_file(filename text[, missing_ok boolean]) recordRenvoie les informations concernant un fichier. Restreint aux superutilisateurs par défaut mais d'autres utilisateurs peuvent se voir donner le droit EXECUTE pour exécuter la fonction.

Certaines de ces fonctions prennent un paramètre optionnel missing_ok qui indique le comportement lorsque le fichier ou le répertoire n'existe pas. Si true, la fonction renvoie NULL (sauf pg_ls_dir, qui renvoie un ensemble vide comme résultat). Si false, une erreur est levée. Il est positionné à false par défaut.

pg_ls_dir renvoie les noms de tous les fichiers (ainsi que les répertoires ou fichiers spéciaux) dans le répertoire indiqué. Le paramètre include_dot_dirs indique si « . » et « .. » sont inclus dans l'ensemble résultat. Le défaut est de les exclure (false), mais les inclure peut être utile lorsque missing_ok est true, pour faire la distinction entre un répertoire vide et un répertoire inexistant.

pg_ls_logdir retourne les nom, taille et date de dernière modification (mtime) de chacun des fichiers dans le répertoire de traces. Par défaut, seuls les super-utilisateurs et les membres du rôle pg_monitor peuvent utiliser cette fonction. L'accès peut être autorisé à d'autres rôles en utilisant GRANT.

pg_ls_waldir retourne les nom, taille et date de dernière modification (mtime) de chacun des fichiers dans le répertoire des journaux de transaction (WAL). Par défaut, seuls les super-utilisateurs et les membres du rôle pg_monitor peuvent utiliser cette fonction. L'accès peut être autorisé à d'autres rôles en utilisant GRANT.

pg_read_file renvoie une partie d'un fichier texte, débutant au décalage indiqué, renvoyant au plus longueur octets (moins, si la fin du fichier est atteinte avant). Si le décalage est négatif, il est relatif à la fin du fichier. Si offset et length sont omis, le fichier entier est renvoyé. Les octets lus à partir de ce fichier sont interprétés comme une chaîne dans l'encodage du serveur. Une erreur est affichée si l'encodage est mauvais.

pg_read_binary_file est similaire à pg_read_file, sauf que le résultat est une valeur de type bytea ; du coup, aucune vérification d'encodage n'est réalisée. Avec la fonction convert_from, cette fonction peut être utilisée pour lire un fichier dans un encodage spécifié :

SELECT convert_from(pg_read_binary_file('fichier_en_utf8.txt'), 'UTF8');
      

pg_stat_file renvoie un enregistrement contenant la taille du fichier, les date et heure de dernier accès, les date et heure de dernière modification, les date et heure de dernier changement de statut (plateformes Unix seulement), les date et heure de création (Windows seulement) et un booléen indiquant s'il s'agit d'un répertoire. Les usages habituels incluent :

SELECT * FROM pg_stat_file('nomfichier');
SELECT (pg_stat_file('nomfichier')).modification;

9.26.10. Fonctions pour les verrous consultatifs

Les fonctions présentées dans Tableau 9.89 gèrent les verrous consultatifs. Pour les détails sur le bon usage de ces fonctions, voir Section 13.3.5.

Tableau 9.89. Fonctions de verrous consultatifs

NomType renvoyéDescription
pg_advisory_lock(key bigint) voidObtient un verrou consultatif exclusif au niveau session
pg_advisory_lock(key1 int, key2 int) voidObtient un verrou consultatif exclusif au niveau session
pg_advisory_lock_shared(key bigint) voidObtient un verrou consultatif partagé au niveau session
pg_advisory_lock_shared(key1 int, key2 int) voidObtient un verrou consultatif partagé au niveau session
pg_try_advisory_lock(key bigint) booleanObtient un verrou consultatif exclusif si disponible
pg_try_advisory_lock(key1 int, key2 int) booleanObtient un verrou consultatif exclusif si disponible
pg_try_advisory_lock_shared(key bigint) booleanObtient un verrou consultatif partagé si disponible
pg_try_advisory_lock_shared(key1 int, key2 int) booleanObtient un verrou consultatif partagé si disponible
pg_advisory_unlock(key bigint) booleanRelâche un verrou consultatif exclusif au niveau session
pg_advisory_unlock(key1 int, key2 int) booleanRelâche un verrou consultatif exclusif au niveau session
pg_advisory_unlock_all() voidRelâche tous les verrous consultatifs au niveau session détenus par la session courante
pg_advisory_unlock_shared(key bigint) booleanRelâche un verrou consultatif partagé au niveau session
pg_advisory_unlock_shared(key1 int, key2 int) booleanRelâche un verrou consultatif partagé au niveau session
pg_advisory_xact_lock(key bigint) voidObtient un verrou consultatif exclusif au niveau transaction
pg_advisory_xact_lock(key1 int, key2 int) voidObtient un verrou consultatif exclusif au niveau transaction
pg_advisory_xact_lock_shared(key bigint) voidObtient un verrou consultatif partagé au niveau transaction
pg_advisory_xact_lock_shared(key1 int, key2 int) voidObtient un verrou consultatif partagé au niveau transaction
pg_try_advisory_lock(key bigint) booleanObtient un verrou consultatif exclusif au niveau session si disponible
pg_try_advisory_lock(key1 int, key2 int) booleanObtient un verrou consultatif exclusif au niveau session si disponible
pg_try_advisory_lock_shared(key bigint) booleanObtient un verrou consultatif partagé au niveau session si disponible
pg_try_advisory_lock_shared(key1 int, key2 int) booleanObtient un verrou consultatif partagé au niveau session si disponible
pg_try_advisory_xact_lock(key bigint) booleanObtient un verrou consultatif exclusif au niveau transaction si disponible
pg_try_advisory_xact_lock(key1 int, key2 int) booleanObtient un verrou consultatif exclusif au niveau transaction si disponible
pg_try_advisory_xact_lock_shared(key bigint) booleanObtient un verrou consultatif partagé au niveau transaction si disponible
pg_try_advisory_xact_lock_shared(key1 int, key2 int) booleanObtient un verrou consultatif partagé au niveau transaction si disponible

pg_advisory_lock verrouille une ressource applicative qui peut être identifiée soit par une valeur de clé sur 64 bits soit par deux valeurs de clé sur 32 bits (les deux espaces de clé ne se surchargent pas). Si une autre session détient déjà un verrou sur le même identifiant de ressource, la fonction attend que la ressource devienne disponible. Le verrou est exclusif. Les demandes de verrou s'empilent de sorte que, si une même ressource est verrouillée trois fois, elle doit être déverrouillée trois fois pour être disponible par les autres sessions.

pg_advisory_lock_shared fonctionne de façon identique à pg_advisory_lock sauf que le verrou peut être partagé avec d'autres sessions qui réclament des verrous partagés. Seules les demandes de verrou exclusif sont bloquées.

pg_try_advisory_lock est similaire à pg_advisory_lock sauf que la fonction n'attend pas la disponibilité du verrou. Si le verrou peut être obtenu immédiatement, la fonction renvoie true, sinon, elle renvoie false.

pg_try_advisory_lock_shared fonctionne de la même façon que pg_try_advisory_lock sauf qu'elle tente d'acquérir un verrou partagé au lieu d'un verrou exclusif.

pg_advisory_unlock relâche un verrou consultatif exclusif précédemment acquis au niveau session. Elle retourne true si le verrou est relaché avec succès. Si le verrou n'était pas détenu, false est renvoyé et un message d'avertissement SQL est émis par le serveur.

pg_advisory_unlock_shared fonctionne de la même façon que pg_advisory_unlock mais pour relâcher un verrou partagé au niveau session.

pg_advisory_unlock_all relâche tous les verrous consultatifs au niveau session détenus par la session courante. (Cette fonction est appelée implicitement à la fin de la session, même si le client se déconnecte brutalement.)

pg_advisory_xact_lock fonctionne de la même façon que pg_advisory_lock, sauf que le verrou est automatiquement relâché à la fin de la transaction courante et ne peut pas être relâché de façon explicite.

pg_advisory_xact_lock_shared fonctionne de la même façon que pg_advisory_lock_shared, sauf que le verrou est automatiquement relâché à la fin de la transaction courante et ne peut pas être relâché de façon explicite.

pg_try_advisory_xact_lock fonctionne de la même façon que pg_try_advisory_lock, sauf que le verrou, s'il est acquis, est automatiquement relâché à la fin de la transaction courante et ne peut pas être relâché de façon explicite.

pg_try_advisory_xact_lock_shared fonctionne de la même façon que pg_try_advisory_lock_shared, sauf que le verrou, s'il est acquis, est automatiquement relâché à la fin de la transaction courante et ne peut pas être relâché de façon explicite.