Database backup archives

1

We’d like to archive our DB backups to long-term storage (render only keeps them around for a week). Is there a way to get programmatic access to the backups so we can download them?

4

What you could do is create a script to dump the database and then archive it. You can run pg_dump on your render databases anytime.

PGPASSWORD=<YOUR_DB_PASS> pg_dump \
  -h postgres.render.com \
  -U <YOUR_DB_USER> \
  --format=custom --no-acl --no-owner \
  <YOUR_DB_NAME> > <YOUR_DUMP_NAME>.dump

Here is a script you can modify to help you with this:
https://sqlbak.com/blog/how-to-automate-postgresql-database-backups-in-linux

1 Like
5

We don’t have this yet, but do plan to support a public api endpoint that would provide your desired functionality. The current plan is for the API to return the same download links we use in the dashboard.

For the short term, @jclarke’s suggestion might help you get started.

1 Like
6

Any update on the status of an API to get backup download links?

Our database uses too much memory when running pg_dump to successfully backup in a cron job (OOM killed even on 8GB Pro Plus instance), so we’re unable to automate backups using the suggested workaround.

Lack of automated archiving is a pretty big let-down coming from Heroku, and has us evaluating alternatives (e.g. Google Cloud SQL in same region) to Render-hosted pg.

7

We’ve been in communication with Jordan here via chat and narrowed down that the issue with the cron was due to hitting the 2GB limit on /tmp. Our recommendation here would be to stream the output of pg_dump directly to s3 to avoid running into these limits.

We are aware that this is just a workaround for the time being, and do have an open feature request around this, which we’ll update with any progress. https://feedback.render.com/features/p/database-backup-rotation-to-s3 We unfortunately do not have an ETA around this that we can provide at the time.

1 Like
8

A backup is a copy of your current data that you use to restore original data if it’s ever damaged . An archive is historical data you must keep long-term retention reasons, such as compliance.

If you want to become a data analyst or become an expert in data science, join this Data Science track program.