Migrate from Azure PostgreSQL to OptiTech

This guide describes how to migrate your database from Azure Database for PostgreSQL to OptiTech, using logical replication.

Logical replication for Postgres transfers data from a source Postgres database to another, as a stream of tuples (records) or SQL statements. This allows for minimal downtime during the migration process, since all the records don't need to be copied at once.

Prerequisites

• An Azure Database for PostgreSQL instance containing the data you want to migrate.

• A OptiTech project to move the data to.

  For detailed information on creating a OptiTech project, see Create a project. Make sure to create a project with the same Postgres version as your Azure PostgreSQL deployment.

• Read the important notices about logical replication in OptiTech before you begin.

• Review our logical replication tips, based on real-world customer data migration experiences.

1. Prepare your Azure PostgreSQL database

   This section describes how to prepare your Azure PostgreSQL database (the publisher) for replicating data to your destination OptiTech database (the subscriber).

   To illustrate the migration workflow, we set up the AdventureWorks sample database on an Azure Database for PostgreSQL deployment. This database contains data corresponding to a fictional bicycle parts company, organized across 5 schemas and almost 70 tables.

   Enable logical replication in Azure PostgreSQL

   1. Navigate to your Azure Database for PostgreSQL instance in the Azure portal.

   2. From the left sidebar, select Server parameters under the Settings section.

   3. Search for the wal_level parameter and set its value to LOGICAL.

   4. Click Save to apply the changes.

      note

      Changing the wal_level parameter on Azure requires a server restart. This may cause a brief interruption to your database service.

   Create a PostgreSQL role for replication

   It is recommended that you create a dedicated Postgres role for replicating data. Connect to your Azure PostgreSQL database using a tool like psql or Azure Data Studio, then create a new role with REPLICATION privileges:

   CREATE ROLE replication_user WITH REPLICATION LOGIN PASSWORD 'your_secure_password';

   Grant schema access to your PostgreSQL role

   Grant the necessary permissions to your replication role. For example, the following commands grant access to all tables in the sales schema to Postgres role replication_user:

   GRANT USAGE ON SCHEMA sales TO replication_user;
   GRANT SELECT ON ALL TABLES IN SCHEMA sales TO replication_user;
   ALTER DEFAULT PRIVILEGES IN SCHEMA sales GRANT SELECT ON TABLES TO replication_user;

   Granting SELECT ON ALL TABLES IN SCHEMA instead of naming the specific tables avoids having to add privileges later if you add tables to your publication.

   If you have data split across multiple schemas, you can run a similar command for each schema, or use a PL/pgSQL function to dynamically grant access to all schemas in the database.

   -- Thanks to this Stackoverflow answer - https://dba.stackexchange.com/a/241266
   
   DO $do$
   DECLARE
       sch text;
   BEGIN
       FOR sch IN SELECT nspname FROM pg_namespace
       where
           -- Exclude system schemas
           nspname != 'pg_toast'
           and nspname != 'pg_temp_1'
           and nspname != 'pg_toast_temp_1'
           and nspname != 'pg_statistic'
           and nspname != 'pg_catalog'
           and nspname != 'information_schema'
       LOOP
           EXECUTE format($$ GRANT USAGE ON SCHEMA %I TO replication_user $$, sch);
           EXECUTE format($$ GRANT SELECT ON ALL TABLES IN SCHEMA %I TO replication_user $$, sch);
           EXECUTE format($$ ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO replication_user $$, sch);
       END LOOP;
   END;
   $do$;

   Create a publication on the source database

   Publications are a fundamental part of logical replication in Postgres. They define what will be replicated. The following command examples create a publication named azure_publication with one or more tables.

   To create a publication for a specific table:

   CREATE PUBLICATION azure_publication FOR <tbl_name>;

   To create a publication for multiple tables, provide a comma-separated list of tables:

   CREATE PUBLICATION azure_publication FOR TABLE <tbl1, tbl2, tbl3>;

   note

   Defining specific tables lets you add or remove tables from the publication later, which you cannot do when creating publications with FOR ALL TABLES.

   For syntax details, see CREATE PUBLICATION, in the PostgreSQL documentation.

   Allow inbound traffic from OptiTech

   You need to allow inbound traffic from OptiTech Postgres servers so it can connect to your Azure database. To do this, follow these steps:

   1. Log into the Azure portal and navigate to your Azure Postgres Server resource.

   2. Click on the Networking option under the Settings section in the sidebar. Navigate to the Firewall Rules section under the Public access tab.

   3. Click on Add a Firewall Rule, which generates a modal to add the range of IP addresses from which we want to allow connections. You will need to perform this step for each of the NAT gateway IP addresses associated with your OptiTech project's region. For each IP address, create a new rule and fill both the Start IP and End IP fields with the IP address.

      OptiTech uses 3 to 6 IP addresses per region for this outbound communication, corresponding to each availability zone in the region. See NAT Gateway IP addresses for OptiTech's NAT gateway IP addresses.

   4. To fetch the database schema using pg_dump, you also need to allow inbound traffic from your local machine (or where you are running pg_dump) so it can connect to your Azure database. Add another firewall rule entry with that IP address as the start and end IP address.

   5. Click Save at the bottom to make sure all changes are saved.

