PostgreSQL/Version imprimable

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

Machine hôte

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

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

Exemple pour choisir l'utilisateur à connecter en tapant son mot de passe :

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

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 :

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

Utilisation par l'interface pgAdmin

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

pgAdmin

PhpPgAdmin

Importer un dump

En shell :

psql -h hostname -d databasename -U username -f mon_dump.sql

Dans la console SQL :

\i C:/database/mon_dump.sql

Sécurité

Comptes système

• 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

PostgreSQL reconnait le concept des rôles (roles) 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

 select * from postgres.pg_catalog.pg_user

psql

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

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

Commandes de base

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

Utilisation

Création de base

GUI pgAdmin

Créer une base de données dans PgAdmin

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 :

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

Ligne de commande (Shell)

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

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

CREATE SCHEMA mon_schema

Création de table

GUI pgAdmin

Créer une table dans PgAdmin

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)

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

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

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

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

Création de variables

Utiliser "with".

Sauvegardes

Windows

Pour définir un batch de backup quotidien, utiliser pg_dump.exe 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

Sauvegarde :

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

Restauration :

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

PostGIS

Description

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.

Installation

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

Un certain nombre de fonctions sont disponibles.

Manipulation

.

Cette section est vide, pas assez détaillée ou incomplète.

Problèmes connus

Logs

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

Messages d'erreur

"HINT: Perhaps you meant to reference the column" ou "ERROR: column "valeur_champ" does not exist"

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

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

cat Backup_MaBase1.sql | psql MaBase1

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

Références

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.