個(gè)人中心
退出
查看和終止 PostgreSQL 連接會(huì)話
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
利用視圖 pg_stat_activity 查看連接會(huì)話
通過操作系統(tǒng)命令查看連接會(huì)話
利用 pg_cancel_backend() 終止正在運(yùn)行的語句
通過操作系統(tǒng)命令終止正在運(yùn)行的語句
利用 pg_terminate_backend() 終止連接會(huì)話
通過操作系統(tǒng)命令終止連接會(huì)話
大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。今天給大家介紹一下如何如何查看和終止 PostgreSQL 的連接會(huì)話。
在 PostgreSQL 中，配置參數(shù) max_connections 決定了數(shù)據(jù)庫的并發(fā)連接上限，默認(rèn)值為 100：
postgres=> show max_connections;
max_connections
-----------------
100
(1 row)
這個(gè)數(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í)用戶才能建立新的數(shù)據(jù)庫連接；普通用戶連接時(shí)將會(huì)返回錯(cuò)誤信息“FATAL: sorry, too many clients
already.”或者“FATAL: remaining connection slots are reserved for
non-replication superuser
connections”。當(dāng)我們遇到這種連接數(shù)過多的錯(cuò)誤，或者由于其他原因需要找出目前存在哪些客戶端的連接時(shí)，可以采用以下方法。
利用視圖 pg_stat_activity 查看連接會(huì)話
PostgreSQL 動(dòng)態(tài)統(tǒng)計(jì)視圖 pg_stat_activity 中的每一行代表了一個(gè)后臺(tái)進(jìn)程，包含了該進(jìn)程當(dāng)前活動(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é)果不僅返回了客戶端連接對(duì)應(yīng)的服務(wù)器進(jìn)程，也包括 PostgreSQL 服務(wù)器的各種守護(hù)進(jìn)程。其中，
pid 代表了后臺(tái)進(jìn)程的 id；
datname 是進(jìn)程連接的數(shù)據(jù)庫，對(duì)于后臺(tái)守護(hù)進(jìn)程顯示為空；
usename 是連接數(shù)據(jù)庫使用的用戶名，對(duì)于后臺(tái)守護(hù)進(jìn)程顯示為空；
application_name 是連接數(shù)據(jù)庫的應(yīng)用程序，對(duì)于后臺(tái)守護(hù)進(jìn)程顯示為空；
client_addr 是客戶端的 IP 地址，對(duì)于后臺(tái)守護(hù)進(jìn)程或者通過 Unix 套接字連接的進(jìn)程顯示為空；
backend_start 是進(jìn)程啟動(dòng)的時(shí)間點(diǎn)，對(duì)于客戶端的后臺(tái)進(jìn)程是連接服務(wù)器的時(shí)間點(diǎn)；
state 代表了進(jìn)程當(dāng)前的狀態(tài)，可能的取值包括 active、idle、idle in transaction、idle in transaction (aborted)、fastpath function call 以及 disabled；
state_change 是最后一次狀態(tài)的修改時(shí)間；
wait_event_type 表示進(jìn)程正在等待的事件類型，如果沒有等待顯示為空；可能的取值包括 LWLock、Lock、BufferPin、Activity、Extension、Client、IPC、Timeout 以及 IO；
wait_event 表示進(jìn)程正在等待的事件名稱，如果沒有等待顯示為空；
query 是進(jìn)程最后一次執(zhí)行的查詢語句。對(duì)于 active 狀態(tài)，顯示正在執(zhí)行的查詢；其他狀態(tài)顯示上一次執(zhí)行的查詢；
backend_type 是進(jìn)程的類型。
??等待事件 wait_event 和進(jìn)程狀態(tài) state 是相互獨(dú)立的。如果進(jìn)程處于 active 狀態(tài)，可能正在等待某個(gè)事件，也可能沒有等待事件如果狀態(tài)為 active 并且 wait_event 不為空，意味著正在執(zhí)行的查詢被其他進(jìn)程阻塞。
視圖 pg_stat_activity 中還包含了更多的字段，具體可以參考官方文檔。
通過操作系統(tǒng)命令查看連接會(huì)話
對(duì)于 Linux 操作系統(tǒng)，我們也可以使用ps命令查看 PostgreSQL 后臺(tái)進(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)程信息相對(duì)簡單一些。
利用 pg_cancel_backend() 終止正在運(yùn)行的語句
函數(shù) pg_cancel_backend(pid) 可以用于取消進(jìn)程正在執(zhí)行的查詢，其中 pid 就是進(jìn)程 id，可以通過上面的 pg_stat_activity 視圖進(jìn)行查看。
我們新建一個(gè)數(shù)據(jù)庫連接，并且執(zhí)行以下語句：
-- 新建數(shù)據(jù)庫連接
postgres=> select pg_sleep(60);
pg_sleep 函數(shù)可以將當(dāng)前會(huì)話的進(jìn)程暫停指定時(shí)間，這里是 60 秒。
然后在另一個(gè)會(huì)話中終止該進(jìn)程正在執(zhí)行的查詢：
-- 另一個(gè)連接會(huì)話
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，此時(shí)第一個(gè)連接會(huì)話將會(huì)返回以下錯(cuò)誤：
ERROR: canceling statement due to user request
雖然當(dāng)前查詢被終止，但是連接會(huì)話仍然存在，可以繼續(xù)執(zhí)行其他查詢。
通過操作系統(tǒng)命令終止正在運(yùn)行的語句
pg_cancel_backend() 函數(shù)實(shí)際上是給進(jìn)程發(fā)送了一個(gè) SIGINT 信號(hào)。因此，對(duì)于 Linux 操作系統(tǒng)，我們也可以使用kill命令終止終止正在運(yùn)行的語句：
kill -2 pid
其中，-2 代表了 SIGINT 信號(hào)；pid 是進(jìn)程 id。
利用 pg_terminate_backend() 終止連接會(huì)話
有時(shí)候 pg_cancel_backend() 需要等待很長時(shí)間才能終止正在執(zhí)行的查詢，為此我們也可以使用 pg_terminate_backend(pid) 函數(shù)強(qiáng)制終止整個(gè)連接進(jìn)程。
例如，我們可以使用以下語句終止連接進(jìn)程（28582）：
-- 另一個(gè)連接會(huì)話
postgres=> select pg_terminate_backend(28582);
pg_terminate_backend|
--------------------|
true |
函數(shù) pg_terminate_backend() 執(zhí)行成功后返回 true，此時(shí)第一個(gè)連接會(huì)話執(zhí)行任何操作都會(huì)返回以下錯(cuò)誤：
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.
從錯(cuò)誤信息可以看出當(dāng)前連接被異常關(guān)閉，同時(shí) psql 自動(dòng)進(jìn)行了重新連接。
通過操作系統(tǒng)命令終止連接會(huì)話
函數(shù) pg_terminate_backend() 實(shí)際上是給進(jìn)程發(fā)送了一個(gè) SIGTERM 信號(hào)。因此，對(duì)于 Linux 操作系統(tǒng)，我們也可以使用kill命令終止 PostgreSQL 后臺(tái)進(jìn)程：
kill -9 pid
kill -15 pid
其中，-9 代表的是 SIGKILL 信號(hào)；-15 代表的是 SIGTERM 信號(hào)。
除了使用命令查看和終止 PostgreSQL 連接會(huì)話之外，pgAdmin
管理工具也提供了相應(yīng)的圖形操作。在 Dashboard 面板最下面的 Server Activity
區(qū)域可以查看當(dāng)前連接的會(huì)話、鎖、預(yù)編譯事務(wù)以及配置信息，同時(shí)可以終止正在執(zhí)行的查詢和連接進(jìn)程。
分類導(dǎo)航