web-development-kb-eu.site

Force Drop DB, während andere verbunden sein können

Ich muss eine Datenbank aus einem PostgreSQL DB-Cluster entfernen. Wie kann ich das tun, auch wenn aktive Verbindungen bestehen? Ich brauche eine Art -force Flag, das alle Verbindungen und dann die DB trennt.

Wie kann ich es implementieren?

Ich verwende derzeit dropdb, aber andere Tools sind möglich.

postgresqlmaintenance
126
Alex

In PostgreSQL* *können Sie eine Datenbank nicht löschen, während Clients mit ihr verbunden sind.

Zumindest nicht mit dem Dienstprogramm dropdb - das ist nur ein einfacher Wrapper um DROP DATABASE Serverabfrage.

Es folgt eine ziemlich robuste Problemumgehung:

Stellen Sie mit psql oder einem anderen Client eine Verbindung zu Ihrem Server als Superuser her. Verwenden Sie nicht die Datenbank, die Sie löschen möchten.

psql -h localhost postgres postgres

Mit dem einfachen Datenbank-Client können Sie das Löschen der Datenbank in drei einfachen Schritten erzwingen:

  1. Stellen Sie sicher, dass niemand eine Verbindung zu dieser Datenbank herstellen kann. Sie können eine der folgenden Methoden verwenden (die zweite scheint sicherer zu sein, verhindert jedoch nicht die Verbindung von Superusern).

    /* Method 1: update system catalog */
UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'mydb';

/* Method 2: use ALTER DATABASE. Superusers still can connect!
ALTER DATABASE mydb CONNECTION LIMIT 0; */

  2. Erzwingen Sie die Trennung aller mit dieser Datenbank verbundenen Clients mithilfe von pg_terminate_backend .

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

/* For old versions of PostgreSQL (up to 9.1), change pid to procpid:

SELECT pg_terminate_backend(procpid)
FROM pg_stat_activity
WHERE datname = 'mydb'; */

  3. Löschen Sie die Datenbank.

    DROP DATABASE mydb;

Schritt 1 erfordert Superuser-Berechtigungen für die 1. Methode und Datenbankbesitzer-Berechtigungen für den 2.. Schritt 2 erfordert Superuser-Berechtigungen . Schritt 3 erfordert die Berechtigung des Datenbankbesitzers .

* * Dies gilt für alle Versionen von PostgreSQL bis Version 12. Version 13 hat DROP DATABASE mydb FORCE

178
filiprem

Verwenden Sie die Antwort von @ filiprem in einem meiner Fälle und vereinfachen Sie sie:

-- Connecting to the current user localhost's postgres instance
psql

-- Making sure the database exists
SELECT * from pg_database where datname = 'my_database_name'

-- Disallow new connections
UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'my_database_name';
ALTER DATABASE my_database_name CONNECTION LIMIT 1;

-- Terminate existing connections
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'my_database_name';

-- Drop database
DROP DATABASE my_database_name
9
Dorian

Es gibt ist eine Möglichkeit, dies mit den Shell-Dienstprogrammen dropdb & pg_ctl (oder pg_ctlcluster in Debian und Derivaten). Aber @ filiprems Methode ist aus mehreren Gründen überlegen :

  • Es werden nur Benutzer von der betreffenden Datenbank getrennt.
  • Es muss nicht der gesamte Cluster neu gestartet werden.
  • Es verhindert sofortige Wiederverbindungen und beeinträchtigt möglicherweise den Befehl dropdb.

Ich zitiere man pg_ctlcluster:

Mit dem --force Option wird der "schnelle" Modus verwendet, der alle aktiven Transaktionen zurücksetzt, Clients sofort trennt und somit sauber herunterfährt. Wenn dies nicht funktioniert, wird das Herunterfahren im "sofortigen" Modus erneut versucht. Dies kann dazu führen, dass der Cluster in einem inkonsistenten Zustand bleibt und somit beim nächsten Start zu einem Wiederherstellungslauf führt. Wenn dies immer noch nicht hilft, wird der Postmaster-Prozess abgebrochen. Beendet mit 0 bei Erfolg, mit 2, wenn der Server nicht ausgeführt wird, und mit 1 bei anderen Fehlerbedingungen. Dieser Modus sollte nur verwendet werden, wenn die Maschine kurz vor dem Herunterfahren steht.

pg_ctlcluster 9.1 main restart --force

oder

pg_ctl restart -D datadir -m fast

oder

pg_ctl restart -D datadir -m immediate

unmittelbar gefolgt von:

dropdb mydb

Möglicherweise in einem Skript zur sofortigen Nachfolge.

7
Erwin Brandstetter

Wenn Sie sich in einem RDS befinden, in dem Verbindungen ohne ausgewählte Datenbank Sie in die Datenbank einfügen, die Sie standardmäßig erstellen möchten, können Sie diese Variante ausführen, um zu umgehen, dass Sie die letzte offene Verbindung sind.

 DROP DATABASE IF EXISTS temporary_db_that_shouldnt_exist; 

 CREATE DATABASE temporary_db_that_shouldnt_exist with OWNER your_user; 

 \connect temporary_db_that_shouldnt_exist 
 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'the_db_you_want_removed'; 


 DROP DATABASE IF EXISTS the_db_you_want_removed; 
 -- 
 -- Name: the_db_you_want_removed; Type: DATABASE; Schema: -; Owner: your_user 
 -- 

 CREATE DATABASE savings_champion WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8'; 


 ALTER DATABASE the_db_you_want_removed OWNER TO your_user; 

 \connect the_db_you_want_removed 

 DROP DATABASE IF EXISTS temporary_db_that_shouldnt_exist;
0
Jharwood