We have a single server which says auto migration is scheduled. But we are not able to view who scheduled the migration automatically. please help. After single to flexi migration, can we still use single server if there are any issues in target flexi servers? In case of several databases (10), which is best online or offline migration? Will online or offline migration, also migrates users, roles, foreign data wrappers? How long will the migration takes for db size around 70GB? Thanks for the support! Regards, Jai

Hi @ Jaiyasree Venkataramanan , Welcome to the Microsoft Q&A and thank you for posting your questions here. As I understand it, you're dealing with a database migration and have a few specific questions related to the process. I'm happy to help you with them. We have a single server which says auto migration is scheduled. But we are not able to view who scheduled the migration automatically. please help. Auto migration from a single server to a flexible server is typically scheduled by the Azure service itself. You can check the migration schedule and details in the Azure portal under the “Migration” blade of your single server instance. For more information, please refer the documents: https://zcusa.951200.xyz/en-us/azure/postgresql/migrate/automigration-single-to-flexible-postgresql https://zcusa.951200.xyz/en-us/azure/postgresql/migrate/migration-service/tutorial-migration-service-single-to-flexible?tabs=portal%2Coffline After single to flexi migration, can we still use single server if there are any issues in target flexi servers? After migrating to a flexible server, the single server is generally set to read-only mode and eventually decommissioned. If there are issues with the target flexible server, you would need to address them directly on the flexible server as the single server won’t be available for write operations. In case of several databases (10), which is best online or offline migration? Online Migration : Minimizes downtime by keeping the source database online during the migration. This is ideal if you need continuous availability. Offline Migration : Involves taking the source database offline, which can be faster but results in downtime. This might be suitable if you can afford a maintenance window. For more information, please refer the documents: https://zcusa.951200.xyz/en-us/azure/dms/faq-mysql-single-to-flex https://zcusa.951200.xyz/en-us/azure/dms/resource-scenario-status#offline-vs-online-migration . Will online or offline migration, also migrates users, roles, foreign data wrappers? The migration service supports migration for users and roles only when the source is Azure Database for PostgreSQL - Single Server. Foreign Data Wrappers: If your database uses this extension, the following steps must be performed to ensure a successful migration. Temporarily remove (unlink) the foreign data wrapper on the source instance. Perform data migration of the rest by using the migration service. Restore the foreign data wrapper roles, user, and links to the target after migration. For more information, please refer the documents: https://zcusa.951200.xyz/en-us/azure/postgresql/migrate/migration-service/best-practices-migration-service-postgresql#database-with-postgres_fdw-extension https://zcusa.951200.xyz/en-us/azure/postgresql/migrate/migration-service/concepts-user-roles-migration-service https://www.postgresql.org/docs/current/postgres-fdw.html How long will the migration takes for db size around 70GB? The time required for migrating a 70GB database can vary based on factors like network speed, server performance, and the complexity of the data. Generally, it could take anywhere from a few hours to a day. Hope this helps. Do let us know if you any further queries. 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.

Will Single to Flexi postgres migration migrates users too? and if there are any foreign data wrappers?

Jaiyasree Venkataramanan 0

We have a single server which says auto migration is scheduled. But we are not able to view who scheduled the migration automatically. please help.
After single to flexi migration, can we still use single server if there are any issues in target flexi servers?
In case of several databases (10), which is best online or offline migration?
Will online or offline migration, also migrates users, roles, foreign data wrappers?
How long will the migration takes for db size around 70GB?

Thanks for the support!

Regards,

Jai

Mahesh Kurva 1,470 Reputation points Microsoft Vendor

2024-11-29T19:50:17.3866667+00:00

Hi @Jaiyasree Venkataramanan,

Just checking in to see if the below answer 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.

1 answer

Mahesh Kurva 1,470 Reputation points Microsoft Vendor

2024-11-27T20:40:23.63+00:00
Hi @Jaiyasree Venkataramanan,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

As I understand it, you're dealing with a database migration and have a few specific questions related to the process. I'm happy to help you with them.

We have a single server which says auto migration is scheduled. But we are not able to view who scheduled the migration automatically. please help.

