From ElephantSQL

Migrating data from ElephantSQL into Crunchy Bridge can be done via logical backup using pg_dump and pg_restore. This guide will walk you through the process.


We strongly recommend validating that this approach will work for you by testing it in a staging environment before moving forward with your production clusters.

Create your Crunchy Bridge cluster

The first step is to create a new cluster on Crunchy Bridge. Note that storage and compute are decoupled on Crunchy Bridge. We recommend starting with a similar memory configuration. You can resize at any time and many customers find after migration that they can use a smaller instance.

High availability and replicas

ElephantSQL offers high availability with a read replica that is automatically promoted in certain circumstances, after which the application database connection is updated. Crunchy Bridge handles this differently.

Crunchy Bridge offers high availability and it is automatic, just like on ElephantSQL. However, a high availability failover on Bridge does not result in a database connection update. In the event of a failover the connection string will stay the same.

It's also worth noting that high availability instances on Bridge can't be used for other purposes, and you can't connect to them. If you need a read-only copy of your cluster you'll want to provision a read replica after your migration is complete.

Find your database URLs

The next step is to find the database URLs for both the database from which you want to migrate data, as well as the empty cluster on Crunchy Bridge to which you want to migrate your data.

On Crunchy Bridge, you can find your database URL in your destination cluster's Connection tab. Select Role: postgres and Format: URL.


Note: We don't recommend that you use the postgres user for your normal application processes. Be sure to choose application or another user when getting your database URL for other non-migration purposes.

Disable pgaudit on Crunchy Bridge

All Crunchy Bridge instances are created with database auditing enabled for the postgres role. Therefore, to reduce excessive logging, temporarily disable pgaudit for the postgres role during migration. You'll turn this back on later.

On your Crunchy Bridge instance as the postgres role, while connected to your Crunchy Bridge instance, run the following:

ALTER USER postgres RESET pgaudit.log;

Begin the data migration

In order to move your data into Crunchy Bridge, you'll pipe the logical backup from pg_dump directly into pg_restore to your empty Crunchy Bridge cluster. The dump is current as of the moment it is executed, so make sure all database changes are completed before beginning the process. We recommend pausing any processes that access this database before beginning your migration.

$ pg_dump -Fc $SOURCE_DATABASE_URL | pg_restore --no-acl --no-owner -d $CRUNCHY_DATABASE_URL

Update ownership and permissions

Since the restore was executed using the postgres role, all the created tables are also owned by the postgres role. This means that the Crunchy Bridge application role -- which we hope you'll be using for your application traffic -- has no default permissions. To give the application role identical permissions to those of the postgres role post-migration, reset objects' owners to the application role.

You'll need to run the SQL below, and then also run the output from running that SQL, as well.


This is a two step process. The statements below will generate the commands used to grant permissions. You will run the statements below in your Crunchy cluster as the postgres role, and then you'll need to take the output of each one, which will be ALTER statements specific to your particular schema, and run those in order to update ownership and permissions in your cluster.

-- Tables
SELECT 'ALTER TABLE '|| schemaname || '."' || tablename ||'" OWNER TO application;'
FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;

-- Sequences
SELECT 'ALTER SEQUENCE '|| sequence_schema || '."' || sequence_name ||'" OWNER TO application;'
FROM information_schema.sequences
WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema')
ORDER BY sequence_schema, sequence_name;

-- Views
SELECT 'ALTER VIEW '|| table_schema || '."' || table_name ||'" OWNER TO application;'
FROM information_schema.views
WHERE NOT table_schema IN ('pg_catalog', 'information_schema')
AND NOT table_name IN ('pg_stat_statements', 'pg_stat_statements_info')
ORDER BY table_schema, table_name;

-- Materialized Views
SELECT 'ALTER TABLE '|| oid::regclass::text ||' OWNER TO application;'
FROM pg_class WHERE relkind = 'm'

Here is an example of running the first statement block above, and then running the resulting output, an ALTER TABLE statement, in order to produce the change in permissions:

postgres=# SELECT 'ALTER TABLE '|| schemaname || '."' || tablename ||'" OWNER TO application;'
FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;
 ALTER TABLE public."sampledata" OWNER TO application;
(1 row)
Time: 65.915 ms

postgres=# ALTER TABLE public."sampledata" OWNER TO application;
Time: 292.640 ms


Set up any additional roles

Be sure to create any other needed roles and set appropriate permissions depending on how they are used (for example, read-only roles used by your application). Note also that the application role is intended for use by your application while the postgres / superuser account is intended for administration (refer to User Management Concepts for details).

Create extensions

If you're using PostgreSQL extensions on ElephantSQL, you'll want to recreate those on your new Crunchy Bridge cluster. You can locate those on the source server using the \dx command in psql. Check out the extensions available on Bridge and how to enable them.

Re-enable pgaudit

Re-enable pgaudit on the postgres user by running the following as the postgres role on your Crunchy Bridge instance:

ALTER USER postgres SET pgaudit.log='all';

Update connection string and exit maintenance mode

Once data is migrated, your applications can be updated with the new Crunchy Bridge connection string and taken out of any maintenance mode.

Don't forget to validate the restore process in a staging environment prior making changes to your production application!

Getting help

If you have questions or need assistance with your migration, please contact support. We're happy to help!