How to migrate database users from Azure PostgreSQL single server to flexible server?

Pratik 20

Hello team,

I was using the migration tool provided in Azure portal to migrate databases from PostgreSQL single server to flexible server.The migration itself was successful and I could see my database and data in the flexible server.

The issue I am facing is the database users present in the single server are not migrated to the flexible server.

While going through help + support I found this statement "The tool takes care of the migration of data and schema. It doesn't migrate managed service features such as server parameters, connection security details, firewall rules, users, roles and permissions. In the later part of the document, we point you to docs that can help you perform the migration of users, roles and firewall rules from single server to flexible server." but I was not able to find where this information is present.

Where can I find the steps to migrate my users securely from single server to flexible server? Does microsoft provide some way to do it from Azure portal or using script securely?

Oury Ba-MSFT 19,581 Reputation points Microsoft Employee

2024-05-06T20:17:18.18+00:00
@Pratik Thank you for reaching out.

Azure Database for PostgreSQL – Single server to Flexible server migration tool does not migrate Entra users to the target server, so create all Entra users manually in advance.

Please note that below Items need to be manually migrated as these cannot be migrated using the migration tools. Please consider manual migration of the following items:

Firewall Rules

Server Parameters

Tags

Alerts

AAD Users

Microsoft Entra ID users present on your source server aren't migrated to the target server. To mitigate this limitation, visit Manage Microsoft Entra roles to manually create all Microsoft Entra users on your target server before triggering a migration.

Migration of user roles, ownerships, and privileges for the migrations service in Azure Database for PostgreSQL

https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/mastering-the-move-migrating-from-azure-database-for-postgresql/ba-p/3995226

I would suggest going through the links above to understand how the migration works.

Hope that helps.

Please let me know if you encounter any issues migrating the users.

Regards,

Oury
Pratik 20 Reputation points

2024-05-07T03:26:01+00:00
Hello @Oury Ba-MSFT Thanks for the quick response.

I went through the links shared and found in the link that DB users (not AAD users) should be migrated when I am migrating from Azure PostgreSQL single server to flexible server. Link: https://learn.microsoft.com/en-us/azure/postgresql/migrate/migration-service/concepts-known-issues-migration-service#common-limitations

When I migrated my PostreSQL single server version 11 to flexible server version 16 I am unable to see database users on flexible server that were created.

When I run the below query on single server I can see 30+ users but on flexible server I see only 3 (azuresu, postgres and replication user)

select * from pg_catalog.pg_user;

As per the following statement in the common limitations link provided before I would expect DB users to be migrated since I am migrating from single server to flexible server: "The migration service only supports users and roles migration when the source is Azure Database for PostgreSQL single server."

Is there any setting configuration that needs to be done pre-migration for DB users?

Accepted answer

Oury Ba-MSFT 19,581 Reputation points Microsoft Employee

2024-05-08T16:13:10.7133333+00:00

Pratik

Any configuration to migrate the db users to the target db.? Any other configuration that needs to be done pre-migration for db users.?

The migration service supports user roles migration when the source is Azure Database for PostgreSQL Single Server. However, this feature is currently disabled for PostgreSQL version 16 servers **(**which includes Flexible Server). In other words, if you’re migrating from Single Server to Flexible Server, the user roles won’t be automatically migrated.

Option 1: The feature will be made available In June. You can choose to wait until this feature is enabled for PostgreSQL 16.

Option 2: Alternatively, you can choose to migrate to a lower version, such as PostgreSQL version 15. Once the migration is completed, you can then use our major version upgrade to upgrade the server to version 16. However, before proceeding with this option, I recommend checking our documentation for further guidance and validate the limitations Major version upgrades in Azure Database for PostgreSQL - Flexible Server.

hope that helps

Regards,

Oury
Please sign in to rate this answer.

1 person found this answer helpful.
Pratik 20 Reputation points

2024-05-09T17:19:27.01+00:00

Do we have a date as to when in June this functionality to migrate user roles will be enabled in PostgreSQL 16?

This will help me plan the migration date and avoid going through Option 2.

Pratik 20 Reputation points

2024-05-14T04:30:58.6033333+00:00

@Oury Ba-MSFT do we have an approximate date as to when we will support migrating user roles in PostgreSQL 16?

Also a follow up question, consider be below scenario

I migrate few of my databases to flexible server PostgreSQL 15 then upgrade to PostgreSQL 16 so that I get my DB users and roles.

Later on support is enabled by Microsoft in PostgreSQL 16 to migrate users sometime in June.

Will any new databases I migrate to PostgreSQL 16 (which was upgraded before the support from 15 to 16 version) have the DB users and roles migrated?

Oury Ba-MSFT 19,581 Reputation points Microsoft Employee

2024-05-16T00:49:53.6833333+00:00

Pratik Thank you for accepting the answer.Thank you for your patience while I get more information on the above ask internally.

Regards,

Oury

Oury Ba-MSFT 19,581 Reputation points Microsoft Employee

2024-05-20T19:32:27.9466667+00:00

Pratik

Thank you for being patient while checking on your query.

I have checked internally, and this is currently being delayed due to some several factors.

If you migrate to PG 15 and perform a major version upgrade to PG 16, this will preserve users/roles along with passwords, ownerships, and permissions. You can use this solution until we have the final fix in place for PG 16.

Let me know if this answers your question.

Regards,

Oury

Petru Dumuta 11 Reputation points

2024-11-04T14:02:32.1333333+00:00

Hi @Oury Ba-MSFT ,Do you have an update on when this limitation for Postgres 16 will be addressed? I've just tried migrating from a Standalone Postgres 11 DB to a Postgres 16 Flex DB and roles are still not migrated.

Thanks,

Petru
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to migrate database users from Azure PostgreSQL single server to flexible server?

0 additional answers

Your answer