Skip to main content

Migrate between PostgreSQL® instances using aiven-db-migrate in Python

The aiven-db-migrate tool is an open source project available on GitHub, useful to perform PostgreSQL migrations. It's written in Python and therefore you can start any migration by directly calling the correct module.

The list of source and target database requirements is available in the dedicated documentation.

Variables

The following variables need to be substituted in the aiven-db-migrate calls

VariableDescription
SRC_USERNAMEUsername for source PostgreSQL connection
SRC_PASSWORDPassword for source PostgreSQL connection
SRC_HOSTNAMEHostname for source PostgreSQL connection
SRC_PORTPort for source PostgreSQL connection
DST_USERNAMEUsername for destination PostgreSQL connection
DST_PASSWORDPassword for destination PostgreSQL connection
DST_HOSTNAMEHostname for destination PostgreSQL connection
DST_PORTPort for destination PostgreSQL connection
DST_DBNAMEBootstrap database name for destination PostgreSQL connection

Execute the aiven-db-migrate in Python

You can run perform a PostgreSQL® migration using aiven-db-migrate by:

  • Cloning the aiven-db-migrate GitHub repository and follow the installation instructions

  • Executing the following script

    python3 -m aiven_db_migrate.migrate pg -d \
    -s postgres://SRC_USERNAME:SRC_PASSWORD@SRC_HOSTNAME:SRC_PORT \
    -t postgres://DST_USERNAME:DST_PASSWORD@DST_HOSTNAME:DST_PORT/DST_DBNAME?sslmode=require \
    --max-replication-lag 1 \
    --stop-replication \
    -f DB_TO_SKIP

The following flags can be set:

  • -d or --debug: enables debug logging, highly recommended when running migration to see details of the migration process
  • -f or --filtered-db: comma separated list of databases to filter out during migrations
  • --stop-replication: by default logical replication will be left running on both source and target database. If set to true, it requires also --max-replication-lag >= 0 to wait until a fixed replication lag before stopping the replication
  • --max-replication-lag: max replication lag in bytes to wait for, by default no wait, this parameter is required when using --stop-replication
  • --skip-table: comma separated list of tables to filter out during migrations, useful when willing to skip extension tables like spatial_ref_sys from postgis extension.
tip

For live migration --stop-replication and --max-replication-lag flags are not needed to keep logical replication running.

The full list of parameters and related descriptions is available by running the following command:

python3 -m aiven_db_migrate.migrate pg -h