Auto migration from a single server to a flexible server is typically scheduled by the Azure service itself. You can check the migration schedule and details in the Azure portal under the “Migration” blade of your single server instance.

For more information, please refer the documents:

https://zcusa.951200.xyz/en-us/azure/postgresql/migrate/automigration-single-to-flexible-postgresql
https://zcusa.951200.xyz/en-us/azure/postgresql/migrate/migration-service/tutorial-migration-service-single-to-flexible?tabs=portal%2Coffline

After single to flexi migration, can we still use single server if there are any issues in target flexi servers?

After migrating to a flexible server, the single server is generally set to read-only mode and eventually decommissioned. If there are issues with the target flexible server, you would need to address them directly on the flexible server as the single server won’t be available for write operations.

In case of several databases (10), which is best online or offline migration?

Online Migration: Minimizes downtime by keeping the source database online during the migration. This is ideal if you need continuous availability.

Offline Migration: Involves taking the source database offline, which can be faster but results in downtime. This might be suitable if you can afford a maintenance window.

For more information, please refer the documents:

https://zcusa.951200.xyz/en-us/azure/dms/faq-mysql-single-to-flex

https://zcusa.951200.xyz/en-us/azure/dms/resource-scenario-status#offline-vs-online-migration.

Will online or offline migration, also migrates users, roles, foreign data wrappers?

The migration service supports migration for users and roles only when the source is Azure Database for PostgreSQL - Single Server.

Foreign Data Wrappers:

If your database uses this extension, the following steps must be performed to ensure a successful migration.

Temporarily remove (unlink) the foreign data wrapper on the source instance.

Perform data migration of the rest by using the migration service.

Restore the foreign data wrapper roles, user, and links to the target after migration.

For more information, please refer the documents:

https://zcusa.951200.xyz/en-us/azure/postgresql/migrate/migration-service/best-practices-migration-service-postgresql#database-with-postgres_fdw-extension

https://zcusa.951200.xyz/en-us/azure/postgresql/migrate/migration-service/concepts-user-roles-migration-service

https://www.postgresql.org/docs/current/postgres-fdw.html

How long will the migration takes for db size around 70GB?

The time required for migrating a 70GB database can vary based on factors like network speed, server performance, and the complexity of the data. Generally, it could take anywhere from a few hours to a day.

Hope this helps. Do let us know if you any further queries.

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.
Please sign in to rate this answer.
Jaiyasree Venkataramanan 0 Reputation points

2024-11-28T05:26:19.3366667+00:00

Thanks for your answers. I have a single server and it is production and i cannot take a risk directly doing the migration in production.

Can i clone the server? will it create another single server?

Mahesh Kurva 1,470 Reputation points Microsoft Vendor

2024-11-28T19:26:29.35+00:00

Hi @Jaiyasree Venkataramanan,

Yes, you can clone your production server to create a test environment before performing the migration. Here’s how you can do it:

Create a Backup: First, create a backup of your single server.

Restore the Backup: Use the backup to restore a new single server instance. This will create a clone of your production server.

This cloned server can then be used to test the migration process without affecting your production environment.

For more information, please refer the document: https://zcusa.951200.xyz/en-us/azure/postgresql/migrate/how-to-migrate-using-dump-and-restore?tabs=psqly

I hope this information helps. Please do let us know if you have any further queries.

Mahesh Kurva 1,470 Reputation points Microsoft Vendor

2024-12-02T15:45:17.3366667+00:00

Hi @Jaiyasree Venkataramanan,

Following up to see if the above answer was helpful. 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.

Jaiyasree Venkataramanan 0 Reputation points

2024-12-02T17:37:46.4533333+00:00

Hello,

The links shared says completely about taking a dump and restore of Flexible server.

My question was regarding taking a dump and restore of single server.

Also after migration, will the usesysid in pg_user remains the same?

Will Flexi migration also migrated the users and roles?

Thanks,

Jai

Mahesh Kurva 1,470 Reputation points Microsoft Vendor

2024-12-03T12:30:30.85+00:00

Hi @Jaiyasree Venkataramanan,

Thanks for the response.

My question was regarding taking a dump and restore of single server.

