Nous avons dans les articles précédents vu comment installer PostgreSQL et quels étaient les composants d’une base. Nous abordons maintenant le point de l’action sur le moteur avec deux outils orientés administration de la base, généralement utilisés par les DBAs. Il s’agit de psql et de PgAdmin. Nous aborderons aussi dans cet article la configuration de connexion au travers des fichiers de configuration du serveur.

psql

psql est un outil installé en même temps que les binaires du serveur, nous l’utiliserons en tant qu’utilisateur PostgreSQL. Il s’utilise en mode ligne de commande et permet d’interagir avec le serveur soit en interactif soit en batch.

La gestion de la connection.

Ce paragraphe n’a pas pour objectif de faire un point complet sur la gestion des authentifications mais de montrer simplement la configuration d’un accés local.

Donc à partir de notre serveur, connectons-nous au serveur gérant les bases postresql.

pib@dbi-NoteBook-01:~$ ssh [email protected]
[email protected]'s password: 
Welcome to Ubuntu 11.10 (GNU/Linux 3.0.0-17-generic x86_64)
* Documentation:  https://help.ubuntu.com/
New release '12.04 LTS' available.
Run 'do-release-upgrade' to upgrade to it.
Last login: Tue May  8 05:56:27 2012 from 192.168.165.1
Lançons la commande psql de connexion à la base .
postgres@ubuntu:~$ psql
psql (8.4.8)
Type "help" for help.
postgres=# select version();
postgres=# l
 List of databases
 Name     |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 gis       | pib      | 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
(4 rows)
postgres=#

 

Nous visualisons grâce au message de connexion quelle version de psql nous utilisons, puis avec la commande sql à quelle version de base nous sommes connectés par default. Si le cluster n’avait pas été lancé nous aurions eu le message suivant:

psql: could not connect to server: No such file or directory
 Is the server running locally and accepting
 connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Mais à quoi psql se connecte-t-il  par defaut ? Je veux dire à quel utilisateur, à quel cluster de base …
psql se connect à un socket Unix de la machine local avec le port, la base et l’utilisateur par défaut.

  • Le numéro de port par defaut est de 5432 ( choix à la compilation).
  • L’utilisateur et la base sera le nom de votre user unix.

On change ces paramètres par défaut, soit en passant des options sur la ligne de commande de psql soit en positionnant des variables d’environnement. EntrepriseDB dans son installation nous propose cela et construit un script pour positionner cet environnement. Ainsi, en executant le script pg_env.sh de l’installation /u00/app/postgresql/product/9.1.3, nous pourrons en lançant la commande psql nous retrouver connectés au bon environnement.

postgres@ubuntu:~$ . /u00/app/postgresql/product/9.1.3/pg_env.sh
postgres@ubuntu:~$ psql
psql.bin (9.1.3)
Type "help" for help.
postgres=# select version();
 version                                                    
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
(1 row)
postgres=# l
 List of databases
 Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   -----------+----------+----------+-------------+-------------+-----------------------
 cave      | caviste  | 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
(4 rows)
postgres=#

 
Liste des variables d’environnement positionnées:

export PATH=/u00/app/postgresql/product/9.1.3/bin:$PATH
export PGDATA=/u01/postgresql/data/prod913
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5434
export PGLOCALEDIR=/u00/app/postgresql/product/9.1.3/share/locale
export MANPATH=$MANPATH:/u00/app/postgresql/product/9.1.3/share/man

 
Notons que dans ces deux configurations, aucun mot de passe ne nous a été demandé, puisqu’il s’agit là d’un paramétrage au niveau serveur. En effet, c’est le fichier de configuration pg_hba.conf qui définit les droits de connexion au cluster de base.

TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         postgres                          ident
local   all         all                               md5

 

Les deux lignes ci-dessus autorisent l’utilisateur PostgreSQL à se connecter à toutes les bases sans authentification et forçent les autres utilisateurs à saisir le mot de passe de l’utilisateur auquel ils tenteront de ce connecter. De plus, il leur sera impossible de se connecter à l’utilisateur PostgreSQL.

N. B.: PostgreSQL est trés riche et trés fin dans la gestion des authentifications, une douzaine de méthodes sont disponibles, dont kerberos, mot de passe, ident (OS), ldap, radius.

Les commandes

Elles sont de deux natures :

  • les meta-commandes
  • les commandes du language SQL

Les meta-commandes sont dans psql toute commande qui commence par un antislash non échappé elles sont listables par la commande ?

Je ne vous donnerais pas ici la liste des commandes SQL ni des Meta-commandes. Vous trouverez dans les références documentaires un pointeur sur ces deux groupes de commande de psql.

Voici deux exemples de méta-commande :

s – qui liste l’historique des commandes de psql


s
q
select version();
l
q
select version();
l

d nomtable – le desc table d’oracle

cave=> d vin
 Table "public.vin"
 Column     |  Type   |                    Modifiers                     
----------------+---------+--------------------------------------------------
 id             | integer | not null default nextval('vin_id_seq'::regclass)
 recoltant_id   | integer | 
 appellation_id | integer | not null
 type_vin_id    | integer | not null
Indexes:
 "vin_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
 "vin_appellation_id_fkey" FOREIGN KEY (appellation_id) REFERENCES appellation(id)
 "vin_recoltant_id_fkey" FOREIGN KEY (recoltant_id) REFERENCES recoltant(id)
 "vin_type_vin_id_fkey" FOREIGN KEY (type_vin_id) REFERENCES type_vin(id)
