1. Answers
  2. Set up monitoring and logging for PostgreSQL on AWS using CloudWatch?

How Can I Set Up Monitoring and Logging for PostgreSQL on AWS Using CloudWatch?

Introduction

Monitoring and logging are crucial for maintaining the health and performance of any database system. When using PostgreSQL on AWS, integrating with CloudWatch provides a robust solution for tracking performance metrics and managing logs. This guide will walk you through setting up monitoring and logging for a PostgreSQL instance on AWS using CloudWatch. By following these steps, you’ll be able to gain valuable insights into your database’s performance and troubleshoot issues effectively.

Step-by-Step Configuration

To set up monitoring and logging for PostgreSQL on AWS using CloudWatch, you’ll create an RDS PostgreSQL instance and enable enhanced monitoring and logging to CloudWatch. Enhanced monitoring provides a comprehensive view of your RDS instances, while CloudWatch logs will store and manage your PostgreSQL logs.

Here’s the step-by-step configuration:

  1. VPC Configuration: This includes creating a VPC, subnets, an Internet Gateway, and associated route tables.
  2. Security Group: Create an RDS Security Group to allow the necessary traffic.
  3. RDS Instance: Create an RDS PostgreSQL instance with enhanced monitoring enabled.
  4. CloudWatch Logs: Set up CloudWatch logs to capture PostgreSQL logs.

Let’s dive into the complete program:

import * as pulumi from "@pulumi/pulumi";
import * as aws from "@pulumi/aws";
import * as std from "@pulumi/std";

// VPC Configuration
const main = new aws.ec2.Vpc("main", {cidrBlock: "10.0.0.0/16"});
const mainSubnet: aws.ec2.Subnet[] = [];
for (const range = {value: 0}; range.value < 2; range.value++) {
    mainSubnet.push(new aws.ec2.Subnet(`main-${range.value}`, {
        vpcId: main.id,
        cidrBlock: std.cidrsubnetOutput({
            input: main.cidrBlock,
            newbits: 4,
            netnum: range.value,
        }).apply(invoke => invoke.result),
    }));
}
const mainInternetGateway = new aws.ec2.InternetGateway("main", {vpcId: main.id});
const mainRouteTable = new aws.ec2.RouteTable("main", {
    vpcId: main.id,
    routes: [{
        cidrBlock: "0.0.0.0/0",
        gatewayId: mainInternetGateway.id,
    }],
});
const mainRouteTableAssociation: aws.ec2.RouteTableAssociation[] = [];
for (const range = {value: 0}; range.value < 2; range.value++) {
    mainRouteTableAssociation.push(new aws.ec2.RouteTableAssociation(`main-${range.value}`, {
        subnetId: mainSubnet.map(__item => __item.id)[range.value],
        routeTableId: mainRouteTable.id,
    }));
}
// Security Group for RDS
const rdsSg = new aws.ec2.SecurityGroup("rds_sg", {
    vpcId: main.id,
    ingress: [{
        fromPort: 5432,
        toPort: 5432,
        protocol: "tcp",
        cidrBlocks: ["0.0.0.0/0"],
    }],
    egress: [{
        fromPort: 0,
        toPort: 0,
        protocol: "-1",
        cidrBlocks: ["0.0.0.0/0"],
    }],
});
// CloudWatch Log Group for RDS
const rdsLogs = new aws.cloudwatch.LogGroup("rds_logs", {
    name: "rds_postgresql_logs",
    retentionInDays: 7,
});
// IAM Role and Policy for RDS Enhanced Monitoring
const rdsMonitoringRole = new aws.iam.Role("rds_monitoring_role", {
    name: "rds_monitoring_role",
    assumeRolePolicy: JSON.stringify({
        Version: "2012-10-17",
        Statement: [{
            Action: "sts:AssumeRole",
            Effect: "Allow",
            Principal: {
                Service: "monitoring.rds.amazonaws.com",
            },
        }],
    }),
});
const rdsMonitoringRolePolicy = new aws.iam.RolePolicyAttachment("rds_monitoring_role_policy", {
    role: rdsMonitoringRole.name,
    policyArn: "arn:aws:iam::aws:policy/service-role/AmazonRDSEnhancedMonitoringRole",
});
// Subnet Group for RDS
const mainSubnetGroup = new aws.rds.SubnetGroup("main", {
    name: "rds_subnet_group",
    subnetIds: mainSubnet.map(__item => __item.id),
});
// RDS Instance
const postgres = new aws.rds.Instance("postgres", {
    identifier: "my-postgresql-db",
    engine: "postgres",
    instanceClass: aws.rds.InstanceType.T2_Micro,
    allocatedStorage: 20,
    name: "mydb",
    username: "masteruser",
    password: "masterpassword",
    dbSubnetGroupName: mainSubnetGroup.name,
    vpcSecurityGroupIds: [rdsSg.id],
    monitoringInterval: 60,
    monitoringRoleArn: rdsMonitoringRole.arn,
    enabledCloudwatchLogsExports: ["postgresql"],
});
export const dbInstanceEndpoint = postgres.endpoint;
export const cloudwatchLogGroupName = rdsLogs.name;

Key Points

  • VPC & Subnets: A VPC with subnets is created to host the RDS instance.
  • Security Group: The security group allows traffic on port 5432, which is the default port for PostgreSQL.
  • CloudWatch Log Group: A log group in CloudWatch to store PostgreSQL logs.
  • IAM Role: An IAM role with AmazonRDS Monitoring policy attached for enhanced monitoring.
  • RDS Instance: The PostgreSQL instance with enhanced monitoring and CloudWatch logs enabled.

Conclusion

This setup provides a comprehensive monitoring and logging solution for PostgreSQL on AWS. By configuring a VPC, security groups, CloudWatch log groups, and an IAM role for enhanced monitoring, you ensure that your database’s performance and logs are effectively managed. This configuration not only helps in real-time monitoring but also aids in troubleshooting and maintaining optimal database performance.

Deploy this code

Want to deploy this code? Sign up for a free Pulumi account to deploy in a few clicks.

Sign up

New to Pulumi?

Want to deploy this code? Sign up with Pulumi to deploy in a few clicks.

Sign up