Skip to main content

How to install Database Lab with Terraform on AWS

Database Lab + Terraform

The most convenient and fastest way to install the Database Lab Engine (DLE) and other Database Lab Platform components are using our Terraform Module. Your source PostgreSQL database can be located anywhere. DLE and other components will be created under your AWS account on an EC2 instance.

info

Currently, only the "logical" mode of data retrieval (dump/restore) is supported – the only available method for managed PostgreSQL cloud services such as RDS Postgres, RDS Aurora Postgres, Azure Postgres, or Heroku. "Physical" mode is not yet supported by the module, but it will be in the future. More about various data retrieval options for DLE.

Prerequisites#

  • AWS cloud account
    • You must have AWS Access Keys and a default region in your Terraform environment. To successfully run this Terraform module, the IAM User/Role must have the following permissions:
      • Read/Write permissions on EC2
      • Read/Write permissions on Route53
      • Read/Write permissions on Cloudwatch
    • The DLE runs on an EC2 instance which can be accessed using a selected set of SSH keys uploaded to EC2. Use the Terraform parameter aws_keypair to specify which EC2 Keypair to use
    • AWS Route 53 Hosted Zone for a domain or sub-domain you control.
  • Terraform CLI
    • Minimum version: 1.0
    • 🚧 Currently, it is supposed that you run terraform commands on a Linux machine (macOS and Windows support planned, but not yet implemented)

Install Database Lab components using Terraform#

The following steps were tested on Ubuntu 20.04 but should be valid for other Linux distributions without significant modification.

1. Install Terraform (optional)#

  1. SSH to any machine with internet access, it will be used as deployment machine.
  2. Install Terraform CLI (see the official guide). Example for Ubuntu:
sudo apt-get update && sudo apt-get install -y gnupg software-properties-common curlcurl -fsSL https://apt.releases.hashicorp.com/gpg | sudo apt-key add -sudo apt-add-repository "deb [arch=amd64] https://apt.releases.hashicorp.com $(lsb_release -cs) main"  # Adjust if you have ARM platform.sudo apt-get update && sudo apt-get install terraform# Verify installation.terraform -help

2. Get and configure Database Lab Terraform Module#

note

The module will be available using Terraform Registry soon, though you can clone module's Git repository and adjust the code for your needs.

  1. Get our Terraform Module for Database Lab using Git:
git clone https://gitlab.com/postgres-ai/database-lab-infrastructure.gitcd database-lab-infrastructure/
note
  • To configure parameters used by Terraform (and the Database Lab Engine itself), you will need to modify terraform.tfvars and create a secret.tfvars file with secrets
  • The variables can be set in multiple ways with the following precedence order (lowest to highest):
    • values passed on the command line
    • values defined in terraform.tfvars
    • default values in variables.tf
  • All variables starting with source_ represent the database connection parameters for the source data to be retrieved by DLE. That database must be accessible from the instance hosting the DLE (that one created by Terraform)
  1. Edit terraform.tfvars file. Variables which values start with YOUR_ are required to configure for your deployment, others are optional.
