Configure database flags

MySQL | PostgreSQL | SQL Server

This page describes how to configure database flags for Cloud SQL, and lists the flags that you can set for your instance. You use database flags for many operations, including adjusting PostgreSQL parameters, adjusting options, and configuring and tuning an instance.

In some cases, setting one flag may require that you set another flag to fully enable the desired functionality.

When you set, remove, or modify a flag for a database instance, the database might be restarted. The flag value is then persisted for the instance until you remove it. If the instance is the source of a replica, and the instance is restarted, the replica is also restarted to align with the current configuration of the instance.

Configure database flags

Set a database flag

Console

In the Google Cloud console , select the project that contains the Cloud SQL instance for which you want to set a database flag.
Open the instance and click Edit .
Scroll down to the Flags section.
To set a flag that has not been set on the instance before, click Add item , choose the flag from the drop-down menu, and set its value.
Click Save to save your changes.
Confirm your changes under Flags on the Overview page.

gcloud

Edit the instance:

gcloud sql instances patch INSTANCE_NAME 
--database-flags= FLAG1 
= VALUE1 
, FLAG2 
= VALUE2

This command will overwrite all database flags previously set. To keep those and add new ones, include the values for all flags you want set on the instance; any flag not specifically included is set to its default value. For flags that do not take a value, specify the flag name followed by an equals sign ("=").

For example, to set the log_connections and log_min_error_statement flags, you can use the following command:

gcloud sql instances patch INSTANCE_NAME 
\
  --database-flags=log_connections=on,log_min_error_statement=error

Terraform

To add database flags, use a Terraform resource .

 resource "google_sql_database_instance" "instance" {
  name             = "postgres-instance"
  region           = "us-central1"
  database_version = "POSTGRES_14"
  settings {
    database_flags {
      name  = "log_connections"
      value = "on"
    }
    database_flags {
      name  = "log_min_error_statement"
      value = "error"
    }
    tier = "db-custom-2-7680"
  }
  # set `deletion_protection` to true, will ensure that one cannot accidentally delete this instance by
  # use of Terraform whereas `deletion_protection_enabled` flag protects this instance at the GCP level.
  deletion_protection = false
}

Apply the changes

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

Launch Cloud Shell .
Set the default Google Cloud project where you want to apply your Terraform configurations.

You only need to run this command once per project, and you can run it in any directory.
```
export GOOGLE_CLOUD_PROJECT= PROJECT_ID 
```
Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (also called a root module ).

In Cloud Shell , create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf . In this tutorial, the file is referred to as main.tf .
```
mkdir DIRECTORY 
&& cd DIRECTORY 
&& touch main.tf
```
If you are following a tutorial, you can copy the sample code in each section or step.

Copy the sample code into the newly created main.tf .

Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.
Review and modify the sample parameters to apply to your environment.
Save your changes.
Initialize Terraform. You only need to do this once per directory.
```
terraform init
```
Optionally, to use the latest Google provider version, include the -upgrade option:
```
terraform init -upgrade
```

Apply the changes

Review the configuration and verify that the resources that Terraform is going to create or update match your expectations:
```
terraform plan
```
Make corrections to the configuration as necessary.
Apply the Terraform configuration by running the following command and entering yes at the prompt:
```
terraform apply
```
Wait until Terraform displays the "Apply complete!" message.
Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.

Delete the changes

To delete your changes, do the following:

To disable deletion protection, in your Terraform configuration file set the deletion_protection argument to false .
```
deletion_protection =  "false"
```
Apply the updated Terraform configuration by running the following command and entering yes at the prompt:
```
terraform apply
```

Remove resources previously applied with your Terraform configuration by running the following command and entering yes at the prompt:
```
terraform destroy
```

REST v1

To set a flag for an existing database:

Before using any of the request data, make the following replacements:

project-id : The project ID
instance-id : The instance ID

HTTP method and URL:

PATCH https://sqladmin.googleapis.com/v1/projects/ project-id 
/instances/ instance-id

Request JSON body:

