Hello, I am encountering a connectivity issue PostgreSQL following a recent migration by microsoft. Here's a brief timeline of events: 1st Migration : On 2nd September 2024 , there was a migration by microsoft mainly for validations from single to flexible server. Everything was working fine after this migration, with no connectivity or timeout issues. 2nd Migration : On 7th September 2024 , a second migration was executed by Microsoft. After this migration, we started facing connectivity problems. The system times out frequently when attempting to connect to the PostgreSQL database after 3 attempts. The following error is raised: System.InvalidOperationException: An exception has been raised that An error occurred using the connection to database 'xxxxxx' on server 'tcp://xxxx.postgres.database.azure.com:5432'. I've ensured that the connection string and database configurations are correct, and there were no changes to the database credentials. The server was functioning correctly before the second migration. How can I resolve the timeout/ connectivity problems? Has anyone encountered something similar with Entity Framework Core and PostgreSQL after migrations?

Connectivity and Timeout Issues After Second Migration with PostgreSQL from Single to flexible server

Waleed Asad 0

Hello,

I am encountering a connectivity issue PostgreSQL following a recent migration by microsoft. Here's a brief timeline of events:

1st Migration: On 2nd September 2024, there was a migration by microsoft mainly for validations from single to flexible server. Everything was working fine after this migration, with no connectivity or timeout issues.
2nd Migration: On 7th September 2024, a second migration was executed by Microsoft. After this migration, we started facing connectivity problems. The system times out frequently when attempting to connect to the PostgreSQL database after 3 attempts. The following error is raised:

System.InvalidOperationException: An exception has been raised that 
An error occurred using the connection to database 'xxxxxx' on server 'tcp://xxxx.postgres.database.azure.com:5432'.

I've ensured that the connection string and database configurations are correct, and there were no changes to the database credentials. The server was functioning correctly before the second migration.

How can I resolve the timeout/ connectivity problems? Has anyone encountered something similar with Entity Framework Core and PostgreSQL after migrations?

Oury Ba-MSFT 19,101 Reputation points Microsoft Employee

2024-09-11T02:12:09.4033333+00:00

@Waleed Asad Thank you for reaching out.

Could you please check if any of the server parameters were modified on your single server earlier. The same changes need to be made on your flexible server. It has been mentioned in the notifications generated.

Please let us know the result.

Regards,

Oury
Waleed Asad 0 Reputation points

2024-09-11T08:23:22.9966667+00:00

@Oury Ba-MSFT , in the migrate tab for the flexible server this is the notification that I see and it did not mention anything about server parameters.
Shriram Muthukrishnan 10 Reputation points Microsoft Employee

2024-09-11T11:46:16.4533333+00:00

Hi Waleed,

This can be related to server parameters not copied from single server to flexible server.

On your single server there is a section called Server Parameters

There might be some parameters which could have been tweaked on your single server.

The automigration does not copy server parameters from single to flexible server which could be causing the connectivity issues.

In order to copy server parameters, you can follow the steps mentioned here.

These steps are documented as part of the post migration steps.

Let me know if this helps.
Mahesh Kurva 240 Reputation points Microsoft Vendor

2024-09-12T18:37:34.17+00:00

Hi @Waleed Asad,

Just checking in to see if the above answer provided by @Shriram Muthukrishnan, @Oury Ba-MSFT helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Waleed Asad 0 Reputation points

2024-09-13T08:18:32.31+00:00

@Mahesh Kurva , the response was useful but I am having issues with running the post migration script to copy the server parameters from single to flexible server using the azure cli.

Both my servers are in the same resource group under same subscription but the script cannot find my single server under that resource.
Hariram Thirukarugavur Ramesh 0 Reputation points Microsoft Employee

2024-09-13T15:40:32.39+00:00
Hi Waleed,

what is the error you are getting when you run the script?

Are you able to access the Single server with any other command?

Is the error persistent or intermittent?

Can you confirm the SSL state on the 2 servers? We do enforce SSL by default
Waleed Asad 0 Reputation points

2024-09-13T16:25:21.5433333+00:00
@Hariram Thirukarugavur Ramesh

what is the error you are getting when you run the script? (This single server cannot be found in the resource group)

Are you able to access the Single server with any other command? (No)

Can you confirm the SSL state on the 2 servers? (It is enabled for both of them)
Mahesh Kurva 240 Reputation points Microsoft Vendor

2024-09-16T04:08:49.85+00:00
Hi @Waleed Asad,

Here are some troubleshooting steps that might help you:

Please make sure that you have provided the correct resource group name and server name in the command.

Check that you have the necessary permissions to access and manage both the single and flexible servers.

Verify that there are no typos or inconsistencies in the resource IDs or names used in your script. Even small errors can prevent the Azure CLI from finding the resources.

Ensure that network security groups, firewall settings, and other configurations are not preventing access to the servers. Though this is less likely the cause if you can access the server with other commands.

For more information, please refer the document: az postgres server show.

