Hey guys! Today we will learn how to restore a Postgres file backup from Heroku and add it locally on the Postgres docker container.

The idea behind this post is just to share this knowledge since I spent a lot of time aggregating these steps, so here we go!

Show all backups from Heroku:

$> heroku pg:backups --app <heroku-app-name>

This will return something like:

=== Backups
ID    Created at                 Status                               Size      Database
────  ─────────────────────────  ───────────────────────────────────  ────────  ────────
b113  2020-06-16 20:30:50 +0000  Completed 2020-06-16 20:30:51 +0000  155.43KB  DATABASE
a112  2020-06-16 07:13:12 +0000  Completed 2020-06-16 07:13:44 +0000  156.21KB  DATABASE
a111  2020-06-15 07:12:38 +0000  Completed 2020-06-15 07:12:59 +0000  155.94KB  DATABASE
a110  2020-06-14 07:14:18 +0000  Completed 2020-06-14 07:14:48 +0000  155.94KB  DATABASE
a109  2020-06-13 07:13:24 +0000  Completed 2020-06-13 07:13:55 +0000  155.94KB  DATABASE
a108  2020-06-12 07:10:20 +0000  Completed 2020-06-12 07:11:27 +0000  155.94KB  DATABASE
a107  2020-06-11 07:13:03 +0000  Completed 2020-06-11 07:13:31 +0000  155.94KB  DATABASE
a106  2020-06-10 07:12:27 +0000  Completed 2020-06-10 07:12:53 +0000  155.94KB  DATABASE
a105  2020-06-09 07:13:43 +0000  Completed 2020-06-09 07:14:17 +0000  136.70KB  DATABASE

Now copy the ID of the backup that you want to download and run the following:

$> heroku pg:backups:url <ID> --remote production --app <heroku-app-name>, example: $> heroku pg:backups:url b113 --remote production --app my-app.

This returns the URL that you need to save. The URL should look something like:

https://xfrtu.s3.amazonaws.com/a0d7744f-43tf-4f09-9816-13051ad02ce8/2020-06-16T20%3A30%3A50Z/1a893b18-c21e-4c8b-a8ce-191c86ab5698?X-Amz-Algorithm=D%RT-DEDS&X-Amz-Credential=AKIAQKDFRD2F20200617%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=2020D$RF2309Z&X-Amz-Expires=3600&X-Amz-SignedHeaders=host&X-Amz-Signature=fc61433aeef53cefe5e05d92adab98229cae8637bd22eb3b2d6f5ffced5

Now run the command to show all containers that are running docker container ls

You should get the CONTAINER ID or the Name of the Postgres container. Then run the following:

docker exec -it <container id/name> bash

This will give you a container prompt.

The next step is to update the container and install a tool called ‘curl’. To do this run the following:

$> apt-get update
$> apt-get install curl

Now we can use the URL to get the backup from Heroku.

$> curl "https://xfrtu.s3.amazonaws.com/a0d7710-Amz-SignedHeaders=host&X-Amz-Signature973bfb67435ae235b6876a1edf37ee3" > production.dump

Important: Don’t forget the quotes “http://…” around the URL. Now we can restore the database with this command:

pg_restore --verbose --clean --no-acl --no-owner -U postgres -h localhost -d <database name> production.dump

Remember that if you are using Rails and Git, you may need to go back to the commit with the same version of the DB that you restored.

That’s it! Let me know your feedback if you have any.

Thanks for reading.

---

Reviewer: Nate Mengler