{
  "settings":
  {
    "databaseFlags":
    [
      {
        "name": " flag_name 
",
        "value": " flag_value 
"
      }
    ]
  }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login , or by using Cloud Shell , which automatically logs you into the gcloud CLI . You can check the currently active account by running gcloud auth list .

Save the request body in a file named request.json , and execute the following command:

curl -X PATCH \
-H "Authorization: Bearer $(gcloud auth print-access-token)" \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
"https://sqladmin.googleapis.com/v1/projects/ project-id 
/instances/ instance-id 
"

PowerShell (Windows)

Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login . You can check the currently active account by running gcloud auth list .

Save the request body in a file named request.json , and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method PATCH `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "https://sqladmin.googleapis.com/v1/projects/ project-id 
/instances/ instance-id 
" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/v1/projects/ project-id 
/instances/ instance-id 
",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "UPDATE",
  "name": " operation-id 
",
  "targetId": " instance-id 
",
  "selfLink": "https://sqladmin.googleapis.com/v1/projects/ project-id 
/operations/ operation-id 
",
  "targetProject": " project-id 
"
}

If there are existing flags configured for the database, modify the previous command to include them. The PATCH command overwrites the existing flags with the ones specified in the request.

REST v1beta4

To set a flag for an existing database:

Before using any of the request data, make the following replacements:

project-id : The project ID
instance-id : The instance ID

HTTP method and URL:

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/instances/ instance-id

Request JSON body:

{
  "settings":
  {
    "databaseFlags":
    [
      {
        "name": " flag_name 
",
        "value": " flag_value 
"
      }
    ]
  }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Save the request body in a file named request.json , and execute the following command:

curl -X PATCH \
-H "Authorization: Bearer $(gcloud auth print-access-token)" \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
"https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/instances/ instance-id 
"

PowerShell (Windows)

Save the request body in a file named request.json , and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method PATCH `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/instances/ instance-id 
" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/instances/ instance-id 
",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "UPDATE",
  "name": " operation-id 
",
  "targetId": " instance-id 
",
  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/operations/ operation-id 
",
  "targetProject": " project-id 
"
}

If there are existing flags configured for the database, modify the previous command to include them. The PATCH command overwrites the existing flags with the ones specified in the request.

Clear all flags to their default values

Console

In the Google Cloud console , select the project that contains the Cloud SQL instance for which you want to clear all flags.
Open the instance and click Edit .
Open the Database flags section.
Click the X next to all of the flags shown.
Click Save to save your changes.

gcloud

Clear all flags to their default values on an instance:

gcloud sql instances patch INSTANCE_NAME 
\
--clear-database-flags

You are prompted to confirm that the instance will be restarted.

REST v1

To clear all flags for an existing instance:

Before using any of the request data, make the following replacements:

project-id : The project ID
instance-id : The instance ID

HTTP method and URL:

PATCH https://sqladmin.googleapis.com/v1/projects/ project-id 
/instances/ instance-id

Request JSON body:

{
  "settings":
  {
    "databaseFlags": []
  }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Save the request body in a file named request.json , and execute the following command:

curl -X PATCH \
-H "Authorization: Bearer $(gcloud auth print-access-token)" \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
"https://sqladmin.googleapis.com/v1/projects/ project-id 
/instances/ instance-id 
"

PowerShell (Windows)

Save the request body in a file named request.json , and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method PATCH `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "https://sqladmin.googleapis.com/v1/projects/ project-id 
/instances/ instance-id 
" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/v1/projects/ project-id 
/instances/ instance-id 
",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "UPDATE",
  "name": " operation-id 
",
  "targetId": " instance-id 
",
  "selfLink": "https://sqladmin.googleapis.com/v1/projects/ project-id 
/operations/ operation-id 
",
  "targetProject": " project-id 
"
}

REST v1beta4

To clear all flags for an existing instance:

Before using any of the request data, make the following replacements:

project-id : The project ID
instance-id : The instance ID

HTTP method and URL:

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/instances/ instance-id

Request JSON body:

{
  "settings":
  {
    "databaseFlags": []
  }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Save the request body in a file named request.json , and execute the following command:

curl -X PATCH \
-H "Authorization: Bearer $(gcloud auth print-access-token)" \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
"https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/instances/ instance-id 
"

PowerShell (Windows)

Save the request body in a file named request.json , and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method PATCH `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/instances/ instance-id 
" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/instances/ instance-id 
",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "UPDATE",
  "name": " operation-id 
",
  "targetId": " instance-id 
",
  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/operations/ operation-id 
",
  "targetProject": " project-id 
"
}

View current values of database flags

To view all current values of the PostgreSQL settings, log into your instance with the psql client and enter the following statement:

 SELECT name, setting FROM pg_settings;

Note that you can change the value only for supported flags (as listed below).

Determine which database flags have been set for an instance

To see which flags have been set for a Cloud SQL instance:

Console

In the Google Cloud console , select the project that contains the Cloud SQL instance for which you want to see the database flags that have been set.
Select the instance to open its Instance Overview page.
The database flags that have been set are listed under the Database flags section.

gcloud

Get the instance state:

gcloud sql instances describe INSTANCE_NAME

In the output, database flags are listed under the settings as the collection databaseFlags . For more information about the representation of the flags in the output, see Instances Resource Representation .

REST v1

To list flags configured for an instance:

Before using any of the request data, make the following replacements:

project-id : The project ID
instance-id : The instance ID

HTTP method and URL:

GET https://sqladmin.googleapis.com/v1/projects/ project-id 
/instances/ instance-id

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Execute the following command:

curl -X GET \
-H "Authorization: Bearer $(gcloud auth print-access-token)" \
"https://sqladmin.googleapis.com/v1/projects/ project-id 
/instances/ instance-id 
"

PowerShell (Windows)

Execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method GET `
-Headers $headers `
-Uri "https://sqladmin.googleapis.com/v1/projects/ project-id 
/instances/ instance-id 
" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "settings":
  {
    "authorizedGaeApplications": [],
    "tier": " machine-type 
",
    "kind": "sql#settings",
    "availabilityType": "REGIONAL",
    "pricingPlan": "PER_USE",
    "replicationType": "SYNCHRONOUS",
    "activationPolicy": "ALWAYS",
    "ipConfiguration":
    {
      "privateNetwork": "projects/ project-id 
/global/networks/default",
      "authorizedNetworks": [],
      "ipv4Enabled": false
    },
    "locationPreference":
    {
      "zone": " zone 
",
      "kind": "sql#locationPreference"
    },
    "databaseFlags": [
      {
        "name": "general_log",
        "value": "on"
      }
    ],
    "dataDiskType": "PD_SSD",
    "maintenanceWindow":
    {
      "kind": "sql#maintenanceWindow",
      "hour": 0,
      "day": 0
    },
    "backupConfiguration":
    {
      "startTime": "03:00",
      "kind": "sql#backupConfiguration",
      "enabled": true,
      "binaryLogEnabled": true
    },
    "settingsVersion": "54",
    "storageAutoResizeLimit": "0",
    "storageAutoResize": true,
    "dataDiskSizeGb": "10"
  }
}

In the output, look for the databaseFlags field.

REST v1beta4

To list flags configured for an instance:

Before using any of the request data, make the following replacements:

project-id : The project ID
instance-id : The instance ID

HTTP method and URL:

GET https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/instances/ instance-id

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Execute the following command:

curl -X GET \
-H "Authorization: Bearer $(gcloud auth print-access-token)" \
"https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/instances/ instance-id 
"

PowerShell (Windows)

Execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method GET `
-Headers $headers `
-Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/instances/ instance-id 
" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "settings":
  {
    "authorizedGaeApplications": [],
    "tier": " machine-type 
",
    "kind": "sql#settings",
    "availabilityType": "REGIONAL",
    "pricingPlan": "PER_USE",
    "replicationType": "SYNCHRONOUS",
    "activationPolicy": "ALWAYS",
    "ipConfiguration":
    {
      "privateNetwork": "projects/ project-id 
/global/networks/default",
      "authorizedNetworks": [],
      "ipv4Enabled": false
    },
    "locationPreference":
    {
      "zone": " zone 
",
      "kind": "sql#locationPreference"
    },
    "databaseFlags": [
      {
        "name": "general_log",
        "value": "on"
      }
    ],
    "dataDiskType": "PD_SSD",
    "maintenanceWindow":
    {
      "kind": "sql#maintenanceWindow",
      "hour": 0,
      "day": 0
    },
    "backupConfiguration":
    {
      "startTime": "03:00",
      "kind": "sql#backupConfiguration",
      "enabled": true,
      "binaryLogEnabled": true
    },
    "settingsVersion": "54",
    "storageAutoResizeLimit": "0",
    "storageAutoResize": true,
    "dataDiskSizeGb": "10"
  }
}

In the output, look for the databaseFlags field.

Supported flags

Flags not mentioned below are not supported.

For a given flag, Cloud SQL might support a different value or range from the corresponding PostgreSQL parameter or option.

A | C | D | E | F | G | H | I | J | L | M | O | P | R | S | T | V | W

Cloud SQL Flag

Type
Acceptable Values and Notes

Restart
Required?

anon.algorithm

String
The default is sha256.

anon.maskschema

String
The default is mask.

anon.restrict_to_trusted_schemas

boolean
on | off
The default is off.

anon.salt

String
There's no default value.

anon.sourceshema

String
The default is public.

auto_explain.log_analyze

boolean
on | off
The default is off.

auto_explain.log_buffers

boolean
on | off
The default is off.

auto_explain.log_min_duration

Integer
-1 ... 2147483647
The default is -1.

auto_explain.log_format

String
text|xml|json|yaml
The default is text.

auto_explain.log_level

auto_explain.log_nested_statements

boolean
on | off
The default is off.

auto_explain.log_settings

boolean
on | off
The default is off.
Supported in PostgreSQL 12 and above.

auto_explain.log_timing

boolean
on | off
The default is on.

auto_explain.log_triggers

boolean
on | off
The default is off.

auto_explain.log_wal

boolean
on | off
The default is off.
Supported in PostgreSQL 13 and above.

auto_explain.log_verbose

boolean
on | off
The default is off.

auto_explain.sample_rate

Float
0 ... 1
The default is 1.

autovacuum

boolean
on | off
The default is on.
For usage information that might affect the SLA, see the Operational guidelines for Cloud SQL for PostgreSQL instances .

autovacuum_analyze_scale_factor

float
0 ... 100
The default is 0.1.

autovacuum_analyze_threshold

integer
0 ... 2147483647
The default is 50.

autovacuum_freeze_max_age

integer
100000 ... 2000000000
The default is 200000000.
For usage information that might affect the SLA, see the Operational guidelines for Cloud SQL for PostgreSQL instances .

Yes

autovacuum_max_workers

integer
1 ... varies (see note)
The default is 3.

