Postgresql: No connection could be made because the target machine actively refused it

Question

Running Postgresql 9.5 on a windows server 2012 R2 in Azure

While running some loadtests on my application, I get errors on not being able to connect to the postgres server. In the logs of postgres I get the following message:

could not receive data from client: No connection could be made because the target machine actively refused it.

This only happens when the loadtest goes to the next scenario, hitting a different part of the code. So new connections to the database are required. But after 10-20 seconds the rest of the scenario works flawlessly without hitting any other hiccups. So the problem seems to be the tcp connections. (My code retries a couple of times but it is not feasible to let it retry for 20 seconds)

I'm using the following settings in the config 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.. It is not save to accept connections from everyone, but this is just for testing purposes and to make sure these settings are not blocking any connection. So this answer is void

I've been monitoring the number of connection on the server and under the load it is stable at 75. Postgres is using around 350mb of RAM. So given the config and the vm specs (7gb ram) there should be plenty of space to create more connections. However when the next scenario is spinning up the number of connections does not increase, it stays level and starts giving these log messages about no connection could be made.

What could be the problem here?

Solution 1

It does sound like this isn't really a Postgres problem (hence no changes in DB stats you're checking), rather that the traffic is being stopped by the server. Possibly because traffic on that port is saturated while handling your load testing queries?

It doesn't sound like you're hitting any of the Azure resource limits (including the database limits if that applies to your setup?), but without more detail on your load tests it's hard to say exactly what is needed.

Solutions from around the web and other SO answers suggest:

• Disable TCP autotuning and tweak the TCP/IP registry keys on the server, e.g. set TcpAckFrequency - see this article for details
• Make TCP setting adjustments (like WinsockListenBacklog) - which may be affected by whether connection pooling is in use or not - see this MS support article, which is for SQL Server 2005 but has some great tips on troubleshooting rejected TCP/IP connections (using Network Monitor, but applies to newer tools)
• Faster request processing if you have enough control of the server - source
• Disabling network proxying (in your load testing app): <defaultProxy> <proxy usesystemdefault="False"/> </defaultProxy> - source

Solution 2

Most possible reason is a Firewall/Anti-virus:

• Software/Personal Firewall Settings
• Multiple Software/Personal Firewalls
• Anti-virus Software
• LSP Layer
• (Virtual) Router Firmware

Does your current Azure infrastructure contain Firewall or Anti-virus ?

Additionally on doing some additional searches, it looks like this is a standard Windows "connection refused" message, which suggests that PostgreSQL is trying to connect to something and being refused.

Also possible that one network element in your network - assuming that you are still connected to the server - will delay or drop somes DB login/authentication network packets (considered for example as a fake auth.replay) ...

You may also use a packet analyzer (like Wireshark) to record/inspect network flow when the error appear.

Regards

Solution 3

I was facing the same issue in my AspNet core application while I was trying to connect the Postgresql from my application. The error was thrown in the Program.cs file when I was calling the Migrate function.

public static void Main(string[] args) {
    try {
        var host = BuildWebHost(args);
        using(var scope = host.Services.CreateScope()) {
            // Migrate once after app is started.
            scope.ServiceProvider.GetService <MyDatabaseContext>().Migrate();
        }
        host.Run();
    }
    catch(Exception e) {
        //NLog: catch setup errors
        _logger ? .Error(e, "Stopped program because of exception: ");
        throw;
    }
}

To fix this problem I did the following steps.

1. Check whether the Postgresql service is running by going to the services.msc
2. Tried to login to the pgAdmin with the user and password I provided in the database context

Everything was file, and as you know that 5432 is the default port of Postgresql and somehow I was using a different port in my application connection string, changing it to 5432 fixed this issue for me.

"ConnectionString": "User Id=postgres;Password=mypwd;Host=localhost;Port=5432;Database=mydb;"

Comments

[P. Gramberg]

I do already have a rule for port 5432 in the firewall, and given that besides the start up phase the connections are all working great (after those 20 sec, it works for 10 minutes. Then switching scenario it stops for 20 sec and then it works again for 10 minutes) I think the problem lays more with the number of connections that can be established. But I don't understand why this is currently capped. As for the suggestion that Postgres is connecting to something, I do not have a clue what it should be connecting to. Would love to hear ideas about that!

[A STEFANI]

Are you using "Barracuda Web Application Firewall" or any other virtual network firewall/proxy in your virtual infrastructure ? Could you test it without any firewall activated on both side (app & server) to be sure that the issue could not be relative to a firewall ? Router/Switch composing the (virtual) network can also have some feature that inspect some layer of packet to protect for malicious request or simply avoid DoS attack ? Did you properly close your previous connection before runing the new scenario ?

[mxlse]

And can you check the number of connections with SELECT sum(numbackends) FROM pg_stat_database; when this happens?

[P. Gramberg]

A Stefani: It looks like none of that is happening because if something would block the packets the server would not be able to tell and give the logging message. @mxlse The query returned 60 at all times, so during the failures but also when it was working

[P. Gramberg]

The issue was indeed with the TCP connections themselves. There were remaining idle connections but the application forgot about them