Utiliser des flux WFS dans PostGIS avec l’extension ogr_fdw

Il est possible de visualiser des flux WFS sur un serveur PostGreSQL/PostGIS en utilisant l’extension ogr_fdw qui conjugue les fonctionnalités de la librairie OGR et de l’extension Foreign Data Wrapper (postgres_fdw).

Installation des extensions nécessaires

Via pgAdmin, par exemple :

CREATE EXTENSION postgis;
CREATE EXTENSION ogr_fdw;

Interrogation du flux WFS avec ogr_fdw_info

Ou utilise ensuite l’utilitaire ogr_fdw_info pour connaître la liste des couches servies par le flux (avec un serveur PostGreSQL 9.6 sur Windows).

Pour connaître le fonctionnement d’ogr_fdw_info, on utilise la commande :

cd "C:\Program Files\PostgreSQL\9.6\bin"
ogr_fdw_info -help

Elle renvoie les usages suivants :

usage: ogr_fdw_info -s <ogr datasource> -l <ogr layer>
ogr_fdw_info -s <ogr datasource>
ogr_fdw_info -f

L’option -f permet de connaître les différents formats supportés :

ogr_fdw_info -f

On retrouve dans la liste l’item « WFS » (read only) qui permet avec l’option -s de paramétrer l’interrogation du serveur.

Dans cet exemple, on cherche à récupérer la couche des obstacles à l’écoulement en Métropole du SANDRE. L’adresse du flux provient de la fiche de métadonnées du lot de données :

ogr_fdw_info -s "WFS:http://services.sandre.eaufrance.fr/geo/obs_FXX"

La commande renvoie le résultat suivant :

Layers:
  sa:ObstEcoul

On peut donc interroger le serveur pour connaître la structure de cette couche :

ogr_fdw_info -s "WFS:http://services.sandre.eaufrance.fr/geo/obs_FXX" -l sa:ObstEcoul

Création du serveur distant

La commande précédente génère automatiquement la requête SQL (à exécuter dans pg Admin, par exemple) pour créer le serveur :

CREATE SERVER myserver
  FOREIGN DATA WRAPPER ogr_fdw
  OPTIONS (
    datasource 'WFS:http://services.sandre.eaufrance.fr/geo/obs_FXX',
    format 'WFS' );

Création de la table distante

On peut ensuite créer la table distante :

CREATE FOREIGN TABLE sa_obstecoul (
  fid bigint,
  msgeometry Geometry(Geometry,4326),
  gml_id varchar,
  cdobstecoul varchar,
  stobstecoul varchar,
  cdmodevalidobstecoul varchar,
  lbmodevalidobstecoul varchar,
  cdetouvrage varchar,
  lbetouvrage varchar,
  nomprincipalobstecoul varchar,
  nomsecondaireobstecoul varchar,
  cdtypeouvrage varchar,
  lbtypeouvrage varchar,
  coordxpointcarouvrage real,
  coordypointcarouvrage real,
  typecoordpointcarouvrage varchar,
  cdtypedispfranchpiscicole1 varchar,
  lbtypedispfranchpiscicole1 varchar,
  cdtypedispfranchpiscicole2 varchar,
  lbtypedispfranchpiscicole2 varchar,
  cdtypedispfranchpiscicole3 varchar,
  lbtypedispfranchpiscicole3 varchar,
  cdtypedispfranchpiscicole4 varchar,
  lbtypedispfranchpiscicole4 varchar,
  cdtypedispfranchpiscicole5 varchar,
  lbtypedispfranchpiscicole5 varchar,
  cdtypeelmobseuil1 varchar,
  lbtypeelmobseuil1 varchar,
  cdtypeelmobseuil2 varchar,
  lbtypeelmobseuil2 varchar,
  cdtypeelmobseuil3 varchar,
  lbtypeelmobseuil3 varchar,
  cdtypedispfranchnavig1 varchar,
  lbtypedispfranchnavig1 varchar,
  cdtypedispfranchnavig2 varchar,
  lbtypedispfranchnavig2 varchar,
  cdtypedispfranchnavig3 varchar,
  lbtypedispfranchnavig3 varchar,
  cdusageobstecoul1 varchar,
  lbusageobstecoul1 varchar,
  cdusageobstecoul2 varchar,
  lbusageobstecoul2 varchar,
  cdusageobstecoul3 varchar,
  lbusageobstecoul3 varchar,
  cdusageobstecoul4 varchar,
  lbusageobstecoul4 varchar,
  hautmaxter varchar,
  hautchutetobstecoul varchar,
  cdhautchutclobstecoul varchar,
  lbhautchutclobstecoul varchar,
  datemajobstecoul varchar,
  datevalidobstecoul varchar,
  grenobstecoul varchar,
  ouvragelie varchar,
  idtronconhydrograelt varchar,
  nomentitehydrographique varchar,
  cdtronconhydrographique varchar,
  cdentitehydrographique varchar,
  cdzonehydro varchar,
  idtronconhydrobdtopo varchar,
  cddepartement varchar,
  lbdepartement varchar,
  cdcommune varchar,
  lbcommune varchar,
  numcircadminbassin varchar,
  nomcircadminbassin varchar,
  cdeumassedeau varchar,
  altipointcarouvrage real,
  nomlimitehydrographique varchar,
  denmaxouvrage real,
  pkobstecoul real
) SERVER myserver
OPTIONS (layer 'sa:ObstEcoul');

