Comparatif PostgresSQL Vs MySQL
MySQL est un RDBMS (Relational Database Management System) et PostgreSQL est un ORDBMS, le O pour objet constitue une différence par rapport à un RDBMS dans l'appréciation de ses éléments fondamentaux : schéma, base, table et ligne. Chaque élément sera apprécier comme un objet pouvant hériter d'un autre.
La principale différence se situe au niveau de la notion de schéma, des rôles et des utilisateurs.
MySQL : Le schéma est la définition d'un regroupement de table et de leur relations.
PostgreSQL : Le schéma est un regroupement de table au sein d'un base. Il existe une notion d'héritage, par défaut un nouveau schéma va hériter du schéma template0. Un schéma va contenir des : tables, trigger et procédures stockées. On parle de base objet car il existe une notion d'héritage sur les schémas et tables et chacune sont considérées comme des objets.
1 - Administration
1.1 - Installation
sudo apt-get install postgresql postgresql-contrib-* php5-pgsql
1.2 - Première connection
Pour se connecter à la CLI de postgres, va devoir se loguer en tant qu'utilisateur postgres :
sudo su - postgres
psql
On arrive dans CLI par défaut sur la base postgres comme l'indique l'invite de commande.
Pour lister les bases :
\l
Pour se connecter à une base :
\c nom_de_base
Pour liste les tables :
\d
Pour listes les colonnes d'une table :
\d nom_table
2 - Gestion mémoire
Postgres organise ses données par pages de 8ko qui seront stockée dans un cache dont la taille sera ajustable avec le paramètre shared_buffer.
Le paramètre work_mem représente l'espace mémoire dédié au tri.
Le paramètre maintenance_work_mem représente l'espace mémoire dédié à la maintenance.
Ces paramètres se trouvent dans le fichier postgresql.conf.
3 - Search path
Le search path est un paramètre de configuration qui équivaut au path du shell unix.
afficher le search path :
show search_path;
$user : schema qui port le nom du user
public : schéma public qui hérite du template0
4 - Rôles et Utilisateurs
Création d'un utilisateur :
shell :
createuser -d -P nom_utilisateur
psql :
CREATE USER nom_utilisateur;
Liste des utilisateurs :
\du
ou
SELECT usename FROM pg_user;
Un rôle est un ensemble de droits affectables à un utilisateur. Le superuser à tous les droits.
Un rôle :
- peut se logger
- peut se connecter
- peut limiter un nombre de connexions clientes
Attribution des rêles de création de base :
ALTER ROLE nom_utilisateur WITH CREATEDB;
On peut affecter un rôle a un utilisateur.
Exemple : On crée un rôle de lecteur
psql
create role lecteur nologin
le param "nologin" indique que le rôle ne possède pas de login, normal ce n'est pas un utilisateur.
On donne le rôle de lecture sur toutes les tables du schéma public dont ma base à héritée (de template0). Il est possible de le faire en global pour toutes les tables en version 9.x mais pas pour la version 8.4. Il faut alors procéder table par table.
psql
grant select on inscription to lecteur
On crée un utilisateur u1 dans le rôle lecteur :
psql
create user u1 login in lecteur
On se logue en tant que u1 en prenant d'avoir modifié au préalable le fichier de connexion pg_hba.conf en y ajoutant une ligne pour autoriser notre nouvel utilisateur u1 :
sudo nano /etc/postgresql/8.4/main/pg_hba.conf
Ajouter la ligne suivante dans la section IPv4 :
local all u1 md5
Puis on redémarre postgresql pour prendre en compte les modifications apportées au fichier pg_hba.conf.
sudo service postgresql reload
On attribue ensuite un mot de passe à notre utilisateur :
psql
alter user u1 password 'secret';
On se connecte ensuite en tant qu'utilisateur u1 sur la base db1.
psql -Uu1 db1
On vérifie ensuite les accès sur la table autorisée :
psql
select * from inscription;
Le select nous renvoie du résultat, on dispose donc des autorisations de lecture.
On vérifie ensuite les autorisations sur un delete :
psql
delete from inscription;
On nous retourne un message signifiant que l'on ne dispose pas les droits.
On vérifie pour la lecture sur une autre table :
psql
select * from adresse
On nous retourne un message signifiant que l'on ne dispose pas les droits.
On crée ensuite un rôle admin :
psql
create role admin nologin;
On donne droit sur la table inscription au rôle admin :
psql
grant all privileges on inscription to admin
On crée un user u2 :
psql
create user u2 password 'secret' noinherit in role lecteur, admin
L'option noinherit sert à ne pas hériter des roles des groupes lecteur et admin.
On autorise u2 à la connexion dans le fichier pg_hba.conf comme pour la création de notre utilisateur u1.
Notre user u2 n'a pas encore endosser de droits, il ne dispose d'aucun droit pour le moment.
On affecte alors le rôle "lecteur" à notre utilisateur u2, se connecter en tant que u2 :
psql -Uu2 -W
set role lecteur;
On peut supprimer des droits à un user ou à un rôle (à un user ds l'exemple) :
psql
remove all privileges on inscription from u1;
Cela ne produit rien, car u1 appartient au groupe lecteur.
5 - Les schémas
Afficher les schéma :
\dn
Création d'un schéma :
create schema nom_schema
Affectation du schéma au search path :
set search_path to nom_schema
A la suite de ces actions, on vient de créer un schéma non public, dans ce dernier on va pouvoir créer des objets tables , procédures, triggers, etc...
Suppression du schéma :
drop schema nom_schema;
6 - Les bases de données
A la création d'un base de données, je peux spécifier de quelle base elle hérite :
create database nom_schema $1 template0
La base crée hérite de la base template définie.
Les bases sont stockées physiquement dans :
cd /var/lib/postgresql/8.4/main/base/
Dans la base postgres, la table pg_class les noms des objets "table" et leur id correspondant :
select * from pg_class;
Afficher les bases en redirigeant vers le shell :
Pour rediriger vers le schell :
\!
Ex : \! ls /var/lib/postgresql/main/base
Création d'un base :
On se connect à la CLI pgsql en utilisateur postgres et on crée la db :
sudo su - postgres
createdb db_name
La base est crée par l'utilisateur postgres et il en sera le propriétaire. On peut créer une base en tant qu'un utilisateur donné :
createdb -O user_name db_name
sudo su - postgres psql create user USER superuser login createdb password 'PASSWORD';
Remarque : Faire attention à la locale du paramètre lc_messages dans le ficher /var/lib/postgresql/8.4/main/postgresql.conf
Par défaut elle à 'C' donc en en_US.UTF-8. Si le paramètre est dans une autre langue, il sera impossible d'utiliser des outils d'analyse de log comme pg_bagder et pg_fouine.
Les bases crées sont par défaut au format UTF8.
7 - Sauvegarde
On va sauvegarder une base au format plain sql en passant par la commande pg_dump :
sudo su postgres
pg_dump -C -Fp db_name > db_name_date.sql
Remarque : Lors de la restauration d'une base, le drop n'étant pas inclus dans le fichier de sauvegarde. Il est donc nécessaire de supprimer la base avant de la restaurer.
Supprimer une base de donnée :
sudo su - postgres
dropdb db_name
depuis le shell psql :
DROP DATABASE name;
8 - Restauration :
psql
\i /path/to/file.sql
équivalent shell :
psql db_name < /path/to/file.sql
Remarque : il n'est pas nécessaire de recréer la base avant de la restaurer.
Execution fichiers sql :
PGPASSWORD=password psql -h localhost -d dbname -U postgres -f /var/www/db.sql
9 - Authentification
Dans le fichier pg_hba.conf on va disposer de différentes méthodes d'authentification :
- md5 : mot de passe
- ident : lié à un compte unix
- trust : pas de mot de passe
- reject : rejette le compte utilisateur (règle d'exclusion)
remarque : l'ordre des règles est important.
Si on souhaite autoriser de nombreuses bases à un utilisateur, on peut lié un fichier qui contient la liste des bases à authoriser. Idem pour les utilisateurs.
Il est possible de passer une authentification avec échange de clés ssl (voir la doc).
Remarque : Dans le fichier postgresql.conf, les paramètres commentées indiquent la valeur qu'on ces derniers par défaut.
10 - Les fonctions
Les fonctions ou procédures stockées sont appelées lors d'un select et peuvent être déclenchées avant ou après ce dernier.
Elles permettent de déplacer un partie de la logique en base et d'empêcher d'éventuelles régressions. Attention, cela implique des tests spéciaux et un déploiement un peu plus complexe (overhead). Lors d'une migration, il sera nécessaire de séparer les données de la logique lors de l'export.
On peut écrire les fonctions dans plusieurs langages :
- PL-SQL
- C
- PL-PGSQL
- INTERNAL
Ces quatres langages sont jugés comme fiable car implémentés nativement dans postgresql. On y intégrer beaucoup plus de langages comme le PHP et autres via le biais d'extension. Ces dernières peuvent fragiliser la stabilité du système et ne sont pas recommandées.
Les fonctions vont permettre de créer des tables temporaires qui existeront le temps d'execution de la fonction et vont nous permettre de segmenter des traitements.
Exemple :
select * from generate_series(100, 0, -2);
-> génére une table comportant une suite décrementielle de 100 à 0 à avec un pas d'incrément de -2.
La fonction generate_series() est une primitive car native à postgresql.
Une fois que le client à récupérer le résultat, la table est effacée de l'espace mémoire.
Exemple :
pg_size_pretty(pg_database_size('db1'));
-> retourne la taille de db au format "human readable".
11 - Les séquences
Les séquences sont des mécanismes qui vont gérer des valeurs de façon incrémentale comme des ids.
Exemple :
On crée une table toto :
psql
create table toto(id serial, a char(10));
On vient de créer une séquence, elle va définir le comportement de l'id de type "serial". Il vient d'être crée une séquence toto_id_seq pour la colonne id de la table toto.
La séquence est caractérisée par des paramètres dont les suivants :
- minvalue : minimum de la valeur de l'id
- maxvalue : maximum de la valeur de l'id
- cycle: défini le pas de l'incrément
On va créer un nouvelle séquence :
psql
create sequence jours minvalue 1 maxvalue 7 cycle
L'option cycle sans valeur définira un pas d'incrément de 1.
12 - Les triggers
C'est une fonction qui peut se déclencher sur un événement : select, insert, update, delete et truncate (vide la table). Comme les fonctions les triggers peuvent se déclencher avant ou après la requête.
13 - Les domaines
C'est un type associé à une contrainte, par exemple la gestion des codes postaux peut mettre en jeu un domaine.
psql
create domain codepos as char(5) check(value ~/^\d{5}$/');
On vérifie que la valeur dispose bien de 5 caractères via l'expression régulière.
14 - Opération de maintenance
On peut réaliser différentes opérations de maintenance :
- Vacuum : une vacuum full identifie et supprime les lignes mortes. Jusqu'en version 8, le vacuum nécessite full nécessite un reindex.
- Analyze : met à jour les statistiques d'index
- Reindex : reconstruit les index (long)
Remarque : Pour automatiser la maintenance il va être nécessaire de réaliser un script pour automatiser les opérations de maintenance.
Attention : L'exécution de la maintenance est bloquante, cela nécessite une mise en veille du site.
L'autovacuum est activé en tâche de fond est sert à limiter la fragmentation, il est cependant nécessaire d'effectuer mensuellement un maintenance complète de bases.
On peut lancer une opération sur toutes les tables avec la commande :
psql
vacuum analyse
15 - Audit et activation de logs
Pour auditer un blocage, nous allons faire appel aux logs. Pour ce faire, on passe la valeur du paramètre logging_collector à "on" dans le fichier postgresql.conf et on relance le service postgresql :
sudo service postgresql restart
15.1 - Exemple d'audit :
On dispose d'un base de données dont la restauration échoue. Les Logs font état d'une erreur de type sur la chaine "e3d" dans le champ uuid.
On se connecte à la base via la CLI psql :
sudo su - postgres
\c DB_NAME
On active l'affichage en colonne pour plus de lisibilité :
\x
Lors du blocage sur l'erreur de type rencontrée lors de la restauration de la base de données. On souhaite afficher la ligne comportant l'id bloquant comportant la chaine "e3d" dans le champ uuid :
psql
select * from inscription order by id_inscription offset 142743 limit 3;
Le mot clé "offset" permet de sauter les n premiers enregistrements.
On a constate que la valeur du champs uuid ne comporte pas le bon type, pour corriger on va caster de dernier :
select * from inscription where cle::text='e3b';
On a transformé le type en text.