Complete PostgreSQL example

A complete RDS PostgreSQL 17 example with automated backup replication, performance insights, and managed password rotation.

This example provisions a production-ready PostgreSQL 17 RDS instance including VPC, security groups, parameter group, CloudWatch log groups, enhanced monitoring, and cross-region automated backup replication. A second db_default instance using AWS-managed groups is also included.

Option groups are not supported for PostgreSQL. The module automatically skips option group creation when the engine is postgres. Any value set for option_group_name will be ignored.

Enable performance_insights_enabled = true for PostgreSQL instances in production. Performance Insights provides query-level diagnostics that are especially valuable for identifying slow queries, lock contention, and wait event bottlenecks. The 7-day retention tier is free.

Configuration

main.tf

provider "aws" {
  region = local.region
}

data "aws_caller_identity" "current" {}
data "aws_availability_zones" "available" {}

locals {
  name    = "complete-postgresql"
  region  = "eu-west-1"
  region2 = "eu-central-1"

  vpc_cidr = "10.0.0.0/16"
  azs      = slice(data.aws_availability_zones.available.names, 0, 3)

  tags = {
    Name       = local.name
    Example    = local.name
    Repository = "https://github.com/terraform-aws-modules/terraform-aws-rds"
  }
}

################################################################################
# RDS Module
################################################################################

module "db" {
  source = "terraform-aws-modules/rds/aws"

  identifier = local.name

  # All available versions: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts
  engine                   = "postgres"
  engine_version           = "17"
  engine_lifecycle_support = "open-source-rds-extended-support-disabled"
  family                   = "postgres17" # DB parameter group
  major_engine_version     = "17"         # DB option group
  instance_class           = "db.t4g.large"

  allocated_storage     = 20
  max_allocated_storage = 100

  # NOTE: Do NOT use 'user' as the value for 'username' as it throws:
  # "Error creating DB Instance: InvalidParameterValue: MasterUsername
  # user cannot be used as it is a reserved word used by the engine"
  db_name  = "completePostgresql"
  username = "complete_postgresql"
  port     = 5432

  # Setting manage_master_user_password_rotation to false after it
  # has previously been set to true disables automatic rotation
  # however using an initial value of false (default) does not disable
  # automatic rotation and rotation will be handled by RDS.
  # manage_master_user_password_rotation allows users to configure
  # a non-default schedule and is not meant to disable rotation
  # when initially creating / enabling the password management feature
  manage_master_user_password_rotation              = true
  master_user_password_rotate_immediately           = false
  master_user_password_rotation_schedule_expression = "rate(15 days)"

  multi_az               = true
  db_subnet_group_name   = module.vpc.database_subnet_group
  vpc_security_group_ids = [module.security_group.security_group_id]

  maintenance_window              = "Mon:00:00-Mon:03:00"
  backup_window                   = "03:00-06:00"
  enabled_cloudwatch_logs_exports = ["postgresql", "upgrade"]
  create_cloudwatch_log_group     = true

  backup_retention_period = 1
  skip_final_snapshot     = true
  deletion_protection     = false

  performance_insights_enabled          = true
  performance_insights_retention_period = 7
  create_monitoring_role                = true
  monitoring_interval                   = 60
  monitoring_role_name                  = "example-monitoring-role-name"
  monitoring_role_use_name_prefix       = true
  monitoring_role_description           = "Description for monitoring role"

  parameters = [
    {
      name  = "autovacuum"
      value = 1
    },
    {
      name  = "client_encoding"
      value = "utf8"
    }
  ]

  tags = local.tags
  db_option_group_tags = {
    "Sensitive" = "low"
  }
  db_parameter_group_tags = {
    "Sensitive" = "low"
  }
  cloudwatch_log_group_tags = {
    "Sensitive" = "high"
  }
}

module "db_default" {
  source = "terraform-aws-modules/rds/aws"

  identifier                     = "${local.name}-default"
  instance_use_identifier_prefix = true

  create_db_option_group    = false
  create_db_parameter_group = false

  # All available versions: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts
  engine               = "postgres"
  engine_version       = "17"
  family               = "postgres17" # DB parameter group
  major_engine_version = "17"         # DB option group
  instance_class       = "db.t4g.large"

  allocated_storage = 20

  # NOTE: Do NOT use 'user' as the value for 'username' as it throws:
  # "Error creating DB Instance: InvalidParameterValue: MasterUsername
  # user cannot be used as it is a reserved word used by the engine"
  db_name  = "completePostgresql"
  username = "complete_postgresql"
  port     = 5432

  db_subnet_group_name   = module.vpc.database_subnet_group
  vpc_security_group_ids = [module.security_group.security_group_id]

