Automated PostgreSQL Backups

A heavily requested feature we keep getting asked about is automated PostgreSQL backups and while we don’t natively support this feature, we tried to help our users by offering a step-by-step tutorial to backing up and restoring their PostgreSQL database on Railway.

The problem though was that while helpful, it was still a manual process and backups were being made to a local filesystem. To fix this, we created a simple node service which will automate database backups and can be deployed at the click of a button.

Before we deploy the template, we need to set up an S3 bucket on AWS to store our backups and create our access key and access secret to access our bucket.

First, we’ll sign into or create a new account on AWS. Once we’ve logged in, we’ll navigate to the Amazon S3 service. Next, we’ll navigate to the Create Bucket page and set a bucket name and region.

S3 bucket configuration

You can leave the other settings to their default values (unless you know what you’re doing) and click the Create bucket button at the bottom of the page.

Now that our bucket has been created, we will create a new IAM user to programmatically access our bucket.

To do this, navigate to your IAM dashboard and click on Users under IAM resources. Next, click the Add users button. Add a name for your user and for the credential type setting, select Access key - Programmatic access.

Add IAM user

Next, on the permissions page, click the Attach existing policies directly option and filter the policies by searching for AmazonS3 and select the AmazonS3FullAccess option.

Set user permissions

Note: Keeping AmazonS3FullAccess isn’t safe for the long run. It is suggested that you update the security policy to only allow access to that specific bucket. Here is a suggested policy to use.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "s3:PutObject",
            "Resource": [
                "arn:aws:s3:::YOURS3RESOURCENAME"
            ]
        }
    ]
}

On the next step, you can optionally add tags if you’d like and then proceed to the Review step, review the name, access type and permissions for the user and once you’re happy with the settings, click the Create User button.

Once your user is created copy the Access key ID and the Secret access key values as we will use them in our automated backups service.

User access key and secret

Now that we have set up everything on AWS, we can proceed to deploy our template. Click the button above and configure the AWS specific values as mentioned below.

• AWS_ACCESS_KEY_ID: The Access key ID from our IAM user
• AWS_SECRET_ACCESS_KEY: The Secret access key from our IAM user
• AWS_S3_BUCKET: The name of our S3 bucket
• AWS_S3_REGION: The region of our S3 bucket (example: us-west-2)

Next, we need to set the value for the BACKUP_DATABASE_URL variable. If your database is part of the same project, you can simply set this to ${{ DATABASE_URL }} and Railway will automatically infer the value. If your database isn’t in the same project, you can simply copy the Postgres Connection URL for your database and enter the value here.

Copy the Postgres Connection URL

Optionally, you can also configure the cron schedule by setting the BACKUP_CRON_SCHEDULE variable. By default, the cron is set to run daily at 5 AM UTC. You can use CronTab to play around with and set up different cron schedules.

Once you’ve added all the variables, simply click the Deploy button and your service should be live. Here’s a link to our demo project running this service and backing up a PostgreSQL database within the same project.

Backup cron in action

Our data should is now being automatically backed up to our S3 bucket. If we ever want to restore this data to a database, we can do so very easily using pg_restore.

First, unzip the backup which is stored as a tar.gz file and then run:

pg_restore -d 'DATABASE_URL' BACKUP_FOLDER

In the above snippet, replace the DATABASE_URL with the connection URL of the database you’d like to restore the data to and the BACKUP_FOLDER with the name of the folder to which you extracted your backup zip file and voila, your backup has been restored!

We hope that this post helps anyone looking for automated PostgreSQL backups on Railway. The template itself is a very simple service written in JavaScript and can easily be extended to backup other databases and/or use other storage solutions, we’d love for users to extend, improve and contribute to the service.

If you do end up wiring up your own version of this service, feel free to reach out to us on Discord as we’d be happy to feature it both on this blog and our templates page!