Deploy a PostgreSQL® service with custom configurations
Set up a Aiven for PostgreSQL® database with configurations defined in Terraform. A part of Aiven's Terraform Cookbook.
PostgreSQL® is a popular choice as an open source, relational database to run in production. This example shows how to use Terraform to create a single PostgreSQL service in a single cloud and region with some custom configurations applied to the service.
The following image shows that the Aiven Terraform Provider calls the Aiven API under the hood to create an Aiven for PostgreSQL® service on the Google Cloud Platform (Europe):
The following sample Terraform script stands up the single PostgreSQL service with some custom configurations.
Be sure to check out the getting started guide
to learn about the common files required to execute the following recipe. For example, you'll need to declare the variables for project_name
, api_token
, admin_username
, and admin_password
.
Common files
Navigate to a new folder and add the following files.
Add the following to a new provider.tf
file:
terraform { required_providers { aiven = { source = "aiven/aiven" version = ">=4.0.0, < 5.0.0" } } } provider "aiven" { api_token = var.aiven_api_token }
You can also set the environment variable TF_VAR_aiven_api_token
for the api_token
property. With this, you don't need to pass the -var-file
flag when executing Terraform commands.
To avoid including sensitive information in source control, the variables are defined here in the variables.tf
file. You can then use a *.tfvars
file with the actual values so that Terraform receives the values during runtime, and exclude it.
The variables.tf
file defines the API token, the project name to use, and the prefix for the service name:
variable "aiven_api_token" { description = "Aiven console API token" type = string } variable "project_name" { description = "Aiven console project name" type = string } variable "admin_username" { description = "Your preferred username for the PostgreSQL service" type = string } variable "admin_password" { description = "Your preferred password for the PostgreSQL service" type = string }
The var-values.tfvars
file holds the actual values and is passed to Terraform using the -var-file=
flag.
var-values.tfvars
file:
aiven_api_token = "<YOUR-AIVEN-AUTHENTICATION-TOKEN-GOES-HERE>" project_name = "<YOUR-AIVEN-CONSOLE-PROJECT-NAME-GOES-HERE>" admin_username = "<YOUR-PREFERRED-SERVICE-USERNAME>" admin_password = "<YOUR-PREFERRED-SERVICE-PASSWORD>"
Services.tf file
services.tf
file:
resource "aiven_pg" "pg" { project = var.project_name cloud_name = "google-europe-west1" plan = "startup-4" service_name = "my-pg1-gcp-eu" maintenance_window_dow = "monday" maintenance_window_time = "10:00:00" termination_protection = true pg_user_config { pg_version = 14 backup_hour = 01 backup_minute = 30 shared_buffers_percentage = 40 ip_filter_string = ["0.0.0.0/0"] admin_username = var.admin_username admin_password = var.admin_password ## project_to_fork_from = "source-project-name" ## service_to_fork_from = "source-pg-service" ## pg_read_replica = true pg { idle_in_transaction_session_timeout = 900 log_min_duration_statement = 1000 deadlock_timeout = 2000 } } }
Execute the files
The init
command performs several different initialization steps in order to prepare the current working directory for use with Terraform. In our case, this command automatically finds, downloads, and installs the necessary Aiven Terraform provider plugins.
terraform init
The plan
command creates an execution plan and shows you the resources that will be created (or modified) for you. This command does not actually create any resource; this is more like a preview.
terraform plan -var-file=var-values.tfvars
If you're satisfied with the output of terraform plan
, go ahead and run the terraform apply
command which actually does the task or creating (or modifying) your infrastructure resources.
terraform apply -var-file=var-values.tfvars
When running a database in production, there are lots of fine tunings that need to happen. Let's go over some of these optional custom configurations used and understand when to use them.
First, you can choose the PostgreSQL version using the pg_version
parameter. A default version is chosen for you if you don't specify the version. backup_hour
and backup_minute
denote the hour and minute of the day (in UTC) when backup for the service is started. In this example, the backup starts at 1:30 AM UTC daily. shared_buffers_percentage
sets the percentage of memory in your system that the database server uses for shared memory buffers.
To learn more about these settings, please refer to the PostgreSQL resource consumption docs mentioned under the More resources section.
The ip_filter_string
parameter filters incoming connections based on the mentioned IP addresses. The example of "0.0.0.0/0" is an allow-all value.
If there are only specific IP addresses that you'd like to allow for clients, you'd put those IP addresses on this list. When you create an Aiven for PostgreSQL service, the database admin username and password are generated for you. You can set your preferred values by using admin_username
and admin_password
parameters.
If you wanted this PostgreSQL service to be a read-only replica of an existing PostgreSQL service, you could do that by declaring the project_to_fork_from
, service_to_fork_from
and setting the value of pg_read_replica
to true. Since the service in this example is not a read-only replica, these configuration lines are commented out.
If you choose to set pg_read_replica
to true, then the custom admin_username
and admin_password
will no longer work because the configurations from the master node will be used.
If a transaction is waiting for a client query, there might be a time limit after which you want the session to time out. This is exactly what idle_in_transaction_session_timeout
will do for you if you set a limit. Keep in mind that a value of zero, which is the default value, will disable the timeout.
Once you have some idea of how long a typical query statement should take to execute, log_min_duration_statement
setting allows you to log only the ones that exceed some threshold you set. And then, you'll only see statements that take longer than the specified time to run. This can be extremely handy in finding the source of outlier statements that take much longer than most to execute.
The deadlock_timeout
is the amount of time that PostgreSQL waits on a lock before it checks for a deadlock condition. The deadlock check is an expensive operation, so it is not run every time the server waits for a lock. The default is one second, but this can be increased for heavily loaded servers. All of these times are taken as milliseconds if specified without units.
More resources
To learn how to get started with Aiven Terraform Provider and specific PostgreSQL configurations for you use case, check out the following resources: