Configure database parameters

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

For information about access to this release, see the access request page.

Select a documentation version: 18.1.0

• Current (18.1.0)
• 18.1.0

This page describes how to configure database parameters using the AlloyDB Omni orchestrator.

Database parameters, also known as Grand Unified Configuration (GUC) parameters, let you tune the performance and behavior of your AlloyDB Omni database cluster. For more information, see Setting parameters in the PostgreSQL documentation.

Before you begin

Before you configure database parameters, make sure that you have:

1. Installed the AlloyDB Omni orchestrator.
2. Provisioned and created your AlloyDB Omni cluster.

Configure database parameters

To configure database parameters, you use the parameters field within the primarySpec section of your DBCluster resource specification.

Note: You can't update database parameters using the AlloyDB Omni orchestrator after your cluster is created.

Caution: A manual update of database parameters requires a database restart on all nodes, which results in downtime.

Update the resource specification

Add the required parameters and their values to your DBCluster resource specification:

DBCluster:
  metadata:
    name: CLUSTER_NAME
  spec:
    primarySpec:
      # ... other settings ...
      parameters:
        max_wal_size: "MAX_WAL_SIZE"
        max_connections: "MAX_CONNECTIONS_LIMIT"

Replace the following:

• CLUSTER_NAME: name of your database cluster.
• MAX_WAL_SIZE: the maximum size the WAL can grow to between automatic WAL checkpoints, for example 2GB.
• MAX_CONNECTIONS_LIMIT: maximum number of concurrent connections allowed to the database server, for example, 200. Minimum supported value is 50.

Note: You can configure only a specific set of parameters during cluster creation using the orchestrator. For more information, see Supported parameters during cluster creation.

Supported parameters during cluster creation

You can configure the following database parameters (GUCs) in the DBCluster resource specification during cluster creation:

• log_connections
• log_disconnections
• log_hostname
• log_statement
• max_connections
• max_wal_size
• huge_pages
• tcp_keepalives_idle
• tcp_keepalives_interval
• tcp_keepalives_count

You cannot configure parameters, such as shared_buffers, that aren't included in this list using the DBCluster specification. Instead, you must update them manually after cluster creation.

Apply the configuration

Apply the changes using your preferred orchestrator interface.

Ansible

ansible-playbook -i DEPLOYMENT_SPECIFICATION DBCLUSTER_PLAYBOOK -e resource_spec=DBCLUSTER_SPECIFICATION

Replace the following variables:

• DEPLOYMENT_SPECIFICATION: path to the deployment specification you created in Install AlloyDB Omni components.

• DBCLUSTER_PLAYBOOK: path to the playbook that you created for your database cluster.

• DBCLUSTER_SPECIFICATION: path to your cluster specification.

alloydbctl

alloydbctl apply -d DEPLOYMENT_SPECIFICATION -r DBCLUSTER_SPECIFICATION

Replace the following variables:

• DEPLOYMENT_SPECIFICATION: path to the deployment specification you created in Install AlloyDB Omni components.

• DBCLUSTER_SPECIFICATION: path to your cluster specification.

Note: Some database parameters require a restart of the database service to take effect. The AlloyDB Omni cluster manager automatically handles these restarts when you apply the changes.

Update database parameters after cluster creation

To update parameters after the cluster is created, you must manually modify the configuration files on your database nodes.

Update parameters in a high-availability cluster

To manually update database parameters in a high-availability (HA) cluster, complete the following steps:

1. If availability.enableAutoFailover and availability.enableAutoHeal are set to true in your DBCluster specification file, then set them to false.

   DBCluster:
     metadata:
       name: my-db-cluster
     spec:
       databaseVersion: 18.1.0
       mode: ""
       availability:
         ...
         enableAutoFailover: false
         enableAutoHeal: false
         ...
   

   Apply the updated resource specification using your preferred interface.

2. On each database node, edit the 30startup.conf file in the following directory: DATA_DISK_PATH/18/db/postgresql.conf.d/.

   Replace DATA_DISK_PATHwith the value from the path field of the disk named DataDisk in your DBCluster resource specification.

   Example DBCluster specification:

   DBCluster:
     spec:
       primarySpec:
         resources:
           disks:
           - name: DataDisk
             path: /var/lib/alloydbomni/data  # This is your DATA_DISK_PATH
   

3. Add or update your database parameter configurations in the 30startup.conf file. For more information, see PostgreSQL documentation.

4. Restart the database service on all nodes:

   sudo systemctl restart alloydbomni18

5. If you modified the availability.enableAutoFailover and availability.enableAutoHeal fields in your DBCluster resource specification, revert them to their original values and apply the changes.

Update parameters in a standalone cluster

To manually update database parameters in a standalone cluster, complete the following steps:

1. On the database node, update the 30startup.conf file in the following directory: DATA_DISK_PATH/18/db/postgresql.conf.d/.

   Replace DATA_DISK_PATH with the file system path defined for your DataDisk in the DBCluster resource specification.

2. Add or update your database parameter configurations in the 30startup.conf file.

3. Restart the database service:

   sudo systemctl restart alloydbomni18

Verify parameter changes

After applying the configuration, you can verify that the parameters are updated using the SHOW command in psql:

SHOW PARAMETER_NAME;

Replace PARAMETER_NAME with the name of the parameter that you configured.