Installer POSTGRESQL et une base de données compatible POSTGIS

Installation générale de PostgreSql

Le document a été réalisé à partir de la version 10.04 d’Ubuntu, et 8.4 de Postgresql.

Liste des paquetages installés

i postgresql - object-relational SQL database (supported

i A postgresql-8.4 - object-relational SQL database, version 8.

i postgresql-8.4-postgis - geographic objects support for PostgreSQL

i A postgresql-client-8.4 - front-end programs for PostgreSQL 8.4

i A postgresql-client-common - manager for multiple PostgreSQL client ver

i A postgresql-common - PostgreSQL database-cluster manager

i A postgresql-doc - documentation for the PostgreSQL database

i A postgresql-doc-8.4 - documentation for the PostgreSQL database

i postgis - geographic objects support for PostgreSQ

i phppgadmin - web-based administration tool for PostgreSQL

Apporter des modifications pour accepter les connexions distantes

Éditez le fichier /etc/postgresql/8.4/main/pg_hba.conf, puis rajoutez les lignes suivantes :

# site 1

host all all 10.xx.xx.0/21 md5

# site 2

host all all 10.xx.xx.0/25 md5

Le test est réalisé à partir de l’adresse IP appelante.

Éditez le fichier /etc/postgresql/8.4/main/postgresql.conf :

# - Connection Settings - 

listen_addresses = '*'

Installer PhpPgAdmin

Il s’agit d’une interface web qui permet de manipuler la base de données. Si elle n’est pas aussi riche que PgAdmin III, elle est largement suffisante pour gérer les bases de données, donner les droits, ou exécuter des requêtes SQL.

L’installation du programme ne pose pas de problème particulier (paquetage phppgadmin), mais un certain nombre de paramétrages doivent être appliqués.

Modifier PhpPgAdmin pour le rendre accessible en mode web

Éditez le fichier /etc/apache2/conf.d/phppgadmin, et modifiez les lignes suivantes :

(...)

order deny,allow

#deny from all

#allow from 127.0.0.0/255.0.0.0 ::1/128

allow from all

(…)

Créer un compte root pour accéder à l’administration du SGBD

En étant connecté en super-administrateur dans le serveur (console SSH) :

su postgres

psql

CREATE USER root;

ALTER ROLE root WITH CREATEDB;

ALTER USER root WITH ENCRYPTED PASSWORD 'mot_de_passe';

ALTER ROLE root WITH SUPERUSER;

Modifier PhpPgAdmin pour inhiber le contrôle du compte root

Éditez le fichier /usr/share/phppgadmin/conf/config.inc.php :

$conf['extra_login_security'] = false;

Mettre en place la sauvegarde des bases de données

Éditez le fichier /root/backupPgsql.sh :

#!/bin/bash 

# Script de sauvegarde des bases de donnees postgresql

# informations de connexion dans le fichier .pgpass

FICHIER=/mnt/svburo/backup/postgresql.sql

LOG=/var/log/backupPgql.log

SUJET="Sauvegarde PostgreSQL sur svbdd"

date > $LOG

echo "sauvegarde vers "$FICHIER >>$LOG

pg_dumpall > $FICHIER 2>>$LOG

# Recuperation des messages de backup

cat $FICHIER|grep "PostgreSQL database" >>$LOG

ls -lh $FICHIER >> $LOG

date >> $LOG

mail -s "$SUJET" root <$LOG

puis :

chmod +x /root/backupPgsql.sh

et programmez son exécution dans le crontab :

crontab -e

00 18 * * * /root/backupPgsql.sh

Gérer les bases de données compatibles Postgis

Rendre compatible une base de données avec POSTGIS

La base de données a été créée au préalable, avec phppgadmin (ou tout autre outil équivalent).

Connectez-vous en root dans la machine, puis basculez vers le login postgres :

su postgres

lancez ensuite les commandes suivantes :

createlang plpgsql testsig

(testsig est le nom de la base de données)

psql -d testsig -f /usr/share/postgresql/8.4/contrib/postgis.sql

Intégrez les données dans la table spatial_ref_sys :

psql -d testsig -f /usr/share/postgresql/8.4/contrib/spatial_ref_sys.sql

Rajoutez ensuite les commentaires des fonctions :

psql -d testsig -f /usr/share/postgresql/8.4/contrib/postgis_comments.sql

Créez un utilisateur pour la base de données, et donnez-lui les droits complets (avec phppgadmin, par exemple).

Vériifiez que l’utilisateur de la base de données a bien accès aux tables spatial_ref_sys et geometry_columns

Déconnectez-vous ensuite de l’utilisateur postgres avec Ctrl D.

Vérifications

On peut vérifier que la mise à jour a bien fonctionné en :

vérifiant l’existence des tables geometry_columns et spatial_ref_sys

vérifiant l’existence de plusieurs dizaines de fonctions traitant de la géométrie, par exemple : _stasgeojson(integer, geometry, integer, integer)

Documentation

Le site http://www.postgis.fr, en français, présente pas mal d’informations concernant Postgis. La documentation d’installation et d’utilisation de postgis, version 1.4, peut être consultée ici :http://www.postgis.fr/book/print/156

Quelques commandes utiles...

Pour connaître la version de Postgis installée :

SELECT PostGIS_full_version();

Indispensable pour savoir avec quelle version on travaille, notamment pour trouver la documentation adaptée...

Requêtes multibases – installer dblink

Avec PostgreSQL, il n'est nativement pas possible de réaliser des requêtes portant sur plusieurs bases de données à la fois. Néanmoins, un outil complémentaire permet de rendre ce service : dblink. Dblink est un jeu de quelques fonctions qui s'occupent de gérer la connexion à une base de données distante, puis de récupérer les données pour les manipuler dans la base de données appelante.

Pour de plus amples informations, consultez la documentation PostgreSQL : http://www.postgresql.org/docs/8.4/static/dblink.html

Avec la version 8.4, l'installation s'effectue ainsi :

Installer DbLink

DbLink est composé de fonctions qui doivent être intégrées à la base de données qui doit accéder aux autres bases.

Le script SQL de création des fonctions est fourni avec le paquetage postgresql-contrib. Une fois installé, cherchez le fichier dblink.sql, puis tapez les commandes suivantes :

su postgres 

psql -d database -f /usr/share/postgresql/8.4/contrib/dblink.sql

Utiliser DbLink

Le plus facile, c'est d'encapsuler DbLink dans une vue, qui pourra alors être manipulée très simplement avec des commandes SQL classiques.

Voici un exemple de script de création d'une vue utilisant DbLink :

CREATE OR REPLACE VIEW v_arrachage_parcelle AS 

SELECT t1.iddossierparcelle, t1.iddossier, t1.nomparcelle, t1.numdepartement, t1.numcommune, t1.numsection, t1.numparcelle 

FROM dblink('dbname=arrachage host=pgsql.local.draf-aquitaine.agri port=5432 user=login password=motDePasse', 

'select iddossierparcelle, iddossier, nomparcelle, numdepartement, numcommune, numsection, numparcelle from v_parcelle'::text)

t1(iddossierparcelle integer, iddossier integer, nomparcelle text, numdepartement text, numcommune text, numsection text, numparcelle text);

 ALTER TABLE v_arrachage_parcelle OWNER TO root; 

GRANT ALL ON TABLE v_arrachage_parcelle TO root; 

GRANT SELECT ON TABLE v_arrachage_parcelle TO "arrachage-Sig"; 

GRANT ALL ON TABLE v_arrachage_parcelle TO "sigGroupe";

Quelques explications...

Dans la commande FROM, le nom de la table est remplacé par un appel de la fonction dblink. La construction de cette fonction est réalisée en trois parties :

• le premier argument correspond aux critères de connexion à la base de données distante ;
• le second argument contient la commande exécutée dans la base de données distante. Le nom des colonnes retournées doit être impérativement détaillé (pas de caractère joker comme select *) ;
• après l'appel à la fonction, les champs retournés doivent être typés (integer, text...).

Compte-tenu de la lourdeur d'écriture, l'utilisation d'une vue est quand même plus simple que de monter des requêtes manuellement...

PGADMIN III

PGADMIN III est un logiciel fonctionnant avec Windows (ou Linux), qui permet de manipuler la base de données, ou de réaliser diverses opérations.

C’est un logiciel qui fonctionne avec des plugins, certains étant indispensables pour intégrer des fichiers externes, comme des shapefiles ou des données numériques.

Les plugins sont constitués :

d’un exécutable (au moins) ;

de paramètres d’exécution, qui sont insérés dans le fichier C :\Program Files\pgAdmin III\1.12\plugins.ini

Pour activer un plugin, il suffit d’installer le programme d’exécution dans le dossier d’installation de Pgadmin, puis de modifier le fichier plugins.ini en rajoutant les lignes correspondant au plugin considéré (lignes fournies avec le plugin).