This article describes how to migrate a PostgreSQL cluster from an external source to an Aiven for PostgreSQL service. Migration uses the aiven-db-migrate tool, which supports both logical replication and using a dump and restore process.

Logical replication is the default method, as once successful, this keeps the two databases synchronized until the replication is interrupted. If the preconditions for logical replication are not met for a database, the migration falls back to using pg_dump.

Regardless of the migration method used, the migration tool first performs a schema dump and migration to ensure schema compatibility.

Note: Logical replication also works when migrating from AWS RDS PostgreSQL 10+. Google Cloud Platform's PostgreSQL for CloudSQL does not support logical replication.

Migration requirements:

  • the source server is publicly available or there is a virtual private cloud (VPC) peering connection between the private networks

  • a user account with access to the destination cluster from an external IP, as configured in pg_hba.conf on the source cluster

  • for logical replication:

    • PostgreSQL version 10 or newer

    • Credentials with superuser access to the source cluster or the aiven-extras extension installed on it.
      The aiven_extras extension allows you to perform publish/subscribe-style logical replication without a superuser account, and it is preinstalled on Aiven for PostgreSQL servers.

    • An available replication slot on the destination cluster for each database migrated from the source cluster.

    • Set the wal_level on the source cluster to logical.
      To check this setting, run the following command on the source cluster:

      $ show wal_level;


      If necessary, run the following command in psql and then reload the PostgreSQL configuration:

      $ ALTER SYSTEM SET wal_level = logical;


      Note: If you are migrating from an AWS RDS PostgreSQL cluster, you have to set the rds.logical_replication parameter to 1 (true) in the parameter group.

The migration tool checks the following requirements before it starts the actual migration:

  • A connection can be established to both source and target servers

  • The source and target server are not the same

  • A connection can be established to all source databases, ignoring template0, template1, and admin databases

  • There is enough disk space on the target for 130% of the total size of the source databases (ignoring template0, template1, and admin databases)

  • There are at least as many free logical replication slots as there are databases to migrate

  • A connection can be established to any target database that already exists on both source and target servers

  • The target version is the same as or newer than the source version

  • All languages installed in the source are available in the target

  • All extensions in the source are installed or available in the target

  • Source extensions not installed in the target can be installed:

    • by connecting as a superuser

    • without requiring superuser access

    • by inclusion in the allowed extensions list

    • by being a trusted extension (for PostgreSQL 13 and newer)

  • In addition, for using the logical replication method:

    • The source version is PostgreSQL 10 or newer

    • The source wal_level is set to logical

    • The user connecting to the source has superuser access or the aiven_extra extension is or can be installed on each database

You can run the following command using the Aiven command-line interface to see the available configuration options for migration:

$ avn service types -v
----
Service type 'pg' options:

Remove migration

=> --remove-option migration

Database name for bootstrapping the initial connection

=> -c migration.dbname=<string>

Hostname or IP address of the server where to migrate data from

=> -c migration.host=<string>

Password for authentication with the server where to migrate data from

=> -c migration.password=<string>

Port number of the server where to migrate data from

=> -c migration.port=<integer>

The server where to migrate data from is secured with SSL

=> -c migration.ssl=<boolean> (default=True)

User name for authentication with the server where to migrate data from

=> -c migration.username=<string>

----

The combination of dbname, username, password, host, port, and ssl are used to connect to the PostgreSQL cluster.

An example of the migration process:

  1. Run the following commands to create a couple of PostgreSQL services:

    $ avn service create -t pg -p startup-4 --project test pg-example-src
    $ avn service create -t pg -p startup-4 --project test pg-example-dst

  2. Run the following command on the destination Aiven PostgreSQL service to enable the aiven_extras extension:

    $ CREATE EXTENSION aiven_extras CASCADE;

  3. Configure the migration details:

    $ avn service update -c migration.host=<host> -c migration.port=<port> -c migration.ssl=true -c migration.username=<user> -c migration.password=<pass> --project test pg-example-dst

    For this example, the source cluster has two databases; one with the aiven_extras extension installed and one without it.

  4. Run the following command to check the migration status:

    $ avn --show-http service migration-status pg-example-dst --project test
    ------
    -----Response Begin-----
    {
    "migration": {
    "error": null,
    "method": "",
    "status": "done"
    },
    "migration_detail": [
    {
    "dbname": "has_aiven_extras",
    "error": null,
    "method": "replication",
    "status": "syncing"
    },
    {
    "dbname": "defaultdb",
    "error": null,
    "method": "pg_dump",
    "status": "done"
    }
    ]
    }
    -----Response End-----
    STATUS METHOD ERROR
    ====== ====== =====
    done null

    Note: The method is left empty due to the mixed methods used to migrate each database.

  5. Remove the configuration from the destination service:

    $ avn service update --project test --remove-option migration pg-example-dst

    This removes all logical replication-related objects from both source and destination cluster, so it effectively stops the logical replication. This has no effect for the pg_dump method, since it is a one-time operation.

    Note that removing a migration configuration can leave the destination cluster in an inconsistent state, depending on the state of the migration procedure when the removal is triggered. The states that are considered safe are done for the pg_dump method and syncing for logical replication.

While running, both migration methods are still copying data from the source cluster to the destination, so stopping the process will probably leave some tables only partially moved or missing.

Note: Running a logical replication migration twice on the same cluster will create duplicate data. Logical replication also has some limitations on what it will copy.

Did this answer your question?