I recently migrated a small production database from SQL Server to PostgreSQL for my URL forwarder project, Elf. The application already supported PostgreSQL, so the migration sounded straightforward:

  1. Export data from SQL Server.
  2. Import it into PostgreSQL.
  3. Update the Azure App Service connection string.
  4. Restart the app.

In reality, the database migration itself was the easy part. The painful part was Azure App Service configuration and networking.

This post documents what happened, what failed, and what I learned.

The Setup

The source database was a small SQL Server database running on LocalDB:

(localdb)\MSSQLLocalDB
Database: elf

The target production PostgreSQL instance was running in Docker on an Azure VM:

PostgreSQL 18
Database: elfprod
Port: 5432

The application was hosted on Azure App Service for Linux containers:

App Service: elf-forwarder
Region: West US 2
Plan: PremiumV4 P0v4
Container: ediwang/elf:latest

The PostgreSQL VM lived in another Azure subscription and region.

Step 1: Test the Migration Locally First

Before touching production, I created a local PostgreSQL container and migrated the SQL Server data into it.

The source database had only a few tables:

Link:             20 rows
Tag:               3 rows
LinkTag:          26 rows
LinkTracking:  4,319 rows
ElfConfiguration: 0 rows

I wrote a small .NET migration utility using:

  • Microsoft.Data.SqlClient for SQL Server
  • Npgsql for PostgreSQL

The tool copied explicit IDs, preserved timestamps as UTC, reset PostgreSQL sequences, and then performed row-by-row, column-by-column verification.

The first local test passed. I also ran the app locally against PostgreSQL and verified the basic read/write flows.

Step 2: Production Migration

For production, I created a new PostgreSQL database:

elfprod

Then I applied the PostgreSQL schema already included in the project.

The first production migration attempt used normal parameterized INSERT statements. It worked locally, but over the public network it was too slow, especially for the LinkTracking table. Since the migration was inside a transaction, I stopped it safely and confirmed the target tables were still empty.

I then changed the migration utility to use PostgreSQL binary COPY.

That made the production migration complete in seconds.

Final migration result:

Link:             20 rows
Tag:               3 rows
LinkTag:          26 rows
LinkTracking:  4,319 rows
ElfConfiguration: 0 rows

The migration tool verified every row and every column against SQL Server successfully.

Step 3: Only Read-Only Validation in Production

After migration, I avoided any production write tests.

I only performed read-only validation:

  • table counts
  • foreign key consistency
  • sequence values
  • application health endpoint
  • read-only list endpoints

The application read tests confirmed:

Health endpoint: 200
Link total rows: 20
Tag rows: 3
Tracking rows: 4,319

So far, everything looked good.

Then I updated Azure App Service.

That is where things got ugly.

Problem 1: Azure App Service "Connection Strings" Did Not Behave as Expected

The application reads the database connection string like this:

configuration.GetConnectionString("ElfDatabase")

For SQL Server, using the App Service "Connection strings" blade had worked before.

After switching to PostgreSQL, the app failed during startup:

Failed to connect to database
System.InvalidOperationException: Database connection failed

The confusing part was that Database__Provider=PostgreSql was correctly set. The app was selecting the PostgreSQL provider. But the actual ElfDatabase connection string was empty from the application's point of view.

The fix was to stop relying on the App Service "Connection strings" mapping and explicitly set the ASP.NET Core configuration key as an App Setting:

Database__Provider=PostgreSql
ConnectionStrings__ElfDatabase=Host=<host>;Port=5432;Database=elfprod;Username=<user>;Password=<password>;SSL Mode=Disable;

For ASP.NET Core, this maps directly to:

ConnectionStrings:ElfDatabase

After that, the application received the correct connection string.

But it still could not connect.

Problem 2: The App Service Service Tag Was Not Enough

The PostgreSQL VM had an NSG rule allowing this source:

AppService.WestUS2

The App Service was in West US 2, so this looked correct.

The VM was healthy:

PostgreSQL listening on 0.0.0.0:5432
Docker port mapping: 0.0.0.0:5432->5432

The NSG had a rule allowing TCP 5432 from AppService.WestUS2.

Still, the app timed out when connecting to PostgreSQL.

The key detail was the App Service Plan:

PremiumV4 P0v4

For this plan, Azure reported empty values for:

outboundIpAddresses
possibleOutboundIpAddresses

That means I could not simply whitelist a stable outbound IP list. More importantly, the actual outbound traffic did not appear to be covered by the AppService.WestUS2 service tag in this case.

The fix was to allow a broader regional Azure service tag:

AzureCloud.westus2

The final NSG rule was:

Name:        PGSQL_AZURECLOUD_WUS2
Priority:    199
Source:      AzureCloud.westus2
Protocol:    TCP
Port:        5432
Access:      Allow

After adding that rule and restarting the App Service, the application started successfully.

The logs finally showed:

Database connection successful
Database is already initialized with all required tables
Application initialization completed successfully
Now listening on: http://[::]:8080

Lessons Learned

1. Test the database migration separately from the app migration

The actual SQL Server to PostgreSQL migration was not the hard part. It was controllable, verifiable, and repeatable.

Having a standalone migration tool was useful because it allowed me to verify the data independently from Azure App Service.

2. Use PostgreSQL COPY for real migrations

Parameterized inserts are fine for tiny local tests, but they are not ideal over a network.

PostgreSQL binary COPY made the production migration much faster while keeping the same verification logic.

3. Do not assume App Service "Connection strings" map cleanly for Linux containers

For ASP.NET Core apps, especially Linux container apps, I now prefer setting the exact configuration key explicitly:

ConnectionStrings__ElfDatabase

This avoids ambiguity around App Service connection string prefixes such as SQL Server, PostgreSQL, MySQL, or custom connection string mappings.

4. Service tags can be misleading

AppService.WestUS2 sounds like it should cover App Service outbound traffic from West US 2.

In this case, it did not solve the problem.

For PremiumV4, where App Service outbound IPs were not listed, AzureCloud.westus2 was the practical fix.

This is broader than I would like, but it was still limited to:

Region: West US 2
Protocol: TCP
Port: 5432

5. Health checks are not enough if startup can fail softly

The application could still listen on HTTP even after database initialization failed. That made a simple HTTP check insufficient.

The reliable signal was the application log:

Database connection successful
Database is already initialized with all required tables

For future improvements, the app should probably expose a stricter database-aware health check.

Final Result

After fixing both App Service configuration and NSG rules, the application successfully connected to the new PostgreSQL database.

The migration completed with verified data integrity:

Link:             20 rows
Tag:               3 rows
LinkTag:          26 rows
LinkTracking:  4,319 rows
ElfConfiguration: 0 rows

The final issue was not PostgreSQL, EF Core, or the migration logic.

It was Azure platform behavior that looked correct from the portal but did not match the runtime reality.

The main takeaway: when migrating database backends on Azure App Service, validate three things separately:

  1. The app's actual runtime configuration.
  2. The database server's listener and firewall.
  3. The real network path between App Service and the database.

Do not trust the names of Azure UI fields or service tags too much. Verify what the app actually sees and what the network actually allows.