使用 psycopg2 关闭现有的 Postgres 连接 [英] Close an existing Postgres connection using psycopg2
问题描述
我在玩Postgresql 和psycopg2.我想我使用终端启动了许多连接，但从未关闭它.使用 pyscopg2，我了解了如何启动和关闭连接.现在我试图使用 pyscopg2 获取现有连接(我之前使用终端启动的)，但端口号似乎存在问题.
I was playing around with Postgresql and psycopg2. I think I started many connections using the terminal but never closed it. Using pyscopg2 I understood how to start a connection and close it too. Now I was trying to get the existing connection (that i launched using the terminal before) using pyscopg2 but it seems there is an issue with port number.
当我运行 SELECT * FROM pg_stat_activity ; 时，这些是我的结果
When I run SELECT * FROM pg_stat_activity ;, these are my results
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type
-------+----------------+-------+----------+----------------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+---------------------+---------------------+-------------+--------------+----------------------------------------------------------------+---------------------
| | 75600 | | | | | | | 2020-01-13 15:28:42.66597+01 | | | | Activity | AutoVacuumMain | | | | | autovacuum launcher
| | 75602 | 10 | siddhanttandon | | | | | 2020-01-13 15:28:42.666037+01 | | | | Activity | LogicalLauncherMain | | | | | background worker
16385 | siddhanttandon | 77470 | 10 | siddhanttandon | agens | | | -1 | 2020-01-13 16:04:04.907286+01 | | | 2020-01-13 16:04:04.910102+01 | Client | ClientRead | idle | | | | client backend
16385 | siddhanttandon | 77115 | 10 | siddhanttandon | | 127.0.0.1 | | 54156 | 2020-01-13 15:45:08.361864+01 | 2020-01-13 15:45:08.365267+01 | 2020-01-13 15:45:08.366289+01 | 2020-01-13 15:45:08.369882+01 | Client | ClientRead | idle in transaction | | | MATCH (a)-[r]->(b) RETURN id(a) AS startNode, id(b) AS endNode | client backend
16385 | siddhanttandon | 82701 | 10 | siddhanttandon | agens | | | -1 | 2020-01-14 12:08:16.601504+01 | 2020-01-14 13:16:55.356656+01 | 2020-01-14 13:16:55.356656+01 | 2020-01-14 13:16:55.35666+01 | | | active | | 565 | SELECT * FROM pg_stat_activity ; | client backend
| | 75598 | | | | | | | 2020-01-13 15:28:42.662682+01 | | | | Activity | BgWriterHibernate | | | | | background writer
| | 75597 | | | | | | | 2020-01-13 15:28:42.662907+01 | | | | Activity | CheckpointerMain | | | | | checkpointer
| | 75599 | | | | | | | 2020-01-13 15:28:42.6631+01 | | | | Activity | WalWriterMain | | | | | walwriter
127.0.0.1 的连接，在端口 54156 是我想关闭的.所以我想我可以使用以下几行在 psycopg2 中获得这个现有的连接:
The connection at 127.0.0.1, at port 54156is what I want to close. So I thought I could get this exisiting connection in psycopg2 using the following lines :
import psycopg2.pool
dbpool = psycopg2.pool.ThreadedConnectionPool(minconn=5,maxconn=25,host='127.0.0.1',
port='54156',
dbname='test_db',
user='siddhanttandon'
)
dbpool.closeall()
但它给了我错误:
could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 54156?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 54156?
可能这个 IP 和端口号上的连接没有激活，所以我做了一个 netstat |grep postgres 以确认连接是否仍处于活动状态，结果如下:
Probably the connection is not active on this IP and port number, so i did a netstat | grep postgres to confirm if the connections are still active and these are the results :
MBP-di-Siddhant:agensgraph siddhanttandon$ netstat | grep postgres
tcp4 0 0 localhost.postgresql localhost.54156 ESTABLISHED
tcp4 0 0 localhost.54156 localhost.postgresql ESTABLISHED
理想情况下，我想控制与现有数据库的连接设置并使用 python 而不是使用命令行界面关闭连接.
Ideally I would like to control the setting up of connection to existing dbs and closing the connections using python instead of using the command line interface.
如果有人能告诉我如何使用 psycopg2 启动/关闭这些连接，我将不胜感激?
I would really appreciate if someone could tell me how can I start/close these connections using psycopg2 ?
推荐答案
您不能仅仅使用客户端端口号以某种方式侵入并接管连接.
You can't just use the client port number to somehow invade and take over the connection.
如果要强制关闭连接，请使用 pid.从 linux 命令行:kill 77115 或从 SQL 命令行(或从 psycopg2 通过不同的连接):select pg_terminate_backend(77115).
If you want force the connection to close, you use the pid. From the linux command line: kill 77115 or from the SQL command line (or from psycopg2 over a different connection): select pg_terminate_backend(77115).
这篇关于使用 psycopg2 关闭现有的 Postgres 连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
