Migrate to Aiven for PostgreSQL with aiven-db-migrate

The aiven-db-migrate tool is an open source project available on GitHub, and it is the preferred way to perform the migration.

aiven-db-migrate performs a schema dump and migration first to ensure schema compatibility.

It supports both logical replication, and using a dump and restore process. Logical replication is the default method which 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.


You can use logical replication when migrating from AWS RDS PostgreSQL 10+, whereas the Google Cloud Platform’s PostgreSQL for CloudSQL does not support it.

What you’ll need

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

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

In order to use the logical replication method, you’ll need the following:

  • PostgreSQL version is 10 or higher.

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

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

Additional migration configuration options are available, check the Migration parameters section of the configuration reference.


You can use the following variables in the code samples provided:




Hostname for source PostgreSQL connection


Port for source PostgreSQL connection


Database name for source PostgreSQL connection


Username for source PostgreSQL connection


Password for source PostgreSQL connection


SSL setting for source PostgreSQL connection


Name of the Aiven destination PostgreSQL service


Aiven plan for the Aiven destination PostgreSQL service


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

-> To perform the migration

  1. Set the wal_level to logical

To review the current wal_level, run the following command on the source cluster via psql:

show wal_level;

If the output is not logical, run the following command in psql and then reload the PostgreSQL configuration:

ALTER SYSTEM SET wal_level = logical;


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

  1. If you don’t have an Aiven for PostgreSQL database yet, run the following command to create a couple of PostgreSQL services via Aiven CLI substituting the parameters accordingly:

    avn service create -t pg -p DEST_PG_PLAN DEST_PG_NAME
  2. Once logged in into the destination Aiven for PostgreSQL service, execute the following command via psql to enable the aiven_extras extension:

  3. Set the migration details via Aiven CLI substituting the parameters accordingly:

    avn service update -c migration.host=SRC_HOSTNAME   \
        -c migration.port=SRC_PORT                      \
        -c migration.ssl=SRC_SSL                        \
        -c migration.username=SRC_USERNAME              \
        -c migration.password=SRC_PASSWORD              \
  4. Check the migration status via Aiven CLI:

    avn --show-http service migration-status DEST_PG_NAME --project test

You should get the following command output which mentions that the pg_dump migration of the defaultdb database is done and the logical replication of the has_aiven_extras database is syncing``:

-----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-----
======  ======  =====
done            null


The overall method field is left empty due to the mixed methods used to migrate each database.

  1. Remove the configuration from the destination service via Aiven CLI Make sure your migration process is in one of the following state when triggering the removal: done for the pg_dump method, and syncing for logical replication. Otherwise, removing a migration configuration can leave the destination cluster in an inconsistent state.

    avn service update --remove-option migration DEST_PG_NAME

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


Don’t stop the process while running as both the logical replication and pg-dump/pg-restore methods are copying data from the source to the destination cluster.

-> Migration using aiven-db-migrate directly

The aiven-db-migrate tool migrates all the tables including extension tables such as spatial_ref_sys from postgis extension.

There will be a feature/fix to allow skip extension tables in the future.

At the moment the workaround is to use skip-table:

pg_migrate -d -v -s "SRC" -t "DST" -f "_aiven" --skip-table spatial_ref_sys