Si le serveur et la table ne sont pas visibles dans pgAdmin, il faut vérifier dans Fichier > Préférences > Affichage que les cases pour les options « Wrappers de serveurs distants » et « Tables distantes » sont bien cochées.

Sources :

Ajouter un champs UUID comme clé primaire avec PostGreSQL

L’objectif est d’ajouter un champs UUID (Universally Unique IDentifier = Identifiant Universel Unique) comme clé primaire dans une table.

Il est tout d’abord nécessaire d’activer l’extension pgcrypto :

CREATE EXTENSION pgcrypto;

On peut dorénavant utiliser la fonction gen_random_uuid() pour créer un UUID :

SELECT gen_random_uuid(); 

Et l’intégrer comme type de champs pour un identifiant dans une table :

CREATE SCHEMA IF NOT EXISTS annuaire;
CREATE TABLE annuaire.contacts(
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT,
email TEXT
);

Sources

Utiliser pgBadger avec Windows

Objectif

Mettre en place une instance de pgBadger 9.1 sur un poste de travail équipé de Windows 7 SP1 avec PostGreSQL 9.6.

Installation

Le seul pré-requis est l’existence d’un environnement Perl sur la machine.

Ou peut utiliser :

Après avoir installé l’un de ces environnements, on télécharge puis on dézippe la dernière version de pgBadger (dans cet exemple la version 9.1) à l’adresse suivante : https://github.com/dalibo/pgbadger

Pour installer pgBadger, il suffit de double-cliquer sur le fichier Perl Makefile.PL.

Paramétrage des logs via postgresql.conf

Pour que les logs soient effectifs, il faut en premier lieu activer le paramètre suivant :

logging_collector = on

La documentation de pgBadger détaille ensuite tous les paramètres à modifier (en décommentant les lignes pour que les changements soient pris en compte) :

log_min_duration_statement = 0
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0 
log_error_verbosity = default
lc_messages='C

Attention : les fichiers de log générés avec ce paramétrage peuvent être volumineux malgré leur rotation. Il faut donc surveiller l’espace disque disponible et éventuellement modifier le répertoire de sortie :

log_directory = 'E:/LOGS/pg_log'

On souhaite utiliser le mode incrémental de pgBadger pour générer un rapport quotidien ainsi qu’un rapport hebdomadaire.

Il faut donc modifier également les paramètres suivants pour le nommage des fichiers de log et leur rotation :

log_filename = 'postgresql-%w.log'
log_file_mode = 0640
log_truncate_on_rotation = on
log_rotation_age = 1d

Une fois les changements sauvegardés, on redémarre le service postgresql-x64-9.6 pour que le serveur puisse les prendre en compte.

Activation de pgBadger

En ligne de commande, on se positionne dans le répertoire de pgBadger :

cd « C:\Program Files\PostgreSQL\pgbadger-9.1 »

Puis on exécute la commande pour chacun des fichiers logs générés :

perl.exe pgbadger E:/LOGS/pg_log/postgresql-1.log -I -O C:/xampp/htdocs/pgbagder

Les options suivantes sont utilisées :

  • -I pour initier le mode incrémental,
  • -O pour spécifier le répertoire de sortie (obligatoire pour le mode incrémental).

Ici, on choisit comme destination le répertoire htdocs de XAMPP afin de pouvoir diffuser les résultats via un Intranet local.

Attention : on utilise le caractère slash plutôt que l’antislash dans les chemins d’accès au logs et au répertoire de destination puisque que l’antislash associé à lettre « p » (\p ou \P), que l’on peut retrouver dans cet exemple, correspond à une expression régulière Perl qui met le script en échec !

Automatisation de la génération des rapports

On compile dans un fichier .cmd qui sera exécuté quotidiennement l’ensemble des commandes pgbadger :

REM positionnement dans le répertoire pgbadger
c:
cd « C:\Program Files\PostgreSQL\pgbadger-9.1 »

REM exécution de la commande via perl pour la génération des rapports journaliers et du rapport hebdomadaire
perl.exe pgbadger E:\LOGS\_pg_log\_postgresql-1.log -I -O C:\xampp\htdocs\pgbadger
perl.exe pgbadger E:\LOGS\_pg_log\_postgresql-2.log -I -O C:\xampp\htdocs\pgbadger
perl.exe pgbadger E:\LOGS\_pg_log\_postgresql-3.log -I -O C:\xampp\htdocs\pgbadger
perl.exe pgbadger E:\LOGS\_pg_log\_postgresql-4.log -I -O C:\xampp\htdocs\pgbadger
perl.exe pgbadger E:\LOGS\_pg_log\_postgresql-5.log -I -O C:\xampp\htdocs\pgbadger
perl.exe pgbadger E:\LOGS\_pg_log\_postgresql-6.log -I -O C:\xampp\htdocs\pgbadger
perl.exe pgbadger E:\LOGS\_pg_log\_postgresql-7.log -I -O C:\xampp\htdocs\pgbadger