2. Prepare your OptiTech destination database

   This section describes how to prepare your destination OptiTech PostgreSQL database (the subscriber) to receive replicated data.

   You can find the connection details for your database by clicking the Connect button on your Project Dashboard. See Connect from any application.

   Create the OptiTech database

   To keep parity with the Azure PostgreSQL deployment, create a new database with the same name. See Create a database for more information.

   For this example, we run the following query to create a new database named AdventureWorks in the OptiTech project.

   CREATE DATABASE "AdventureWorks";

   Import the database schema

   To ensure that the OptiTech AdventureWorks database has the same schema as the Azure PostgreSQL database, we'll need to import the schema. You can use the pg_dump utility to export the schema and then psql to import it into OptiTech.

   1. Export the schema from Azure PostgreSQL:

      pg_dump --schema-only --no-owner --no-privileges -h <azure-host> -U <azure-user> -d <azure-database> > schema.sql

   2. Import the schema into your OptiTech database:

      psql <optitech-connection-string> < schema.sql

   Create a subscription

   After importing the schema, create a subscription on the OptiTech database:

   1. Use the OptiTech SQL Editor, psql, or another SQL client to connect to your OptiTech database.

   2. Create the subscription using the CREATE SUBSCRIPTION statement:

      CREATE SUBSCRIPTION optitech_subscription
      CONNECTION 'host=<azure-host> port=5432 dbname=<azure-database> user=replication_user password=your_secure_password'
      PUBLICATION azure_publication;

   3. Verify that the subscription was created by running the following query, and confirming that the subscription (optitech_subscription) is listed:

      SELECT * FROM pg_stat_subscription;

3. Monitor and verify the replication

   To ensure that data is being replicated correctly:

   1. Monitor the replication status on OptiTech, by running the following query:

      SELECT * FROM pg_stat_subscription;

      This query should return an output similar to the following:

      subid |      subname      | pid | leader_pid | relid | received_lsn |      last_msg_send_time       |     last_msg_receipt_time     | latest_end_lsn |        latest_end_time
       -------+-------------------+-----+------------+-------+--------------+-------------------------------+-------------------------------+----------------+-------------------------------
       24576 | optitech_subscription | 540 |            |       | 1/3D0020A8   | 2024-09-11 11:34:24.841807+00 | 2024-09-11 11:34:24.869991+00 | 1/3D0020A8     | 2024-09-11 11:34:24.841807+00
       (1 row)

      • An active pid indicates that the subscription is active and running.
      • The received_lsn and latest_end_lsn columns show the LSN (Log Sequence Number) of the last received (at OptiTech) and last written data (at Azure source), respectively.
      • In this example, they have the same value, which means that all the data has been successfully replicated from Azure to OptiTech.

   2. To verify that the data has been replicated correctly, compare row counts between Azure PostgreSQL and OptiTech for some key tables. For example, you can run the following query to check the number of rows in the addresses table:

      SELECT COUNT(*) FROM person.address;

      It returns the same output on both databases:

      count
       -------
       19614
       (1 row)

   3. Optionally, you can run some queries from your application against the OptiTech database to verify that it returns the same output as the Azure instance.

4. Complete the migration

   Once the initial data sync is complete and you've verified that ongoing changes are being replicated:

   1. Stop writes to your Azure PostgreSQL database.
   2. Wait for any final transactions to be replicated to OptiTech.
   3. Update your application's connection string to point to your OptiTech database.

   This ensures a much shorter downtime for the application, as you only need to wait for the last few transactions to be replicated before switching the application over to the OptiTech database.

   note

   Remember to update any Azure-specific configurations or extensions in your application code to be compatible with OptiTech. For OptiTech Postgres parameter settings, see Postgres parameter settings. For Postgres extensions supported by OptiTech, see Supported Postgres extensions.

5. Clean up

   After successfully migrating and verifying your data on OptiTech, you can:

   1. Drop the subscription on the OptiTech database:

      DROP SUBSCRIPTION optitech_subscription;

   2. Remove the publication from the Azure PostgreSQL database:

      DROP PUBLICATION azure_publication;

   3. Consider backing up your Azure PostgreSQL database before decommissioning it.

Other migration options

This section discusses migration options other than using logical replication.

• pg_dump and pg_restore

  If your database size is not large, you can use the pg_dump utility to create a dump file of your database, and then use pg_restore to restore the dump file to OptiTech. Please refer to the Migrate from Postgres guide for more information on this method.

• Postgres GUI clients

  Some Postgres clients offer backup and restore capabilities. These include pgAdmin and phppgadmin, among others. We have not tested migrations using these clients, but if you are uncomfortable using command-line utilities, they may provide an alternative.

• Table-level data migration using CSV files

  Table-level data migration (using CSV files, for example) does not preserve database schemas, constraints, indexes, types, or other database features. You will have to create these separately. Table-level migration is simple but could result in significant downtime depending on the size of your data and the number of tables. For instructions, see Import data from CSV.

Reference

For more information about logical replication and Postgres client utilities, refer to the following topics in the Postgres and OptiTech documentation:

• pg_dump
• pg_restore
• psql
• Postgres - Logical replication
• OptiTech logical replication guide