How to migrate a PostgreSQL database from Heroku to Platform.sh
Goal
Migrate data stored in a Heroku PostgreSQL resource to a Platform.sh service.
Assumptions
You will need:
- an active application on Platform.sh configured to an empty database
- an active application on Heroku configured to the target database
- Platform.sh Postgres database credentials
- A local repository with the Platform.sh project as git remote
- The Platform.sh CLI installed locally
Problems
When migrating an existing PostgreSQL project from Heroku to Platform.sh, the corresponding resources must be migrated as well. Heroku and Platform.sh use different db_dump formats, as well as different styles of configuration settings.
Steps
1. Download database dump from Heroku.
Find the Resources tab of the Heroku App Dashboard and click on the name of the desired database add-on, for example Heroku Postgres. From the datastore dashboard, visit the Durability tab, create a manual backup of the data, and download the backup file. For the purposes of this guide, the location of the dump will be assumed to be ~/Downloads/db.dump.
2. Add extensions to Platform.sh configuration.
Next, issue the following command to filter the list of SQL commands in the dump by the word “extension”, giving all Postgres extensions loaded into the database:
$ pg_restore -l ~/Downloads/db.dump | grep -o -P '(?<=EXTENSION \").*(?=\")'
For example, this might output
plpgsql
hstore
For each extension, consult the PostgreSQL docs to determine whether it is supported by Platform. If so, add it to the app’s platform.app.yaml as follows:
runtime:
extensions:
- plpgsql
- hstore
3. Convert Heroku dump to a filtered SQL file.
Next, create a list of the contents of the database dump again, this time filtered to remove all extension-related commands, by the following:
pg_restore -l ~/Downloads/db.dump | grep -v 'EXTENSION' > db.list
Finally, use this list to generate a new, filtered SQL file from the original dump file:
pg_restore --no-owner -L db.list ~/Downloads/db.dump > db.sql
This creates an intermediary file db.list, which may be removed.
4. Import database to Platform.sh
note Importing a database snapshot is a destructive operation, which will overwrite data already in your database. Backing up data before completing this step is strongly recommended.
The final step is importing the SQL file to the Platform database instance. The simplest way to do so is via the following command in the project directory:
platform sql < db.sql
To specify a database or environment, add the following flags:
platform sql --relationship database -e master < my_database_snapshot.sql
If prompted for a password, supply the password from the Postgres database credentials.
Conclusion
The steps for migrating PostgreSQL data from Heroku to Platform.sh involve downloading the data from Heroku, configuring extensions and converting the dump format, then importing it to the project, making sure nothing important is being overwritten.