nugoncal/terraform-oci-cloudbricks-postgresql

Cloud Bricks framework: PostgreSQL

OCI Cloud Bricks: PostgreSQL

Introduction

The following brick contains the logic to provision a PostgreSQL database in a highly available architecture. This architecture makes use of up to 3 instance in a master and standby configuration with streaming replication.

This brick is only supported on Oracle Linux for the time being.

Reference Architecture

The following is the reference architecture associated to this brick

Prerequisites

• Pre-baked Artifact and Network Compartments
• Pre-baked VCN

Sample tfvar file

If using hotstandby instances and fixed shapes.

######################################## COMMON VARIABLES ######################################
region           = "re-region-1"
tenancy_ocid     = "ocid1.tenancy.oc1..aaaaaaaabcedfghijklmonoprstuvwxyz"
user_ocid        = "ocid1.tenancy.oc1..aaaaaaaabcedfghijklmonoprstuvwxyz"
fingerprint      = "fo:oo:ba:ar:ba:ar"
private_key_path = "/absolute/path/to/api/key/your_api_key.pem"
######################################## COMMON VARIABLES ######################################
######################################## ARTIFACT SPECIFIC VARIABLES ######################################
ssh_public_key                          = "/absolute/path/to/api/key/your_ssh_public_key.pub"
ssh_private_key                         = "/absolute/path/to/api/key/your_ssh_private_key"
compute_nsg_name                        = "MY_NSG"
linux_compute_instance_compartment_name = "MY_ARTIFACT_COMPARTMENT"
linux_compute_network_compartment_name  = "MY_NETWORK_COMPARTMENT"
private_network_subnet_name             = "MY_PRIVATE_SUBNET"
vcn_display_name                        = "MY_VCN"

postgresql_replicat_username = "replicator"
postgresql_password          = "MY_DATABASE_PASSWORD"
postgresql_version           = "14"
database_size_in_gb          = "50"
database_vpus_per_gb         = "10"
database_backup_policy_level = "gold"
instance_backup_policy_level = "bronze"

postgresql_master_name          = "MY_MASTER_INSTANCE_NAME"
postgresql_master_ad            = "aBCD:RE-REGION-1-AD-1"
postgresql_master_fd            = "FAULT-DOMAIN-1"
postgresql_master_shape         = "VM.Standard2.2"

postgresql_hotstandby_is_flex_shape = false
postgresql_hotstandby_shape         = "VM.Standard2.1"

postgresql_deploy_hotstandby1 = true
postgresql_standyby1_name     = "MY_HOTSTANDBY1_INSTANCE_NAME"
postgresql_hotstandby1_ad     = "aBCD:RE-REGION-1-AD-2"
postgresql_hotstandby1_fd     = "FAULT-DOMAIN-1"

postgresql_deploy_hotstandby2 = true
postgresql_standyby2_name     = "MY_HOTSTANDBY2_INSTANCE_NAME"
postgresql_hotstandby2_ad     = "aBCD:RE-REGION-1-AD-3"
postgresql_hotstandby2_fd     = "FAULT-DOMAIN-1"
######################################## ARTIFACT SPECIFIC VARIABLES ######################################

If using hotstandby instances and flex shapes.

######################################## COMMON VARIABLES ######################################
region           = "re-region-1"
tenancy_ocid     = "ocid1.tenancy.oc1..aaaaaaaabcedfghijklmonoprstuvwxyz"
user_ocid        = "ocid1.tenancy.oc1..aaaaaaaabcedfghijklmonoprstuvwxyz"
fingerprint      = "fo:oo:ba:ar:ba:ar"
private_key_path = "/absolute/path/to/api/key/your_api_key.pem"
######################################## COMMON VARIABLES ######################################
######################################## ARTIFACT SPECIFIC VARIABLES ######################################
ssh_public_key                          = "/absolute/path/to/api/key/your_ssh_public_key.pub"
ssh_private_key                         = "/absolute/path/to/api/key/your_ssh_private_key"
compute_nsg_name                        = "MY_NSG"
linux_compute_instance_compartment_name = "MY_ARTIFACT_COMPARTMENT"
linux_compute_network_compartment_name  = "MY_NETWORK_COMPARTMENT"
private_network_subnet_name             = "MY_PRIVATE_SUBNET"
vcn_display_name                        = "MY_VCN"

