PostgreSQL Recovery and Backups

Need to recover lost data? Start here.

We’re happy to help with restores and disaster recovery. Reach out to our support team in the Render Dashboard.

Render continually backs up paid PostgreSQL databases to provide point-in-time recovery (PITR). This enables you to restore your database to any previous state from the past few days, so you can recover from an accidental table drop or other data loss.

Your database’s available recovery window depends on your workspace plan:

Workspace plan	Recovery window
Hobby	Past 3 days
Professional or higher	Past 7 days

When you trigger PITR, Render spins up a new database instance that reflects your original instance’s state at a specified time in the past. 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.

Perform a recovery

Render does not provide recovery capabilities for free PostgreSQL databases.

To enable these capabilities, upgrade your instance type.

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:

   

3. The following form appears:

   

4. Provide a name for the new database instance.

5. 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.

6. 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.

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

8. 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.

9. 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.

10. 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.

11. 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.

Logical backups

You can create and export logical backups of your database in the Render Dashboard. Download these backups for long-term retention or to restore into a new database instance.

Render retains logical backups for seven days after creation, regardless of your workspace plan.

Render does not create logical backups for free PostgreSQL databases.

To create a logical backup for a free instance, do one of the following:

• Upgrade your instance type.
• Use the pg_dump utility from your local machine.

Trigger a backup

From your database’s Recovery page, click Create export:

You can’t trigger a backup if another backup is in progress for the same database.

When it’s ready, your database’s new backup appears in the table on its Recovery page. Each logical backup is provided as a compressed SQL file (.sql.gz). Click any backup’s download link to save it to your local machine.

Interested in automating logical backup retention?

See Backup PostgreSQL to Amazon S3. This guide walks through creating a cron job that uploads SQL backups from pg_dump to S3.

Restoring from a backup file

Read this before you proceed:

• Backup files include SQL commands to drop relevant databases and then recreate them.
  • Do not restore into a database that contains important data in the same schema as the backup.
• In the event of data loss, we recommend instead using point-in-time recovery to restore your database.
  • PITR almost always enables you to recover more recent data than what’s available in your latest backup.

You can use an exported backup to restore your data into a PostgreSQL instance running on Render or your local machine:

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

   • The downloaded backup’s filename indicates its time of creation (e.g., 2024-11-28T00_36Z.sql.gz).

2. If you’re restoring into a Render-hosted database, obtain its external database URL.

3. Run the following commands, substituting your backup’s filename and the target database URL where indicated:

   # Unzip the backup file
   gzip -d BACKUP_NAME.sql.gz
   
   # Connect to the database's URL and execute the logical backup
   psql DATABASE_URL -f BACKUP_NAME.sql

   Note above that after unzipping the backup file, its extension changes from .sql.gz to .sql.

How Render creates backups

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