Geek Answers Handbook

Postgresql: the connection could not be completed because the target machine actively refused it

Running Postgresql 9.5 on a Windows 2012 R2 server in Azure

When I run some boot tests in my application, I get errors when it is not possible to connect to the postgres server. In postgres logs, I get the following message:

Failed to receive data from the client: the connection could not be completed because the target machine actively refused it.

This only happens when loadtest moves on to the next script, falling into another part of the code. Therefore, new database connections are needed. But after 10-20 seconds, the rest of the script works flawlessly, without hitting any other hiccups. So the problem is with tcp connection. (My code retries several times, but it is not possible to retry within 20 seconds)

I use the following settings in configuration files

postgresql.conf

listen_addresses = '*' max_connections = 500 shared_buffers = 1024MB temp_buffers = 2MB work_mem = 2MB maintenance_work_mem = 128MB

pg_hba.conf

 host all all 0.0.0.0/0 trust host all all ::/0 trust

I know, I know. You cannot accept connections from everyone, but this is only for testing purposes and to ensure that these settings do not block any connection. So this answer is invalid

I track the number of connections on the server and under load it is stable at 75. Postgres uses about 350 MB of RAM. Therefore, given the configuration and specifications of vm (7gb ram), there should be enough space to create more connections. However, when the following scenario unfolds, the number of connections does not increase, it remains at the level and starts issuing these log messages about the impossibility of connection.

What could be the problem?

+5
source share
2 answers

It sounds like it is not a Postgres problem (hence, no changes to the database statistics that you check), but rather that the traffic is stopped by the server. Perhaps due to the fact that traffic on this port is saturated when processing load load requests?

It doesn't seem like you will click any of the Azure resource limits (including database limits , if that applies to your setup?), But without additional details about your load tests, it's hard to say exactly what you need.

Solutions from all over the Internet and other SO answers offer:

  • Disable TCP autoconfiguration and configure the TCP / IP registry keys on the server, for example. set TcpAckFrequency - see this article for more details.
  • Configuring TCP settings (for example, WinsockListenBacklog ) - which can be affected by the use of a connection pool or not - see this MS support article , which is for SQL Server 2005, but provides some helpful troubleshooting tips for rejecting TCP / IP connections ( using Network Monitor, but applies to newer tools)
  • Faster request processing if you have enough server management - source
  • Disabling network proxy (in a load testing application): <defaultProxy> <proxy usesystemdefault="False"/> </defaultProxy> - source
+4
source

The most likely cause is a firewall / antivirus:

  • Software / Personal Firewall Settings
  • Multiple software / personal firewalls
  • Antivirus software
  • LSP Layer
  • (virtual) router firmware

Does your Azure infrastructure have a firewall or antivirus?

In addition, when doing some additional searches, it looks like this is a standard message about refusing to connect to Windows, which indicates that PostgreSQL is trying to connect to something and refuse.

It is also possible that one network element on your network - provided that you are still connected to the server - will delay or delete some DB network login / authentication packets (which are considered, for example, as fake auth.replay) ...

You can also use a packet analyzer (e.g. Wireshark ) to record / check the network stream when an error occurs.

Hello

+1
source

More articles:


All Articles