Backup PostgreSQL to Amazon S3
In this guide we’ll show you how to backup your PostgreSQL instance to Amazon S3.
Every Render PostgreSQL instance has daily backups that are retained for at least 7 days, but you may want more control over backups. Backing up your PostgreSQL instance to Amazon S3 with a Cron Job gives you full control over the frequency and retention of your backups.
By following this guide, you’ll be able to:
- Create AWS credentials that will enable working with Amazon S3.
- Configure and create a backup Cron Job for your database.
- Validate that the backup is working.
We will create credentials with AWS IAM to enable working with Amazon S3.
Open the AWS console and navigate to the IAM service. Open the Users view and select the
Enter a descriptive username, such as
Select AWS credential type*select
Access key - Programmatic access.
Next: Permissionsbutton to move to the
Set Permissionsview, select
Attach existing policies directlyand search for
AmazonS3FullAccess. Check the box to select
Skip through the next two views with the
Nextbuttons to move to the
Reviewview. Confirm the details of your user.
Record the access key ID (
AKIAXXXXXXXXXXXXXXXX) and the secret access key.
render.yamlfile, edit the
fromDatabasename in the Cron Job’s
DATABASE_URLenvironment variable to be the name of your PostgreSQL instance.
render.yamlfile, edit the Cron Job’s
regionto match the region of your database.
By default, the Cron Job will run the backup daily at 3 a.m. UTC. You can change the time and frequency by modifying the Cron Job’s
Commit and push your changes.
On the Render Dashboard, go to Blueprints and click the
New Blueprint Instancebutton. Select your repository (after giving Render permission to access it, if you haven’t already). Alternatively, you can click the Deploy To Render button in the Readme of the forked repo.
Enter a descriptive
Service Group Namesuch as
Backup <database name> to S3.
Fill in the environment variables:
Environment Variable Value AWS_REGION Choose the AWS region closest to the region of your database. For example, a PostgreSQL instance in Render’s Oregon region would use
us-west-2for the AWS Region US West (Oregon).
S3_BUCKET_NAME Choose a globally unique name for your bucket. For example
<your-username>-<database name>-render-postgres-backups. The name must follow Bucket naming rules.
AWS_ACCESS_KEY_ID Copy the
Access key ID(
AKIAXXXXXXXXXXXXXXXX) we saved when creating the User.
AWS_SECRET_ACCESS_KEY Copy the secret access key we saved when creating the User. POSTGRES_VERSION Enter your PostgreSQL Version. You can see the version when viewing your PostgreSQL instance. For example,
Applyto create the Cron Job.
View the newly created Cron Job and wait for the first build to finish.
Trigger Runbutton and wait for the job to finish with a
Cron job succeededevent.
Verify the backup by inspecting the contents of your S3 bucket.
That’s it! Your Cron Job will now periodically backup your PostgreSQL instance to Amazon S3.
aws CLI tool requires additional configuration when uploading large files to S3. If your compressed backup file exceeds 50 GB, add an
--expected-size flag in the the
upload_to_bucket function in
You may have an error with your IAM user if your Cron Job fails and you see an error message similar to:
An error occurred (SignatureDoesNotMatch) when calling the CreateBucket operation: The request signature we calculated does not match the signature you provided. Check your key and signing method.
Check over the Create AWS Credentials instructions.