{"id":7552,"date":"2021-08-04T15:30:02","date_gmt":"2021-08-04T10:00:02","guid":{"rendered":"https:\/\/opstree.com\/blog\/\/?p=7552"},"modified":"2025-11-20T15:36:04","modified_gmt":"2025-11-20T10:06:04","slug":"the-migration-of-postgresql-using-azure-dms","status":"publish","type":"post","link":"https:\/\/opstree.com\/blog\/2021\/08\/04\/the-migration-of-postgresql-using-azure-dms\/","title":{"rendered":"The Migration of Postgresql using Azure DMS"},"content":{"rendered":"\r\n

Azure Database Migration Service can be used to migrate the databases from an on-premises PostgreSQL instance to Azure Database for PostgreSQL with minimal downtime to the application.<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

<\/p>\r\n\r\n\r\n\r\n

Prerequisites:-<\/strong><\/h3>\r\n\r\n\r\n\r\n

Setup Postgresql on a azure VM<\/strong><\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Enable CDC and connection settings in source DB\u2019s postgresql.conf file<\/strong><\/p>\r\n\r\n\r\n\r\n

\r\n
\r\n

#\u200a\u2014\u200aConnection Settings –<\/p>\r\n<\/blockquote>\r\n\r\n\r\n\r\n

\r\n

listen_addresses = \u2018*\u2019 # what IP address(es) to listen on;<\/p>\r\n<\/blockquote>\r\n\r\n\r\n\r\n

\r\n

#\u200a\u2014\u200aSettings –<\/p>\r\n<\/blockquote>\r\n\r\n\r\n\r\n

\r\n

wal_level = logical # minimal, replica, or logical<\/p>\r\n<\/blockquote>\r\n\r\n\r\n\r\n

\r\n

# (change requires restart)<\/p>\r\n<\/blockquote>\r\n\r\n\r\n\r\n

\r\n

# REPLICATION<\/p>\r\n<\/blockquote>\r\n\r\n\r\n\r\n

\r\n

# Set these on the master and on any standby that will send replication data.<\/p>\r\n<\/blockquote>\r\n\r\n\r\n\r\n

\r\n

max_wal_senders = 10 # max number of walsender processes<\/p>\r\n<\/blockquote>\r\n\r\n\r\n\r\n

\r\n

# (change requires restart)<\/p>\r\n<\/blockquote>\r\n\r\n\r\n\r\n

\r\n

max_replication_slots = 5 # max number of replication slots<\/p>\r\n<\/blockquote>\r\n\r\n\r\n\r\n

\r\n

# (change requires restart)<\/p>\r\n<\/blockquote>\r\n<\/div><\/div>\r\n\r\n\r\n\r\n

Restart the postgres service.<\/p>\r\n\r\n\r\n\r\n

Download dvdrental sample data from google<\/strong>\u00a0<\/p>\r\n\r\n\r\n\r\n

Created one Database named test and importing dvdrental in to test Database.<\/p>\r\n\r\n\r\n\r\n

pg_restore -U postgres -d test dvdrental.tar<\/p>\r\n\r\n\r\n\r\n

Login to source DB and verify the data.<\/p>\r\n\r\n\r\n\r\n

psql \u201chost=akankshaVM port=5432 dbname=postgres user=postgres<\/em><\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Now our source DB is ready.<\/p>\r\n\r\n\r\n\r\n

Now create a Azure Database for PostgreSQL<\/a> which is managed by azure.<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Enable logical replication and update firewall settings<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Firewall setting-<\/p>\r\n\r\n\r\n\r\n

\"\"<\/figure>\r\n\r\n\r\n\r\n

Migrate the sample\u00a0schema<\/strong><\/h3>\r\n\r\n\r\n\r\n

Migrate the schema from source to destination using pg_dump utility<\/strong><\/p>\r\n\r\n\r\n\r\n

To complete all the database objects like table schemas, indexes and stored procedures, we need to extract schema from the source database and apply to the database.<\/p>\r\n\r\n\r\n\r\n

Use pg_dump -s command to create a schema dump file for a database.<\/p>\r\n\r\n\r\n\r\n

pg_dump -o -h hostname -U db_username -d db_name -s > your_schema.sql<\/p>\r\n\r\n\r\n\r\n

For example, to create a schema dump file for the test database:<\/p>\r\n\r\n\r\n\r\n

pg_dump -o -h akankshaVM -U postgres -d test -s -O -x > dvdrentalSchema.sql<\/strong><\/p>\r\n\r\n\r\n\r\n

Create an empty database in your target environment, which is Azure Database for PostgreSQL.<\/p>\r\n\r\n\r\n\r\n

Import the schema into the target database you created by restoring the schema dump file.<\/p>\r\n\r\n\r\n\r\n

psql -h hostname -U db_username -d db_name < your_schema.sql<\/p>\r\n\r\n\r\n\r\n

For example:-<\/p>\r\n\r\n\r\n\r\n

psql -h postgresql-destination.postgres.database.azure.com -U akanksha@postgresql-destination -d dvdrental < dvdrentalSchema.sql<\/strong><\/p>\r\n

[ looking for data warehousing services<\/a> ]<\/strong><\/p>\r\n\r\n\r\n\r\n

Register the Microsoft.DataMigration resource\u00a0provider<\/h3>\r\n\r\n\r\n\r\n
    \r\n
  1. Sign in to the Azure portal, select All services, and then select Subscriptions.<\/li>\r\n<\/ol>\r\n\r\n\r\n\r\n
    \"\"<\/figure>\r\n\r\n\r\n\r\n

    2. Select the subscription in which you want to create the instance of Azure Database Migration Service, and then select Resource providers.<\/p>\r\n\r\n\r\n\r\n

    \"\"<\/figure>\r\n\r\n\r\n\r\n

    3. Search for migration, and then to the right of Microsoft.DataMigration<\/a>, select Register.<\/p>\r\n\r\n\r\n\r\n

    \"\"<\/figure>\r\n\r\n\r\n\r\n

    Create a DMS\u00a0instance<\/h3>\r\n\r\n\r\n\r\n
      \r\n
    1. In the Azure portal, select + Create a resource, search for Azure Database Migration Service, and then select Azure Database Migration Service from the drop-down list.<\/li>\r\n<\/ol>\r\n\r\n\r\n\r\n
      \"\"<\/figure>\r\n\r\n\r\n\r\n

      2. On the Azure Database Migration Service<\/a> screen, select Create.<\/p>\r\n\r\n\r\n\r\n

      \"\"<\/figure>\r\n\r\n\r\n\r\n

      3. On the Create Migration Service screen, specify a name, the subscription, a new or existing resource group, and the location for the service.<\/p>\r\n\r\n\r\n\r\n

      4. Select an existing virtual network or create a new one.
      The virtual network provides Azure Database Migration Service with access to the source PostgreSQL server and the target Azure Database for       PostgreSQL<\/a> instance.<\/p>\r\n\r\n\r\n\r\n

      5. Select a pricing tier.
      \u00a0Premium SKU is recommended for online Data Migration<\/p>\r\n\r\n\r\n\r\n

      \"\"<\/figure>\r\n\r\n\r\n\r\n

      6. Select Review + create to create the service.
      \u00a0Service creation will complete within about 10 to 15 minutes.<\/p>\r\n\r\n\r\n\r\n

      Create a migration project<\/h3>\r\n\r\n\r\n\r\n

      After the service is created, locate it within the Azure portal, open it, and then create a new migration project.<\/p>\r\n\r\n\r\n\r\n

        \r\n
      1. In the Azure portal, select All services, search for Azure Database Migration Service, and then select Azure Database Migration Services.<\/li>\r\n<\/ol>\r\n\r\n\r\n\r\n
        \"\"<\/figure>\r\n\r\n\r\n\r\n

        2. On the Azure Database Migration Services screen, search for the name of Azure Database Migration Service instance that you created, select the instance, and then select + New Migration Project.<\/p>\r\n\r\n\r\n\r\n

        3. On the New migration project screen, specify a name for the project, in the Source server type text box, select PostgreSQL, in the Target server type text box, select Azure Database for PostgreSQL.<\/p>\r\n\r\n\r\n\r\n

        4. In the Choose type of activity section, select Online data migration.<\/p>\r\n\r\n\r\n\r\n

        \"\"<\/figure>\r\n\r\n\r\n\r\n

        Note<\/strong>
        Alternately, you can choose Create project only to create the migration project now and execute the migration later.<\/p>\r\n\r\n\r\n\r\n

        5. Select Save, note the requirements to successfully use Azure Database Migration Service to migrate data, and then select Create and run activity.<\/p>\r\n\r\n\r\n\r\n

        Specify source\u00a0details<\/h3>\r\n\r\n\r\n\r\n

        On the Add Source Details screen, specify the connection details for the source PostgreSQL instance.<\/p>\r\n\r\n\r\n\r\n

        \"\"<\/figure>\r\n\r\n\r\n\r\n

        Specify target\u00a0details<\/h3>\r\n\r\n\r\n\r\n

        On the Target details screen, specify the connection details for the target server, which is the pre-provisioned instance of Azure Postgresql to which the DVD Rentals schema was deployed by using pg_dump.<\/p>\r\n\r\n\r\n\r\n

        If the target database contains the same database name as the source database, Azure Database Migration Service selects the target database by default.<\/p>\r\n\r\n\r\n\r\n

        \"\"<\/figure>\r\n\r\n\r\n\r\n

        Select Save, and then on the Map to target databases screen, map the source and the target database for migration.<\/p>\r\n\r\n\r\n\r\n

        \"\"<\/figure>\r\n\r\n\r\n\r\n
        \"\"<\/figure>\r\n\r\n\r\n\r\n

        Select Save, and then on the Migration settings screen, accept the default values.<\/p>\r\n\r\n\r\n\r\n

        \"\"<\/figure>\r\n\r\n\r\n\r\n

        Select Save, on the Migration summary screen, in the Activity name text box, specify a name for the migration activity, and then review the summary to ensure that the source and target details match what you previously specified.<\/p>\r\n\r\n\r\n\r\n

        \"\"<\/figure>\r\n\r\n\r\n\r\n

        Run the migration<\/strong><\/h2>\r\n\r\n\r\n\r\n
          \r\n
        1. Select Run migration.
          The migration activity window appears, and the Status of the activity should update to show as Backup in Progress.<\/li>\r\n<\/ol>\r\n\r\n\r\n\r\n
          \"\"<\/figure>\r\n\r\n\r\n\r\n

          Monitor the migration<\/h3>\r\n\r\n\r\n\r\n
            \r\n
          1. On the migration activity screen, select Refresh to update the display until the Status of the migration shows as Complete.<\/li>\r\n<\/ol>\r\n\r\n\r\n\r\n
            \"\"<\/figure>\r\n\r\n\r\n\r\n

            2. When the migration is complete, under Database Name, select a specific database to get to the migration status for Full data load and Incremental data sync operations.<\/p>\r\n\r\n\r\n\r\n

            Note:-<\/strong>
            Full data load shows the initial load migration status, while Incremental data sync shows change data capture (CDC) status.<\/p>\r\n\r\n\r\n\r\n

            \"\"<\/figure>\r\n\r\n\r\n\r\n

            Perform migration cutover<\/h3>\r\n\r\n\r\n\r\n

            After the initial Full load is completed, the databases are marked Ready to cutover.<\/p>\r\n\r\n\r\n\r\n

              \r\n
            1. When you\u2019re ready to complete the database migration, select Start Cutover.<\/li>\r\n
            2. Wait until the Pending changes counter shows 0 to ensure that all incoming transactions to the source database are stopped, select the Confirm checkbox, and then select Apply.<\/li>\r\n<\/ol>\r\n\r\n\r\n\r\n
              \"\"<\/figure>\r\n\r\n\r\n\r\n

              3. When the database migration status shows Completed, recreate sequences<\/a> (if applicable), and connect your applications to the new target instance of Azure Database for PostgreSQL.<\/p>\r\n\r\n\r\n\r\n\r\n\r\n

              Related Searches:\u00a0 DevOps solution provider<\/a> | AWS Partner<\/a>\u00a0<\/p>\r\n\r\n\r\n\r\n

              <\/div>\r\n\r\n\r\n\r\n