dle_version = "2.5.0"
aws_ami_name = "DBLABserver*"aws_keypair = "YOUR_AWS_KEYPAIR" # e.g. "johndoe"
aws_deploy_region = "us-east-1"aws_deploy_ebs_availability_zone = "us-east-1a"aws_deploy_ec2_instance_type = "t2.large"aws_deploy_ec2_instance_tag_name = "DBLABserver-ec2instance"aws_deploy_ebs_size = "YOUR_INSTANCE_DISK_SIZE" # e.g. "40".aws_deploy_ebs_type = "gp2"aws_deploy_allow_ssh_from_cidrs = ["0.0.0.0/0"]aws_deploy_dns_zone_name = "YOUR_HOSTED_ZONE" # e.g. "mycompany.com".aws_deploy_dns_api_subdomain = "dle-tf-test" # Requires Route 53 hosted zone setup.
# Data source. You can choose one of two options:#    - direct connection to source DB (source_type = "postgres")#    - dump stored on AWS S3 (source_type = "s3")
# Option 1 – direct Postgres connection.source_type = "postgres"source_postgres_version = "YOUR_POSTGRES_VERSION" # e.g. "13".source_postgres_host = "YOUR_POSTGRES_HOST" # e.g. "ec2-3-215-57-87.compute-1.amazonaws.com".source_postgres_port = "YOUR_POSTGRES_PORT" # e.g. "5432".source_postgres_dbname = "YOUR_POSTGRES_DBNAME" # e.g. "postgres".source_postgres_username = "YOUR_POSTGRES_USERNAME" # e.g. "postgres".
# Option 2 – dump on S3.# Important: your AWS user has to be able to create IAM roles# to work with S3 buckets in your AWS account.# source_type = "s3" # source is dump stored on demo s3 bucket# source_pgdump_s3_bucket = "YOUR_S3_BUCKET" # e.g. tf-demo-dump", this is an example public bucket.# source_pgdump_path_on_s3_bucket = "YOUR_PGDUMP_FILEPATH" # e.g. "heroku.dmp" for one-file dump(plain text or compressed plain text)# or test/heroku_dmp_2 for directory type dump, these are example dumps from the demo database.# Important: all vCPUs of the EC2 instance will be utilized for dump restore, so restore time depends on the EC2 instance class
dle_debug_mode = "true"dle_retrieval_refresh_timetable = "0 0 * * 0"
# Include all libraries your installation are using.# Database Lab DB Migration Checker requires "logerrors" extension.postgres_config_shared_preload_libraries = "pg_stat_statements,logerrors"
platform_project_name = "aws_test_tf"
  1. Create secret.tfvars containing platform_access_token, source_postgres_password, and vcs_github_secret_token. An example:
# Database Lab Platform.# Open https://console.postgres.ai/, choose your organization,# then "Access tokens" in the left menu, generate token under "Add token"# section, "Personal token" should be unchecked.platform_access_token = "YOUR_PLATFORM_ACCESS_TOKEN"
# Postgres password, set only if you are using a direct Postgres connection.source_postgres_password = "YOUR_POSTGRES_PASSWORD"
# GitHub token. To generate, open https://github.com/settings/tokens/new.vcs_github_secret_token = "YOUR_VCS_SECRET_TOKEN"
info

If you need to use your own PostgreSQL configuration parameters (copying them into postgresql_clones_custom.conf file), make sure there is a private key in this Terraform module directory to access the EC2 machine.

The private file must be named ubuntu.pem.

  1. Set environment variables with AWS credentials:
# Browse to AWS Console / My security credentials / Access keys for CLI, SDK, & API access.# Create or use existing Access Key.# Link: https://console.aws.amazon.com/iam/home#/security_credentials?credentials=iamexport AWS_ACCESS_KEY_ID="YOUR_KEY_ID"export AWS_SECRET_ACCESS_KEY="YOUR_SECRET_ACCESS_KEY"
  1. Review and adjust file postgresql_clones_custom.conf. This file will be added to all Postgres instances in clones created by DLE. If you don't need any custom parameters, leave this file empty but do not delete it.

  2. Copy to your current directory (that is used for TF deployment) your AWS key-pair file that was configured with aws_keypair parameter in terraform.tfvars file before and rename it to ubuntu.pem.

3. Run deployment#

  1. Initialize Terraform working directory:
terraform init
  1. Deploy:
terraform apply -var-file="secret.tfvars" -auto-approve
  1. If everything goes well, you should get an output like this:
vcs_db_migration_checker_verification_token = "gsio7KmgaxECfJ80kUx2tUeIf4kEXZex"dle_verification_token = "zXPodd13LyQaKgVXGmSCeB8TUtnGNnIa"ec2_public_dns = "ec2-11-111-111-11.us-east-2.compute.amazonaws.com"ec2instance = "i-0000000000000"ip = "11.111.111.11"platform_joe_signing_secret = "lG23qZbUh2kq0ULIBfW6TRwKzqGZu1aP"public_dns_name = "demo-api-engine.aws.postgres.ai"  # todo: this should be URL, not hostname – further we'll need URL, with protocol – `https://`
  1. To verify the result and check the progress, you might want to connect to the just-created EC2 machine using the IP address or hostname from the Terraform output. In our example, it can be done using this one-liner:
echo "sudo docker logs dblab_server -f" | ssh [email protected] -i postgres_ext_test.pem
DLE server started successfully and is waiting for your commands if you see a message like:
2021/07/02 10:28:51 [INFO]   Server started listening on :2345.
note

You can find more about DLE logs and configuration on this page.

4. Set up Database Lab Platform#

note

🚀 We are working on the automation of this step, the auto-registration feature will be available soon.

  1. Sign in to the Postgres.ai Platform and register your new DLE server:

    1. Go to Database Lab / Instances in the left menu.
    2. Press the Add instance button.
    3. Project – specify any name (this is how your DLE server will be named in the platform).
    4. Verification token – use the token generated above (verification_token value); do NOT press the "Generate" button here!
    5. URL – use the value generated above.
    6. Click the Verify URL button to check the connectivity. Then click the Add button to register the DLE instance. If everything is right, you should see the DLE page with green "OK" status.
  2. Add Joe chatbot for efficient SQL optimization workflow:

    1. Go to the SQL Optimization / Ask Joe page using the left menu, click the Add instance button, specify the same project as you defined in the previous step.
    2. Signing secret – use platform_joe_signing_secret from the Terraform output.
    3. URL – use public_dns_name values from the Terraform output with port 444; in our example, it's https://demo-api-engine.aws.postgres.ai:444.
    4. Click the Verify URL button to check the connectivity and then click the Add button to register the Joe instance.

    Now you can start using Joe chatbot for SQL execution plans troubleshooting and verification of optimization ideas. As a quick test, go to SQL Optimization / Ask Joe in the left menu, select the instance, and enter \dt+ command (a psql command to show the list of tables with sizes). You should see how Joe created a thin clone behind the scenes and immediately ran this psql command, presenting the result to you.

5. Set up Database Migration Checker#

  1. Prepare a repository with your DB migrations (Flyway, Sqitch, Liquibase, etc.)
  2. Add secrets:
  • DLMC_CI_ENDPOINT - an endpoint of your Database Lab Migration Checker service – use vcs_db_migration_checker_registration_url from the Terraform output
  • DLMC_VERIFICATION_TOKEN - verification token for the Database Lab Migration Checker API – use vcs_db_migration_checker_verification_token from the Terraform output
  1. Configure a new workflow in the created repository (see an example of configuration: https://github.com/postgres-ai/green-zone/blob/master/.github/workflows/main.yml)

6. Install and try the client CLI (dblab)#

  1. Follow the guide to install Database Lab CLI
  2. Initialize CLI:
dblab init --environment-id=<ANY NAME FOR ENVIRONMENT> --url=https://<public_dns_name> --token=<your_personal_token_from_postgres_ai_platform>
  1. Try it:
dblab instance status

It should return the OK status:

{    "status": {        "code": "OK",        "message": "Instance is ready"    },    ...}

Important Note#

When the DLE creates new database clones, it makes them available on incremental ports in the 6000 range (e.g. 6000, 6001, ...). The DLE CLI will also report that the clone is available on a port in the 6000 range. However, please note that these are the ports when accessing the DLE from localhost. This Terraform module deploys Envoy to handle SSL termination and port forwarding to provide connection to the clones provisioned by the DLE.

Bottom Line: When connecting to clones, add 3000 to the port number reported by the DLE CLI to connect to the clone. for example, if the CLI reports that a new clone is available at port 6001 connect that clone at port 9001.

Known Issues#

Certificate Authority Authorization (CAA) for your Hosted Zone#

Depending on your DNS provider and configuration, you may need to create a CAA record in your hosted zone.vOn instance creation, this Terraform module will use Let's Encrypt to generate a valid SSL Certificate. For that to succeed, Let's Encrypt must be recognized as a valid issuing CA by your domain. To do this, add a DNS record that looks like this:

Domain Record  type  Valueexample.com.   CAA   0 issue "letsencrypt.org"

Troubleshooting#

You can get help deploying the DLE. Here are two great ways to do this:

Reporting Issues & Contributing#

We want to make deploying and managing the Database Lab Engine as easy as possible! Please report bugs and submit feature ideas using Gitlab's Issue feature.