Postgresql – Comment accélérer un GROUP BY

Postgresql - Comment accélérer un GROUP BY 1

En SQL, un GROUP BY regroupe les enregistrements pour les présenter de manière plus synthétique et agrégée. Bien que très pratique, cette fonction peut s’avérer pénalisante en termes de performances.

Hans-Jürgen Schönig, expert Postgresql, propose sur le site cybertec une petite astuce pour accélérer les traitements avec utilisation de la clause GROUP BY.

https://www.cybertec-postgresql.com/en/speeding-up-group-by-in-postgresql/

Répliquer une base de données Postgresql en local

Répliquer une base de données Postgresql en local 2

Quand on a peur de se louper avant de manipuler violemment une base de données, il est utile de la sauvegarder. Avec postgresql, les commandes les plus couramment utilisées sont pg_dump et pg_restore.

L’inconvénient est que cette manipulation est chiante et très longue avec une base de taille conséquente.

Il est alors préférable de préférer utiliser la réplication qui s’opère en deux lignes avec l’avantage énorme d’être ultra rapide.

Pour répliquer une base de données postgresql, il faut d’abord tuer les connexions existantes sur la base que l’on souhaite dupliquer. Pour cela, il est indispensable de connaître le mot de passe du user postgres. Pas de panique, si vous l’avez oublié, il vous est possible de récupérer ce mot de passe super-utilisateur postgres très facilement en suivant ce tuto.

C’est fait ? Alors on continue.

Tuer les connexions Postgresql en cours

Première étape : Se connecter à postgresql en ligne de commande avec psql

user@pc:~$ psql -h localhost -U postgres
psql (9.5.10, server 9.3.15)
Type "help" for help.

postgres=#

Deuxième étape : tuer les connexions en cours sur la base que l’on veut répliquer

La base à traiter s’appelle restits

postgres=# select pg_terminate_backend(pid) from pg_stat_activity where pid <> pg_backend_pid() and datname = 'restits';
 pg_terminate_backend
----------------------
 t
(1 row)

C’est fait ! On a tué notre utilisateur (1 row). Passons à la suite.

Répliquer une base postgresql

A partir d’ici, c’est du velours vu que ça se joue en une ligne (assurez vous préalablement d’avoir l’espace disque suffisant sur votre machine).

postgres=# create database shhh template restits;
CREATE DATABASE
postgres=#

Et voilà. Pour vous donner une idée de la chose, il a fallu 3mn10 secondes pour répliquer 24Go. Cette commande a créé la base shhh à l’identique de restits en utilisant comme template la base que l’on souhaitait dupliquer.

Cette opération est rendue possible parce que postgresql crée toujours une base à partir d’une template ; la template standard utilisée par CREATE DATABASE est template1 (voir la doc).

 

 

Comment réinitialiser le mot de passe super-utilisateur PostgreSQL

Comment réinitialiser le mot de passe super-utilisateur PostgreSQL 3

Avec Postgresql, il y a deux options pour retrouver le mot de passe super-utilisateur postgres qu’on a lamentablement paumé.

L’option 1 et l’option 2 🙂

La première option consiste, sous windows, à aller fouiller le fichier pgpass.conf généré lorsqu’on utilise pgAdmin. Il faut « afficher les dossiers et fichiers cachés« , puis se rendre dans Utilisateur > NOMUTILISATEUR > AppData > Roaming > postgresql.

A cet endroit se trouvent 2 fichiers, dont pgpass.conf qui contient les mots de passe de connexion postgreSQL enregistrés en clair ! (sous Linux, tout est stocké dans le fichier pgpass. Un find vous aidera à le trouver)

Comment réinitialiser le mot de passe super-utilisateur PostgreSQL 4

Il n’y a alors plus qu’à se servir et se connecter.

La seconde option nécessite un accès au serveur. Il faut que vous éditiez le fichier pg_hba.conf qui se trouve en principe dans /etc/postgresql/9.3/main.

Dans ce fichier, vous devez changer

local all postgres peer

en

local all postgres trust

Sauvegardez, et relancez postgreSQL.

Vous pouvez dès lors vous connecter avec le compte postgres, puis modifier le mot de passe :

psql postgres postgres

postgres=# alter user postgres with password 'postgres';

Pour finir, vous pouvez retourner sur votre fichier pg_hba.conf et le remettre dans son état initial avant de redémarrer le serveur.

 

Postgresql – Sélectionner les champs d’une colonne commençant par un chiffre

Postgresql - Sélectionner les champs d'une colonne commençant par un chiffre 5

Aide mémoire

La table adresse présentée ci-dessous est à remettre en forme en remettant les valeurs dans les bons champs. Un bon nombre de valeurs de libellevoie sont mal formées et nécessitent préalablement d’identifier les champs commençant par un nombre afin de reventiler les données.

Postgresql - Sélectionner les champs d'une colonne commençant par un chiffre 6

Pour les sélectionner, la requête est :

select * from origine 
where left(libellevoie, 1) ~ '^[0-9]*$'

Cette requête est utilisable ensuite dans un script d’update de la table où les valeurs sont réaffectées dans numvoie, puis dans typevoie en une passe.