Installation de PostGres sous FreeBSD

Présentation générale

Procédure détaillée

Adjonction d'une table supplémentaire

Transfert d'une base

Tests de charge

Programme PgAccess

Programme PgAdmin

Présentation générale

Installation et exécution de la base sur un serveur (unx-1923)
Consultation de la base depuis un client (unx-1923)

Procédure détaillée

Installation par les ports de FreeBSD :

unx-1923# cd /usr/ports/databases/postgresql7/
unx-1923# make install && make clean
....

Configuration de la base

# préparation du répertoire de stockage des données
unx-1923# mkdir /usr/local/pgsql/data
unx-1923# chown pgsql /usr/local/pgsql/data
unx-1923# su pgsql
$ /usr/local/bin/initdb -D /usr/local/pgsql/data
The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.

The database cluster will be initialized with locale C.

Fixing permissions on existing directory /usr/local/pgsql/data... ok
creating directory /usr/local/pgsql/data/base... ok
creating directory /usr/local/pgsql/data/global... ok
creating directory /usr/local/pgsql/data/pg_xlog... ok
creating directory /usr/local/pgsql/data/pg_clog... ok
creating template1 database in /usr/local/pgsql/data/base/1... ok
creating configuration files... ok
initializing pg_shadow... ok
enabling unlimited row size for system tables... ok
initializing pg_depend... ok
creating system views... ok
loading pg_description... ok
creating conversions... ok
setting privileges on built-in objects... ok
vacuuming database template1... ok
copying template1 to template0... ok

Success. You can now start the database server using:

    /usr/local/bin/postmaster -D /usr/local/pgsql/data
or
    /usr/local/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

# modifications dans les fichiers de configuration
# dans postgresql.conf :
tcpip_socket = true

# dans pg_hba.conf
host all all 191.250.104.230 255.255.255.255 trust

Démarrage de la base

[sur le serveur]
# puis en revenant sur le compte root :
unx-1923# /usr/local/etc/rc.d/010.pgsql.sh start
 pgsqlunx-1923#

# creation de la premiere base
unx-1923# su pgsql
$ psql template1
Welcome to psql 7.3.2, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# CREATE DATABASE trace;
CREATE DATABASE
# creation d'un utilisateur avec les droits superuser
template1=# CREATE USER trace CREATEUSER;
CREATE USER
template1=#
template1=# \q

Remplissage de la base (sur le client)

w00-3415% psql -h unx-1923 trace trace
Welcome to psql 7.3.2, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

trace=# \i create_trace.sql
psql:create_trace.sql:10: NOTICE:  CREATE TABLE / PRIMARY KEY will create
implicit index 'titre_rfc_pkey' for table 'titre_rfc'
CREATE TABLE
psql:create_trace.sql:16: NOTICE:  CREATE TABLE / PRIMARY KEY will create
implicit index 'prots_reseau_pkey' for table 'prots_reseau'
CREATE TABLE
psql:create_trace.sql:22: NOTICE:  CREATE TABLE will create implicit
trigger(s) for FOREIGN KEY check(s)
CREATE TABLE
psql:create_trace.sql:30: NOTICE:  CREATE TABLE will create implicit
trigger(s) for FOREIGN KEY check(s)
CREATE TABLE
# copy dans la table distante de fichiers dispo en local
trace=# \copy titre_rfc FROM 'RFC_titre'
\.
trace=# \copy prots_reseau FROM 'prots'
\.
trace=# \copy normes FROM 'normes'
\.
trace=# \copy module_perl FROM 'modules'
\.
[verification du bon remplisssage de la base : requete SQL]
trace=# SELECT normes.no_rfc,titre_rfc FROM normes, titre_rfc
trace-# WHERE normes.no_rfc = titre_rfc.no_rfc AND protocole = 'FTP';
 no_rfc |                            titre_rfc
--------+-----------------------------------------------------------------
    959 | File Transfer Protocol. J. Postel, J.K. Reynolds. Oct-01-1985.
   2228 | FTP Security Extensions. M. Horowitz, S. Lunt. October 1997.
   2640 | Internationalization of the File Transfer Protocol. B. Curtin.
   2773 | Encryption using KEA and SKIPJACK. R. Housley, P. Yee, W. Nace.
