Following the abandon of Google Analytics and the beginning of umami usage (you can read the details in this post) I encountered in a little trouble: how to make a backup of the umami database, without having sFTP access to Heroku VPS, because of the free plan?

I’ve found a solution that is a bit tricky (compared to a normal backup) but it works.

It requires having Heroku CLI installed, can be done via

brew tap heroku/brew && brew install heroku

then run (replace ‘blog-stats’ with the name of your Heroku app)

heroku pg:backups:capture --app blog-stats

it will take a dozen of seconds and in the end it will display the number of your backup:

heroku pg:backups:capture --app blog-stats 
Starting backup of postgresql-deep-11593... done

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.

Backing up DATABASE to b005... done

b005 is the name/number of your backup. But having a backup on a remote server is not very useful if something breaks, (and the Heroku free plan delete the backups after every 2 of them), so in order to download it on your local machine/storage, just write:

heroku pg:backups:url b005 --app blog-stats

remember to change the number of backup ‘b005’ and ‘blog-stats’ to your backup number and app name. It will generate a temporary (60 mins) AWS link with the DB that can be easily downloaded and moved where you prefer.

Or you can download directly to your local folder using: heroku pg:backups:download --app blog-stats

Getting backup from ⬢ blog-stats... done, #5
Downloading latest.dump... ████████████████████████▏  100% 00:00 45.07KB   

That’s all. If you want to check when you’ve done the last backup, the name and size:

heroku pg:backups --app blog-stats
=== Backups
ID    Created at                 Status                               Size     Database
────  ─────────────────────────  ───────────────────────────────────  ───────  ────────
b005  2022-02-02 08:12:27 +0000  Completed 2022-02-02 08:12:37 +0000  45.07KB  DATABASE
b004  2022-02-02 07:03:31 +0000  Completed 2022-02-02 07:03:42 +0000  45.02KB  DATABASE

=== Restores
No restores found. Use heroku pg:backups:restore to restore a backup

=== Copies
ID    Started at                 Status                               Size     From       To
────  ─────────────────────────  ───────────────────────────────────  ───────  ─────────  ───────────
c003  2022-02-01 14:28:33 +0000  Completed 2022-02-01 14:29:29 +0000  44.28KB  From Name  Target Name
c002  2022-01-27 11:53:19 +0000  Completed 2022-01-27 11:53:57 +0000  28.70KB  From Name  Target Name

In order to restore the backup to your Heroku app:

heroku pg:backups:restore b005 --app blog-stats

Or if you want to restore the backup from your local machine, upload it somewhere (I used iCloud link), and write:

heroku pg:backups:restore '' -a blog-stats

 ▸    WARNING: Destructive Action
 ▸    This command will affect the app blog-stats
 ▸    To proceed, type blog-stats or re-run this command with --confirm blog-stats

Hope this will help. For more options and info there’s an article here: Heroku PGBackups