How to drop all connections to a specific database in postgres without stopping the server?

What can be done about the unreliable nature of TCP?

• When I check email or when I'm browsing the web, I find that at times it's actually faster to stop/abort the page load and start again.  This is especially true when doing this over a mobile network.  A check of email can take a long time if there is a delay.  Also, there is a need for a long round-trip time.  These limitations are all there even though the amount of data transferred is small (where sending it redundantly or in anticipation might not incur too big of an overhead). When connected over SSH, I've found that the connection stalls indefinitely, such that I'll need to kill the session and then start again.  TCP itself doesn't seem to have the know-how to see that the connection between the local host and the remote host is still basically open - it does not handle dropped or lost packets very well at all. When connecting to a wireless network, a temporary outage or network condition causing the wireless router to be inaccessible for even a couple seconds has caused the wireless subsystem to believe that this wireless network is unavailable.  On a Mac, it even then shows the wireless menu icon to be greyed out - requiring manual reselection of that network.  This doesn't really reflect the observed nature of the connection, since the conclusion is made so rapidly (within seconds) and this results in no further automatic attempts to reconnect. Further, when the network is unavailable or otherwise when there are dropped packets, even a UDP or IPSec based VPN needs to be reestablished.  This means stopping the VPN and starting it again - manually. Ideally I'd like for the networking systems to conclude that there is really a downtime only if the remote end is unreachable for over two minutes (after several reattempts), and when protocols are stacked upon one another (a wireless connection as a prerequisite for TCP and UDP, a VPN as a prerequisite for an SSH connection), I'd like them to be robust and resilient in a way that does not topple the whole stack or require this layered manual intervention. Any thoughts to improve the state of the art of network connections, however general, are appreciated.  For example if one is checking email on a mobile phone and if that user just wants to know if there is new mail, then there could be a UDP-based service that stands "above" TCP/IMAP in that it can reply positively or negatively about whether there is new email.  This could also be done securely, but if TCP is not the basis, then the phone could send out a few dozen-byte requests and then wait for an answer from the server, which would also be repeatedly sending its brief answer.  This can move past the need to wait 30 seconds or more just to see if there's new email (and it can move past the need to reset the TCP connection manually). It would seem that an ongoing UDP-based connection (such as OpenVPN) might offer some persistence of connection even in the event that the network is down.  But still it would be important to have a Mac not drop the wireless connection altogether when seconds later that same network is reachable (such resets should NOT be needed - especially manual ones).  And a VPN can expect a potential window of unavailability and should not cause underlying TCP connections to be reset.  SSH also should not stall indefinitely; in the event that the TCP layer is deemed unavailable, it should clearly indicate this or there should be an escape sequence that gives details on the remaining timeout.  Also a new TCP session should not be needed.  It should not be needed to have TCP sessions sent over forwarded ports start over again.  The mosh project may be promising. While I'm at it, when a VPN connection goes down for whatever reason, further and ongoing TCP connections should not go over the insecure network; whatever VPN software is on the system, it should not automatically switch the insecure mode.  If somehow the remote VPN server is unavailable (and in my opinion that means unavailable for over two minutes, not unavailable for ten seconds), then further connections should be blocked by default, since when the user switches on VPN what that means is they want and expect a secure and reliable channel of communication.  To default to an insecure communication automatically - without user intervention - is quite insecure.

• Answer:

  I disagree with the premise of the question; TCP, by nature, is remarkably reliable.  However, as you note, the house of cards that comprise most end-to-end TCP connections can result in less than optimal experiences. Let's try to look at your issues point by point.  Why is it faster to stop and abort? Problem: A wireless network is sometimes described as a "leaky bucket" network, which means that it has a natural tendency to lose packets during normal operation.  Unfortunately, many TCP stacks treat packet loss as a signal to slow down and TCP slows down by backing off exponentially.  This behavior dates back to wired networks, where packet loss served as a fairly reliable, easy to detect indication of congestion.  Unfortunately, a radio signal or transport path interruption can cause the TCP to rapidly back off and suffer a dramatic loss of speed. What can be done: Newer TCP stacks make estimates of round trip delay as a congestion signal, and retransmit more aggressively in the presence of a 'natural' packet loss where the round trip delay is within expected bounds. Problem: Mobile networks are out of IPv4 addresses.  As a result, they have to employ Carrier Grade NAT systems (CGNAT) that translate from a mobile endpoint's RFC1918 address to a global address.  Depending on the level of oversubscription on each global address, translation timeouts may be in the order of seconds.   If this state expires during one of the aforementioned backoff events, the connection will not be able to recover since the mapping to the active session on the server has expired. Solution: Switch to IPv6, or use a carrier that hands out only global IPv4 addresses. Problem:  A stateful load balancer or cache farm in front of a server might expire a TCP session handle if traffic drops off due to a congestion event. Solution: Complain to the admin of the stateful load balancer; reduce congestion events; use a better TCP stack. Why doesn't TCP detect my lost connection? Problem: TCP will detect a lost connection.  If you send a packet to a closed socket, a TCP RST should be sent to alert the application that it's partner process has disappeared.  However, a TCP session only triggers a RST if it sends data.  A quiesced TCP session will remain open forever, ignorant of whether or not the server is still active (or of the NAT entry has expired.) Solution: Set your system to send TCP keepalives on long lasting TCP flows.  This will both reset the timer on the NAT translations and also cause a session to terminate if the other side has closed its connection. Problem: Firewalls sometimes block RST signals with the misguided notion that hiding signalling will reduce the effectiveless of black-hats poking at the system.  That's all well and good, but it breaks error recovery, too. Solution: Don't get too clever about your firewall settings; follow TCP endpoint best practices. Wireless Icon is Grayed Out This is not a TCP issue.  Seems like there may be a problem with your access point?  Anyway, this issue is the wireless subsystem design, not TCP itself.  I am inclined to set this aside. VPN Session needs to be restarted Problem: VPN sessions are interrupted and stalled indefinitely when wireless interruptions occur. Solution: This may be a bug with your VPN client.  VPN sessions should use mechanisms like DPD (Dead Peer Detection) and keepalives to actively recover from network interruptions.  Well designed VPN systems will have mechanisms to manage session certificates and gracefull restart connections that suffer an interruption. Please note that this is also not TCP. Single Packet Email Check Adding a parallel program  or protocol to check for email seems pointless.  Also, modern email systems expect an authentication check before yielding any information including the presence of new mail. To riff on Jamie Zawinski:  Suppose you have an email problem.  You say, 'I know, I'll invent a new email protocol.'  Now you have two problems. VPN should Fail Closed I couldn't agree more.  I think this is a poor implementation choice of the native MacOS and iOS VPN to continue transmitting data in the clear when a VPN session fails.  This is a case of user convenience trumping security.  There are third party VPN clients that will fail closed; you should switch to those.  Be sure to open a bug with Apple, too. In Summary The robustness and resilience you are asking for already exists.  Lots of protocol adaptions and extensions exist to avoid the very problems you re experiencing. Most of your issues seem to be related to specific implementations of these things.