{"id":2574,"date":"2012-07-30T03:20:00","date_gmt":"2012-07-30T01:20:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/"},"modified":"2012-07-30T03:20:00","modified_gmt":"2012-07-30T01:20:00","slug":"postgresql-outils-dadministration","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/","title":{"rendered":"PostgreSQL: Un aper\u00e7u des outils d&#8217;administration"},"content":{"rendered":"<p>Nous avons dans les articles pr\u00e9c\u00e9dents vu comment installer PostgreSQL et quels \u00e9taient les composants d&#8217;une base. Nous abordons maintenant le point de l&#8217;action sur le moteur avec deux outils orient\u00e9s administration de la base, g\u00e9n\u00e9ralement utilis\u00e9s par les DBAs. Il s&#8217;agit de psql et de PgAdmin. Nous aborderons aussi dans cet article la configuration de connexion au travers des fichiers de configuration du serveur.<\/p>\n<h3><\/h3>\n<h3>psql<\/h3>\n<p>psql est un outil install\u00e9 en m\u00eame temps que les binaires du serveur, nous l&#8217;utiliserons en tant qu&#8217;utilisateur PostgreSQL. Il s&#8217;utilise en mode ligne de commande et permet d&#8217;interagir avec le serveur soit en interactif soit en batch.<\/p>\n<h4><\/h4>\n<h3>La gestion de la connection.<\/h3>\n<p>Ce paragraphe n&#8217;a pas pour objectif de faire un point complet sur la gestion des authentifications mais de montrer simplement la configuration d&#8217;un acc\u00e9s local.<\/p>\n<p>Donc \u00e0 partir de notre serveur, connectons-nous au serveur g\u00e9rant les bases postresql.<\/p>\n<pre>pib@dbi-NoteBook-01:~$ ssh postgres@192.168.165.130\npostgres@192.168.165.130's password: \nWelcome to Ubuntu 11.10 (GNU\/Linux 3.0.0-17-generic x86_64)<\/pre>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">* Documentation:\u00a0 https:\/\/help.ubuntu.com\/\nNew release '12.04 LTS' available.\nRun 'do-release-upgrade' to upgrade to it.\nLast login: Tue May\u00a0 8 05:56:27 2012 from 192.168.165.1\nLan\u00e7ons la commande psql de connexion \u00e0 la base .\npostgres@ubuntu:~$ psql\npsql (8.4.8)\nType \"help\" for help.\npostgres=# select version();\npostgres=# l\n List of databases\n Name\u00a0\u00a0\u00a0\u00a0 |\u00a0 Owner\u00a0\u00a0 | Encoding |\u00a0 Collation\u00a0 |\u00a0\u00a0\u00a0 Ctype\u00a0\u00a0\u00a0 |\u00a0\u00a0 Access privileges\u00a0 \n-----------+----------+----------+-------------+-------------+-----------------------\n gis\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | pib\u00a0\u00a0\u00a0\u00a0\u00a0 | UTF8\u00a0\u00a0\u00a0\u00a0 | en_US.UTF-8 | en_US.UTF-8 | \n postgres\u00a0 | postgres | UTF8\u00a0\u00a0\u00a0\u00a0 | en_US.UTF-8 | en_US.UTF-8 | \n template0 | postgres | UTF8\u00a0\u00a0\u00a0\u00a0 | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres\n : postgres=CTc\/postgres\n template1 | postgres | UTF8\u00a0\u00a0\u00a0\u00a0 | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres\n : postgres=CTc\/postgres\n(4 rows)\npostgres=#<\/pre>\n<p>&nbsp;<\/p>\n<p>Nous visualisons gr\u00e2ce au message de connexion quelle version de psql nous utilisons, puis avec la commande sql \u00e0 quelle version de base nous sommes connect\u00e9s par default. Si le cluster n&#8217;avait pas \u00e9t\u00e9 lanc\u00e9 nous aurions eu le message suivant:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">psql: could not connect to server: No such file or directory\n Is the server running locally and accepting\n connections on Unix domain socket \"\/var\/run\/postgresql\/.s.PGSQL.5432\"?<\/pre>\n<p>Mais \u00e0 quoi psql se connecte-t-il\u00a0 par defaut ? Je veux dire \u00e0 quel utilisateur, \u00e0 quel cluster de base &#8230;<br \/>\npsql se connect \u00e0 un socket Unix de la machine local avec le port, la base et l&#8217;utilisateur par d\u00e9faut.<\/p>\n<ul>\n<li>Le num\u00e9ro de port par defaut est de 5432 ( choix \u00e0 la compilation).<\/li>\n<li>L&#8217;utilisateur et la base sera le nom de votre user unix.<\/li>\n<\/ul>\n<p>On change ces param\u00e8tres par d\u00e9faut, soit en passant des options sur la ligne de commande de psql soit en positionnant des variables d&#8217;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&#8217;installation \/u00\/app\/postgresql\/product\/9.1.3, nous pourrons en lan\u00e7ant la commande psql nous retrouver connect\u00e9s au bon environnement.<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">postgres@ubuntu:~$ . \/u00\/app\/postgresql\/product\/9.1.3\/pg_env.sh\npostgres@ubuntu:~$ psql\npsql.bin (9.1.3)\nType \"help\" for help.\npostgres=# select version();\n version\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n---------------------------------------------------------------------------------------------------------------\n 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\n(1 row)\npostgres=# l\n List of databases\n Name\u00a0\u00a0\u00a0 |\u00a0 Owner\u00a0\u00a0 | Encoding |\u00a0\u00a0 Collate\u00a0\u00a0 |\u00a0\u00a0\u00a0 Ctype\u00a0\u00a0\u00a0 |\u00a0\u00a0 Access privileges\u00a0\u00a0 -----------+----------+----------+-------------+-------------+-----------------------\n cave\u00a0\u00a0\u00a0\u00a0\u00a0 | caviste\u00a0 | UTF8\u00a0\u00a0\u00a0\u00a0 | en_US.UTF-8 | en_US.UTF-8 | \n postgres\u00a0 | postgres | UTF8\u00a0\u00a0\u00a0\u00a0 | en_US.UTF-8 | en_US.UTF-8 | \n template0 | postgres | UTF8\u00a0\u00a0\u00a0\u00a0 | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +\n |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | postgres=CTc\/postgres\n template1 | postgres | UTF8\u00a0\u00a0\u00a0\u00a0 | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +\n |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | postgres=CTc\/postgres\n(4 rows)\npostgres=#<\/pre>\n<p>&nbsp;<br \/>\nListe des variables d&#8217;environnement positionn\u00e9es:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\"><samp><\/samp>export PATH=\/u00\/app\/postgresql\/product\/9.1.3\/bin:$PATH\nexport PGDATA=\/u01\/postgresql\/data\/prod913\nexport PGDATABASE=postgres\nexport PGUSER=postgres\nexport PGPORT=5434\nexport PGLOCALEDIR=\/u00\/app\/postgresql\/product\/9.1.3\/share\/locale\nexport MANPATH=$MANPATH:\/u00\/app\/postgresql\/product\/9.1.3\/share\/man<\/pre>\n<p>&nbsp;<br \/>\nNotons que dans ces deux configurations, aucun mot de passe ne nous a \u00e9t\u00e9 demand\u00e9, puisqu&#8217;il s&#8217;agit l\u00e0 d&#8217;un param\u00e9trage au niveau serveur. En effet, c&#8217;est le fichier de configuration pg_hba.conf qui d\u00e9finit les droits de connexion au cluster de base.<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\"><samp><\/samp>TYPE\u00a0 DATABASE\u00a0\u00a0\u00a0 USER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CIDR-ADDRESS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 METHOD\n# \"local\" is for Unix domain socket connections only\nlocal\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 postgres\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ident\nlocal\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 md5<\/pre>\n<p>&nbsp;<\/p>\n<p>Les deux lignes ci-dessus autorisent l&#8217;utilisateur PostgreSQL \u00e0 se connecter \u00e0 toutes les bases sans authentification et for\u00e7ent les autres utilisateurs \u00e0 saisir le mot de passe de l&#8217;utilisateur auquel ils tenteront de ce connecter. De plus, il leur sera impossible de se connecter \u00e0 l&#8217;utilisateur PostgreSQL.<\/p>\n<p>N. B.: PostgreSQL est tr\u00e9s riche et tr\u00e9s fin dans la gestion des authentifications, une douzaine de m\u00e9thodes sont disponibles, dont kerberos, mot de passe, ident (OS), ldap, radius.<\/p>\n<h4><\/h4>\n<h3>Les commandes<\/h3>\n<p>Elles sont de deux natures :<\/p>\n<ul>\n<li>les meta-commandes<\/li>\n<li>les commandes du language SQL<\/li>\n<\/ul>\n<p>Les meta-commandes sont dans psql toute commande qui commence par un antislash non \u00e9chapp\u00e9 elles sont listables par la commande ?<\/p>\n<p>Je ne vous donnerais pas ici la liste des commandes SQL ni des Meta-commandes. Vous trouverez dans les r\u00e9f\u00e9rences documentaires un pointeur sur ces deux groupes de commande de psql.<\/p>\n<p>Voici deux exemples de m\u00e9ta-commande :<\/p>\n<h4>s &#8211; qui liste l&#8217;historique des commandes de psql<\/h4>\n<p><samp><br \/>\ns<br \/>\nq<br \/>\nselect version();<br \/>\nl<br \/>\nq<br \/>\nselect version();<br \/>\nl<\/samp><\/p>\n<h5><\/h5>\n<h4>d nomtable &#8211; le desc table d&#8217;oracle<\/h4>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">cave=&gt; d vin\n Table \"public.vin\"\n Column\u00a0\u00a0\u00a0\u00a0 |\u00a0 Type\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Modifiers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\n----------------+---------+--------------------------------------------------\n id\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | integer | not null default nextval('vin_id_seq'::regclass)\n recoltant_id\u00a0\u00a0 | integer | \n appellation_id | integer | not null\n type_vin_id\u00a0\u00a0\u00a0 | integer | not null\nIndexes:\n \"vin_pkey\" PRIMARY KEY, btree (id)\nForeign-key constraints:\n \"vin_appellation_id_fkey\" FOREIGN KEY (appellation_id) REFERENCES appellation(id)\n \"vin_recoltant_id_fkey\" FOREIGN KEY (recoltant_id) REFERENCES recoltant(id)\n \"vin_type_vin_id_fkey\" FOREIGN KEY (type_vin_id) REFERENCES type_vin(id)\nReferenced by:\n TABLE \"stock\" CONSTRAINT \"stock_vin_id_fkey\" FOREIGN KEY (vin_id) REFERENCES vin(id)<\/pre>\n<p>Les commandes sql elles sont listable par la meta-commande h attention pas H qui lui passe la sortie en format html.<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">cave=&gt; select count(*) from vin;\n count \n-------\n 6058\n(1 row)\ncave=&gt; H\nOutput format is html.\ncave=&gt; select count(*) from vin;<\/pre>\n<p>&nbsp;<\/p>\n<table border=\"1\">\n<tbody>\n<tr>\n<th align=\"center\">count<\/th>\n<\/tr>\n<tr valign=\"top\">\n<td align=\"right\">6058<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h4><\/h4>\n<h3>Les options<\/h3>\n<p>Les options suivent imm\u00e9diatement le nom de la commande sur la ligne de commande, et sont s\u00e9par\u00e9es entre elles par des espaces. psql en compte environ 30:<\/p>\n<p style=\"padding-left: 30px;\">-a, &#8211;echo-all<br \/>\n-A, &#8211;no-align<br \/>\n-c command, &#8211;command=command<br \/>\n-d dbname, &#8211;dbname=dbname<br \/>\n-e, &#8211;echo-queries<br \/>\n-E, &#8211;echo-hidden<br \/>\n-f filename, &#8211;file=filename<br \/>\n-F separator, &#8211;field-separator=separator<br \/>\n-h hostname, &#8211;host=hostname<br \/>\n-H, &#8211;html<br \/>\n-l, &#8211;list<br \/>\n-L filename, &#8211;log-file=filename<br \/>\n-n, &#8211;no-readline<br \/>\n-o filename, &#8211;output=filename<br \/>\n-p port, &#8211;port=port<br \/>\n-P assignment, &#8211;pset=assignment<br \/>\n-q, &#8211;quiet<br \/>\n-R separator, &#8211;record-separator=separator<br \/>\n-s, &#8211;single-step<br \/>\n-S, &#8211;single-line<br \/>\n-t, &#8211;tuples-only<br \/>\n-T table_options, &#8211;table-attr=table_options<br \/>\n-U username, &#8211;username=username<br \/>\n-v assignment, &#8211;set=assignment, &#8211;variable=assignment<br \/>\n-V, &#8211;version<br \/>\n-w, &#8211;no-password<br \/>\n-W, &#8211;password<br \/>\n-x, &#8211;expanded<br \/>\n-X,, &#8211;no-psqlrc<br \/>\n-1, &#8211;single-transaction<\/p>\n<h3>Les variables<\/h3>\n<p>Les variables sont des paires nom\/valeur o\u00f9 la valeur peut \u00eatre toute cha\u00eene. Pour initialiser des variables, utilisez la m\u00e9ta-commande psql set ou une des options -v affectation, &#8211;set variable=affectation, &#8211;variable=affectation<\/p>\n<p>Un exemple d&#8217;utilisation conjointe des variables et options:<br \/>\nSoit une requ\u00eate affichant n lignes de la table r\u00e9gion.<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">cat rqt1.sql\n--\n-- subtitution de variable \n--\nselect libelle from region limit :VinP;\nq<\/pre>\n<p>&nbsp;<\/p>\n<p>Le lancement de la commande suivante\u00a0 produit l&#8217;affichage des libelles restreint au nombre de lignes transmis en param\u00eatre.<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">pib@ubuntu:~\/sql$ psql -H -q -U caviste --set VinP=3 cave -f rqt1.sql\nPassword for user caviste:<\/pre>\n<p>&nbsp;<\/p>\n<table border=\"1\">\n<tbody>\n<tr>\n<th align=\"center\">libelle<\/th>\n<\/tr>\n<tr valign=\"top\">\n<td align=\"left\">Provence et Corse<\/td>\n<\/tr>\n<tr valign=\"top\">\n<td align=\"left\">Bourgogne<\/td>\n<\/tr>\n<tr valign=\"top\">\n<td align=\"left\">Alsace<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n<p>(3 rows)<\/p>\n<h3>PgAdmin3<\/h3>\n<p>PgAdmin est un projet ind\u00e9pendant de la communaut\u00e9 PostgreSQL, il est graphique et\u00a0 multiplateforme.<\/p>\n<p>Les objectifs de PgAdmin est la gestion de tous les aspects du serveur de base:<\/p>\n<ul>\n<li>utilisateurs et groupes<\/li>\n<li>Base<\/li>\n<li>Sch\u00e9mas<\/li>\n<li>Tables, Indexes, triggers, r\u00e8gles et droits.<\/li>\n<li>Vues, s\u00e8quences et function &#8230;<\/li>\n<\/ul>\n<p>Lors d&#8217;une installation avec l&#8217;installateur d&#8217;entrepriseDB, une version de pgadmin3 est install\u00e9e.<\/p>\n<p>Dans le cas d&#8217;une installation de type debian \/ Ubuntu ou d&#8217;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\u00a0 soit en connexion sur le serveur soit de son poste.<\/p>\n<h3>Utiliser PGadmin connect\u00e9 sur le serveur<\/h3>\n<p>Dans notre situation, il n&#8217;y pas d&#8217;acc\u00e9s \u00e0 l&#8217;interface graphique du serveur, nous utiliserons une connexion ssh avec la redirection X11.<\/p>\n<p>ssh -X pib@ubuntu<\/p>\n<p>apr\u00e8s avoir positionn\u00e9 sont environnement on lance la commande<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">postgres@ubuntu:~$ prod913\npostgres@ubuntu:~$ env | grep PG\nPGPORT=5434\nPGUSER=postgres\nPGDATABASE=postgres\nPGLOCALEDIR=\/u00\/app\/postgresql\/product\/9.1.3\/share\/locale\nPGDATA=\/u01\/postgresql\/data\/prod913\npostgres@ubuntu:~$ \/u00\/app\/postgresql\/product\/9.1.3\/pgAdmin3\/bin\/pgadmin3<\/pre>\n<p><samp><img loading=\"lazy\" decoding=\"async\" style=\"display: block; margin-left: auto; margin-right: auto;\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-pgadmin-0.png\" alt=\"Screenshot-pgadmin-0\" width=\"552\" height=\"368\" \/><\/samp><samp><\/samp><\/p>\n<p><samp><\/samp>On notera que dans le cas de pgadmin et du fait de notre configuration, il y a authentification. Une fois connect\u00e9 \u00e0 un cluster de base on peut travailler sur les objets tablespace, role , et tous les objets d&#8217;une base.<\/p>\n<p><samp><\/samp><samp><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot_pgadmin-1.png\" alt=\"Screenshot_pgadmin-1\" width=\"549\" height=\"362\" \/><\/samp><br \/>\nIl comporte un editeur de query et un explain plan graphique dont voici 2 screenshots :<\/p>\n<p><samp><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-Query_-_cave_on_postgreslocalhost5434_.png\" alt=\"Screenshot-Query_-_cave_on_postgreslocalhost5434_\" width=\"550\" height=\"419\" \/><\/samp><samp><\/samp><samp><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-Query_-_cave_on_postgreslocalhost5434_-1.png\" alt=\"Screenshot-Query_-_cave_on_postgreslocalhost5434_-1\" width=\"551\" height=\"353\" \/><\/samp><samp><\/samp><\/p>\n<h3>Utiliser PgAdmin \u00e0 partir de son poste.<\/h3>\n<p>Lorsque l&#8217;on utilise PgAdmin \u00e0 partir de son poste le mieux est d&#8217;utiliser une redirection de port entre le poste locale et le serveur distant.<\/p>\n<p><samp>ssh -fNg -L 5440:localhost:5434 pib@192.168.165.130<\/samp><samp><\/samp><samp><\/samp><\/p>\n<p>Puis de lancer localement pgAdmin3 .<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot_pgadmin_3.png\" alt=\"Screenshot_pgadmin_3\" width=\"551\" height=\"380\" \/><\/p>\n<p>Un avertissement nous montre qu&#8217;il est important d&#8217;avoir une version de pgadmin en ad\u00e9quation avec le niveau du serveur PostgreSQL. soit la version 1.14 pour la version 9.1.3 du serveur.<\/p>\n<p>Si l&#8217;approche graphique est s\u00e9duisante, il ne faut pas oublier que suivant les conditions il n&#8217;est pas possible d&#8217;acc\u00e9der \u00e0 une interface graphique, d&#8217;ou la pr\u00e9sentation de psql pr\u00e9c\u00e8dement. Une autre interface graphique sera abord\u00e9e dans un prochain article il s&#8217;agit de phpPgAdmin. Nous verrons que les objectifs de phpPgAdmin sont les m\u00eames que ceux de PgAdmin.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-phpPgAdmin_-_Google_Chrome.png\" alt=\"Screenshot-phpPgAdmin_-_Google_Chrome\" width=\"549\" height=\"447\" \/><\/p>\n<h3>R\u00e9f\u00e9rences documentaires<\/h3>\n<p><a href=\"http:\/\/docs.postgresqlfr.org\/9.1\/app-psql.html\">http:\/\/docs.postgresqlfr.org\/9.1\/app-psql.html<\/a><br \/>\n<a href=\"http:\/\/docs.postgresqlfr.org\/9.1\/libpq-envars.html\">http:\/\/docs.postgresqlfr.org\/9.1\/libpq-envars.html<\/a><a href=\"http:\/\/www.pgadmin.org\/\">http:\/\/www.pgadmin.org\/<\/a><br \/>\n<a href=\"http:\/\/www.pgadmin.org\/docs\/1.14\/index.html\">http:\/\/www.pgadmin.org\/docs\/1.14\/index.html<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Nous avons dans les articles pr\u00e9c\u00e9dents vu comment installer PostgreSQL et quels \u00e9taient les composants d&#8217;une base. Nous abordons maintenant le point de l&#8217;action<br \/>sur le moteur avec deux outils orient\u00e9s administration de la base, g\u00e9n\u00e9ralement utilis\u00e9s par les DBAs. Il s&#8217;agit de psql et de PgAdmin. Nous aborderons aussi dans cet article la configuration de connexion au travers des fichiers de configuration du serveur.<\/p>\n","protected":false},"author":28,"featured_media":2575,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198],"tags":[203,23,77],"type_dbi":[],"class_list":["post-2574","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-management","tag-command-lines","tag-dba","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>PostgreSQL: Un aper\u00e7u des outils d&#039;administration - dbi Blog<\/title>\n<meta name=\"description\" content=\"Nous avons dans les articles pr\u00e9c\u00e9dents vu comment installer PostgreSQL et quels \u00e9taient les composants d&#039;une base. Nous abordons maintenant le point de l&#039;action sur le moteur avec deux outils orient\u00e9s administration de la base, g\u00e9n\u00e9ralement utilis\u00e9s par les DBAs.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL: Un aper\u00e7u des outils d&#039;administration\" \/>\n<meta property=\"og:description\" content=\"Nous avons dans les articles pr\u00e9c\u00e9dents vu comment installer PostgreSQL et quels \u00e9taient les composants d&#039;une base. Nous abordons maintenant le point de l&#039;action sur le moteur avec deux outils orient\u00e9s administration de la base, g\u00e9n\u00e9ralement utilis\u00e9s par les DBAs.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2012-07-30T01:20:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-pgadmin-0.png\" \/>\n\t<meta property=\"og:image:width\" content=\"958\" \/>\n\t<meta property=\"og:image:height\" content=\"637\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b\"},\"headline\":\"PostgreSQL: Un aper\u00e7u des outils d&#8217;administration\",\"datePublished\":\"2012-07-30T01:20:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/\"},\"wordCount\":1200,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-pgadmin-0.png\",\"keywords\":[\"Command lines\",\"DBA\",\"PostgreSQL\"],\"articleSection\":[\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/\",\"name\":\"PostgreSQL: Un aper\u00e7u des outils d'administration - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-pgadmin-0.png\",\"datePublished\":\"2012-07-30T01:20:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b\"},\"description\":\"Nous avons dans les articles pr\u00e9c\u00e9dents vu comment installer PostgreSQL et quels \u00e9taient les composants d'une base. Nous abordons maintenant le point de l'action sur le moteur avec deux outils orient\u00e9s administration de la base, g\u00e9n\u00e9ralement utilis\u00e9s par les DBAs.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-pgadmin-0.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-pgadmin-0.png\",\"width\":958,\"height\":637},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL: Un aper\u00e7u des outils d&#8217;administration\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL: Un aper\u00e7u des outils d'administration - dbi Blog","description":"Nous avons dans les articles pr\u00e9c\u00e9dents vu comment installer PostgreSQL et quels \u00e9taient les composants d'une base. Nous abordons maintenant le point de l'action sur le moteur avec deux outils orient\u00e9s administration de la base, g\u00e9n\u00e9ralement utilis\u00e9s par les DBAs.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL: Un aper\u00e7u des outils d'administration","og_description":"Nous avons dans les articles pr\u00e9c\u00e9dents vu comment installer PostgreSQL et quels \u00e9taient les composants d'une base. Nous abordons maintenant le point de l'action sur le moteur avec deux outils orient\u00e9s administration de la base, g\u00e9n\u00e9ralement utilis\u00e9s par les DBAs.","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/","og_site_name":"dbi Blog","article_published_time":"2012-07-30T01:20:00+00:00","og_image":[{"width":958,"height":637,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-pgadmin-0.png","type":"image\/png"}],"author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b"},"headline":"PostgreSQL: Un aper\u00e7u des outils d&#8217;administration","datePublished":"2012-07-30T01:20:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/"},"wordCount":1200,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-pgadmin-0.png","keywords":["Command lines","DBA","PostgreSQL"],"articleSection":["Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/","name":"PostgreSQL: Un aper\u00e7u des outils d'administration - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-pgadmin-0.png","datePublished":"2012-07-30T01:20:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b"},"description":"Nous avons dans les articles pr\u00e9c\u00e9dents vu comment installer PostgreSQL et quels \u00e9taient les composants d'une base. Nous abordons maintenant le point de l'action sur le moteur avec deux outils orient\u00e9s administration de la base, g\u00e9n\u00e9ralement utilis\u00e9s par les DBAs.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-pgadmin-0.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-pgadmin-0.png","width":958,"height":637},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-outils-dadministration\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL: Un aper\u00e7u des outils d&#8217;administration"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2574","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=2574"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2574\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/2575"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=2574"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=2574"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=2574"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=2574"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}