Quick Look at Heroku PostgreSQL Backups

Table of Contents

Data backups are critical for every application. Every Heroku Postgres database on the Standard or above tier has an automated, behind-the-scenes Continuous Protection system that records physical backups for disaster recovery. Logical backups are more portable than physical backups for testing, setting up environments, and moving data. Heroku Postgres includes PGBackups, which lets you to do manual and scheduled logical backups. These backups are in compressed binary format and are substantially lower in size than the current size of your database on disk. This article will teach you how to do manual and scheduled logical backups, view existing backups, recover backups, and transfer data directly between two databases.

As a web developer, you must guarantee that your data is backed up in case of a calamity. Here's when Heroku Postgres backups come in handy. Heroku Postgres is a relational database service that offers data protection and recovery. In this article, we'll go through Heroku Postgres backups in depth, explaining what they are and how to utilize them efficiently for your web application.

What is Heroku Postgres Backups?

Heroku Postgres backups are a feature that allows you to produce a time-stamped snapshot of your database. In the event of data loss or corruption, these backups can be utilized to restore your database to its previous condition. Heroku Postgres backups are performed automatically every 24 hours, and you may also perform manual backups as needed. This is a great feature for those who need to ensure that their data is backed up regularly and securely. The automated backups are incredibly convenient, as they are performed without any manual intervention, and they can be easily restored in the event of a data loss.

Continuous Archiving and Point-In-Time Recovery (PITR) technologies are used in Heroku Postgres backups. Continuous Archiving guarantees that all database updates are preserved in archive files. Based on the archive files, PITR allows you to restore your database to a specified point in time. This guarantees that you can restore your database to its original condition at the time of backup.

Only use PGBackups for moderately loaded databases up to 20 GB in size. For databases that are heavily loaded, greater than 20 GB, or have multiple schemas or huge items, the backup procedure may slow out. Heroku Postgres intends to implement continuous protection for data backup and recovery across all production environments. Logical backups, such as those performed by PGBackups, are better suited for usage in circumstances where data mobility is required.

How to Create a Backup Manually

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


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

Alternatively, you can run the pg:backups:capture command and provide the name of the database add-on you want to capture:

USAGE
  $ heroku pg:backups:capture [ADDON]

OPTIONS
  -a, --app=app  (required) app to run command against

EXAMPLE
  $ heroku pg:backups:capture postgresql-sinuous-83720 --app example-app

Do a Manual Backup on a Different 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.

You can use the flag --verbose to see logs as your backup progresses.

Cancelling Manual Backups

To stop a backup, use the cancel command:

$ heroku pg:backups:cancel

Scheduled Backups

You may plan automated backups in addition to manually triggered backups. These backups are performed on a daily basis against the selected database. This is to ensure that any data that is lost or corrupted can be restored quickly and easily. The backups are stored securely on a remote server, and can be accessed from any device with an internet connection.

To set up a backup schedule, use this command:

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.

Stop Scheduled Backups

To stop regular backups, use unschedule:

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

Check Backup Schedules

To check your backup schedules, you can view them by using the schedules command:

$ heroku pg:backups:schedules --app example-app

Export-Import

On the surface, PGBackups seems to be a tool for taking frequent backups of your Heroku Postgres database. Yet, due to its general-purpose design and usage of common PostgreSQL functions, it is also a helpful tool for exporting to and importing from external PostgreSQL databases.

If you have a Postgres instance on your local system, you may automate the procedure by utilizing the pg:push and pg:pull CLI commands instead of the dump and restore technique.

Export

PGBackups creates backup files using the native PostgreSQL pg dump utility, making it simple to export to other PostgreSQL installations. The custom format option in pg_dump is used in the generated backup file. Custom format choices result in backup files that are substantially smaller than plain-text format. PGBackups are designed for moderately loaded databases up to 20 GB in size.

With a moderate load, competition for the I/O, RAM, and CPU required for backing up a bigger database becomes prohibitive, and the longer run time increases the likelihood of a mistake that stops your backup capture prematurely. See Capturing Logical Backups on Bigger Databases for databases greater than 20 GB.


Manual Dump with pg_dump

If you need a partial backup of your Heroku Postgres database or a backup in a non-custom format, you can use pg_dump to create your backup.

For example, to create a plain-text dump from your Heroku Postgres database:

$ pg_dump -Fp --no-acl --no-owner mydb.dump

Use any of the supported pg_dump options as needed, such as --schema or --table to create dumps of specific schemas or tables of your database.


Restore to Local Database

Using the pg restore tool, load the dump into your local database. If items already exist in a local copy of the database, you may see inconsistencies while doing a pg_restore. Because of differences between your Heroku database and a local database, this procedure normally produces some warnings, but they are generally safe to ignore.

$ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump

If you’re using an old version of pg_restore, you can see an error such as pg_restore: [archiver] unsupported version (1.13) in the file header when you try to run pg_restore. Ensure that the pg_restore version you’re using is up-to-date and compatible with the version of the exported database. You can check your version of pg_restore by running pg_restore --version.

You can optionally use the --jobs flag with pg_restore to parallelize the restore of the dump. Only the custom and directory archive formats are supported with this option. More on this option can be found in the Postgres documentation.


Import

PGBackups is a useful tool for importing database dumps from external sources into your Heroku Postgres database.

If you're importing data as part of the setup for a new app, you must first build and configure the app on Heroku before proceeding with the import.


Create Dump File

Dump your local database in a compressed custom format using the open-source pg_dump tool:

# set the password in an environment variable
export PGPASSWORD=mypassword # linux/mac
set PGPASSWORD=mypassword # windows
# create the database dump
$ pg_dump -Fc --no-acl --no-owner -h localhost -U myuser -d mydb -f mydb.dump


Import to Heroku Postgres

In order for PG Backups to access and import your dump file, you must upload it somewhere with an HTTP-accessible URL.

The pg:backups restore command drops any tables and other database objects before recreating them.

The pg:backups:restore command expects the provided backup to use the compressed custom format. Other backup formats result in restore errors.
While Heroku PGBackups can download any backup files that are directly accessible through a URL, we recommend using Amazon S3 with a signed URL. Generate a signed URL using the AWS console:

$ aws s3 presign s3://your-bucket-address/your-object

Use the raw file URL in the pg:backups restore command:

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

DATABASE_URL represents the HEROKU_POSTGRESQL_COLOR_URL of the database you wish to restore to. You must specify a database configuration variable to restore the database.

If you’re using a Unix-like operating system, make sure to use single quotes around the temporary S3 URL, because it can contain ampersands and other characters that confuse your shell. If you’re running Windows, you must use double quotes.

When you’ve completed the import process, delete the dump file from its storage location if it’s no longer needed.

Conclusion

In conclusion, Heroku Postgres backups are a crucial feature that ensures your data is safe and recoverable in case of data loss or corruption. If you're looking for an automated backup service that offers a hassle-free setup and doesn't require your credit card for a 14-day free trial, try Ottomatik! With Ottomatik, you can worry less about your data being in constant danger due to various malicious threats. Ottomatik provides a comprehensive suite of tools to help protect your data from potential harm. It offers a wide range of features, from automatic backups to real-time monitoring, that can help you keep your data safe and secure.


Ready to secure your backups today?

Try for free
14 Day Free Trial • Cancel Anytime • No Credit Card Required