do it automatically - apply changes in rds parameter group, reboot rds, and apply another change

Question

Our infrastructure as code is using terraform.

I want to create a new postgreSQL database by terraform, with pgaudit log enabled.

Following aws document Working with the pgaudit Extension

It has extra steps to:

Step 1 CREATE ROLE rds_pgaudit

so I need to wait the rds instance status is available first, then run a sql command to this new database (in vpc)

But how to run this sql command in terraform?

Step 2. Modify the parameter group

I can do this via resource aws_db_parameter_group, set the variable parameters

Step 3. reboot the instance

Mot sure how to do this with terraform, by provisioner local-exec or remote-exec???

Step 4 ~ 6 more sql commands

Seems I need wait the database back to available status, then I can run the sql script. Then how to run?

Any suggestions?

Venu Venu · Accepted Answer · 2018-10-23T06:31:45

You can use remote-exec or local-exec(if RDS is publicly available and you are able to reach to RDS from your local machine).

Remote-Exec:

resource "aws_instance" "remote_execution_server" 
{
 provisioner "remote-exec" {
 inline = [
  "Download the sql related script from S3"
  "aws s3 cp s3://your_bucket/sql.sh /home/ec2-user/",
  "sh /home/ec2-user/sql.sh",
  ]
 }
}

Add this snippet in your terraform and this will launch a new server as part of your RDS update/creation and run the sql query. Make sure that RDS credentials are provided as arguments for sql.sh script to connect to RDS.

Ref: https://www.terraform.io/docs/provisioners/remote-exec.html#script

Local-Exec:

If your RDS is publicly accessible or it is accessible from your local machine then you don't need to launch a new instance for running the sql query. Just have your commands ready for executing the sql query including RDS user credentials.

resource "aws_instance" "web" {
# ...

    provisioner "local-exec" {
    command = "echo ${aws_instance.web.private_ip} >> private_ips.txt"
  }
}

Ref: https://www.terraform.io/docs/provisioners/local-exec.html

do it automatically - apply changes in rds parameter group, reboot rds, and apply another change

1 Answers