How can I update database parameters in a Lightsail for MySQL or Lightsail for PostgreSQL database?

Short description

When you create a Lightsail database, that database uses a custom parameter group named after the instance endpoint. Amazon Relational Database Service (Amazon RDS) DB instances use a default DB parameter group. To modify the database parameters for a Lightsail database instance, use the AWS Command Line Interface (AWS CLI).

Resolution

Note: If you receive errors when running AWS CLI commands, make sure that you're using the most recent version of the AWS CLI.

1. Install the AWS CLI in the same AWS Region as the Lightsail database.
2. Get a list of the available database parameters that can be modified.
3. Make sure that your IAM user has the required permission (lightsail:UpdateRelationalDatabaseParameters) associated at the policy level.
4. Identify the parameter that you want to change, and then update the parameter.

Note: If you update a static parameter and the apply method is set to pending-reboot, then the parameter update is applied only after the instance is rebooted.

The following is an example Lightsail command for Amazon RDS for MySQL 5.7.26:

aws lightsail update-relational-database-parameters --relational-database-name Lightsail-Database-Ireland-1 --parameters "parameterName=connect_timeout,parameterValue=30,applyMethod=immediate"

Example output:

{
  "operations": [{
    "status": "Succeeded",
    "resourceType": "RelationalDatabase",
    "isTerminal": true,
    "statusChangedAt": 1579868316.024,
    "location": {
      "availabilityZone": "eu-west-1a",
      "regionName": "eu-west-1"
    },
    "operationType": "UpdateRelationalDatabaseParameters",
    "resourceName": "Lightsail-Database-Ireland-1",
    "id": "23a7de77-aa6c-4831-8525-8c6d97921676",
    "createdAt": 1579868316.024
  }]
}

The following is an example Lightsail command for Amazon RDS for PostgreSQL 10.10:

aws lightsail update-relational-database-parameters --relational-database-name lightsail-postgres --parameters "parameterName=deadlock_timeout,parameterValue=30,applyMethod=immediate"

Example output:

{
  "operations": [{
    "status": "Succeeded",
    "resourceType": "RelationalDatabase",
    "isTerminal": true,
    "statusChangedAt": 1579869403.669,
    "location": {
      "availabilityZone": "eu-west-1a",
      "regionName": "eu-west-1"
    },
    "operationType": "UpdateRelationalDatabaseParameters",
    "resourceName": "lightsail-postgres",
    "id": "e18a2827-b140-4872-b90c-ab7850a7b6df",
    "createdAt": 1579869403.669
  }]
}

Related information

Creating a database in Amazon Lightsail