Referenced by:
 TABLE "stock" CONSTRAINT "stock_vin_id_fkey" FOREIGN KEY (vin_id) REFERENCES vin(id)

Les commandes sql elles sont listable par la meta-commande h attention pas H qui lui passe la sortie en format html.

cave=> select count(*) from vin;
 count 
-------
 6058
(1 row)
cave=> H
Output format is html.
cave=> select count(*) from vin;

 

count
6058

Les options

Les options suivent immédiatement le nom de la commande sur la ligne de commande, et sont séparées entre elles par des espaces. psql en compte environ 30:

-a, –echo-all
-A, –no-align
-c command, –command=command
-d dbname, –dbname=dbname
-e, –echo-queries
-E, –echo-hidden
-f filename, –file=filename
-F separator, –field-separator=separator
-h hostname, –host=hostname
-H, –html
-l, –list
-L filename, –log-file=filename
-n, –no-readline
-o filename, –output=filename
-p port, –port=port
-P assignment, –pset=assignment
-q, –quiet
-R separator, –record-separator=separator
-s, –single-step
-S, –single-line
-t, –tuples-only
-T table_options, –table-attr=table_options
-U username, –username=username
-v assignment, –set=assignment, –variable=assignment
-V, –version
-w, –no-password
-W, –password
-x, –expanded
-X,, –no-psqlrc
-1, –single-transaction

Les variables

Les variables sont des paires nom/valeur où la valeur peut être toute chaîne. Pour initialiser des variables, utilisez la méta-commande psql set ou une des options -v affectation, –set variable=affectation, –variable=affectation

Un exemple d’utilisation conjointe des variables et options:
Soit une requête affichant n lignes de la table région.

cat rqt1.sql
--
-- subtitution de variable 
--
select libelle from region limit :VinP;
q

 

Le lancement de la commande suivante  produit l’affichage des libelles restreint au nombre de lignes transmis en paramêtre.

pib@ubuntu:~/sql$ psql -H -q -U caviste --set VinP=3 cave -f rqt1.sql
Password for user caviste:

 

libelle
Provence et Corse
Bourgogne
Alsace

 

(3 rows)

PgAdmin3

PgAdmin est un projet indépendant de la communauté PostgreSQL, il est graphique et  multiplateforme.

Les objectifs de PgAdmin est la gestion de tous les aspects du serveur de base:

  • utilisateurs et groupes
  • Base
  • Schémas
  • Tables, Indexes, triggers, règles et droits.
  • Vues, sèquences et function …

Lors d’une installation avec l’installateur d’entrepriseDB, une version de pgadmin3 est installée.

Dans le cas d’une installation de type debian / Ubuntu ou d’une autre distribution il est disponible directement depuis les miroirs. La version courante est la version 1.14 , disponible pour FreeBSD, linux, Solaris, windows et Mac OSX. On utilise pgadmin3  soit en connexion sur le serveur soit de son poste.

Utiliser PGadmin connecté sur le serveur

Dans notre situation, il n’y pas d’accés à l’interface graphique du serveur, nous utiliserons une connexion ssh avec la redirection X11.

ssh -X pib@ubuntu

après avoir positionné sont environnement on lance la commande

postgres@ubuntu:~$ prod913
postgres@ubuntu:~$ env | grep PG
PGPORT=5434
PGUSER=postgres
PGDATABASE=postgres
PGLOCALEDIR=/u00/app/postgresql/product/9.1.3/share/locale
PGDATA=/u01/postgresql/data/prod913
postgres@ubuntu:~$ /u00/app/postgresql/product/9.1.3/pgAdmin3/bin/pgadmin3

Screenshot-pgadmin-0

On notera que dans le cas de pgadmin et du fait de notre configuration, il y a authentification. Une fois connecté à un cluster de base on peut travailler sur les objets tablespace, role , et tous les objets d’une base.

Screenshot_pgadmin-1
Il comporte un editeur de query et un explain plan graphique dont voici 2 screenshots :

Screenshot-Query_-_cave_on_postgreslocalhost5434_Screenshot-Query_-_cave_on_postgreslocalhost5434_-1

Utiliser PgAdmin à partir de son poste.

Lorsque l’on utilise PgAdmin à partir de son poste le mieux est d’utiliser une redirection de port entre le poste locale et le serveur distant.

ssh -fNg -L 5440:localhost:5434 [email protected]

Puis de lancer localement pgAdmin3 .

Screenshot_pgadmin_3

Un avertissement nous montre qu’il est important d’avoir une version de pgadmin en adéquation avec le niveau du serveur PostgreSQL. soit la version 1.14 pour la version 9.1.3 du serveur.

Si l’approche graphique est séduisante, il ne faut pas oublier que suivant les conditions il n’est pas possible d’accéder à une interface graphique, d’ou la présentation de psql précèdement. Une autre interface graphique sera abordée dans un prochain article il s’agit de phpPgAdmin. Nous verrons que les objectifs de phpPgAdmin sont les mêmes que ceux de PgAdmin.

Screenshot-phpPgAdmin_-_Google_Chrome

Références documentaires

http://docs.postgresqlfr.org/9.1/app-psql.html
http://docs.postgresqlfr.org/9.1/libpq-envars.htmlhttp://www.pgadmin.org/
http://www.pgadmin.org/docs/1.14/index.html