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