Get Started

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.
  • 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:

List of backups in the Render Dashboard

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:

List of backups in the Render Dashboard

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

With Point-in-time recovery (PITR), your database is continually archived. This can help you recover your database from unexpected data loss, for example, due to an unintentional table drop or deletion of data. With PITR, you can restore into a new database at the point before the data loss occurred, up to 7 days ago.

When you trigger a restore via PITR, your data are restored into a new database. This allows you to examine and validate the data before updating all of your services to use the new database. If you don’t find the data you were looking for, you can attempt a restore at a different time.

Before restoring, you have the opportunity to modify the instance type, Datadog API key, and project which will otherwise be copied from the source database. The IP address allow list will always be copied from the source database. The default database name and connection strings to connect to your database will always be different from the source database and can be found in your new database settings page. You will need to manually delete your previous database after you have verified the new database is working as expected.

PITR is available on Team, Enterprise, and Organization plans for new Pro database instance types and above. If your database has been upgraded, you will only be able to restore to a time corresponding to when your database was on the Pro instance type or above.

Databases created before April 2023 may not have PITR enabled. Email us at support@render.com to enable PITR on an existing eligible database. Please include when you would like to schedule the brief downtime to enable PITR.

Performing a restore

To begin a restore of your database, go to the Backups section of your database settings and click the Restore Database button. Database Restore Section

Database Restore Modal

  1. [Optional] Change the name of your new database, or use the suggested default.
  2. Determine the date and time to which you want to restore your database. The time can be no later than 10 minutes prior to the current time.
  3. Click Restore to initiate the restore, and you will be taken to the settings page of your new database.
  4. Your database status will advance from Recovery In Progress to Creating, and then Available when it is ready to accept connections.
  5. Validate that the data in your new database is what you expect.
  6. Update all of the connection strings on your services to point to your new database.
  7. Delete or Suspend your old database.

Using with PostgreSQL 11 and 12

We’ve rolled out an improvement for PITR on Postgres 11 and 12 to ensure PITR succeeds for points in time when there is no database activity. Databases on other versions of Postgres do not require the change.

Postgres 11 or Postgres 12 databases with PITR enabled after 7/5/23 already have the change. Users on Postgres 11 or Postgres 12 with PITR enabled before 7/5/23 can contact support@render.com to schedule the brief downtime to update their database.