aiven-db-migrate

Aiven database migration tool. This tool is meant for easy migration of databases from some database service provider, such AWS RDS, or on premises data center, to Aiven Database as a Service. However, it's not limited for Aiven services and it might be useful as a generic database migration tool.

Usually database service providers, such as Aiven, AWS RDS, and alike, don't allow superuser/root access. Instead the service's master/admin user is granted permissions for the most common DBA tasks, see e.g. https://help.aiven.io/en/articles/489557-postgresql-superuser-access. In addition, service provider's web console/API can be used for performing some DBA tasks requiring more privileges than granted for the master/admin user. However, the missing superuser access makes some existing database migrating tools, such as pg_dumpall, not useful when migrating database to/from service provider.

Currently this tool supports only PostgreSQL but we aim to add support for other databases, such as MySQL.

Requires Python 3.9 or newer.

Usage

Running library module:

$ python3 -m aiven_db_migrate.migrate -h
Available commands: pg

Installing in virtualenv:

$ python3 -m venv venv
$ . venv/bin/activate
$ ## Run make to set the proper version
$ make
$ pip install .

This installs console scripts which have the same interface as the library module:

pg_migrate: PostgreSQL migration

PostgreSQL

Requirements:

pg_dump: from any PostgreSQL version between the source and target versions
psql: any modern version should work

Run library module:

$ python3 -m aiven_db_migrate.migrate pg -h

or, if installed:

$ pg_migrate -h

Migrating is supported to the same or newer PostgreSQL version starting from PostgreSQL 10 to PostgreSQL 14. Migrating to older version is not supported.

By default it searches pg_dump under /usr/, when using PostgreSQL installs on different directory such as on Mac, use --pgbin parameter to define PostgreSQL home directory. e,g,

--pgbin /Applications/Postgres.app/Contents/Versions/14/bin

Supports regular data dump (pg_dump) and logical replication (PostgreSQL 10 or newer). In case that logical replication is not available or privileges/requirements are missing migrating falls back to data dump.

CLI example

Migrating from AWS RDS to Aiven for PostgreSQL. Logical replication is enabled in source AWS RDS PostgreSQL server and aiven-extras extension is installed in target database.

$ pg_migrate -s "postgres://postgres:<password>@jappja-pg1.chfhzaircbpb.eu-west-1.rds.amazonaws.com:5432/defaultdb" -t "postgres://avnadmin:<password>@pg1-test-jappja-test.avns.net:26192/defaultdb?sslmode=require"

# Or:
$ SOURCE_SERVICE_URI="postgres://postgres:<password>@jappja-pg1.chfhzaircbpb.eu-west-1.rds.amazonaws.com:5432/defaultdb" TARGET_SERVICE_URI="postgres://avnadmin:<password>@pg1-test-jappja-test.avns.net:26192/defaultdb?sslmode=require" pg_migrate
...

Roles:
  rolname: 'rdsadmin', rolpassword: None, status: 'failed', message: 'must be superuser to create superusers'
  rolname: 'rds_password', rolpassword: None, status: 'created', message: 'role created'
  rolname: 'rds_superuser', rolpassword: None, status: 'created', message: 'role created'
  rolname: 'test_user1', rolpassword: 'placeholder_kfbqrvmdhgrpgpvy', status: 'created', message: 'role created'
  rolname: 'rds_ad', rolpassword: None, status: 'created', message: 'role created'
  rolname: 'rds_iam', rolpassword: None, status: 'created', message: 'role created'
  rolname: 'rds_replication', rolpassword: None, status: 'created', message: 'role created'
  rolname: 'rdsrepladmin', rolpassword: None, status: 'failed', message: 'must be superuser to create replication users'
  rolname: 'postgres', rolpassword: None, status: 'exists', message: 'role already exists'
  rolname: 'test_user2', rolpassword: None, status: 'created', message: 'role created'

Databases:
  dbaname: 'rdsadmin', method: None, status: 'failed', message: 'FATAL:  pg_hba.conf rejects connection for host "80.220.195.174", user "postgres", database "rdsadmin", SSL on\nFATAL:  pg_hba.conf rejects connection for host "80.220.195.174", user "postgres", database "rdsadmin", SSL off\n'
  dbaname: 'defaultdb', method: 'replication', status: 'running', message: 'migrated to existing database'

By default logical replication is left running and the created pub/sub objects need to be cleaned up once workloads have been moved to the new server. Objects created by this tool are named like managed_db_migrate_<dbname>_<sub|pub|slot>.

Starting from the target (using aiven-extras extension), get first the subscription name:

defaultdb= > SELECT * FROM aiven_extras.pg_list_all_subscriptions();

and then drop it:

defaultdb= > SELECT * FROM aiven_extras.pg_drop_subscription('managed_db_migrate_defaultdb_sub');

Note that with aiven-extras dropping subscription in target also drops replication slot in source (dblink).

In the source get first the publication name:

defaultdb=> SELECT * FROM pg_publication;

and then drop it:

defaultdb=> DROP PUBLICATION managed_db_migrate_defaultdb_pub;

In case that aiven-extras is not used clean up replication slot too:

defaultdb=> SELECT * FROM pg_replication_slots;
defaultdb=> SELECT * FROM pg_drop_replication_slot('managed_db_migrate_defaultdb_slot');

Using --max-replication-lag waits until replication lag in bytes is less than/equal to given max replication lag. This can be used together with --stop-replication to clean up all created pub/sub objects when replication is done.

With --validate only best effort validation is run. This checks e.g. PL/pgSQL languages, extensions etc. installed in source are also installed/available in target.

Use --no-replicate-extension-tables to skip extension tables. By default it attempts to replicate all extension tables during logical replication.

