![]() In the unlikely event that your end users are connected with psql, it will try to reconnect them. This way you disconnect all sessions of a certain user: edb=# select pg_terminate_backend(pid) from pg_stat_activity where usename='adam' This way you cancel all SQL statements of a certain user: edb=# select pg_cancel_backend(pid) from pg_stat_activity where usename='adam' This probably means the server terminated abnormally Server closed the connection unexpectedly The disconnected session gets this output after trying to do anything: edb=> \dįATAL: terminating connection due to administrator command This disconnects a single session: edb=# select pg_terminate_backend( 14346) The session that was doing the endless loop gets this output: ERROR: canceling statement due to user requestĬONTEXT: edb-spl function inline_code_block line 3 at NULL This cancels the SQL statement of one session: edb=# select pg_cancel_backend( 14346) | | from pg_catalog.pg_stat_activity where datname='edb' +-+-ĥ517 | enterprisedb | select pid,usename,query + Back to the superuser session: edb=# select pid,usename,queryĮdb-# from pg_catalog.pg_stat_activity where datname='edb' That session burns CPU now in an endless loop. Now opening another session with that new user: -bash-4.2$ psql -U adam First I create a demo user: edb=# create role adam password 'adam' login Įdb=# grant connect on database edb to adam The demo is done with EDB Postgres Advanced Server 10.1.5 but the shown technique should work the same with other Postgres distributions and older versions. edb=# select current_database,current_user This article shows how you can do it with working examples. In PostgreSQL, you can cancel problem statements or terminate offending sessions remotely with PG_CANCEL_BACKEND and PG_TERMINATE_BACKEND.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |