Postgres psql est un client en ligne de commande du serveur PostgreSQL. Il permet de se connecter au serveur de bases de données en local sur la machine serveur ou à distance depuis une autre machine. Une fois connecté, l’utilisateur autorisé peut exécuter des commandes spécifiques à psql qui retournent différentes informations utiles ou exécuter des requêtes SQL. A ce titre il constitue un outil léger et performant pouvant rendre de nombreux services au cours d’un développement qui utilise ce serveur de bases de données. Dans ce tutoriel le login de l’utilisateur authentifié sur la machine est supposé être jdupond. Pour exécuter les commandes des exemples, remplacer toujours ce login par le votre. Cet article fait partie d’une série qui concerne le serveur de bases de données PostgreSQL et son utilisation dans un environnement de développement:

1. Installation de base de PostgreSQL
2. Utiliser Postgres pgAdmin 3 pour administrer PostgreSQL
3. Installer PostgreSQL 10
4. Reconfigurer l’installation par défaut de PostgreSQL 10
5. Utiliser PostgreSQL 10 dans un environnement de développement
6. Cet article: Administrer PostgreSQL avec psql
7. Postgres pgAdmin 4 : installation et configuration
8. Postgres pgAdmin 4: Utilisation courante

1. Connexion au serveur de BDD avec Postgres psql

psql -h <AdresseServeur> -p <Port> -U <UtilisateurBDD> -d <BaseDeDonnees>

psql

psql -d postgres

psql -h localhost -U utappli -d bdappli

2. Postgres psql peut se souvenir de la BDD par défaut et des mots de passe

• Modifier la BDD à utiliser par défaut.

vi /home/jdupond/.postgresqlrc

10  pgtest  bdappli

psql

• Se souvenir du mot de passe de l’utilisateur de connexion

localhost:5432:*:utappli:mputappli

chmod 600 /home/jdupond/.pgpass

psql -h localhost -U utappli -d bdappli

3. Utiliser les commandes de Postgres psql

\?

\h select

\l

\dn

\d commerce.*

\d commerce.produits

4. Echanger les données avec des fichiers grâce à Postgres psql

CU;clé usb 500 Go
EC;ecran 26 pouces
SR;souris 3 boutons
CA;clavier azerty

copy commerce.produits(code,nom) from '/home/jdupond/produits.csv' (format csv, delimiter ';', header, encoding 'utf8');
select * from commerce.produits;

copy commerce.produits to '/home/jdupond/extraction_produits.csv' (format csv, delimiter ';', header, encoding 'utf8');

copy (select * from commerce.produits where id=1 or id=2) to '/home/ameddeb/dvlp/travaux/pgdoc/extractionl_produits.csv' (format csv, delimiter ';', header, encoding 'utf8');

5. Exécuter une requête SQL depuis le shell système

psql -c "select * from commerce.produit;"

6. Exécuter un script SQL depuis un fichier

create table commerce.prix(id serial, produitid int, prixachat numeric(7,2), prixvente numeric(7,2));
insert into commerce.prix(produitid, prixachat, prixvente) values(1, 6.2, 7.35);
insert into commerce.prix(produitid, prixachat, prixvente) values(2, 230.5, 274.75);

psql -f /home/jdupond/cree_prix.sql
psql -c "select * from commerce.prix;"

 Créer un utilisateur de base de données standard

psql -d postgres

create user utappli with login encrypted password 'mputappli';

select * from pg_roles where rolname='utappli';

\q

psql -h localhost -d postgres -U utappli

Créer une base de données

psql -d postgres
create database bdappli owner utappli;
\q

Créer les tables de données

psql -h localhost -U utappli -d bdappli
create schema commerce;
create table commerce.produits (id serial, code varchar(12), nom varchar(100));
select * from commerce.produits;

Se connecter au serveur à partir d’une autre machine

vi /etc/postgresql/10/pgtest/pg_hba.conf

host    all     all    0.0.0.0/0     md5

vi /etc/postgresql/10/pgtest/postresql.conf

#listen_addresses = 'localhost'

listen_addresses = '*'

pg_ctlcluster 10 pgtest restart

psql -h xxx.xxx.xxx.xxx -U utappli -d bdappli

sudo ifconfig

sudo ifconfig | sed "s/^[ ]*//g" | egrep ^inet | egrep -v "(^inet6|127.0.0.1)" | awk '{ print $2 }'

Contrôler les requêtes SQL exécutées par l’application

#log_statement = 'none'

log_statement = 'all'

pg_ctlcluster 10 pgtest reload

psql -d postgres
select * from pg_roles;
\q

cat ~/pgtest.log

cluster de bases de données installé par défaut. L’objectif est d’obtenir une installation qui convient pour une utilisation dans un environnement de développement sur une machine de bureau. Cet article fait partie d’une série qui concerne le serveur de bases de données PostgreSQL et son utilisation dans un environnement de développement:

1. Installation de base de PostgreSQL
2. Utiliser Postgres pgAdmin 3 pour administrer PostgreSQL
3. Installer PostgreSQL 10
4. Cet article: Reconfigurer l’installation par défaut de PostgreSQL 10
5. Utiliser PostgreSQL 10 dans un environnement de développement
6. Administrer PostgreSQL avec psql
7. Postgres pgAdmin 4 : installation et configuration
8. Postgres pgAdmin 4: Utilisation courante

L’installation par défaut ne convient pas pour un environnement de développement

Supprimer le cluster PostgreSQL installé par défaut

sudo pg_dropcluster --stop 10 main

Créer un cluster PostgreSQL personnalisé

sudo pg_createcluster -u jdupond -g jdupond --locale=fr_FR.UTF-8 -l /home/jdupond/pgtest.log -d /home/jdupond/pgtest 10 pgtest

• Le propriétaire (fichiers, droits et utilisateur BDD d’administration) est jdupond
• La locale qui détermine le format des données et le mode de recherche est le français de France avec encodage unicode UTF-8.
• Le fichier de log qui est très utile pour le développement parcequ’il permet de visualiser les requêtes SQL exécutées par l’application est /home/jdupond/pgtest.log
• Les données sont dans /home/jdupond/pgtest/ ce qui permet leur manipulation directe par l’utilisateur-développeur.

Contrôle du serveur de base de données PostgreSQL

pg_ctlcluster 10 pgtest start # démarrage
pg_ctlcluster 10 pgtest stop  # arrêt
pg_ctlcluster 10 pgtest status # affichage du statut marche/arrêt
pg_ctlcluster 10 pgtest reload # recharger la configuration
pg_ctlcluster 10 pgtest restart # redémarrer le serveur

pg_dropcluster --stop 10 pgtest

Gérer les données avec l’utilitaire psql

psql -d <NomDeBaseDeDonnees>

psql -d postgres

select rolname, rolsuper from pg_roles;

Spécificité de l’installation de PostgreSQL 10

sudo apt-get update
apt-cache search postgresql | egrep "^postgresql-[0-9]+(.[0-9])? "

postgresql-9.6 - object-relational SQL database, version 9.6 server

Versions disponibles pour le système installé

sudo vi /etc/apt/sources.list.d/postgresql-sources.list

deb http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main

deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
apt-cache search postgresql | egrep "^postgresql-[0-9]+(.[0-9])? "

postgresql-9.6 - object-relational SQL database, version 9.6 server
postgresql-10 - object-relational SQL database, version 10 server
postgresql-9.2 - object-relational SQL database, version 9.2 server
postgresql-9.3 - object-relational SQL database, version 9.3 server
postgresql-9.4 - object-relational SQL database, version 9.4 server
postgresql-9.5 - object-relational SQL database, version 9.5 server

Installation

sudo apt-get install postgresql-10

• /var/lib/postgresql/10/main/: les fichiers des données.
• /var/log/postgresql/postgresql-10-main.log: le fichier de log du cluster.
• /etc/postgresql/10/main/: les fichiers de configuration.
• /usr/lib/postgresql/10/: fichiers binaires du serveur, exécutables et librairies.

locale

Désinstallation

sudo pg_ctlcluster 10 main stop
sudo apt-get remove postgresql-10 postgresql-client-10 postgresql-client-common postgresql-common
sudo apt-get purge postgresql-10 postgresql-client-10 postgresql-client-common postgresql-common

Objectif :  Installer le serveur de base de données relationnelles PostgreSQL afin de l’utiliser dans un environnement de développement. Cible: Système GNU Linux Debian ou compatible, notamment Ubuntu/KUbuntu pour les machines de bureau. Cet article fait partie d’une série qui concerne le serveur de bases de données PostgreSQL et son utilisation dans un environnement de développement:

1. Cet article: Installation de base de PostgreSQL
2. Utiliser Postgres pgAdmin 3 pour administrer PostgreSQL
3. Installer PostgreSQL 10
4. Reconfigurer l’installation par défaut de PostgreSQL 10
5. Utiliser PostgreSQL 10 dans un environnement de développement
6. Administrer PostgreSQL avec psql
7. Postgres pgAdmin 4 : installation et configuration
8. Postgres pgAdmin 4: Utilisation courante

apt-cache search postgresql | grep "^postgresql-[0-9].[0-9] "

sudo apt-get install postgresql-9.3

sudo service postgresql

Usage: /etc/init.d/postgresql {start|stop|restart|reload|force-reload|status}

sudo -u postgres psql

CREATE USER admin WITH PASSWORD 'admin' CREATEDB CREATEUSER;

SELECT * FROM pg_catalog.pg_user;

\q