postgresql_replicat_username = "replicator"
postgresql_password          = "MY_DATABASE_PASSWORD"
postgresql_version           = "14"
database_size_in_gb          = "50"
database_vpus_per_gb         = "10"
database_backup_policy_level = "gold"
instance_backup_policy_level = "bronze"

postgresql_master_name          = "MY_MASTER_INSTANCE_NAME"
postgresql_master_ad            = "aBCD:RE-REGION-1-AD-1"
postgresql_master_fd            = "FAULT-DOMAIN-1"
postgresql_master_is_flex_shape = true
postgresql_master_shape         = "VM.Standard.E3.Flex"
postgresql_master_ocpus         = "2"
postgresql_master_memory_in_gb  = "32"

postgresql_hotstandby_is_flex_shape = true
postgresql_hotstandby_shape         = "VM.Standard.E3.Flex"
postgresql_hotstandby_ocpus         = "1"
postgresql_hotstandby_memory_in_gb  = "16"

postgresql_deploy_hotstandby1 = true
postgresql_standyby1_name     = "MY_HOTSTANDBY1_INSTANCE_NAME"
postgresql_hotstandby1_ad     = "aBCD:RE-REGION-1-AD-2"
postgresql_hotstandby1_fd     = "FAULT-DOMAIN-1"

postgresql_deploy_hotstandby2 = true
postgresql_standyby2_name     = "MY_HOTSTANDBY2_INSTANCE_NAME"
postgresql_hotstandby2_ad     = "aBCD:RE-REGION-1-AD-3"
postgresql_hotstandby2_fd     = "FAULT-DOMAIN-1"
######################################## ARTIFACT SPECIFIC VARIABLES ######################################

If using just a master instance and fixed shapes.

######################################## COMMON VARIABLES ######################################
region           = "re-region-1"
tenancy_ocid     = "ocid1.tenancy.oc1..aaaaaaaabcedfghijklmonoprstuvwxyz"
user_ocid        = "ocid1.tenancy.oc1..aaaaaaaabcedfghijklmonoprstuvwxyz"
fingerprint      = "fo:oo:ba:ar:ba:ar"
private_key_path = "/absolute/path/to/api/key/your_api_key.pem"
######################################## COMMON VARIABLES ######################################
######################################## ARTIFACT SPECIFIC VARIABLES ######################################
ssh_public_key                          = "/absolute/path/to/api/key/your_ssh_public_key.pub"
ssh_private_key                         = "/absolute/path/to/api/key/your_ssh_private_key"
compute_nsg_name                        = "MY_NSG"
linux_compute_instance_compartment_name = "MY_ARTIFACT_COMPARTMENT"
linux_compute_network_compartment_name  = "MY_NETWORK_COMPARTMENT"
private_network_subnet_name             = "MY_PRIVATE_SUBNET"
vcn_display_name                        = "MY_VCN"

postgresql_password          = "MY_DATABASE_PASSWORD"
postgresql_version           = "14"
database_size_in_gb          = "50"
database_vpus_per_gb         = "10"
database_backup_policy_level = "gold"
instance_backup_policy_level = "bronze"

postgresql_master_name          = "MY_MASTER_INSTANCE_NAME"
postgresql_master_ad            = "aBCD:RE-REGION-1-AD-1"
postgresql_master_fd            = "FAULT-DOMAIN-1"
postgresql_master_shape         = "VM.Standard2.2"
######################################## ARTIFACT SPECIFIC VARIABLES ######################################

If using just a master instance and a flex shape.

######################################## COMMON VARIABLES ######################################
region           = "re-region-1"
tenancy_ocid     = "ocid1.tenancy.oc1..aaaaaaaabcedfghijklmonoprstuvwxyz"
user_ocid        = "ocid1.tenancy.oc1..aaaaaaaabcedfghijklmonoprstuvwxyz"
fingerprint      = "fo:oo:ba:ar:ba:ar"
private_key_path = "/absolute/path/to/api/key/your_api_key.pem"
######################################## COMMON VARIABLES ######################################
######################################## ARTIFACT SPECIFIC VARIABLES ######################################
ssh_public_key                          = "/absolute/path/to/api/key/your_ssh_public_key.pub"
ssh_private_key                         = "/absolute/path/to/api/key/your_ssh_private_key"
compute_nsg_name                        = "MY_NSG"
linux_compute_instance_compartment_name = "MY_ARTIFACT_COMPARTMENT"
linux_compute_network_compartment_name  = "MY_NETWORK_COMPARTMENT"
private_network_subnet_name             = "MY_PRIVATE_SUBNET"
vcn_display_name                        = "MY_VCN"

postgresql_password          = "MY_DATABASE_PASSWORD"
postgresql_version           = "14"
database_size_in_gb          = "50"
database_vpus_per_gb         = "10"
database_backup_policy_level = "gold"
instance_backup_policy_level = "bronze"

postgresql_master_name          = "MY_MASTER_INSTANCE_NAME"
postgresql_master_ad            = "aBCD:RE-REGION-1-AD-1"
postgresql_master_fd            = "FAULT-DOMAIN-1"
postgresql_master_is_flex_shape = true
postgresql_master_shape         = "VM.Standard.E3.Flex"
postgresql_master_ocpus         = "2"
postgresql_master_memory_in_gb  = "32"
######################################## ARTIFACT SPECIFIC VARIABLES ######################################

Variable Specific Conisderions

• Compute ssh keys to later log into instances. Paths to the keys should be provided in variables ssh_public_key and ssh_private_key.
• Variable compute_nsg_name is an optional network security group that can be attached.
• Variable postgresql_replicat_username is used as a login name to setup replication. This doesn't need to be supplied in a master only configuration.
• Variable postgresql_version can be any of the supported version of PostgreSQL at the time of making this brick (9.6, 10, 11, 12, 13 and 14). Note: PostgreSQL version 9.6 will lose official support from 11 Nov 2021.
• Variable database_size_in_gb is the size of the attached ISCSI disks to store the PostgreSQL database on. This can be between 50 and 32768.
• Variable database_vpus_per_gb is the number of volume performance units to be applied to the attached ISCSI disks. The value must be between 0 and 120 and be multiple of 10.
• Variable database_backup_policy_level specifies the name of the backup policy used on the attached database ISCSI disks.
• Variable instance_backup_policy_level specifies the name of the backup policy used on the instance boot volumes.
• Flex Shapes:
  • Variable postgresql_master_is_flex_shape should be defined as true when the master instance is a flex shape. The variables postgresql_master_ocpus and postgresql_master_memory_in_gb should then also be defined. Do not use any of these variables at all when using a standard shape as they are not needed and assume sensible defaults.
  • Variable postgresql_hotstandby_is_flex_shape should be defined as true when the standby instances are a flex shape. The variables postgresql_hotstandby_ocpus and postgresql_hotstandby_memory_in_gb should then also be defined. Do not use any of these variables at all when using a standard shape as they are not needed and assume sensible defaults.
• Standby Instances:
  • Variable postgresql_deploy_hotstandby1 should be defined as true when a hotstandby1 is needed. The variables postgresql_standyby1_name, postgresql_hotstandby1_ad and postgresql_hotstandby1_fd then should also be defined.
  • Variable postgresql_deploy_hotstandby2 should be defined as true when a hotstandby2 is needed. The variables postgresql_standyby2_name, postgresql_hotstandby2_ad and postgresql_hotstandby2_fd then should also be defined.

Sample provider

The following is the base provider definition to be used with this module

terraform {
  required_version = ">= 0.13.5"
}
provider "oci" {
  region       = var.region
  tenancy_ocid = var.tenancy_ocid
  user_ocid        = var.user_ocid
  fingerprint      = var.fingerprint
  private_key_path = var.private_key_path
  disable_auto_retries = "true"
}