Note: To determine the maximum value that you can set for this flag, you must first calculate the number of backend connections that are already in use. This calculation is the sum of the values for max_connections (maximum number of client connections), autovacuum_max_workers (maximum number of autovacuum processes), and max_worker_processes . The sum cannot exceed 262142 .

Yes

autovacuum_multixact_freeze_max_age

integer
10000 ... 2000000000
The default is 400000000.
For usage information that might affect the SLA, see the Operational guidelines for Cloud SQL for PostgreSQL instances .

Yes

autovacuum_naptime

integer
1 ... 2147483 s
The default is 60s.

autovacuum_vacuum_cost_delay

integer
0 ... 100 ms, or -1 to use the vacuum_cost_delay value
For PostgreSQL 9.6, 10, and 11, the default value is 2 ms.

autovacuum_vacuum_cost_limit

integer
0 ... 10000 , or -1 to use the vacuum_cost_limit value
The default is -1.

autovacuum_vacuum_insert_scale_factor

float
0 ... 100
The default is 0.2.

autovacuum_vacuum_insert_threshold

integer
-1 ... 2147483647
The default is 1000.

autovacuum_vacuum_scale_factor

float
0 ... 100
The default is 0.2.

autovacuum_vacuum_threshold

integer
0 ... 2147483647
The default is 50.

autovacuum_work_mem

integer
0 ... 2147483647 KB, or -1 to use the maintenance_work_mem value
The default is -1.

checkpoint_completion_target

float
0.0 ... 1.0
The default is 0.9.

checkpoint_timeout

integer
30 ... 86,400 s
The default is 300s.
For usage information that might affect the SLA, see the Operational guidelines for Cloud SQL for PostgreSQL instances .

checkpoint_warning

integer
0 ... 2147483647 s
The default is 30s.

cloudsql.allow_passwordless_local_connections

boolean
on | off
The default is off .

cloudsql.enable_anon

boolean
on | off
The default is off .

cloudsql.enable_auto_explain

boolean
on | off
The default is off .

Yes

cloudsql.enable_maintenance_mode

boolean
on | off
The default is off .

Yes

cloudsql.enable_pgaudit

boolean
on | off
The default is off.

Yes

cloudsql.enable_pg_bigm

boolean
on | off
default: off

Yes

cloudsql.enable_pg_cron

boolean
on | off
The default is off .
Supported in PostgreSQL 10 and above.

Yes

cloudsql.enable_pg_hint_plan

boolean
on | off
The default is off .

Yes

cloudsql.enable_pglogical

boolean
on | off
The default is off .

Yes

cloudsql.enable_pg_squeeze

boolean
on | off
The default is off .

Yes

cloudsql.enable_pg_wait_sampling

boolean
on | off

Yes

cloudsql.iam_authentication

boolean
on | off
The default is off .

cloudsql.logical_decoding

boolean
on | off
The default is off .

Yes

cloudsql.max_failed_attempts_user

integer
0 ... 10000
The default is 10.

Yes

cloudsql.pg_authid_select_role

string

commit_delay

integer
0 ... 100000
The default is 0.

commit_siblings

integer
0 ... 1000
The default is 5.

constraint_exclusion

enumeration
partition | on | off
The default is partition.

cpu_index_tuple_cost

float
0.0 ... inf
The default is 0.005.

cpu_operator_cost

float
0.0 ... inf
The default is 0.0025.

cpu_tuple_cost

float
0.0 ... inf
The default is 0.01.

cron.database_name

String .
Supported in PostgreSQL 10 and above.

Yes

cron.log_statement

boolean
on | off
The default is on.
Supported in PostgreSQL 10 and above.

Yes

cron.log_run

boolean
on | off
The default is on.
Supported in PostgreSQL 10 and above.

Yes

cron.max_running_jobs

Integer
0 ... varies
The default is 5.
Supported in PostgreSQL 10 and above.

Yes

cron.log_min_messages

cursor_tuple_fraction

float
0.0 ... 1.0
The default is 0.1.

deadlock_timeout

integer
1 ... 2147483647 ms
The default is 1000ms.

default_statistics_target

integer
1 ... 10000
The default is 100.

default_tablespace

string

default_transaction_deferrable

boolean
on | off
The default is off.

default_transaction_isolation

enumeration
serializable | 'repeatable read' | 'read committed' | 'read uncommitted'
The default is 'read committed'.

effective_cache_size

integer
The size range is from 10% - 70% of the instance's memory.
Unit is 8 KB.
The default is 40% of VM memory. For example, for a 45GB instance memory, the default value is 18537160 KB.

effective_io_concurrency

integer
0 ... 1000
The default is 1.

enable_bitmapscan

boolean
on | off
The default is on.

enable_hashagg

boolean
on | off
The default is on.

enable_hashjoin

boolean
on | off
The default is on.

enable_indexonlyscan

boolean
on | off
The default is on.

enable_indexscan

boolean
on | off
The default is on.

enable_material

boolean
on | off
The default is on

enable_mergejoin

boolean
on | off
The default is on.

enable_nestloop

boolean
on | off
The default is on.

enable_seqscan

boolean
on | off
The default is on.

enable_sort

boolean
on | off
The default is on.

enable_tidscan

boolean
on | off
The default is on.

force_parallel_mode

enumeration
off | on | regress
The default is off.

from_collapse_limit

integer
1 ... 2147483647
The default is 8.

geqo

boolean
on | off
The default is on.

geqo_effort

integer
1 ... 10
The default is 5.

geqo_generations

integer
0 ... 2147483647
The default is 0.

geqo_pool_size

integer
0 ... 2147483647
The default is 0.

geqo_seed

float
0.0 ... 1.0
The default is 0.

geqo_selection_bias

float
1.5 ... 2.0
The default is 2.

geqo_threshold

integer
2 ... 2147483647
The default is 12.

gin_fuzzy_search_limit

integer
0 ... 2147483647
The default is 0.

gin_pending_list_limit

integer
64 ... 2147483647 KB
The default is 4096KB.

hash_mem_multiplier

float
1 ... 1000
The default is 2.

hot_standby_feedback

boolean
on | off
The default is off.

huge_pages

enumeration
try | off
The default is try.

Yes

idle_in_transaction_session_timeout

integer
0 ... 2147483647 ms
The default is 0.

ivfflat.probes

integer
1 ... varies
The default is 1.
Supported in PostgreSQL 11 and above.

join_collapse_limit

integer
1 ... 2147483647
The default is 8.

lock_timeout

integer
0 ... 2147483647 ms
The default is 0.

log_autovacuum_min_duration

integer
0 ... 2147483647 ms, or -1 to disable
The default is 0.

log_checkpoints

boolean
on | off
The default is off.

log_connections

boolean
on | off
The default is off.

log_disconnections

boolean
on | off
The default is off.

log_duration

boolean
on | off
The default is off.

log_error_verbosity

enumeration
terse | default | verbose
The default is default.

log_executor_stats

boolean
on | off
The default is off.

log_hostname

boolean
on | off
The default is off.

log_line_prefix

String
A printf-style string that's generated at the beginning of each line of a log file.
The default is %m [%p]: [%l-1] db=%d,user=%u , which logs timestamp, process ID, database, and username.

log_lock_waits

boolean
on | off
The default is off.

log_min_duration_statement

integer
-1 ... 2147483647 ms
The default is -1.

log_min_error_statement

log_min_messages

log_parser_stats

boolean
on | off
The default is off.

log_planner_stats

boolean
on | off
The default is off.

log_replication_commands

boolean
on | off
The default is off.

log_statement

enumeration
none | ddl | mod | all
Set to mod to log all Data definition language (DDL) statements, plus data-modifying statements such as INSERT , UPDATE , DELETE , TRUNCATE
The default is none.

log_statement_stats

boolean
on | off
May not be enabled together with log_parser_stats , log_planner_stats , or log_executor_stats .
The default is off.

log_temp_files

integer
0 ... 2147483647 KB, or -1 to disable
The default is 0.

log_timezone

string
This flag provides Cloud SQL for PostgreSQL users with a way to set the time zone used for timestamps that are written in the server log.

You specify time zones by name. For example, Europe/London is the timezone name for London.

You must update this flag manually, on the primary instance and on all read replicas, to account for it.

Timezone names are case insensitive. You can supply the timezone name in any case.

We support UTC+X as a valid format for this flag, where X is +/-HH:MM .

Yes

logical_decoding_work_mem

integer
64 ... 2147483647
The default is 65536.

maintenance_io_concurrency

integer
0 ... 1000
The default is 10.

maintenance_work_mem

integer
1024 ... 2147483647 KB
The default is 64MB.

max_connections

integer
14 ... varies (see note)
The default value depends on the amount of memory of the largest instance in the chain of primaries (this instance; and, if it's a read replica, its primary, its primary's primary, etc. all the way up to the root of the replication tree).

Memory (GB) on largest instance	Default value
tiny (~0.5)	25
small (~1.7)	50
from 3.75 to < 6	100
from 6 to < 7.5	200
from 7.5 to < 15	400
from 15 to < 30	500
from 30 to < 60	600
from 60 to < 120	800
>=120	1,000

The value on replicas must be greater than or equal to the value on the primary. Changes on the primary propagate to replicas that have a value that is lower than the new value on the primary, or that have not been changed from the default value. Such changes on the primary cause the replica to restart.

Yes

max_locks_per_transaction

integer
10 ... 2,147,483,647
The default is 64.

Yes

max_logical_replication_workers

integer
4 ... 8192
The default is 4.
Supported in PostgreSQL 10 and above.

Yes

max_parallel_maintenance_workers

integer
0 ... varies
The default is 2.
Supported in PostgreSQL 11 and above.

The value on replicas must be greater than or equal to the value on the primary. Changes on the primary propagate to replicas that have a value that's lower than the new value on the primary, or that haven't been changed from the default value.

