Installer et configurer Postgresql 9.2.4

On notera qu'il existe d'autres type de SGBD dont Oracle, Microsoft, IBM (privatif) et MySQL (semi-libre).

L'avantage de Postgre est qu'il est performant, il n'y a pas de coût de licence et par défaut, c'est l'outil le plus sécurisé. Les coûts interviennent lorsque l'on doit faire appel à une main d’œuvre ou lors d'une migration par ex.

Notes

Installation

Pour se faire il faut télécharger le paquet, le décompresser et on l'installe :

# apt-get install gcc zlib1g libreadline6-dev zlib1g-dev
# wget https://ftp.postgresql.org/pub/source/v9.2.4/postgresql-9.2.4.tar.gz
# mv postgresql-9.2.4.tar.gz /usr/local/
# cd /usr/local/
# gunzip postgresql-9.2.4.tar.gz
# tar -xvf postgresql-9.2.4.tar
# cd postgresql-9.2.4/
# ./configure
# make
# make install

La réponse doit être :

PostgreSQL installation complete.

Création des utilisateurs

Par principe de sécurité, on ajoute l'utilisateur “postgres” au groupe “dba” :

# useradd -m -d /home/postgres -s /bin/bash postgres
# passwd postgres
# groupadd dba
# usermod -g dba postgres

Créer son environnement de travail

Crééon l'espace de travail de postgres :

# mkdir /usr/local/pgsql/data
# chown postgres:dba /usr/local/pgsql/data/

On va maintenant se connecter en tant que postgres pour travailler de manière sécurisé :

# su postgres

Pour que la manipulation des logiciels de postgresql soient plus simple, on renseigne une variable d'environnement dans le fichier .bashrc :

$ echo 'PATH="$PATH:/usr/local/pgsql/bin"' >> ~/.bashrc
$ . ~/.bashrc

On initialise et on démarre postgresql :

$ initdb -D /usr/local/pgsql/data
$ pg_ctl -D /usr/local/pgsql/data -l logfile start
S'il y a des erreurs lors du démarrage, elles peuvent être expliqué dans ce fichier logfile :
Pour la prochaine fois il est possible de le démarrer avec :

# service postgres start

En faisant une copie de ce fichier :

# cp /usr/local/postgresql-9.2.4/contrib/start-scripts/linux /etc/init.d/postgres

Créer et se connecter à la BDD

On se déconnecte et on se reconnecter à un terminal pour créer une base de donnée :

$ createdb ma_bdd

Enfin on s'y connecte :

$ psql ma_bdd

Cas d'utilisations

Lors que l'on est connecté à la BDD, il est possible d'exécuter des commandes pour réaliser diverses actions.

Commandes Résultat attendus
\dS Affiche toutes les tables + celles du systèmes
\d Affiche les tables
\d <table> Affiche la structure d'une table
\pset tuples_only on Affiche uniquement le résultat des collones
\o /home/postgres/spool.sql Active les logs des requêtes
\o Désactive les logs
\i <script.sql> Insérer du code SQL depuis un fichier (Ex : importation d'une table)
\g <script> Lance un script avec le résultat inscrit dans le fichier appelé
\z Liste les droits d'accès
\list Liste les bases de données
\x Permet de changer l'orientation de l'affichage de sortie
\q Quitte psql
\! Execute une commande système
  • Connaitre la taile d'une table :
  SELECT pg_size_pretty(pg_relation_size('ma_table'));

Par défaut la taille d’un bloc est de 8Ko.

  • Change le mot de passe d'un compte utilisateur
alter user nom_utilisateur with password 'nouveau_mot_de_passe';
  • Change le propriétaire d'une base
alter database nom_proprietaire owner to nouveau_nom_proprietaire;

Autoriser les connexions à distance

Pour se faire, il faut débord modifier le fichier de configuration qui est présent dans :

nano /usr/local/pgsl/data/pg_hda.conf

Ajouter cette ligne à la fin du fichier :

host     all     all     192.168.1.0/24     mot_de_passe
nano /usr/local/pgsql/data/postgres.conf

Ajouter ces lignes à la fin du fichier :

listen_addresses = '*'
port = 5432

Puis lancer cette commande depuis une machine distante :

/usr/local/pgsql/bin/psql -h <IP_MACHINE_DISTANTE> -d <NOM_BDD> -U <Utilisateur>;

Backup de sa BDD

  • Faire un backup d'une base de donnée :
pg_dump ma_bdd > ma_bdd.sql
  • Restauré une base de donnée :
psql ma_bdd < ma_bdd.sql

Mettre à jour son cluster

Les cluster gère virtuellement des espaces contenant des bases. Après une mise à jour du service, il y a besoin de faire la mise à jour des clusters manuellemnt. Par exemple pour passer de la version 11 à 13:

pg_dropcluster --stop 13 main
pg_upgradecluster 11 main
pg_dropcluster 11 main

Test de charge

  • Compiler le logiciel pgbench :
# cd /usr/local/postgresql-9.2.4/contrib/pgbench
# make
# make install
  • Le lancer depuis son son répertoire :
$ /usr/local/pgsql/pgbench -c 10 -t 10 -S ma_bdd
starting vacuum...end.
transaction type: SELECT only
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 100/100
tps = 53.987693 (including connections establishing)
tps = 54.537819 (excluding connections establishing)

BONUS

Bon sinon il y a plus simple pour l'installer :

# apt-get install postgresql-9.1

se connecter avec l'utilisateur postgres :

# su postgres

Puis utiliser postgresql :

$ psql

Et voilà! :)