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

Last updated: 2020-02-21

How can I update database parameters in an Amazon 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

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
        }
    ]
}

Did this article help you?

Anything we could improve?


Need more help?