Introduction à PostgreSQL

hadrien45
Proposition de tutoriels

Bonjour,

J'utilise depuis quelques temps le SGBD PostgreSQL à titre perso.
Voici un modeste tutoriel pour démarrer avec PostgreSQL. N'hésitez pas à me faire des retours.

PostgreSQL Introduction

PostgreSQL est un système open source de gestion de base de données relationnelle et objet (SGBDRO) fonctionnant en mode Client-Serveur.
Ecrit en C et developpé depuis 1995, PostgreSQL est un logiciel mutliplateforme dont le développement et la maintenance sont assurés par une communauté mondiale de développeurs et d'entreprises. La dernière version disponible est la 9.6.1.
Pour plus d'informations, rendez-vous sur le site officiel.
PostrgeSQL est également très bien documenté, les versions françaises se trouvent ici.

Les avantages de PostgreSQL

Comparé à d'autres SGBD, PostgreSQL supporte la plupart des fonctionnalités majeures de la norme SQL:2011. Pour plus d'informations sur la compatibilié SQL des différents SGBD, je vous invite à parcourir les slides de Markus Winand ou parcourir ses sites Use The Index Luke et Modern SQL.

Un autre avantage de PostgreSQL est la possiblité d'utiliser des types "complexes" (Array, JSON/JSONB, XML, Hstore facilitant la dénormalisation des données et permettant de "faire du NoSQL" dans un SGBD relationnel.
Vous pouvez également créer vos propres types. D'ailleurs, chaque table dans PostgreSQL est un type composite du nom de la table créée.
Pour en savoir plus sur les types de PostgreSQL et sur les fonctions d'agrégats .

PostgreSQL est extensible et dispose d'un très grand nombre d'extensions (Ex: PostGIS pour la gestion des données spatiales).

PostgreSQL offre également la possiblité de créer des fonctions, des procédures grâce au support natif de langage tels que le PL/pqSQL, PL/Python, PL/Perl. D'autres langages peuvent être ajouter facilement comme le PL/v8 qui permet de faire du JavaScript dans le SGBD.

Pour en terminer sur les avantages de PostgreSQL, en voici quelques autres :

  • Les schémas.
  • Le SGBD dispose d'un système de notification asynchrone natif (Listen/Notify).
  • Il implémente des portions de la norme SQL/MED permettant d'accéder en SQL à des données qui résident en dehors de PostgreSQL (Foreign data wrappers).
  • La création de vue matérialisée est possible.
  • Un système de réplication est également disponible.
  • La création de plusieurs schémas dans une même base.
  • Héritage de tables.
  • L'opérateur RETURNING.
  • Index partiels.
  • et bien d'autres...
Installation

Nous nous focaliserons sur l'installation sur une machine Debian Jessie. Si vous disposez d'un autre système, je invite à recupérer l'installeur depuis ce lien.

Par défaut, la version de PostgreSQL dans la distribution Jessie est la 9.4. Si vous souhaiter utiliser une version plus à jour, je vous invite à suivre le lien

Que vous installiez PostgreSQL depuis un dépot tiers ou depuis le dépot Jessie la commande est la suivante :

sudo apt-get install postgresql postgresql-client

