Are you running into Postgres connection issues on Azure?

Nowadays there are tons of customers who have successful stories using our fully-managed PostgreSQL database due to its various advantages such as pay-as-you-go, high availability, security, and manageability features. However, because a cloud database service like Azure Database for PostgreSQL uses a different connectivity architecture (it's running on cloud, after all), you may run into connectivity issues if you don't follow the best practices.

Working in Azure Database Support, we often deal with connectivity issues. Many of these Postgres connection issues fit into some common patterns and here I am listing those patterns and all possible causes below—to give you a checklist you can use to investigate connection issues with the Single Server deployment option for Azure Database for PostgreSQL. 

1. Scenario: You have just provisioned a server in Azure Database for PostgreSQL - Single Server, but the connection fails

There can be multiple reasons for this as follows: 

Invalid connection String or password 

• Make sure the user you are connecting with an account has the appropriate permission. 
• If you are using the single server deployment mode, confirm that the username you are passing ends with the correct server name/hostname field (usernames need to be passed as username@servername) 
• Make sure the password is correct in all connections. If you have enabled connection throttling server parameter in the portal, the database service will temporarily throttle connections per IP if there are too many invalid password login failures.  

Security 

• Check the firewall rules in the portal. The error pg_hba.conf entry for host 'xxxx', user 'xxxx', database 'pxxxx', SSL... indicates that a firewall rule is needed. Set up firewall rules to allow your client's IP address. 
• Make sure you are using the correct  SSL configuration and choose the right certificate. 

• As a part of our maintenance activity, we are working on changing out gateway certificate used to connect to the server using SSL. Refer to the steps to mitigate the issue in this article.
• Make sure you are using the correct TLS configuration. This is the link for single server TLS configuration. If you use flexible deployment mode, TLS/SSL is enabled by default and cannot be disabled. Minimum TLS version supported on the server is TLS1.2. 

Is it a client Issue? 

• You can simply test the connection from Azure CLI in the portal and see if you can connect. This test can help narrow down if the database is having availability issue or your client network issue.  
• Ping the FQDN and see if it resolves to our Gateway IP correctly when using PostgreSQL single server deployment mode. If you are using the private endpoint, it should resolve to your private IP for the private endpoint.  
• Confirm that your network allows outbound connections on port 5432. You can try to telnet to your server. When using single server deployment mode, confirm your network/firewall does not block connection to the regional Azure Database for PostgreSQL Gateway IP.
• If you are connecting within Azure VM (virtual machines), check NSG (network security groups) rules to see if it blocks the connection. Also check the route table and see if there is any VPN device which may need to be configured. 

• If you are using VNET rules, ensure that the service endpoints are correctly configured. 
• If you are using basic tier and see the error 'Server is not configured to allow IPv6 connections', note that the Basic tier does not support VNet service endpoints. You must remove the endpoint Microsoft.Sql from the subnet attempting to connect to the Basic tier server. 
• When you see  ‘An existing connection was forcibly closed by the remote host,’ that indicates your client closed the connection to the Postgres server. Check your client timeout and idle connection settings. Learn more about this error. 

Are you using the right connection drivers? 

• Check out this supported client library list.
• If you see an error related to GSS, you are likely using a newer client/driver version which Azure Postgres Single Server does not yet fully support. This error is known to affect JDBC driver versions 42.2.15 and 42.2.16.  Consider using a later driver version. Or, consider disabling the request of GSSAPI. Use a connection parameter like gssEncMode=disable. 

2. Scenario: Is your connection taking longer time?

The Single Server architecture leads to high connection time. This can impact your workload performance if there are large short duration connections. For example, user creates a connection, runs a simple query, and closes the connection. We highly recommend connection pooling if you have not done it yet and exam your pool configuration. Learn more about this. 

If you notice the connection latency suddenly increases, you can start checking if you have increased workload. 

3. Scenario: Are you experiencing any connection failures/timeout during peak hours? 

This means your connection string is correct and you can connect successfully in regular hours. However, during peak hours you may experience either connection failures or time outs. 

• Please check your active connections as well as CPU/memory/IO usage percentage in the portal metrics tab. High utilization may lead to unavailable resources for a new connection. Please consider upgrading your server if the resource is hitting 100%.  

• Check the connection limit for your server's pricing tier. Review how many connections your server has and how many more you are attempting. 

4. Scenario: All connections are failing?  

This could be because of issue in Azure infra or maintenance activities. 

• There may be a planned maintenance activity going on your database server. Check your Resource Health for the status. You may also want to setup planned maintenance notifications to get notified of any planned activities. 
• If you think there is a regional outage, see Overview of business continuity with Azure Database for PostgreSQL for steps to recover to a new region. 

Hopefully, you have found the cause of the connectivity issue if you have checked all items in the checklist above. If you have any questions, please feel free to let me know.  

Enjoy!