Migrating into Crunchy Bridge

If you are migrating from an existing database hosting provider to Crunchy Bridge we have experience helping customers migrate from a variety of different platforms. We’ve helped migrate databases of all sizes from a few Gigabytes to several Terabytes while minimizing downtime to minutes or less. Databases can be migrated from a number of providers including:

  • Amazon RDS
  • Azure Postgres
  • Google Cloud SQL
  • Heroku Postgres
  • More

Choosing your migration approach

The first step in the migration is choosing your approach. The two factors that inform your approach are:

  1. Size of your dataset
  2. Engineering effort you want to invest to minimize downtime.

Datasets under 100 GB

The Postgres pg_dump / pg_restore process is a size of data operation, meaning the amount of data you have determines how long it will take to migrate over. Databases that are under 100 GB can usually be dumped / restored in under an hour making them a good candidate for this approach. The pg_dump / pg_restore is the fastest is terms of engineering hours required to make a migration.

Migrating larger datasets

When you have a larger dataset (over 100 GB) the pg_dump / pg_restore method of migrating becomes prohibitively long. In these cases the best approach is to stream data from your existing Postgres database into Crunchy Bridge. The approach for streaming varies based on the provider you are coming from, but all providers have at least some mechanism for being able to migrate and minimize down time. We’ll lay out the two primary mechanisms below and then give database provider specific details as well.

Logical replication

If you are on a version of Postgres that supports logical replication this is one option to both be able to migrate into Crunchy Bridge, as well as perform a version upgrade at the same time. The three major requirements for logical replication to work are:

  1. Be on a provider and version that support it
  2. Not be using temporary tables or expecting those to be replicated
  3. Have primary keys on all your tables

If all of the above is true and you’d like to migrate with logical replication you can start with the Postgres docs, or if you have questions you can reach out to us via support to answer any questions or further assistance.

Replica using Postgres tooling (wal-e, wal-g, pgbackrest)

If you are running your own setup you likely have some form of backups/disaster recovery system in place. We can often leverage that same process to help you migrate. The best process for setting up these tools is to reach out to support and schedule a call to begin to dive into the process. In order to prepare you will want to:

  1. Open a ticket within your account that you’re wishing to migrate to and specify which tooling you have in place.
  2. Create a target database in Crunchy Bridge of sufficient size
  3. Share the credentials with us so we can begin the replication process

We’re happy to do some test cutovers once the replica is setup so you can see the process working. Once you’re ready for the production cutover we will coordinate a time, then follow the following steps:

  1. You will put your app into maintenance mode
  2. We will await the last WAL segment (usually under 1 minute)
  3. We will promote your Crunchy Bridge database to be the leader, meanwhile you will update your apps to point to the new DB
  4. We will perform a few final steps including cycling the super user credential and beginning backups

In cases due to underlying libc changes between providers we will want to check indexes for being invalid. We can do this during the staging/test process. If we do detect invalid indexes a final step of reindexing is required during the production cutover.

pg_audit

For general security, Crunchy Bridge audits the main superuser postgres role for the database. During a migration, consider the role being used. It might make more sense to run a restore as the application role. If you are using the postgres role for a pg_restore, be sure to disable pg_audit during this process to reduce the amount of logs generated.

To stop pg_audit:

alter user postgres reset pgaudit.log;

To turn it back on:

alter user postgres set pgaudit.log='all';

Provider Specific migration approaches

Amazon RDS

When migrating from Amazon RDS the valid approaches include:

  • Postgres dump and restore
  • Logical replication
  • Amazon DMS (Data Migration Service)

Azure Postgres

When migrating from Azure Postgres the valid approaches include:

  • Postgres dump and restore
  • Logical decoding (please contact us for more details here)
  • Azure DMS (Data Migration Service)

Google Cloud SQL

When migrating from Google Cloud SQL the valid approaches include:

  • Postgres dump and restore
  • Logical replication

Heroku Postgres

When migrating from Heroku Postgres the valid approaches include:

  • Postgres dump and restore
  • Postgres replication via wal-e (please contact us for more details)