Moving a postgresql database from heroku to your dokku instance

I have enjoyed hosting a few apps on Heroku, but recently wanted to give dokku a shot.  A few of my apps have production data stored in postgres databases,and I wanted to move that data over to my dokku containers for testing.

I found a process that seemed to work fairly painlessly for me.  here are the steps:

Backup the database on Heroku and retrieve backup file:

connect to heroku using the heroku command line (don’t have it?  use ‘apt-get install heroku-toolbelt’ in linux).  Login using ‘heroku login’ and enter your Heroku credentials when prompted.

$ heroku login
Enter your Heroku credentials.
Email: <your email>
Password (typing will be hidden): <your password>
Authentication successful.

Backup the database:

$ heroku pg:backups capture --app <YOURAPP>

Use Ctrl-C at any time to stop monitoring progress; the backup
will continue running. Use heroku pg:backups info to check progress.
Stop a running backup with heroku pg:backups cancel.

HEROKU_POSTGRESQL_CHARCOAL ---backup---> b004

Backup completed

Get the public url for the backup file:

$ heroku pg:backups public-url --app <YOURAPP>

The following URL will expire at 2015-05-16 18:30:19 +0000:
 <"Very Long AWS URL">

Paste that url into your browser (or use wget, but I couldn’t get that to work with the AWS url, if you know a way to make that work, please post a comment).  And download the file.

Upload that file to an easily accessible directory on your dokku host.

Restoring the database to your dokku app

I found that deleting, creating, and finally restoring the database worked well.  First, let’s delete the current database:

$ dokku postgresql:delete <YOURAPP>

-----> PostgreSQL container deleted: postgresql/<YOURAPP>

Next, re-create a clean database.  This will cause dokku to redeploy the app as well:

$ dokku postgresql:create <YOURAPP>

-----> Setting config vars
 DATABASE_URL: postgres://root:GfOLmcTje92sJ79x@<YOUR SERVER IP>:32776/db
-----> Restarting app <YOURAPP>
-----> Releasing <YOURAPP> ...
-----> Deploying <YOURAPP>...
-----> Running pre-flight checks
 CHECKS file not found in container: running simple container check...
-----> Waiting for 35 seconds ...
-----> Default container check successful!
=====> <YOURAPP> container output:
 [2015-05-16 18:25:52] INFO WEBrick 1.3.1
 [2015-05-16 18:25:52] INFO ruby 2.0.0 (2015-04-13) [x86_64-linux]
 [2015-05-16 18:25:52] INFO WEBrick::HTTPServer#start: pid=14 port=5000
=====> end <YOURAPP> container output
-----> Running post-deploy
-----> Shutting down old container in 60 seconds
=====> Application deployed:
 http://<YOURAPP URL>


-----> <YOURAPP> linked to postgresql/<YOURAPP> database

-----> PostgreSQL container created: postgresql/<YOURAPP>

 Host: <YOUR SERVER IP>
 Port: 32776
 User: 'root'
 Password: 'thepassword'
 Database: 'db'

 Url: 'postgres://root:GfOLmcTje92sJ79x@<YOUR SERVER IP>:32776/db'

Now all that’s left is to restore the database, I had difficulty doing this, but found this syntax (local postgresql command piped into dokku command) here:

pg_restore -O <backup_name.sql> | dokku postgresql:restore <database>

<MUCH SQL OUTPUT>
-----> <YOURAPP> database restored

Your production database should now be restored to your dokku app and data should be synced to the database at the time of backup.