Wie kann man eine PostgreSQL-Datenbank löschen, wenn es aktive Verbindungen zu ihr gibt?

Question

Ich muss ein Skript schreiben, das eine PostgreSQL-Datenbank löschen soll. Es kann viele Verbindungen zu ihr geben, aber das Skript sollte das ignorieren.

Die Norm DROP DATABASE db_name Die Abfrage funktioniert nicht, wenn es offene Verbindungen gibt.

Wie kann ich das Problem lösen?

Answer 1

Dadurch werden alle bestehenden Verbindungen außer der Ihren gelöscht:

Abfrage pg_stat_activity und erhalten Sie die Pid-Werte, die Sie töten wollen, dann geben Sie SELECT pg_terminate_backend(pid int) zu ihnen.

PostgreSQL 9.2 und höher:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' --  change this to your DB
  AND pid <> pg_backend_pid();

PostgreSQL 9.1 und niedriger:

SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' --  change this to your DB
  AND procpid <> pg_backend_pid();

Sobald Sie alle Verbindungen getrennt haben, müssen Sie die Verbindung trennen und den Befehl DROP DATABASE von einer Verbindung zu einer anderen Datenbank aus eingeben, die nicht diejenige ist, die Sie löschen wollen.

Beachten Sie die Umbenennung der procpid Spalte zu pid . Siehe dieses Thema der Mailingliste .

Answer 2

In PostgreSQL 9.2 und höher, um alles außer Ihrer Sitzung von der Datenbank, mit der Sie verbunden sind, zu trennen:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid();

In älteren Versionen ist es dasselbe, ändern Sie einfach pid zu procpid . Um die Verbindung zu einer anderen Datenbank zu trennen, ändern Sie einfach current_database() auf den Namen der Datenbank, von der Sie die Verbindung zu den Benutzern trennen möchten.

Sie möchten vielleicht REVOKE el CONNECT von den Nutzern der Datenbank, bevor Sie die Verbindung trennen. Andernfalls werden sich die Nutzer immer wieder neu verbinden und Sie werden nie die Möglichkeit haben, die DB zu löschen. Siehe dieser Kommentar und die Frage, mit der sie verbunden ist, Wie kann ich alle anderen Benutzer von der Datenbank trennen? .

Wenn Sie nur untätige Benutzer trennen wollen, siehe diese Frage .

Answer 3

PostgreSQL 13 eingeführt FORCE opción.

DROP DATABASE

DROP DATABASE lässt eine Datenbank fallen ... Auch, wenn jemand anderes mit der Zieldatenbank verbunden ist, wird dieser Befehl fehlschlagen, es sei denn, Sie verwenden die FORCE Option, die weiter unten beschrieben wird.

FORCE

Versucht, alle bestehenden Verbindungen zur Zieldatenbank zu beenden. Es wird nicht beendet, wenn vorbereitete Transaktionen, aktive logische Replikations-Slots oder Abonnements in der Zieldatenbank vorhanden sind.

DROP DATABASE db_name WITH (FORCE);

Answer 4

Kinderleicht.

Ich starte den Dienst in Ubuntu einfach neu, um die Verbindung zu den Clients zu trennen.

sudo service postgresql stop
sudo service postgresql start

psql
DROP DATABASE DB_NAME;

Answer 5

Sie können alle Verbindungen vor dem Beenden der Datenbank mit dem Befehl pg_terminate_backend(int) Funktion.

Sie können alle laufenden Backends über die Systemansicht abrufen pg_stat_activity

Ich bin mir nicht ganz sicher, aber das Folgende würde wahrscheinlich alle Sitzungen beenden:

select pg_terminate_backend(procpid)
from pg_stat_activity
where datname = 'doomed_database'

Natürlich dürfen Sie selbst nicht mit dieser Datenbank verbunden sein