PostgreSQL Migration

NOTE
Due to the fact that migrations always involve interaction with third party services running outside of Exoscale, only best effort support is provided on all migrations.

Migrate your PostgreSQL database to our Managed PostgreSQL service. Two methods are supported: Replication and pg_dump.

The migration first attempts to use the replication method, and if it fails, it uses pg_dump.

The replication method is preferred for production databases because it allows migrating data with minimal downtime. The pg_dump method is slower and will migrate one database at a time.

Prerequisites

  • A target PostgreSQL service: this can be a new or an existing service.
  • The hostname, port and password of the source PostgreSQL service.
  • The source PostgreSQL service can be secured with SSL/TLS.
  • Publicly accessible source PostgreSQL service (for that, you might need to set bind parameter to a publicly reachable network interface in your PostgreSQL configuration). Any firewalls need to be open on port TCP 5432 if using the default to allow traffic from the source to the target servers. Also pay attention to other protections, that can restrict connections: SELinux should be disabled or set in permissive mode for the duration of the migration on the source server.
  • Migration using replication will not work for PostgreSQL databases with capital letters If you can not rename this database please check Aiven set up logical replication to replicate your data
  • Access to a superuser role on the source cluster.
  • wal_level setting to logical on the source cluster: This setting ensures that the necessary information for logical replication, such as individual data modifications and the associated metadata, is recorded in the WAL; it allows an accurate data replication to the target cluster during the migration process.

Performing data migration

To perform the migration, you can either use the portal or the Exoscale CLI, depends on when the action it’s performed and if the service receiving the migration already exists or not.

Run the following command to create a new service and start the migration immediately:

exo dbaas create pg business-4 target-postgresql-service-name --pg-ip-filter 0.0.0.0/0 --pg-migration-host x.x.x.x --pg-migration-port 5432 --pg-migration-username xxxxx --pg-migration-password xxxxx --pg-migration-method replication --pg-migration-ssl false --pg-migration-dbname xxxxx
  • target-PostgreSQL-service-name : Target DBaaS Service Name
  • –pg-migration-host : the IP address of your source PostgreSQL server
  • –pg-migration-port : the port on which your source PostgreSQL server is reachable
  • –pg-migration-username : username on your source PostgreSQL server
  • –pg-migration-password : password on your source PostgreSQL server
  • –pg-migration-method : the chosen method for migration, which could be replication or dump method.

In case the PostgreSQL service is already created, you can run the command below:

exo dbaas update --pg-migration-host x.x.x.x --pg-migration-port 5432 --pg-migration-username xxxxx --pg-migration-password xxxxx --pg-migration-method dump target-postgresql-service-name

To check status of our migration we can run command:

exo dbaas migration status target-postgresql-service-name

And if we want to stop the running migration:

exo dbaas migration stop target-postgresql-service-name

NOTE
Ensure you are using at least version 1.53.0 of the Exoscale CLI.

Disabling all constraints/triggers using aiven_extras

NOTE
If you are migrating data and before deciding on the fully manual approach using aiven_extras consider checking if logical replication suits better your use case. The approach outlined below is done automatically via logical replication.

Access to super-user functionality is restricted in order to have a clear demarcation line for service availability. Disabling triggers is one of them.

Fortunately an alternative is available using aiven_extras plugin.

Step 1: In the database shell install the aiven_extras extension with

CREATE EXTENSION aiven_extras CASCADE;

Step 2: Disable constraints with:

SELECT aiven_extras.session_replication_role('replica');

Step 3: Import your data or another task that requires disabled constraints

Step 4: Re-enable triggers

SELECT aiven_extras.session_replication_role('origin');

Keep in mind that as all constraints get disabled there’s a risk of creating orphaned records or inconsistent data.

Changes made without constraints can affect other transactions in progress, leading to data anomalies, so ideally should be done when users don’t insert data into the database.

Re-enabling constraints might trigger a check of all rows, which can be resource-intensive for large tables.