# Outils graphiques et console

> Transcription française complète du chapitre « Outils graphiques et console » du manuel DBA1, révision 26.09, pages PDF 195 à 278.

---

Index LLMS : [llms.txt](/fr/llms.txt)

---

## 4/ Outils graphiques et console

Contenu extrait du manuel complet, pages PDF 195 à 278.

## 4.1 PRÉAMBULE
Les outils graphiques et console :
  - les outils graphiques d’administration
  - la console
  - les outils de contrôle de l’activité
  - les outils DDL
  - les outils de maintenance
Ce module nous permet d’approcher le travail au quotidien du DBA et de l’utilisateur de la base de données. Nous verrons les différents outils disponibles, en premier lieu la console texte, psql .
### 4.1.1 Plan
  - Outils en ligne de commande de PostgreSQL
  - Réaliser des scripts
  - Outils graphiques

## 4.2 OUTILS CONSOLE DE POSTGRESQL
  - Plusieurs outils PostgreSQL en ligne de commande existent
  - une console interactive
  - des outils de maintenance
  - des outils de sauvegardes/restauration
  - des outils de gestion des bases
Les outils console de PostgreSQL que nous allons voir sont fournis avec la distribution de PostgreSQL. Ils permettent de tout faire : exécuter des requêtes manuelles, maintenir l’instance, sauvegarder et restaurer les bases.
### 4.2.1 Outils : Gestion des bases
**Tableau 4-1.** Tableau ou résultat de commande extrait du PDF\, page 197\.

```text
                -
                 -
                     createdb : ajouter une nouvelle base de données
                     createuser : ajouter un nouveau compte utilisateur
```
  - dropdb : supprimer une base de données
  - dropuser : supprimer un compte utilisateur
Chacune de ces commandes est un « alias », un raccourci qui permet d’exécuter certaines commandes SQL directement depuis le shell sans se connecter explicitement au serveur. L’option --echo permet de voir les ordres SQL envoyés. Par exemple, la commande système dropdb est équivalente à la commande SQL DROP DATABASE . L’outil dropdb se connecte à la base de données nommée postgres et exécute l’ordre SQL et enfin se déconnecte. La création d’une base se fait en utilisant l’outil createdb et en lui indiquant le nom de la nouvelle base, de préférence avec un utilisateur dédié. createuser crée ce que l’on appelle un « rôle », et appelle CREATE ROLE en SQL. Nous verrons plus loin les droits de connexion, de superutilisateur, etc. Une création de base depuis le shell peut donc ressembler à ceci :
**Exemple 4-2.** Exemple extrait du PDF\, page 197\.

```console
$ createdb --echo --owner erpuser erp_prod
SELECT pg_catalog.set_config('search_path', '', false);
CREATE DATABASE erp_prod OWNER erpuser;
```
Alors qu’une création de rôle peut ressembler à ceci :
**Exemple 4-3.** Exemple extrait du PDF\, page 197\.

```console
$ createuser --echo --login --no-superuser erpuser
SELECT pg_catalog.set_config('search_path', '', false);
CREATE ROLE erpuser NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
```

Et si le pg_hba.conf le permet :
**Exemple 4-4.** Exemple extrait du PDF\, page 198\.

```console
$ psql -U erpuser erp_prod           < script_installation.sql
```
### 4.2.2 Outils : Sauvegarde / Restauration
- Sauvegarde logique, pour une instance
- pg_dumpall : sauvegarder l’instance PostgreSQL
- Sauvegarde logique, pour une base de données
- pg_dump : sauvegarder une base de données
- pg_restore : restaurer une base de données PostgreSQL
- Sauvegarde physique :
- pg_basebackup
- avec : pg_verifybackup et pg_combinebackup (v17)
Ces commandes sont essentielles pour assurer la sécurité des données du serveur. Sauvegardes logiques : pg_dumpall et pg_dump réalisent des sauvegardes logiques des données, donc au niveau des objets logiques (tables, index, etc). Leur utilisation est détaillée dans le module de formation I1 - Sauvegarde et restauration 1 . Comme son nom l’indique, pg_dumpall sauvegarde l’instance complète, autrement dit toutes les bases mais aussi les objets globaux. Il est cependant possible d’exclure une ou plusieurs bases de cette sauvegarde. Pour ne sauvegarder qu’une seule base, il est préférable de passer par l’outil pg_dump , qui possède plus d’options. Il faut évidemment lui fournir le nom de la base à sauvegarder. Pour sauvegarder notre base b1, il suffit de lancer la commande suivante :
**Exemple 4-5.** Exemple extrait du PDF\, page 198\.

```console
$ pg_dump -f b1.sql b1
```
Restaurations de sauvegardes logiques : Pour la restauration d’une sauvegarde, l’outil habituel est pg_restore . psql est utilisé pour la restauration d’une sauvegarde faite en mode texte (script SQL). Sauvegardes physiques : La sauvegarde physique (donc au niveau des fichiers) à chaud est possible avec pg_basebackup , qui copie les fichiers d’une instance en fonctionnement, journaux de transaction inclus. Son fonctionnement est nettement plus complexe qu’un simple pg_dump . pg_basebackup est utilisé par les outils de sauvegarde PITR et pour créer des serveurs secondaires. À partir de PostgreSQL 17, pg_basebackup sait effectuer des sauvegardes incrémentales, à recombiner avec pg_combinebackup .
1 https://dali.bo/i1_html

pg_verifybackup permet de vérifier l’intégrité d’une sauvegarde réalisée avec pg_basebackup .
Les sauvegardes physiques sont traitées dans les modules de formation I2 - Point In Time Recovery 2 et I4 - Outils de sauvegarde physique 3 .
### 4.2.3 Outils : Maintenance
- Maintenance des bases
- vacuumdb : récupérer l’espace inutilisé, statistiques
- clusterdb : réorganiser une table en fonction d’un index
- reindexdb : réindexer
reindexdb , là encore, est un alias lançant des ordres REINDEX . Une réindexation périodique des index peut être utile. Par exemple, pour lancer une réindexation de la base b1 en affichant la commande exécutée :
**Exemple 4-6.** Exemple extrait du PDF\, page 199\.

```console
$ reindexdb --echo --concurrently
SELECT pg_catalog.set_config('search_path', '', false);
REINDEX DATABASE CONCURRENTLY b1;
```
vacuumdb permet d’exécuter les différentes variantes du VACUUM ( FULL , ANALYZE , FREEZE …) depuis le shell, principalement le nettoyage des lignes mortes, la mise à jour des statistiques sur les données, et la reconstruction de tables. L’usage est ponctuel, le démon autovacuum s’occupant de cela en temps normal. clusterdb lance un ordre CLUSTER , soit une reconstruction de la table avec tri selon un index. L’usage est très spécifique. Rappelons que ces opérations posent des verrous qui peuvent être très gênants sur une base active.
### 4.2.4 Outils : Maintenance de l’instance
**Tableau 4-2.** Tableau ou résultat de commande extrait du PDF\, page 199\.

```text
                      -
                       -
                               initdb : création d’instance
                               pg_ctl : lancer, arrêter, relancer, promouvoir l’instance
```
    - pg_upgrade : migrations majeures
    - pg_config , pg_controldata : configuration
2 https://dali.bo/i2_html

3 https://dali.bo/i4_html