I hope this information helps. Please do let us know if you have any further queries.
Waleed Asad 0 Reputation points

2024-09-17T09:25:19.93+00:00

@Mahesh Kurva

This is the error I see when the script runs.
Waleed Asad 0 Reputation points

2024-09-17T11:03:17.3666667+00:00

@Mahesh Kurva

I have managed to update the server parameters from single to flexible server. But the issue I am seeing is it cannot connect to the database until we add all the Client IP address in the firewall from the range (0.0.0.0 - 255.255.255.255)

Do you know how we can we fix that?

Thanks
Mahesh Kurva 240 Reputation points Microsoft Vendor

2024-09-18T12:30:41.6533333+00:00
Hi @Waleed Asad,

It sounds like you are having trouble connecting to your Azure Database for PostgreSQL Flexible Server due to firewall restrictions.

By default, connections to the server are restricted to allowed IP addresses only. To connect to your server, you need to add your client IP address to the firewall rules.

Instead of adding the entire range of IP addresses (0.0.0.0 - 255.255.255.255) to the firewall, you can add your specific IP address to the firewall rules. This will ensure that only your IP address is allowed to connect to the server, which is more secure than allowing all IP addresses.

To add your IP address to the firewall rules, please refer the document: Connect from Azure.

If you are connecting from a different network or your IP address changes frequently, you can also add a range of IP addresses to the firewall rules. This will allow connections from any IP address within the specified range.
For more information, please refer the document: https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-manage-firewall-portal.

I hope this information helps. Please do let us know if you have any further queries.
Mahesh Kurva 240 Reputation points Microsoft Vendor

2024-09-19T12:37:44.1766667+00:00

Hi @Waleed Asad,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Waleed Asad 0 Reputation points

2024-09-19T12:46:01.7166667+00:00

@Mahesh Kurva

I did try the steps above, but the main issue is that the azure app services cannot access the flexible database.

I had added the outbound IP addresses for one of our app services into firewall settings in the flexible db and that worked.

I would like to understand why can't the azure services auatomatically connect the flexible db.

I have also checked the box below but that does not resolve the issue.

Best

Waleed
Mahesh Kurva 240 Reputation points Microsoft Vendor

2024-09-20T12:45:11.0833333+00:00
Hi @Waleed Asad,

Regarding your question:

The main issue is that the azure app services cannot access the flexible database.

Allow public access from any Azure services to this server" option only allows access from Azure services that are configured to use Azure Virtual Network (VNet) Service Endpoints. This option does not automatically add the IP addresses of all Azure services to the firewall rules.

When you enable this option, it allows Azure services to access your Flexible Database through a trusted Azure service IP address, rather than the public IP address of the service. However, this does not bypass the firewall rules, and you still need to add the IP addresses of the specific Azure services that need to access your database to the firewall rules.

For more Information, please refer the document: https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-firewall-rules#connect-from-the-internet.

I hope this information helps. Please do let us know if you have any further queries.
Mahesh Kurva 240 Reputation points Microsoft Vendor

2024-09-23T18:07:32.6033333+00:00

Hi @Waleed Asad,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Waleed Asad 0 Reputation points

2024-09-24T10:07:09.8733333+00:00
@Mahesh Kurva

Thanks for getting back to me.

Based on the following answer:

When you enable this option**, it allows Azure services to access your Flexible Database through a trusted Azure service IP address**, rather than the public IP address of the service. However, this does not bypass the firewall rules, and you still need to add the IP addresses of the specific Azure services that need to access your database to the firewall rules.

In the past we did not have to add any outbound IP address for our Azure app services to single server database. Has this changed now with flexible where we need to specifically add those IP addresses to flexible server firewall?
Mahesh Kurva 240 Reputation points Microsoft Vendor

2024-09-24T23:12:22.2933333+00:00

Hi @Waleed Asad,

Yes, with public access, the Azure Database for PostgreSQL flexible server instance is accessed through a public endpoint. By default, the firewall blocks all access to the server. To specify which IP hosts can access the server, you create server-level firewall rules. Firewall rules specify allowed public IP address ranges. The firewall grants access to the server based on the originating IP address of each request.

For more information, please refer the document: https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-firewall-rules.

I hope this information helps. Please do let us know if you have any further queries.
Waleed Asad 0 Reputation points

2024-09-25T10:52:14.5666667+00:00

Thanks for providing the information.
Mahesh Kurva 240 Reputation points Microsoft Vendor

2024-09-25T20:57:44.4133333+00:00

Hi @Waleed Asad,

Following up to see, whether the issue got resolved or still facing the issue. If the issue still persists, please do let us know.
Mahesh Kurva 240 Reputation points Microsoft Vendor

2024-09-26T21:39:05.9633333+00:00

Hi @Waleed Asad,

Just following up to see if you’ve had a chance to look into it further.In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

Connectivity and Timeout Issues After Second Migration with PostgreSQL from Single to flexible server

Your answer