PostgreSQL
Sommaire
Récupération des sources
Pour récupérer les sources, utiliser la commande wget (remplacer les numéros de version par celle que vous désirez)
Extraction des sources
L'archive étant de type gz, l'extraction se fait avec un tar -xvf classique
Compilation de PostgreSQL
Configuration
La configuration fait appel au script configure situé à la racine du code source. Il est possible de passer des paramètres au script configure pour activer/désactiver certains fonctionnalités
Pour notre cas, il faut passer au script configure les options suivantes:
- --prefix: L'emplacement des exécutables
- --with-ldap: Ajouter le support de l'authentification LDAP
- --with-openssl: Pour le support des connections cryptées
- --without-readline: Car nous n'utilisons pas les bibliothèques readline
La commande ci-dessus créera une sous-arborescence avec les répertoires suivants:
- bin: Pour stocker les exécutables;
- include: Pour les en-têtes nécessaires aux développements autour de PostgreSQL
- lib: Pour les bibliothèques partagées de PostgreSQL
- share: Où l'on retrouve entre autres des exemples de fichiers de configuration
Il faut au préalable installer les librairies SSL et LDAP : libssl-dev et libldap-dev
Compilation
Une fois le script configure déroulé sans erreur, il faut lancer la compilation de PostgreSQL. Cela s'effectue simplement avec la commande make suivie de la commande make install pour l'installation
[root@ordi ~]# make install
Lorsque l'installation est terminé, nous pouvons déplacer les sources de PostgreSQL dans le dossier /usr/local/src
Utilisateur Postgres
Pour des raisons de sécurité, il est préférable de créer un utilisateur spécifique pour administrer PostgreSQL :
[root@ordi ~]# adduser postgres --ingroup postgres
Ajout des commandes Postgres
Editer le fichier .bashrc de root et de postgres et rajouter ceci à la fin :
export PATH=$PATH:/opt/postgresql/bin
Initialisation de la base
Pour initialiser la base de données, nous devons d'abord créer le répertoire qui va accueillir les différents fichiers
Il existe 2 méthodes pour initialiser la base de données qui donneront exactement le même résultat
Démarrage de l'instance
- L'option -D spécifie le chemin de l'instance
- L'option -l spécifie l'emplacement des logs
Arrêt de l'instance
La commande classique permettant d'arrêter proprement une instance PostgreSQL est la suivante :
Si la procédure d'arrêt ne fonctionne pas correctement, il est possible d'arrêter le serveur en mode "rapide" :
Dans ce cas, les utilisateurs voient leurs transactions interrompues et leurs connexions rompues. Cela n'est pas très courtois envers les utilisateurs, mais les données sont totalement protégées.
PostgreSQL effectue un checkpoint avant de s’arrêter. Le démarrage suivant sera donc rapide et propre.
Script d'automatisation
Il est possible de créer un script bash qui permettrait de faciliter le start/stop/restart de la BDD. Ce script pourrait ressembler à ceci:
#! /bin/bash
# Auteur : Devleeschauwer Denis
# Postgresql start script
#
# Mars 2012
#
#GLOBAL VARIABLES
DIRECTORY=/opt/redmine
LOG_FILE=/opt/redmine/postgres.log
APPLICATION=Redmine
######################################################################
##########################Function####################################
######################################################################
function startInstance
{
#Commande pour demmarer l'instance Redmine
cd /opt/postgresql/bin
./pg_ctl -D $DIRECTORY -l $LOG_FILE start
if [ $? -eq 0 ]
then
echo "Start $APPLICATION Instance [OK]"
else
echo "Start $APPLICATION Instance [KO]"
fi
}
function stopInstance
{
#Commande pour arreter l'instance Redmine
cd /opt/postgresql/bin
./pg_ctl -D $DIRECTORY stop
if [ $? -eq 0 ]
then
echo "Stop $APPLICATION Instance [OK]"
else
echo "Stop $APPLICATION Instance [KO]"
fi
}
case "$1" in
'start')
startInstance
;;
'stop')
stopInstance
;;
'restart')
stopInstance
sleep 5
startInstance
;;
'status')
cat $DIRECTORY/postmaster.pid > /dev/null 2>&1
if [ $? -eq 0 ]
then
echo "$APPLICATION Instance Running"
else
echo "$APPLICATION Instance Stop"
fi
;;
*)
echo "Usage: $0 { start | stop | restart | status }"
exit 1
;;
esac
exit 0
Connexion au serveur
Ligne de commande
La connexion au serveur en ligne de commande se fait via le client integré à PostgreSQL
Le serveur nous répond avec une invite explicite:
psql (9.1.3) Type "help" for help
nomDatabase=#
Si la database n'écoute pas sur le port par défaut ( 5432 ), il faut spécifier dasn la commande de connexion l'option -p numeroPort
PgAdmin
Si l'on veut utiliser PgAdmin, il faut d'abord demander à PostgreSQL d'écouter sur le réseau et d'autoriser la connexion avec ou sans mot de passe crypté ou non crypté.
postgresql.conf
Le fichier chemin/instance/postgresql.conf vous nous permettre de spécifier sur quel adresse IP notre serveur PostgreSQl doit écouter.
Pour ce faire, il faut remplacer la ligne :
#listen_addresses = 'localhost"
par :
listen_addresses = 'votreIP'
Vous pouvez également spécifier '*' afin que le serveur écoute sur toutes les interfaces réseaux
Il est tout a fait possible de lancer plusieurs instances de PostgreSQL en parallèle sur un même serveur. Il suffit de modifier le port réseau sur lequel il va écouter dans le fichier /chemin/instance/postgresql.conf
pg_hba.conf
Le fichier chemin/instance/pg_hba.conf est le fichier gérant l'authentification des clients
Ajouter à la fin du fichier la ligne :
host all all 0.0.0.0/0 trust
Cette ligne signifie que tout le monde peut se connecter à la base de données sans demande de mot de passe
La prise en compte de ces 2 fichiers nécessite le redémarrage de l'instance
Les mots clés
Le premier champ décrit le type de connexion :
- local : Toute connexion depuis la même machine
- host : Toute connexion depuis l'extérieur, crypté ou non.
- hostssl : Toute connexion externe cryptée
- hostnossl : Toute connexion externe non-crypyée
Le champ database donne le nom de la ou des bases de données concernées. Outre le nom de la base, certains mots clés existent :
- all : décrit toutes les bases
- sameuser : décrit une base ayant le même nom que l'utilisateur
- samerole : décrit une base ayant le même nom que le rôle dont l'utilisateur fait partie
- replication : décrit une base quelconque ayany une réplication active
Le quatrième champ noté ici address décrit l'adresse de la ou les machines qui se connectent :
- all : désigne n'importe quelle adresse IP
- samehost : désigne n'importe quelle adresse IP du serveur local
- samenet : désigne l'ensemble du ou des sous-réseaux sur lesquels le serveur est connecté
- adresse masque : spécifier une adresse IP sous la forme de deux champs.
Le champ auth-method désigne la méthode d'authentification désirée :
- trust : autorise toute connexion de manière inconditionnelle
- reject : est l'inverse de trust
- md5 : exige du client un mot de passe encodé en md5
- password : exige du client un mot de passe non encrypté.
Créer la database
Pour créer la base de donnée, il faut être connecté en tant qu'utilisateur postgres :
- -e : Affiche le resultat de la commande
- -p : Spécifie le port d'écoute de l'instance ( définis dans pg_hba.conf)
- -h : Le nom d'hote
- sogo : nom de la database
Commandes
Changement password
Pour changer le mot de passe d'un utilisateur (également celui de postgres), il suffit d’exécuter la commande suivante
Créer utilisateur
CREATE USER name ; ALTER USER name WITH ENCRYPTED PASSWORD 'password';
Changer propriétaire database
ALTER DATABASE name OWNER TO new_owner;
Monitoring
Activité
SELECT * FROM pg_stat_activity;
Taille Database
SELECT pg_size_pretty(pg_database_size('DATABASE'));
Procédures
Backup/Restore
SQL Dump
Pour réaliser un backup d'une database, il suffit d'utiliser la commande pg_dump
- wikidb : Nom de la database ( A modifier selon le besoin)
- -p : Port d'écoute du serveur postgresql
- -u : Utilisateur ( Postgres est l'utilisateur "admin" de su serveur postgresql)
Restore Dump
Pour restaurer une base de données à partir d'un fichier de sauvegarde obtenu par la commande pg_dump :
Il faut penser à créer la base de données que l'on veut restaurer avant de réaliser l'import des données
Nouveaux utilisateur
On se connecte à l'instance postgres
Pour créer l'utilisateur tapez (Attention : mon_user est à changer par un nom d'utilisateur de vôtre choix) :
CREATE USER mon_user;
Pour donner le droit de créer des bases de données tapez ceci (Attention : mon_user est à changer par le nom d'utilisateur choisi au préalable) :
ALTER ROLE mon_user WITH CREATEDB;
Il est vivement conseillé d'attribuer un mot de passe pour votre nouvel utilisateur (Attention : mon_user est à changer par le nom d'utilisateur choisi au préalable, password est à changer par le mot de passe correspondant) :
ALTER USER mon_user WITH ENCRYPTED PASSWORD 'password';
Nouvelle Base de données
On se connecte à l'instance postgres
On crée la base de donnée de l'utilisateur (Attention : mon_user est à changer par le nom d'utilisateur choisi au préalable) :
CREATE DATABASE mon_user WITH OWNER user;
Nouvelle Instance pour applicatifs
Si on doit créer une nouvelle instance de PostgreSQL pour un applicatif, voici la marche à suivre :
- On crée le répertoire pour l'instance et on change le propriétaire:
- On initialise la base de donnée ( en tant qu'utilisateur postgres ):
- On copie le script d'une instance existante (ex: sogo) :
- Dans le script on modifie les 3 variables GLOBALES et la date d'édition du script:
#! /bin/bash # Auteur : Devleeschauwer Denis # Postgresql nomApplicatif start script # # Octobre 2012 # #GLOBAL VARIABLES DIRECTORY=/opt/postgres-nomApplicatif LOG_FILE=/opt/postgres-nomApplicatif/postgres.log APPLICATION="Postgres nomApplicatif"
- On rajoute le script pour qu'il soit exécuter au démarrage et à l'arret du système
- On édite le fichier /opt/postgres-nomApplicatif/postgresql.conf pour lui renseigner les interfaces et le port d'écoute :
Avant d'assigner les ports, consulter le tableau suivant
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
port = 5434
- On édite le fichier /opt/postgres-nomApplicatif/pg_hba.conf pour spécifier les accès à l'instance selon le réseaux :
# Mot de passe crypté en md5 pour le réseaux 192.168.1.0/24 host all all 192.168.1.0/24 md5 # Pas de mot de passe demandé pour le réseaux Management et serveurs host all all 192.168.11.0/24 trust host all all 192.168.168.0/24 trust
- On lance l'instance :
- On se connecte à la database pour changer le mot de passe de l'administrateur postgres
psql postgres -h adresseIP -p port -U postgres psql (9.1.6) Type "help" for help. postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD 'postgres'; ALTER ROLE
Mise a jour de version
Cette procédure a été créé pour la mise à jour d'un Postgresql de version 9.1.6 vers 9.2.2
- Backup de la base existante
Avant toute chose, on réalise un backup de la database
- Téléchargement de la nouvelle version
- Extraction des sources
- Configuration
- Installation
make make install
- Changement de PATH
On oublie pas changer la valeur du PATH pour qu'il prenne en compte la nouvelle version
A réaliser pour root et l'utilisateur postgres
- Initialisation
On va créer un nouveau dossier qui va accueillir la nouvelle version de la database pour l'application
On se connecte en tant que postgres
su postgres
On va initier la nouvelle database
On peux vérifier en affichant le contenu du fichier /opt/postgres-nomApplicatif-new/PG_VERSION
- Configuration
On modifie/recopie le fichier postgresql.conf et le fichier pg_hba.conf pour qu'il ressemble à celui de l'ancienne database.
- Modification du script de demarrage
On crée un script temporaire pour tester
On modifie les variable globales
DIRECTORY=/opt/postgres-nomApplicatif-new LOG_FILE=/opt/postgres-nomApplicatif-new/postgres.log APPLICATION="Postgres Sogo" DIRECTORY_BIN=/opt/postgresql-9.2.2/bin
On arrête l'ancienne instance
On lance la nouvelle version
On va remettre un mot de passe à l'utilisateur postgres de la database
ALTER USER postgres WITH ENCRYPTED PASSWORD 'postgres';
Pour vérifier le bon fonctionnement, on se connecte au serveur et on exécute la requête suivante :
SELECT version();
- Restauration des données
On va injecter dans la nouvelle version les données réalisé avec le backup qu'on a réalisé
On redémarre le serveur par acquis de conscience et on se connecte pour voir si toutes les données ont bien été restauré
- Changement nom dossier
Si la connexion et la restauration des données s'est bien déroulé, on va passer au remplacement de l'ancienne version.
On arrête les base de données ( la nouvelle et ancienne version )
On renomme "l'ancienne version" de la base de donnée
[root@ordi ~]# mv /opt/postgres-nomApplicatif-new /opt/postgres-nomApplicatif
- Changement nom de script
On change le nom du script /etc/init.d/postgres-nomApplicatif-new
- Modification script
Dans le script on doit modifier les variables globales
DIRECTORY=/opt/postgres-sogo LOG_FILE=/opt/postgres-sogo/postgres.log APPLICATION="Postgres Sogo" DIRECTORY_BIN=/opt/postgresql-9.2.2/bin
- Lancement script
On peux démarrer le serveur
On peux vérifier si on arrive à se connecter au serveur et vérifier si les donnes sont bien reprise
- Modification script de backup
Il ne faut pas oublier de modifier le script de backup nommé bckApplication (ex: /root/bckRedmine, /root/bckSogo ...) Il suffit de modifier dans les variables globales la variables DIRECTORY_POSTGRES pour la faire pointer vers la nouvelle version de postgresql
#Variables
DIRECTORY_POSTGRES=/opt/postgresql-9.2.2/bin
DATABASE=redmine
PORT=5434
USERNAME=postgres- Centreon
Pour que Centreon puisse vérifier le status des différentes instances PostgreSQL, il faut pas oublier de mettre à jour le lien symbolique qui se trouve dans /usr/bin
- PgPool
Pour s'assurer du bon fonctionnement, on se connecte à l'IP virtuelle de l'application voir Postgres-table
On essaie égalmement à partir des 2 PgPool. Si l'erreur suivante est affiché
psql postgres -H 192.168.168.15 -p 5433 -U postgres
psql: warning: extra command-line argument "192.168.168.15" ignored
psql (9.1.6, server 9.2.2)
WARNING: psql version 9.1, server version 9.2.
Some psql features might not work.
Type "help" for help.
postgres=# \q
Il suffit de mettre à jour postgresql sur les Pgpool comme on à fais pour l'applicatif. On réalise le configure, le make, le make install et l'export du PATH
