How to create PostgreSQL RDS read replica with terraform

Hi Team
I am able to create PostgreSQL AWS RDS with terraform but now I want to create read replica of that PostgreSQL AWS RDS. Please help me with terraform option/document to setup this.

$ terraform version
Terraform v0.12.28

  • provider.aws v2.69.0
  • provider.null v2.1.2

This is the main.tf file i am using

> #####################
> # DB parameter group
> # We create this up-front to avoid having to restart to modify parameters later
> #####################
> resource "aws_db_parameter_group" "this" {
>   name        = "${var.environment}-${var.name}"
>   description = "Database parameter group for ${var.environment}-${var.name}"
>   family      =  var.parameter_group
> 
>   parameter {
>     name = "max_connections"
>     value = var.max_connections
>     apply_method =  "pending-reboot"
>   }
> 
>   lifecycle {
>     create_before_destroy = true
>   }
> 
>   tags = {
>     Terraform   = "true"
>     Environment = "${var.environment}"
>     Project     = "${var.name}"
>   }
> }
> 
> ######## AWS RDS INSTANCE #########
> 
> resource "aws_db_instance" "postgresql" {
>   count                   = var.aws_rds_instance_count
>   engine                  = var.engine
>   engine_version          = var.engine_version
>   name                    = "${var.environment}_${var.name}"
>   username                = "master"
>   password                = data.aws_secretsmanager_secret_version.password.secret_string
>   identifier_prefix       = "${var.environment}-${var.name}-"
>   instance_class          = var.instance_class
>   db_subnet_group_name    = var.subnet_group_name
>   parameter_group_name    = aws_db_parameter_group.this.id
>   depends_on              = [null_resource.random-pw]
>   ca_cert_identifier      = "rds-ca-2019"
>   allocated_storage       = var.storage-size
>   storage_encrypted       = "true"
>   kms_key_id              = aws_kms_key.aurora_db.arn
>   deletion_protection     = var.deletion_protection
>   performance_insights_enabled  = var.performance_insights
>   performance_insights_kms_key_id = var.performance_insights_keyid
>   final_snapshot_identifier       = "${var.environment}-${var.name}-final"
>   vpc_security_group_ids          = var.security_groups
>   backup_retention_period         = var.backup_retention_period
> 
>   tags = {
>     Name        = "${var.environment}-${var.name}-db"
>     Project     = "${var.name}"
>     PCR         = "${var.PCR}" 
>   }
> }
> 
> 
> #################
> ## DB PASSWORD ##
> # Genarates a random password and then store it in SSM parameter store
> #################
> 
> data "aws_secretsmanager_secret" "name" {
>   name       = "/${var.environment}/rds/${var.name}/master"
>   depends_on = [null_resource.random-pw]
> }
> 
> data "aws_secretsmanager_secret_version" "password" {
>   secret_id = "${data.aws_secretsmanager_secret.name.id}"
> }
> 
> resource "null_resource" "random-pw" {
> 
>   provisioner "local-exec" {
>     command = "aws secretsmanager create-secret --name \"/${var.environment}/rds/${var.name}/master\" --secret-string `openssl rand -base64 32 | cut -c1-32 | tr '/@' '_'`  --kms-key-id ${aws_kms_key.aurora_db.arn} "
>   }
> 
>   provisioner "local-exec" {
>     command =  "aws secretsmanager delete-secret --secret-id \"/${var.environment}/rds/${var.name}/master\"  --force-delete-without-recovery"
>     when    =  destroy
>   }
> }
> 
> ####### RDS KMS Encryption ########
> 
> data "aws_caller_identity" "current" {}
> 
> resource "aws_kms_key" "aurora_db" {
>   description         = "${var.environment}/rds/${var.environment}-${var.name}"
>   enable_key_rotation = true
> 
>   policy = <<POLICY
> {
>   "Version": "2012-10-17",
>   "Id": "key-consolepolicy-3",
>   "Statement": [
>     {
>       "Sid": "Enable IAM User Permissions",
>       "Effect": "Allow",
>       "Principal": {
>         "AWS": "arn:aws:iam::${data.aws_caller_identity.current.account_id}:root"
>       },
>       "Action": "kms:*",
>       "Resource": "*"
>     },
>     {
>       "Sid": "Allow access for Key Administrators",
>       "Effect": "Allow",
>       "Principal": {
>         "AWS": "${data.aws_caller_identity.current.arn}"
>       },
>       "Action": [
>         "kms:Create*",
>         "kms:Describe*",
>         "kms:Enable*",
>         "kms:List*",
>         "kms:Put*",
>         "kms:Update*",
>         "kms:Revoke*",
>         "kms:Disable*",
>         "kms:Get*",
>         "kms:Delete*",
>         "kms:TagResource",
>         "kms:UntagResource",
>         "kms:ScheduleKeyDeletion",
>         "kms:CancelKeyDeletion"
>       ],
>       "Resource": "*"
>     },
>     {
>       "Sid": "Allow use of the key",
>       "Effect": "Allow",
>       "Principal": {
>         "AWS": "${data.aws_caller_identity.current.arn}"
>       },
>       "Action": [
>         "kms:Encrypt",
>         "kms:Decrypt",
>         "kms:ReEncrypt*",
>         "kms:GenerateDataKey*",
>         "kms:DescribeKey"
>       ],
>       "Resource": "*"
>     },
>     {
>       "Sid": "Allow attachment of persistent resources",
>       "Effect": "Allow",
>       "Principal": {
>         "AWS": "${data.aws_caller_identity.current.arn}"
>       },
>       "Action": [
>         "kms:CreateGrant",
>         "kms:ListGrants",
>         "kms:RevokeGrant"
>       ],
>       "Resource": "*",
>       "Condition": {
>         "Bool": {
>           "kms:GrantIsForAWSResource": "true"
>         }
>       }
>     }
>   ]
> }
> POLICY
> }
> 
> resource "aws_kms_alias" "aurora_db" {
>   name          = "alias/${var.environment}/rds/${var.name}"
>   target_key_id = aws_kms_key.aurora_db.key_id
> }

Thanks,
Bharat Jarwal

1 Like

You create a second RDS DB specifying the replicate_source_db with the ID or ARN of the master DB.

Thanks for the response.

I tried that with adding on more resource aws_db_instance something like below but is creating one more PostgreSQL instance which is not replica of first one.

Can you please let me know what wrong I am doing

I added this block in above provided TF files.

resource “aws_db_instance” “postgresql-read-replica” {
count = var.aws_rds_instance_count
engine = var.engine
engine_version = var.engine_version
name = “{var.environment}_{var.name}”
username = “master”
password = data.aws_secretsmanager_secret_version.password.secret_string
instance_class = var.instance_class
identifier_prefix = “{var.environment}-{var.name}-”
replicate_source_db = “”
db_subnet_group_name = var.subnet_group_name
parameter_group_name = aws_db_parameter_group.this.id
depends_on = [null_resource.random-pw]
ca_cert_identifier = “rds-ca-2019”
allocated_storage = var.storage-size
storage_encrypted = “true”
kms_key_id = aws_kms_key.aurora_db.arn
deletion_protection = var.deletion_protection
performance_insights_enabled = var.performance_insights
performance_insights_kms_key_id = var.performance_insights_keyid
final_snapshot_identifier = “{var.environment}-{var.name}-final”
vpc_security_group_ids = var.security_groups
backup_retention_period = var.backup_retention_period

tags = {
Name = “{var.environment}-{var.name}-db”
Project = “{var.name}" PCR = "{var.PCR}”
}
}

The replica should have the ID of the master on replicate_source_db param.

In the provided code for the replica I see that replicate_source_db is empty:

And I think it should be as:

replicate_source_db = aws_db_instance.postgresql.id

1 Like

It worked. Thank you for all your help.

I also had to made changes in replicas block and it looks like this now. I am posting this because it might help anybody in future

resource “aws_db_instance” “postgresql-read-replica” {
name = “{var.environment}_{var.name}”
instance_class = var.instance_class
identifier_prefix = “{var.environment}-{var.name}-”
replicate_source_db = aws_db_instance.postgresql.id
parameter_group_name = aws_db_parameter_group.this.id
depends_on = [null_resource.random-pw]
ca_cert_identifier = “rds-ca-2019”
storage_encrypted = “true”
kms_key_id = aws_kms_key.aurora_db.arn
deletion_protection = var.deletion_protection
performance_insights_enabled = var.performance_insights
performance_insights_kms_key_id = var.performance_insights_keyid
skip_final_snapshot = true
final_snapshot_identifier = null
vpc_security_group_ids = var.security_groups

tags = {
Name = “{var.environment}-{var.name}-db”
Project = “{var.name}" PCR = "{var.PCR}”
}
}

Both master and replica got created in same AZ now trying to use multi_az in TF script.

multi_az = “true”

Great!!

In order to use a different availability zone for the replica and the master you use availability_zone not multi_az

Multi AZ is for a high availability and DR managed by AWS, not for read replica placement. In Multi AZ, AWS will have a ready to use standby replica, not a read replica.

If using Multi AZ you will need to create a RDS subnet group with at least two availability zones for the AZ. I have an example that uses a subnet group (it is not in AZ)

1 Like

I have created three subsets in AWS to create DB instances.

It seems “Multi AZ” is PostgreSQL RDS option to enable DB for multi AZ
image

I created PostgreSQL RDS without specifying availability_zone in TF script and Master and replica instance got created in different AWS AZ.

I guess, with availability_zone option we can specify which instance will get created in which AWS AZ. I will try this option.

I added availability_zone in both aws_db_instance.postgresql and aws_db_instance.postgresql-read-replica as below

availability_zone = var.availability_zone

And also added in variable as below.

variable “availability_zone” {
description = “The Availability Zone of the RDS instance”
type = string
default = “”
}

It seems I am missing something. If possible can you please tell me how to use availability_zone in TF script?

Ideally you will use a different availability zone value for each instance, for example if you are in N Virginia Region, the availability zones are as follows: us-east-1a; us-east-1b; us-east-1c; us-​east-1d; us-east-1e; us-east-1f.

You could put a replica in each of the zones (data centers in the same Region) but be aware that you will pay for data transfer between zones.

Your RDS Subnet group needs to have all the availability zones that you want to use as members.

See the second diagram about RDS, subnets and MultiAZ at: