Task 02: Complete offline and online migration

You’ll finalize necessary configurations and perform an offline migration of the Adventureworks database. This approach is suitable when a brief service interruption is acceptable.

1. From the Azure portal, on the top global search bar, enter and select Private DNS zones.

   

2. Select the azuredb@lab.labInstance.Id.private.postgres.database.azure.com entry.

3. On the left service menu, select DNS Management, then select Recordsets.

4. Identify the private IP address listed under Value. This should be the only address, which belongs to the new server.

   

   Your IP may differ from the screenshot.

5. Enter the IP address below for future use:

   @lab.TextBox(privateIP)

6. Select Windows Explorer from the taskbar, and then go to C:\Program files\PostgreSQL\16\data.

7. Right-click the pg_hba.conf file, select Open with, select Notepad, then select OK.

   You’ll have to select More apps to see Notepad.

8. In Notepad, go to the bottom of the pg_hba.conf file and add an entry under # IPv4 local connections with the following:

   Item	Value
   TYPE	host
   DATABASE	all
   USER	postgres
   ADDRESS	@lab.Variable(privateIP)/24
   METHOD	scram-sha-256

   The ADDRESS listed above is the address from the earlier text box. If it’s blank here, ensure the text box from step 4 is filled out.

   

9. Ensure the file looks like the image above, with the exception of the ADDRESS, if it’s different.

10. Save and close the file.

11. In the Azure portal, return to the home page by selecting the Home breadcrumb link in the upper left.

12. Under Resources, in the Recent list, select the azuredb@lab.LabInstance.Id Azure Database.

13. On the azuredb@lab.LabInstance.Id page, on the left service menu, select Settings, then select Server parameters.

14. On the Server parameters tab, enter azure.extensions in the search filter.

15. For the azure.extensions parameter, select the Value dropdown, then select the checkboxes for:

    • TABLEFUNC
    • UUID-OSSP

16. Select Save.

    

17. Once the deployment of the parameters is complete, select the azuredb@lab.LabInstance.Id - Server parameters breadcrumb link in the upper left to return to the server page.

18. On the azuredb@lab.LabInstance.Id page, on the left service menu, select Migration.

19. On the Migration page, select + Create.

    

20. On the Migrate PostgreSQL to Azure Database for PostgreSQL Flexible Server page, configure the following settings:

    Item	Value
    Migration name	offlinemigration
    Source server type	On-premise Server
    Migration option	Validate and Migrate
    Migration mode	Offline

    

21. Select Next: Select Runtime Server >.

22. On the Select Runtime Server step, leave the default option of No selected, then select Next: Connect to source >.

23. From the Windows taskbar, open Windows PowerShell and run the ipconfig command.

24. Select and copy the IP address listed under the Vnet1 connection, then close PowerShell.

    Select the text and then select Ctrl+C to copy.

    

25. Enter the IP address here for future use:

    @lab.TextBox(VPN)

26. In Azure portal, on the Connect to source tab, configure the following settings:

    Item	Value
    Server name	@lab.Variable(VPN)
    Port	5432
    Server admin login name	postgres
    Password	Passw0rd!
    SSL mode	Prefer

    

    Your IP may differ from the screenshot.

27. Select Connect to source.

28. Once the connection test completes, select Next: Select migration target >.

29. On the Select migration target tab, enter Passw0rd! in the Password field, then select Connect to target.

30. Once the connection test completes, select Next: Select database(s) for migration >.

31. On the Select database(s) for migration tab, select the checkbox for Adventureworks, then select Next: Summary >.

32. Select Start Validation and Migration.

    This process may take 3-4 minutes.

    In an offline migration, all applications that connect to the source instance are stopped. The tradeoff is that it requires less configuration and performs faster. This can be useful in situations where downtime or maintenance is expected.

33. Select Refresh on the azuredb@lab.LabInstance.Id - Migration page periodically until the offlinemigration job is complete.

34. Once the migration is complete, select offlinemigration.

    

35. Everything should show as Succeeded. At the bottom, select the link to the Adventureworks database.

36. In the Validation and migration details for Adventureworks flyout menu, select the Migration tab.

37. Verify the Database Status shows as Succeeded and the Number of tables copied shows as 68.

    

Next, you’ll configure connection options and execute an online migration, ensuring minimal disruption and enabling the city to continue providing public services without extended downtime.

1. From Windows File Explorer, go to C:\Program Files\PostgreSQL\16\data and open the postgresql.conf file with Notepad.

2. In the postgresql.conf file, select Ctrl+F to find the value wal_level.

3. Delete the # at the start of the line to uncomment the setting.

4. Set the wal_level value to logical, and then remove the comment to the right.

   

5. Save and close the file.

6. A server restart is required for the changes to take effect. Right-click the Windows icon in the lower left, select Shut down or sign out, then select Restart to reboot the VM.

7. Once the VM has rebooted, sign in again to Your VM username with Your VM password.

8. Reconnect to the Vnet1 VPN by selecting the network icon in the notification area and then selecting the Vnet1 connection.

   

9. From the VPN settings, select Vnet1 and then select Connect.

   

10. A separate Vnet1 connection window will open in the background. Switch to it and select Connect.

    A Window may show asking about privilege escalation, select Continue.

    

11. At the User Account Control prompt, enter Your VM password and select Yes.

12. Verify that Vnet1 is connected successfully. The word “Connected” should show under the Vnet1 connection.

    

13. If your Microsoft Edge session is not restored, open it and go to https://portal.azure.com.

14. If needed, sign in again to the Azure portal with the following:

    Item	Value
    Username	Your Azure Portal username
    Password	Your Azure Portal password

15. From the Azure portal home page, select the azuredb@lab.LabInstance.Id Azure Database from the Recent list of resources.

16. On the azuredb@lab.LabInstance.Id page, select Migration from the left service menu.

17. Select + Create.

    

18. On the Migrate PostgreSQL to Azure Database for PostgreSQL Flexible Server page, configure the following settings:

    Item	Value
    Migration name	onlinemigration
    Source server type	On-premise Server
    Migration option	Validate and Migrate
    Migration mode	Online

    When switching from Offline to Online for the Migration mode, you might notice an additional prerequisite is added below. The additional prerequisite for the online connection is to set the WAL_LEVEL to logical in the source server. This is the reason for the extra configuration and server reboot earlier.

19. Select Next: Select Runtime Server >.

20. On the Select Runtime Server tab, leave the default option of No selected and select Next: Connect to source >.

21. On the Connect to source tab, configure the following settings:

    Item	Value
    Server name	@lab.Variable(VPN)
    Port	5432
    Server admin login name	postgres
    Password	Passw0rd!
    SSL mode	Prefer

    

22. Select Connect to source.

23. Once the connection test completes, select Next: Select migration target >.

24. On the Select migration target tab, enter Passw0rd! in the Password field, then select Connect to target.

25. Once the connection test completes, select Next: Select database(s) for migration >.

26. On the Select database(s) for migration tab, select the checkbox for Adventureworks and then select Next: Summary >.

    After selecting the Adventureworks database, there will be an additional checkbox. This is authorizing Azure to overwrite the data from the previous migration with the data from this one. In this case, the data is the same and you can proceed without fear of data loss. However, in a production environment, pay attention to this setting to determine when you’re about to overwrite data.

27. Select Start Validation and Migration.

    This process may take 3-4 minutes.

    An online migration has more steps and takes a bit more time, but the source server applications aren’t stopped in the process. Data is copied to the target server, then a cutover is performed to finalize the migration with no downtime in between.

28. Select Refresh on the azuredb@lab.LabInstance.Id - Migration page periodically until the onlinemigration job status shows as Waiting For Use.

29. From the Windows taskbar, select Windows PowerShell and run the following command to connect to the newly migrated Adventureworks database:

     psql -h @lab.Variable(privateIP) -p 5432 -U postgres Adventureworks 
    

30. Enter Your VM password for the password.

    The text cursor for password entry does not move as you type. You may need to select Enter a couple times.

31. Once connected to the Adventureworks database through PowerShell, run the following command:

     SELECT name FROM humanresources.department; 
    

    

    This command should return a list of 16 rows, showing the records in the department table. Since the online migration hasn’t been completed yet, the data is being pulled from the offline migration performed earlier. With the online migration method, data can be added even after the migration has started, as long as the cutover hasn’t been performed yet. Let’s add a new row now to test this.

32. From the PowerShell connection to Adventureworks, run the following command to add a new row to the department table.

     INSERT INTO humanresources.department VALUES (17, 'Logistics', 'Inventory Management', DEFAULT); 
    

33. Minimize the PowerShell connection, you’ll return to it later.

34. Return to the Azure portal and the azuredb@lab.LabInstance.Id - Migration page.

35. Select onlinemigration from the list.

    

36. On the onlinemigration page, select Cutover, then select Yes.

    This process may take 5-7 minutes to complete.

    

37. Select Refresh on the onlinemigration page until the Validation status shows as Succeeded.

38. At the bottom of the onlinemigration page, select the link to the Adventureworks database.

39. In the Validation and migration details for Adventureworks flyout menu, select the Migration tab.

40. Verify the Database Status shows as Succeeded and the Number of tables copied shows as 69.

    

41. Return to the azuredb@lab.LabInstance.Id - Migration page selecting the breadcrumb link in the upper left.

42. Refresh the azuredb@lab.LabInstance.Id - Migration page and verify that the onlinemigration shows as Succeeded.

    

43. From the Windows taskbar, select Windows PowerShell. You should still be connected to the Adventureworks database. If not, run the following command:

     psql -h @lab.Variable(privateIP) -p 5432 -U postgres Adventureworks 
    

    Enter Your VM password for the password.

44. Once connected to the Adventureworks database through PowerShell, run the following command:

     SELECT name FROM humanresources.department; 
    

    

    You’re running this command again to check the row names in the department table. At the bottom of the results, notice that the Logistics record was migrated successfully.

Conclusion

Congratulations! You’ve successfully completed this task and have completed the Migrate an on-premises PostgreSQL server to Azure Database for PostgreSQL lab.