Ceci est la version imprimable de PostgreSQL.

Si vous imprimez cette page, choisissez « Aperçu avant impression » dans votre navigateur, ou cliquez sur le lien Version imprimable dans la boîte à outils, vous verrez cette page sans ce message, ni éléments de navigation sur la gauche ou en haut.
Cliquez sur Rafraîchir cette page pour obtenir la dernière version du wikilivre.
Pour plus d'informations sur les version imprimables, y compris la manière d'obtenir une version PDF, vous pouvez lire l'article Versions imprimables.

PostgreSQL

Une version à jour et éditable de ce livre est disponible sur Wikilivres,
une bibliothèque de livres pédagogiques, à l'URL :
https://fr.wikibooks.org/wiki/PostgreSQL

Vous avez la permission de copier, distribuer et/ou modifier ce document selon les termes de la Licence de documentation libre GNU, version 1.2 ou plus récente publiée par la Free Software Foundation ; sans sections inaltérables, sans texte de première page de couverture et sans Texte de dernière page de couverture. Une copie de cette licence est incluse dans l'annexe nommée « Licence de documentation libre GNU ».

Installation

Mise en place du logiciel[modifier | modifier le wikicode]

Machine hôte[modifier | modifier le wikicode]

PostgreSQL est un logiciel libre multiplateforme, il se télécharge sur http://www.postgresql.org/download/.

Sur Ubuntu c'est le paquet :

sudo apt-get install postgresql

Une fois installé, il écoute le port 5432.

Pour administrer les bases, il est fournit avec le client lourd pgAdmin, mais il existe aussi une interface web appelée PhpPgAdmin.

Le site officiel fournit une longue documentation en français : http://docs.postgresqlfr.org/. C'est pourquoi ce livre se veut le plus pratique possible.

PostgreSQL se lance ensuite automatiquement à chaque démarrage de la machine, ce qui la ralentit significativement.

Pour éviter cela :

