PostgreSQLLa base de données la plus sophistiquée au monde.

9.24. Fonctions d'administration système

Le Tableau 9.55, « Fonctions agissant sur les paramètres de configuration » affiche les fonctions disponibles pour consulter et modifier les paramètres de configuration en exécution.

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

Nom Type de retour Description
current_setting (nom_paramètre) text valeur courante du paramètre
set_config (nom_paramètre, , nouvelle_valeur, , est_local) text configure 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)

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)

Les fonctions présentées dans le Tableau 9.56, « Fonctions d'envoi de signal au serveur » envoient des signaux de contrôle aux autres processus serveur. L'utilisation de ces fonctions est restreinte aux superutilisateurs.

Tableau 9.56. Fonctions d'envoi de signal au serveur

Nom Type de retour Description
pg_cancel_backend (pid int) boolean Annule une requête en cours sur le serveur
pg_terminate_backend(pid int) boolean Termine une processus serveur
pg_reload_conf() boolean Impose le rechargement des fichiers de configuration par les processus serveur
pg_rotate_logfile() boolean Impose une rotation du journal des traces du serveur

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 procpid 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™.

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.

Les fonctions présentées dans le Tableau 9.57, « Fonctions de contrôle de la sauvegarde » aident à l'exécution de sauvegardes à chaud. Ces fonctions ne peuvent pas être exécutées lors d'une restauration. L'utilisation des trois premières fonctions est réservée aux superutilisateurs.

Tableau 9.57. Fonctions de contrôle de la sauvegarde

Nom Type de retour Description
pg_start_backup (label text [, fast boolean ]) text Préparation de la sauvegarde à chaud
pg_stop_backup() text Arrêt de la sauvegarde à chaud
pg_switch_xlog() text Passage forcé à un nouveau journal de transactions
pg_current_xlog_location() text Récupération de l'emplacement d'écriture du journal de transactions courant
pg_current_xlog_insert_location() text Récupération de l'emplacement d'insertion du journal de transactions courant
pg_xlogfile_name_offset(location text) text, integer Conversion 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_xlogfile_name(location text) text Conversion de la chaîne décrivant l'emplacement du journal de transactions en nom de fichier

pg_start_backup accepte un label utilisateur de la sauvegarde (typiquement, le nom du fichier d'enregistrement de la sauvegarde). La fonction écrit un fichier de label (backup_label) dans le répertoire de données du cluster, réalise un point de retournement, et renvoie la position du début de la sauvegarde dans le journal de transactions au format texte. Ce résultat ne nécessite pas qu'on s'y intéresse, mais il est fourni dans cette éventualité.

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.

pg_stop_backup supprime le fichier de label créé par pg_start_backup et crée, à la place, un fichier d'historique dans l'aire de stockage des archives 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_xlog 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_xlog ne fait rien et renvoie l'emplacement de fin du journal de transactions en cours.

pg_current_xlog_location 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_xlog_insert_location affiche le point d'insertion dans le journal de transactions courant. 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. 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. Le point d'insertion n'est donné principalement 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_xlogfile_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_xlogfile_name_offset(pg_stop_backup());
        file_name         | file_offset 
--------------------------+-------------
 00000001000000000000000D |     4039624
(1 row)

De façon similaire, pg_xlogfile_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é.

Pour les détails sur le bon usage de ces fonctions, voir la Section 24.3, « Archivage continu et récupération d'un instantané (PITR) ».

Les fonctions affichées dans Tableau 9.58, « Fonctions d'information sur la restauration » 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.58. Fonctions d'information sur la restauration

Nom Type du retour Description
pg_is_in_recovery() bool True si la restauration est en cours.
pg_last_xlog_receive_location() text Ré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. Par contre, quand la réplication en flux est redémarrée, elle reviendra à la position de départ de la réplication, typiquement au début du journal de transactions contenant l'emplacement actuel de rejeu. 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_xlog_replay_location() text Ré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.

Les fonctions présentées dans le Tableau 9.59, « Fonctions de calcul de la taille des objets de la base de données » calculent l'utilisation de l'espace disque par les objets de la base de données.

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

Nom Code de retour Description
pg_column_size(any) int Nombre d'octets utilisés pour stocker une valeur particulière (éventuellement compressée)
pg_total_relation_size(regclass) bigint Espace disque total utilisé par la table dont l'OID ou le nom est indiqué, en incluant toutes les données TOAST et les index
pg_table_size(regclass) bigint Espace disque utilisé par la table dont l'OID ou le nom est indiqué, en excluant les index (mais en incluant les données TOAST, la carte des espaces libres et la carte de visibilité)
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_database_size(oid) bigint Espace disque utilisé par la base de données d'OID indiqué
pg_database_size(name) bigint Espace disque utilisé par la base de données de nom indiqué
pg_tablespace_size(oid) bigint Espace disque utilisé par le tablespace ayant cet OID
pg_tablespace_size(name) bigint Espace disque utilisé par le tablespace ayant ce nom
pg_relation_size(relation regclass, fork text) bigint Espace disque utilisé par le fork indiqué, 'main', 'fsm' ou 'vm', 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_pretty(bigint) text Convertit une taille en octets en format interprétable par l'utilisateur avec unités

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 renvoit l'espace disque total utilisé par cette table, incluant les index associés. Cette fonction est équivalenté à 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 renvoit 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 l'attribut CONNECT sur la base de données indiquée (qui est donné par défaut). Pour utiliser pg_tablespace_size, vous devez avoir l'attribut CREATE sur le tablespace indiqué, sauf s'il s'agit du tablespace par défaut de la base courante.

La fonction pg_relation_size accepte l'OID ou le nom d'une table, d'un index ou d'une table toast et renvoie la taille sur disque en octets d'une branche de la relation. (On note que, dans la majorité des cas, il est plus pratique d'utiliser les fonctions de plus haut niveau comme pg_total_relation_size ou pg_table_size qui réalisent la somme des branches.) Avec un seul argument, la fonction renvoit la taille de la branche principale des données de la relation. Un deuxième argument peut être fourni pour spécifier la branche à examiner  :

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

