In this module, we will configure Aurora PostrgreSQL with one node (reader and writer). Furthermore, we will configure PostgreSQL pg_logical extension to replicate to another PostgreSQL RDS. So let's get started.

Firstly, we will start with the creation of the Aurora PostgreSQL, which will acts as a Publisher / Provider

resource "aws_rds_cluster_parameter_group" "pg14logical" {
  name        = "aurorapgsql14logicalcluster"
  family      = "aurora-postgresql14"
  description = "RDS cluster parameter group for logical replication"

  parameter {
    name  = "max_replication_slots"
    value = "10"
    apply_method = "pending-reboot"
  }

  parameter {
    name  = "max_wal_senders"
    value = "15"
    apply_method = "pending-reboot"
  }
  parameter {
    name  = "max_worker_processes"
    value = "10"
    apply_method = "pending-reboot"
  }
  parameter {
    name  = "rds.logical_replication"
    value = "1"
    apply_method = "pending-reboot"

  }
  parameter {
    name  = "shared_preload_libraries"
    value = "pg_stat_statements,pglogical"
    apply_method = "pending-reboot"
  }
}

locals {
  destination_endpoint = "${trim(module.cluster.cluster_endpoint,":5432")}"
  rds_cidr_blocks = [for s in data.aws_subnet.rds_subnet_array : s.cidr_block]
  all_cidr_blocks = concat(tolist([data.aws_subnet.ec2_subnet.cidr_block]), local.rds_cidr_blocks)
}

data "aws_subnets" "rds_subnets" {
  filter {
    name   = "tag:Name"
    values = ["${var.environment}-main-db*"]
  }
}

data "aws_subnet" "rds_subnet_array" {
  for_each = toset(data.aws_subnets.rds_subnets.ids)
  id       = each.value
}

data "aws_vpc" "main_vpc" {
  filter {
    name   = "tag:Name"
    values = ["${var.environment}-main"]
  }
}

resource "aws_key_pair" "deployer" {
  key_name   = "deployer-key"
  public_key = var.public_key
}

data "aws_subnet" "ec2_subnet" {
  id = data.aws_subnets.private_subnets.ids[0]
}

data "aws_subnets" "private_subnets" {
  filter {
    name   = "tag:Name"
    values = ["*main-private*"]
  }
}

resource "aws_security_group" "pgsql_allow" {
  name        = "allow_postgresql_ingress"
  description = "Allow PostgreSQL & SSH access"
  vpc_id      = data.aws_vpc.main_vpc.id

  tags = {
    Name = "allow_ssh_postgresql"
  }
}

data "aws_security_group" "selected_sg" {
  vpc_id = data.aws_vpc.main_vpc.id

  filter {
    name   = "tag:Name"
    values = ["allow_ssh_postgresql"]

  }
  depends_on = [aws_security_group.pgsql_allow]
}

data "aws_security_group" "default" {
  vpc_id = data.aws_vpc.main_vpc.id
  name = "default"
}

resource "aws_security_group_rule" "ingress_rules" {
  count             = length(var.rules)
  type              = "ingress"
  from_port         = var.rules[count.index].from_port
  to_port           = var.rules[count.index].to_port
  protocol          = var.rules[count.index].protocol
  cidr_blocks       = local.all_cidr_blocks
  description       = var.rules[count.index].description
  security_group_id = data.aws_security_group.selected_sg.id
}

resource "aws_security_group_rule" "egress_rules" {
  count             = length(var.rules)
  type              = "egress"
  from_port         = var.rules[count.index].from_port
  to_port           = var.rules[count.index].to_port
  protocol          = var.rules[count.index].protocol
  cidr_blocks       = local.all_cidr_blocks
  description       = var.rules[count.index].description
  security_group_id = data.aws_security_group.selected_sg.id
}

resource "local_file" "executable_file" {
  file_permission = "644"
  content = templatefile("${path.cwd}/source.tftpl", {
    source_endpoing         = "${module.cluster.cluster_endpoint}"
    database_name           = "${var.database_name}"
    database_admin_username = "${var.database_admin_username}"
    database_admin_password = "${var.database_admin_password}"
  })
  filename = "source.sql"
}

resource "aws_instance" "ec2_instance" {
  ami           = var.ami
  subnet_id     = data.aws_subnets.private_subnets.ids[0]
  instance_type = "t3.micro"
  key_name      = aws_key_pair.deployer.key_name
  vpc_security_group_ids = [data.aws_security_group.selected_sg.id, data.aws_security_group.default.id]

  tags = {
    Name        = "psqlclient"
    Environment = "Backup"
    Terraform   = "true"
  }
}

resource "null_resource" "database_configuration"{
  
    triggers = {
    always_run = timestamp()
    }
    provisioner "file" {
    source      = "${var.source_path}"
    destination = "/tmp/source.sql"

    connection {
      type     = "ssh"
      user     = "ec2-user"
      private_key = "${file(var.private_key_path)}"
      host     = aws_instance.ec2_instance.private_ip
    }
  }

    provisioner "remote-exec" {
    inline = ["sudo yum -y update",
    "sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm",
    "sudo yum install -y postgresql14",
	  "sudo yum install -y telnet",
	  "sudo yum install -y unzip",
    "PGPASSWORD=\"${var.database_admin_password}\" psql -U${var.database_admin_username} -h ${local.destination_endpoint} -d ${var.database_name}  -f /tmp/source.sql"]

    connection {
      type     = "ssh"
      user     = "ec2-user"
      private_key = "${file(var.private_key_path)}"
      host     = aws_instance.ec2_instance.private_ip
    }
  }
  depends_on = [module.cluster]
}

module "cluster" {
  source                      = "terraform-aws-modules/rds-aurora/aws"
  name                        = "test-aurora-db-postgres145"
  engine                      = "aurora-postgresql"
  engine_version              = "14.5"
  manage_master_user_password = false
  master_username             = "${var.database_admin_username}"
  master_password             = "${var.database_admin_password}"
  instances = {
    one = {
      instance_class = "db.r5.large"
    }
  }

  vpc_id                 = data.aws_vpc.main_vpc.id
  db_cluster_parameter_group_name = aws_rds_cluster_parameter_group.pg14logical.name
  vpc_security_group_ids = [data.aws_security_group.selected_sg.id]
  db_subnet_group_name   = "${var.environment}-db-main"
  skip_final_snapshot    = true

  tags = {
    Environment = "dev"
    Terraform   = "true"
  }
}

data "aws_vpc" "main_vpc" {
  filter {
    name   = "tag:Name"
    values = ["${var.environment}-main"]
  }
}

resource "aws_key_pair" "deployer" {
  key_name   = "deployer-key"
  public_key = "${var.public_key}"
}

data "aws_subnet" "ec2_subnet" {
	id = data.aws_subnets.private_subnets.ids[0]
}

data "aws_subnets" "private_subnets" {
	filter {
		name = "tag:Name"
		values = ["*main-private*"]
	}
}

resource "aws_security_group" "pgsql_allow" {
  name        = "allow_postgresql_ingress"
  description = "Allow PostgreSQL & SSH access"
  vpc_id      =  data.aws_vpc.main_vpc.id
  
  tags = {
    Name = "allow_ssh_postgresql"
  }
}

data "aws_security_group" "selected_sg" {
	vpc_id      =  data.aws_vpc.main_vpc.id
	
	filter {
		name = "tag:Name"
		values = ["allow_ssh_postgresql"]
	
	}
	depends_on = [aws_security_group.pgsql_allow]
}

resource "aws_security_group_rule" "ingress_rules" {
  count = length(var.ingress_rules)
  type              = "ingress"
  from_port         = var.ingress_rules[count.index].from_port
  to_port           = var.ingress_rules[count.index].to_port
  protocol          = var.ingress_rules[count.index].protocol
  cidr_blocks       = [data.aws_subnet.ec2_subnet.cidr_block]
  description       = var.ingress_rules[count.index].description
  security_group_id = data.aws_security_group.selected_sg.id
}

resource "aws_instance" "ec2_instance" {
    ami = var.ami
	count = 1
    subnet_id = data.aws_subnets.private_subnets.ids[0]
    instance_type = "t3.micro"
    key_name = aws_key_pair.deployer.key_name
	
	user_data=<<-EOF
	#!/bin/bash
	yum -y update
	yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
	yum install -y postgresql14
	yum install -y barman
	yum install -y barman-cli
	yum install -y telnet
	yum install -y unzip
	curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
	unzip awscliv2.zip
	sudo ./aws/install
	EOF
	
	tags = {
		Name = "Barman"
		Environment = "Backup"
		Terraform = "true"
	}
}

module "cluster" {
  source  = "terraform-aws-modules/rds-aurora/aws"
  name           = "test-aurora-db-postgres145"
  engine         = "aurora-postgresql"
  engine_version = "14.5"
  manage_master_user_password = false
  master_username= "admin1"
  master_password= "Test1234#!"
  instances = {
    one = {
	  instance_class = "db.r5.large"
	}
    two = {
      instance_class = "db.r5.large"
	}
  }
  
  vpc_id               	 = data.aws_vpc.main_vpc.id
  vpc_security_group_ids = [data.aws_security_group.selected_sg.id]
  db_subnet_group_name   = "${var.environment}-db-main"
  skip_final_snapshot  	 = true

  tags = {
    Environment = "dev"
    Terraform   = "true"
  }
}

This code will create Aurora PostgreSQL with pg_logical extension enabled and furthermore, it will execute the SQL template below:

/* Setup */
\c ${database_name}
create extension pglogical;
select pglogical.drop_node('provider', true);

/* Create node */
select pglogical.create_node(node_name := 'provider', dsn := 'host=${source_endpoing} port=5432 user=${database_admin_username} dbname=${database_name} password=${database_admin_password}');

/* DR Setup */
select pglogical.drop_replication_set('b2b_subscription',true);
select pglogical.create_replication_set(set_name := 'b2b_subscription');
select pglogical.replication_set_add_all_tables(set_name  := 'dr', schema_names := ARRAY['a2a_transfers'], synchronize_data := true);
select pglogical.replication_set_add_all_tables(set_name  := 'dr', schema_names := ARRAY['account_service'], synchronize_data := true);


/* BI Setup */
select pglogical.drop_replication_set(set_name := 'bi_subscription');
select pglogical.create_replication_set(set_name := 'bi_subscription');
select pglogical.replication_set_add_table(set_name := 'bi_subscription', relation := 'bi.employees', synchronize_data := true, columns := '{id,salary}');
select pglogical.replication_set_add_table('bi_subscription', 'bi.departments');
select pglogical.replication_set_add_table('bi_subscription', 'bi.sales');


select pglogical.replicate_ddl_command('create schema bi;', '{ddl_sql}');
select pglogical.replicate_ddl_command('create table bi.employees(id int primary key, name text, salary int);', '{ddl_sql}');
select pglogical.replicate_ddl_command('create table bi.departments(id int primary key, name text);', '{ddl_sql}');

These are OF COURSE, test settings, BUT they will setup a pg_logical extension and start the node as publisher/provider Then, let's configure the Subscriber

The following code, will configure RDS PostgreSQL and it will create some Demo structure for the pg_logical:

resource "aws_db_parameter_group" "rds_pg14logical" {
  name        = "pgsql14logicalrds-destination"
  family      = "postgres14"
  description = "RDS parameter group for logical replication"

  parameter {
    name  = "max_replication_slots"
    value = "10"
    apply_method = "pending-reboot"
  }
  parameter {
    name  = "max_wal_senders"
    value = "15"
    apply_method = "pending-reboot"
  }
  parameter {
    name  = "max_worker_processes"
    value = "10"
    apply_method = "pending-reboot"
  }
  parameter {
    name  = "rds.logical_replication"
    value = "1"
    apply_method = "pending-reboot"
  }
  parameter {
    name  = "shared_preload_libraries"
    value = "pg_stat_statements,pglogical"
    apply_method = "pending-reboot"
  }

  lifecycle {
    create_before_destroy = true
  }
}


locals {
  destination_endpoint = "${trim(module.db.db_instance_endpoint,":5432")}"
  rds_cidr_blocks = [for s in data.aws_subnet.rds_subnet_array : s.cidr_block]
  all_cidr_blocks = concat(tolist([data.aws_subnet.ec2_subnet.cidr_block]), local.rds_cidr_blocks)
}


data "aws_vpc" "main_vpc" {
  filter {
    name   = "tag:Name"
    values = ["${var.environment}-main"]
  }
}

resource "aws_key_pair" "deployer" {
  key_name   = "deployer-key-destination"
  public_key = var.public_key
}

data "aws_subnets" "private_subnets" {
  filter {
    name   = "tag:Name"
    values = ["*main-private*"]
  }
}

data "aws_subnet" "ec2_subnet" {
  id = data.aws_subnets.private_subnets.ids[0]
}

data "aws_subnets" "rds_subnets" {
  filter {
    name   = "tag:Name"
    values = ["${var.environment}-main-db*"]
  }
}

data "aws_subnet" "rds_subnet_array" {
  for_each = toset(data.aws_subnets.rds_subnets.ids)
  id       = each.value
}

resource "aws_security_group" "pgsql_allow" {
  name        = "allow_postgresql_ingress_destination"
  description = "Allow PostgreSQL & SSH access"
  vpc_id      = data.aws_vpc.main_vpc.id

  tags = {
    Name = "allow_ssh_postgresql_destination"
  }
}

data "aws_security_group" "selected_allow_ssh_postgresql" {
  vpc_id = data.aws_vpc.main_vpc.id

  filter {
    name   = "tag:Name"
    values = ["allow_ssh_postgresql_destination"]

  }
  depends_on = [aws_security_group.pgsql_allow]
}

data "aws_security_group" "default" {
  vpc_id = data.aws_vpc.main_vpc.id
  name = "default"
}

resource "aws_security_group_rule" "ingress_rules" {
  count             = length(var.rules)
  type              = "ingress"
  from_port         = var.rules[count.index].from_port
  to_port           = var.rules[count.index].to_port
  protocol          = var.rules[count.index].protocol
  cidr_blocks       = local.all_cidr_blocks
  description       = var.rules[count.index].description
  security_group_id = data.aws_security_group.selected_allow_ssh_postgresql.id
}

resource "aws_security_group_rule" "egress_rules" {
  count             = length(var.rules)
  type              = "egress"
  from_port         = var.rules[count.index].from_port
  to_port           = var.rules[count.index].to_port
  protocol          = var.rules[count.index].protocol
  cidr_blocks       = local.all_cidr_blocks
  description       = var.rules[count.index].description
  security_group_id = data.aws_security_group.selected_allow_ssh_postgresql.id
}

resource "local_file" "executable_file" {
  file_permission = "644"
  content = templatefile("${path.cwd}/destination.tftpl", {
    destination_endpoint    = "${local.destination_endpoint}"
    database_name           = "${var.database_name}"
    database_admin_username = "${var.database_admin_username}"
    database_admin_password = "${var.database_admin_password}"
    source_endpoint         = "${var.source_endpoint}"
  })
  filename = "destination.sql"
}

resource "aws_instance" "ec2_instance" {
  ami           = var.ami
  subnet_id     = data.aws_subnets.private_subnets.ids[0]
  instance_type = "t3.micro"
  key_name      = aws_key_pair.deployer.key_name
  vpc_security_group_ids = [data.aws_security_group.selected_allow_ssh_postgresql.id, data.aws_security_group.default.id]

  tags = {
    Name        = "ec2instance-dest"
    Environment = "pglogical"
    Terraform   = "true"
  }
}

resource "null_resource" "database_configuration"{

    triggers = {
    always_run = timestamp()
    }
  
    provisioner "file" {
    source      = "${var.destination_sql_path}"
    destination = "/tmp/destination.sql"

    connection {
      type     = "ssh"
      user     = "ec2-user"
      private_key = "${file(var.private_key_path)}"
      host     = aws_instance.ec2_instance.private_ip
    }
  }

    provisioner "remote-exec" {
    inline = ["sudo yum -y update",
    "sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm",
    "sudo yum install -y postgresql14",
	  "sudo yum install -y telnet",
	  "sudo yum install -y unzip",
    "PGPASSWORD=\"${var.database_admin_password}\" psql -U${var.database_admin_username} -h ${local.destination_endpoint} -d postgres  -f /tmp/destination.sql"]

    connection {
      type     = "ssh"
      user     = "ec2-user"
      private_key = "${file(var.private_key_path)}"
      host     = aws_instance.ec2_instance.private_ip
    }
  }
  depends_on = [module.db]
}

module "db" {
  source = "terraform-aws-modules/rds/aws"
  engine                      = "postgres"
  engine_version              = "14.5"
  instance_class              = "db.m5.large"
  allocated_storage           = 5
  manage_master_user_password = false
  create_db_parameter_group   = false
  identifier                  = "postgres145destination"
  username                    = "${var.database_admin_username}"
  password                    = "${var.database_admin_password}"
  port                        = "5432"
  db_subnet_group_name        = "${var.environment}-db-main"
  vpc_security_group_ids      = [data.aws_security_group.selected_allow_ssh_postgresql.id]
  parameter_group_name        = aws_db_parameter_group.rds_pg14logical.name
  major_engine_version        = "14.5"
  
  tags = {
    Environment = "dev"
    Terraform   = "true"
  }
}

As before, the code will create RDS PostgreSQL with all the options for pg_logical. It will also execute the SQL template below:

/* Clean up & Setup */
drop database ${database_name} with (FORCE);
create database ${database_name};
\c ${database_name}
create extension pglogical;
select pglogical.drop_node('subscriber', true);


/* Create node */
select pglogical.create_node(node_name := 'subscriber', dsn := 'host=${destination_endpoint} port=5432 user=${database_admin_username} dbname=${database_name} password=${database_admin_password}');

/* DR Setup */
select pglogical.drop_subscription('ddl_sql',true);
select pglogical.create_subscription(subscription_name := 'ddl_sql', provider_dsn := 'host=${source_endpoint} port=5432 dbname=${database_name} user=${database_admin_username} password=${database_admin_password}', replication_sets := array['ddl_sql'], synchronize_structure := true, synchronize_data := false);
select pglogical.create_subscription(subscription_name := 'b2b_subscription', provider_dsn := 'host=${source_endpoint} port=5432 dbname=${database_name} user=${database_admin_username} password=${database_admin_password}', replication_sets := array['b2b_subscription'], synchronize_structure := false, synchronize_data := true);

/* BI Setup */
select pglogical.drop_subscription('bi_subscription',true);
select pglogical.create_subscription(subscription_name := 'bi_subscription', provider_dsn := 'host=${source_endpoint} port=5432 dbname=${database_name} user=${database_admin_username} password=${database_admin_password}', replication_sets := array['bi_subscription'], synchronize_structure := false, synchronize_data := true);

Of course, that excludes all the meta configuration which you need to do for Terraform like: “provider.tf”, “terraform.tfvars” and “variables.tf”

That being said, as we need the security rules (ports, etc), here is extract from vars:

variable "region" {
  description = "Defines AWS region"
  type        = string
  default     = "eu-central-1"
}

variable "environment" {
  description = "Name of the environment"
  type        = string
}

variable "database_admin_password" {
  description = "The password for the database"
  type        = string

}

variable "database_admin_username"{
  description = "The username for the database"
  type        = string
}

variable "private_key_path" {
  description = "The path of the private key"
  type        = string
}

variable "public_key" {
  description = "Public key for the session"
  type        = string  
}

variable "ami" {
  description = "AMI for the EC2 image"
  type        = string
  default     = "ami-025d24108be0a614c"
}

variable "destination_sql_path" {
  description = "The path for the configuration of the source cluster"
  type        = string
}

variable "database_name" {
  description = "The name of the virtual database we will replicate"
  type        = string
}

variable "source_endpoint" {
  description = "The endpoint of the source database, provided manually for now"
  type        = string
}


variable "rules" {
  type = list(object({
    from_port   = number
    to_port     = number
    protocol    = string
    description = string
  }))
  default = [
    {
      from_port   = 22
      to_port     = 22
      protocol    = "tcp"
      description = "ssh access"
    },
    {
      from_port   = 5432
      to_port     = 5432
      protocol    = "tcp"
      description = "postgresql access"
    },
  ]
}

The “terraform.tfvars” are of course customized.