Nie mogę usunąć bazy - database is being accessed by other users

PostgreSQL

Nie mogę usunąć bazy - database is being accessed by other users

W skrócie

To klasyczny błąd, który zatrzymuje niejednego administratora przy porządkach. Chcesz usunąć niepotrzebną bazę - testową, po nieudanej migracji, po kliencie - a PostgreSQL uparcie odmawia. Komunikat jest jednoznaczny: ktoś jest podłączony. Problem w tym, że tym "kimś" bywasz Ty sam, a czasem porzucona sesja aplikacji albo otwarte narzędzie graficzne. Pokażemy, jak namierzyć podłączone sesje, jak je bezpiecznie rozłączyć i jak w nowszym PostgreSQL usunąć bazę jednym poleceniem mimo aktywnych połączeń.

Jak to wygląda w praktyce

Wpisujesz DROP DATABASE stara_testowa; i zamiast potwierdzenia dostajesz błąd "database stara_testowa is being accessed by other users, detail: there is 1 other session using the database". Liczba sesji potrafi rosnąć, gdy działa aplikacja z pulą połączeń, która natychmiast otwiera je z powrotem. Bywa mylące, gdy jesteś pewien, że nikt nie pracuje - a i tak nie idzie, bo jesteś zalogowany właśnie do tej bazy, którą chcesz skasować. Podobnie działa otwarte okno klienta graficznego, które trzyma sesję w tle. W efekcie polecenie wraca z błędem raz za razem, mimo że baza wygląda na nieużywaną.

Dlaczego tak się dzieje

Usunięcie bazy to operacja, która fizycznie kasuje jej pliki. PostgreSQL nie może tego zrobić, dopóki jakikolwiek proces obsługuje połączenie do tej bazy - inaczej wyrywałby ziemię spod nóg działającej sesji. Dlatego DROP DATABASE wymaga, żeby liczba aktywnych sesji do bazy wynosiła zero. Kluczowe i często pomijane jest to, że własna sesja też się liczy: nie da się usunąć bazy, będąc do niej zalogowanym, bo trzymasz do niej połączenie. Drugi typowy sprawca to aplikacja z pulą połączeń - nawet jeśli rozłączysz jej sesje, pula od razu otworzy nowe, więc kasowanie znów zobaczy zajętą bazę. Trzeba więc albo zatrzymać to, co się łączy, albo odciąć nowe połączenia na czas operacji.

Jak to rozwiązać krok po kroku

1. Przełącz się na inną bazę, żeby nie trzymać połączenia do tej kasowanej. W psql wystarczy \c postgres - łączysz się z bazą systemową postgres i dopiero z niej wykonujesz DROP.
2. Sprawdź, kto jeszcze jest podłączony: SELECT pid, usename, application_name, state FROM pg_stat_activity WHERE datname = 'stara_testowa';. Zobaczysz każdą sesję trzymającą bazę.
3. Jeśli to aplikacja, która sama wznawia połączenia, najpierw ją zatrzymaj albo zablokuj nowe logowania: REVOKE CONNECT ON DATABASE stara_testowa FROM PUBLIC;, żeby pula nie otwierała ich w kółko.
4. Rozłącz pozostałe sesje: SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'stara_testowa' AND pid <> pg_backend_pid();. Warunek na końcu chroni Twoją własną sesję przed odcięciem.
5. Teraz usuń bazę: DROP DATABASE stara_testowa;. Przy braku aktywnych sesji polecenie przejdzie bez błędu.
6. W PostgreSQL od wersji 13 możesz pominąć ręczne rozłączanie i wymusić usunięcie jednym ruchem: DROP DATABASE stara_testowa WITH (FORCE);. Baza sama zakończy podłączone sesje przed skasowaniem.

Jak sprawdzić, że zadziałało

Najprościej wylistować bazy w psql poleceniem \l - skasowanej nie powinno już być na liście. Możesz też sprawdzić to zapytaniem: SELECT datname FROM pg_database WHERE datname = 'stara_testowa'; powinno zwrócić zero wierszy. Jeśli usuwanie nadal się nie udaje mimo rozłączenia, wróć do pg_stat_activity i policz sesje do tej bazy - jeśli wciąż są, znaczy, że coś je odtwarza (najczęściej niezatrzymana aplikacja albo pula połączeń), i trzeba najpierw zablokować nowe połączenia poleceniem REVOKE CONNECT, a dopiero potem powtórzyć kasowanie.

Wróć do listy: 100 najczęstszych pytań i problemów z PostgreSQL

Sprawdź szkolenie: Administracja, replikacja i tuning baz danych PostgreSQL

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5

Sprawdź szkolenie: Zaawansowana administracja PostgreSQL (HA, DR, monitoring, skalowanie)

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5