(4 rows)

trace=# SELECT normes.no_rfc,nom_module FROM normes, module_perl
trace-# WHERE normes.protocole = module_perl.protocole AND normes.no_rfc = 2616;
 no_rfc |     nom_module
--------+--------------------
   2616 | libwww-perl-5.69
   2616 | HTTP-Lite-2.1.3
   2616 | libwww-perl-5.69
   2616 | IO-Socket-SSL-0.92
(4 rows)

Adjonction d'une table supplémentaire

Table des exigences de la PTS

# création de la table dans la base [sous psql]
trace=# \i  create_req.sql
psql:create_req.sql:12: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'exigences_pkey' for table 'exigences'
CREATE TABLE

# remplissage de la table avec un script perl (dup_reqs est la liste des exigences en double)
# le fichier Excel en entrée doit par ailleurs être modifié sous Vi
# (les "vraies" fins de ligne dans le fichier Excel sont repérées
# par un ^M (Ctrl-M / CR) en plus du NL : il faut donc remplacer les NL par des
# caractères de continuation \(NL) et les couples CR/NL par le seul NL)

w00-3415% perl csv_in.pl < ~/poub/PTS/PTS-Vol-I.csv > dup_reqs

# il faudra compléter la base par des informations telles que :
- référence du document contenant l'exigence
- révision du document
- allocation de l'exigence
- version de NSS où l'exigence sera livrée
- liaison avec les exigences de niveau inférieur (traçabilité)
- liaison avec les tests de validation (détermination de la couverture des tests)
- ....

Consultation de la base des exigences

Création d'un script perl/CGI pour consulter la base de données.
postgres.pl permet une consultation multi-critère de la base avec affichage en HTML. Le script doit pouvoir être amélioré (IHM peu claire dans la version courante).
Le script est accessible ici

A faire : consultation de la base PostGres par le programme avec IHM "PgAccess" (en fait, PgAdmin est un meilleur candidat.

Transfert d'une base

Contexte

La machine unx-1923 a été mise à niveau de FreeBSD 5.1 vers 5.2.
A cette occasion, tous ses ports ont été réinstallés, dont PostGres.
Les bases existantes ont été transférées de PG 7.3.2 (version avec FreeBSD 5.1) vers PG 7.3.4.
Le transfert s'est fait par sauvegarde des bases (dump) avec PG 7.3.2, avant de réinstaller la machine, puis ré-injection des données dans la nouvelle base.

Sauvegarde

La commande pg_dumpall est utilisée pour sauvegarder les données dans l'ancienne base :

cd ~test/tmp
pg_dumpall -U superuser > db.out
ll
total 530
-rw-r--r--  1 test  users  514518 Jan 15 10:14 db.out

Le fichier db.out est la suite des commandes SQL à taper pour recréer la base.

Restauration

(processus analogue à celui de la création initiale de la base)

unx-1923# mkdir /usr/local/pgsql/data
unx-1923# chown pgsql /usr/local/pgsql/data
unx-1923# su pgsql
$ /usr/local/bin/initdb -D /usr/local/pgsql/data
....

# modifications dans les fichiers de configuration
# dans postgresql.conf :
tcpip_socket = true

# dans pg_hba.conf
host all all 191.250.104.230 255.255.255.255 trust

# redémarrage de la base
unx-1923# /usr/local/etc/rc.d/010.pgsql.sh start
 pgsqlunx-1923#

Remplissage de la base avec les données sauvegardées :

unx-1923# su pgsql
$ psql -f db.out template1
You are now connected to database template1.
DELETE 0
CREATE USER
CREATE USER
CREATE USER
CREATE USER
....
psql:db.out:3789: NOTICE:  ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ALTER TABLE
psql:db.out:3798: NOTICE:  ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ALTER TABLE
$

La base est alors créée et remplie (vérification par exemple avec PgAdmin).

Tests de charge

Programme

Le programme utilisé est dérivé du programme csv_in.pl initial développé pour intégrer les exigences de la PTS dans une base PostGres. Les sources du programme sont ici (adjonction d'un nombre de boucles pour augmenter le nombre d'écritures en base).

Tests

Résultats de mesure (base de données sur deux disques en RAID

Test à réaliser : installation d'une base PostGres sur la machine Motorola avec des disques configurés en mode "RAID". Cette machine est une bonne approximation de la configuration retenue pour les ANSU. La performance obtenue avec PostGres utilisant un disque RAID est intéressante par rapport à la performance avec un seul disque.

Le test est réalisé avec deux options : "commit"er les écritures en base immédiatement après chaque envoi ou bien attendre la fin des envois de données.

Les performances sont bien différentes :

Explications :

Les copies d'écran suivantes prises dans le test avec "commit" au fil de l'eau expliquent l'énorme différence de performance :

La sortie de "top" montre que le CPU n'est pas limitant.

La sortie de "systat -vmstat 1" montre que l'accès au disque dur ad0 est le facteur bloquant (c'est un disque à faible performance et est limité à 50 E/S par seconde).

Les copies d'écran suivantes prises dans le test avec "commit" en fin de session montrent de meilleurs performances :

La sortie de "top" : il y a plus d'E/S, donc le CPU passe plus de temps dans le kernel (d'où la portion "sys"), de même, les E/S génèrent des interuptions.