provider "oci" {
  alias        = "home"
  region       = data.oci_identity_region_subscriptions.home_region_subscriptions.region_subscriptions[0].region_name
  tenancy_ocid = var.tenancy_ocid  
  user_ocid        = var.user_ocid
  fingerprint      = var.fingerprint
  private_key_path = var.private_key_path
  disable_auto_retries = "true"
}

Variable documentation

Requirements

No requirements.

Providers

Name	Version
null	3.1.0
oci	4.46.0
template	2.2.0

Modules

No modules.

Resources

Name	Type
null_resource.format_disk_exec_hotstandby1	resource
null_resource.format_disk_exec_hotstandby2	resource
null_resource.format_disk_exec_master	resource
null_resource.mount_disk_exec_hotstandby1	resource
null_resource.mount_disk_exec_hotstandby2	resource
null_resource.mount_disk_exec_master	resource
null_resource.partition_disk_hotstandby1	resource
null_resource.partition_disk_hotstandby2	resource
null_resource.partition_disk_master	resource
null_resource.postgresql_hotstandby1_install_binaries	resource
null_resource.postgresql_hotstandby1_setup	resource
null_resource.postgresql_hotstandby2_install_binaries	resource
null_resource.postgresql_hotstandby2_setup	resource
null_resource.postgresql_master_initdb	resource
null_resource.postgresql_master_install_binaries	resource
null_resource.postgresql_master_setup	resource
null_resource.postgresql_master_setup2	resource
null_resource.provisioning_disk_hotstandby1	resource
null_resource.provisioning_disk_hotstandby2	resource
null_resource.provisioning_disk_master	resource
null_resource.pvcreate_exec_hotstandby1	resource
null_resource.pvcreate_exec_hotstandby2	resource
null_resource.pvcreate_exec_master	resource
null_resource.vgcreate_exec_hotstandby1	resource
null_resource.vgcreate_exec_hotstandby2	resource
null_resource.vgcreate_exec_master	resource
oci_core_instance.postgresql_hotstandby1	resource
oci_core_instance.postgresql_hotstandby2	resource
oci_core_instance.postgresql_master	resource
oci_core_volume.ISCSIDisk_hotstandby1	resource
oci_core_volume.ISCSIDisk_hotstandby2	resource
oci_core_volume.ISCSIDisk_master	resource
oci_core_volume_attachment.ISCSIDiskAttachment_hotstandby1	resource
oci_core_volume_attachment.ISCSIDiskAttachment_hotstandby2	resource
oci_core_volume_attachment.ISCSIDiskAttachment_master	resource
oci_core_volume_backup_policy_assignment.backup_policy_assignment_ISCSI_Disk_hotstandby1	resource
oci_core_volume_backup_policy_assignment.backup_policy_assignment_ISCSI_Disk_hotstandby2	resource
oci_core_volume_backup_policy_assignment.backup_policy_assignment_ISCSI_Disk_master	resource
oci_core_volume_backup_policy_assignment.backup_policy_assignment_postgresql_hotstandby1	resource
oci_core_volume_backup_policy_assignment.backup_policy_assignment_postgresql_hotstandby2	resource
oci_core_volume_backup_policy_assignment.backup_policy_assignment_postgresql_master	resource
oci_core_images.ORACLELINUX	data source
oci_core_network_security_groups.NSG	data source
oci_core_subnets.PRIVATESUBNET	data source
oci_core_vcns.VCN	data source
oci_core_volume_backup_policies.DATABASEBACKUPPOLICY	data source
oci_core_volume_backup_policies.INSTANCEBACKUPPOLICY	data source
oci_identity_compartments.COMPARTMENTS	data source
oci_identity_compartments.NWCOMPARTMENTS	data source
template_file.postgresql_install_binaries_sh	data source
template_file.postgresql_master_initdb_sh	data source
template_file.postgresql_master_setup2_sh	data source
template_file.postgresql_master_setup_sh	data source
template_file.postgresql_master_setup_sql	data source
template_file.postgresql_standby_setup_sh	data source

Inputs