Cette commande installera le serveur et client de PostgreSQL et créera un utilisateur postgres (équivalent de l'utilisateur root avec MySQL). Une fois l'installation terminée, le serveur PosgreSQL demarrera.

Voici quelques commandes de base pour lancer / arrêter / recharger / redémarrer le serveur PostgreSQL:

  • sudo service postgresql start
  • sudo service postgresql stop
  • sudo service postgresql reload
  • sudo service postgresql restart
Configuration

Par défaut, la configuration de PostgreSQL se trouve dans le dossier /etc/postgresql/X.X/main/ où X.X correspond au numéro de version installé, dans moncas c'est la 9.5.
Dans ce dossier, les fichiers qui nous intéressent sont les suivants :

  • pg_hba.conf, contient les configuration pour l'authentification
  • postgresql.conf, contient les configurations

Le fichier pg_hba.conf permet de gérer les configurations d'authentification du client. Ce fichier permet entre autres de filtrer les machines, utilisateurs, bases de données acessibles.
Avant de faire un modification sur le fichier, je vous conseille d'en réailiser une sauvegarde.

Le fichier postgresql.conf, contient les configurations globales (activation des logs, port, adresses d'écoute,...). C'est ce fichier qui sera notamment altéré lorsque vous voudrez améliorer les performances de PostgreSQL. Tout comme pour le fichier pg_hba.conf, je vous conseille de réailiser une sauvegarde avant toute modification.

Psql, le terminal interactif PostgreSQL

psql est une interface en mode texte pour PostgreSQL. Il permet de saisir, exécuter et visualiser des requêtes SQL et leurs résultats. Il dispose également de quelques raccourcis pour lister les tables, visualiser les index, ... , sans passer par du SQL.

  • Exemple psql 
    $ su postgres
$ psql template1
template1=# CREATE USER tester WITH PASSWORD 'test_password';
template1=# CREATE DATABASE "test_database";
template1=# GRANT ALL PRIVILEGES ON DATABASE "test_database" to tester;
template1=# \q

    Dans l'exemple ci-dessus, je :

  • change d'utilisateur système pour me mettre en postgres (utilisateur créer lors de l'installation)
  • me connecte à la base template1 (base crée par défaut à lors de l'installation) à l'aide de psql
  • crée un utilisateur "tester" avec comme mot de passe "test_password"
  • crée une base de donnée "test_database"
  • donne toute les permissions à l'utilisateur "tester" sur "test_database"
  • quitte le terminal

Attention à ne pas oublier le ";" à la fin des requêtes.

Général

Connexion

$ psql
$ psql -U <username> -d <database> -h <hostname> -p <port>
$ psql --username=<username> --dbname=<database> --host=<hostname> --port=<port>

Informations sur la connexion

\conninfo

Nettoyer l'écran

(CTRL + L)

Déconnexion

\q
\!

Base de données

Lister les bases

\l

Connexion

\c <database_name>

Création

CREATE DATABASE <database_name> WITH OWNER <username>;

Suppression

DROP DATABASE IF EXISTS <database_name>;

Utilisateurs

Lister les rôles

SELECT rolname FROM pg_roles;

Création

CREATE USER <user_name> WITH PASSWORD '<password>';

Suppression

DROP USER IF EXISTS <user_name>;

Modification du mot de passe

ALTER ROLE <user_name> WITH PASSWORD '<password>';

Droit d'accés

Autoriser tous les droits d'accès à une base de données

GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <user_name>;

Autoriser la connexion à une base de données

GRANT CONNECT ON DATABASE <db_name> TO <user_name>;

Autoriser l'USAGE pour un schéma

GRANT USAGE ON SCHEMA public TO <user_name>;

Autoriser l'exécution d'une fonction

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO <user_name>;

Autoriser le CRUD sur toutes les tables

GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA public TO <user_name>;

Autoriser le CRUD sur une table

GRANT SELECT, UPDATE, INSERT ON <table_name> TO <user_name>;

Autoriser le SELECT sur toutes les tables

GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user_name>;

Schema

Lister les schémas

\dn
SELECT schema_name FROM information_schema.schemata;
SELECT nspname FROM pg_catalog.pg_namespace;

Création

CREATE SCHEMA IF NOT EXISTS <schema_name>;

Suppression

DROP SCHEMA IF EXISTS <schema_name> CASCADE;

Tables

Lister des tables de la base courante

\dt

SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;

Lister les tables (Global)

\dt *.*.

SELECT * FROM pg_catalog.pg_tables

Lister les tables du schema

\d <table_name>
\d+ <table_name>

SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<table_name>';

Création

CREATE TABLE <table_name>(
  <column_name> <column_type>,
  <column_name> <column_type>
);

Suppression

DROP TABLE IF EXISTS <table_name> CASCADE;

Ajout d'une colonne

ALTER TABLE <table_name> IF EXISTS
ADD <column_name> <data_type> [<constraints>];

Modification d'une colonne

ALTER TABLE <table_name> IF EXISTS
ALTER <column_name> TYPE <data_type> [<constraints>];

Suppression d'une colonne

ALTER TABLE <table_name> IF EXISTS
DROP <column_name>;

Création d'une clé primaire auto-incrementée

CREATE TABLE <table_name> (
  <column_name> SERIAL PRIMARY KEY
);

Modification d'une colonne en clé primaire auto-incrementée

ALTER TABLE <table_name>
ADD COLUMN <column_name> SERIAL PRIMARY KEY;

Insérer dans une table avec une clé primaire auto-incrementée

INSERT INTO <table_name>
VALUES (DEFAULT, <value1>);

INSERT INTO <table_name> (<column1_name>,<column2_name>)
VALUES ( <value1>,<value2> );

Scripting

Exécuter un script local

psql -U <username> -d <database> -h <host> -f <local_file>

Sauvegarder une base

pg_dump -a <database_name>
pg_dump --data-only <database_name>

Sauvegarder un schema

pg_dump -s <database_name>
pg_dump --schema-only <database_name>

Restaurer une base

pg_restore -a <database_name> <file_pathway>

Restaurer un schema

pg_restore -d <database_name> <file_pathway>

Exporter une table dans un fichier CSV

COPY <table_name> TO '<file_path>' DELIMITER ',' CSV;
\copy <table_name> TO '<file_path>' DELIMITER ',' CSV

Exporter une table (X colonnes), dans un fichier CSV

COPY <table_name>(<column_1>,<column_1>,<column_1>) TO '<file_path>' DELIMITER ',' CSV;
\copy <table_name>(<column_1>,<column_1>,<column_1>) TO '<file_path>' DELIMITER ',' CSV

Importer un fichier CSV dans une table

COPY <table_name> FROM '<file_path>' DELIMITER ',' CSV;
\copy <table_name> FROM '<file_path>' DELIMITER ',' CSV

Importer un fichier CSV (X colonnes) dans une table

COPY <table_name>(<column_1>,<column_1>,<column_1>) FROM '<file_path>' DELIMITER ',' CSV;
\copy <table_name>(<column_1>,<column_1>,<column_1>) FROM '<file_path>' DELIMITER ',' CSV
Quelques outils pour PostgreSQL

Aucune réponse