個人中心
退出
查看和終止 PostgreSQL 連接會話
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
利用視圖 pg_stat_activity 查看連接會話
通過操作系統(tǒng)命令查看連接會話
利用 pg_cancel_backend() 終止正在運行的語句
通過操作系統(tǒng)命令終止正在運行的語句
利用 pg_terminate_backend() 終止連接會話
通過操作系統(tǒng)命令終止連接會話
大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。今天給大家介紹一下如何如何查看和終止 PostgreSQL 的連接會話。
在 PostgreSQL 中，配置參數(shù) max_connections 決定了數(shù)據(jù)庫的并發(fā)連接上限，默認(rèn)值為 100：
postgres=> show max_connections;
max_connections
-----------------
100
(1 row)
這個數(shù)值還包括了預(yù)留給 superuser 的一些連接，具體數(shù)量由參數(shù) superuser_reserved_connections 決定：
postgres=> show superuser_reserved_connections;
superuser_reserved_connections
--------------------------------
3
(1 row)
也就是說，當(dāng)連接數(shù)到達(dá) max_connections - superuser_reserved_connections
時，只有超級用戶才能建立新的數(shù)據(jù)庫連接；普通用戶連接時將會返回錯誤信息“FATAL: sorry, too many clients
already.”或者“FATAL: remaining connection slots are reserved for
non-replication superuser
connections”。當(dāng)我們遇到這種連接數(shù)過多的錯誤，或者由于其他原因需要找出目前存在哪些客戶端的連接時，可以采用以下方法。
利用視圖 pg_stat_activity 查看連接會話
PostgreSQL 動態(tài)統(tǒng)計視圖 pg_stat_activity 中的每一行代表了一個后臺進(jìn)程，包含了該進(jìn)程當(dāng)前活動相關(guān)的信息。
postgres=> select pid,
datname as database_name,
usename as user_name,
application_name,
client_addr,
backend_start,
state,
state_change,
wait_event_type,
wait_event,
query,
backend_type
from pg_catalog.pg_stat_activity;
pid |database_name|user_name|application_name
|client_addr |backend_start |state |state_change
|wait_event_type|wait_event
|query
|backend_type |
-----|-------------|---------|-----------------------------------------|------------|-------------------|------|-------------------|---------------|-------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|----------------------------|
1677| | |
| |2020-07-06 09:29:33| |
|Activity |AutoVacuumMain
|
|autovacuum launcher |
1679| |postgres
| | |2020-07-06
09:29:33| | |Activity
|LogicalLauncherMain|
|logical replication launcher|
23483|hrdb |postgres
|DBeaver 7.1.2 - Main <hrdb> |192.168.56.1|2020-07-08
08:09:58|idle |2020-07-08 08:09:58|Client |ClientRead
|SHOW
search_path
|client backend |
23484|hrdb |postgres
|DBeaver 7.1.2 - Metadata <hrdb> |192.168.56.1|2020-07-08
08:09:58|idle |2020-07-08 08:09:58|Client |ClientRead
|SELECT t.oid,t.*,c.relkind,format_type(nullif(t.typbasetype, 0),
t.typtypmod) as base_type_name?FROM pg_catalog.pg_type t?LEFT OUTER JOIN
pg_class c ON c.oid=t.typrelid?WHERE typnamespace=$1 ?ORDER by
t.oid |client
backend |
23486|pagila |postgres |DBeaver 7.1.2 -
SQLEditor <Script-13.sql>|192.168.56.1|2020-07-08
08:09:58|active|2020-07-08 08:10:30| |
|select pid,? datname as database_name,? usename as
user_name,? application_name,? client_addr,?
backend_start,? state,? state_change,?
wait_event_type,? wait_event,? query,?
backend_type?fr|client backend |
23487|pagila
|postgres |DBeaver 7.1.2 - Main <pagila>
|192.168.56.1|2020-07-08 08:09:58|idle |2020-07-08
08:09:58|Client |ClientRead |SHOW
search_path
|client backend |
23488|pagila |postgres
|DBeaver 7.1.2 - Metadata <pagila> |192.168.56.1|2020-07-08
08:09:58|idle |2020-07-08 08:10:00|Client |ClientRead
|SELECT t.oid,t.*,c.relkind,format_type(nullif(t.typbasetype, 0),
t.typtypmod) as base_type_name FROM pg_catalog.pg_type t?LEFT OUTER JOIN
pg_class c ON c.oid=t.typrelid?WHERE
t.oid=$1
|client backend |
1675| |
| | |2020-07-06
09:29:33| | |Activity |BgWriterHibernate
|
|background writer |
1674| |
| | |2020-07-06
09:29:33| | |Activity |CheckpointerMain
|
|checkpointer |
1676| |
| | |2020-07-06
09:29:33| | |Activity |WalWriterMain
|
|walwriter |
查詢結(jié)果不僅返回了客戶端連接對應(yīng)的服務(wù)器進(jìn)程，也包括 PostgreSQL 服務(wù)器的各種守護(hù)進(jìn)程。其中，
pid 代表了后臺進(jìn)程的 id；
datname 是進(jìn)程連接的數(shù)據(jù)庫，對于后臺守護(hù)進(jìn)程顯示為空；
usename 是連接數(shù)據(jù)庫使用的用戶名，對于后臺守護(hù)進(jìn)程顯示為空；
application_name 是連接數(shù)據(jù)庫的應(yīng)用程序，對于后臺守護(hù)進(jìn)程顯示為空；
client_addr 是客戶端的 IP 地址，對于后臺守護(hù)進(jìn)程或者通過 Unix 套接字連接的進(jìn)程顯示為空；
backend_start 是進(jìn)程啟動的時間點，對于客戶端的后臺進(jìn)程是連接服務(wù)器的時間點；
state 代表了進(jìn)程當(dāng)前的狀態(tài)，可能的取值包括 active、idle、idle in transaction、idle in transaction (aborted)、fastpath function call 以及 disabled；
state_change 是最后一次狀態(tài)的修改時間；
wait_event_type 表示進(jìn)程正在等待的事件類型，如果沒有等待顯示為空；可能的取值包括 LWLock、Lock、BufferPin、Activity、Extension、Client、IPC、Timeout 以及 IO；
wait_event 表示進(jìn)程正在等待的事件名稱，如果沒有等待顯示為空；
query 是進(jìn)程最后一次執(zhí)行的查詢語句。對于 active 狀態(tài)，顯示正在執(zhí)行的查詢；其他狀態(tài)顯示上一次執(zhí)行的查詢；
backend_type 是進(jìn)程的類型。
??等待事件 wait_event 和進(jìn)程狀態(tài) state 是相互獨立的。如果進(jìn)程處于 active 狀態(tài)，可能正在等待某個事件，也可能沒有等待事件如果狀態(tài)為 active 并且 wait_event 不為空，意味著正在執(zhí)行的查詢被其他進(jìn)程阻塞。
視圖 pg_stat_activity 中還包含了更多的字段，具體可以參考官方文檔。
通過操作系統(tǒng)命令查看連接會話
對于 Linux 操作系統(tǒng)，我們也可以使用ps命令查看 PostgreSQL 后臺進(jìn)程：
ps -ef|grep 'postgres'|grep -v 'grep'
postgres 1258 1 0 Jul06 ? 00:00:09 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/
postgres 1335 1258 0 Jul06 ? 00:00:00 postgres: logger
postgres 1674 1258 0 Jul06 ? 00:00:00 postgres: checkpointer
postgres 1675 1258 0 Jul06 ? 00:00:05 postgres: background writer
postgres 1676 1258 0 Jul06 ? 00:00:05 postgres: walwriter
postgres 1677 1258 0 Jul06 ? 00:00:05 postgres: autovacuum launcher
postgres 1678 1258 0 Jul06 ? 00:00:11 postgres: stats collector
postgres 1679 1258 0 Jul06 ? 00:00:00 postgres: logical replication launcher
postgres 23483 1258 0 08:09 ? 00:00:00 postgres: postgres hrdb 192.168.56.1(63614) idle
postgres 23484 1258 0 08:09 ? 00:00:00 postgres: postgres hrdb 192.168.56.1(63615) idle
postgres 23486 1258 0 08:09 ? 00:00:00 postgres: postgres pagila 192.168.56.1(63616) idle
postgres 23487 1258 0 08:09 ? 00:00:00 postgres: postgres pagila 192.168.56.1(63617) idle
postgres 23488 1258 0 08:09 ? 00:00:00 postgres: postgres pagila 192.168.56.1(63618) idle
其中，1258 是 postmaster 主進(jìn)程；其他進(jìn)程都是它的子進(jìn)程。這種方式顯示的進(jìn)程信息相對簡單一些。
利用 pg_cancel_backend() 終止正在運行的語句
函數(shù) pg_cancel_backend(pid) 可以用于取消進(jìn)程正在執(zhí)行的查詢，其中 pid 就是進(jìn)程 id，可以通過上面的 pg_stat_activity 視圖進(jìn)行查看。
我們新建一個數(shù)據(jù)庫連接，并且執(zhí)行以下語句：
-- 新建數(shù)據(jù)庫連接
postgres=> select pg_sleep(60);
pg_sleep 函數(shù)可以將當(dāng)前會話的進(jìn)程暫停指定時間，這里是 60 秒。
然后在另一個會話中終止該進(jìn)程正在執(zhí)行的查詢：
-- 另一個連接會話
postgres=> select pid,
query
from pg_catalog.pg_stat_activity
where wait_event = 'PgSleep';
pid |query |
-----|--------------------|
28582|select pg_sleep(60);|
postgres=> select pg_cancel_backend(28582);
pg_cancel_backend|
-----------------|
true |
函數(shù) pg_cancel_backend() 執(zhí)行成功后返回 true，此時第一個連接會話將會返回以下錯誤：
ERROR: canceling statement due to user request
雖然當(dāng)前查詢被終止，但是連接會話仍然存在，可以繼續(xù)執(zhí)行其他查詢。
通過操作系統(tǒng)命令終止正在運行的語句
pg_cancel_backend() 函數(shù)實際上是給進(jìn)程發(fā)送了一個 SIGINT 信號。因此，對于 Linux 操作系統(tǒng)，我們也可以使用kill命令終止終止正在運行的語句：
kill -2 pid
其中，-2 代表了 SIGINT 信號；pid 是進(jìn)程 id。
利用 pg_terminate_backend() 終止連接會話
有時候 pg_cancel_backend() 需要等待很長時間才能終止正在執(zhí)行的查詢，為此我們也可以使用 pg_terminate_backend(pid) 函數(shù)強制終止整個連接進(jìn)程。
例如，我們可以使用以下語句終止連接進(jìn)程（28582）：
-- 另一個連接會話
postgres=> select pg_terminate_backend(28582);
pg_terminate_backend|
--------------------|
true |
函數(shù) pg_terminate_backend() 執(zhí)行成功后返回 true，此時第一個連接會話執(zhí)行任何操作都會返回以下錯誤：
postgres=> select 1;
FATAL: terminating connection due to administrator command
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
從錯誤信息可以看出當(dāng)前連接被異常關(guān)閉，同時 psql 自動進(jìn)行了重新連接。
通過操作系統(tǒng)命令終止連接會話
函數(shù) pg_terminate_backend() 實際上是給進(jìn)程發(fā)送了一個 SIGTERM 信號。因此，對于 Linux 操作系統(tǒng)，我們也可以使用kill命令終止 PostgreSQL 后臺進(jìn)程：
kill -9 pid
kill -15 pid
其中，-9 代表的是 SIGKILL 信號；-15 代表的是 SIGTERM 信號。
除了使用命令查看和終止 PostgreSQL 連接會話之外，pgAdmin
管理工具也提供了相應(yīng)的圖形操作。在 Dashboard 面板最下面的 Server Activity
區(qū)域可以查看當(dāng)前連接的會話、鎖、預(yù)編譯事務(wù)以及配置信息，同時可以終止正在執(zhí)行的查詢和連接進(jìn)程。
分類導(dǎo)航