Configure database flags

MySQL | PostgreSQL | SQL Server

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

In some cases, setting one flag may require that you set another flag tofully enable the functionality you want to use.

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

Configure database flags

The following sections cover common flag management tasks.

Set a database flag

Console

In theGoogle Cloud console,select the project that contains the Cloud SQL instance for which you want to set a database flag.
Open the instance and clickEdit.
Go to theFlags section.
To set a flag that has not been set on the instance before, clickAdd item, choose the flag from the drop-down menu, and set its value.
ClickSave to save your changes.
Confirm your changes underFlags on the Overview page.

gcloud

Edit the instance:

gcloudsqlinstancespatchINSTANCE_NAME--database-flags=FLAG1=VALUE1,FLAG2=VALUE2

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

For example, to set thelog_connections andlog_min_error_statement flags, youcan use the following command:

gcloudsqlinstancespatchINSTANCE_NAME\--database-flags=log_connections=on,log_min_error_statement=error

Terraform

To add database flags, use aTerraform 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

LaunchCloud 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 (alsocalled aroot module).

InCloud Shell, create a directory and a new file within that directory. The filename must have the.tf extension—for examplemain.tf. In this tutorial, the file is referred to asmain.tf.
```
mkdirDIRECTORY && cdDIRECTORY && 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 createdmain.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 enteringyes 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.

Note: Terraform samples typically assume that the required APIs are enabled in your Google Cloud project.

Delete the changes

To delete your changes, do the following:

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

Remove resources previously applied with your Terraform configuration by running the following command and enteringyes 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 thegcloud CLI with your user account by running gcloud init orgcloud auth login , or by usingCloud Shell, which automatically logs you into thegcloud CLI . You can check the currently active account by runninggcloud auth list.

Save the request body in a file namedrequest.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 thegcloud CLI with your user account by running gcloud init orgcloud auth login . You can check the currently active account by runninggcloud auth list.

Save the request body in a file namedrequest.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 previouscommand to include them. ThePATCH command overwrites the existingflags 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 namedrequest.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 namedrequest.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 previouscommand to include them. ThePATCH command overwrites the existingflags with the ones specified in the request.

Clear all flags to their default values

Console

In theGoogle Cloud console,select the project that contains the Cloud SQL instance for which you want to clear all flags.
Open the instance and clickEdit.
Open theDatabase flags section.
Click theX next to all of the flags shown.
ClickSave to save your changes.

gcloud

Clear all flags to their default values on an instance:

gcloudsqlinstancespatchINSTANCE_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 namedrequest.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 namedrequest.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 namedrequest.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 namedrequest.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 intoyour instance with thepsql client and enter the following statement:

SELECTname,settingFROMpg_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 theGoogle 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 itsInstance Overview page.
The database flags that have been set are listed under theDatabase flags section.

gcloud

Get the instance state:

gcloudsqlinstancesdescribeINSTANCE_NAME

In the output, database flags are listed under thesettings asthe collectiondatabaseFlags. For more information about the representation of the flags in the output, seeInstances 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 thedatabaseFlags 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 thedatabaseFlags field.

Supported flags

Flags not mentioned below are not supported.

For a given flag, Cloud SQL might support a different value or rangefrom 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 issha256. No

anon.maskschema String
The default ismask. No

anon.restrict_to_trusted_schemas boolean
on |off
The default isoff. No

anon.salt String
There's no default value. No

anon.sourceschema String
The default ispublic. No

auto_explain.log_analyze boolean
on |off
The default isoff. No

auto_explain.log_buffers boolean
on |off
The default isoff. No

auto_explain.log_min_duration Integer
-1 ... 2147483647
The default is-1. No

auto_explain.log_format String
text|xml|json|yaml
The default istext. No

auto_explain.log_nested_statements boolean
on |off
The default isoff. No

auto_explain.log_settings boolean
on |off
The default isoff.
Supported in PostgreSQL 12 and above. No

auto_explain.log_timing boolean
on |off
The default ison. No

auto_explain.log_triggers boolean
on |off
The default isoff. No

auto_explain.log_wal boolean
on |off
The default isoff.
Supported in PostgreSQL 13 and above. No

auto_explain.log_verbose boolean
on |off
The default isoff. No

auto_explain.sample_rate Float
0 ... 1
The default is1. No

autovacuum boolean
on |off
The default ison.
For usage information that might affect the SLA, see theOperational guidelines for Cloud SQL for PostgreSQL instances. No

autovacuum_analyze_scale_factor float
0 ...100
The default is0.1. No

autovacuum_analyze_threshold integer
0 ...2147483647
The default is50. No

autovacuum_freeze_max_age integer
100000 ...2000000000
The default is200000000.
For usage information that might affect the SLA, see theOperational guidelines for Cloud SQL for PostgreSQL instances. Yes

autovacuum_max_workers integer
1 ...varies (see note)
The default is3.Note: To determine the maximum value that you canset for this flag, you must first calculate the numberof backend connections that are already in use. This calculation is the sum ofthe values for max_connections (maximum number ofclient connections), autovacuum_max_workers (maximumnumber of autovacuum processes), and max_worker_processes. The sum cannot exceed262142. Yes

autovacuum_multixact_freeze_max_age integer
10000 ...2000000000
The default is400000000.
For usage information that might affect the SLA, see theOperational guidelines for Cloud SQL for PostgreSQL instances. Yes

autovacuum_naptime integer
1 ...2147483s
The default is60s. No

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

autovacuum_vacuum_cost_limit integer
0 ... 10000, or-1 to use thevacuum_cost_limit value
The default is-1. No

autovacuum_vacuum_insert_scale_factor float
0 ...100
The default is0.2. No

autovacuum_vacuum_insert_threshold integer
-1 ...2147483647
The default is1000. No

autovacuum_vacuum_scale_factor float
0 ...100
The default is0.2. No

autovacuum_vacuum_threshold integer
0 ...2147483647
The default is50. No

autovacuum_work_mem integer
0 ...2147483647 KB, or-1 to use themaintenance_work_mem value
The default is-1. No

backend_flush_after integer
0 ...256
The unit is8 KB.
The default is0. No

bgwriter_delay integer
10 ...10000 ms
The default is200. No

bgwriter_flush_after integer
0 ...256
The unit is8 KB.
The default is64. No

bgwriter_lru_maxpages integer
0 ...1073741823
The default is100. No

bgwriter_lru_multiplier Float
0 ...10
The default is2. No

checkpoint_completion_target float
0.0 ...1.0
The default is0.9. No

checkpoint_flush_after integer
0 ...256
Unit is8 KB.
The default is32. No

checkpoint_timeout integer
30 ...86,400s
The default is300s.
For usage information that might affect the SLA, see theOperational guidelines for Cloud SQL for PostgreSQL instances. No

checkpoint_warning integer
0 ...2147483647s
The default is30s. No

client_connection_check_interval integer
0 ...2147483647
The default is0. No

cloudsql.allow_passwordless_local_connections boolean
on |off
The default isoff.Note: This flag is specific to Cloud SQL. Also, Cloud SQL doesn't support the flag for Identity and Access Management (IAM) users. No

cloudsql.enable_anon boolean
on |off
The default isoff.Note: This flag is specific to Cloud SQL. No

cloudsql.enable_auto_explain boolean
on |off
The default isoff.Note: This flag is specific to Cloud SQL. Yes

cloudsql.enable_index_advisor boolean
on |off
The default isoff.Note: This flag is specific to Cloud SQL. Yes

cloudsql.enable_maintenance_mode boolean
on |off
The default isoff.Note: This flag is specific to Cloud SQL. For more information about this flag, see Overcome transaction ID (TXID) wraparound. Yes

cloudsql.enable_pgaudit boolean
on |off
The default isoff.Note: This flag is specific to Cloud SQL. For more information about this flag, see Auditing for PostgreSQL using pgAudit. Yes

cloudsql.enable_pg_bigm boolean
on |off
default:offNote: This flag is specific to Cloud SQL. For more information about this flag, see pg_bigm. Yes

cloudsql.enable_pg_cron boolean
on |off
The default isoff.
Supported in PostgreSQL 10 and above.Note: This flag is specific to Cloud SQL. Yes

cloudsql.enable_pg_hint_plan boolean
on |off
The default isoff.Note: This flag is specific to Cloud SQL. Yes

cloudsql.enable_pglogical boolean
on |off
The default isoff.Note: This flag is specific to Cloud SQL. For more information about this flag, see Setting up logical replication and decoding. Yes

cloudsql.enable_pg_squeeze boolean
on |off
The default isoff.Note: This flag is specific to Cloud SQL. Yes

cloudsql.enable_pg_wait_sampling boolean
on |offNote: This flag is specific to Cloud SQL. Yes

cloudsql.iam_authentication boolean
on |off
The default isoff.Note: This flag is specific to Cloud SQL. No

cloudsql.logical_decoding boolean
on |off
The default isoff.Note: This flag is specific to Cloud SQL. For more information about this flag, see Setting up logical replication and decoding. Yes

cloudsql.max_failed_attempts_user integer
0 ...10000
The default is10.
Note: This flag is specific to Cloud SQL. It determines the number of users that can set theLock after failed attempts restriction for a user password policy. This restriction specifies the number of times that a user can try a password incorrectly before their account is locked. Yes

cloudsql.pg_authid_select_role string
Note: This flag is specific to Cloud SQL. For more information about this flag, see Access to thepg_shadow view and thepg_authid table. No

commit_delay integer
0 ...100000
The default is0. No

commit_siblings integer
0 ...1000
The default is5. No

constraint_exclusion enumeration
partition |on |off
The default ispartition. No

cpu_index_tuple_cost float
0.0 ...inf
The default is0.005. No

cpu_operator_cost float
0.0 ...inf
The default is0.0025. No

cpu_tuple_cost float
0.0 ...inf
The default is0.01. No

cron.database_name String.
Supported in PostgreSQL 10 and above. Yes

cron.log_statement boolean
on |off
The default ison.
Supported in PostgreSQL 10 and above. Yes

cron.log_run boolean
on |off
The default ison.
Supported in PostgreSQL 10 and above. Yes

cron.max_running_jobs Integer
0 ... varies
The default is5.
Supported in PostgreSQL 10 and above. Yes

cursor_tuple_fraction float
0.0 ...1.0
The default is0.1. No

deadlock_timeout integer
1 ...2147483647 ms
The default is1000ms. No

default_statistics_target integer
1 ...10000
The default is100. No

default_tablespace string No

default_transaction_deferrable boolean
on |off
The default isoff. No

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

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

effective_io_concurrency integer
0 ...1000
The default is1. No

enable_async_append boolean
on |off
The default ison. No

enable_bitmapscan boolean
on |off
The default ison. No

enable_gathermerge boolean
on |off
The default ison. No

enable_incremental_sort boolean
on |off
The default ison. No

enable_memoize boolean
on |off
The default ison. No

enable_parallel_append boolean
on |off
The default ison. No

enable_parallel_hash boolean
on |off
The default ison. No

enable_partition_pruning boolean
on |off
The default ison. No

enable_partitionwise_aggregate boolean
on |off
The default isoff. No

enable_partitionwise_join boolean
on |off
The default isoff. No

enable_hashagg boolean
on |off
The default ison. No

enable_hashjoin boolean
on |off
The default ison. No

enable_indexonlyscan boolean
on |off
The default ison. No

enable_indexscan boolean
on |off
The default ison. No

enable_material boolean
on |off
The default ison No

enable_mergejoin boolean
on |off
The default ison. No

enable_nestloop boolean
on |off
The default ison. No

enable_seqscan boolean
on |off
The default ison. No

enable_sort boolean
on |off
The default ison. No

enable_tidscan boolean
on |off
The default ison. No

force_parallel_mode enumeration
off |on |regress
The default isoff.
Note: For PostgreSQL 16 and later, this flag is renamed to debug_parallel_query. No

from_collapse_limit integer
1 ...2147483647
The default is8. No

geqo boolean
on |off
The default ison. No

geqo_effort integer
1 ...10
The default is5. No

geqo_generations integer
0 ...2147483647
The default is0. No

geqo_pool_size integer
0 ...2147483647
The default is0. No

geqo_seed float
0.0 ...1.0
The default is0. No

geqo_selection_bias float
1.5 ...2.0
The default is2. No

geqo_threshold integer
2 ...2147483647
The default is12. No

gin_fuzzy_search_limit integer
0 ...2147483647
The default is0. No

gin_pending_list_limit integer
64 ...2147483647KB
The default is4096KB. No

hash_mem_multiplier float
1 ...1000
The default is2. No

hot_standby_feedback boolean
on |off
The default isoff. No

huge_pages enumeration
try |off
The default istry. Yes

idle_in_transaction_session_timeout integer
0 ...2147483647 ms
The default is0. No

ivfflat.probes integer
1 ...varies
The default is1.
Supported in PostgreSQL 11 and above. No

join_collapse_limit integer
1 ...2147483647
The default is8. No

lock_timeout integer
0 ...2147483647 ms
The default is0. No

log_autovacuum_min_duration integer
0 ...2147483647 ms, or-1 to disable
The default is0. No

log_checkpoints boolean
on |off
The default isoff. No

log_connections boolean
on |off
The default isoff. No

log_disconnections boolean
on |off
The default isoff. No

log_duration boolean
on |off
The default isoff. No

log_error_verbosity enumeration
terse |default |verbose
The default isdefault. No

log_executor_stats boolean
on |off
The default isoff. No

log_hostname boolean
on |off
The default isoff. No

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. No

log_lock_waits boolean
on |off
The default isoff. No

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

log_parser_stats boolean
on |off
The default isoff. No

log_planner_stats boolean
on |off
The default isoff. No

log_recovery_conflict_waits boolean
on |off
The default isoff. No

log_replication_commands boolean
on |off
The default isoff. No

log_statement enumeration
none |ddl |mod |all
Set tomod to log all Data definition language (DDL) statements, plus data-modifying statements such asINSERT,UPDATE,DELETE,TRUNCATE
The default isnone. No

log_statement_stats boolean
on |off
May not be enabled together withlog_parser_stats,log_planner_stats, orlog_executor_stats.
The default isoff. No

log_temp_files integer
0 ...2147483647 KB, or-1 to disable
The default is0. No

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 supportUTC+X as a valid format for this flag, whereX is+/-HH:MM.

At this time, we don't support POSIX time zone specifications.

Yes

logical_decoding_work_mem integer
64 ...2147483647
The default is65536. No

maintenance_io_concurrency integer
0 ...1000
The default is10. No

maintenance_work_mem integer
1024 ...2147483647 KB
The default is64 MB. No

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

Note: To determine the maximum value that you canset for this flag, you must first calculate the numberof backend connections that are already in use. This calculation is the sum ofthe values for max_connections (maximum number ofclient connections), autovacuum_max_workers (maximumnumber of autovacuum processes), and max_worker_processes. The sum cannot exceed262142.

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 is64.

Yes

max_logical_replication_workers integer
4 ...8192
The default is4.
Supported in PostgreSQL 10 and above. Yes

max_parallel_maintenance_workers

integer
0 ...variesNote: Limits based on the RAM in the instance, in the way described for max_worker_processes.
The default is2.
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 isdefault, 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 ...variesNote: Limits based on the RAM in the instance, in the way described for max_worker_processes.
The default is8.
Supported in PostgreSQL 10 and above.

If the value on the primary isdefault, 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 ...variesNote: Limits based on the RAM in the instance, in the way described for max_worker_processes.
The default is2.

If the value on the primary isdefault, 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 is2.
Supported in PostgreSQL 10 and above. No

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

max_pred_locks_per_transaction

integer
64 ...1048576Note: 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 ...variesNote: 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 is10.

Yes

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

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

max_sync_workers_per_subscription integer
2 ...64
Cannot be greater thanmax_logical_replication_workers. No

max_wal_senders

integer
10 ...varies
The default is10.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 ofmax_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 is1504 MB if instance memory more than or equal to3.75 GB. The default value is1 GB if instance memory is less than3.75 GB.

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

max_worker_processes

integer
8 ...variesNote: 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_index_scan_size integer
0 ...715827882
Unit is8 KB No

min_parallel_table_scan_size integer
0 ...715827882
Unit is8 KB No

min_parallel_relation_size integer
0 ...715827882
Unit is8 KB
Supported only in PostgreSQL 9.6. No

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.Note: For PostgreSQL 17, this flag is deprecated. Yes

parallel_setup_cost float
0.0 ...inf
The default is1000. No

parallel_tuple_cost float
0.0 ...inf
The default is0.1. No

password_encryption

enumeration
md5 |scram-sha-256

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

pg_bigm.enable_recheck boolean
on |off No

pg_bigm.gin_key_limit integer
0 ...2147483647 No

pg_bigm.similarity_limit float
0.0 ...1.0 No

pg_hint_plan.enable_hint boolean
on |off
The default ison. No

pg_hint_plan.debug_print String
off|on|detailed|verbose|0|1||2|3|no|yes|false|true
The default isoff. No

pg_hint_plan.enable_hint_table boolean
on |off
The default isoff. No

pglogical.batch_inserts boolean
on |off
The default isoff.Note: For information about the feature that uses this flag, see Setting up logical replication and decoding. Yes

pglogical.conflict_log_level String
The default isLOG.
This flag accepts the same values aslog_min_messages.Note: For information about the feature that uses this flag, see Setting up logical replication and decoding. No

pglogical.conflict_resolution String
error|apply_remote|keep_local|last_update_wins|first_update_wins The default isapply_remote.Note: The valueskeep_local,last_update_wins andfirst_update_wins requirestrack_commit_timestamps to be enabled on both nodes. For information about the feature that uses this flag, see Setting up logical replication and decoding. No

pglogical.extra_connection_options String
Accepts PostgreSQL keyword/value connection strings.
The default is the empty string.Note: For information about the feature that uses this flag, see Setting up logical replication and decoding. No

pglogical.synchronous_commit boolean
on |off
The default ison.Note: For information about the feature that uses this flag, see Setting up logical replication and decoding. Yes

pglogical.use_spi boolean
on |off
The default isoff.Note: For information about the feature that uses this flag, see Setting up logical replication and decoding. Yes

pg_stat_statements.max integer
100 ...2147483647
The default is5000. Yes

pg_stat_statements.save boolean
on |off
The default ison. No

pg_stat_statements.track enumeration
none |top |all
The default istop. No

pg_stat_statements.track_utility boolean
on |off
The default ison. No

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 isnone.

pgaudit.log_catalog boolean
on |off
The default ison. No

pgaudit.log_client boolean
on |off
The default isoff. No

pgaudit.log_parameter boolean
on |off
The default isoff. No

pgaudit.log_relation boolean
on |off
The default isoff. No

pgaudit.log_statement_once boolean
on |off
The default isoff. No

pgaudit.role string
There is no default. No

pgtt.enabled boolean
on |off
The default ison. No

pg_wait_sampling.history_period integer
1 ...2147483647 No

pg_wait_sampling.history_size integer
1 ...2147483647 No

pg_wait_sampling.profile_period integer
1 ...2147483647 No

pg_wait_sampling.profile_pid boolean
on |off No

pg_wait_sampling.profile_queries boolean
on |off No

random_page_cost float
0.0 ...inf
The default is4. No

plan_cache_mode

String
auto|force_generic_plan|force_custom_plan
The default isauto.

rdkit.agent_FP_bit_ratio float
0 ...3 No

rdkit.avalon_fp_size integer
64 ...9192 No

rdkit.dice_threshold float
0 ...1 No

rdkit.difference_FP_weight_agents integer
-10 ...10 No

rdkit.difference_FP_weight_nonagents integer
1 ...20 No

rdkit.do_chiral_sss boolean
on |off No

rdkit.do_enhanced_stereo_sss boolean
on |off No

rdkit.featmorgan_fp_size integer
64 ...9192 No

rdkit.hashed_atompair_fp_size integer
64 ...9192 No

rdkit.hashed_torsion_fp_size integer
64 ...9192 No

rdkit.ignore_reaction_agents boolean
on |off No

rdkit.init_reaction boolean
on |off No

rdkit.layered_fp_size integer
64 ...9192 No

rdkit.morgan_fp_size integer
64 ...9192 No

rdkit.move_unmmapped_reactants_to_agents boolean
on |off No

rdkit.rdkit_fp_size integer
64 ...9192 No

rdkit.reaction_difference_fp_size integer
64 ...9192 No

rdkit.reaction_difference_fp_type integer
1 ...3 No

rdkit.reaction_sss_fp_size integer
64 ...9192 No

rdkit.reaction_sss_fp_type integer
1 ...5 No

rdkit.sss_fp_size integer
64 ...4096 No

rdkit.tanimoto_threshold float
0 ...1 No

rdkit.threshold_unmapped_reactant_atoms float
0 ...1 No

replacement_sort_tuples integer
0 ...2147483647Warning: This flag is supported only until PostgreSQL 9.6 for Cloud SQL. No

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

seq_page_cost float
0.0 ...inf
The default is1.0. No

shared_buffers integer
The size range is from 10% - 60% of the instance's memory.
Unit is8 KB.
The default is one-third of the instance's total VM memory (in MB).For example, for45 GB of instance memory, the default value is15085MB. Yes

squeeze.max_xlock_time integer
1 ...2147483647 No

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 forssl_min_protocol_version, with the addition of an empty string, which allows any protocol version to be specified.
Supported in PostgreSQL 12 and above. No

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 isTLSv1.
Supported in PostgreSQL 12 and above. No

standard_conforming_strings boolean
on |off
The default ison. No

synchronize_seqscans boolean
on |off
The default ison. No

tcp_keepalives_count integer
0 ...2147483647
The default is5. No

tcp_keepalives_idle integer
0 ...2147483647
The default is60. No

tcp_keepalives_interval integer
0 ...2147483647
The default is60. No

temp_buffers integer
100 ...1,073,741,823
Unit is8 KB No

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 is10262623KB. No

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 supportUTC+X as a valid format for this flag, whereX is+/-HH.

At this time, we don't support POSIX time zone specifications.

Yes

trace_notify boolean
on |off
The default isoff. No

trace_sort boolean
on |off
The default isoff. No

track_activities boolean
on |off
The default ison. No

track_activity_query_size integer
100 ...102400
The default is1 KB. Yes

track_commit_timestamp boolean
on |off
The default isoff. Yes

track_counts boolean
on |off
The default ison. No

track_functions enumeration
none |pl |all
The default isnone. No

track_io_timing boolean
on |off
The default isoff. No

vacuum_cost_delay integer
0 ...100 ms
The default is0. No

vacuum_cost_limit integer
1 ...10000
The default is200. No

vacuum_cost_page_dirty integer
0 ...10000
The default is20. No

vacuum_cost_page_hit integer
0 ...10000
The default is1. No

vacuum_cost_page_miss integer
0 ...10000
The default is10. No

vacuum_failsafe_age integer
0 ...2100000000
The default is1600000000. No

vacuum_freeze_min_age integer
0 ...1000000000
The default is50000000. No

vacuum_freeze_table_age integer
0 ...2000000000
The default is150000000. No

vacuum_multixact_failsafe_age integer
0 ...2100000000
The default is1600000000. No

vacuum_multixact_freeze_min_age integer
0 ...1000000000
The default is5000000. No

vacuum_multixact_freeze_table_age integer
0 ...2000000000
The default is150000000. No

wal_buffers integer
The size range is from -1 - 5% of the instance's memory.
Unit is8 KB.
The default is16 MB. Yes

wal_compression enumeration
off |on |pglz |lz4 |zstd
The default isoff.
Note: Thepglz,lz4, andzstd values are supported only for PostgreSQL versions 15 and later. No

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.

wal_writer_delay integer
1 ...10000
The default is200. No

wal_writer_flush_after integer
0 ...2147483647
The default is128. No

work_mem integer
64 ...2147483647 KB
The default is4 MB. No

Special flags

`bgwriter`

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

Two flags associated with thebgwriter flag arebgwriter_delay andbgwriter_lru_maxpages.bgwriter_delay specifies the delay between activityrounds for the background writer in milliseconds (ms), andbgwriter_lru_maxpagesspecifies how many buffers will be written by the background writer.

The default value for thebgwriter flag is200 ms. However, ifyou select a solid state drive (SSD) that's greater than 500 GB, then the valueof thebgwriter_delay flag is set to50, and the value ofthebgwriter_lru_maxpages flag is set to200.

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

`session_replication_role`

PostgreSQL has thesession_replication_role flag, which is designed to be usedfor logical replication and lets you disable constraint triggers inindividual sessions.

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

This flag can be set in a session by any user which has theREPLICATIONproperty set. TheREPLICATION property for any user can be set bycloudsqlsuperuser when one of flagscloudsql.enable_pglogical orcloudsql.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: ALTERDATABASE`dbname`SETTIMEZONETO'`timezone`';ALTERUSER`username`SETTIMEZONETO'`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:

ALTERDATABASEdbnameSETTIMEZONETO'timezone';ALTERUSERusernameSETTIMEZONETO'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 aboutPostgreSQL server configuration.
Learn more aboutOperational Guidelines.

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-07-16 UTC.

Movatterモバイル変換

Configure database flags Stay organized with collections Save and categorize content based on your preferences.

Configure database flags

Set a database flag

Console

gcloud

Terraform

Apply the changes

Prepare Cloud Shell

Prepare the directory

Apply the changes

Delete the changes

REST v1

curl (Linux, macOS, or Cloud Shell)

PowerShell (Windows)

Response

REST v1beta4

curl (Linux, macOS, or Cloud Shell)

PowerShell (Windows)

Response

Clear all flags to their default values

Console

gcloud

REST v1

curl (Linux, macOS, or Cloud Shell)

PowerShell (Windows)

Response

REST v1beta4

curl (Linux, macOS, or Cloud Shell)

PowerShell (Windows)

Response

View current values of database flags

Determine which database flags have been set for an instance

Console

gcloud

REST v1

curl (Linux, macOS, or Cloud Shell)

PowerShell (Windows)

Response

REST v1beta4

curl (Linux, macOS, or Cloud Shell)

PowerShell (Windows)

Response

Supported flags

Special flags

bgwriter

session_replication_role

Troubleshooting

What's next

Configure database flags

`bgwriter`

`session_replication_role`