If the value on the primary is default , then the value for the replicas can't be changed. To change the value for the replicas, first, set the value on the primary to an integer.

max_parallel_workers

integer
0 ... varies
The default is 8.
Supported in PostgreSQL 10 and above.

If the value on the primary is default , then the value for the replicas can't be changed. To change the value for the replicas, first, set the value on the primary to an integer.

max_parallel_workers_per_gather

integer
0 ... varies
The default is 2.

If the value on the primary is default , then the value for the replicas can't be changed. To change the value for the replicas, first, set the value on the primary to an integer.

max_pred_locks_per_page

integer
0 ... 2147483647
The default is 2.
Supported in PostgreSQL 10 and above.

Yes

max_pred_locks_per_relation

integer
-2147483648 ... 2147483647
The default is -2.
Supported in PostgreSQL 10 and above.

Yes

max_pred_locks_per_transaction

integer
64 ... 1048576

Note: The maximum value of this setting scales linearly with the memory available on your instance.

Memory size (GB)	Maximum value
0 - 1	512
1 - 2	1024
2 - 4	2048
4 - 8	4096
8 - 16	8192
16 - 32	16384
32 - 64	32768
64 - 128	65536
128 - 256	131072
256 - 512	262144
512 - 1024	524288
> 1024	1048576

Yes

max_prepared_transactions

integer
0 ... varies

Memory size (MB)	Max value
0 - 3,840	30,000
3,840 - 7,680	85,000
7,680 - 15,360	200,000
15,360 and above	262,000

Yes

max_replication_slots

integer
10 ... varies

Note: The maximum value of this setting scales linearly with the memory available on your instance.

Memory size (GB)	Max value
0 - 4	10
4 - 16	32
16 - 32	128
32 - 64	256
64 -128	512
128 - 256	1024
256 - 512	2048
> 512	4096

For more information about this flag, see Setting up logical replication and decoding .

The default is 10.

Yes

max_standby_archive_delay

integer
0 ... 2147483647 ms, or -1 to wait forever

max_standby_streaming_delay

integer
0 ... 2147483647 ms, or -1 to wait forever

max_sync_workers_per_subscription

integer
2 ... 64
Cannot be greater than max_logical_replication_workers .

max_wal_senders

integer
10 ... varies
The default is 10.

Note: The maximum value of this setting scales linearly with the memory available on your instance. This value must be set to a value greater than or equal to the value of max_replication_slots .

Memory size (GB)	Max value
0 - 4	10
4 - 16	32
16 - 32	128
32 - 64	256
64 -128	512
128 - 256	1024
256 - 512	2048
> 512	4096

For more information about this flag, see Setting up logical replication and decoding .

Yes

max_wal_size

integer
2 ... 2147483647

The unit is 16 MB (the WAL file size) for version 9.6 and 1 MB for PostgreSQL 10 and above.

The default value is 1504 MB if instance memory more than or equal to 3.75 GB. The default value is 1 GB if instance memory is less than 3.75 GB.

For usage information that might affect the SLA, see the Operational guidelines for Cloud SQL for PostgreSQL instances .

max_worker_processes

integer
8 ... varies

Note: The maximum value of this setting scales linearly with the memory available on your instance.

Memory size (GB)	Max value
from 0 to < 8	10
from 8 to < 16	16
from 16 to < 32	32
from 32 to < 64	64
from 64 to < 128	128
from 128 to < 256	256
from 256 to < 512	512
> 512	1024

For more information about this flag, see Setting up logical replication and decoding .

Yes

min_parallel_relation_size

integer
0 ... 715827882
Unit is 8 KB
Supported only in PostgreSQL 9.6.

min_wal_size

integer
32 ... 2147483647

The unit is 16 MB (the WAL file size) for version 9.6 and 1 MB for PostgreSQL 10 and above.

old_snapshot_threshold

integer
0 ... 86400 min, or -1 to disable
The default is -1.

Yes

parallel_setup_cost

float
0.0 ... inf
The default is 1000.

parallel_tuple_cost

float
0.0 ... inf
The default is 0.1.

password_encryption

enumeration
md5 | scram-sha-256

The default depends on the PostgreSQL version. For PostgreSQL versions 10 to 13, the default is md5 . For PostgreSQL 14 and higher, the default is scram-sha-256 .

pg_bigm.enable_recheck

boolean
on | off

pg_bigm.gin_key_limit

integer
0 ... 2147483647

pg_bigm.similarity_limit

float
0.0 ... 1.0

pg_hint_plan.enable_hint

boolean
on | off
The default is on.

pg_hint_plan.debug_print

String
off|on|detailed|verbose|0|1||2|3|no|yes|false|true
The default is off.

pg_hint_plan.parse_messages

pg_hint_plan.message_level

pg_hint_plan.enable_hint_table

boolean
on | off
The default is off.

pglogical.batch_inserts

boolean
on | off
The default is off .

Yes

pglogical.conflict_log_level

String
The default is LOG .
This flag accepts the same values as log_min_messages .

pglogical.conflict_resolution

String
error|apply_remote|keep_local|last_update_wins|first_update_wins The default is apply_remote .

Note: The values keep_local , last_update_wins and first_update_wins requires track_commit_timestamps to be enabled on both nodes. For information about the feature that uses this flag, see Setting up logical replication and decoding .

pglogical.extra_connection_options

String
Accepts PostgreSQL keyword/value connection strings .
The default is the empty string.

pglogical.synchronous_commit

boolean
on | off
The default is on .

Yes

pglogical.use_spi

boolean
on | off
The default is off .

Yes

pg_stat_statements.max

integer
100 ... 2147483647
The default is 5000.

Yes

pg_stat_statements.save

boolean
on | off
The default is on.

pg_stat_statements.track

enumeration
none | top | all
The default is top.

pg_stat_statements.track_utility

boolean
on | off
The default is on.

pgaudit.log

You can provide multiple classes using a comma-separated list, and subtract a class by prefacing the class with a - sign. The default is none .

pgaudit.log_catalog

boolean
on | off
The default is on .

pgaudit.log_client

boolean
on | off
The default is off .

pgaudit.log_level

pgaudit.log_parameter

boolean
on | off
The default is off .

pgaudit.log_relation

boolean
on | off
The default is off .

pgaudit.log_statement_once

boolean
on | off
The default is off .

pgaudit.role

string
There is no default.

pgtt.enabled

boolean
on | off
The default is on.

pg_wait_sampling.history_period

integer
1 ... 2147483647

pg_wait_sampling.history_size

integer
1 ... 2147483647

pg_wait_sampling.profile_period

integer
1 ... 2147483647

pg_wait_sampling.profile_pid

boolean
on | off

pg_wait_sampling.profile_queries

boolean
on | off

random_page_cost

float
0.0 ... inf
The default is 4.

rdkit.agent_FP_bit_ratio

float
0 ... 3

rdkit.avalon_fp_size

integer
64 ... 9192

rdkit.dice_threshold

float
0 ... 1

rdkit.difference_FP_weight_agents

integer
-10 ... 10

rdkit.difference_FP_weight_nonagents

integer
1 ... 20

rdkit.do_chiral_sss

boolean
on | off

rdkit.do_enhanced_stereo_sss

boolean
on | off

rdkit.featmorgan_fp_size

integer
64 ... 9192

rdkit.hashed_atompair_fp_size

integer
64 ... 9192

rdkit.hashed_torsion_fp_size

integer
64 ... 9192

rdkit.ignore_reaction_agents

boolean
on | off

rdkit.init_reaction

boolean
on | off

rdkit.layered_fp_size

integer
64 ... 9192

rdkit.morgan_fp_size

integer
64 ... 9192

rdkit.move_unmmapped_reactants_to_agents

boolean
on | off

rdkit.rdkit_fp_size

integer
64 ... 9192

rdkit.reaction_difference_fp_size

integer
64 ... 9192

rdkit.reaction_difference_fp_type

integer
1 ... 3

rdkit.reaction_sss_fp_size

integer
64 ... 9192

rdkit.reaction_sss_fp_type

integer
1 ... 5

rdkit.sss_fp_size

integer
64 ... 4096

rdkit.tanimoto_threshold

float
0 ... 1

rdkit.threshold_unmapped_reactant_atoms

float
0 ... 1

replacement_sort_tuples

integer
0 ... 2147483647

session_replication_role

enumeration
origin | replica | local
Can be set only in current session

shared_buffers

integer
The size range is from 10% - 60% of the instance's memory.
Unit is 8 KB.
The default is one-third of the instance's total VM memory (in MB). For example, for 45 GB of instance memory, the default value is 15085 MB.

Yes

squeeze.max_xlock_time

integer
1 ... 2147483647

squeeze.worker_autostart

string

Yes

squeeze.worker_role

string

Yes

ssl_max_protocol_version

enumeration
Postgres 14: Sets the maximum SSL/TLS protocol version to use. Valid values are as for ssl_min_protocol_version , with the addition of an empty string, which allows any protocol version to be specified.
Supported in PostgreSQL 12 and above.

ssl_min_protocol_version

enumeration
Postgres 14: Sets the minimum SSL/TLS protocol version to use. Valid values are currently: TLSv1 , TLSv1.1 , TLSv1.2 , TLSv1.3 .
The default is TLSv1 .
Supported in PostgreSQL 12 and above.

standard_conforming_strings

boolean
on | off
The default is on.

synchronize_seqscans

boolean
on | off
The default is on.

tcp_keepalives_count

integer
0 ... 2147483647
The default is 5.

tcp_keepalives_idle

integer
0 ... 2147483647
The default is 60.

tcp_keepalives_interval

integer
0 ... 2147483647
The default is 60.

temp_buffers

integer
100 ... 1,073,741,823
Unit is 8 KB

temp_file_limit

integer
1048576 ... 2147483647 KB
The default is 10% of the initial disk size. For example, for a 100-GB disk, the default value is 10262623 KB.

