Shulou(Shulou.com)06/01 Report--

I. principle

In database PostgreSQL, a client-to-server connection is actually a tcp socket connection, a tcp connection is a virtual connection, one party exits abnormally (such as a power outage), and the other party continues to maintain the connection.

For example: after a client computer is normally connected to the server, the power supply is forcibly unplugged, resulting in an artificial power outage, restart the computer, and then connect to the server. Check all the connections of the server with the SQL statement select * from pg_stat_activily, you will find that the connection of this client is still there before the power outage, except this time. Because the server did not know the power outage behavior of the client at all, it thought that the connection was idle. However, this dead connection will not last forever, and after 2 hours, the connection on the server will be automatically cut off because PostgreSQL supports the TCP_KEEPLIVE mechanism. There are three system variables tcp_keepalives_idle,tcp_keepalives_interval,tcp_keepalives_count to set how PostgreSQL handles dead connections. For each connection, PostgreSQL will actively send tcp_keeplive packets to the client after the connection is idle for tcp_keepalives_idle seconds to detect whether the client is still alive. When sending tcp_keepalives_count detective packets and each detective package does not respond within tcp_keepalives_interval seconds, PostgreSQL thinks the connection is dead and cuts off the dead connection.

In PostgreSQL, if all three parameters are set to 0, the default value of the operating system will be used. Under linux, tcp_keepalives_idle usually takes 2 hours, that is, 2 hours before the server can automatically shut down the dead connection. In the actual operation, you can adjust the above parameters by yourself.

However, it is never enough to rely on the server to cut the dead connection in this way. Suppose you have a connection that suddenly loses power while running the following interactive command

The sudden power outage of begin transaction;lock table xxx in exclusive mode;-- is unlikely, but it must exist. Commit1.1, cancel the executing sql pid (will not release the connection, only cancel the sql query statement) SELECT pg_cancel_backend ('26945') # 26945 is procpid1.2, releases and kills the executing sql, releases connection select pg_terminate_backend (procpid) 1.3, batch releases connection SELECT pg_terminate_backend (procpid) FROM pg_stat_activity WHERE current_query=''1.4 that kills idle state, and kills process pg_ctl kill TERM 4004 using pg_ctl tools that come with PG.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.