On utilise ensuite le planificateur de tâches Windows pour exécuter ce fichier tous les jours.

Résultat

Le script génère dans le répertoire pgbadger les fichiers et répertoire suivants :

pgbadger_result

Le répertoire 2017 contient des sous-répertoires par mois, semaine et jour stockant les données tirées des logs.

Le fichier LAST_PARSED indique la date et l’heure de la dernière ligne parcourue dans le log.

Le fichier index.html renvoie sur un agenda permettant de consulter les différentes statistiques par jour et par semaine :

pgbadger_year

pgbadger_day

 

 

Sources

 

 

Résoudre le problème de démarrage du service PostGreSQL sous Windows

Si le service Windows n’est pas arrêté proprement, il peut – dans certains cas – ne pas se relancer même avec un redémarrage du serveur.

Pour résoudre ce problème, il suffit parfois de vider  le cache du WAL (Write Ahead Log) :

  • dans un premier temps, sauvegarder le dossier data
  • puis exécuter la commande suivante dans une fenêtre DOS :

cd C:\Program Files\PostgresSQL\9.6\bin\

pg_resetxlog.exe -f « C:\Program Files\PostgresSQL\9.6\data »

Sources :

How to démarrage des services PostGreSQL sous Windows :

Modifier le propriétaire ou les droits sur les schémas d’une base avec PgAdmin

Pour modifier le propriétaire de tous les objets d’une base en deux étapes, on utilisera les requêtes suivantes qui vont générer automatiquement, pour chaque objet, les requêtes SELECT nécessaires qu’il suffira ensuite d’exécuter à la chaîne dans une nouvelle fenêtre SQL (après avoir supprimé les doubles quotes).

1. Modifier le propriétaire d’un objet

1.1 Tables

SELECT ‘ALTER TABLE ‘|| schemaname || ‘.’ || tablename ||’ OWNER TO my_new_owner;’
FROM pg_tables WHERE NOT schemaname IN (‘pg_catalog’, ‘information_schema’)
ORDER BY schemaname, tablename;

1.2 Séquences

SELECT ‘ALTER SEQUENCE ‘|| sequence_schema || ‘.’ || sequence_name ||’ OWNER TO my_new_owner;’
FROM information_schema.sequences WHERE NOT sequence_schema IN (‘pg_catalog’, ‘information_schema’)
ORDER BY sequence_schema, sequence_name;

1.3 Vues

SELECT ‘ALTER VIEW ‘|| table_schema || ‘.’ || table_name ||’ OWNER TO my_new_owner;’
FROM information_schema.views WHERE NOT table_schema IN (‘pg_catalog’, ‘information_schema’)
ORDER BY table_schema, table_name;

1.4 Vues matérialisées

SELECT ‘ALTER TABLE ‘|| oid::regclass::text ||’ OWNER TO my_new_owner;’
FROM pg_class WHERE relkind = ‘m’
ORDER BY oid;

2. Attribuer ou révoquer des droits

2.1 Sur des tables

2.1.1 GRANT

SELECT ‘GRANT SELECT ON ‘|| table_schema || ‘.’ || table_name ||’ TO group_role;’
FROM information_schema.tables WHERE NOT table_schema IN (‘pg_catalog’, ‘information_schema’)
ORDER BY table_schema, table_name;

2.1.2 REVOKE

SELECT ‘REVOKE SELECT ON ‘|| table_schema || ‘.’ || table_name ||’ FROM group_role;’
FROM information_schema.tables WHERE NOT table_schema IN (‘pg_catalog’, ‘information_schema’)
ORDER BY table_schema, table_name;

2.2 Sur des schémas

2.2.1 GRANT

SELECT ‘GRANT USAGE ON SCHEMA ‘|| schema_name ||’ TO group_role;’
FROM information_schema.schemata
ORDER BY schema_name;

2.2.2 REVOKE

SELECT ‘REVOKE ALL ON SCHEMA ‘|| schema_name ||’ FROM dreal;’
FROM information_schema.schemata
ORDER BY schema_name;

Sources :

Faire des requêtes entre bases avec PostGreSQL

Deux extensions de PostGreSQL permettent d’effectuer des requêtes entre bases de données, fonctionnalité qui n’est pas présente nativement dans le SGBD, il s’agit de dblink et de Foreign Data Wrapper (FDW).

Installation des extensions

CREATE EXTENSION dblink;
CREATE EXTENSION postgres_fdw;

Utilisation de dblink

ToDo

Utilisation de Foreign Data Wrapper (FDW)

ToDo

Sources :

Exploser un champs en lignes selon un délimitateur avec PostGreSQL

On utilisera la fonction unnest() qui permet normalement de récupérer les valeurs d’un array. Si le champs contient des chaînes de caractères, on combinera avec la fonction string_to_array() :

SELECT unnest(table_name.field_name) AS alias
FROM schema_name.table_name ;

Sources :