The preferred approach to migrating a database to Aiven for PostgreSQL is to use the built-in migration tool. However, if you are running PostgreSQL 9.6 or do not have superuser access to your database to add replication slots, you can use the open source Bucardo tool to allow replication to Aiven.

Requirements:

  • An Aiven for PostgreSQL database

  • Your current database

  • A computer with Bucardo installed on it

  • Connectivity between your current database, the Aiven database, and Bucardo

Moving Existing Data

To move existing data, you can follow the steps below and update your sync job to use the onetimecopy and move existing data across. You can also use the standard pg_dump and pg_restore commands to fill the Aiven database and use Bucardo for syncing any changes to the source database and ensuring it remains up to date.

Replicating Changes

To migrate your data using Bucardo:

  1. Install Bucardo using the installation instructions on the Bucardo site.

  2. Install the aiven_extras extension to your current database.
    Bucardo requires the superuser role to set the session_replication_role parameter. Aiven uses the open source aiven_extras extension to allow you to run superuser commands as a different user, as direct superuser access is not provided for security reasons.

  3. Open and edit the Bucardo.pm file with administrator privileges.
    The location of the file can vary according to your operating system, but you might find it in /usr/local/share/perl5/5.32/Bucardo.pm, for example.

    1. Scroll down until you see a disable_triggers function, on line 5324 in the following example:

    2. On line 5360, change SET session_replication_role = default to the following:

      $dbh->do(q{select aiven_extras.session_replication_role('replica');});

    3. Scroll down to the enable_triggers function:

    4. On line 5429, change SET session_replication_role = default to the following:

      $dbh->do(q{select aiven_extras.session_replication_role('origin');});

    5. Save your changes and close the file.

  4. Add your source and destination databases.
    For example:

    bucardo add db srcdb dbhost=0.0.0.0 dbport=5432 dbname=all_your_base dbuser=$DBUSER dbpass=$DBPASS

    bucardo add db destdb dbhost=cg-pg-dev-sandbox.aivencloud.com dbport=21691 dbname=all_your_base dbuser=$DBUSER dbpass=$DBPASS

  5. Add the tables that you want to replicate:

    bucardo add table belong to us herd=$HERD db=srcdb

    You can set $HERD to any name you choose for the herd, which is used to set up the synchronization.

  6. Dump and restore the database from your source to Aiven:

    pg_dump --schema-only --no-owner all_your_base > base.sql
    psql "$AIVEN_DB_URL" < base.sql

    You can restore the source data or provide only the schema, depending on the size of your current database.

  7. Create the dbgroup for Bucardo:

    bucardo add dbgroup src_to_dest srcdb:source destdb:target
    bucardo add sync sync_src_to_dest relgroup=$HERD db=srcdb,destdb
    (sudo) bucardo start
    bucardo status sync_src_to_dest

  8. Start Bucardo and run the status command.
    This should give you output similar to the following example:

    When Current state is Good, the data is flowing to your Aiven database.

  9. Log in to the Aiven web console and go to the Current Queries tab for your service.
    This shows you that the bucardo process is inserting data:

  10. Once all your data is synchronized, switch the database connection for your applications to the Aiven service URI.

Did this answer your question?