TimeZone

string
This flag provides Cloud SQL for PostgreSQL users with a way to set the time zone for displaying and analyzing timestamps.

You specify time zones by name. For example, Europe/London is the timezone name for London.

You must update this flag manually, on the primary instance and on all read replicas, to account for it.

Timezone names are case insensitive. You can supply the timezone name in any case.

We support UTC+X as a valid format for this flag, where X is +/-HH .

Yes

trace_notify

boolean
on | off
The default is off.

trace_recovery_messages

trace_sort

boolean
on | off
The default is off.

track_activities

boolean
on | off
The default is on.

track_activity_query_size

integer
100 ... 102400
The default is 1KB.

Yes

track_commit_timestamp

boolean
on | off
The default is off.

Yes

track_counts

boolean
on | off
The default is on.

track_functions

enumeration
none | pl | all
The default is none.

track_io_timing

boolean
on | off
The default is off.

vacuum_cost_delay

integer
0 ... 100 ms
The default is 0.

vacuum_cost_limit

integer
1 ... 10000
The default is 200.

vacuum_defer_cleanup_age

integer
0 ... 1000000
The default is 0.

vacuum_failsafe_age

integer
0 ... 2100000000
The default is 1600000000.

vacuum_freeze_min_age

integer
0 ... 1000000000
The default is 50000000.

vacuum_freeze_table_age

integer
0 ... 2000000000
The default is 150000000.

vacuum_multixact_failsafe_age

integer
0 ... 2100000000
The default is 1600000000.

vacuum_multixact_freeze_min_age

integer
0 ... 1000000000
The default is 5000000.

vacuum_multixact_freeze_table_age

integer
0 ... 2000000000
The default is 150000000.

wal_buffers

integer
The size range is from -1 - 5% of the instance's memory.
Unit is 8 KB.
The default is 16MB.

Yes

wal_compression

enumeration
off | on | pglz | lz4 | zstd
The default is off.

Note: The pglz , lz4 , and zstd values are supported only for PostgreSQL versions 15 and later.

wal_receiver_timeout

integer
0 ... 2147483647
The default is 60 seconds.

This flag affects the WAL sender and receiver. If not appropriately set, this flag affects logical and physical replication. This flag also affects replication performance and latency. A value of zero disables the timeout mechanism. The unit is milliseconds.

wal_sender_timeout

integer
0 ... 2147483647
The default is 60 seconds.

work_mem

integer
64 ... 2147483647 KB
The default is 4MB.

Special flags

`bgwriter`

PostgreSQL has a background writer ( bgwriter ) flag. This flag issues writes of new or modified shared buffers. These shared buffers are known as dirty buffers. When the number of clean shared buffers is insufficient, the background writer writes dirty buffers to the file system and marks them as clean.

Two flags associated with the bgwriter flag are bgwriter_delay and bgwriter_lru_maxpages . bgwriter_delay specifies the delay between activity rounds for the background writer in milliseconds (ms), and bgwriter_lru_maxpages specifies how many buffers will be written by the background writer.

The default value for the bgwriter flag is 200 ms . However, if you select a solid state drive (SSD) that's greater than 500 GB, then the value of the bgwriter_delay flag is set to 50 , and the value of the bgwriter_lru_maxpages flag is set to 200 .

For more information about the background writer , see the PostgreSQL documentation.

`session_replication_role`

PostgreSQL has the session_replication_role flag, which is designed to be used for logical replication and lets you disable constraint triggers in individual sessions.

Sometimes this flag can also be used for some maintenance operations to circumvent constraint (most often Foreign Key) checks.

This flag can be set in a session by any user which has the REPLICATION property set. The REPLICATION property for any user can be set by cloudsqlsuperuser when one of flags cloudsql.enable_pglogical or cloudsql.logical_decoding is set for the instance.

This flag can not be set for the whole instance.

Troubleshooting

Issue Troubleshooting

You set the time zone for a session, but it expires when you log off.

Issue	Troubleshooting
You set the time zone for a session, but it expires when you log off.	Connect to the database and set the database time zone to the one you want, either per user or per database. In Cloud SQL for PostgreSQL, you can specify the following. These settings remain after a session is closed, mimicking a `.conf` configuration: ALTER DATABASE `dbname` SET TIMEZONE TO ' `timezone` '; ALTER USER `username` SET TIMEZONE TO ' `timezone` '; These settings apply only to new connections to the database. To see the change to the time zone, disconnect from the instance and then reconnect to it.

Connect to the database and set the database time zone to the one you want, either per user or per database.

In Cloud SQL for PostgreSQL, you can specify the following. These settings remain after a session is closed, mimicking a .conf configuration:

ALTER DATABASE dbname 
SET TIMEZONE TO ' timezone 
';
ALTER USER username 
SET TIMEZONE TO ' timezone 
';

These settings apply only to new connections to the database. To see the change to the time zone, disconnect from the instance and then reconnect to it.

What's next

Learn more about PostgreSQL server configuration .
Learn more about Operational Guidelines .