Les fonctions affichées dans Tableau 9.60, « Fonctions de récupération de l'emplacement des objets de la base de données » facilitent l'identification des fichiers associées aux objets de la base de données.

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

Nom Type en retour Description
pg_relation_filenode(relation regclass) oid Numéro filenode de la relation dont l'OID ou le nom est précisé
pg_relation_filepath(relation regclass) text Chemin et nom du fichier pour la relation dont l'OID ou le nom est précisé

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 renvoit 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 54.1, « Emplacement des fichiers de la base de données » 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 renvoit 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 renvoit le chemin complet vers le fichier (relatif au répertoire des données de l'instance, PGDATA) de la relation.

Les fonctions présentées dans le Tableau 9.61, « Fonctions d'accès générique aux fichiers » 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. 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. L'utilisation de ces fonctions est restreinte aux superutilisateurs.

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

Nom Code de retour Description
pg_ls_dir(nom_répertoire text) setof text Liste le contenu d'un répertoire
pg_read_file(nom_fichier text, décalage bigint, longueur bigint) text Renvoie le contenu d'un fichier texte
pg_stat_file(nom_fichier text) record Renvoie les informations concernant un fichier

pg_ls_dir renvoie tous les noms contenus dans le répertoire indiqué, à l'exception des entrées spéciales « . » et « .. ».

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.

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;

Les fonctions présentées dans Tableau 9.62, « Fonctions de verrous consultatifs » gèrent les verrous consultatifs. Pour les détails sur le bon usage de ces fonctions, voir Section 13.3.4, « Verrous informatifs ».

Tableau 9.62. Fonctions de verrous consultatifs

Nom Type renvoyé Description
pg_advisory_lock(key bigint) void Obtient un verrou consultatif exclusif
pg_advisory_lock(key1 int, key2 int) void Obtient un verrou consultatif exclusif
pg_advisory_lock_shared(key bigint) void Obtient un verrou consultatif partagé
pg_advisory_lock_shared(key1 int, key2 int) void Obtient un verrou consultatif partagé
pg_try_advisory_lock(key bigint) boolean Obtient un verrou consultatif exclusif si disponible
pg_try_advisory_lock(key1 int, key2 int) boolean Obtient un verrou consultatif exclusif si disponible
pg_try_advisory_lock_shared(key bigint) boolean Obtient un verrou consultatif partagé si disponible
pg_try_advisory_lock_shared(key1 int, key2 int) boolean Obtient un verrou consultatif partagé si disponible
pg_advisory_unlock(key bigint) boolean Relâche un verrou consultatif exclusif
pg_advisory_unlock(key1 int, key2 int) boolean Relâche un verrou consultatif exclusif
pg_advisory_unlock_shared(key bigint) boolean Relâche un verrou consultatif partagé
pg_advisory_unlock_shared(key1 int, key2 int) boolean Relâche un verrou consultatif partagé
pg_advisory_unlock_all() void Relâche tous les verrous consultatifs détenus par la session en cours

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). The key type is specified in pg_locks.objid. Si une autre session détient déjà un verrou sur la même 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 exclusif précédemment acquis. 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é.

pg_advisory_unlock_all relâche tous les verrous consultatifs 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.)