Ces outils sont rarement utilisés directement, car on passe généralement par les outils du système d’exploitation et ceux fournis par les paquets, qui les utilisent. Ils peuvent toutefois servir et il faut les connaître. initdb crée une instance, c’est-à-dire crée tous les fichiers nécessaires dans le répertoire indiqué ( PGDATA ). Les options permettent d’affecter certains paramètres par défaut. La plus importante (car
**Exemple 4-7.** Exemple extrait du PDF\, page 200\.

```text
on ne peut corriger plus tard qu’à condition que l’instance soit arrêtée, donc en arrêt de production)
```
est l’option --data-checksums activant les sommes de contrôle, dont l’activation est généralement conseillée. Cette activation est réalisée par défaut à partir de la version 18. pg_ctl est généralement utilisé pour démarrer/arrêter une instance, pour recharger les fichiers de configuration après modification, ou pour promouvoir une instance secondaire en primaire. Toutes les actions possibles sont documentées ici 4 . pg_upgrade est utilisée pour convertir une instance existante lors d’une migration entre versions majeures. L’instance à migrer doit être en version 9.2 au moins. Pour des versions plus anciennes, pg_upgrade en version 14 accepte encore de migrer des bases remontant à la 8.4.
pg_config fournit des informations techniques sur les binaires installés (chemins notamment).
pg_controldata fournit des informations techniques de base sur une instance.
### 4.2.5 Autres outils en ligne de commande
- pgbench pour des tests
- Outils liés à la réplication/sauvegarde physique, aux tests, analyse…
pgbench est l’outil de base pour tester la charge et l’influence de paramètres. Créez les tables de travail avec l’option -i , fixez la volumétrie avec -s , et lancez pgbench en précisant le nombre de clients, de transactions… L’outil vous calcule le nombre de transactions par secondes et diverses informations statistiques. Les requêtes utilisées sont basiques mais vous pouvez fournir les vôtres. D’autres outils sont liés à l’archivage ( pg_receivewal ) et/ou à la réplication par log shipping ( pg_archivecleanup ) ou logique ( pg_recvlogical ), au sauvetage d’instances secondaires ( pg_rewind ), à la vérification de la disponibilité ( pg_isready ), à des tests de la configuration matérielle ( pg_test_fsync , pg_test_timing ), ou d’intégrité ( pg_checksums ), à l’analyse des journaux de transactions ( pg_waldump ).
4 https://www.postgresql.org/docs/current/app-pg-ctl.html

## 4.3 CHAÎNES DE CONNEXION
Pour se connecter à une base :
  - paramètres propres aux outils
  - via la libpq
    - variables d’environnement
    - par chaînes clés/valeur
    - par chaînes URI
    - idem en Python,PHP,Perl
  - JDBC/.NET/ODBC ont des syntaxes spécifiques
Les types de connexion connus de PostgreSQL et de sa librairie cliente (libpq) sont, au choix, les paramètres explicites (assez variables suivants les outils), les variables d’environnement, les chaînes clés/valeur, et les URI ( postgresql://… ). Les pilotes PHP 5 …), Perl 6 , ou Python 7 utilisent la libpq, comme bien sûr les outils du projet PostgreSQL, et ce qui suit est directement utilisable. Nous ne traiterons pas ici des syntaxes propres aux outils n’utilisant pas la libpq, comme les pilotes JDBC 8 (dont les URI sont proches mais différente de celles décrites plus bas) ou .NET 9 , ou encore les différents pilotes ODBC (comme psqlODBC 10 , très limité).
### 4.3.1 Paramètres
Outils habituels, et très souvent :
**Exemple 4-8.** Exemple extrait du PDF\, page 201\.

```console
              $ psql -h serveur -d mabase -U nom -p 5432
```
Option Variable Valeur par défaut
-h HÔTE $PGHOST /tmp , /var/run/postgresql -p PORT $PGPORT 5432 -U NOM $PGUSER nom de l’utilisateur OS -d base $PGDATABASE nom de l’utilisateur PG $PGOPTIONS options de connexions
5 https://www.php.net/manual/en/function.pg-connect.php

6 https://metacpan.org/pod/DBD::Pg#data_sources

7 https://www.psycopg.org/docs/module.html#psycopg2.connect

8 https://jdbc.postgresql.org/documentation/head/connect.html

9 https://www.npgsql.org/doc/connection-string-parameters.html

10 https://odbc.postgresql.org/

Option Variable Valeur par défaut
Les options de connexion permettent d’indiquer comment trouver l’instance (serveur, port), puis d’indiquer l’utilisateur et la base de données concernés parmi les différentes de l’instance. Ces deux derniers champs doivent passer le filtre du pg_hba.conf du serveur pour que la connexion réussisse. Lorsque l’une de ces options n’est pas précisée, la bibliothèque cliente PostgreSQL cherche une variable d’environnement correspondante et prend sa valeur. Si elle ne trouve pas de variable, elle se rabat sur une valeur par défaut. Les paramètres et variables d’environnement qui suivent sont utilisés par les outils du projet, et de nombreux autres outils de la communauté. La problématique du mot de passe est laissée de côté pour le moment. Hôte : Le paramètre `-h <hôte>` ou la variable d’environnement $PGHOST permettent de préciser le nom ou l’adresse IP de la machine qui héberge l’instance. Sans précision, sur Unix, le client se connecte sur la socket Unix, généralement dans /var/run/postgresql (défaut sur Debian et Red Hat) ou /tmp (défaut de la version compilée). Le réseau n’est alors pas utilisé, et il y a donc une différence entre -h localhost (via ::1 ou 127.0.0.1 donc) et -h /var/run/postgresql (défaut), ce qui peut avoir un résultat différent selon la configuration du pg_hba.conf . Par défaut, l’accès par le réseau exige un mot de passe. Sous Windows, le comportement par défaut est de se connecter à localhost. Serveur : `-p <port>` ou $PGPORT permettent de préciser le port sur lequel l’instance écoute les connexions entrantes. Sans indication, le port par défaut est le 5432. Utilisateur : `-U <nom>` ou $PGUSER permettent de préciser le nom de l’utilisateur, connu de PostgreSQL, qui doit avoir été créé préalablement sur l’instance. Sans indication, le nom d’utilisateur PostgreSQL est le nom de l’utilisateur système connecté. Base de données : -d base ou $PGDATABASE permettent de préciser le nom de la base de données utilisée pour la connexion. Sans précision, le nom de la base de données de connexion sera celui de l’utilisateur PostgreSQL (qui peut découler de l’utilisateur connecté au système). Exemples :
- Chaîne de connexion classique, implicitement au port 5432 en local sur le serveur :
**Exemple 4-9.** Exemple extrait du PDF\, page 202\.

```console
$ psql -U jeanpierre -d comptabilite
```

- Connexion à un serveur distant pour une sauvegarde :
**Exemple 4-10.** Exemple extrait du PDF\, page 203\.

```console
$ pg_dump -h serveur3 -p 5435 -U postgres -d basecritique -f fichier.dump
```
- Connexion sans paramètre via l’utilisateur système postgres, et donc implicitement en tant qu’utilisateur postgres de l’instance à la base postgres (qui existent généralement par défaut).
**Exemple 4-11.** Exemple extrait du PDF\, page 203\.

```console
$ sudo -iu postgres     psql
```
Dans les configurations par défaut courantes, cette commande est généralement la seule qui fonctionne sur une instance fraîchement installée.
- Utilisation des variables d’environnement pour alléger la syntaxe dans un petit script de maintenance :
**Exemple 4-12.** Exemple extrait du PDF\, page 203\.

```console
#! /bin/bash
export PGHOST=/var/run/postgresql
export PGPORT=5435
export PGDATABASE=comptabilite
export PGUSER=superutilisateur
# liste des bases
psql -l
# nettoyage
```
vacuumdb
**Exemple 4-13.** Exemple extrait du PDF\, page 203\.

```text
# une sauvegarde
pg_dump -f fichier.dump
```
Raccourcis Généralement, préciser -d n’est pas nécessaire quand la base de données est le premier argument non nommé de la ligne de commande. Par exemple :
**Exemple 4-14.** Exemple extrait du PDF\, page 203\.

```console
$ psql -U jeanpierre comptabilite

$ sudo -iu postgres     vacuumdb nomdelabase
```
Il faut faire attention à quelques différences entre les outils : pour pgbench , -d désigne le mode debug et le nom de la base est un argument non nommé ; alors que pour pg_restore , le -d est nécessaire pour restaurer vers une base. Variable d’environnement PGOPTIONS La variable d’environnement $PGOPTIONS permet de positionner la plupart des paramètres de sessions disponibles, qui surchargent les valeurs par défaut. Par exemple, pour exécuter une requête avec un paramétrage différent de work_mem (mémoire de tri) :
**Exemple 4-15.** Exemple extrait du PDF\, page 203\.

```console
$ PGOPTIONS="-c work_mem=100MB" psql -p 5433 -h serveur nombase             < grosse_requete.sql
```
ou pour importer une sauvegarde sans être freiné par un serveur secondaire synchrone :
**Exemple 4-16.** Exemple extrait du PDF\, page 203\.

```console
$ PGOPTIONS="-c synchronous_commit=local" pg_restore -d nombase sauvegarde.dump
```

### 4.3.2 Autres variables d’environnement
  - $PGAPPNAME
    - $PGSSLMODE
    - $PGPASSWORD
    - …
Il existe aussi :
- $PGSSLMODE pour définir le niveau de chiffrement SSL de la connexion avec les valeurs disable , prefer (le défaut), require … (il existe d’autres variables d’environnement pour une gestion fine du SSL);
- $PGAPPNAME permet de définir une chaîne de caractère arbitraire pour identifier la connexion dans les outils et vues d’administration (paramètre de session application_name ) : mettez-y par exemple le nom du script;
- $PGPASSWORD peut stocker le mot de passe pour ne pas avoir à l’entrer à la connexion (voir plus loin).
Toutes ces variables d’environnement, ainsi que de nombreuses autres, et leurs diverses valeurs possibles, sont documentées 11 .
### 4.3.3 Chaînes libpq clés/valeur
**Exemple 4-17.** Exemple extrait du PDF\, page 204\.

```text
               psql "host=serveur1       user=jeanpierre        dbname=comptabilite"

                psql -d "host=serveur1 port=5432 user=jeanpierre dbname=comptabilite
                     sslmode=require application_name='chargement'
                     options='-c work_mem=30MB' "
```
En lieu du nom de la base, une chaîne peut inclure tous les paramètres nécessaires. Cette syntaxe permet de fournir plusieurs paramètres d’un coup. Les paramètres disponibles sont listés dans la documentation 12 . On retrouvera de nombreux paramètres équivalents aux variables d’environnement 13 , ainsi que d’autres. Dans cet exemple, on se connecte en exigeant le SSL, en positionnant application_name pour mieux repérer la session, et en modifiant la mémoire de tri, ainsi que le paramétrage de la synchronisation sur disque pour accélérer les choses :
11 https://docs.postgresql.fr/current/libpq-envars.html

12 https://docs.postgresql.fr/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS

13 https://docs.postgresql.fr/current/libpq-envars.html

**Exemple 4-18.** Exemple extrait du PDF\, page 205\.

```console
$ psql "host=serveur1 port=5432 user=jeanpierre dbname=comptabilite
       sslmode=require application_name='chargement'
       options='-c work_mem=99MB' -c synchronous_commit=off' " \
       < script_chargement.sql
```
Tous les outils de l’écosystème ne connaissent pas cette syntaxe (par exemple pgCluu).
### 4.3.4 Chaînes URI
**Exemple 4-19.** Exemple extrait du PDF\, page 205\.

```text
             psql -d "postgresql://jeanpierre@serveur1:5432/comptabilite"

              psql \
              "postgres://jeanpierre@serveur1/comptabilite?sslmode=require\
              &options=-c%20synchronous_commit%3Doff"

              psql -d postgresql://serveur1/comptabilite?user=jeanpierre\&port=5432
```
Une autre possibilité existe : des chaînes sous forme URI comme il en existe pour beaucoup de pilotes et d’outils. La syntaxe est de la forme :
postgresql://[user[:motdepasse]@][lieu][:port][/dbname][?param1=valeur21&param2=valeur2…]
Là encore cette chaîne remplace le nom de la base dans les outils habituels. postgres:// et postgresql:// sont tous deux acceptés.
Cette syntaxe est très souple. Une difficulté réside dans la gestion des caractères spéciaux, signes = et des espaces :
**Exemple 4-20.** Exemple extrait du PDF\, page 205\.

```console
# Ces deux appels sont équivalents
$ psql -d postgresql:///var/lib/postgresql?dbname=pgbench\&user=pgbench\&port=5436
$ psql -h /var/lib/postgresql -d pgbench -U pgbench -p 5436

# Ces deux appels sont équivalents
$ psql "postgresql://bob@vm1/proddb?&options=-c%20synchronous_commit%3Doff"
$ psql -U bob -h vm1 -d proddb -c 'synchronous_commit=off'
```
Tous les outils de l’écosystème ne connaissent pas cette syntaxe (par exemple pgCluu).

### 4.3.5 Connexion avec choix automatique du serveur
**Exemple 4-21.** Exemple extrait du PDF\, page 206\.

```text
                psql "host=serveur1,serveur2,serveur3
                       port=5432,5433,5434
                       user=jeanpierre dbname=comptabilite
                       target_session_attrs=read-write
                       load_balance_hosts=random"   # v16
```
Il est possible d’indiquer plusieurs hôtes et ports. Jusque PostgreSQL 15 inclus, l’hôte sélectionné est le premier qui répond avec les conditions demandées. À partir de PostgreSQL 16, on peut ajouter `load_balance_hosts=random` pour que la libpq choisisse un serveur au hasard, pour une répartition de charge basique, mais très simple à mettre en place.
Si l’authentification ne passe pas, la connexion tombera en erreur. Il est possible de préciser si la connexion doit se faire sur un serveur en lecture/écriture ou en lecture seule grâce au paramètre target_session_attrs .
Par exemple, on se connectera ainsi au premier serveur de la liste ouvert en écriture et disponible parmi les trois précisés :
**Exemple 4-23.** Exemple extrait du PDF\, page 206\.

```text
psql postgresql://jeanpierre@serveur1:5432,serveur2:5433,serveur3:5434/\
```
comptabilite?target_session_attrs=read-write
qui équivaut à :
**Exemple 4-24.** Exemple extrait du PDF\, page 206\.

```text
psql "host=serveur1,serveur2,serveur3 port=5432,5433,5434
      target_session_attrs=read-write user=jeanpierre dbname=comptabilite"
```
Dans le but de faciliter la connexion aux différents serveurs d’une grappe, target_session_attrs possède d’autres options 14 que read-write , à savoir : any , read-only , primary , standby , prefer-standby . Par exemple, la commande suivante permet de se connecter à un secondaire au hasard parmi ceux qui répondent. serveur3 est doublé pour prendre une charge double des autres. S’il ne reste plus que le primaire, la connexion se fera à celui-ci.
**Exemple 4-25.** Exemple extrait du PDF\, page 206\.

```text
psql "host=serveur1,serveur2,serveur3,serveur3 port=5432,5433,5434,5434
      target_session_attrs=prefer-standby
      load_balance_hosts=random
      user=jeanpierre dbname=comptabilite"
```
14 https://docs.postgresql.fr/current/libpq-connect.html#LIBPQ-CONNECT-TARGET-SESSION-ATTRS

### 4.3.6 Authentification d’un client (outils console)
  - En interactif ( psql )
**Tableau 4-3.** Tableau ou résultat de commande extrait du PDF\, page 207\.

```text
                     - -W | --password
                     - -w | --no-password
```
  - Variable $PGPASSWORD
  - À préférer : fichier .pgpass
    - chmod 600 .pgpass
    - nom_hote:port:database:nomutilisateur:motdepasse
Options -W et -w de psql L’option -W oblige à saisir le mot de passe de l’utilisateur. C’est le comportement par défaut si le serveur demande un mot de passe. Si les accès aux serveurs sont configurés sans mot de passe et que cette option est utilisée, le mot de passe sera demandé et fourni à PostgreSQL lors de la demande de connexion. Mais PostgreSQL ne vérifiera pas s’il est bon si la méthode d’authentification ne le réclame pas. L’option -w empêche la saisie d’un mot de passe. Si le serveur a une méthode d’authentification qui nécessite un mot de passe, ce dernier devra être fourni par le fichier .pgpass ou par la variable d’environnement $PGPASSWORD . Dans tous les autres cas, la connexion échoue. Variable $PGPASSWORD Si psql détecte une variable $PGPASSWORD initialisée, il se servira de son contenu comme mot de passe qui sera soumis pour l’authentification du client. Fichier .pgpass Le fichier .pgpass , situé dans le répertoire personnel de l’utilisateur ou celui référencé par $PGPASSFILE , est un fichier contenant les mots de passe à utiliser si la connexion requiert un mot de passe (et si aucun mot de passe n’a été spécifié). Sur Microsoft Windows, le fichier est nommé %APPDATA%\postgresql\pgpass.conf (où %APPDATA% fait référence au sous-répertoire Application Data du profil de l’utilisateur).
Ce fichier devra être composé de lignes du format : nom_hote:port:nom_base:nom_utilisateur:mot_de_passe
Chacun des quatre premiers champs pourraient être une valeur littérale ou * (qui correspond à tout). La première ligne réalisant une correspondance pour les paramètres de connexion sera utilisée (du coup, placez les entrées plus spécifiques en premier lorsque vous utilisez des jokers). Si une entrée a besoin de contenir * ou \ , échappez ce caractère avec \ . Un nom d’hôte localhost correspond à la fois aux connexions host ( TCP ) et aux connexions local ( socket de domaine Unix) provenant de la machine locale.

Les droits sur .pgpass doivent interdire l’accès aux autres et au groupe; réalisez ceci avec la commande :
**Exemple 4-26.** Exemple extrait du PDF\, page 208\.

```text
chmod 0600 ~/.pgpass
```
Attention : si les droits du fichier sont moins stricts, le fichier sera ignoré!
Les droits du fichier ne sont actuellement pas vérifiés sur Microsoft Windows.

## 4.4 LA CONSOLE PSQL
  - Un outil simple pour
  - les opérations courantes
  - les tâches de maintenance
  - l’exécution des scripts
  - les tests
postgres$ psql
**Exemple 4-27.** Exemple extrait du PDF\, page 209\.

```text
                base=#
```
La console psql permet d’effectuer l’ensemble des tâches courantes d’un utilisateur de bases de données. Si ces tâches peuvent souvent être effectuées à l’aide d’un outil graphique, la console présente l’avantage de pouvoir être utilisée en l’absence d’environnement graphique ou de scripter les opérations à effectuer sur la base de données. Elle a la garantie d’être également toujours disponible. Nous verrons également qu’il est possible d’administrer la base de données depuis cette console. Enfin, elle permet de tester l’activité du serveur, l’existence d’une base, la présence d’un langage de programmation…
### 4.4.1 Obtenir de l’aide et quitter
  - Obtenir de l’aide sur les commandes internes psql
  - \?
  - Obtenir de l’aide sur les ordres SQL
  - `\h <COMMANDE>`
  - Quitter
  - \q , ctrl-D , quit , exit
\? liste les commandes propres à psql (par exemple \d ou \du pour voir les tables ou les utilisateurs), trop nombreuses pour pouvoir être mémorisées. `\h <COMMANDE>` affiche l’aide en ligne des commandes SQL, y compris l’URL vers la page web documentant cette commande. Sans argument, la liste des commandes disponibles est affichée. Exemple :
**Exemple 4-28.** Exemple extrait du PDF\, page 209\.

```console
postgres=# \h ALTER TA
```
Commande : ALTER TABLE Description : modifier la définition d'une table Syntaxe :

**Exemple 4-29.** Exemple extrait du PDF\, page 210\.

```sql
ALTER TABLE [ IF EXISTS ] [ ONLY ] nom [ * ]
    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] nom [ * ]
```
… URL: https://www.postgresql.org/docs/15/sql-altertable.html
Pour quitter psql , il y a plusieurs moyens : Ctrl-D ou les commandes quit , exit et \q .
### 4.4.2 Gestion de la connexion
  - Modifier le mot de passe d’un utilisateur
  - \password nomutilisateur
  - Quelle est la connexion courante?
  - \conninfo
  - SELECT current_user,session_user,system_user;
  - Se connecter à une autre base :
  - \c ma base
  - \c mabase utilisateur serveur 5432
\c permet de changer d’utilisateur et/ou de base de données sans quitter le client. \conninfo permet de savoir où l’on est connecté. Exemple :
**Exemple 4-30.** Exemple extrait du PDF\, page 210\.

```console
CREATE DATABASE formation;
CREATE DATABASE prod;
CREATE USER stagiaire1;
CREATE USER stagiaire2;
CREATE USER admin;

postgres=# \c formation stagiaire1
```
You are now connected to database "formation" as user "stagiaire1".
**Exemple 4-31.** Exemple extrait du PDF\, page 210\.

```text
formation=> \c - stagiaire2
```
You are now connected to database "formation" as user "stagiaire2".
**Exemple 4-32.** Exemple extrait du PDF\, page 210\.

```text
formation=> \c prod admin
```
You are now connected to database "prod" as user "admin".
**Exemple 4-33.** Exemple extrait du PDF\, page 210\.

```text
prod=> \conninfo
```
You are connected to database "prod" as user "admin"
**Exemple 4-34.** Exemple extrait du PDF\, page 210\.

```text
                  on host "localhost" (address "::1") at port "5412".
               Connection Information
```
**Tableau 4-4.** Tableau ou résultat de commande extrait du PDF\, page 210\.

```text
      Parameter       |   Value
----------------------+-----------
 Database             | prod
```

**Tableau 4-5.** Tableau ou résultat de commande extrait du PDF\, page 211\.

```text
 Client User          | admin
 Host                 | localhost
 Host Address         | ::1
 Server Port          | 5412
 Options              |
 Protocol Version     | 3.0
 Password Used        | true
 GSSAPI Authenticated | false
 Backend PID          | 845483
 SSL Connection       | false
 Superuser            | on
 Hot Standby          | off
```
(13 rows)
Au niveau SQL, un équivalent partiel de \conninfo serait :
**Exemple 4-35.** Exemple extrait du PDF\, page 211\.

```sql
SELECT   current_user,
         current_catalog,                                 -- base
         inet_server_addr(), inet_server_port(),          -- serveur
         system_user                                      -- depuis PG16
         \gx

-[ RECORD 1 ]----+-----------------------
```
**Tableau 4-6.** Tableau ou résultat de commande extrait du PDF\, page 211\.

```text
current_user     | dalibo
current_catalog | pgbench
inet_server_addr | 192.168.74.5
inet_server_port | 5433
system_user      | scram-sha-256:postgres
```
Un superutilisateur pourra affecter un mot de passe à un autre utilisateur grâce à la commande \password , qui en fait encapsule un ALTER USER … PASSWORD … . Le gros intérêt de \password est d’envoyer le mot de passe chiffré au serveur. Ainsi, même si les traces contiennent toutes les requêtes SQL exécutées, il est impossible de retrouver les mots de passe via le fichier de traces. Ce n’est pas le cas avec un CREATE ROLE ou un ALTER ROLE manuel (à moins de chiffrer soi-même le mot de passe).
### 4.4.3 Catalogue système : objets utilisateurs
Lister :
  - les bases de données
    - \l , \l+
  - les tables
    - \d , \d+ , \dt , \dt+
  - les index
    - \di , \di+
  - les schémas
    - \dn
  - les fonctions & procédures
    - \df[+]
  - etc…

Ces commandes permettent d’obtenir des informations sur les objets utilisateurs de tout type stockés dans la base de données. Pour les commandes qui acceptent un motif, celui-ci permet de restreindre les résultats retournés à ceux dont le nom d’opérateur correspond au motif précisé. Le client psql en version 15 est compatible avec toutes les versions du serveur depuis la 9.2 incluse. \l dresse la liste des bases de données sur le serveur. Avec \l+ , les commentaires, les tablespaces par défaut et les tailles des bases sont également affichés, ce qui peut être très pratique. \dt affiche les tables, \di les index, \dn les schémas, \ds les séquences, \dv les vues, etc. Là encore, on peut ajouter + pour d’autres informations comme la taille, et même S pour inclure les objets système normalement masqués. La version 18 propose aussi le caractère optionnel x pour un affichage étendu. Par exemple, \dtx . Exemple : Si l’on crée une simple base grâce à pgbench avec un utilisateur dédié :
**Exemple 4-36.** Exemple extrait du PDF\, page 212\.

```console
$ psql

=# CREATE ROLE testeur LOGIN ;
=# \password testeur
Saisir le nouveau mot de passe :
Saisir le mot de passe à nouveau :

=# \du
```
Utilisateurs en place :
**Tableau 4-7.** Tableau ou résultat de commande extrait du PDF\, page 212\.

```text
          Nom du rôle        |         Attributs       |       Membre de
-----------------------------+-------------------------+-------------------
 dalibo                      |                         | {}
 nagios                      | Superutilisateur        | {}
 postgres                    | Superutilisateur, (...) | {}
 testeur                     |                         | {}
```
Création de la base :
**Exemple 4-37.** Exemple extrait du PDF\, page 212\.

```sql
CREATE DATABASE pgbench OWNER testeur ;
```
Bases de données en place : =# \l Liste des bases de données
**Tableau 4-8.** Tableau ou résultat de commande extrait du PDF\, page 212\.

```text
      Nom       | Propriétaire | Encodage | Collationnement | Type caract. | …
----------------+--------------+----------+-----------------+--------------+----
 pgbench        | testeur      | UTF8     | fr_FR.UTF-8     | fr_FR.UTF-8 |
 postgres       | postgres     | UTF8     | fr_FR.UTF-8     | fr_FR.UTF-8 |
```
Création des tables de cette nouvelle base :
**Exemple 4-38.** Exemple extrait du PDF\, page 212\.

```console
$ pgbench --initialize --scale=10 -U testeur -h localhost pgbench
```
Connexion à la nouvelle base :
**Exemple 4-39.** Exemple extrait du PDF\, page 212\.

```console
$ psql -h localhost -U testeur -d pgbench
```

Les tables : =# \d Liste des relations
**Tableau 4-9.** Tableau ou résultat de commande extrait du PDF\, page 213\.

```text
 Schéma |       Nom        | Type | Propriétaire
--------+------------------+-------+--------------
 public | pgbench_accounts | table | testeur
 public | pgbench_branches | table | testeur
 public | pgbench_history | table | testeur
 public | pgbench_tellers | table | testeur
```
(4 lignes)
=# \dt+ pgbench_*s Liste des relations
**Tableau 4-10.** Tableau ou résultat de commande extrait du PDF\, page 213\.

```text
 Schéma |       Nom        | Type | Propriétaire | Persistence | M… | Taille |
--------+------------------+-------+--------------+-------------+------+--------+--
 public | pgbench_accounts | table | testeur      | permanent   | heap | 128 MB |
 public | pgbench_branches | table | testeur      | permanent   | heap | 40 kB |
 public | pgbench_tellers | table | testeur       | permanent   | heap | 40 kB |
```
(3 lignes)
Une table (affichage réduit pour des raisons de mise en page) : =# \d+ pgbench_accounts Table « public.pgbench_accounts »
**Tableau 4-11.** Tableau ou résultat de commande extrait du PDF\, page 213\.

```text
 Colonne |      Type      | Col..nt | NULL-able | … | Stockage | Compr. | …
----------+---------------+---------+-----------+---+----------+--------+--
 aid      | integer       |         | not null |    | plain    |        |
 bid      | integer       |         |           |   | plain    |        |
 abalance | integer       |         |           |   | plain    |        |
 filler   | character(84) |         |           |   | extended |        |
```
Index : "pgbench_accounts_pkey" PRIMARY KEY, btree (aid) Méthode d'accès : heap Options: fillfactor=100
Les index : => \di Liste des relations
**Tableau 4-12.** Tableau ou résultat de commande extrait du PDF\, page 213\.

```text
 Schéma |          Nom          | Type | Propriétaire |       Table
--------+-----------------------+-------+--------------+------------------
 public | pgbench_accounts_pkey | index | testeur      | pgbench_accounts
 public | pgbench_branches_pkey | index | testeur      | pgbench_branches
 public | pgbench_tellers_pkey | index | testeur       | pgbench_tellers
```
(3 lignes)
Les schémas, utilisateur ou système : => \dn+ Liste des schémas
**Tableau 4-13.** Tableau ou résultat de commande extrait du PDF\, page 213\.

```text
  Nom   | Propriétaire |    Droits d´accès    |      Description
--------+--------------+----------------------+------------------------
 public | postgres     | postgres=UC/postgres+| standard public schema
        |              | =UC/postgres         |
```
(1 ligne)
=> \dnS

Liste des schémas
**Tableau 4-14.** Tableau ou résultat de commande extrait du PDF\, page 214\.

```text
        Nom          | Propriétaire
--------------------+--------------
 information_schema | postgres
 pg_catalog          | postgres
 pg_toast            | postgres
 public              | postgres
```
(4 lignes)
Les tablespaces (ici ceux par défaut) :
=> \db Liste des tablespaces
**Tableau 4-15.** Tableau ou résultat de commande extrait du PDF\, page 214\.

```text
    Nom     | Propriétaire | Emplacement
------------+--------------+-------------
 pg_default | postgres     |
 pg_global | postgres      |
```
(2 lignes)
Les tablespaces (toujours par défaut) en mode étendu :
=> \dbx List of tablespaces
**Tableau 4-16.** Tableau ou résultat de commande extrait du PDF\, page 214\.

```text
Name     | pg_default
Owner    | postgres
Location |
```
**Exemple 4-40.** Exemple extrait du PDF\, page 214\.

```text
-[ RECORD 1 ]--------
-[ RECORD 2 ]--------
```
**Tableau 4-17.** Tableau ou résultat de commande extrait du PDF\, page 214\.

```text
Name     | pg_global
Owner    | postgres
Location |
```
### 4.4.4 Catalogue système : rôles et accès
  - Lister les rôles (utilisateurs et groupes)
    - \du[+]
  - Lister les droits d’accès
    - \dp
  - Lister les droits d’accès par défaut
    - \ddp
    - ALTER DEFAULT PRIVILEGES
  - Lister les configurations par rôle et par base
    - \drds
On a vu que `\du` (`u` pour _user_) affiche les rôles existants. Rappelons qu’un rôle peut être aussi bien un utilisateur (si le rôle a le droit de `LOGIN`) qu’un groupe d’utilisateurs, voire les deux à la fois.

Dans les versions antérieures à la 8.1, il n’y avait pas de rôles, et les groupes et les utilisateurs étaient deux notions distinctes. Certaines commandes ont conservé le terme de user, mais il s’agit bien de rôles dans tous les cas.
Les droits sont accessibles par les commandes \dp ( p pour « permissions ») ou \z . Dans cet exemple, le rôle admin devient membre du rôle système pg_signal_backend : =# GRANT pg_signal_backend TO admin;
=# \du List of roles
**Tableau 4-18.** Tableau ou résultat de commande extrait du PDF\, page 215\.

```text
 Nom du rôle |                    Attributs         |      Membre de
-------------+--------------------------------------+---------------------
 admin       |                                      | {pg_signal_backend}
 postgres    | Superuser, Create role, Create DB,   |
             | Replication, Bypass RLS              | {}
 …
```
Toujours dans la base pgbench : =# GRANT SELECT ON TABLE pgbench_accounts TO dalibo ; =# GRANT ALL ON TABLE pgbench_history TO dalibo ; =# \z
=> \z Droits d'accès
**Tableau 4-19.** Tableau ou résultat de commande extrait du PDF\, page 215\.

```text
 Schéma |       Nom        | Type |      Droits d'accès      | Droits … | …
--------+------------------+-------+-------------------------+----------+---
 public | pgbench_accounts | table | testeur=arwdDxt/testeur+|          |
        |                  |       | dalibo=r/testeur        |          |
 public | pgbench_branches | table |                         |          |
 public | pgbench_history | table | testeur=arwdDxt/testeur+|           |
        |                  |       | dalibo=arwdDxt/testeur |           |
 public | pgbench_tellers | table |                          |          |
```
(4 lignes)
La commande \ddp permet de connaître les droits accordés par défaut à un utilisateur sur les nouveaux objets avec l’ordre ALTER DEFAULT PRIVILEGES .
=# ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO dalibo ;
=# \ddp Droits d´accès par défaut
**Tableau 4-20.** Tableau ou résultat de commande extrait du PDF\, page 215\.

```text
 Propriétaire | Schéma | Type |      Droits d´accès
--------------+--------+-------+-------------------------
 testeur      |        | table | dalibo=arwdDxt/testeur +
              |        |       | testeur=arwdDxt/testeur
```
(1 ligne)
Enfin, la commande \drds permet d’obtenir la liste des paramètres appliqués spécifiquement à un utilisateur ou une base de données. =# ALTER DATABASE pgbench SET work_mem TO '15MB'; =# ALTER ROLE testeur SET log_min_duration_statement TO '0';

=# \drds Liste des paramètres
**Tableau 4-21.** Tableau ou résultat de commande extrait du PDF\, page 216\.

```text
   Rôle   | Base de données |         Réglages
---------+-----------------+--------------------------
  testeur |                 | log_min_duration_statement=0
          | pgbench         | work_mem=15MB
```
…
### 4.4.5 Visualiser le code des objets
  - Voir les vues ou les fonctions & procédures
    - \dv , \df
  - Code d’une vue
    - \sv
  - Code d’une procédure stockée
    - \sf
Ceci permet de visualiser le code de certains objets sans avoir besoin de l’éditer. Par exemple avec cette vue système : =# \dv pg_tables Liste des relations
**Tableau 4-22.** Tableau ou résultat de commande extrait du PDF\, page 216\.

```text
   Schéma   |     Nom    | Type | Propriétaire
------------+-----------+------+--------------
 pg_catalog | pg_tables | vue | postgres
```
(1 ligne)
=# \sv+ pg_tables
**Exemple 4-42.** Exemple extrait du PDF\, page 216\.

```sql
1       CREATE OR REPLACE VIEW pg_catalog.pg_tables AS
2        SELECT n.nspname AS schemaname,
3           c.relname AS tablename,
4           pg_get_userbyid(c.relowner) AS tableowner,
5           t.spcname AS tablespace,
6           c.relhasindex AS hasindexes,
7           c.relhasrules AS hasrules,
8           c.relhastriggers AS hastriggers,
9           c.relrowsecurity AS rowsecurity
10         FROM pg_class c
11           LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
12           LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
13        WHERE c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"])
```
Ou cette fonction :
**Exemple 4-43.** Exemple extrait du PDF\, page 216\.

```console
=# CREATE FUNCTION nb_sessions_actives () RETURNS int
LANGUAGE sql
AS $$
  SELECT COUNT(*) FROM pg_stat_activity
  WHERE backend_type = 'client backend' AND state='active' ;
$$ ;
```

**Tableau 4-23.** Tableau ou résultat de commande extrait du PDF\, page 217\.

```text
=# \df nb_sessions_actives
                             Liste des fonctions
 Schéma |         Nom         | Type … du résultat | Type …des paramètres | Type
--------+---------------------+--------------------+----------------------+------
 public | nb_sessions_actives | integer            |                      | func
(1 ligne)
```
**Exemple 4-44.** Exemple extrait du PDF\, page 217\.

```console
=# \sf nb_sessions_actives
CREATE OR REPLACE FUNCTION public.nb_sessions_actives()
 RETURNS integer
 LANGUAGE sql
AS $function$
  SELECT COUNT(*) FROM pg_stat_activity
  WHERE backend_type = 'client backend' AND state='active' ;
$function$
```
<a id="eg-4-45"></a>
Il est même possible de lancer un éditeur depuis psql pour modifier directement la vue ou la fonction avec respectivement \ev ou \ef .
### 4.4.6 Configuration
  - Lister les paramètres correspondants au motif indiqué
    - \dconfig (v15+)
La méta-commande \dconfig permet de récupérer la configuration d’un paramètre. Par exemple :
**Tableau 4-24.** Tableau ou résultat de commande extrait du PDF\, page 217\.

```text
          Parameter          | Value
-----------------------------+-------
 log_recovery_conflict_waits | off
 log_replication_commands    | off
 log_rotation_age            | 1d
 log_rotation_size           | 0
```
**Exemple 4-46.** Exemple extrait du PDF\, page 217\.

```text
b1=# \dconfig log_r*
  List of configuration parameters
```
(4 rows)
En ajoutant le signe + , il est possible d’obtenir plus d’informations :
**Tableau 4-25.** Tableau ou résultat de commande extrait du PDF\, page 217\.

```text
          Parameter          | Value | Type    | Context | Access privileges
-----------------------------+-------+---------+-----------+-------------------
 log_recovery_conflict_waits | off   | bool    | sighup    |
 log_replication_commands    | off   | bool    | superuser |
 log_rotation_age            | 1d    | integer | sighup    |
 log_rotation_size           | 0     | integer | sighup    |
```
**Exemple 4-47.** Exemple extrait du PDF\, page 217\.

```text
b1=# \dconfig+ log_r*
                       List of configuration parameters
```
(4 rows)

### 4.4.7 Exécuter des requêtes
  - Exécuter une requête :
**Exemple 4-48.** Exemple extrait du PDF\, page 218\.

```sql
              SELECT * FROM pg_tables ;
              SELECT * FROM pg_tables \g
              SELECT * FROM pg_tables \gx   -- une ligne par champ
              INSERT INTO … VALUES (1) \; INSERT INTO … VALUES (2) ; -- 1 transaction
```
  - Rappel des requêtes :
    - flèche vers le haut
    - \g
    - Ctrl-R suivi d’un extrait de texte représentatif
Les requêtes SQL doivent se terminer par ; ou, pour marquer la parenté de PostgreSQL avec Ingres, \g . Cette dernière commande permet de relancer un ordre.
Il est aussi possible d’utiliser \gx pour bénéficier de l’affichage étendu pour la requête en cours sans avoir à jouer avec \x on et \x off .
La console psql , lorsqu’elle est compilée avec la bibliothèque libreadline ou la bibliothèque libedit (cas des distributions Linux courantes), dispose des mêmes possibilités de rappel de commande que le shell bash. Exemple :
**Exemple 4-49.** Exemple extrait du PDF\, page 218\.

```console
postgres=# SELECT * FROM pg_tablespace LIMIT 5;

```
**Tableau 4-26.** Tableau ou résultat de commande extrait du PDF\, page 218\.

```text
  oid | spcname     | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        |
  1664 | pg_global |        10 |        |
 16502 | ts1        |       10 |        |
```
**Exemple 4-50.** Exemple extrait du PDF\, page 218\.

```console
postgres=# SELECT * FROM pg_tablespace LIMIT 5\g

```
**Tableau 4-27.** Tableau ou résultat de commande extrait du PDF\, page 218\.

```text
  oid | spcname     | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        |
  1664 | pg_global |        10 |        |
 16502 | ts1        |       10 |        |
```
**Exemple 4-51.** Exemple extrait du PDF\, page 218\.

```console
postgres=# \g

```
**Tableau 4-28.** Tableau ou résultat de commande extrait du PDF\, page 218\.

```text
  oid | spcname     | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        |
  1664 | pg_global |        10 |        |
 16502 | ts1        |       10 |        |
```
**Exemple 4-52.** Exemple extrait du PDF\, page 218\.

```console
postgres=# \gx
```

**Tableau 4-29.** Tableau ou résultat de commande extrait du PDF\, page 219\.

```text
oid        | 1663
spcname    | pg_default
spcowner   | 10
spcacl     |
spcoptions |
```
**Tableau 4-30.** Tableau ou résultat de commande extrait du PDF\, page 219\.

```text
oid        | 1664
spcname    | pg_global
spcowner   | 10
spcacl     |
spcoptions |
```
**Exemple 4-53.** Exemple extrait du PDF\, page 219\.

```text
-[ RECORD 1 ]----------
-[ RECORD 2 ]----------
-[ RECORD 3 ]----------
```
**Tableau 4-31.** Tableau ou résultat de commande extrait du PDF\, page 219\.

```text
oid        | 16502
spcname    | ts1
spcowner   | 10
spcacl     |
spcoptions |
```
Plusieurs ordres sur la même ligne séparés par des ; seront exécutés et affichés à la suite. Par contre, s’ils sont séparés par \; , ils seront envoyés ensemble et implicitement dans une même transaction (sauf utilisation explicite de BEGIN / COMMIT / ROLLBACK ). Avant la version 15, ne sera affiché que le résultat du dernier ordre.
Dans cet exemple, la division par zéro fait tomber en erreur et annule l’insertion de la valeur 2 car les deux sont dans la même transaction :
**Exemple 4-54.** Exemple extrait du PDF\, page 219\.

```console
=# CREATE TABLE demo_insertion (i float);
CREATE TABLE
=# INSERT INTO demo_insertion VALUES(1.0); INSERT INTO demo_insertion VALUES(1.0/0);
INSERT 0 1
ERROR: division by zero
=# SELECT * FROM demo_insertion \; INSERT INTO demo_insertion VALUES (2.0) \;
   INSERT INTO demo_insertion VALUES (2.0/0) ;
 i
 1
(1 ligne)

INSERT 0 1
ERROR: division by zero
=# SELECT * FROM demo_insertion ;
 i
 1
(1 ligne)
```
<a id="eg-4-55"></a>
<a id="eg-4-56"></a>
<a id="eg-4-57"></a>
<a id="eg-4-58"></a>

### 4.4.8 Afficher le résultat d’une requête
  - \x pour afficher un champ par ligne
  - Affichage par paginateur si l’écran ne suffit pas
  - Préférer less :
  - set PAGER='less -S'
  - Ou outil dédié :
  - \setenv PAGER 'pspg'
En mode interactif, psql cherche d’abord à afficher directement le résultat :
**Exemple 4-59.** Exemple extrait du PDF\, page 220\.

```console
postgres=# SELECT relname,reltype, relchecks, oid,oid FROM pg_class LIMIT 3;

```
**Tableau 4-32.** Tableau ou résultat de commande extrait du PDF\, page 220\.

```text
      relname      | reltype | relchecks | oid | oid
-------------------+---------+-----------+-------+-------
 pg_statistic      |   11319 |         0 | 2619 | 2619
 t3                |   16421 |         0 | 16419 | 16419
 capitaines_id_seq |   16403 |         0 | 16402 | 16402
 t1                |   16415 |         0 | 16413 | 16413
 t2                |   16418 |         0 | 16416 | 16416
```
S’il y a trop de colonnes, on peut préférer n’avoir qu’un champ par ligne grâce au commutateur \x :
**Exemple 4-60.** Exemple extrait du PDF\, page 220\.

```console
postgres=# \x on
```
Expanded display is on.
**Tableau 4-33.** Tableau ou résultat de commande extrait du PDF\, page 220\.

```text
relname   | pg_statistic
reltype   | 11319
relchecks | 0
oid       | 2619
oid       | 2619
```
**Tableau 4-34.** Tableau ou résultat de commande extrait du PDF\, page 220\.

```text
relname   | t3
reltype   | 16421
relchecks | 0
oid       | 16419
oid       | 16419
```
**Exemple 4-61.** Exemple extrait du PDF\, page 220\.

```console
postgres=# SELECT relname,reltype, relchecks, oid,oid FROM pg_class LIMIT 3;

-[ RECORD 1 ]----------------
-[ RECORD 2 ]----------------
-[ RECORD 3 ]----------------
```
**Tableau 4-35.** Tableau ou résultat de commande extrait du PDF\, page 220\.

```text
relname   | capitaines_id_seq
reltype   | 16403
relchecks | 0
oid       | 16402
oid       | 16402
```
\x on et \x off sont alternativement appelés si l’on tape plusieurs fois \x . \x auto délègue à psql la décision du meilleur affichage, en général à bon escient. \gx à la place de ; bascule l’affichage pour une seule requête.

S’il n’y a pas la place à l’écran, psql appelle le paginateur par défaut du système. Il s’agit souvent de more , parfois de less . Ce dernier est bien plus puissant, permet de parcourir le résultat en avant et en arrière, avec la souris, de chercher une chaîne de caractères, de tronquer les lignes trop longues (avec l’option -S ) pour naviguer latéralement. Le paramétrage du paginateur s’effectue par des variables d’environnement :
**Exemple 4-62.** Exemple extrait du PDF\, page 221\.

```console
export PAGER='less -S'
psql
```
ou :
**Exemple 4-63.** Exemple extrait du PDF\, page 221\.

```text
PAGER='less -S'       psql
```
ou dans psql directement, ou .psqlrc :
**Exemple 4-64.** Exemple extrait du PDF\, page 221\.

```text
\setenv PAGER 'less -S'
```
Mais less est généraliste. Un paginateur dédié à l’affichage de résultats de requêtes a été récemment développé par Pavel Stěhule 15 et son paquet figure dans les principales distributions. Pour les gens qui consultent souvent des données dans les tables depuis la console, pspg permet de naviguer dans les lignes avec la souris en figeant les entêtes ou quelques colonnes; de poser des signets sur des lignes ; de sauvegarder les lignes. (Pour plus de détail, voir cette présentation 16 et la page Github du projet 17 ). La mise en place est similaire :
**Exemple 4-65.** Exemple extrait du PDF\, page 221\.

```text
\setenv PAGER 'pspg'
```
À l’inverse, la pagination se désactive complètement avec :
**Exemple 4-66.** Exemple extrait du PDF\, page 221\.

```text
\pset pager
```
(et bien sûr en mode non interactif, par exemple quand la sortie de psql est redirigée vers un fichier).
### 4.4.9 Afficher les détails d’une requête
  - \gdesc
  - Afficher la liste des colonnes correspondant au résultat d’exécution d’une requête
    - noms
    - type de données
Après avoir exécuté une requête, ou même à la place de l’exécution, il est possible de connaître le nom des colonnes en résultat ainsi que leur type de données.
15 http://okbob.blogspot.fr/2017/07/i-hope-so-every-who-uses-psql-uses-less.html

16 http://blog-postgresql.verite.pro/2017/11/21/test-pspg.html

17 https://github.com/okbob/pspg

Requête :
**Exemple 4-67.** Exemple extrait du PDF\, page 222\.

```sql
SELECT nspname, relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind = 'r';
```
Description des colonnes :
**Exemple 4-68.** Exemple extrait du PDF\, page 222\.

```console
postgres=# \gdesc

```
**Tableau 4-36.** Tableau ou résultat de commande extrait du PDF\, page 222\.

```text
 Column | Type
---------+------
 nspname | name
 relname | name
```
Ou sans exécution :
**Exemple 4-69.** Exemple extrait du PDF\, page 222\.

```console
postgres=# SELECT * FROM generate_series (1, 1000) \gdesc

```
**Tableau 4-37.** Tableau ou résultat de commande extrait du PDF\, page 222\.

```text
     Column      | Type
-----------------+---------
 generate_series | integer
```
### 4.4.10 Exécuter le résultat d’une requête
  - Exécuter le résultat d’une requête
  - \gexec
Parfois, une requête permet de créer des requêtes sur certains objets. Par exemple, si nous souhaitons exécuter un VACUUM sur toutes les tables du schéma public , nous allons récupérer la liste des tables avec cette requête :
**Tableau 4-38.** Tableau ou résultat de commande extrait du PDF\, page 222\.

```text
 nspname |     relname
---------+------------------
 public | pgbench_branches
 public | pgbench_tellers
 public | pgbench_accounts
 public | pgbench_history
```
**Exemple 4-70.** Exemple extrait du PDF\, page 222\.

```sql
SELECT nspname, relname FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind = 'r';

```
(4 lignes)
Plutôt que d’éditer manuellement cette liste de tables pour créer les ordres SQL nécessaires, autant modifier la requête pour qu’elle prépare elle-même les ordres SQL :
**Tableau 4-39.** Tableau ou résultat de commande extrait du PDF\, page 222\.

```text
SELECT 'VACUUM ' || quote_ident(nspname) || '.' || quote_ident(relname)
```
**Exemple 4-71.** Exemple extrait du PDF\, page 222\.

```text
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind = 'r';
```

?column?
**Tableau 4-40.** Tableau ou résultat de commande extrait du PDF\, page 223\.

```text
--------------------------------
 VACUUM public.pgbench_branches
 VACUUM public.pgbench_tellers
 VACUUM public.pgbench_accounts
 VACUUM public.pgbench_history
```
(4 lignes)
Une fois que nous avons vérifié la validité des requêtes SQL, il ne reste plus qu’à les exécuter. C’est ce que permet la commande \gexec :
=# \gexec
VACUUM VACUUM VACUUM VACUUM
### 4.4.11 Manipuler le tampon de requêtes
  - Éditer
    - dernière requête : \e
    - vue : \ev nom_vue
    - fonction PL/pgSQL : \ef nom_fonction
  - Historique :
    - \s
\e nomfichier.sql édite le tampon de requête courant ou un fichier existant indiqué à l’aide d’un éditeur externe. L’éditeur désigné avec les variables d’environnement $EDITOR ou $PSQL_EDITOR notamment. Sur Unix, c’est généralement par défaut une variante de vi mais n’importe quel éditeur fait l’affaire :
**Exemple 4-72.** Exemple extrait du PDF\, page 223\.

```text
PSQL_EDITOR=nano      psql
```
ou dans psql ou dans le .psqlrc :
**Exemple 4-73.** Exemple extrait du PDF\, page 223\.

```text
\setenv PSQL_EDITOR 'gedit'

\p affiche le contenu du tampon de requête.
```
\r efface la requête qui est en cours d’écriture. La précédente requête reste accessible avec \p .
\w nomfichier.sql provoque l’écriture du tampon de requête dans le fichier indiqué (à modifier par la suite avec \e par exemple).
**Exemple 4-74.** Exemple extrait du PDF\, page 223\.

```text
 \ev v_mavue édite la vue indiquée. Sans argument, cette commande affiche le squelette de création
```
d’une nouvelle vue.

\ef f_mafonction est l’équivalent pour une fonction.
\s affiche l’historique des commandes effectuées dans la session. \s historique.log sauvegarde cet historique dans le fichier indiqué.
### 4.4.12 Entrées/sorties
  - Charger et exécuter un script SQL
    - \i fichier.sql
  - Rediriger la sortie :
**Exemple 4-75.** Exemple extrait du PDF\, page 224\.

```sql
                     \o /tmp/resultat
                     SELECT * FROM matable ;
                     \o
```
  - Écrire un texte sur la sortie standard
    - \echo "Texte…"
\i fichier.sql lance l’exécution des commandes placées dans le fichier passé en argument. Un script lancé depuis le shell par psql -f script_maitre.sql peut ainsi appeler d’autres scripts. \ir fait la même chose sauf que le chemin est relatif à l’emplacement courant.
\o resultat.out envoie les résultats de la requête vers le fichier indiqué (voire vers une commande UNIX via un pipe).
Attention : cela va concerner toutes les commandes suivantes. Entrer \o pour revenir au mode normal.
Exemple :
**Exemple 4-76.** Exemple extrait du PDF\, page 224\.

```sql
\o tables.txt
SELECT * FROM pg_tables ;
\o
```
(Si l’on intercale \H , on peut avoir un formatage en HTML.)
\echo "Texte" affiche le texte passé en argument sur la sortie standard. Ce peut être utile entre les étapes d’un script.

### 4.4.13 Gestion de l’environnement système
  - Chronométrer les requêtes
    - \timing on
  - Exécuter une commande OS
    - \! ls -l (sur le client !)
  - Changer de répertoire courant
    - \cd /tmp
  - Affecter la valeur d’une variable d’environnement (v15+)
    - \getenv toto PATH
\timing on active le chronométrage de toutes les commandes. C’est très utile mais alourdit l’affichage. Sans argument, la valeur actuelle est basculée de on à off et vice-versa.
=# \timing on Chronométrage activé.
=# VACUUM ; VACUUM Temps : 26,263 ms
`\! <commande>` ouvre un shell interactif en l’absence d’argument ou exécute la commande indiquée sur le client (pas le serveur!) :
\cd (et non \! cd !) permet de changer de répertoire courant, là encore sur le client. Cela peut servir pour définir le chemin d’un script à exécuter ou d’un futur export.
\getenv permet de récupérer la valeur d’une valeur d’environnement système et de l’affecter à une variable.
Exemple :
\! cat nomfichier.out \! ls -l /tmp \! mkdir /home/dalibo/travail
**Exemple 4-77.** Exemple extrait du PDF\, page 225\.

```text
\cd /home/dalibo/travail
```
\! pwd /home/dalibo/travail

### 4.4.14 Variables internes psql
- Positionner des variables internes
- \set NOMVAR nouvelle_valeur
- Variables internes usuelles
- ON_ERROR_STOP : on / off
- ON_ERROR_ROLLBACK : on / off / interactive
- ROW_COUNT : nombre de lignes renvoyées par la dernière requête
- ERROR : true si dernière requête en erreur
- Ne pas confondre avec SET (au niveau du serveur)!
Les variables déclarées avec \set sont positionnées au niveau de psql , outil client. Elles ne sont pas connues du serveur et n’existent pas dans les autres outils clients (pgAdmin, DBeaver…). Il ne faut pas les confondre avec les paramètres définis sur le serveur au niveau de la session avec SET . Ceux-ci sont transmis directement au serveur quand ils sont entrés dans un outil client, quel qu’il soit.
\set affiche les variables internes et utilisateur.
\set NOMVAR nouvelle_valeur permet d’affecter une valeur.
La liste des variables prédéfinies est disponible dans la documentation de psql 18 . Beaucoup modifient le comportement de psql . Exemple :
**Exemple 4-78.** Exemple extrait du PDF\, page 226\.

```console
postgres=# \set
AUTOCOMMIT = 'on'
COMP_KEYWORD_CASE = 'preserve-upper'
DBNAME = 'b1'
ECHO = 'none'
ECHO_HIDDEN = 'off'
ENCODING = 'UTF8'
FETCH_COUNT = '0'
HIDE_TABLEAM = 'off'
HIDE_TOAST_COMPRESSION = 'off'
HISTCONTROL = 'none'
HISTSIZE = '500'
HOST = '/var/run/postgresql'
IGNOREEOF = '0'
LAST_ERROR_MESSAGE = ''
LAST_ERROR_SQLSTATE = '00000'
ON_ERROR_ROLLBACK = 'off'
```
18 https://docs.postgresql.fr/current/app-psql.html#APP-PSQL-VARIABLES

**Exemple 4-79.** Exemple extrait du PDF\, page 227\.

```text
ON_ERROR_STOP = 'off'
PORT = '5432'
PROMPT1 = '%/%R%x%# '
PROMPT2 = '%/%R%x%# '
PROMPT3 = '>> '
QUIET = 'off'
SERVER_VERSION_NAME = '15.1'
SERVER_VERSION_NUM = '150001'
SHOW_ALL_RESULTS = 'on'
SHOW_CONTEXT = 'errors'
SINGLELINE = 'off'
SINGLESTEP = 'off'
USER = 'postgres'
VERBOSITY = 'default'
VERSION = 'PostgreSQL 15.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0
   20210514 (Red Hat 8.5.0-15), 64-bit'
VERSION_NAME = '15.1'
VERSION_NUM = '150001'
```
Les variables ON_ERROR_ROLLBACK et ON_ERROR_STOP sont discutées dans la partie relative à la gestion des erreurs. ROW_COUNT indique le nombre de lignes de résultat de la dernière requête exécutée :
=# SELECT * FROM pg_namespace;
**Tableau 4-41.** Tableau ou résultat de commande extrait du PDF\, page 227\.

```text
      nspname       | nspowner |               nspacl
--------------------+----------+-------------------------------------
 pg_toast           |       10 |
 pg_temp_1          |       10 |
 pg_toast_temp_1    |       10 |
 pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
 public             |       10 | {postgres=UC/postgres,=UC/postgres}
 information_schema |       10 | {postgres=UC/postgres,=U/postgres}
```
=# \echo :ROW_COUNT
**Exemple 4-80.** Exemple extrait du PDF\, page 227\.

```text
6
```
=# SELECT :ROW_COUNT ;
**Exemple 4-81.** Exemple extrait du PDF\, page 227\.

```text
6
```
alors que ERROR est un booléen indiquant si la dernière requête était en erreur ou pas :
=# CREATE TABLE t1(id integer);
**Exemple 4-82.** Exemple extrait du PDF\, page 227\.

```sql
CREATE TABLE
```
=# CREATE TABLE t1(id integer);
**Exemple 4-83.** Exemple extrait du PDF\, page 227\.

```console
ERROR:   relation "t1" already exists

postgres=# \echo :ERROR
```
true
**Exemple 4-84.** Exemple extrait du PDF\, page 227\.

```console
postgres=# CREATE TABLE t2(id integer);
CREATE TABLE
postgres=# \echo :ERROR
```
false

### 4.4.15 Variables utilisateur psql
  - Définir une variable utilisateur
    - \set NOMVAR nouvelle_valeur
  - Invalider une variable
    - \unset NOMVAR
  - Stockage du résultat d’une requête :
    - si résultat est une valeur unique
    - Exemple :
**Exemple 4-85.** Exemple extrait du PDF\, page 228\.

```sql
                     SELECT now() AS maintenant \gset
                     SELECT :'maintenant' ;
```
En dehors des variables internes évoquées dans le chapitre précédent, il est possible à un utilisateur de psql de définir ses propres variables.
**Exemple 4-86.** Exemple extrait du PDF\, page 228\.

```text
-- initialiser avec une constante
\set active 'y'
\echo :active
```
y
**Exemple 4-87.** Exemple extrait du PDF\, page 228\.

```text
-- initialiser avec le résultat d'une commande système
\set uptime `uptime`
\echo :uptime
```
09:38:58 up 53 min, 1 user, load average: 0,12, 0,07, 0,07
Et pour supprimer la variable :
**Exemple 4-88.** Exemple extrait du PDF\, page 228\.

```text
\unset uptime
```
Il est possible de stocker le résultat d’une requête dans une variable pour sa réutilisation dans un script avec la commande \gset . Le nom de la variable est celui de la colonne ou de son alias. La valeur retournée par la requête doit être unique sous peine d’erreur.
**Exemple 4-89.** Exemple extrait du PDF\, page 228\.

```sql
SELECT now() AS "curdate" \gset
\echo :curdate
```
2020-08-16 10:53:51.795582+02
Il est possible aussi de donner un préfixe au nom de la variable :
**Exemple 4-90.** Exemple extrait du PDF\, page 228\.

```sql
SELECT now() AS "curdate" \gset v_
\echo :v_curdate
```
2020-08-16 10:54:20.484344+02

### 4.4.16 Tests conditionnels
**Tableau 4-42.** Tableau ou résultat de commande extrait du PDF\, page 229\.

```text
                -
                 -
                     \if
                     \elif
```
  - \else
  - \endif
Ces quatre instructions permettent de tester la valeur de variables psql , ce qui permet d’aller bien plus loin dans l’écriture de scripts SQL.
Par exemple, si on souhaite savoir si on se trouve sur un serveur standby ou sur un serveur primaire, il suffit de configurer la variable PROMPT1 à partir du résultat de l’interrogation de la fonction pg_is_in_recovery() . Pour cela, il faut enregistrer ce code dans le fichier .psqlrc :
**Exemple 4-91.** Exemple extrait du PDF\, page 229\.

```console
SELECT pg_is_in_recovery() as est_standby \gset
\if :est_standby
    \set PROMPT1 'standby %x$ '
\else
    \set PROMPT1 'primaire %x$ '
\endif
```
Puis, en lançant psql sur un serveur primaire, on obtient :
**Exemple 4-92.** Exemple extrait du PDF\, page 229\.

```text
psql (15.1)
```
Type "help" for help.
primaire $
alors qu’on obtient sur un serveur secondaire :
**Exemple 4-93.** Exemple extrait du PDF\, page 229\.

```text
psql (15.1)
```
Type "help" for help.
standby $
Attention : ce code n’est évalué qu’une fois, à la connexion! Si l’on se connecte à une autre machine avec \c , l’invite peut devenir fausse! Voir plus bas.

### 4.4.17 Personnaliser psql
- Fichier de configuration ~/.psqlrc
- voire ~/.psqlrc-X.Y ou ~/.psqlrc-X
- ignoré avec -X
- Exemple de .psqlrc :
**Exemple 4-94.** Exemple extrait du PDF\, page 230\.

```text
                   \set ON_ERROR_ROLLBACK interactive -- paramétrage de session
                   \timing on
                   \set PROMPT1 '%M:%> %n@%/%R%#%x'    -- invite
                   \set cfg 'SHOW ALL ;'               -- requête utilisable avec :cfg
                   \set cls '\\! clear;'               -- nettoyer l'écran avec :cls
                   -- read only par défaut
                   SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY ;
```
Fichier .psqlrc : psql est personnalisable par le biais de plusieurs variables internes. Il est possible de pérenniser ces personnalisations par le biais d’un fichier ~/.psqlrc (ou %APPDATA%\postgresql\psqlrc.conf sous Windows, ou dans un répertoire désigné par $PSQLRC ). Il peut exister des fichiers par version (par exemple ~/.psqlrc-15 ou ~/.psqlrc-15.0 ), voire un fichier global 19 .
Soyez très prudent en cas de modification du .psqlrc de l’utilisateur système postgres : il sert à lancer des scripts et certains outils l’utilisent.
Exemple de configuration .psqlrc : Modifier l’invite est utile, par exemple pour toujours savoir où et comment l’on est connecté. Par exemple, ajouter %> dans l’invite affiche le port. On peut aussi afficher toutes les variables listées par \set (par exemple ainsi : %:PORT: ou %:USER: ). En cas de reconnexion, psql en régénère certaines, mais pas toutes. Exemple de fichier .psqlrc :
**Exemple 4-95.** Exemple extrait du PDF\, page 230\.

```text
\set QUIET 1
\timing on
\pset pager on
\setenv pager
-- Valeur NULL : symbole  plutôt qu'un vide
\pset null ''
-- Mots clés autocomplétés en majuscule
\set COMP_KEYWORD_CASE upper
-- Affichage
\x auto
```
19 https://docs.postgresql.fr/current/app-psql.html#APP-PSQL-FILES-PSQLRC

**Exemple 4-96.** Exemple extrait du PDF\, page 231\.

```console
\pset linestyle 'unicode'
\pset border 2
\pset unicode_border_linestyle double
\pset unicode_column_linestyle double
\pset unicode_header_linestyle double
-- Prompt dynamique
-- %> indique le port, %m le serveur, %n l'utilisateur, %/ la base…
\set PROMPT1 '%m:%> %[%033[1;33;40m%]%n@%/%R%[%033[0m%]%#%x '
-- Serveur secondaire ? (NB : non mis à jour lors d'une reconnexion !)
SELECT pg_is_in_recovery() as est_standby \gset
\if :est_standby
   \set PROMPT1 :PROMPT1 '(standby) '
\else
   \set PROMPT1 :PROMPT1
\endif
\set QUIET 0

$ psql -h serveur -p5435 -U jeanpierre -d mabase
```
[serveur]:5435 jeanpierre@postgres=# (standby) SELECT pi(), now(), null ; ╔═══════════════════╦═══════════════════════════════╦══════════╗ ║ pi ║ now ║ ?column? ║ ╠═══════════════════╬═══════════════════════════════╬══════════╣ ║ 3.141592653589793 ║ 2022-01-07 16:08:39.925262+01 ║ ║ ╚═══════════════════╩═══════════════════════════════╩══════════╝ (1 ligne)
Il est aussi possible d’y rajouter du paramétrage de session avec SET pour adapter le fuseau horaire, par exemple. Requêtes courantes : Des requêtes très courantes, ou des parties de requêtes, peuvent être ajoutées dans le .psqlrc , par exemple celles-ci :
**Exemple 4-97.** Exemple extrait du PDF\, page 231\.

```sql
\set extensions 'SELECT * FROM pg_available_extensions ORDER BY name ;'
\set c 'SELECT count(*) FROM '
\set s 'SELECT * FROM '
\set g1 'GROUP BY 1 ORDER BY 1'
```
Ces raccourcis sont utilisables ainsi dans psql , avec une complétion automatique bien sûr :
=# :extensions
=# :c pg_database ;
=# :s pg_database WHERE datname = 'postgres' ;
=# SELECT schemaname, count(*) FROM pg_tables :g1 ;
La requête suivante de Christoph Berg 20 affiche les paramètres modifiés dans la configuration :
**Exemple 4-98.** Exemple extrait du PDF\, page 231\.

```console
-- Impérativement sur une ligne dans .psqlrc
\set config 'SELECT name, current_setting(name), CASE source WHEN $$configuration file$$ THEN regexp_replace(sourcefile, $$^/.*/$$, $$$$)||$$:$$||sourceline ELSE source END FROM pg_settings WHERE source <> $$default$$ OR name LIKE $$%.%$$;'
```
<a id="tbl-4-43"></a>
<a id="eg-4-99"></a>
20 https://www.postgresql.org/message-id/YlFQLzlPi4QD0wSi%40msg.df7cb.de

Les commandes doivent être monolignes pour fonctionner dans .psqlrc ou un script.
Attention : le .psqlrc n’est exécuté qu’au démarrage de psql , mais pas lors d’une reconnexion avec \c ! Les prompts dynamiques à base de variables utilisateur sont donc susceptibles d’être faux! Pour relancer le script depuis psql , utiliser :
**Exemple 4-100.** Exemple extrait du PDF\, page 232\.

```text
              \i ~/.psqlrc
```
Dans tous les scripts, ou sur un serveur inconnu ou douteux, il vaut mieux ignorer systématiquement ce fichier de configuration grâce à l’option -X ( --no-psqlrc ) pour revenir à l’environnement par défaut et éviter de polluer l’affichage :
**Exemple 4-101.** Exemple extrait du PDF\, page 232\.

```console
$ psql -X -f script.sql
$ psql -X -At -c 'SELECT name, setting FROM pg_settings ;'
```
Connexion en lecture seule : Pour éviter toute étourderie tragique, une bonne pratique est de se connecter systématiquement en lecture seule. Ajouter dans votre .psqlrc (mais surtout pas celui de l’utilisateur système postgres!) :
**Exemple 4-102.** Exemple extrait du PDF\, page 232\.

```text
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY ;
```
Il sera encore possible de tout lire (dans le respect des droits accordés), mais pas de modifier les données ni de créer des tables, même temporaires. Les commandes VACUUM ou ANALYZE restent possibles. Au besoin il faut passer explicitement en écriture avec :
**Exemple 4-103.** Exemple extrait du PDF\, page 232\.

```text
SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE ;
```
ou ouvrir une transaction en écriture :
**Exemple 4-104.** Exemple extrait du PDF\, page 232\.

```sql
BEGIN TRANSACTION READ WRITE ;
```
…
**Exemple 4-105.** Exemple extrait du PDF\, page 232\.

```sql
COMMIT ;
```
Fichier d’historique : Un fichier d’historique des commandes peut être créé, ici par base :
**Exemple 4-106.** Exemple extrait du PDF\, page 232\.

```text
\set HISTFILE ~/.psql_history- :DBNAME
\set HISTCONTROL ignoredups
\set HISTSIZE = '500'
```
Pour aller plus loin :

- Tous les détails sont dans la documentation 21 .
- .psqlrc de Kaarell Moppel 22
- Getting more out of psql 23 , par Craig Kerstiens
- Psqlrc sur le Wiki PostgreSQL 24 : une collection de liens
21 https://docs.postgresql.fr/current/app-psql.html#APP-PSQL-PROMPTING

22 https://gist.github.com/kmoppel/d27bf1d31affcc26f56fd3a9bd596477

23 https://www.craigkerstiens.com/2013/02/21/more-out-of-psql/

24 https://wiki.postgresql.org/wiki/Psqlrc

## 4.5 ÉCRITURE DE SCRIPTS SHELL
  - Script SQL
  - Script Shell
  - Exemple sauvegarde
### 4.5.1 Exécuter un script SQL avec psql
  - Des commandes :
**Exemple 4-107.** Exemple extrait du PDF\, page 234\.

```sql
             psql -c 'SELECT * FROM matable' -c 'SELECT fonction(123)' ;
             psql -AtX -c 'SELECT datname FROM pg_database'
```
  - Un script entier :
**Exemple 4-108.** Exemple extrait du PDF\, page 234\.

```text
             psql -f nom_fichier.sql
             psql < nom_fichier.sql
             psql < nom_fichier.sql > resultat.log
             psql < nom_fichier.sql -o resultat.log
             psql -f nom_fichier.sql --log-file=resultat.log
```
L’option -c permet de spécifier du SQL en ligne de commande sans avoir besoin de faire un script. Plusieurs ordres sont enchaînés dans la même session. Les ordres à la suite dans un même appel sont par défaut dans une même transaction, plusieurs appels à -c forment des transactions séparées. Une erreur n’interrompt pas psql , juste éventuellement la transaction (sauf utilisation de ON_ERROR_STOP ).
**Exemple 4-109.** Exemple extrait du PDF\, page 234\.

```sql
psql -X -c 'DROP TABLE IF EXISTS demo ; CREATE TABLE demo (i float) ;' \
-c 'INSERT INTO demo SELECT 1' \
-c 'INSERT INTO demo SELECT 2 ; INSERT INTO demo SELECT 3/0'\
-c 'TABLE demo'
```
**Exemple 4-110.** Exemple extrait du PDF\, page 234\.

```text
DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
ERROR: division by zero
 i
 1
(1 ligne)
```
<a id="eg-4-111"></a>
Pour récupérer facilement des données, on utilisera souvent ce genre d’ordre :
**Exemple 4-112.** Exemple extrait du PDF\, page 234\.

```sql
psql -AtX -c 'SELECT datname FROM pg_database'
```

postgres template0 template1
**Exemple 4-113.** Exemple extrait du PDF\, page 235\.

```sql
NB_BASES=$(psql -AtX -c 'SELECT count(datname) FROM pg_database')
echo $NB_BASES

36
```
Il est généralement préférable d’enregistrer les ordres dans des fichiers si on veut les exécuter plusieurs fois sans se tromper. L’option -f est très utile dans ce cas. La redirection avec < est une alternative répandue. Rappelons que \i fichier.sql , depuis une session ouverte, permet aussi d’appeler et d’exécuter un script. Pour récupérer le résultat dans un fichier, le script peut utiliser \o (vu plus haut). L’appel à psql peut utiliser l’option --output ( -o ), le fichier ne contiendra que les résultats des requêtes, qui n’apparaîtront pas à l’écran. Avec la redirection de la sortie vers un fichier avec > (ou un pipe Unix avec
**Tableau 4-44.** Tableau ou résultat de commande extrait du PDF\, page 235\.

```text
| ), toutes les sorties écran iront dans le fichier.
```
Un fichier généré avec l’option --log-file ( -L ) contiendra les requêtes et leur résultat, qui apparaîtront aussi à l’écran. --log-file est pratique pour historiser le contenu et les résultats de vos sessions de travail.
### 4.5.2 Gestion des transactions
  - psql est en mode auto-commit par défaut
    - variable AUTOCOMMIT
  - Ouvrir une transaction explicitement
    - BEGIN;
  - Terminer une transaction
    - COMMIT; ou ROLLBACK;
  - Ouvrir une transaction implicitement
    - option -1 ( --single-transaction )
Par défaut, psql est en mode « autocommit », c’est-à-dire que tous les ordres SQL sont automatiquement validés après leur exécution. Pour exécuter une suite d’ordres SQL dans une seule et même transaction, il faut soit ouvrir explicitement une transaction avec BEGIN; et la valider avec COMMIT; ou l’annuler avec ROLLBACK; . Les autres outils clients sont généralement dans ce même cas. L’ordre =# \set AUTOCOMMIT off

a pour effet d’insérer systématiquement un BEGIN avant chaque ordre s’il n’y a pas déjà une transaction ouverte; il faudra valider ensuite avec COMMIT avant de déconnecter. Il est déconseillé de changer le comportement par défaut ( on ), même s’il peut désorienter au premier abord des personnes ayant connu une base de données concurrente.
Une autre possibilité est d’utiliser psql -1 ou psql --single-transation : les ordres sont automatiquement encadrés d’un BEGIN et d’un COMMIT . La présence d’ordres BEGIN , COMMIT ou ROLLBACK explicites modifiera ce comportement en conséquence.
### 4.5.3 Écrire un script SQL
  - Attention à l’encodage des caractères
  - \encoding
  - SET client_encoding
  - Écriture des requêtes
L’encodage a moins d’importance depuis qu’UTF-8 s’est généralisé, mais il y a encore parfois des problèmes dans de vieilles bases ou certains vieux outils.
Rappelons que les bases modernes devraient toutes utiliser l’encodage UTF-8 (c’est le défaut).
\encoding [ENCODAGE] permet, en l’absence d’argument, d’afficher l’encodage du client. En présence d’un argument, il permet de préciser l’encodage du client.
Exemple :
**Exemple 4-114.** Exemple extrait du PDF\, page 236\.

```console
postgres=# \encoding
```
UTF8
**Exemple 4-115.** Exemple extrait du PDF\, page 236\.

```console
postgres=# \encoding LATIN9
postgres=# \encoding
```
LATIN9
Cela a le même effet que d’utiliser l’ordre SQL SET client_encoding TO LATIN9; .
En terme de présentation, il est commun d’écrire les mots clés SQL en majuscules et d’écrire les noms des objets et fonctions manipulés dans les requêtes en minuscule. Le langage SQL est un langage au même titre que Java ou PHP, la présentation est importante pour la lisibilité des requêtes, même si les variations personnelles sont nombreuses.

### 4.5.4 Les blocs anonymes
- Bloc procédural anonyme en PL/pgSQL :
**Exemple 4-116.** Exemple extrait du PDF\, page 237\.

```sql
DO $$
DECLARE r record;
BEGIN
    FOR r IN (SELECT schemaname, relname
              FROM pg_stat_user_tables
              WHERE coalesce(last_analyze, last_autoanalyze) IS NULL
              ) LOOP
        RAISE NOTICE 'Analyze %.%', r.schemaname, r.relname;
        EXECUTE 'ANALYZE ' || quote_ident(r.schemaname)
                           || '.' || quote_ident(r.relname);
    END LOOP;
END$$;
```
<a id="tbl-4-45"></a>
Les blocs anonymes sont utiles pour des petits scripts ponctuels qui nécessitent des boucles ou du conditionnel, voire du transactionnel, sans avoir à créer une fonction ou une procédure. Ils ne renvoient rien. Ils sont habituellement en PL/pgSQL mais tout langage procédural installé est possible.
L’exemple ci-dessus lance un ANALYZE sur toutes les tables où les statistiques n’ont pas été calculées d’après la vue système, et donne aussi un exemple de SQL dynamique. Le résultat est par exemple :
**Exemple 4-117.** Exemple extrait du PDF\, page 237\.

```text
NOTICE: Analyze public.pgbench_history
NOTICE: Analyze public.pgbench_tellers
NOTICE: Analyze public.pgbench_accounts
NOTICE: Analyze public.pgbench_branches
DO
```
Temps : 141,208 ms
Pour ce genre de SQL dynamique, si l’on est sous `psql`, il est souvent plus pratique d’utiliser `\gexec`.
Noter que les ordres constituent une transaction unique, à moins de rajouter des `COMMIT` ou `ROLLBACK` explicitement (ce n’est autorisé qu’à partir de la version 11).
25 https://docs.postgresql.fr/current/app-psql.html#R2-APP-PSQL-4

### 4.5.5 Utiliser des variables
**Exemple 4-120.** Exemple extrait du PDF\, page 238\.

```sql
               \set nom_table 'ma_table'
                SELECT * FROM :"nom_table";

                \set valeur_col1 'test'
                SELECT * FROM :"nom_table" WHERE col1 = :'valeur_col1';

                \prompt 'invite' nom_variable
                \unset variable

                psql -v VARIABLE=valeur
```
psql permet de manipuler des variables internes personnalisées dans les scripts. Ces variables peuvent être particulièrement utiles pour passer des noms d’objets ou des termes à utiliser dans une requête par le biais des options de ligne de commande ( -v variable=valeur ).
Noter la position des guillemets quand la variable est une chaîne de caractères!
Exemple : Une fois connecté à la base pgbench, on déclare deux variables propres au client :
**Exemple 4-121.** Exemple extrait du PDF\, page 238\.

```text
pgbench=# \set nomtable pgbench_accounts
pgbench=# \set taillemini 1000000
```
Elles apparaissent bien dans la liste des variables :
**Exemple 4-122.** Exemple extrait du PDF\, page 238\.

```text
pgbench=# \set
AUTOCOMMIT = 'on'
COMP_KEYWORD_CASE = 'preserve-upper'
DBNAME = 'pgbench'
```
…
**Exemple 4-123.** Exemple extrait du PDF\, page 238\.

```text
nomtable = 'pgbench_accounts'
taillemini = '1000000'
```
Elles s’utilisent ainsi :
**Exemple 4-124.** Exemple extrait du PDF\, page 238\.

```sql
SELECT pg_relation_size (:'nomtable') ;
```
pg_relation_size
**Tableau 4-46.** Tableau ou résultat de commande extrait du PDF\, page 238\.

```text
------------------
        134299648
```
**Exemple 4-125.** Exemple extrait du PDF\, page 238\.

```sql
SELECT relname, pg_size_pretty(pg_relation_size (oid))
FROM pg_class
WHERE relkind = 'r' AND pg_relation_size (oid) > :taillemini ;
```

**Tableau 4-47.** Tableau ou résultat de commande extrait du PDF\, page 239\.

```text
     relname      | pg_size_pretty
------------------+----------------
 pgbench_accounts | 128 MB
```
La substitution s’effectue bien au niveau du client. Si l’on trace tout au niveau du serveur, ces requêtes apparaissent :
**Exemple 4-126.** Exemple extrait du PDF\, page 239\.

```sql
SELECT pg_relation_size ('pgbench_accounts') ;

SELECT relname, pg_size_pretty(pg_relation_size (oid))
FROM pg_class WHERE relkind = 'r'
AND pg_relation_size (oid) > 1000000 ;
```
### 4.5.6 Gestion des erreurs
  - Ignorer les erreurs dans une transaction
    - ON_ERROR_ROLLBACK
  - Gérer des erreurs SQL en shell
    - ON_ERROR_STOP
La variable interne ON_ERROR_ROLLBACK n’a de sens que si elle est utilisée dans une transaction. Elle peut prendre trois valeurs :
- off (défaut);
- on ;
- interactive .
Lorsque ON_ERROR_ROLLBACK est à on , psql crée un SAVEPOINT systématiquement avant d’exécuter une requête SQL. Ainsi, si la requête SQL échoue, psql effectue un ROLLBACK TO SAVEPOINT pour annuler cette requête. Sinon il relâche le SAVEPOINT .
Lorsque ON_ERROR_ROLLBACK est à interactive , le comportement de psql est le même seulement si il est utilisé en interactif. Si psql exécute un script, ce comportement est désactivé. Cette valeur permet de se protéger d’éventuelles fautes de frappe. Utiliser cette option n’est donc pas neutre, non seulement en terme de performances, mais également en terme d’intégrité des données. Il ne faut donc pas utiliser cette option à la légère. Enfin, la variable interne ON_ERROR_STOP a deux objectifs : arrêter l’exécution d’un script lorsque psql rencontre une erreur et retourner un code retour shell différent de 0. Si cette variable reste à off , psql retournera toujours la valeur 0 même s’il a rencontré une erreur dans l’exécution d’une requête. Une fois activée, psql retournera un code d’erreur 3 pour signifier qu’il a rencontré une erreur dans l’exécution du script. L’exécution d’un script qui comporte une erreur retourne le code 0, signifiant que psql a pu se connecter à la base de données et exécuté le script :

**Exemple 4-127.** Exemple extrait du PDF\, page 240\.

```console
$ psql -f script_erreur.sql postgres
psql:script_erreur.sql:1: ERROR: relation "vin" does not exist
```
LINE 1: SELECT * FROM vin; ^
**Exemple 4-128.** Exemple extrait du PDF\, page 240\.

```console
$ echo $?
0
```
Lorsque la variable ON_ERROR_STOP est activée, psql retourne un code erreur 3, signifiant qu’il a rencontré une erreur :
**Exemple 4-129.** Exemple extrait du PDF\, page 240\.

```console
$ psql -v ON_ERROR_STOP=on -f script_erreur.sql postgres
psql:script_erreur.sql:1: ERROR: relation "vin" does not exist
```
LINE 1: SELECT * FROM vin; ^
**Exemple 4-130.** Exemple extrait du PDF\, page 240\.

```console
$ echo $?
3
```
psql retourne les codes d’erreurs suivant au shell :
- 0 au shell s’il se termine normalement ;
- 1 s’il y a eu une erreur fatale de son fait (pas assez de mémoire, fichier introuvable);
- 2 si la connexion au serveur s’est interrompue ou arrêtée;
- 3 si une erreur est survenue dans un script et si la variable ON_ERROR_STOP a été initialisée.
### 4.5.7 Formatage des résultats
  - Sortie simplifiée pour exploitation automatisée : -XAt
    - -t ( --tuples-only )
    - -A ( --no-align )
    - -X ( --no-psqlrc )
    - séparateurs : -F ( --field-separator ) et -R ( --record-separator )
    - Formats HTML ou CSV
**Tableau 4-48.** Tableau ou résultat de commande extrait du PDF\, page 240\.

```text
                      - -H | --html
```
    - --csv
psql peut servir à afficher des rapports basiques et possède quelques options de formatage.
L’option --csv suffit à répondre à ce besoin. S’il faut définir plus finement le format, il existe des options. -A impose une sortie non alignée des données. En ajoutant -t , qui supprime l’entête, et -X , qui demande à ignorer le .psqlrc , la sortie peut être facilement exploitée par des outils classiques comme awk ou sed :
**Exemple 4-131.** Exemple extrait du PDF\, page 240\.

```console
$ psql -XAt -c 'select datname, pg_database_size(datname) from pg_database'
```
**Tableau 4-49.** Tableau ou résultat de commande extrait du PDF\, page 240\.

```text
postgres|87311139
powa|765977379
```

**Tableau 4-50.** Tableau ou résultat de commande extrait du PDF\, page 241\.

```text
template1|9028387
template0|8593923
pgbench|166134563
```
**Tableau 4-51.** Tableau ou résultat de commande extrait du PDF\, page 241\.

```text
Le séparateur | peut être remplacé par un autre avec -F , ou un octet nul avec -z , et le retour
```
chariot de fin de ligne par une chaîne définie avec -R , ou un octet nul avec -0 . -H permet une sortie en HTML pour une meilleure lisibilité par un humain.
### 4.5.8 Résultats en pivot (tableau croisé)
  - \crosstabview [colV [colH [colD [colonnedetriH]]]]
  - Exécute la requête en tampon
    - au moins 3 colonnes
Par exemple, pour voir les différents types de clients connectés aux bases (clients système inclus), le résultat n’est pas très lisible : =# \pset null
=# SELECT datname, backend_type, COUNT(*) as nb FROM pg_stat_activity
**Tableau 4-52.** Tableau ou résultat de commande extrait du PDF\, page 241\.

```text
 datname |           backend_type        | nb
----------+------------------------------+----
 pgbench | client backend                | 2
 postgres | client backend               | 1
 powa     | powa                         | 1
         | archiver                     | 1
         | autovacuum launcher          | 1
         | background writer            | 1
         | checkpointer                 | 1
         | logical replication launcher | 1
         | pg_wait_sampling collector   | 1
         | walwriter                    | 1
```
**Exemple 4-132.** Exemple extrait du PDF\, page 241\.

```text
   GROUP BY 1,2
   ORDER BY datname NULLS LAST, backend_type ;

```
(10 lignes)
On peut le reformater ainsi : =# \crosstabview backend_type datname
**Tableau 4-53.** Tableau ou résultat de commande extrait du PDF\, page 241\.

```text
         backend_type         | postgres |  | powa | pgbench
------------------------------+----------+---+------+---------
 client backend               |        2 |   |      |       1
 walwriter                    |          | 1 |      |
 autovacuum launcher          |          | 1 |      |
 logical replication launcher |          | 1 |      |
 powa                         |          |   |    1 |
 background writer            |          | 1 |      |
```

**Tableau 4-54.** Tableau ou résultat de commande extrait du PDF\, page 242\.

```text
 archiver                                   |            | 1 |         |
 checkpointer                               |            | 1 |         |
```
(9 lignes)
### 4.5.9 Formatage dans les scripts SQL
- Donner un titre au résultat de la requête
- \pset title 'Résultat de la requête ’
- Formater le résultat
- \pset format html (ou csv …)
- Diverses options peu utilisées
Il est possible de réaliser des modifications sur le format de sortie des résultats de requête directement dans le script SQL ou en mode interactif dans psql. Afficher \pset permet de voir ces différentes options. La complétion automatique après \pset affiche les paramètres et valeurs possibles. Par exemple, l’option format est par défaut à aligned mais possède d’autres valeurs :
`=# \pset format <TAB>`
aligned csv latex troff-ms wrapped asciidoc html latex-longtable unaligned
D’autres options existent, peu utilisées. La liste complète des options de formatage et leur description est disponible dans la documentation de la commande psql 26 .
### 4.5.10 Scripts & Crontab
- cron
- Attention aux variables d’environnement!
- Ou tout ordonnanceur
La planification d’un script périodique s’effectue de préférence avec les outils du système, donc sous Unix avec cron ou une de ses variantes, même si n’importe quel ordonnanceur peut convenir. Avec cron , il faut se rappeler qu’à l’exécution d’un script, l’environnement de l’utilisateur n’est pas initialisé, ou plus simplement, les fichiers de personnalisation (par ex. .bashrc ) de l’environnement ne sont pas lus. Seule la valeur $HOME est initialisée. Un script fonctionnant parfaitement dans une
26 https://docs.postgresql.fr/current/app-psql.html

session peut échouer une fois planifié. Il faut donc prévoir ce cas, initialiser les variables d’environnement requises de façon adéquate, et bien tester.
Par exemple, pour charger l’environnement de l’utilisateur :
**Exemple 4-133.** Exemple extrait du PDF\, page 243\.

```text
#!/bin/bash
```
. ${HOME}/.bashrc
…
Rappelons que chaque utilisateur du système peut avoir ses propres crontab. L’utilisateur peut les visualiser avec la commande crontab -l et les éditer avec la commande crontab -e .
### 4.5.11 Exemple de script de sauvegarde
- Sauvegarder une base et classer l’archive (squelette) :
**Exemple 4-134.** Exemple extrait du PDF\, page 243\.

```text
                   #!/bin/bash
                   # Paramètre : la base
                   t=$(mktemp)                    # fichier temporaire
                   pg_dump -Fc "$1" > $t          # sauvegarde
                   d=$(eval date +%d%m%y-%H%M%S) # date
                   mv $t /backup/"${1}_${d}.dump" # déplacement
                   exit 0
```
    - …et ajouter la gestion des erreurs!
    - …et les surveiller
Il est délicat d’écrire un script fiable. Ce script d’exemple possède plusieurs problèmes potentiels si le paramètre (la base) manque, si la sauvegarde échoue, si l’espace disque manque dans /tmp , si le déplacement échoue, si la partition cible n’est pas montée…
Parmi les outils existants, nous évoquerons notamment pg_back lors des sauvegardes 27 .
Par convention, un script doit renvoyer 0 s’il s’est déroulé correctement.
27 https://dali.bo/i1_html

### 4.5.12 COPY
  - Pour charger des données depuis une entrée :
**Exemple 4-135.** Exemple extrait du PDF\, page 244\.

```sql
                COPY […] FROM […]
```
    - Pour exporter des données :
**Exemple 4-136.** Exemple extrait du PDF\, page 244\.

```sql
                COPY […] TO […]
```
Une commande très utilisée pour l’export et l’insertion en masse de données est la commande COPY .
Lors de l’export, il est possible de cibler les données d’une table spécifique ou bien les données retournées par une requête. Au niveau performance, utiliser COPY est beaucoup plus rapide que d’utiliser plusieurs petits ordres INSERT les uns à la suite des autres. L’insertion faite par COPY peut être considérée comme une insertion bulk. Ses cas d’usage sont très nombreux, comme le chargement d’une base ( pg_dump utilise COPY ), de migration ou d’export vers un tableur.
### 4.5.13 Export avec COPY
Exemples d’export :
**Exemple 4-137.** Exemple extrait du PDF\, page 244\.

```sql
               COPY clients TO '/tmp/clients.csv' WITH DELIMITER ',' CSV
                                                   HEADER ENCODING 'UTF8';
                COPY clients(nom, email, age) […];
                COPY (SELECT nom, email, age FROM clients WHERE age > 18) […];
                COPY […] TO STDOUT […];
```
Lors de l’export, il est possible de cibler une table entière (avec ou sans colonnes), ou bien le résultat d’une requête SELECT . Les options les plus utiles sont :
- CSV : pour Comma Separated Value, qui utilisera par défaut le DELIMITER ',' ;
- DELIMITER : permet de choisir le séparateur, , par défaut mais souvent ; ou \t ;
- HEADER : ajoute le nom des colonnes en ligne d’entête;
- ENCODING : garantit la compatibilité pour la lecture du fichier par d’autres logiciels.
Il est possible de rediriger la sortie de COPY vers le terminal en utilisant TO STDOUT .

**Exemple 4-138.** Exemple extrait du PDF\, page 245\.

```sql
COPY (SELECT nom, email, age FROM clients WHERE age > 18) TO STDOUT;
```
Un cas d’usage avec TO STDOUT est l’utilisation de la commande psql dans un script :
**Exemple 4-139.** Exemple extrait du PDF\, page 245\.

```sql
psql -d formation -c "COPY (SELECT nom, email, age FROM clients WHERE age > 18) TO
```
**Tableau 4-55.** Tableau ou résultat de commande extrait du PDF\, page 245\.

```text
   STDOUT;" | grep dalibo
```
### 4.5.14 Imports avec COPY
Exemples d’import : COPY clients
**Exemple 4-140.** Exemple extrait du PDF\, page 245\.

```sql
                   FROM '/tmp/clients.csv'
                   WITH DELIMITER ',' CSV
                   HEADER ENCODING 'UTF8';

              COPY clients(nom, email, age) […];

              COPY […] FROM STDIN […];
```
Après l’export, ou la mise à disposition de fichiers correctement structurés, il est alors possible d’importer un fichier depuis le serveur avec COPY , ou depuis votre session psql avec \copy . Les options les plus utiles sont les mêmes que précédemment lors de l’export.
Avec COPY , le fichier doit être lisible par l’utilisateur postgres sur le serveur (rarement accessible à un simple utilisateur). Ne pas confondre avec l’ordre \copy de psql , où le fichier est lu depuis la machine client de l’utilisateur.
### 4.5.15 Comment exporter/importer?
  - COPY :
    - s’exécute sur le serveur, postgres doit pouvoir lire le fichier
    - s’exécute à distance via DBeaver, pgAdmin, etc…
    - pas de latence réseau
    - attention à l’espace occupé sur disque
  - \copy :
    - propre à psql
    - équivaut à COPY […] FROM STDOUT ou COPY […] TO STDOUT
    - Attention à la charge réseau!
  - Votre outil favori
  - pg_dump

COPY n’importe ou n’exporte que depuis ou vers le système de fichiers du serveur PostgreSQL. L’intérêt est la rapidité car l’export est réalisé sur le serveur même. Il faut bien sûr avoir les accès nécessaires. Un ordre \copy sous psql est simplement un COPY TO STDOUT/FROM STDIN auquel psql ajoute des redirections pour que cela fonctionne du point de vue de l’utilisateur. \copy n’est donc pas disponible pour les autres outils (un ETL, un tableur, DBeaver, pgAdmin) qui récupèrent les données chacun à leur manière. pg_dump est également un outil pratique pour exporter ou importer des données par sa fiabilité et sa rapidité (compression, parallélisme), et ses différents formats d’export.

## 4.6 OUTILS GRAPHIQUES
  - Outils graphiques d’administration
    - temBoard
    - pgAdmin 4
    - pgmodeler
Il existe de nombreux outils graphiques permettant d’administrer des bases de données PostgreSQL. Certains sont libres, d’autres propriétaires. Certains sont payants, d’autres gratuits. Ils ont généralement les mêmes fonctionnalités de base, mais vont se distinguer sur certaines fonctionnalités un peu plus avancées comme l’import et l’export de données.
Nous allons étudier ici plusieurs outils proposés par la communauté, temBoard, pgAdmin.
### 4.6.1 temBoard
  - Adresse : https://labs.dalibo.com/temboard
  - Licence : PostgreSQL
  - Notes : Serveur sur Linux, client web
### 4.6.2 temBoard - PostgreSQL Remote Control
  - Multi-instances
  - Surveillance OS / PostgreSQL
  - Suivi de l’activité
  - Gestion des performances de PostgreSQL
  - Configuration de chaque instance
temBoard est un outil permettant à un DBA de mener à bien la plupart de ses tâches courantes.
Le serveur web est installé de façon centralisée et un agent est déployé pour chaque instance.

### 4.6.3 temBoard - Vue parc

![Illustration extraite du PDF, page 248.](/images/dba1/manual/p248-image-024.png)
{#img-p248-024}

temBoard fournit un tableau de bord global.
temBoard peut administrer plusieurs centaines d’instances.

### 4.6.4 temBoard - Tableau de bord

![Illustration extraite du PDF, page 249.](/images/dba1/manual/p249-image-026.png)
{#img-p249-026}

temBoard présente un tableau de bord par instance.

### 4.6.5 temBoard - Activity

![Illustration extraite du PDF, page 250.](/images/dba1/manual/p250-image-028.png)
{#img-p250-028}

La section Activity permet de lister toutes les requêtes courantes (Running), les requêtes bloquées (Waiting) ou bloquantes (Blocking). Il est possible à partir de cette vue de terminer une session.

### 4.6.6 temBoard - Supervision

![Illustration extraite du PDF, page 251.](/images/dba1/manual/p251-image-030.png)
{#img-p251-030}

La section Monitoring permet de visualiser les graphiques historisés au niveau du système d’exploitation (CPU, mémoire, …) ainsi qu’au niveau de l’instance PostgreSQL.
temBoard inclut un système d’alerte par courriel et SMS lorsqu’une métrique dépasse un seuil.

### 4.6.7 temBoard - Configuration

![Illustration extraite du PDF, page 252.](/images/dba1/manual/p252-image-032.png)
{#img-p252-032}

La section Configuration permet naviguer dans les paramètres de PostgreSQL de modifier ces paramètres
Suivant les cas, il sera proposé de recharger la configuration ou de redémarrer l’instance pour appliquer ces changements.

### 4.6.8 temBoard - Maintenance

![Illustration extraite du PDF, page 253.](/images/dba1/manual/p253-image-034.png)
{#img-p253-034}

temBoard estime la fragmentation du stockage des tables et des index. Une interface permet de visualiser la fragmentation et d’agir dessus avec une granularité fine.

### 4.6.9 pgAdmin 4
- https://www.pgadmin.org
- Application web
- Licence : PostgreSQL
- Multiplateforme, multilangue

![Illustration extraite du PDF, page 254.](/images/dba1/manual/p254-image-036.png)
{#img-p254-036}

pgAdmin 4 est une application web, même si une version émulant un client lourd existe. Après un début difficile, le produit est à présent mature. Il reprend l’essentiel des fonctionnalités de pgAdmin III. Il est bien entendu compatible avec les dernières versions de PostgreSQL. Il peut être déployé sur Windows et macOS X et bien sûr Linux, où il faudra utiliser les dépôts fournis par le projet 28 , ou l’image docker. Il est disponible sous licence PostgreSQL.
### 4.6.10 pgAdmin 4 : tableau de bord
28 https://www.pgadmin.org/download/

Une des nouvelles fonctionnalités de pgAdmin 4 est l’apparition d’un tableau de bord remontant quelques métriques intéressantes. Il permet aussi la visualisation des géométries PostGIS, ce qui est parfois très utile.

### 4.6.11 DBeaver
- https://dbeaver.io
- Version Community sous Licence Apache 2.0
- Application Java Multiplateforme
- Version web CloudBeaver aussi disponible
- Supporte PostgreSQL (et ~ 80 autres SBGD)
- Version Pro payante et plus complète
DBeaver est un outil graphique basé sur Eclipse. Il permet l’administration de plus de 80 SGBDs différents, y compris PostgreSQL. Le projet a sa propre entreprise depuis 2017 ainsi qu’une version payante plus complète. La version open-source Community Edition est disponible sous licence Apache 2.0. Une version web de DBeaver est disponible sous le nom de CloudBeaver (avec une page de démonstration publique 29 ).
La version Pro, disponible sous différentes déclinaisons, propose des connecteurs propriétaires supplémentaires vers des systèmes de bases de données non relationnels (MongoDB, Redis, Cassandra, etc.), une gestion native des bases de données Cloud (AWS, Azure, etc.), des fonctionnalités dédiées aux entreprises ainsi qu’un support de la part de l’éditeur.
Il existe des paquets d’installation pour les systèmes d’exploitation les plus courants 30 .
29 https://demo.cloudbeaver.io/

30 https://dbeaver.io/download/

### 4.6.12 DBeaver : fenêtre principale

![Illustration extraite du PDF, page 257.](/images/dba1/manual/p257-image-038.png)
{#img-p257-038}

La vue principale est divisée en deux sections, le côté gauche référence les différentes instances et le côté droit permet de consulter les différentes informations d’une instance en particulier. Ici, nous avons une vue entité-association des tables crées avec l’outil pgbench quand l’option --foreign-keys est spécifiée.

### 4.6.13 phpPgAdmin

![Illustration extraite du PDF, page 258.](/images/dba1/manual/p258-image-040.png)
{#img-p258-040}

### 4.6.14 phpPgAdmin : fonctionnalités
- Licence : GNU Public License
- Application web, simple
- consultation, édition
- sauvegarde, export
- Pérennité?
PhpPgAdmin est une application web en PHP, légère et simple d’emploi, que l’on peut ouvrir à un utilisateur non informaticien pour y modifier des données. Le projet PhpPgAdmin a malheureusement des problèmes de maintenance récurrents. En août 2025, le « nouveau » dépôt officiel 31 semble toujours abandonné. Ce fork 32 semble être devenu la référence de fait, et il sert de base entre autres aux paquets Debian. Les paquets RPM sont malheureusement basés sur l’ancien dépôt.
31 https://github.com/phppgadmin/phppgadmin

32 https://github.com/ReimuHakurei/phpPgAdmin

Notre conseil est de préférer la version web de pgAdmin 4, qui est beaucoup plus lourd, mais plus puissant et plus pérenne. Du moins s’il convient au public voulu.
### 4.6.15 Adminer

![Illustration extraite du PDF, page 259.](/images/dba1/manual/p259-image-041.png)
{#img-p259-041}

### 4.6.16 Adminer : fonctionnalités
- https://www.adminer.org/
- Application web pour utilisateurs
- Basique mais simple & efficace
- Et simple : 1 fichier PHP
- Multibases, multilangues
- Licence : Apache License ou GPL 2
Adminer 33 C’est une application web à destination des utilisateurs, pouvant gérer plusieurs types de bases, dont PostgreSQL. Il utilise en un unique fichier PHP (éventuellement personnalisable par CSS). On récupérera simplement le dépôt Github 34 .
33 https://www.adminer.org/

34 https://github.com/vrana/adminer/

L’interface est très basique. Elle peut sembler datée, voire primitive, mais elle est très simple et regroupe efficacement l’essentiel des fonctionnalités. C’est un candidat au remplacement de phpPgAdmin pour des utilisateurs non techniques.
### 4.6.17 pgModeler

![Illustration extraite du PDF, page 260.](/images/dba1/manual/p260-image-042.png)
{#img-p260-042}

### 4.6.18 pgModeler
  - Site officiel : https://pgmodeler.io/
  - Licence : GPLv3
  - Modélisation de base de données
  - Fonctionnalité d’import export
  - Comparaison de base

pgModeler permet de modéliser une base de données. Son intérêt par rapport à d’autres produits concurrents est qu’il est spécialisé pour PostgreSQL. Il en supporte donc toutes les spécificités, comme l’héritage de tables, les types composites, les types tableaux… C’est une excellente solution pour modéliser une base en partant de zéro, ou pour extraire une visualisation graphique d’une base existante. Il est à noter que, bien que le projet soit libre, son installation par les sources peut être laborieuse, et les paquets ne sont pas forcément disponibles. L’équipe de développement propose des paquets binaires à prix modique.

## 4.7 CONCLUSION
  - Les outils en ligne de commande sont « rustiques » mais puissants
  - Ils peuvent être remplacés par des outils graphiques
  - En cas de problème, il est essentiel de les maîtriser.
### 4.7.1 Questions
N’hésitez pas, c’est le moment !

## 4.8 QUIZ
https://dali.bo/ad10_quiz

## 4.9 INTRODUCTION À PGBENCH

![Illustration extraite du PDF, page 264.](/images/dba1/manual/p264-image-043.png)
{#img-p264-043}

pgbench est un outil de test livré avec PostgreSQL. Son but est de faciliter la mise en place de benchmarks simples et rapides. Par défaut, il installe une base très simple, génère une activité plus ou moins intense et calcule le nombre de transactions par seconde et la latence. C’est ce qui sera fait ici dans cette introduction. On peut aussi lui fournir ses propres scripts.
La documentation complète est sur https://docs.postgresql.fr/current/pgbench.html. L’auteur principal, Fabien Coelho, a fait une présentation complète, en français, à la PG Session #9 de 2017 35 .
### 4.9.1 Installation
L’outil est installé avec les paquets habituels de PostgreSQL, client ou serveur suivant la distribution. Sur les distributions à paquets RPM (RockyLinux…), l’outil n’est pas dans le chemin par défaut, il faudra fournir le chemin complet (qui ne sera pas répété ici) :
**Exemple 4-141.** Exemple extrait du PDF\, page 264\.

```console
/usr/pgsql-17/bin/pgbench
```
Il est préférable de créer un rôle non privilégié dédié, qui possédera la base de donnée :
**Exemple 4-142.** Exemple extrait du PDF\, page 264\.

```sql
CREATE ROLE pgbench LOGIN PASSWORD 'unmotdepassebienc0mplexe';
CREATE DATABASE pgbench OWNER pgbench ;
```
35 https://youtu.be/aTwh_CgRaE0

Le pg_hba.conf doit éventuellement être adapté. La base par défaut s’initialise ainsi (ajouter --port et --host au besoin) :
**Exemple 4-143.** Exemple extrait du PDF\, page 265\.

```text
pgbench -U -d pgbench --initialize --scale=100           pgbench
```
--scale permet de faire varier proportionnellement la taille de la base. À 100, la base pèsera 1,5 Go, avec 10 millions de lignes dans la table principale pgbench_accounts :
**Exemple 4-144.** Exemple extrait du PDF\, page 265\.

```text
pgbench@pgbench=# \d+
                           Liste des relations
```
**Tableau 4-56.** Tableau ou résultat de commande extrait du PDF\, page 265\.

```text
 Schéma |       Nom        | Type | Propriétaire | Taille | Description
--------+------------------+-------+--------------+---------+-------------
 public | pg_buffercache   | vue   | postgres     | 0 bytes |
 public | pgbench_accounts | table | pgbench      | 1281 MB |
 public | pgbench_branches | table | pgbench      | 40 kB   |
 public | pgbench_history | table | pgbench       | 0 bytes |
 public | pgbench_tellers | table | pgbench       | 80 kB   |
```
### 4.9.2 Générer de l’activité
Pour simuler une activité de 20 clients simultanés, répartis sur 4 processeurs, pendant 100 secondes :
**Exemple 4-145.** Exemple extrait du PDF\, page 265\.

```text
pgbench -U pgbench -c 20 -j 4 -T100 pgbench
```
Pour afficher, ajouter --debug :
**Exemple 4-146.** Exemple extrait du PDF\, page 265\.

```sql
 UPDATE pgbench_accounts SET abalance = abalance + -3455 WHERE aid = 3789437;
 SELECT abalance FROM pgbench_accounts WHERE aid = 3789437;
 UPDATE pgbench_tellers SET tbalance = tbalance + -3455 WHERE tid = 134;
 UPDATE pgbench_branches SET bbalance = bbalance + -3455 WHERE bid = 78;
 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
  VALUES (134, 78, 3789437, -3455, CURRENT_TIMESTAMP);
```
À la fin, s’affichent notamment le nombre de transactions (avec et sans le temps de connexion) et la durée moyenne d’exécution du point de vue du client ( latency ) :
scaling factor: 100 query mode: simple number of clients: 20 number of threads: 4 duration: 10 s number of transactions actually processed: 20433 latency average = 9.826 ms
**Exemple 4-147.** Exemple extrait du PDF\, page 265\.

```text
 tps = 2035.338395 (including connections establishing)
 tps = 2037.198912 (excluding connections establishing)
```
Modifier le paramétrage est facile grâce à la variable d’environnement PGOPTIONS :
**Exemple 4-148.** Exemple extrait du PDF\, page 265\.

```text
PGOPTIONS='-c synchronous_commit=off -c commit_siblings=20' \
             pgbench -U pgbench -c 20 -j 4 -T100 pgbench 2>/dev/null
```

latency average = 6.992 ms
**Exemple 4-149.** Exemple extrait du PDF\, page 266\.

```text
tps = 2860.465176 (including connections establishing)
tps = 2862.964803 (excluding connections establishing)
```
Des tests rigoureux doivent durer bien sûr beaucoup plus longtemps que 100 s, par exemple pour tenir compte des effets de cache, des checkpoints périodiques, etc.

## 4.10 TRAVAUX PRATIQUES
La version en ligne des solutions de ces TP est disponible sur https://dali.bo/ad10_solutions.
But :
  - Acquérir certains automatismes dans l’utilisation de psql
  - Créer des premières bases de données
L’ensemble des informations permettant de résoudre ces exercices a été abordé au cours de la partie théorique. Il est également possible de trouver les réponses dans le manuel de psql ( man psql ) ou dans l’aide en ligne de l’outil. Il est important de bien discerner les différents utilisateurs impliqués au niveau système et PostgreSQL.
Ouvrir plusieurs fenêtres ou consoles : au moins une avec l’utilisateur habituel (dalibo ici), une avec root, une avec l’utilisateur système postgres, une pour suivre le contenu des traces ( postgresql*.log ).
Nouvelle base bench : En tant qu’utilisateur système postgres, et avec l’utilitaire en ligne de commande createdb , créer une base de données nommée bench (elle appartiendra à postgres).
Avec psql , se connecter à la base bench en tant qu’utilisateur postgres.
Lister les bases de l’instance.
Se déconnecter de PostgreSQL.
Voir les tables : Pour remplir quelques tables dans la base bench, on utilise un outil de bench livré avec PostgreSQL :
**Exemple 4-150.** Exemple extrait du PDF\, page 267\.

```console
   /usr/pgsql-17/bin/pgbench -i --foreign-keys bench
```
Quelle est la taille de la base après alimentation?
Afficher la liste des tables de la base bench et leur taille.

Quelle est la structure de la table pgbench_accounts ?
Afficher l’ensemble des autres objets non système (index, séquences, vues…) de la base.
Nouvel utilisateur : Toujours en tant qu’utilisateur système postgres, avec l’utilitaire createuser , créer un rôle dupont (il doit avoir l’attribut LOGIN !).
Sous psql, afficher la liste des rôles (utilisateurs).
Voir les objets système :
Dans la base bench, afficher l’ensemble des tables systèmes (schéma pg_catalog ).
Afficher l’ensemble des vues systèmes (schéma pg_catalog ).
Manipuler les données :
Le but est de créer une copie de la table pgbench_tellers de la base bench avec
**Exemple 4-151.** Exemple extrait du PDF\, page 268\.

```sql
   CREATE TABLE AS . Afficher l’aide de cette commande.
```
Créer une table pgbench_tellers_svg , copie de la table pgbench_tellers .
Sortir le contenu de la table pgbench_tellers dans un fichier /tmp/pgbench_tellers.csv (commande \copy ).
Quel est le répertoire courant? \ Sans quitter psql , se déplacer vers /tmp/ , et en lister le contenu.
Afficher le contenu du fichier /tmp/pgbench_tellers.csv depuis psql .
Créer un fichier décompte.sql , contenant 3 requêtes pour compter le nombre de lignes dans les 3 tables de bench :
- Il devra écrire dans le fichier /tmp/décompte.txt .
- Le faire exécuter par psql .

Détruire la base : Supprimer la base bench.

## 4.11 TRAVAUX PRATIQUES (SOLUTIONS)
Les copies d’écran qui suivent proviennent d’un PostgreSQL 15 sous RockyLinux 8 paramétré en anglais. Elles peuvent différer légèrement selon la version de PostgreSQL, l’OS et la langue. Ouvrir plusieurs fenêtres ou consoles : au moins une avec l’utilisateur habituel (dalibo ici), une avec root, une avec l’utilisateur système postgres, une pour suivre le contenu des traces ( postgresql*.log ).
Pour devenir root :
**Exemple 4-152.** Exemple extrait du PDF\, page 270\.

```console
$ sudo su -
```
Pour devenir postgres :
**Exemple 4-153.** Exemple extrait du PDF\, page 270\.

```console
$ sudo -iu postgres
```
Pour voir le contenu des traces défiler, se connecter dans une nouvelle fenêtre à nouveau en tant que postgres, et aller chercher le fichier de traces. Sur Red Hat/CentOS, il est par défaut dans $PGDATA/log et son nom exact varie chaque jour :
**Exemple 4-154.** Exemple extrait du PDF\, page 270\.

```console
$ sudo -iu postgres
$ ls -l /var/lib/pgsql/15/data/log
```
-rw-------. 1 postgres postgres 4462 Jan 6 11:12 postgresql-Fri.log
**Exemple 4-155.** Exemple extrait du PDF\, page 270\.

```console
$ tail -f /var/lib/pgsql/15/data/log/postgresql-Tue.log
```
Par défaut ne s’afficheront que peu de messages : arrêt/redémarrage, erreur de connexion… Laisser la fenêtre ouverte en arrière-plan ; elle servira à analyser les problèmes. Nouvelle base bench : En tant qu’utilisateur système postgres, et avec l’utilitaire en ligne de commande createdb , créer une base de données nommée bench (elle appartiendra à postgres).
Si vous n’êtes pas déjà postgres :
**Exemple 4-156.** Exemple extrait du PDF\, page 270\.

```console
$ sudo -iu postgres

$ createdb --echo bench
SELECT pg_catalog.set_config('search_path', '', false)
CREATE DATABASE bench;
```
Noter que createdb ne fait que générer un ordre SQL. On peut aussi aussi directement exécuter cet ordre depuis psql sous un compte superutilisateur.
Avec psql , se connecter à la base bench en tant qu’utilisateur postgres.
**Exemple 4-157.** Exemple extrait du PDF\, page 270\.

```console
$ psql -d bench -U postgres
psql (15.1)
```
Type "help" for help.
**Exemple 4-158.** Exemple extrait du PDF\, page 270\.

```text
bench=#
```

Lister les bases de l’instance.
**Tableau 4-57.** Tableau ou résultat de commande extrait du PDF\, page 271\.

```text
  Name    | Owner   | Encoding |    Collate   |   Ctype    | …|   Access privileges
----------+----------+----------+-------------+-------------+--+--------------------
   ---
bench     | postgres | UTF8      | en_US.UTF-8 | en_US.UTF-8 | |
postgres | postgres | UTF8       | en_US.UTF-8 | en_US.UTF-8 | |
template0 | postgres | UTF8    | en_US.UTF-8 | en_US.UTF-8 | | =c/postgres          +
          |          |         |              |            | | postgres=CTc/postgres
template1 | postgres | UTF8    | en_US.UTF-8 | en_US.UTF-8 | | =c/postgres          +
          |          |         |              |            | | postgres=CTc/postgres
```
**Exemple 4-159.** Exemple extrait du PDF\, page 271\.

```text
bench=# \l

bench=# \l
                                                  List of databases
```
(4 rows)
(Deux colonnes indiquant à la source des librairies pour les collations ont été masquées.) Noter que depuis le shell, le même résultat est renvoyé par :
**Exemple 4-160.** Exemple extrait du PDF\, page 271\.

```console
$ psql -l
```
Se déconnecter de PostgreSQL.
**Exemple 4-161.** Exemple extrait du PDF\, page 271\.

```text
bench=# \q
```
( exit et Ctrl-D fonctionnent également.) Voir les tables : Pour remplir quelques tables dans la base bench, on utilise un outil de bench livré avec PostgreSQL :
**Exemple 4-162.** Exemple extrait du PDF\, page 271\.

```console
   /usr/pgsql-17/bin/pgbench -i --foreign-keys bench
```
L’outil est livré avec PostgreSQL, mais n’est pas dans les chemins par défaut sur Red Hat/CentOS/Rocky Linux. La connexion doit fonctionner depuis n’importe quel compte système, il s’agit d’une connexion cliente tout comme psql .
Quelle est la taille de la base après alimentation?
\l+ renvoie ceci :
**Exemple 4-163.** Exemple extrait du PDF\, page 271\.

```console
$ psql
postgres=# \l+
                                            List of databases
```
**Tableau 4-58.** Tableau ou résultat de commande extrait du PDF\, page 271\.

```text
   Name    | Owner   | Encoding |   Collate    |    …    |   Access privileges  |
    Size   | …
----------+----------+----------+-------------+---------+-----------------------+---
    ------+----
```

**Tableau 4-59.** Tableau ou résultat de commande extrait du PDF\, page 272\.

```text
bench     | postgres | UTF8              | en_US.UTF-8 |               |                           | 23 MB
   | …
postgres | postgres | UTF8               | en_US.UTF-8 |               |                           | 6477
   kB | …
template0 | postgres | UTF8              | en_US.UTF-8 |               | =c/postgres               +| 7297
   kB | …
           |         |                   |                 |               | postgres=CTc/postgres |
   | …
template1 | postgres | UTF8              | en_US.UTF-8 |               | =c/postgres               +| 7377
   kB | …
         |         |                 |               |             | postgres=CTc/postgres |            |
```
La base bench fait donc 23 Mo. Afficher la liste des tables de la base bench et leur taille.
\dt affiche les tables, \dt+ ajoute quelques informations dont la taille.
**Exemple 4-164.** Exemple extrait du PDF\, page 272\.

```console
postgres=# \c bench
```
You are now connected to database "bench" as user "postgres".
**Exemple 4-165.** Exemple extrait du PDF\, page 272\.

```text
bench=# \dt+
```
**Tableau 4-60.** Tableau ou résultat de commande extrait du PDF\, page 272\.

```text
 Schema |       Name       | Type | Owner     | Persistence | Access method | Size
   | Description
--------+------------------+-------+----------+-------------+---------------+-------
   --+-------------
 public | pgbench_accounts | table | postgres | permanent   | heap          | 13 MB
   |
 public | pgbench_branches | table | postgres | permanent   | heap          | 40 kB
   |
 public | pgbench_history | table | postgres | permanent    | heap         | 0 bytes
   |
 public | pgbench_tellers | table | postgres | permanent    | heap          | 40 kB
   |
```
(Il est courant d’utiliser \d et non \dt . S’afficheront alors aussi les vues et les séquences.)
Quelle est la structure de la table pgbench_accounts ?
\d (voire d+ ) est sans doute un des ordres les plus utiles à connaître :
**Tableau 4-61.** Tableau ou résultat de commande extrait du PDF\, page 272\.

```text
  Column |       Type      | Collation | Nullable | Default
----------+---------------+-----------+----------+---------
 aid      | integer        |           | not null |
 bid      | integer        |           |          |
 abalance | integer        |           |          |
 filler   | character(84) |            |          |
```
**Exemple 4-166.** Exemple extrait du PDF\, page 272\.

```text
bench=# \d pgbench_accounts
               Table "public.pgbench_accounts"
```
Indexes: "pgbench_accounts_pkey" PRIMARY KEY, btree (aid) Foreign-key constraints: "pgbench_accounts_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid) Referenced by:

TABLE "pgbench_history" CONSTRAINT "pgbench_history_aid_fkey" FOREIGN KEY (aid) REFERENCES pgbench_accounts(aid)
La table a quatre colonnes : aid , bid , abalance , filler .
La première porte la clé primaire (et ne peut donc être à NULL ).
La seconde est une clé étrangère pointant vers pgbench_branches .
La table pgbench_history porte une clé étrangère pointant vers la clé primaire de cette table.
Afficher l’ensemble des autres objets non système (index, séquences, vues…) de la base.
Les index :
**Exemple 4-167.** Exemple extrait du PDF\, page 273\.

```text
bench=# \di+
                                                     List of relations
```
**Tableau 4-62.** Tableau ou résultat de commande extrait du PDF\, page 273\.

```text
Schema |         Name         | Type | Owner     |      Table      | Persistence | …
   | Size    | …
-------+-----------------------+-------+----------+------------------+-------------
   +---+---------+--
public | pgbench_accounts_pkey | index | postgres | pgbench_accounts | permanent    |
   | 2208 kB |
public | pgbench_branches_pkey | index | postgres | pgbench_branches | permanent    |
   | 16 kB   |
public | pgbench_tellers_pkey | index | postgres | pgbench_tellers | permanent      |
   | 16 kB   |
```
Ces index sont ceux nécessités par les clés primaires. Il n’y a ni séquence ni vue :
**Exemple 4-168.** Exemple extrait du PDF\, page 273\.

```text
bench=# \ds
```
N'a trouvé aucune relation.
**Exemple 4-169.** Exemple extrait du PDF\, page 273\.

```text
bench=# \dv
```
N'a trouvé aucune relation.
Nouvel utilisateur : Toujours en tant qu’utilisateur système postgres, avec l’utilitaire createuser , créer un rôle dupont (il doit avoir l’attribut LOGIN !).
**Exemple 4-170.** Exemple extrait du PDF\, page 273\.

```console
$ createuser --echo --login dupont
SELECT pg_catalog.set_config('search_path', '', false)
CREATE ROLE dupont NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
```
Sous psql, afficher la liste des rôles (utilisateurs).
\du affiche les rôles (sauf ceux système).
Il n’y a que le superutilisateur postgres (par défaut), et dupont créé tout à l’heure mais sans droit particulier :

**Exemple 4-171.** Exemple extrait du PDF\, page 274\.

```console
postgres=# \du
                                   List of roles
```
**Tableau 4-63.** Tableau ou résultat de commande extrait du PDF\, page 274\.

```text
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 dupont    |                                                            | {}
 postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
```
Voir les objets système :
Dans la base bench, afficher l’ensemble des tables systèmes (schéma pg_catalog ).
**Tableau 4-64.** Tableau ou résultat de commande extrait du PDF\, page 274\.

```text
    Schema   |           Name          | Type | Owner
------------+--------------------------+-------+----------
  pg_catalog | pg_aggregate            | table | postgres
  pg_catalog | pg_aggregate            | table | postgres
  pg_catalog | pg_am                   | table | postgres
```
**Exemple 4-172.** Exemple extrait du PDF\, page 274\.

```text
bench=# \dt pg_catalog.*
                     List of relations
```
…
**Tableau 4-65.** Tableau ou résultat de commande extrait du PDF\, page 274\.

```text
  pg_catalog | pg_statistic            | table | postgres
```
… (64 rows)
Notons que pour afficher uniquement les tables système, on préférera le raccourci \dtS .
Afficher l’ensemble des vues systèmes (schéma pg_catalog ).
Certaines des vues ci-dessous sont très utiles dans la vie de DBA :
**Tableau 4-66.** Tableau ou résultat de commande extrait du PDF\, page 274\.

```text
    Schema   |              Name               | Type | Owner
------------+---------------------------------+------+----------
  pg_catalog | pg_available_extension_versions | view | postgres
  pg_catalog | pg_available_extensions         | view | postgres
  pg_catalog | pg_backend_memory_contexts      | view | postgres
  pg_catalog | pg_config                       | view | postgress
  pg_catalog | pg_cursors                      | view | postgres
  pg_catalog | pg_file_settings                | view | postgres
  pg_catalog | pg_group                        | view | postgres
  pg_catalog | pg_hba_file_rules               | view | postgres
  pg_catalog | pg_ident_file_mappings          | view | postgres
  pg_catalog | pg_indexes                      | view | postgres
  pg_catalog | pg_locks                        | view | postgres
  pg_catalog | pg_matviews                     | view | postgres
```
**Exemple 4-173.** Exemple extrait du PDF\, page 274\.

```text
bench=# \dv pg_catalog.*
                        List of relations
```
…
**Tableau 4-67.** Tableau ou résultat de commande extrait du PDF\, page 274\.

```text
  pg_catalog | pg_roles                        | view | postgres
```
…
**Tableau 4-68.** Tableau ou résultat de commande extrait du PDF\, page 274\.

```text
  pg_catalog | pg_settings                     | view | postgres
  pg_catalog | pg_shadow                       | view | postgres
```
…
**Tableau 4-69.** Tableau ou résultat de commande extrait du PDF\, page 274\.

```text
  pg_catalog | pg_stat_activity                | view | postgres
```
…
**Tableau 4-70.** Tableau ou résultat de commande extrait du PDF\, page 274\.

```text
  pg_catalog | pg_stat_archiver                | view | postgres
```
…

**Tableau 4-71.** Tableau ou résultat de commande extrait du PDF\, page 275\.

```text
    pg_catalog | pg_stat_database                    | view | postgres
```
…
**Tableau 4-72.** Tableau ou résultat de commande extrait du PDF\, page 275\.

```text
    pg_catalog | pg_stat_progress_analyze            | view | postgres
    pg_catalog | pg_stat_progress_basebackup         | view | postgres
    pg_catalog | pg_stat_progress_cluster            | view | postgres
    pg_catalog | pg_stat_progress_copy               | view | postgres
    pg_catalog | pg_stat_progress_create_index       | view | postgres
    pg_catalog | pg_stat_progress_vacuum             | view | postgres
```
…
**Tableau 4-73.** Tableau ou résultat de commande extrait du PDF\, page 275\.

```text
    pg_catalog | pg_stat_replication                 | view | postgres
    pg_catalog | pg_stat_replication_slots           | view | postgres
```
…
**Tableau 4-74.** Tableau ou résultat de commande extrait du PDF\, page 275\.

```text
    pg_catalog | pg_statio_all_tables                | view | postgres
```
…
**Tableau 4-75.** Tableau ou résultat de commande extrait du PDF\, page 275\.

```text
    pg_catalog | pg_statio_user_indexes              | view | postgres
    pg_catalog | pg_statio_user_sequences            | view | postgres
    pg_catalog | pg_statio_user_tables               | view | postgres
    pg_catalog | pg_stats                            | view | postgres
```
… (75 rows)
Là encore, \dvS est un équivalent pour les tables systèmes. Manipuler les données :
Le but est de créer une copie de la table pgbench_tellers de la base bench avec
**Exemple 4-174.** Exemple extrait du PDF\, page 275\.

```console
      CREATE TABLE AS . Afficher l’aide de cette commande.

bench=# \h CREATE TABLE AS
postgres=# \c bench
```
You are now connected to database "bench" as user "postgres".
**Exemple 4-175.** Exemple extrait du PDF\, page 275\.

```sql
bench=# \h CREATE TABLE AS
```
Command: CREATE TABLE AS Description: define a new table from the results of a query Syntax:
**Tableau 4-76.** Tableau ou résultat de commande extrait du PDF\, page 275\.

```text
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ]
   table_name
    [ (column_name [, ...] ) ]
    [ USING method ]
    [ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]
```
URL: https://www.postgresql.org/docs/15/sql-createtableas.html
Créer une table pgbench_tellers_svg , copie de la table pgbench_tellers .
**Exemple 4-176.** Exemple extrait du PDF\, page 275\.

```sql
bench=# CREATE TABLE pgbench_tellers_svg AS SELECT * FROM pgbench_tellers ;
```
SELECT 10

Sortir le contenu de la table pgbench_tellers dans un fichier /tmp/pgbench_tellers.csv (commande \copy ).
**Exemple 4-177.** Exemple extrait du PDF\, page 276\.

```sql
bench=# \copy pgbench_tellers TO '/tmp/pgbench_tellers.csv'
```
COPY 10
Rappelons que la commande \copy est propre à psql (outil client), et est exécutée sur le client, avec l’accès au système de fichiers du client. \copy ne doit pas être confondue avec COPY , commande similaire exécutée par le serveur, et n’ayant accès qu’au système de fichiers du serveur. Il est important de bien connaître la distinction même si le client est utilisé ici sur le serveur avec l’utilisateur système postgres.
Quel est le répertoire courant? \ Sans quitter psql , se déplacer vers /tmp/ , et en lister le contenu.
Le répertoire courant est celui en cours quand psql a été lancé. Selon les cas, ce peut être /home/dalibo , /var/lib/pgsql/ … On peut se déplacer avec \cd .
**Exemple 4-178.** Exemple extrait du PDF\, page 276\.

```text
bench=# \! pwd
```
/home/dalibo
**Exemple 4-179.** Exemple extrait du PDF\, page 276\.

```text
bench=# \cd /tmp

bench=# \! ls
```
pgbench_tellers.csv systemd-private-1b08135528d846088bb892f5a82aec9e-bolt.service-1hjHUH …
**Exemple 4-180.** Exemple extrait du PDF\, page 276\.

```text
bench=#
```
Afficher le contenu du fichier /tmp/pgbench_tellers.csv depuis psql .
Son contenu est le suivant :
**Exemple 4-181.** Exemple extrait du PDF\, page 276\.

```text
bench=# \! cat /tmp/pgbench_tellers.csv
1       1       0       \N
2       1       0       \N
3       1       0       \N
4       1       0       \N
5       1       0       \N
6       1       0       \N
7       1       0       \N
8       1       0       \N
9       1       0       \N
10      1       0       \N
```
On aurait pu l’ouvrir avec un éditeur de texte ou n’importe quel autre programme présent sur le client :
**Exemple 4-182.** Exemple extrait du PDF\, page 276\.

```text
bench=# \! vi /tmp/pgbench_tellers.csv
```

Créer un fichier décompte.sql , contenant 3 requêtes pour compter le nombre de lignes dans les 3 tables de bench :
- Il devra écrire dans le fichier /tmp/décompte.txt .
- Le faire exécuter par psql .
Le fichier doit contenir ceci :
**Exemple 4-183.** Exemple extrait du PDF\, page 277\.

```sql
\o /tmp/décompte.txt
SELECT COUNT(*) FROM pgbench_accounts ;
SELECT COUNT(*) FROM pgbench_tellers ;
SELECT COUNT(*) FROM pgbench_branches ;
```
La première ligne ordonne d’écrire la sortie des ordres dans le fichier indiqué. Il peut être appelé par :
**Exemple 4-184.** Exemple extrait du PDF\, page 277\.

```console
$ psql -d bench -f /tmp/décompte.sql
```
ou :
**Exemple 4-185.** Exemple extrait du PDF\, page 277\.

```console
$ psql -d bench < /tmp/décompte.sql
```
Vérifier ensuite le contenu de /tmp/décompte.txt . Détruire la base : Supprimer la base bench.
Depuis la ligne de commande du système d’exploitation, en tant qu’utilisateur système postgres :
**Exemple 4-186.** Exemple extrait du PDF\, page 277\.

```console
$ dropdb --echo bench
SELECT pg_catalog.set_config('search_path', '', false);
DROP DATABASE bench;
```
Alternativement, si l’on est connecté en tant que superutilisateur à l’instance (pas sous la base à supprimer!) :
**Exemple 4-187.** Exemple extrait du PDF\, page 277\.

```console
postgres=# DROP DATABASE bench ;
```
DROP DATABASE
Noter l’absence de demande de confirmation!

---

Source : [manuel complet PDF](/downloads/dalibo/manuals/dba1-handout.pdf), révision 26.09, pages PDF 195-278.