Last updated December 14, 2022

Backups of your data are crucial to any application. Every Heroku Postgres database on the Standard tier or higher comes with an automatic, behind-the-scenes Continuous Protection mechanism that captures physical backups for disaster recovery.

For testing, setting up environments, and migrating data, logical backups offer more portability than physical backups. Heroku Postgres has a PGBackups feature that allows you to take manual and scheduled logical backups. These backups are in a compressed binary format, and are much smaller in size than your database’s actual size on disk.

This article explains how to take manual and scheduled logical backups, show existing backups, restore backups, and transfer data directly between two databases.

Manual Backups

Create a Manual Backup

By default, pg:backups operates against your primary database, identified by the DATABASE_URL config var:

$ heroku pg:backups:capture --app example-app
Hit Ctrl-C at any time to stop watching progress; the backup will
continue running. Stop a running backup with heroku pg:backups:cancel.

HEROKU_POSTGRESQL_BLACK (DATABASE_URL)  ----backup--->  b251

Running... done

Create a Manual Backup on Another Database

If you have multiple databases on your application, choose which one to back up by specifying the database name:

$ heroku pg:backups:capture HEROKU_POSTGRESQL_PINK
Hit Ctrl-C at any time to stop watching progress; the backup will
continue running. Stop a running backup with heroku pg:backups:cancel.

HEROKU_POSTGRESQL_PINK  ----backup--->  b252

Running... done

Use the flag --verbose to see logs as your backup progresses.

Cancel Manual Backup Creation

To stop a backup, use the cancel command:

$ heroku pg:backups:cancel

Canceled backup b252

Manual Backup Retention Limits

There’s a limit to the number of manual backups you can retain, depending on your database plan.

If you’ve reached this limit and take additional backups, the capture command automatically deletes the oldest manual backup before capturing a new one.

Scheduled Backups

In addition to manually triggered backups, you can schedule regular automatic backups. These backups run daily against the specified database.

Set Up a Backup Schedule

$ heroku pg:backups:schedule DATABASE_URL --at '02:00 America/Los_Angeles' --app example-app

The --at option uses a 24-hour clock to indicate the hour of the day that you want the backup taken. It also accepts a timezone in either the full TZ format (America/Los_Angeles) or the abbreviation (PST), but we recommend using the full TZ format.

You can lose the backup schedule if:

• You upgrade the database from an Essential-tier plan to a plan in another tier.
• A backup is restored to a new database.
• You update your Heroku Postgres plan with a follower changeover. The schedule from the original database remains associated to the original database. You must create a schedule for the promoted database if it doesn’t exist.

Stop Scheduled Backups

To stop regular backups, use unschedule:

$ heroku pg:backups:unschedule DATABASE_URL --app example-app

View Backup Schedules

To view current schedules for your app, use schedules:

$ heroku pg:backups:schedules --app example-app
Current backup schedules:
RED: daily at 2:00 (America/Los_Angeles)

Scheduled Backups Retention Limits

Scheduled backups have a different retention policy as compared to manual backups. This policy is also based on the database plan.

• A daily backup is taken each day at your scheduled time. Daily backups are retained for the last 7 days. That means that 7 backups exist, one for each of the last 7 days.
• A weekly backup means that one backup is saved over a 7-day period. For example, a Standard-4 database has 4 weeks of weekly backups, one for each of the last 4 weeks.
• A monthly backup means that 1 backup is saved over the course of a month. For example, a Premium-0 database has 12 monthly backups, one for each of the last 12 months.

Downloading Your Backups

Download via a URL

You can create a publicly accessible backup URL with the pg:backups:url command. This URL is useful for exporting your data outside of Heroku Postgres When the command is specified without a backup id, it returns the latest available backup URL.

$ heroku pg:backups:url b001 --app example-app
The following URL will expire at 2015-04-07 18:35:50 +0000:
"http://s3.amazonaws.com/xkpgbackups/app1234567@heroku.com/b004.dump?AWSAccessKeyId=ABCD1234&Expires=1289261668&Signature=3mMBeKISewgEUDT%2FL5mRz4EYS4M%3D"