Name	Description	Type	Default	Required
compute_nsg_name	Name of the NSG associated to the compute	string	""	no
database_backup_policy_level	Backup policy level for Database ISCSI disks	any	n/a	yes
database_size_in_gb	Disk Capacity for Database	any	n/a	yes
database_vpus_per_gb	Disk VPUS for the Database	any	n/a	yes
fingerprint	API Key Fingerprint for user_ocid derived from public API Key imported in OCI User config	any	n/a	yes
instance_backup_policy_level	Backup policy level for instance boot volume disks	any	n/a	yes
instance_os	Operating system for compute instances	string	"Oracle Linux"	no
linux_compute_instance_compartment_name	Defines the compartment name where the infrastructure will be created	any	n/a	yes
linux_compute_network_compartment_name	Defines the compartment where the Network is currently located	any	n/a	yes
linux_os_version	Operating system version for all Linux instances	string	"7.9"	no
postgresql_deploy_hotstandby1	Boolean to determine if to provision hotstandby1	bool	false	no
postgresql_deploy_hotstandby2	Boolean to determine if to provision hotstandby2	bool	false	no
postgresql_hotstandby1_ad	The availability domain to provision the hoststandby1 instance in	string	""	no
postgresql_hotstandby1_fd	The fault domain to provision the hoststandby1 instance in	string	""	no
postgresql_hotstandby2_ad	The availability domain to provision the hoststandby2 instance in	string	""	no
postgresql_hotstandby2_fd	The fault domain to provision the hoststandby2 instance in	string	""	no
postgresql_hotstandby_is_flex_shape	Boolean to determine if the standy instances are flex or not	bool	false	no
postgresql_hotstandby_memory_in_gb	The amount of memory in GB for the standby instances to use when flex shape is enabled	string	""	no
postgresql_hotstandby_ocpus	The number of OCPUS for the flex instances to use when flex shape is enabled	string	""	no
postgresql_hotstandby_shape	The shape for the hotstandby instances to use	string	""	no
postgresql_master_ad	The availability domain to provision the master instance in	any	n/a	yes
postgresql_master_fd	The fault domain to provision the master instance in	any	n/a	yes
postgresql_master_is_flex_shape	Boolean to determine if the master instance is flex or not	bool	false	no
postgresql_master_memory_in_gb	The amount of memory in GB for the master instance to use when flex shape is enabled	string	""	no
postgresql_master_name	The name given to the master instance	any	n/a	yes
postgresql_master_ocpus	The number of OCPUS for the master instance to use when flex shape is enabled	string	""	no
postgresql_master_shape	The shape for the master instance to use	any	n/a	yes
postgresql_password	The password used in setup of the PostgreSQL database	any	n/a	yes
postgresql_replicat_username	The username used in setup of PostgreSQL replication	string	"replicator"	no
postgresql_standyby1_name	The name given to the standby1 instance	string	""	no
postgresql_standyby2_name	The name given to the standby2 instance	string	""	no
postgresql_version	The version of PostgreSQL used in the setup	any	n/a	yes
private_key_path	Private Key Absolute path location where terraform is executed	any	n/a	yes
private_network_subnet_name	Defines the subnet display name where this resource will be created at	any	n/a	yes
region	Target region where artifacts are going to be created	any	n/a	yes
ssh_private_key	Defines SSH Private Key to be used in order to remotely connect to compute instances	any	n/a	yes
ssh_public_key	Defines SSH Public Key to be used in order to remotely connect to compute instances	any	n/a	yes
tenancy_ocid	OCID of tenancy	any	n/a	yes
user_ocid	User OCID in tenancy. Currently hardcoded to user denny.alquinta@oracle.com	any	n/a	yes
vcn_display_name	VCN Display name to execute lookup	any	n/a	yes

Outputs

Name	Description
PostgreSQL_Master	PostgreSQL Master Instance
PostgreSQL_Username	n/a

Contributing

This project is open source. Please submit your contributions by forking this repository and submitting a pull request! Oracle appreciates any contributions that are made by the open source community.

License

Copyright (c) 2021 Oracle and/or its affiliates.

Licensed under the Universal Permissive License (UPL), Version 1.0.

See LICENSE for more details.