terraform_aurora_postgresql

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		 Previous revision
terraform_aurora_postgresql [2023/06/20 15:07] – created andonovjterraform_aurora_postgresql [2023/09/10 15:05] (current) – andonovj
Line 1: Line 1:
 +
 +======Overview======
 +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.
 +
 +
 +====== Configure the publisher / provider ======
 +Firstly, we will start with the creation of the Aurora PostgreSQL, which will acts as a Publisher / Provider
 +
 +<Code:Bash| Aurora PostgreSQL>
 +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"
 +  }
 +}
 +</Code>
 +
 +
 +
 <Code:Bash|To do> <Code:Bash|To do>
  
Line 112: Line 334:
 } }
 </Code> </Code>
 +
 +This code will create Aurora PostgreSQL with pg_logical extension enabled and furthermore, it will execute the SQL template below:
 +
 +<Code:Bash| SQL Template Source>
 +/* 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}');
 +</Code>
 +
 +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
 +
 +
 +====== Configure the Subscriber ======
 +The following code, will configure RDS PostgreSQL and it will create some Demo structure for the pg_logical:
 +
 +<Code:bash|Configure Subscriber>
 +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"
 +  }
 +}
 +</Code>
 +
 +As before, the code will create RDS PostgreSQL with all the options for pg_logical. It will also execute the SQL template below:
 +
 +<Code:Bash|SQL Template Destination>
 +/* 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);
 +</Code>
 +
 +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:
 +
 +
 +<Code:bash|Variables>
 +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"
 +    },
 +  ]
 +}
 +</Code>
 +
 +The "terraform.tfvars" are of course customized.
  • terraform_aurora_postgresql.1687273674.txt.gz
  • Last modified: 2023/06/20 15:07
  • by andonovj