Vous appréciez mon travail ? Je serais ravi de prendre un café !

Vous prenez du plaisir à lire mes articles ? Vous apprenez de nouvelles choses ? Je serais ravis que vous supportiez mon travail avec une petite participation

1 café Merci, vous financez ma dose quotidienne de théïne (oui, en vrai je ne bois pas de café).
5 cafés Génial, ça couvre mes frais de serveur mensuels.
10 cafés Fantastique, avec ça je peux investir dans du matériel et approfondir mes connaissances.
BazinGa's - Tips & tuto IT

PostgreSQL – Vade-mecum SQL – 7/8 – Administration serveur

Voici quelques éléments pour vous aider dans l’administration de vos bases de données. En effet, il est possible de connaitre bon nombre d’information sur la BDD avec quelques requêtes.

Niveau serveur

Paramètres

-- Visualiser les paramètres du serveur :
SHOW mon_parametre;

-- Visualiser tous les paramètres :
SHOW ALL;

Les paramètre utilisables sont ceux qui se trouvent dans le fichier de configuration de PostgreSQL. Voici les principaux paramètres utilisables :

  • SERVER_VERSION : le numéro de version du serveur.
  • SERVER_ENCODING : l’encodage de la BDD.
  • LC_COLLATE : la locale utilisée pour la collation (ordonnancement des textes).
  • data_directory : emplacement du répertoire contenant les données de la BDD.
  • config_file : emplacement du fichier de configuration du cluster.
  • hba_file : emplacement du fichier de configuration d’accès au cluster.
  • listen_addresses : adresses IP écoutées par le serveur.
  • port : port utilisé par le serveur.
  • max_connections : nombre de connexions simultanées maximum.
  • shared_buffers : quantité de mémoire partagée allouée au server.
  • work_mem : quantité de mémoire utilisée par les opérations internes au serveur.
  • log_destination : type de logs.
  • log_directory : emplacement des logs.
  • log_filename : nom des fichiers de log.
  • log_rotation_age : durée avant rotation du log.
  • log_rotation_size : taille avant rotation du log.
  • log_min_messages : niveau de log.

Ce qui est intéressant, c’est que pour ces différent paramètres, vous pouvez les modifier également depuis une ligne de commande :

-- Pour la session courante
SET mon_parametre TO nouvelle_valeur;

-- Pour la transaction courante
SET LOCAL mon_parametre TO nouvelle_valeur;

Vous aurez bien sur remarqué qu’il s’agit des commandes que nous avons utilisé avec search_path dans le cours suivant : cliquez-ici.

Vous pouvez également appeler ces paramètres au moyen de la fonction current_setting('mon_parametre'). Voici un exemple :

-- Récupérer les principaux paramètres du serveur.
SELECT
	current_setting('config_file') as "config_file",
	current_setting('data_directory') as "data_directory",
	current_setting('hba_file') as "hba_file",
	current_setting('listen_addresses') as "listen_addresses",
	current_setting('log_directory') as "log_directory",
	current_setting('port') as "port",
	current_setting('search_path') as "search_path",
	current_setting('server_encoding') as "server_encoding",
	current_setting('server_version') as "server_version",
	current_setting('TimeZone') as "TimeZone"
;

Fonctions

Il existe un rand nombre de fonctions permettant de gérer ou de récupérer des informations sur la BDD.

Comme toute autre fonction, celles-ci s’utilisent dans un SELECT comme ceci :

-- Utilisation d'une fonction dans un SELECT
SELECT ma_fonction();

En voici une liste bien loin d’être exhaustive :

FonctionDescription
pg_reload_conf()Recharger la configuration du serveur
pg_column_size("ma_colonne")Poids d’une colonne en octet (Byte)
pg_database_size('nom_bdd')Poids d’une BDD en octet (Byte)
pg_total_relation_size('"mon_schema"."ma_table"')Poids totale d’une table (y compris index et toast) en octet (Byte)
pg_table_size('"mon_schema"."ma_table"')Poids d’une table (sans index, ni toast) en octet (Byte)
pg_size_pretty(valeur_numerique)Conversion des octets en valeur lisible (en B, kB, MB, GB…)
pg_relation_filepath('"mon_schema"."ma_table"')Emplacement des données d’une table sur le disque
pg_ls_dir('chemin/vers/repertoire') Lister le contenu d’un répertoire
pg_read_file('chemin/du/fichier.txt') Lire le contenu d’un fichier texte (renvoi du text)
pg_read_binary_file('chemin/du/fichier.xxx') Lire le contenu d’un fichier (renvoi du binaire)
pg_stat_file('chemin/du/fichier.xxx')Lire les informations d’un fichier

Niveau données

Les vues d’information système

Les tables d’information système PostgreSQL intègre de nombreuses tables et vues contenant des informations sur tous les objets de la base. En requêtant correctement ces données, vous pourrez obtenir de précieuses informations sur vos bases de données et vous faciliter la vie dans le cadre de migrations.

information_schema.columns 			-- Information sur les colonnes
information_schema.column_privileges 		-- Droits sur les colonnes
information_schema.tables 			-- Information sur les tables
information_schema.table_privileges 		-- Droits sur les tables
information_schema.views 			-- Information sur les vues
information_schema.sequences 			-- Information sur les séquences
information_schema.schemata 			-- Information sur les schema

Les tables systèmes

Les tables systèmes sont comme les vues d’information sauf qu’elles sont plus détaillées et plus rapide à interroger. Cependant, ceci elles sont plus difficilement lisibles car elles listes les objets par leurs oids et non par leurs noms.

pg_database 				-- Bases de données
pg_tablespace 				-- Tablespaces de la base
pg_tables				-- tables
pg_matviews				-- vues matérialisées
pg_views				-- vues
pg_indexes				-- index
pg_available_extensions			-- extensions disponibles
pg_file_settings			-- résumé du contenu des fichiers de configuration
pg_settings				-- configuration
pg_group				-- Groupe d'utilisateurs de la base de données
pg_roles				-- rôles des bases de données
pg_user					-- utilisateurs des bases de données
pg_shadow 				-- Utilisateurs
pg_authid 				-- Utilisateurs avec plein d'info
pg_stats				-- statistiques du planificateur
pg_stat_database 			-- Statistiques sur la base de données
pg_stat_all_tables 			-- Stat sur les tables
pg_class 				-- Tous les objets qui ressemblent de près ou de loin à une table

Plus de détails

Pour plus de détail sur vos données et le contenu de votre BDD, je vous invite à lire cet article sur l’audit des données :


Vous avez maintenant toutes les informations sur votre cluster. Nous allons maintenant voir les utilitaires en ligne de commande que nous pouvons utiliser avec le cluster.

Sommaire général

Voici le sommaire général du cours :


Cet article vous a plu ?

N'hésitez pas à le partager, il interessera surement certains de vos contacts.

Les thèmes suivants contiennent des articles en lien avec celui-ci, allez faire un tour :

BDDPostgreSQLProgrammationSQL tuto

50%