# PostgreSQL/Version imprimable

* 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.
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

# 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 :
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 :

### Docker

Dans Docker compose :

## Utilisation en shell via psql

Exemple pour choisir l'utilisateur à connecter en tapant son mot de passe :
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 :
Sinon, les accès peuvent aussi être stockés dans un fichier ~/.pgpass :

## Utilisation par l'interface pgAdmin

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

## Références

* ↑ https://docs.postgresql.fr/10/app-psql.html
* ↑ https://www.postgresql.org/docs/9.1/libpq-pgpass.html

# 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.
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.

## Références

* ↑ 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.
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 :
Se connecter à une base :
ou :
Obtenir la liste des schémas :
Lister toutes les tables de tous les schémas :
Restreindre à toutes les tables d'un schéma :
Voir les champs d'une table :
Assigne une variable :
L'afficher :
Importer un dump :

## Références

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

# Utilisation
## Création de base
### GUI 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 :
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)

Via le terminal, 2 choix apparaissent, soit écrire directement dans le shell:
Soit, il faudra d'abord se mettre sous le user postgres. Cela se fait tout simplement de la manière suivante:
Puis, lancer PostgreSQL:
Et ensuite créer une base de données en écrivant:
Pour s'y connecter, entrer simplement dans le terminal:
Pour changer la base courante :

## 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.

## Création de table
### GUI 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 à :
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 :
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 :

## Insertion

On suppose une table client dans le schéma operation.
Insertion et retourne l'enregistrement inséré :

## 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 :
Restauration d'un fichier plat :
Restauration d'un fichier compressé :

### Linux

Sauvegarde :
Restauration :

## Références

* ↑ https://docs.postgresql.fr/10/sql-createschema.html
* ↑ https://stackoverflow.com/questions/36959/how-do-you-use-script-variables-in-psql
* ↑ http://www.postgresql.org/docs/8.3/static/app-pgdump.html

# 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 :

## Fonctions

Un certain nombre de fonctions sont disponibles.

## Références

* ↑ http://www.postgis.fr/chrome/site/docs/workshop-foss4g/doc/indexing.html
* ↑ http://postgis.net/docs/PostGIS_Special_Functions_Index.html
* ↑ (en) « Chapter 4. Using PostGIS: Data Management and Queries »

# 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 :

### 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 :
Sous Windows il suffit d'exécuter le fichier avec pgAdmin.

## Références

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