Repository files navigation

ThisTerraform Module can be used as a template for deploying theDatabase Lab Engine to cloud hosting providers. Please feel free to tailor it to meet your requirements.

Your source PostgreSQL database can be located anywhere, but DLE with other components will be created on an EC2 instance under your AWS account. Currently, only "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, but it will be in the future. More about various data retrieval options for DLE:https://postgres.ai/docs/how-to-guides/administration/data.

• AWS

• AWS Account
• Terraform Installed (minimal version: 1.0.0)
• AWSRoute 53 Hosted Zone (For setting up TLS) for a domain or sub-domain you control
• You must have AWS Access Keys and a default region in your Terraform environment (See section on required IAM Permissions)
• The DLE runs on an EC2 instance which can be accessed using a selected set of SSH keys uploaded to EC2.
• Required IAM Permissions: 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

• 🚧 Currently, it is supposed that you runterraform commands on a Linux machine or MacOS. Windows support is not yet implemented (but planned).
• It is recommended to clone this Git repository and adjust for your needs. Below we provide the detailed step-by-step instructions for quick start (see "Quick start") for a PoC setup
• To configure parameters used by Terraform (and the Database Lab Engine itself), you will need to modifyterraform.tfvars and create a file with secrets (secret.tfvars)
• This Terraform module can be run independently or combined with any other standard Terraform module. You can learn more about using Terraform and the Terraform CLIhere
• The variables can be set in multiple ways with the following precedence order (lowest to highest):
  • default values invariables.tf
  • values defined interraform.tfvars
  • values passed on the command line
• All variables starting withpostgres_ represent the source database connection information for the data (from that database) to be fetched by the DLE. That database must be accessible from the instance hosting the DLE (that one created by Terraform)

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

1. SSH to any machine with internet access, it will be used as deployment machine

2. Install Terraformhttps://learn.hashicorp.com/tutorials/terraform/install-cli. Example for Ubuntu:

   sudo apt-get update&& sudo apt-get install -y gnupg software-properties-common curl curl -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

3. Get TF code for Database Lab:

   git clone https://gitlab.com/postgres-ai/database-lab-infrastructure.gitcd database-lab-infrastructure/

4. Editterraform.tfvars file. In our example, we will use Heroku demo database as a source:

   dle_version_full = "2.5.0"aws_ami_name = "DBLABserver*"aws_deploy_region = "us-east-1"aws_deploy_ebs_availability_zone = "us-east-1a"aws_deploy_ec2_instance_type = "c5.large"aws_deploy_ec2_instance_tag_name = "DBLABserver-ec2instance"aws_deploy_ebs_size = "10"aws_deploy_ec2_volumes_count = "2"aws_deploy_ebs_type = "gp2"aws_deploy_allow_ssh_from_cidrs = ["0.0.0.0/0"]aws_deploy_dns_api_subdomain = "tf-test" # subdomain in aws.postgres.ai, fqdn will be ${dns_api_subdomain}-engine.aws.postgressource_postgres_version = "13"source_postgres_host = "ec2-3-215-57-87.compute-1.amazonaws.com"source_postgres_port = "5432"source_postgres_dbname = "d3dljqkrnopdvg" # this is an existing DB (Heroku example DB)source_postgres_username = "bfxuriuhcfpftt" # in secret.tfvars, use:   source_postgres_password = "dfe01cbd809a71efbaecafec5311a36b439460ace161627e5973e278dfe960b7"dle_debug_mode = "true"dle_retrieval_refresh_timetable = "0 0 * * 0"postgres_config_shared_preload_libraries = "pg_stat_statements,logerrors" # DB Migration Checker requires logerrors extensionplatform_project_name = "aws_test_tf"# list of ssh public keys stored in filesssh_public_keys_files_list = ["~/.ssh/id_rsa.pub"]# or provided inlinessh_public_keys_list = [  "ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDhbblazDXCFEc21DtFzprWC8DiqidnVRROzp6J6BeJR9+XydPUtl0Rt2mcNvxL5ro5bI9u5JRW8aDd6s+Orpr66hEDdwQTbT1wp5nyduFQcT3rR   +aeDSilQvAHjr4/z/GZ6IgZ5MICSIh5hJJagHoxAVqeS9dCA27tv/n2T2XrxIUeBhywH1EmfwrnEw97tHM8F+yegayFDI1nVOUWUIxFMaygMygix8uKbQ2fl4rkkxG2oEx7uyAFMXHt4bewNbZuAp8b/b5ODL6tGHuHhcwfbWGriCO+l7UOf1K9maTx00o4wkzAPyd+qs70y/1iMX2YOOLYaYYdptEnFal2DVoD example@example.com"  ]