With --force-method you can specify if you wish to use either replication or dump method. Otherwise the most suitable method is chosen automatically.

Using --dbs-max-total-size together with --validate you can check if the size of the source database in below some threshold.

API example

Migrating from AWS RDS to Aiven for PostgreSQL. Logical replication is enabled in source AWS RDS PostgreSQL server but aiven-extras extension is not installed in target database so migrating falls back to data dump.

>>> from aiven.migrate import PGMigrate, PGMigrateResult
>>> pg_mig = PGMigrate(source_conn_info="postgres://postgres:<password>@jappja-pg1.chfhzaircbpb.eu-west-1.rds.amazonaws.com:5432/defaultdb", target_conn_info="postgres://avnadmin:<password>@pg2-test-jappja-test.avns.net:26192/defaultdb?sslmode=require")
>>> result: PGMigrateResult = pg_mig.migrate()
...
Logical replication failed with error: 'must be superuser to create subscriptions', fallback to dump
>>> result
PGMigrateResult(pg_databases={'rdsadmin': {'dbname': 'rdsadmin', 'message': 'FATAL:  pg_hba.conf rejects connection for host "80.220.195.174", user "postgres", database "rdsadmin", SSL on\nFATAL:  pg_hba.conf rejects connection for host "80.220.195.174", user "postgres", database "rdsadmin", SSL off\n', 'method': None, 'status': 'failed'}, 'defaultdb': {'dbname': 'defaultdb', 'message': 'migrated to existing database', 'method': 'dump', 'status': 'done'}}, pg_roles={'rdsadmin': {'message': 'must be superuser to create superusers', 'rolname': 'rdsadmin', 'rolpassword': None, 'status': 'failed'}, 'rds_password': {'message': 'role created', 'rolname': 'rds_password', 'rolpassword': None, 'status': 'created'}, 'rds_superuser': {'message': 'role created', 'rolname': 'rds_superuser', 'rolpassword': None, 'status': 'created'}, 'test_user1': {'message': 'role created', 'rolname': 'test_user1', 'rolpassword': 'placeholder_qkdryldfsrdaocio', 'status': 'created'}, 'rds_ad': {'message': 'role created', 'rolname': 'rds_ad', 'rolpassword': None, 'status': 'created'}, 'rds_iam': {'message': 'role created', 'rolname': 'rds_iam', 'rolpassword': None, 'status': 'created'}, 'rds_replication': {'message': 'role created', 'rolname': 'rds_replication', 'rolpassword': None, 'status': 'created'}, 'rdsrepladmin': {'message': 'must be superuser to create replication users', 'rolname': 'rdsrepladmin', 'rolpassword': None, 'status': 'failed'}, 'postgres': {'message': 'role already exists', 'rolname': 'postgres', 'rolpassword': None, 'status': 'exists'}, 'test_user2': {'message': 'role created', 'rolname': 'test_user2', 'rolpassword': None, 'status': 'created'}})

Logical replication

requires PostgreSQL 10 or newer
wal_level needs to be logical
currently supports only FOR ALL TABLES publication in source
aiven-extras extension installed in both source and target database, or
superuser or superuser-like privileges, such as rds_replication role in AWS RDS, in both source and target
AWS RDS additional settings/info

Warning

⚠️ Large objects are unable to be replicated using logical replication, up to and including PostgreSQL 15.

Schemas

schemas are migrated without object ownership; the user used for migration is given all object ownership
NOTE: schema changes break logical replication

Roles

roles with LOGIN attribute are migrated using placeholder passwords: placeholder_<16 char random string>
migrating superuser or replication roles requires superuser privileges

Extensions

requires whitelisting the extension in target with pgextwlist, or
superuser or superuser-like privileges
Aiven for PostgreSQL supported extensions

Development

Install build depends (Fedora):

$ make build-dep-fedora

Style checks:

$ make validate-style

Fix style errors with:

$ make isort
$ make yapf

Static checks (flake8, pylint and mypy):

$ make static-checks

Tests (pytest):

$ make test

Running whole test set takes time since all supported migration paths are tested. During development it's usually enough to run tests only for a certain PostgreSQL version, e.g.:

$ PG_VERSION="12" make test

It's also possible to test migration from one PostgreSQL version to another, e.g.:

$ PG_SOURCE_VERSION="10" PG_TARGET_VERSION="12" make test

Test set can be targeted even further by invoking pytest, e.g.:

$ PG_SOURCE_VERSION="10" PG_TARGET_VERSION="12" python3 -m pytest -s test/test_pg_migrate.py::Test_PGMigrate::test_migrate

TODO

JSON output with CLI (for automation)
- Hard to make pg_dump silent for outputting JSON to stdout
- Output json to file instead?
More options
- --dump-only, --repl-only
- --include-databases, --exclude-databases
- --include-tables, --exclude-tables
- --role-passwords (role/passwords file for creating roles with real passwords instead of placeholders)
More tests
- Notably error/corner cases
Schema changes break logical replication
- While logical replication is running dump schema periodically and check if has changed, e.g. by calculating hash of the schema dump
- How to continue if schema has changed? Stop replication, dump schema and restart replication?
Proper README + API doc
RPM build recipe for aiven-core/prune integration
Test automation: Jenkins/Github Actions

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

README.md

README.md

aiven-db-migrate

Usage

PostgreSQL

CLI example

API example

Logical replication

Warning

Schemas

Roles

Extensions

Development

TODO

Files

README.md

Latest commit

History

README.md

File metadata and controls

aiven-db-migrate

Usage

PostgreSQL

CLI example

API example

Logical replication

Warning

Schemas

Roles

Extensions

Development

TODO