Migrating Azure Database for PostgreSQL Elastic Cluster (single-node, no sharding) to Single Server/Flexible Server — recommended path + role/password migration

Question

Migrating Azure Database for PostgreSQL Elastic Cluster (single-node, no sharding) to Single Server/Flexible Server — recommended path + role/password migration

Aashay Thakkar 0

We have an Azure Database for PostgreSQL - Elastic Cluster running as a single node — we don't use or require Citus/sharding features. We need to migrate this to a standard (non-elastic) Postgres server, ultimately to end up on Postgres 17 (see our related question here: [link to Post 1]).

Questions:

What's the Microsoft-recommended/supported path to migrate an Elastic Cluster's data to Single Server or Flexible Server — native pg_dump/pg_restore against the coordinator, Azure Database Migration Service, or something else?
How do we migrate Postgres roles along with their password hashes (not resetting passwords) given Azure's managed role model (no true superuser, restricted access to pg_authid)? Specifically: does pg_dumpall --globals-only work against an Elastic Cluster coordinator, and are there Azure-reserved roles we need to exclude before restoring on the target to avoid conflicts?

Any guidance or gotchas specific to Elastic Cluster → Flexible Server migrations would help.

Peram Venkateswara Reddy 260 Reputation points Microsoft External Staff Moderator

2026-08-12T12:45:31.94+00:00
Hi @Aashay Thakkar , Hope you are doing well.

Taking your questions in order, and starting with the one that has a hard answer you'll need to plan around.

Short version: you cannot migrate password hashes. Passwords must be reset on the target.

Recommended migration path

First — Single Server is not an option. Azure Database for PostgreSQL – Single Server was retired on March 28, 2025. Your target is Flexible Server.

Elastic Clusters are built on Flexible Server with the Citus extension, so a single-node cluster with no distributed tables is effectively a Flexible Server with Citus loaded. pg_dump / pg_restore against the coordinator endpoint is the right path for your case. Two things to watch:

Tool versioning. Since you're targeting v17 from a v16 source, use v17 client utilities. From the docs: "use the pg_dump, psql, pg_restore and pg_dumpall utilities that are either of the same major version or a higher major version than the database server you are exporting data from or importing data to... If your target server has a higher major version than the source server, use utilities that are either the same major version or higher than the target server."

One database per dump. "pg_dump can export only one database at a time." Loop if you have several.

Drop Citus objects first**.** Since you don't use sharding, confirm no tables are distributed (SELECT * FROM citus_tables;) and drop the citus extension before dumping, so the restore doesn't reference an extension you won't enable on the target.

Before cutover: disable high availability and read replicas on the target. Per the migration prerequisites, "These features should be enabled only after the migration is completed." Turn them on after you've validated.

Roles and password hashes — the definitive answer

Password hashes cannot be exported from your source. The documentation is unambiguous:

"In a flexible server environment, enhanced security measures mean users don't have access to the pg_authid table, which is where role passwords are stored. This restriction affects how you perform a roles dump, as the standard pg_dumpall -r command attempts to access this table for passwords and fail due to lack of permission."

And again on the restore side:

"This does not apply if your source server is a flexible server, as flexible server does not allow dumping passwords for users due to enhanced security measures."

So pg_dumpall --globals-only (or -r) will fail against your coordinator unless you add --no-role-passwords — and that flag is precisely what strips the passwords. There is no supported way around this. Plan on reissuing credentials as part of cutover: build the new password set in your secret store ahead of time and swap application connection strings at the same moment you flip the endpoint.

Working command:

pg_dumpall -r --no-role-passwords \ -h <coordinator>.postgres.database.azure.com \ -U <admin_user> > roles.sql

Azure-reserved roles to exclude — yes, here's the list

roles.sql will contain service roles and superuser attributes that will conflict or fail on the target. The documented cleanup:

sed -i '/azure_superuser/d; /azure_pg_admin/d; /azuresu/d; /^CREATE ROLE replication/d; /^ALTER ROLE replication/d; /^ALTER ROLE/ {s/NOSUPERUSER//; s/NOBYPASSRLS//;}' roles.sql

That strips azure_superuser, azure_pg_admin, azuresu, the replication role, and the NOSUPERUSER / NOBYPASSRLS attributes. The Flexible Server restore guidance extends the exclusion list to azure_su, localadmin, and Entra Admin as well — "These specific service roles are automatically recreated when administrators are added to the new Flexible server."

Since your source is an Elastic Cluster, also grep the file for Citus-internal roles before restoring.

Then restore roles before the data:

psql -f roles.sql -h <target>.postgres.database.azure.com -U <admin_user>

Note the ordering caution in the docs: avoid duplication between roles.sql and your data dump.

Testing without building a second environment

Rather than standing up a test Elastic Cluster, use the migration service dry run feature — the Elastic Cluster docs recommend it exactly so "you don't need to create a separate test environment." If you do want an isolated copy, use point-in-time restore (PITR) off your existing cluster.

On your companion question

This connects to your v16→v17 in-place upgrade question. If dump/restore into a fresh v17 Flexible Server gets you off Citus and onto 17 in one operation, you may not need the in-place Elastic Cluster upgrade at all — worth deciding which of the two threads you actually need resolved before you build the runbook.

References:

Migrate your PostgreSQL database by using dump and restore — https://learn.microsoft.com/azure/postgresql/migrate/how-to-migrate-using-dump-and-restore

Elastic clusters in Azure Database for PostgreSQL flexible server — https://learn.microsoft.com/azure/postgresql/elastic-clusters/concepts-elastic-clusters

Benefits of Azure Database for PostgreSQL with Elastic Cluster (dry run, PITR) — https://learn.microsoft.com/azure/cosmos-db/postgresql/concepts-benefits-database-postgresql

Restore roles and users for restored databases (full exclusion list) — https://learn.microsoft.com/azure/backup/restore-azure-database-postgresql-flex

Extensions and modules — https://learn.microsoft.com/azure/postgresql/extensions/concepts-extensions

Kind Regards,
Microsoft Support Team.

1 answer

Your answer

Answer 1

Hi @Aashay Thakkar

Peram has covered the main migration path and the limitation around migrating password hashes. I would also like to highlight a few additional considerations for the migration:

Since the target is PostgreSQL 17 Flexible Server, make sure the PostgreSQL client utilities used for the migration are compatible with the target version.
For a single-node Elastic Cluster where Citus/sharding isn't being used, verify that there are no distributed tables or Citus-dependent objects before removing Citus-related dependencies.
Elastic Cluster migration documentation currently supports logical migration using pg_dump/pg_restore or pgcopydb.
Server-level settings/parameters should be reviewed separately because they aren't automatically carried over as part of a database dump. They should be compared and configured on the target as required.
Users and roles need separate consideration during migration. Azure Database for PostgreSQL Flexible Server has managed service roles and does not provide customer access to the PostgreSQL superuser.
As already mentioned, password hashes cannot be migrated from Flexible Server, so application/service credentials should be planned for reset or rotation during cutover.
Before production cutover, validate the restored databases, extensions, users/roles, permissions, application connectivity, and any required server parameters.
If HA or read replicas are required on the new Flexible Server, it is recommended to enable them after the migration and validation are completed.

For testing, it would also be useful to perform a dry run/non-production migration first and validate the application workload before the final cutover

References:

I hope this helps. Please let me know if you have any questions.