Automating backups of PostgreSQL database to S3

Question

Pulumi · Accepted Answer

To automate backups of a PostgreSQL database to an S3 bucket, we will use a combination of resources. Firstly, we require a PostgreSQL database. For this, we can deploy an AWS RDS instance that is running PostgreSQL. Next, we'll set up S3 backup storage, ensuring that we have an S3 bucket where we can store our database backups.

To perform the backup operation itself, we need to create a process that takes the database's data and saves it to the S3 bucket. This is not a direct feature of any Pulumi resource, but we can achieve it by creating a custom script and running it on an AWS Lambda function or using an EC2 instance that periodically performs the backup using a cron job. We can use AWS services like Data Pipeline, but it's not within the scope of Pulumi's resources such as `aws.rds.Snapshot` which enables automatic backup within AWS RDS itself, not to S3.

Please ensure you have the necessary permissions and your AWS provider is correctly set up in Pulumi before running this program.

Here is a Pulumi TypeScript program that sets up a PostgreSQL RDS instance and an S3 bucket:

```typescript
import * as pulumi from "@pulumi/pulumi";
import * as aws from "@pulumi/aws";

// Create an AWS resource (S3 bucket)
const bucket = new aws.s3.Bucket("my-database-backup-bucket", {
    acl: "private",  // Access control list
});

// Create an RDS instance that will run PostgreSQL
const dbInstance = new aws.rds.Instance("my-database-instance", {
    engine: "postgres",
    instanceClass: "db.t3.micro",
    allocatedStorage: 20,
    dbSubnetGroupName: "my-dbsubnetgroup", // Replace with your DB Subnet Group
    vpcSecurityGroupIds: ["sg-12345678"], // Replace with your VPC Security Group ID
    username: "mydatabaseuser",  // Replace with your desired username
    password: "mydatabasepassword",  // Replace with your desired password
    parameterGroupName: "default.postgres9.6",  // Use the default parameter group for PostgreSQL 9.6
    skipFinalSnapshot: true,  // Careful, only use in development as this will skip creating final backup when deleting
});

// This is an inline policy to allow our RDS instance to access the bucket
const dbBucketAccessPolicy = new aws.iam.Policy("dbBucketAccessPolicy", {
    policy: bucket.arn.apply(arn => JSON.stringify({
        Version: "2012-10-17",
        Statement: [{
            Action: [
                "s3:ListBucket",
                "s3:GetBucketLocation",
                "s3:ListBucketMultipartUploads",
                "s3:ListBucketVersions",
                "s3:GetObject",
                "s3:GetObjectVersion",
                "s3:GetObjectTagging",
                "s3:PutObject",
                "s3:PutObjectTagging",
                "s3:DeleteObject",
                "s3:AbortMultipartUpload"
            ],
            Resource: [arn, `${arn}/*`],
            Effect: "Allow",
        }],
    })),
});

// The IAM role which will be attached to the RDS instance
const dbInstanceRole = new aws.iam.Role("dbInstanceRole", {
    assumeRolePolicy: JSON.stringify({
        Version: "2012-10-17",
        Statement: [{
            Action: "sts:AssumeRole",
            Effect: "Allow",
            Principal: {
                Service: "rds.amazonaws.com"
            }
        }],
    }),
});

// Attach the policy to the role
const rolePolicyAttachment = new aws.iam.RolePolicyAttachment("rolePolicyAttachment", {
    role: dbInstanceRole.name,
    policyArn: dbBucketAccessPolicy.arn
});

// Although Pulumi does not provide a direct way to create database backups to S3, you could manage backup automation
// with a cron job on an EC2 instance or with a Lambda function that uses AWS SDKs to interact with RDS and S3.

export const bucketName = bucket.id;
export const dbInstanceAddress = dbInstance.address;
export const dbInstanceUsername = dbInstance.username;
```

This program sets up the S3 bucket and PostgreSQL RDS instance. The database instance has been given a role, equipped with a policy that allows it to interact with the S3 bucket. You can run scheduled backups using a separate mechanism, which can be an EC2 instance with a cron job or a Lambda function that invokes backup procedures to this S3 bucket.

Remember that the database information sensitive, thus it's strongly recommended to use Pulumi's secret management for sensitive data such as the database password. This could include any AWS access keys needed for the Lambda function or EC2 instance that performs backups.

To further secure your S3 bucket, you can enable server-side encryption and versioning to protect your backups.

In terms of backing up the data to S3, you have options depending on your preferences:
- Use a scheduled AWS Lambda function that runs pg_dump to export the PostgreSQL database and stores it to the S3 bucket.
- Use an EC2 instance that runs a cron job to perform the backup regularly using pg_dump and aws-cli to store the output to the S3 bucket.
- Configure AWS Data Pipeline to automate the transfer of data (note: working with Data Pipeline is out of Pulumi's scope).

Make sure to consider the backup strategy (e.g., frequency, retention, etc.) that meets your application's requirements.