PostgreSQL/Version imprimable

Un livre de Wikilivres.
Sauter à la navigation Sauter à la recherche
Nuvola-inspired File Icons for MediaWiki-fileicon-ps.png

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]

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.

Attention !

Logo 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

Configuration par l'interface[modifier | modifier le wikicode]

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

pgAdmin
PhpPgAdmin


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.

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


Utilisation

Commandes de base[modifier | modifier le wikicode]

Une liste des commandes est disponible avec :

\?

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

\l

Se connecter à une base :

\c 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

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

GUI pgAdmin[modifier | modifier le wikicode]

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)[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

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

GUI pgAdmin[modifier | modifier le wikicode]

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
);

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.

Sauvegardes[modifier | modifier le wikicode]

Windows[modifier | modifier le wikicode]

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

Logo square postgis.png

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

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




Messages d'erreur

Logs[modifier | modifier le wikicode]

Sous Windows, des logs sont disponibles dans C:\Users\MonUtilisateur\Documents\pgadmin.log.

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]


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