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.
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.
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.
Go to your database’s Recovery page and click the
.sql.gzdownload link for a recent backup.
Obtain the External Database URL for the database you want to restore into.
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
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.
- To create SQL-based backups, Render uses
pg_dumpfor instances with a single database and
pg_dumpallfor instances with multiple databases.
- Backups using
--no-privilegesflag, and they omit
REVOKEcommands. 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.
pg_dump, some diagnostic messages from
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 firstname.lastname@example.org to enable PITR on an existing eligible database. Please include when you would like to schedule the brief downtime to enable PITR.
To begin a restore of your database, go to the Backups section of your database settings and click the
Restore Database button.
- [Optional] Change the name of your new database, or use the suggested default.
- 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.
- Click Restore to initiate the restore, and you will be taken to the settings page of your new database.
- Your database status will advance from Recovery In Progress to Creating, and then Available when it is ready to accept connections.
- Validate that the data in your new database is what you expect.
- Update all of the connection strings on your services to point to your new database.
- Delete or Suspend your old database.
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 email@example.com to schedule the brief downtime to update their database.