5. Createsecret.tfvars containingsource_postgres_password,platform_access_token, andvcs_github_secret_token. An example:

   source_postgres_password = "YOUR_DB_PASSWORD" # todo: put pwd for heroku example DB hereplatform_access_token = "YOUR_ACCESS_TOKEN"   # to generate, open https://console.postgres.ai/, choose your organization,                                            # then "Access tokens" in the left menuvcs_github_secret_token = "vcs_secret_token"  # to generate, open https://github.com/settings/tokens/new

6. Initialize

   terraform init

7. Set environment variables with AWS credentials:

   export AWS_ACCESS_KEY_ID ="keyid"# todo: how to get it?export AWS_SECRET_ACCESS_KEY ="accesskey"

8. Deploy:

   terraform  apply -var-file="secret.tfvars" -auto-approve

9. If everything goes well, you should get an output like this:

   #####################################################################Congratulations! Database Lab Engine installed.Data initialization may take time, depending on the database size.You should be able to work with all DLE interfaces already:- [RECOMMENDED] UI: https://tf-test.aws.postgres.ai:446- CLI: dblab init --url=https://tf-test.aws.postgres.ai --token=sDTPu17pzXhW9DkhcSGpAMj72KgiIJxG --environment="i-0687b060f45314be5" --insecure- API: https://tf-test.aws.postgres.ai- SSH connection for troubleshooting: ssh ubuntu@3.92.133.178 -i dmitry-DBLABserver-ec2instance.pem(Use verification token: sDTPu17pzXhW9DkhcSGpAMj72KgiIJxGFor support, go to https://postgres.ai/contact.#####################################################################

10. To verify result and check the progress, you might want to connect to the just-created EC2 machine using IP address or hostname from the Terraform output and ssh key from ssh_public_keys_files_list and/or ssh_public_keys_list variables. In our example, it can be done using this one-liner (you can find more about DLE logs and configuration on this page:https://postgres.ai/docs/how-to-guides/administration/engine-manage):

    echo"sudo docker logs dblab_server -f"| ssh ubuntu@18.118.126.25 -i postgres_ext_test.pem

    Once you see the message like:

    2021/07/02 10:28:51 [INFO]   Server started listening on :2345.

    – it means that the DLE server started successfully and is waiting for you commands

11. Sign in to thePostgres.ai Platform and register your new DLE server:

    1. Go toDatabase 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 // todo: not convenient, we need URL but reported was only hostname
    6. Press the "Verify URL" button to check the connectivity. Then press "Add". If everything is right, you should see the DLE page with green "OK" status:
    

12. 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 – useplatform_joe_signing_secret from the Terraform output
    3. URL – usepublic_dns_name values from the Terraform output with port444; in our example, it'shttps://demo-api-engine.aws.postgres.ai:444
    4. Press "Verify URL" to check connectivity and then press "Add". You should see:
    

    Now you can start using Joe chatbot for SQL execution plans troubleshooting and verification of optimization ideas. As a quick test, go toSQL Optimization > Ask Joe in the left menu, 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:

13. Set upDB migration checker. Prepare a repository with your DB migrations(Flyway, Sqitch, Liquibase, etc.):

    1. Add secrets:
       • DLMC_CI_ENDPOINT - an endpoint of your Database Lab Migration Checker service – usevcs_db_migration_checker_registration_url from the Terraform output
       • DLMC_VERIFICATION_TOKEN - verification token for the Database Lab Migration Checker API – usevcs_db_migration_checker_verification_token from the Terraform output
    2. 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)
       • add a custom action:https://github.com/marketplace/actions/database-lab-realistic-db-testing-in-ci
       • provide input params for the action (the full list of available input params)
       • provide environment variables:
         • DLMC_CI_ENDPOINT - use a CI Checker endpoint from the repository secrets
         • DLMC_VERIFICATION_TOKEN - use a verification token from the repository secrets

14. Install and try the client CLI (dblab)

    1. Follow theguide 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"    },...}

This is it!

If you need to remove everything created by this Terraform module, you can simply runterraform destroy. Do not forget to do it if you're just experimenting. Otherwise, if you leave infrastructure blocks running, they might significantly affect your AWS bill (depending on the EC2 instance family you've chosen, the disk type, and size).

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 fromlocalhost. This Terraform module deploysEnvoy to handle SSL termination and port forwarding to connect to DLE generated clones.

Follow thehow-to guide to install Database Lab with Terraform on AWS

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 useLet'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"

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

• Join theDatabase Lab Community Slack
• Reach out to the Postgres.ai team onIntercom chat widget (located at the bottom right corner)

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