Observe your PostgreSQL^® metrics with Terraform, InfluxDB and Grafana

In our last adventure, we bootstrapped the Aiven Terraform Provider and deployed a PostgreSQL database with very few lines of code.

To follow up the Aiven Databases and Terraform for Fun and Profit article, we will expand our infrastructure by sending our PostgreSQL metrics to an InfluxDB service and visualize them with Grafana.

If you haven't gone through the previous article, you can start with some basic Terraform code to spin up a PostgreSQL database in a main.tf file:

variable "aiven_api_token" {
  type = string
}

variable "aiven_project_name" {
  type = string
}

terraform {
  required_providers {
    aiven = {
      source  = "aiven/aiven"
      version = "2.1.12" # check out the latest release in the docs page!
    }
  }
}

provider "aiven" {
  api_token = var.aiven_api_token
}

data "aiven_project" "my_project" {
  project = var.aiven_project_name
}

resource "aiven_pg" "postgresql" {
  project      = data.aiven_project.my_project.project
  service_name = "postgresql"
  cloud_name   = "google-europe-west3"
  plan         = "startup-4"

  termination_protection = true

  pg_user_config {
    pg_version     = 13
    admin_username = "admin"

    pgbouncer {
      autodb_max_db_connections = 200
    }
  }
}

output "postgresql_service_uri" {
  value     = aiven_pg.postgresql.service_uri
  sensitive = true
}
Copy to clipboard

You will also need a file named terraform.tfvars with your authentication token:

aiven_api_token    = "your_access_token_here"
aiven_project_name = "your_project_name_here"
Copy to clipboard

Finally, execute the following commands on your terminal to create the PostgreSQL service:

$ terraform init
$ terraform plan
$ terraform apply
Copy to clipboard

The init configures your local directory as a Terraform project, plan outputs the changes Terraform is about to make, and apply applies those changes.

Let's push PostgreSQL metrics to InfluxDB

Out of the box, Aiven provides metrics like CPU and memory usage for all its products. You can see them under the Metrics section in the Aiven web console.

If you need more than that, don't worry, Aiven has your back. You can push detailed metrics to an Aiven for InfluxDB instance and visualize them with a custom Aiven for Grafana dashboard prebuilt by us, all without leaving your code editor. All of this is possible thanks to the Aiven service integrations that help you integrate Aiven services and external applications to level up your data infrastructure observability.

Start the metrics push by creating an InfluxDB instance, and then integrate it with PostgreSQL metrics. Add the code below in the main.tf file:

resource "aiven_influxdb" "influxdb" {
  project      = data.aiven_project.my_project.project
  cloud_name   = "google-europe-west3"
  plan         = "startup-4"
  service_name = "influxdb"
}

resource "aiven_service_integration" "postgresql_metrics" {
  project                  = data.aiven_project.my_project.project
  integration_type         = "metrics"
  source_service_name      = aiven_pg.postgresql.service_name
  destination_service_name = aiven_influxdb.influxdb.service_name
}
Copy to clipboard

The aiven_influxdb resource creates an InfluxDB instance following the same code pattern as the PostgreSQL already created. The aiven_service_integration resource is responsible for integration between Aiven products. Here it is sending "metrics" from aiven_pg.postgresql to aiven_influxdb.influxdb.

To finish the infrastructure setup, spin up a Grafana instance to visualize the PostgreSQL metrics sent to InfluxDB. Add the last bit of content to the main.tf file:

resource "aiven_grafana" "grafana" {
  project      = data.aiven_project.my_project.project
  cloud_name   = "google-europe-west3"
  plan         = "startup-4"
  service_name = "grafana"

  grafana_user_config {
    public_access {
      grafana = true
    }
  }
}

resource "aiven_service_integration" "grafana_dashboard" {
  project                  = data.aiven_project.my_project.project
  integration_type         = "dashboard"
  source_service_name      = aiven_grafana.grafana.service_name
  destination_service_name = aiven_influxdb.influxdb.service_name
}
Copy to clipboard

After getting used to the Terraform Provider, you can probably guess what is happening above: creating a Grafana instance with aiven_grafana resource and giving it public access within the grafana_user_config block. You can take a peek at the official Aiven for Grafana docs for more options.

The aiven_service_integration resource integrates Grafana with InfluxDB, to read the metrics and show them in a dashboard.

Finally, let's output the Grafana access information, composed of the URI, username and password:

output "grafana_service_uri" {
  value     = aiven_grafana.grafana.service_uri
  sensitive = true
}

output "grafana_service_username" {
  value = aiven_grafana.grafana.service_username
}

output "grafana_service_password" {
  value     = aiven_grafana.grafana.service_password
  sensitive = true
}
Copy to clipboard

It's showtime. Run the Terraform plan and apply commands to visualize the changes, and put our infrastructure in the desired state:

$ terraform plan
$ terraform apply
Copy to clipboard

After deploying, we need the Grafana URL and password to access its interface. As this information is considered sensitive, we need to run the following commands to output it.

First for the URI:

$ terraform output -raw grafana_service_uri
https://grafana-your-project.aivencloud.com:443
Copy to clipboard

And for the password:

$ terraform output -raw grafana_service_password
your_password_output
Copy to clipboard

Open the grafana_service_uri in your browser and log in with the avnadmin username (created by default on every instance) and the grafana_service_password output.

When you get to the main page, navigate to Dashboards > Manage, access the Aiven PostgreSQL - postgresql - Resources and voilà, telemetry heaviness.

We now have metrics about the machine, indexes, databases and much, much more.

This kind of visibility is extremely important during load testing and performance tuning, as well as when creating alerts and understanding your system's behavior.

Tidy up the resources when you're done

Infrastructure costs money and natural resources. When you've finished with the services, you can remove them with Terraform:

$ terraform destroy
Copy to clipboard

Type yes and hit enter. Your whole infrastructure should be gone.

Error: 403: {"errors":[{"message":"Service is protected against termination and shutdown. Remove termination protection first.","status":403}],"message":"Service is protected against termination and shutdown. Remove termination protection first."} -
Copy to clipboard

Oh wait, it seems we got an error 🤔 While Grafana and InfluxDB are gone, the termination_protection flag protected our PostgreSQL resource. To remove it, go back to the main.tf file and set the flag to false.

resource "aiven_pg" "postgresql" {
  [...]
  termination_protection = false
  [...]
}
Copy to clipboard

Run the following Terraform apply command to change only the PostgreSQL resource (otherwise it will create the whole infrastructure again):

$ terraform apply --target aiven_pg.postgresql
Copy to clipboard

And, finally, today's last command:

$ terraform destroy
[...]

Destroy complete! Resources: 1 destroyed.
Copy to clipboard

Terraform and Aiven for IaaS success

Working with GUIs and CLIs is perfect for most teams and organisations, but infrastructure as code is taking over the way we manage our platforms. Terraform is the current de facto tool to achieve this level abstraction, allowing engineers and operators to collaborate in building and deploying their solutions. And remember, if you need an open source data service, the Aiven Terraform Provider is there for you.

Further reading

Please check this GitHub repository to see the whole code and any future updates for it.

If you want to learn more about the tools and services used here, make sure to check the links below:

• Introduction to Terraform
• An introduction to PostgreSQL
• An introduction to time series databases

If you have any comments about Aiven's Terraform Provider or want to help us improve it, please check the GitHub repository and the official documentation.

Not using Aiven services yet? Sign up now for your free trial at https://console.aiven.io/signup!

In the meantime, make sure you follow our changelog and blog RSS feeds or our LinkedIn and Twitter accounts to stay up-to-date with product and feature-related news.