Dans Linux, on peut déplacer /etc/init.d/postgresql.
Dans Windows, exécuter services.msc, puis passer le service postgresql en démarrage manuel. Ensuite pour lancer le service à souhait (en tant qu'administrateur), créer un script PostgreSQL.cmd contenant le nom du service, par exemple :

net start postgresql-x64-9.5
pause
net stop postgresql-x64-9.5

Docker[modifier | modifier le wikicode]

Dans Docker compose :

   postgres:
       image: 'postgres'
       environment:
           TZ: Europe/Paris
           POSTGRES_USER: 'root'
           POSTGRES_PASSWORD: "${DB_ROOT_PASSWORD}"
           POSTGRES_DB: 'root'
       ports:
           - "5432:5432"

   pgadmin:
       image: dpage/pgadmin4
       depends_on:
           - postgres
       environment:
           PGADMIN_DEFAULT_EMAIL: "${ADMIN_EMAIL}"
           PGADMIN_DEFAULT_PASSWORD: "${ADMIN_PASSWORD}"
       ports:
           - "8889:80"

Utilisation en shell via psql[modifier | modifier le wikicode]

Exemple pour choisir l'utilisateur à connecter en tapant son mot de passe^[1] :

psql -U superadmin -W

Options les plus courantes :

--username (-U)
--host (-h) : si la base est sur un autre serveur, il faut le préciser avec.
--port (-p)
--dbname (-d)

Se connecter sans prompt[modifier | modifier le wikicode]

Voici un exemple sans interaction de demande de mot de passe :

psql postgresql://my_user:my_password@my_postgres_server/my_database

Sinon, les accès peuvent aussi être stockés dans un fichier ~/.pgpass^[2] :

echo "my_postgres_server:5432:my_database:my_user:my_password" >> ~/.pgpass && chmod 0600 ~/.pgpass

Utilisation par l'interface pgAdmin[modifier | modifier le wikicode]

Lancer le raccourci pgAdmin III fournit une interface complète de manipulation du SGBD :

Références[modifier | modifier le wikicode]

Sécurité

Comptes système[modifier | modifier le wikicode]

Sur Windows, il faut que l'utilisateur se soit connecté au moins une fois avec PgAdmin pour que le mot de passe de la base soit enregistré (en clair) dans son répertoire personnel : C:\Users\MonUtilisateur\AppData\Roaming\postgresql\pgpass.conf.
Sur Linux, c'est stocké dans ~/.pgpass par défaut.

Rôles[modifier | modifier le wikicode]

PostgreSQL reconnait le concept des rôles (roles)^[1] pour assurer la sécurité de l'authentification et de l'identification, indépendamment des comptes du système d'exploitation.

Ce concept dépasse celui des utilisateurs et groupes : un rôle peut être pensé soit comme un utilisateur de base de données, soit comme un groupe de ceux-ci. Les rôles possèdent certain privilèges sur les objets de la base comme les tables ou les fonctions, et peuvent les transmettre à d'autres rôles. Les rôles sont globaux au sein d'un cluster, et pas seulement valables pour une seule base.

Souvent les utilisateurs devant avoir des privilèges identiques sont rassemblés dans un groupe qui reçoit les permissions.

 -- Groupe
 CREATE ROLE group_1 ENCRYPTED PASSWORD 'xyz';
 GRANT SELECT ON table_1 TO group_1;
 -- Utilisateurs
 CREATE ROLE adam LOGIN ENCRYPTED PASSWORD 'xyz';  -- NOLOGIN par défaut
 CREATE ROLE anne LOGIN ENCRYPTED PASSWORD 'xyz';
 -- Lien entre les deux
 GRANT group_1 TO adam, anne;

La commande CREATE ROLE peut assigner les privilèges SUPERUSER, CREATEDB, CREATEROLE, REPLICATION et LOGIN. GRANT confère les permissions d'accès aux tables ou l'appartenance à un groupe.

Implicitement le rôle spécial PUBLIC peut être vu comme un groupe qui inclut tous les rôles. Par conséquent les privilèges assignés à PUBLIC sont implicitement donnés à tous les rôles, même ceux créés plus tard.

Utilisateurs[modifier | modifier le wikicode]

 select * from postgres.pg_catalog.pg_user

Références[modifier | modifier le wikicode]

↑ Concept of roles

psql

psql est un programme fourni avec PostgreSQL, pouvant interpréter le SQL ainsi que des métacommandes qui lui sont propres^[1].

Les commandes suivantes ne marcheront pas dans des procédures stockées ou dans PgAdmin.

Commandes de base[modifier | modifier le wikicode]

Une liste des commandes est psql disponible avec :

\?

Par exemple, pour obtenir la liste des bases de données du serveur :

\l

Se connecter à une base :

\c MaBase

ou :

\connect MaBase

Obtenir la liste des schémas :

\dn

Lister toutes les tables de tous les schémas :

\dt *.*

Restreindre à toutes les tables d'un schéma :

\dt MaBase.*

Voir les champs d'une table :

\d MaTable

Assigne une variable :

\set ma_variable 'test';

L'afficher :

\echo :ma_variable

Importer un dump :

\i /dossiers/fichier.sql

Références[modifier | modifier le wikicode]

↑ https://docs.postgresql.fr/10/app-psql.html

Utilisation

Création de base[modifier | modifier le wikicode]

GUI pgAdmin[modifier | modifier le wikicode]

Dans pgAdmin, les commandes SQL sont disponibles dans l'icône de puzzle "PSQL console".

Par ailleurs, un clic droit sur le menu "Bases de données" à gauche, permet d'accéder à l'option "Ajouter une base de données". Cette dernière affiche dans un onglet "SQL" son équivalent en code :

CREATE DATABASE "WikibooksDB"
  WITH ENCODING='UTF8'
       CONNECTION LIMIT=-1;

Une fois la base sélectionnée, les boutons suivants se dégrisent :

Affiche/éditer les propriétés de l'objet sélectionné.
Supprimer l'objet sélectionné.
SQL (un éditeur de requête).
Maintenir la base ou la table courante.

Ligne de commande (Shell)[modifier | modifier le wikicode]

Via le terminal, 2 choix apparaissent, soit écrire directement dans le shell:

createdb nom_database

Soit, il faudra d'abord se mettre sous le user postgres. Cela se fait tout simplement de la manière suivante:

su postgres

Puis, lancer PostgreSQL:

psql

Et ensuite créer une base de données en écrivant:

CREATE DATABASE nom_database;
-- Ou, pour ajouter la base de données pour un utilisateur spécifique:
CREATE DATABASE nom_database OWNER nom_utilisateur;

Pour s'y connecter, entrer simplement dans le terminal:

psql nom_database
# Ou, pour s'y connecter avec un utilisateur spécifique:
psql -d nom_database -U nom_utilisateur

Pour changer la base courante :

 SELECT current_database(); # postgres
 \connect root;
 SELECT current_database(); # root

Création de schéma[modifier | modifier le wikicode]

Un schéma est un élément d'une base similaire à un espace de nom^[1] : il peut contenir plusieurs tables et fonctions.

CREATE SCHEMA mon_schema

Création de table[modifier | modifier le wikicode]

GUI pgAdmin[modifier | modifier le wikicode]

En dépliant le schéma public à gauche, un clic droit sur le menu "Table", "Ajouter table", il devient possible de remplir les différents onglets nécessaires. Cela équivaut en SQL à :

CREATE TABLE Livres (
    id        integer CONSTRAINT firstkey PRIMARY KEY,
    titre     varchar(255) NOT NULL
);

On peut ensuite la lire avec un clic droit dessus, View/Edit Data.

Ligne de commande (Shell)[modifier | modifier le wikicode]

Après s’être connecté à une base de données, il suffira d'écrire :

CREATE TABLE Livres (
    id        integer CONSTRAINT firstkey PRIMARY KEY,
    titre     varchar(255) NOT NULL
);

L’indentation en ligne de commande est purement optionnelle, les instructions entrées ne seront que traitées après le ; final.

L'auto-incrémentation est assurée par :

SERIAL PRIMARY KEY

Insertion[modifier | modifier le wikicode]

On suppose une table client dans le schéma operation.

INSERT INTO "operation"."client" (id, nom, prenom, code_postal, ville, pays) 
VALUES ('01234', 'Brochet', 'Pierre', 75001, 'Paris', 'France');

Insertion et retourne l'enregistrement inséré :

INSERT INTO "operation"."client" (id, nom, prenom, code_postal, ville, pays) 
VALUES ('01235', 'Laplace', 'Jean', 75006, 'Brest', 'France')
RETURNING *;

Modification[modifier | modifier le wikicode]

 SELECT * FROM "auth"."account" WHERE "account"."accountId" = 'SystemDepositor';

 UPDATE "auth"."account"
 SET "password" = 'xxx'
 WHERE "account"."accountId" = 'SystemDepositor';

Création de variables[modifier | modifier le wikicode]

Utiliser "with"^[2].

Sauvegardes[modifier | modifier le wikicode]

Windows[modifier | modifier le wikicode]

Pour définir un batch de backup quotidien, utiliser pg_dump.exe^[3] en DOS. Par exemple, la commande suivante envoie un dump au format "aaaa-mm-jj-NomDeLaBase.sql" sur un serveur du réseau :

"C:\Program Files\PostgreSQL\bin\pg_dump.exe" -U Utilisateur1 -f "\\192.168.1.2\d$\Backup\%DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2%-MaBase1.sql" MaBase1

Restauration d'un fichier plat :

"C:\Program Files\PostgreSQL\bin\psql.exe" -U Utilisateur1 MaBase1 < "\\192.168.1.2\d$\Backup\2016-07-15-MaBase1.sql"

Restauration d'un fichier compressé :

"C:\Program Files\PostgreSQL\bin\pg_restore.exe" -U Utilisateur1 "\\192.168.1.2\d$\Backup\2016-07-15-MaBase1.sql"

Linux[modifier | modifier le wikicode]

Sauvegarde :

$ pg_dump --dbname=finance --username=boss --file=finance.sql

Restauration :

$ psql --dbname=finance_x --username=boss <finance.sql

Références[modifier | modifier le wikicode]

PostGIS

Description[modifier | modifier le wikicode]

Lors du typage des champs, certains représentent des objets graphiques, et sont donc considérés comme étant de catégorie "Spatial" (base de données spatiales). Par conséquent, ils se manipulent par des requêtes différentes que pour le texte.

PostGIS est un plugin permettant la manipulation d'informations géographiques sous forme de géométries (points, lignes, polygones).

Le mode d'indexation choisi pour ces objets est l'arbre R^[1].

Installation[modifier | modifier le wikicode]

Il est multiplateforme et se télécharge sur http://postgis.net/install/.

Une fois son contenu copié dans C:/Program Files/PostgreSQL/9.4/share/extension/ (en fusionnant les répertoires /bin/ et autres), lancer :

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;

Fonctions[modifier | modifier le wikicode]

Un certain nombre de fonctions sont disponibles^[2].

Manipulation[modifier | modifier le wikicode]

^[3].

Cette section est vide, pas assez détaillée ou incomplète. Votre aide est la bienvenue !

Références[modifier | modifier le wikicode]

Problèmes connus

Logs[modifier | modifier le wikicode]

Sous Windows, des logs sont disponibles dans ~\Documents\pgadmin.log.
Sous Linux, dans /var/log/postgresql/.

Messages d'erreur[modifier | modifier le wikicode]

"HINT: Perhaps you meant to reference the column" ou "ERROR: column "valeur_champ" does not exist"[modifier | modifier le wikicode]

Ajouter des guillemets autour du nom du champ et des apostrophes autour des valeurs. Ex :

 select * from "auth"."account" where "account"."accountId" = 'SystemDepositor';

pg_restore: [archiver] input file does not appear to be a valid archive[modifier | modifier le wikicode]

Se produit quand on tente de restaurer un fichier plat avec pg_restore. Il faut donc soit utiliser pgsql, soit convertir le fichier^[1]. Exemple sous Linux :

cat Backup_MaBase1.sql | psql MaBase1

Sous Windows il suffit d'exécuter le fichier avec pgAdmin.

Références[modifier | modifier le wikicode]

↑ http://serverfault.com/questions/260607/pg-dump-and-pg-restore-input-file-does-not-appear-to-be-a-valid-archive

GFDL

Vous avez la permission de copier, distribuer et/ou modifier ce document selon les termes de la licence de documentation libre GNU, version 1.2 ou plus récente publiée par la Free Software Foundation ; sans sections inaltérables, sans texte de première page de couverture et sans texte de dernière page de couverture.

[1] ttps://docs.postgresql.fr/10/app-psql.html

[2] ttps://www.postgresql.org/docs/9.1/libpq-pgpass.html

[3] Concept of roles

[4] ttps://docs.postgresql.fr/10/app-psql.html

[5] ttps://docs.postgresql.fr/10/sql-createschema.html

[6] ttps://stackoverflow.com/questions/36959/how-do-you-use-script-variables-in-psql

[7] ttp://www.postgresql.org/docs/8.3/static/app-pgdump.html

[8] ttp://www.postgis.fr/chrome/site/docs/workshop-foss4g/doc/indexing.html

[9] ttp://postgis.net/docs/PostGIS_Special_Functions_Index.html

[10] (en) « Chapter 4. Using PostGIS: Data Management and Queries »

[11] ttp://serverfault.com/questions/260607/pg-dump-and-pg-restore-input-file-does-not-appear-to-be-a-valid-archive

[1]

[2]

[1]

[1]

[1]

[2]

[3]

[1]

[2]

[3]

[1]