PostgreSQL Backups and Recovery

Render provides data backup and recovery features for your PostgreSQL database:

• Standard backups. Render automatically takes a complete backup of every paid database once per day. Backups are available to download for at least seven days after they’re created.
  • You can also trigger a manual backup for a database using a Standard instance type or higher.
• Point-in-time recovery. Render can continuously archive your database, enabling you to restore it to any point in time from the last seven days.
  • Point-in-time recovery requires a team account and a database using a Pro instance type or higher.

Standard backups

Render does not back up free PostgreSQL databases. To enable backup features, upgrade to a paid instance type.

Render automatically takes a complete backup of every paid database once per day. Backups are available to download from the Render Dashboard for at least seven days after they’re created:

Render performs your database’s first automatic backup 24 hours after you create the database.

Each backup consists of two compressed artifacts: a SQL-based backup (.sql.gz) and a filesystem-based backup (.tar.gz). Both artifacts contain all data from all databases in your PostgreSQL instance at the time of capture.

Restoring from a backup

You can use a SQL-based (.sql) backup file to restore your data to a running PostgreSQL instance. These backup files include SQL commands to drop databases and recreate them, to prevent data duplication when restoring.

1. Go to your database’s Recovery page and click the .sql.gz download link for a recent backup.

2. Obtain the External Database URL for the database you want to restore into.

3. Run the following commands, providing your backup file and the target database URL where indicated:

   # First unzip the backup file
   gzip -d $backup_file
   
   # Restore this backup to your database using its external connection
   # string (available in the dashboard).
   psql $external_database_url -f $backup_file

We’re happy to help with restores and disaster recovery. Just contact us on Render Community or email us at support@render.com.

Triggering a manual backup

If your database uses the Standard instance type or higher, you can trigger a manual backup from the Render Dashboard. From your database’s Recovery page, click Trigger Backup:

Note that backups can take a while to complete, especially for larger databases. You can’t trigger a manual backup if another backup is already in progress.

Manual backups appear on your database’s Recovery page alongside automatic backups.

For more control over the frequency and storage of your backups, see Backup PostgreSQL to Amazon S3. This guide walks through creating a Cron Job that uploads SQL backups from pg_dump to S3.

How Render creates backups

• To create SQL-based backups, Render uses pg_dump for instances with a single database and pg_dumpall for instances with multiple databases.
• Backups using pg_dumpall include the --no-privileges flag, and they omit GRANT and REVOKE commands. We omit these commands to minimize errors when performing a restore.
• If you need to set custom access privileges for your restored database, you can configure them after the restore completes.
• Because pg_dumpall itself uses pg_dump, some diagnostic messages from pg_dumpall mention pg_dump.

Point-in-time recovery

Point-in-time recovery requires a team account and a database using a Pro instance type or higher.

Point-in-time recovery (PITR) enables you to restore your database to any previous state from the past 7 days. This is useful for recovering from unexpected data loss, such as an unintentional table drop.

When you trigger PITR, Render spins up a new database instance that reflects your original instance’s past state at a specified time. This enables you to validate the new instance in isolation before updating your services to use it.

• If your recovery instance reflects the state you expect, you can then configure your other services to use it instead of the original instance.
• Otherwise, you can delete the recovery instance and initiate a new recovery using a different point in time.

Performing a recovery

If you recently upgraded your database to a Pro instance type or higher, you can’t restore to a point in time before the upgrade.

1. In the Render Dashboard, select your database from the service list and open its Recovery page.

2. Scroll down to the Point-in-Time Recovery section and click Restore Database:

   

   The following form appears:

   

3. Provide a name for the new database instance.

4. Specify an available date and time to restore to.

   • You can’t restore to a time that’s within ten minutes of the current time.

5. Select whether to Copy Existing Settings.

   • If you select No, you’ll have the option to specify a different instance type, Datadog API key, and/or project for the recovery instance.
   • The recovery instance always copies the IP address allow list from the original instance.

6. Click Start Recovery to initiate the restore.

   • If you selected No in the previous step, click Customize Recovery and then provide your new settings for the recovery instance.

7. In your service list, the recovery instance’s status will advance from Recovery In Progress to Creating, and then to Available when it’s ready to accept connections.

8. Validate that the data in the recovery instance is what you expect.

   • You can connect to the recovery instance from your terminal using the PSQL Command provided on the database’s Info page.

9. Update your services and other tools to connect to the recovery instance instead of the original instance.

   • The recovery instance’s name and connection strings are available in the Render Dashboard.
   • Environment groups enable you to update the connection string for multiple services in one place.

10. After verifying that all systems are successfully connected to the recovery instance, you can delete or suspend the original instance.

The recovery is complete. Your recovery instance is now your primary instance.