The pg:backups:url command outputs when the URL expires, along with the URL itself. If you pipe the URL to other commands, pg:backups:url refrains from adding the expiration information to the output so that all subsequent commands only have the URL. For example, if to list the URL in the terminal without the extra information:

$ heroku pg:backups:url --app example-app | cat
http://s3.amazonaws.com/xkpgbackups/app1234567@heroku.com/b004.dump?AWSAccessKeyId=ABCD1234&Expires=1289261668&Signature=3mMBeKISewgEUDT%2FL5mRz4EYS4M%3D

Download via the Command Line

To download your backup via the command line, you can use the download command:

$ heroku pg:backups:download

Check Backup Status and Info

To list your backups and their statuses, run the following:

$ heroku pg:backups --app example-app
=== Backups
ID    Backup Time                Status                              Size    Database
----  -------------------------  ----------------------------------  ------  --------
b013  2015-03-18 19:03:16 +0000  Running                                     IVORY
b011  2015-02-18 17:55:38 +0000  Finished 2015-02-18 17:55:39 +0000  1.9GB   IVORY
b010  2015-02-17 19:14:43 +0000  Finished 2015-02-17 19:14:48 +0000  1.9GB   IVORY
b004  2015-02-11 19:00:55 +0000  Finished 2015-02-17 19:14:48 +0000  1.9GB   IVORY

==== Restores
ID    Restore Time               Status                              Size    Database
----  -------------------------  ----------------------------------  ------  --------
r002  2015-03-16 17:33:19 +0000  Finished 2015-03-16 17:33:19 +0000  1.9GB   IVORY
r001  2015-03-15 12:13:44 +0000  Failed 2015-03-15 12:13:47 +0000    1.7GB   IVORY

To get more details about a given backup, use the info command:

$ heroku pg:backups:info b017 --app example-app
=== Backup info: b017
Database: HEROKU_POSTGRESQL_IVORY
Started:  2013-06-24 17:11.28 UTC
Status:   Running
Type:     Manual
Size:     1.2GB
Schema:   0bff3ac

With the optional --verbose flag, you can also see the backup’s logs. If the backup is still running, the command prints the ongoing logs until the backup finishes or you cancel the command by typing CTRL+C.

Delete a Backup

You can manually delete a backup to remove obsolete backups or to make room for new captures. Use the backup ID to specify which backup to remove.

$ heroku pg:backups:delete b101 --app foo
Deleting b101... done

Restore a Backup

To restore a backup, use the restore command:

$ heroku pg:backups:restore b101 DATABASE_URL --app example-app

This command restores backup id b101 to the specified database URL in the app example-app. Note: you can omit the backup id and the target database to restore the latest backup to DATABASE_URL, otherwise you must provide both the backup id and target database.

You can also restore from a backup on another app (from example-app to example-staging app):

$ heroku pg:backups:restore example-app::b101 DATABASE_URL --app example-staging-app

Or from a publicly accessible URL:

$ heroku pg:backups:restore 'https://s3.amazonaws.com/me/items/mydb.dump' DATABASE_URL -a example-app

Over time, a database grows on disk due to table bloat and unused index data. As a result, restoring a backup into a new database almost always result in a reduction in overall disk size as reported by pg:info.

Direct Database-to-Database Copies

In addition to backups and restores, pg:backups also provides the ability to transfer data directly between databases.

To perform a transfer, run the following:

$ heroku pg:copy COBALT GREEN --app example-app

This command copies all data from the COBALT database to the GREEN database in the example-app app.

You can also transfer directly from a database on another app, for example, to copy production data into a staging app for testing purposes.

$ heroku pg:copy example-app::HEROKU_POSTGRESQL_ORANGE_URL GREEN --app example-staging-app

This command copies data from the HEROKU_POSTGRESQL_ORANGE database of the example-app to the GREEN database in example-staging-app.

Visibility

Heroku Postgres must connect to your database using your credentials in order to capture a backup. This connection counts against your plan connection limit. You can identify connections made by Heroku Postgres by running the following command from your terminal:

$ heroku pg:psql -c "select * from pg_stat_activity where application_name = 'Heroku Postgres Backups'"

Data Residency

All backups captured via pg:backups are stored in the U.S., regardless of where your database is located.