Nous allons voir dans cet article comment mettre en place un SGBD PostgreSQL.
Nous verrons notament comment l'installer, le configurer et le gérer.

Sommaire

Installation

Pré-requis

Nous allons commencer par activer les backport de Debian :

nano /etc/apt/sources.list  

Ajoutez :

#Backport for Postgresql
deb ftp://debian.advalem.net/debian-backports jessie-backports main  

Puis mettez à jour le gestionnaire de packet :

aptitude update  

Installation

Exécutez la commande suivante :

aptitude install postgresql-9.3  

Nous allons maintenant modifier les propriétés systèmes pour allouer plus de mémoire à PostgreSQL :

sysctl -w kernel.shmmax=134217728  

Pour que les paramètres soient conservés au démarrage de la machine, éditez le fichier suivant :

nano /etc/sysctl.conf  

Ajoutez :

kernel.shmmax = 134217728  

Configuration

Éditez le fichier :

nano  /etc/postgresql/9.3/main/postgresql.conf  

Modifiez :

listen_addresses = '*'  

Pour tunner la mémoire en fonction de vos besoins, vous pouvez suivre l'article suivant : Tunning PostgreSQL

Ensuite éditez :

nano /data/postgresInt/databases/pg_hba.conf  

Mettez :

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust  
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust  
# IPv6 local connections:
host    all             all             ::1/128                 trust  
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                trust
#host    replication     postgres        127.0.0.1/32            trust
#host    replication     postgres        ::1/128                 trust


# Management
host    all     all   192.168.0.0/24    md5

local   all     vacum                   trust

host    all all all md5  

Si vous souhaitez autoriser tous les réseaux à potentiellement se connecter sur votre PostgreSQL, il ajouter la ligne host all all all md5. Actuellement, seule réseau 192.168.0.0/24 est autorisé.

Pour prendre en compte les paramètres, vous devez relancer PostgreSQL :

/etc/init.d/postgres restart

Gestion

Les comptes utilisateurs

Création d'un compte utilisateur

su postgres  
createuser -p {db_port} -P {db_user}  

Maintenance

Défragmentation des bases

vacuumdb -a -f -U vacum -w  

Ré-indexation des bases

reindexdb -a -U vacum -w  

Backup et Restauration

Backup

Backup d'une unique base de données

Localement sur le serveur :

su postgres  
pg_dump {db_name} -f /tmp/{db_name}.sql  

A distance :

pg_dump -U postgres -h {server_ip} -p {server_port} {db_name} -f /tmp/{db_name}.sql  

Backup de l'ensemble des bases de données

Localement sur le serveur :

su postgres  
pg_dumpall > /tmp/all.sql  

A distance :

pg_dumpall -U postgres -h {server_ip} -p {server_port} > /tmp/all.sql  

Restauration

Restauration d'une unique base de données

Localement sur le serveur :

su postgres  
psql -f /tmp/{db_name}.sql  

Dans une nouvelle base de données :

su postgres  
psql -d {db_name} -f /tmp/{db_name}.sql  

A distance :

psql -U postgres -h {server_ip} -p {server_port} -f /tmp/{db_name}.sql  

Restauration de l'ensemble des bases de données

Localement sur le serveur :

su postgres  
psql -f /tmp/all.sql  

A distance :

psql -U postgres -h {server_ip} -p {server_port} -f /tmp/all.sql