Azure DB for PostgreSQL Migration Offline vs Online

Question

In my existing Azure Database for PostgreSQL - Single Server instance, I have three databases.....(MyDB1, MyDB2, and MyDB3). The migration service allows me to pick online mode or offline mode for the migration, and which databases I wish to migrate. I would like to migrate only "MyDB3", and I want to be in offline mode. If I choose offline mode, what happens to my Single Server instance and the databases that it holds? Does this mean the entire Database Server instance is placed in some kind of "Offline" mode, or is only the selected database "MyDB3" placed in some kind of offline mode, or it doesn't change any mode at all in the server or database(s) being migrated and it is just a rule that clients should not be connecting to the Server / or specific databases in the server?...Based on this statement here...

Taken from here...
https://learn.microsoft.com/en-us/azure/postgresql/migrate/migration-service/overview-migration-service-postgresql#migrate-to-azure-database-for-postgresql---flexible-server

I want to perform a test offline migration, using a test database in that server, but I'm afraid it will take my entire database server offline.

Please help me understand the scope of what the online/offline mode really means to the server and the individual databases in that server.

Answer 1

Hi @Vision Intelligence

Thanks for the question and using MS Q&A platform.

When you choose offline mode for migration in Azure Database for PostgreSQL, only the database you are migrating (MyDB3) will be affected.

Here's what happens:

Offline Migration:

During the migration, MyDB3 will be in a read-only state. This means that no changes can be made to it, and no new connections will be allowed. MyDB1 and MyDB2 will keep working normally, and people can still connect to them. The server itself stays up and running. Only the database you’re migrating is affected.

Online Migration:

The database you’re migrating will stay online and users can still connect and make changes while it’s being moved.

If you migrate MyDB3 in offline mode, just that database will go down temporarily. Other databases and the server itself will stay online and unaffected.

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.

 

Answer 2

Hello Vision Intelligence,

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

I understand that you would like to clarify your knowledge of Azure DB for PostgreSQL Migration Offline vs Online.

When migrating a database offline in Azure Database for PostgreSQL, consider the following:

Offline Migration

The database being migrated (MyDB3) will enter a read-only state, preventing any changes or new connections. To avoid inconsistencies, it's advisable to minimize connections to the entire server during this process. While MyDB1 and MyDB2 remain accessible, reducing server activity is recommended.

Online Migration

The migrating database stays online, allowing users to connect and make changes during the migration.

This is a recommendation for you:

1. Make sure the server is not heavily used during the test offline migration to prevent issues.
2. Notify users about the migration schedule and advise them to avoid making changes during this period.
3. Closely monitor the migration to ensure it completes successfully without affecting other databases.

For more details, refer to the Azure Database for PostgreSQL - Migration Service Overview and the Tutorial on migrating offline from an Azure VM or an on-premises PostgreSQL server. Also, Azure Database for PostgreSQL - Migration Service Overview

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.