我在和Postgresql和psycopg2一起玩。我想我用终端开始了很多连接,但从未关闭过。使用pyscopg2,我理解了如何启动连接并关闭连接。现在,我试图使用pyscopg2获取现有连接(之前使用终端启动的连接),但是端口号似乎存在问题。
当我运行SELECT * FROM pg_stat_activity ;时,这些是我的结果
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中获得这个存在的连接:
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来确认连接是否仍然处于活动状态,结果如下:
MBP-di-Siddhant:agensgraph siddhanttandon$ netstat | grep postgres
tcp4 0 0 localhost.postgresql localhost.54156 ESTABLISHED
tcp4 0 0 localhost.54156 localhost.postgresql ESTABLISHED理想情况下,我希望控制连接到现有dbs的设置,并使用python关闭连接,而不是使用命令行接口。
如果有人能告诉我如何使用psycopg2启动/关闭这些连接,我将非常感激。
发布于 2020-01-14 15:40:32
您不能只使用客户端端口号以某种方式入侵并接管连接。
如果要强制关闭连接,则使用pid。来自linux命令行:kill 77115或SQL命令行(或通过不同的连接从psycopg2 ):select pg_terminate_backend(77115)。
https://stackoverflow.com/questions/59734370
复制相似问题