La sortie de "systat -vmstat 1" montre aussi pourquoi les performances sont meilleures : il y a beaucoup moins d'E/S disque, donc PostGres peut prendre les requêtes à la vitesse du réseau (voir le nombre d'interruptions pour le composant em0 (2009 contre 55 dans le cas "commit au fil de l'eau").

Autre série de mesures sans utiliser un des deux disques

Comme un des deux disques de la machine Motorola est vraiment très lent, une autre série de mesures est déclenché avec la base de données sur un seul disque.

Avec un "commit" en fin d'insertion de données : le temps (26s) est équivalent à celui obtenu avec les deux disques RAID en parallèle (PostGres optimise pour grouper les E/S disques et peut donc contourner les faibles performances d'un mauvais sous-système disque).

Avec un "commit" au fil de l'eau, le temps est moins catastrophique que dans la configuration RAID : 1 minute 20 secondes contre plus de 20 minutes.

Les copies d'écran suivantes prises dans le test avec "commit" au fil de l'eau montrent les performances suivantes :

La sortie de "top" : les E/S disque font que le CPU passe du temps dans le kernel (d'où 38% de "sys"), de même, les E/S génèrent des interuptions. La charge ("User") due à PostGres est négligeable.

La sortie de "systat -vmstat 1" montre de bonnes performances : le disque dur encaisse jusqu'à 550 E/S d'où une baisse de performance limitée par rapport au "commit" en fin de session..

Mesures sur un "vieux" serveur

Le serveur unx-1923 est une machine assez ancienne, avec des composants obsolètes : voir son dmesg (parmi les points marquants : seulement 64 MO de RAM et un processeur à 266MHz contre 1Go de RAM et 933MHz sur la machine Motorola).

PostGres a été installé sur cette machine avec la même configuration que sur la machine Motorola.

Le même programme de mesure de performance a été exécuté depuis le poste client w00-3415.

Les mesures de performance sont très proches des meilleurs obtenues sur la machine Motorola :

Une explication à la relative bonne performance de PostGres sur cette vieille machine est l'accès disque : voir une copie d'écran de systat avec plus de 420 accès disque par seconde (PostGres en mode "commit au fil de l'eau").

Programme PgAccess

Description

Programme IHM graphique de pilotage de PostGres. Le programme est installé depuis les "ports" FreeBSD (avec gestion automatique des dépendances : installation des composantes TCL/Tk indispendables à l'exécution).

Ce programme est une alternative au programme pgsql, utilisé en mode "ligne de commande".

Problèmes

Les bases de données ou les tables doivent être mal déclarées : je n'arrive pas à voir dans PgAccess les tables déjà existante dans la base de données "trace".

Programme PgAdmin

Description

Programme IHM graphique de pilotage de PostGres. Le programme est installé depuis les "ports" FreeBSD (Contrairement à PgAccess, PgAdmin fonctionne de façon satisfaisante).

Fonctions principales

Consultation du contenu des bases
Modification des données de la base.

$Id: PostGres_FB.html,v 1.9 2004/02/12 10:57:43 herbelot Exp $