How to migrate a PostgreSQL database from Heroku to Platform.sh
Migrate data stored in a Heroku PostgreSQL resource to a Platform.sh service.
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
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.
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
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
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
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.
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.