Skip to main content

AWS RDS/Aurora: Production setup—Manual agent installation

DBtune is designed to fit seamlessly into your existing AWS infrastructure. Whether you prefer standalone Amazon EC2 instances or containerized orchestration via Docker, ECS, or AWS Fargate, the agent is flexible enough to adapt to your environment.

For organizations managing large-scale environments, that need to tune multiple database instances from a single client host, they can easily use their own automation scripts to deploy and manage several agents simultaneously.

Prerequisites

  1. You must have an existing RDS or Aurora PostgreSQL database.

  2. AWS IAM permissions to create and modify IAM roles and policies.

  3. The rds_superuser role is required to create an extension pg_stat_statements.

  4. Make sure that you have a custom parameter group attached to your RDS/Aurora instance. DBtune will modify the parameter group that is assigned to the instance during tuning. We advise a separate parameter group per database instance so as to not impact the server parameters on other database instances that are not the target for the tuning. You must manually update this setting by selecting Modify your database instance in the AWS console when assigning a new parameter group.

    Create a new parameter group if one doesn't exist as mentioned here.

    Note:

    Attaching the custom parameter group requires a manual reboot of the database.*

    Apply that parameter group to the database as mentioned here.

  5. Ensure that port 5432 is reachable from your client host to the RDS/Aurora endpoint. Check your RDS/Aurora Security group inbound rules. If needed, contact your network team for assistance with Firewall or NAT gateway configuration.

  6. Ensure you have outbound access to whitelist the following URL for external access: https://app.dbtune.com

Step 1 Database user & extension setup

Log in with your master user using psql.

psql "host=$RDSHOST port=5432 dbname=<your_db_name> user=<username> password=<your_master_password>"

Create user dbtune_agent and grant pg_monitor role.

postgres=> create user dbtune_agent with password 'password';
CREATE ROLE
postgres=> grant pg_monitor to dbtune_agent;
GRANT ROLE

Enable pg_stat_statements on your database.

postgres=> create extension pg_stat_statements;
CREATE EXTENSION
Step 2: Configure IAM permissions

  1. Create a new IAM policy with the following permissions.

    Navigate to IAM > Policies > Create policy > Policy editor > select JSON and copy the below permissions.

    {
      "Version": "2012-10-17",
      "Statement": [
        {
          "Sid": "DescribeRDSInstanceTypes",
          "Effect": "Allow",
          "Action": "ec2:DescribeInstanceTypes",
          "Resource": "*"
        },
        {
          "Sid": "DescribeAndModifyDBInstance",
          "Effect": "Allow",
          "Action": [
            "rds:DescribeDBInstances",
            "rds:ModifyDBInstance"
          ],
          "Resource": "*"
        },
        {
          "Sid": "GetDBMetrics",
          "Effect": "Allow",
          "Action": [
            "cloudwatch:GetMetricStatistics",
            "pi:GetResourceMetrics"
          ],
          "Resource": "*"
        },
        {
          "Sid": "ModifyDBParameterGroup",
          "Effect": "Allow",
          "Action": "rds:ModifyDBParameterGroup",
          "Resource": "*"
        },
        {
          "Sid": "RebootDBInstance",
          "Effect": "Allow",
          "Action": "rds:RebootDBInstance",
          "Resource": "*"
        }
      ]
    }

Click Next, provide a unique policy name, and click Create policy.

  1. Attach the policy to your IAM role.

    Go to IAM > Roles > IAM role > Add permissions.

    Select Permissions policies and choose the policy you created in previous step, then click Next and Add permissions.

  2. Attach the IAM role to your EC2 instance.

    Go to EC2 > Actions > Security > Modify IAM role.

    Select the IAM role you updated with the required permissions in the previous step, then click Update IAM role.

Step 3 : Install and configure the agent

Download the binary for dbtune-agent on your client host.

curl https://raw.githubusercontent.com/dbtuneai/dbtune-agent/refs/heads/main/setup.sh > /tmp/dbtune-agent.sh && sh /tmp/dbtune-agent.sh

Create a dbtune.yaml file and copy the contents below.

 postgresql:
  connection_url: postgresql://dbtune:<password>@<RDSendpoint>:5432/<database>
  include_queries: true
  # Set to true to allow the agent to restart the database during tuning

rds:
  AWS_ACCESS_KEY_ID: "<your-aws-access-key-id>" # Optional for EC2 and ECS
  AWS_SECRET_ACCESS_KEY: "<your-aws-secret-access-key>" # Optional for EC2 and ECS
  AWS_REGION: "<RDS-region>" 
  RDS_DATABASE_IDENTIFIER: "<your-database-identifier>" # The writer instance of the cluster
  RDS_PARAMETER_GROUP_NAME: "<your-parameter-group-name>" # Be sure to define a custom one and not to use the default.postgresXX one

dbtune:
  server_url: https://app.dbtune.com
  api_key: <copy-from-app-settings>
  database_id: <copy-from-app-settings>

debug: false

Ensure the below details are entered correctly.

  • connection_url: Username, password, rds endpoint(Find at: RDS Console > Your DB > Connectivity & Security > Endpoint) and database name.

  • RDS_DATABASE_IDENTIFIER: Find at: RDS/Aurora Console > Databases > Instance > Configuration (DB instance ID).

  • AWS_REGION: Find at: RDS/Aurora Console > Databases > Summary.

  • RDS_PARAMETER_GROUP_NAME:Find at: RDS/Aurora Console > Parameter Groups.

  • DBT_DBTUNE_API_KEY: Find at: DBtune app >Agent tab > Binary > Config file

  • DBT_DBTUNE_DATABASE_ID: Find at: DBtune app >Agent tab > Binary > Config file

Finally, run the DBtune agent on your client host. You can use the screen feature to keep it running in the background (press Ctrl + A, then D to detach the screen session).

Finally run the DBtune agent:

./dbtune-agent --rds

OR

./dbtune-agent --aurora

Note:

To run multiple agents on a single client host, create a separate directory for each database instance with its own* dbtune.yaml and dbtune-agent files.

Last updated on