PostgreSQL: fermer toutes les connexions sur une base de données

PostgreSQL dispose d'une table d'état très utile appelée pg_stat_activity. Cette table est similaire au "SHOW PROCESSLIST" qu'on retrouve en MySQL, mais a le net avantage d'être requêtable et dispose d'informations plus précises que MySQL.

En voici un extrait:

 12407 | postgres | 51572 | 10 | postgres | | 127.0.0.1 | | 44244 | 2017-07-26 07:41:44.454929+00 | | 2017-07-26 12:48:44.474222+00 | 2017-07-26 12:48:44.474335+00 | | | idle | | | SELECT CASE WHEN pg_is_in_recovery = 'false' THEN 0 ELSE COALESCE(ROUND(EXTRACT(epoch FROM now() - pg_last_xact_replay_timestamp())), 0) END AS seconds FROM pg_is_in_recovery()<br> 349593 | db02 | 51573 | 10 | postgres | | 127.0.0.1 | | 44245 | 2017-07-26 07:41:46.305319+00 | | 2017-07-26 12:48:46.336722+00 | 2017-07-26 12:48:46.33685+00 | | | idle | | | SELECT xact_commit,xact_rollback FROM pg_stat_database WHERE datname=$1;<br> 24816 | db01 | 51575 | 10 | postgres | | 127.0.0.1 | | 44246 | 2017-07-26 07:41:46.588503+00 |

Dans certains cas, il peut être utile de couper toutes les connexions à une base de données précise (par exemple des connexions dormantes en masse). Voici une simple requête SQL à jouer sur votre PostgreSQL (9.2 et plus) permettant de couper toutes les connexions:

SELECT
pg_terminate_backend(pid) FROM
pg_stat_activity
WHERE pid <> pg_backend_pid() AND datname = 'target_database';

Cette requête va lancer un ordre de fermeture sur toutes les connexions de la base target_database excepté la connexion en cours.

Si vous souhaitez uniquement tuer les requêtes inactives vous pouvez utiliser la variante suivante:

SELECT
pg_terminate_backend(pid) FROM
pg_stat_activity
WHERE pid <> pg_backend_pid() AND datname = 'target_database' AND state = 'idle';

PostgreSQL: changer le owner de toutes les tables/séquences d’un schéma

Lors d’une restauration de base de données, parfois il se peut que vous ayez besoin de changer le propriétaire d’une table ou d’une séquence pour un autre user, par exemple si vous prenez une base de production pour la mettre sur votre intégration pour vos développeurs (anonymisées, bien sûr 😉 ).

Plutôt que de devoir faire fastidieusement un ALTER TABLE table par table, voici 2 requêtes SQL qui vont vous permettre de générer les SQL pour changer rapidement le owner de toutes les tables et séquences: Continuer à lire

Benchmark comparatif: PostGreSQL 9.1

Suite à une volonté de tester l’arbre de dports de DragonFlyBSD, et voulant voir ce que vallaient les performances de PostGreSQL sur DragonFlyBSD j’ai décidé de monter un rapide benchmark de performances afin de comparer plusieurs systèmes ayant une version de PostGreSQL commune. J’ai choisi pour ces raisons PostGreSQL 9.1.

Lors de mon benchmark sous Linux (Debian), je me suis retrouvé confronté à des statistiques étonnantes en terme de performances, de ce fait, j’ai décidé de voir ce que vallait une Redhat, j’ai donc pris Centos 6.4.

Les tests ont été effectués sur les plateformes suivantes:

  • DragonFlyBSD 3.4.1 (système de fichiers Hammer)
  • FreeBSD 9.1-p3 (système de fichiers UFS2+J)
  • FreeBSD 9.1-p3 (système de fichiers ZFS v28)
  • Debian 7: Wheezy (système de fichiers ext4)
  • Centos 6.4 (système de fichiers ext4)

Continuer à lire