BŁĄD PostgreSQL: dalsze połączenia nie są dozwolone

Jak zwolnić połączenia PostgreSQL, które nie są poprawnie zamykane przez aplikacje klienckie?

Mam aplikację do eksploracji danych, która uruchamia wiele procesów, wszystkie połączenia z lokalną bazą danych PostgreSQL 9.1 w celu pobrania danych. Działa dobrze przez kilka godzin, ale potem umiera z błędem:

FATAL:  remaining connection slots are reserved for non-replication superuser connections

Badanie tego pokazuje, że jest to najprawdopodobniej spowodowane nieprawidłowym zamykaniem połączeń przez aplikację. Jednak nawet po zabiciu aplikacji połączenia te nigdy nie zostaną uwolnione. Czy nie istnieje jakiś limit czasu, w którym PostgreSQL automatycznie zamknie połączenie?

Próbowałem również zwiększyć maksymalną liczbę połączeń Postgres ze 100 do 200, ale ponowne uruchomienie spowodowało błąd:

2014-02-23 10:51:15 EST FATAL:  could not create shared memory segment: Invalid argument
2014-02-23 10:51:15 EST DETAIL:  Failed system call was shmget(key=5432001, size=36954112, 03600).
2014-02-23 10:51:15 EST HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter.  You can either reduce the request size or reconfigure the kernel with larger SHMMAX.  To reduce the request size (currently 36954112 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
    If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for.
    The PostgreSQL documentation contains more information about shared memory configuration.

Mój system to Ubuntu 12.04 i ma 8 GB pamięci, a wszystkie inne ustawienia PG są domyślne, więc nie jestem pewien, dlaczego uważa, że ​​system nie ma wystarczającej ilości pamięci.

Następnie próbowałem użyć pgbouncer do łączenia i ponownego wykorzystywania połączeń. To wydawało się działać trochę lepiej, ale nawet to w końcu zabrakło połączeń, dając mi błąd:

ERROR:  no more connections allowed

Jak dalej zdiagnozować i naprawić ten problem?

Możesz zwiększyć maksymalną liczbę połączeń, zmieniając ustawienia maksymalnej pamięci współdzielonej, jednak jeśli problem polega na tym, że twoje połączenia nie są zamykane, powinieneś to naprawdę rozwiązać. Jeśli oprogramowanie jest poza twoją kontrolą i jest błędne, ponieważ nie zamyka połączeń, możesz użyć jakiegoś zadania cron, takiego jak:

select pg_terminate_backend(procpid)
from pg_stat_activity
where usename = 'yourusername'
 and current_query = '<IDLE>'
 and query_start < current_timestamp - interval '5 minutes'
;

To właśnie robię, aby zabić nieszczelne połączenia z podobnego błędnego oprogramowania.

Alternatywnie możesz być w stanie uruchomić swoje błędne oprogramowanie poprzez pulę połączeń, która ma podobną funkcjonalność, aby zabijać bezczynne połączenia, takie jak pgpool.

Uwaga : nowsze wersje Postgres mają nieco inne nazwy kolumn:

select pg_terminate_backend(pid)
from pg_stat_activity
where usename = 'YOURDATABASEUSERNAME*'
 and state = 'idle'
 and query_start < current_timestamp - interval '5 minutes'
;

W przypadku nowszych wersji PostgreSQL:

select pg_terminate_backend(pid)
from pg_stat_activity
where usename = 'YOUR_DATABASE_USERNAME*'
 and state = 'idle'
 and query_start < current_timestamp - interval '5 minutes'
;

Powyższe pomoże ci zakończyć bezczynne połączenia. Miałem ten sam problem, ale okazało się, że jest to problem z moim sposobem Flask i SQLAlchemy na połączenie z bazą danych.

* usename nie jest literówką

Dokument PG dotyczący zarządzania zasobami jądra. To powinno pomóc zwiększyć limit pamięci w jądrze.