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

Publicités

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

Source (Linux) :

 

 

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
  • supprimer le fichier postmaster.pid dans 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  ..\data

Puis redémarrer le service Windows.

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 :

Correction automatique des erreurs de géométrie avec PostGIS

Ces opérations sont réalisées pour des tables comportant un champs de type géométrie, stockées dans un SGBD PostGreSQL avec l’extension PostGIS installée.

1. Comptage des erreurs GEOS, des géométries nulles et des collections

SELECT 'invalid' AS nb, count(*) FROM my_table WHERE NOT ST_IsValid(the_geom)
UNION
SELECT 'geonul' AS nb, count(*) FROM my_table WHERE the_geom is null
UNION
SELECT 'collection' AS nb, count(*) FROM ma_table WHERE not ST_IsValid(the_geom)
AND ST_GeometryType(ST_MakeValid(the_geom))='ST_GeometryCollection';

Afficher les causes des erreurs

SELECT id, ST_IsValidReason(the_geom) FROM my_table WHERE NOT ST_IsValid(the_geom);

2. Correction des géométries invalides, des collections et des nœuds doubles (sur des couches de polygones)

2.1 Requête globale

UPDATE my_table
SET the_geom =
ST_Multi(ST_Simplify(ST_Multi(ST_CollectionExtract(ST_ForceCollection(ST_MakeVa
lid(the_geom)),3)),0))
WHERE ST_GeometryType(the_geom) = 'ST_MultiPolygon';

2.2 Correction seule de la géométrie de type GEOS

UPDATE my_table SET the_geom = ST_MakeValid(the_geom) WHERE NOT ST_IsValid(the_geom);

2.3 Suppression seule des géométries nulles

DELETE FROM my_table WHERE the_geom IS NULL;

A n’effectuer que si le nombre n’est pas nul à l’étape 1.

2.4 Correction seule des nœuds doubles

UPDATE my_table SET the_geom = ST_Multi(ST_Simplify(the_geom,0));

A n’effectuer que si la liste des erreurs indique la présence de nœuds double et que l’étape 2 n’est pas réalisée.

2.5 Vérifier de nouveau le nombre d’erreurs de type GEOS

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 :