For taking a dump and restoring a single PostgreSQL server, you can use the pg_dump and psql commands.

For more information, please refer the documents:

https://zcusa.951200.xyz/en-us/azure/postgresql/migrate/how-to-migrate-using-dump-and-restore?source=recommendations&tabs=pgrestore

https://startup-house.com/blog/dump-and-restore-postgresql-database

Also after migration, will the usesysid in pg_user remains the same?

After migration, the usesysid in pg_user should remain the same as long as the user roles are migrated correctly. This is because usesysid is a unique identifier for each user in PostgreSQL.

Will Flexi migration also migrated the users and roles?

When migrating from a single server to a flexible server, users and roles can be migrated using the pg_dumpall command, which includes roles and database configurations:

pg_dumpall -U [username] -f [output_file]

This command creates a dump of all databases, roles, and configurations.

For more information, please refer the document: Dumping users and roles with pg_dumpall -r.

I hope this information helps. Please do let us know if you have any further queries.

Mahesh Kurva 1,470 Reputation points Microsoft Vendor

2024-12-04T16:26:46.4+00:00

Hi @Jaiyasree Venkataramanan,

Following up to see if the above answer was helpful. 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.

Jaiyasree Venkataramanan 0 Reputation points

2024-12-05T15:46:42.4233333+00:00

Hello Mahesh,

Thanks and your answer was help. We tried a trial migration today and none of the users in the source db is migrated to flexi server.

But the article you forwarded says the below:

The migration service supports migration for users and roles only when the source is Azure Database for PostgreSQL - Single Server.

Please confirm.

Mahesh Kurva 1,470 Reputation points Microsoft Vendor

2024-12-05T16:07:53.4666667+00:00

Hi @Jaiyasree Venkataramanan,

Yes, that’s correct. The migration service supports migrating users and roles only when the source is Azure Database for PostgreSQL - Single Server.

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.

Jaiyasree Venkataramanan 0 Reputation points

2024-12-05T17:20:41.71+00:00

As mentioned in my previous comment, we tried trial migration today. But we dont see users and roles getting migrated. Only default users and roles related to postgres is available in the newly created target flexi server.

Please advise on who can help us in debugging this issue

Mahesh Kurva 1,470 Reputation points Microsoft Vendor

2024-12-06T09:44:13.0433333+00:00

Hi @Jaiyasree Venkataramanan,

Please refer the thread link, it might help you: https://zcusa.951200.xyz/en-us/answers/questions/1663535/how-to-migrate-database-users-from-azure-postgresq.
I hope this information helps. Please do let us know if you have any further queries.

Mahesh Kurva 1,470 Reputation points Microsoft Vendor

2024-12-09T09:39:50.88+00:00

Hi @Jaiyasree Venkataramanan,

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.

Jaiyasree Venkataramanan 0 Reputation points

2024-12-09T12:58:09.5633333+00:00

Hello.. Thanks for following up. The users and roles are getting migrated in version 15. But the oid is regenerated

We also tried to dump and restore the users and roles. But we get an error that the db user is not superuser, so we cannot create users

Pls suggest

Mahesh Kurva 1,470 Reputation points Microsoft Vendor

2024-12-10T13:19:22.0166667+00:00

Hi @Jaiyasree Venkataramanan,

The users and roles are getting migrated in version 15. But the oid is regenerated

When you migrate users and roles, the OIDs (Object Identifiers) can be regenerated. This is a known behavior in PostgreSQL, as OIDs are unique identifiers for database objects and can change during migration. Unfortunately, there’s no direct way to preserve OIDs during a dump and restore process.

We also tried to dump and restore the users and roles. But we get an error that the db user is not superuser, so we cannot create users

Since superuser privileges are managed by Microsoft, you might not have direct control over them.

I hope this information helps. Please do let us know if you have any further queries.

Mahesh Kurva 1,470 Reputation points Microsoft Vendor

2024-12-11T14:22:06.6266667+00:00

Hi @Jaiyasree Venkataramanan,

Following up to see if the above answer was helpful. 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.
Sign in to comment

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

Share via

Will Single to Flexi postgres migration migrates users too? and if there are any foreign data wrappers?

1 answer

Your answer