Postgresql - Impossible de supprimer la base de données en raison de certaines connexions automatiques à la base de données.

Question

Chaque fois que j'essaie d'abandonner la base de données, j'obtiens :

ERROR:  database "pilot" is being accessed by other users
DETAIL:  There is 1 other session using the database.

Quand je l'utilise :

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB';

J'ai mis fin à la connexion de cette base de données, mais si j'essaie de supprimer la base de données après cela, quelqu'un se connecte automatiquement à cette base de données et donne cette erreur. Qu'est-ce qui peut provoquer cela ? Personne n'utilise cette base de données, sauf moi.

Answer 1

Sous macOS, essayez de redémarrer la base de données postgresql via la console en utilisant la commande :

brew services restart postgresql

Answer 2

REVOKE CONNECT n'empêchera pas les connexions du propriétaire de la base de données ou du superutilisateur. Donc si vous voulez que personne ne se connecte à la base de données, la commande suivante peut être utile.

alter database pilot allow_connections = off;

Alors utilisez :

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'pilot';

Answer 3

Dans mon cas, j'utilise AWS Redshift (basé sur Postgres). Et il semble qu'il n'y ait pas d'autres connexions à la base de données, mais je reçois cette même erreur.

ERROR:  database "XYZ" is being accessed by other users

Dans mon cas, il semble que le cluster de base de données effectue toujours un traitement sur la base de données, et bien qu'il n'y ait pas d'autres connexions externes/utilisateurs, la base de données est toujours utilisée en interne. J'ai découvert cela en exécutant ce qui suit :

SELECT * FROM stv_sessions;

Mon astuce a donc consisté à écrire une boucle dans mon code, à la recherche de lignes contenant le nom de ma base de données. (bien sûr la boucle n'est pas infinie, et est une boucle de sommeil, etc)

SELECT * FROM stv_sessions where db_name = 'XYZ';

Si des lignes sont trouvées, supprimez chaque PID, un par un.

SELECT pg_terminate_backend(PUT_PID_HERE);

Si aucune ligne n'a été trouvée, il faut abandonner la base de données.

DROP DATABASE XYZ;

Note : Dans mon cas, j'écris des tests unitaires/systèmes Java, où cela pourrait être considéré comme acceptable. Ce n'est pas acceptable pour un code de production.

---

Voici le hack complet, en Java (ignorez mes classes de test/utilité).

  int i = 0;
  while (i < 10) {
    try {
      i++;
      logStandardOut("First try to delete session PIDs, before dropping the DB");
      String getSessionPIDs = String.format("SELECT stv_sessions.process, stv_sessions.* FROM stv_sessions where db_name = '%s'", dbNameToReset);
      ResultSet resultSet = databaseConnection.execQuery(getSessionPIDs);
      while (resultSet.next()) {
        int sessionPID = resultSet.getInt(1);
        logStandardOut("killPID: %s", sessionPID);
        String killSessionPID = String.format("select pg_terminate_backend(%s)", sessionPID);
        try {
          databaseConnection.execQuery(killSessionPID);
        } catch (DatabaseException dbEx) {
          //This is most commonly when a session PID is transient, where it ended between my query and kill lines
          logStandardOut("Ignore it, you did your best: %s, %s", dbEx.getMessage(), dbEx.getCause());
        }
      }

      //Drop the DB now
      String dropDbSQL = String.format("DROP DATABASE %s", dbNameToReset);
      logStandardOut(dropDbSQL);
      databaseConnection.execStatement(dropDbSQL);
      break;
    } catch (MissingDatabaseException ex) {
      //ignore, if the DB was not there (to be dropped)
      logStandardOut(ex.getMessage());
      break;
    } catch (Exception ex) {
      logStandardOut("Something went wrong, sleeping for a bit: %s, %s", ex.getMessage(), ex.getCause());
      sleepMilliSec(1000);
    }
  }

Answer 4

A mon avis, il y a des requêtes inutiles qui tournent dans le backgroud.

1. Essayez d'abord de montrer les requêtes en cours

SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

2. kill idle query ( Vérifiez s'ils font référence à la base de données en question ou vous pouvez les tuer tous ou tuer un spécifique en utilisant le pid des résultats de la sélection)

SELECT pg_terminate_backend(procpid) ;

Note : La suppression d'une requête de sélection n'a pas d'impact négatif.

Answer 5

C'est ce qui a fonctionné pour nous sur postgres 12. Nous utilisons pgadmin, pgbouncer, et plusieurs applications clientes.

REVOKE CONNECT ON DATABASE <mydbname> FROM public;
ALTER DATABASE <mydbname> allow_connections = off;
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '<mydbname>';
DROP DATABASE <mydbname>;