Migrating from Heroku Postgres
Migrating data from Heroku Postgres into Crunchy Bridge can be achieved in one of two ways, depending on the size of your dataset.
Datasets under 100 GB
For datasets under 100 GB, it is generally simplest to migrate via logical backup (pg_dump
/ pg_restore
) as outlined below.
Running the pg_dump
/ pg_restore
on-dyno saves time by eliminating the need to download the backups, and all operations occur within the same datacenter. The pg_dump
is performed using the Heroku default user's credentials, while pg_restore
will use the Crunchy Bridge superuser role.
Datasets over 100 GB
For datasets larger than 100 GB, it is recommended to use Heroku's physical backups and WAL files to create a replica of your Heroku database and cutover your apps in a coordinated event. If you'd like to take this approach, please reach out to Crunchy Bridge support. We are very familiar with this process and can collaborate with you on each step.
Steps to migrate via logical backup
If your dataset is under 100 GB, migrating using pg_dump
and pg_restore
is likely the quickest and simplest approach. Here is a step-by-step guide. 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.
Disable pgaudit
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. On your Crunchy Bridge instance as the postgres role, while connected to your Crunchy Bridge instance:
ALTER USER postgres RESET pgaudit.log;
Pipe pg_dump
on Heroku to pg_restore
on Crunchy Bridge
Start a Heroku bash session in an app that has the Heroku database attached, and pipe the logical backup from pg_dump
directly into pg_restore
to your empty Crunchy Bridge instance. The dump is current as of the moment it is executed, so if this is being done for a cutover ensure that your application is in maintenance mode so that there are no changes to the data during the migration.
$ heroku run bash --app your-app-here
$ pg_dump -Fc $DATABASE_URL | pg_restore --no-acl --no-owner -d postgres://postgres:[email protected]:5432/postgres
Update ownership and permissions
Since the restore was executed using the postgres role, all the created tables are also owned by the postgres role, and so the Crunchy Bridge application role has no default permissions. To give the application role identical permissions to those that the Heroku role had, reset objects' owners to the application role. Run the SQL below, and then run the output as well.
Info
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'
ORDER BY oid;
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;
?column?
-------------------------------------------------------
ALTER TABLE public."sampledata" OWNER TO application;
(1 row)
Time: 65.915 ms
postgres=# ALTER TABLE public."sampledata" OWNER TO application;
ALTER TABLE
Time: 292.640 ms
postgres=#
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).
Re-enable pgaudit
Re-enable pgaudit
on the postgres user by, on your Crunchy Bridge instance as the postgres role:
ALTER USER postgres SET pgaudit.log='all';
Update DATABASE_URL and exit maintenance mode
Once data is migrated, apps can be updated with the new, Crunchy Bridge, connection string and taken out of maintenance mode. If you wish to continue to use $DATABASE_URL
in your app, be sure to detach the database URL from your app so that Heroku doesn't overwrite the Crunchy Bridge URL with the old Heroku Postgres URL, for example:
$ heroku addons:detach DATABASE --app your-app-here
Don't forget to validate the restore process in a staging environment prior making changes to your production application!