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

Short description

When a Lightsail database is created, it is uses a custom parameter group that is named after the instance endpoint, unlike Amazon Relational Database Service (Amazon RDS) DB instances that 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.    After you identify the parameter that you want to change, 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 command for Lightsail Amazon RDS 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"

See the following example output for this operation:

{
  "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 command for Lightsail Amazon RDS PostgreSQL 10.10:

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

See the following example output for this operation:

{
    "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