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
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.
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.confon 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.
aiven_extrasextension 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.
wal_levelon the source cluster to
To check this setting, run the following command on the source cluster:
$ show wal_level;
If necessary, run the following command in
psqland 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
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
template1, and admin databases
There is enough disk space on the target for 130% of the total size of the source databases (ignoring
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
wal_levelis set to
The user connecting to the source has superuser access or the
aiven_extraextension 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-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
ssl are used to connect to the PostgreSQL cluster.
An example of the migration process:
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
Run the following command on the destination Aiven PostgreSQL service to enable the
$ CREATE EXTENSION aiven_extras CASCADE;
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_extrasextension installed and one without it.
Run the following command to check the migration status:
$ avn --show-http service migration-status pg-example-dst --project test
STATUS METHOD ERROR
====== ====== =====
methodis left empty due to the mixed methods used to migrate each database.
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
syncingfor 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.