  maintenance_window      = "Mon:00:00-Mon:03:00"
  backup_window           = "03:00-06:00"
  backup_retention_period = 0

  tags = local.tags
}

module "db_disabled" {
  source = "terraform-aws-modules/rds/aws"

  identifier = "${local.name}-disabled"

  create_db_instance        = false
  create_db_parameter_group = false
  create_db_option_group    = false
}

################################################################################
# RDS Automated Backups Replication Module
################################################################################

provider "aws" {
  alias  = "region2"
  region = local.region2
}

module "kms" {
  source      = "terraform-aws-modules/kms/aws"
  version     = "~> 1.0"
  description = "KMS key for cross region automated backups replication"

  # Aliases
  aliases                 = [local.name]
  aliases_use_name_prefix = true

  key_owners = [data.aws_caller_identity.current.arn]

  tags = local.tags

  providers = {
    aws = aws.region2
  }
}

module "db_automated_backups_replication" {
  source = "terraform-aws-modules/rds/aws//modules/db_instance_automated_backups_replication"

  source_db_instance_arn = module.db.db_instance_arn
  kms_key_arn            = module.kms.key_arn

  providers = {
    aws = aws.region2
  }
}

################################################################################
# Supporting Resources
################################################################################

module "vpc" {
  source  = "terraform-aws-modules/vpc/aws"
  version = "~> 6.0"

  name = local.name
  cidr = local.vpc_cidr

  azs              = local.azs
  public_subnets   = [for k, v in local.azs : cidrsubnet(local.vpc_cidr, 8, k)]
  private_subnets  = [for k, v in local.azs : cidrsubnet(local.vpc_cidr, 8, k + 3)]
  database_subnets = [for k, v in local.azs : cidrsubnet(local.vpc_cidr, 8, k + 6)]

  create_database_subnet_group = true

  tags = local.tags
}

module "security_group" {
  source  = "terraform-aws-modules/security-group/aws"
  version = "~> 5.0"

  name        = local.name
  description = "Complete PostgreSQL example security group"
  vpc_id      = module.vpc.vpc_id

  # ingress
  ingress_with_cidr_blocks = [
    {
      from_port   = 5432
      to_port     = 5432
      protocol    = "tcp"
      description = "PostgreSQL access from within VPC"
      cidr_blocks = module.vpc.vpc_cidr_block
    },
  ]

  tags = local.tags
}

PostgreSQL-specific configuration

No option groups

PostgreSQL on RDS does not support option groups. The module skips option group creation entirely when engine = "postgres", regardless of what is set for create_db_option_group or option_group_name.

Log exports

PostgreSQL supports two CloudWatch log export types:

Log type	Description
postgresql	PostgreSQL server log (errors, connections, slow queries depending on log_min_duration_statement)
upgrade	Engine version upgrade events

These differ from MySQL which uses general, slowquery, audit, and error log types.

Parameter group configuration

The example sets two parameters:

• autovacuum = 1 — ensures autovacuum is enabled (it is on by default, but making it explicit prevents accidental disabling)
• client_encoding = utf8 — sets the client-side character encoding

Reserved usernames

Do not use user as the value for username. PostgreSQL reserves this word and RDS will return an InvalidParameterValue error. Other reserved words include rdsadmin, rds_superuser, and postgres (for some versions).

Managed password rotation

The example configures Secrets Manager to rotate the master user password on a 15-day schedule using manage_master_user_password_rotation = true with master_user_password_rotation_schedule_expression = "rate(15 days)". Setting master_user_password_rotate_immediately = false prevents an immediate rotation on first apply.

Automated backup replication

The example uses the db_instance_automated_backups_replication submodule to replicate automated backups to eu-central-1. A KMS key is created in the target region to encrypt the replicated backups.

Engine lifecycle support

engine_lifecycle_support = "open-source-rds-extended-support-disabled" opts out of RDS Extended Support (which incurs additional charges for end-of-life engine versions). This is appropriate for PostgreSQL 17, which is current. Remove this setting or use "open-source-rds-extended-support" if you need extended support for older engine versions.

Outputs

Output	Description
db_instance_address	DNS hostname of the RDS instance
db_instance_endpoint	Full connection endpoint including port
db_instance_identifier	The RDS instance identifier
db_instance_engine_version_actual	The resolved engine version running
db_instance_port	Database port (5432)
db_instance_name	The database name
db_instance_username	Master username (sensitive)
db_instance_master_user_secret_arn	ARN of the Secrets Manager secret
db_instance_secretsmanager_secret_rotation_enabled	Whether automatic rotation is enabled
db_parameter_group_id	The parameter group name
db_enhanced_monitoring_iam_role_arn	ARN of the enhanced monitoring IAM role
db_instance_cloudwatch_log_groups	Map of CloudWatch log group names and ARNs