Configuring database flags

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.

Configuring database flags

Setting a database flag

Console

  1. In the Google Cloud Console, create a new Cloud Console project, or open an existing project by selecting the project name.
  2. Open the instance and click Edit.
  3. Scroll down to the Flags section.
  4. 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.
  5. Click Save to save your changes.
  6. 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 ("=").

REST v1beta4

To set a flag for an existing database:

Before using any of the request data below, 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:

You should receive a JSON response similar to the following:

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.

Clearing all flags to their default value

Console

  1. In the Google Cloud Console, create a new Cloud Console project, or open an existing project by selecting the project name.
  2. Open the instance and click Edit.
  3. Open the Database flags section.
  4. Click the X next to all of the flags shown.
  5. 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 v1beta4

To clear all flags for an existing instance:

Before using any of the request data below, 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:

You should receive a JSON response similar to the following:

Viewing 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).

Determining what database flags have been set for an instance

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

Console

  1. In the Google Cloud Console, create a new Cloud Console project, or open an existing project by selecting the project name.
  2. 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 v1beta4

To list flags configured for an instance:

Before using any of the request data below, 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:

You should receive a JSON response similar to the following:

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 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?
autovacuum boolean
on | off
For usage information that might affect the SLA, see the Operational guidelines for Cloud SQL for PostgreSQL instances.
No
autovacuum_analyze_scale_factor float
0 ... 100
No
autovacuum_analyze_threshold integer
0 ... 2147483647
No
autovacuum_freeze_max_age integer
100000 ... 2000000000
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)
Yes
autovacuum_multixact_freeze_max_age integer
10000 ... 2000000000
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
No
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.
No
autovacuum_vacuum_cost_limit integer
0 ... 10000, or -1 to use the vacuum_cost_limit value
No
autovacuum_vacuum_scale_factor float
0 ... 100
No
autovacuum_vacuum_threshold integer
0 ... 2147483647
No
autovacuum_work_mem integer
0 ... 2147483647 KB, or -1 to use the maintenance_work_mem value
No
checkpoint_completion_target float
0.0 ... 1.0
No
checkpoint_timeout integer
30 ... 86,400
For usage information that might affect the SLA, see the Operational guidelines for Cloud SQL for PostgreSQL instances.
No
checkpoint_warning integer
0 ... 2147483647 s
No
cloudsql.enable_pgaudit boolean
on | off
Yes
cloudsql.enable_iam_login boolean
on | off
default: off
Enables database user authentication using the Cloud SQL IAM database authentication.
Yes
cloudsql.iam_authentication boolean
on | off
default: off
Yes
cloudsql.enable_pglogical (Beta) boolean
on | off
default: off
Yes
cloudsql.logical_decoding (Beta) boolean
on | off
default: off
Yes
commit_delay integer
0 ... 100000
No
commit_siblings integer
0 ... 1000
No
constraint_exclusion enumeration
partition | on | off
No
cpu_index_tuple_cost float
0.0 ... inf
No
cpu_operator_cost float
0.0 ... inf
No
cpu_tuple_cost float
0.0 ... inf
No
cursor_tuple_fraction float
0.0 ... 1.0
No
deadlock_timeout integer
1 ... 2147483647 ms
No
default_statistics_target integer
1 ... 10000
No
default_tablespace string No
default_transaction_deferrable boolean
on | off
No
default_transaction_isolation enumeration
serializable | 'repeatable read' | 'read committed' | 'read uncommitted'
No
effective_cache_size integer
The size range is from 10% - 70% of the instance's memory.
Unit is 8 KB.
No
enable_bitmapscan boolean
on | off
No
enable_hashagg boolean
on | off
No
enable_hashjoin boolean
on | off
No
enable_indexonlyscan boolean
on | off
No
enable_indexscan boolean
on | off
No
enable_material boolean
on | off
No
enable_mergejoin boolean
on | off
No
enable_nestloop boolean
on | off
No
enable_seqscan boolean
on | off
No
enable_sort boolean
on | off
No
enable_tidscan boolean
on | off
No
force_parallel_mode enumeration
off | on | regress
No
from_collapse_limit integer
1 ... 2147483647
No
geqo boolean
on | off
No
geqo_effort integer
1 ... 10
No
geqo_generations integer
0 ... 2147483647
No
geqo_pool_size integer
0 ... 2147483647
No
geqo_seed float
0.0 ... 1.0
No
geqo_selection_bias float
1.5 ... 2.0
No
geqo_threshold integer
2 ... 2147483647
No
gin_fuzzy_search_limit integer
0 ... 2147483647
No
gin_pending_list_limit integer
64 ... 2147483647 KB
No
hot_standby_feedback boolean
on | off
No
idle_in_transaction_session_timeout integer
0 ... 2147483647 ms
No
join_collapse_limit integer
1 ... 2147483647
No
lock_timeout integer
0 ... 2147483647 ms
No
log_autovacuum_min_duration integer
0 ... 2147483647 ms, or -1 to disable
No
log_checkpoints boolean
on | off
No
log_connections boolean
on | off
No
log_disconnections boolean
on | off
No
log_duration boolean
on | off
No
log_error_verbosity enumeration
terse | default | verbose
No
log_executor_stats boolean
on | off
No
log_hostname boolean
on | off
No
log_lock_waits boolean
on | off
No
log_min_duration_statement integer
-1 ... 2147483647 ms
No
log_min_error_statement enumeration
debug5 | debug4 | debug3 | debug2 | debug1 | info | notice | warning | error | log | fatal | panic
No
log_min_messages enumeration
debug5 | debug4 | debug3 | debug2 | debug1 | info | notice | warning | error | log | fatal | panic
No
log_parser_stats boolean
on | off
No
log_planner_stats boolean
on | off
No
log_replication_commands boolean
on | off
No
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
No
log_statement_stats boolean
on | off
May not be enabled together with log_parser_stats, log_planner_stats, or log_executor_stats.
No
log_temp_files integer
0 ... 2147483647 KB, or -1 to disable
No
maintenance_work_mem integer
1024 ... 2147483647 KB
No
max_connections integer
14 ... varies (see note)
Memory Size (MB)Default ValueMax Value
0 - 1,7002525
1,700 - 6,0005030,000
6,000 - 7,50020085,000
7,500 - 120,000400200,000
120,00 and above1,000262,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.

Yes
max_locks_per_transaction integer
10 ... 2,147,483,647

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.

Yes
max_prepared_transactions integer
0 ... varies
Memory Size (MB)Max Value
0 - 3,84030,000
3,840 - 7,68085,000
7,680 - 15,360200,000
15,360 and above262,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.

Yes
max_replication_slots (Beta) integer
10 ... varies
Yes
max_standby_archive_delay integer
0 ... 2147483647 ms, or -1 to wait forever
No
max_standby_streaming_delay integer
0 ... 2147483647 ms, or -1 to wait forever
No
max_wal_senders (Beta) integer
10 ... varies
Yes
max_wal_size integer
2 ... 2147483647

Unit is 1 MB for Postgres 10, 11, 12, and 13. Unit is 16MB (the WAL file size) for version 9.6.

For usage information that might affect the SLA, see the Operational guidelines for Cloud SQL for PostgreSQL instances.
No
max_worker_processes (Beta) integer
8 ... varies
Yes
min_parallel_relation_size integer
0 ... 715827882
Unit is 8 KB
No
old_snapshot_threshold integer
0 ... 86400 min, or -1 to disable
Yes
parallel_setup_cost float
0.0 ... inf
No
parallel_tuple_cost float
0.0 ... inf
No
password_encryption enumeration
md5 | scram-sha-256

Postgres 10, 11, 12, and 13.

default is md5.

No
pglogical.batch_inserts (Beta) boolean
on | off
default: on
Yes
pglogical.conflict_log_level (Beta) String
default: LOG.
This flag accepts the same values as log_min_messages.
No
pglogical.conflict_resolution (Beta) String
error|apply_remote|keep_local|last_update_wins|first_update_wins
default: apply_remote
No
pglogical.extra_connection_options (Beta) String
Accepts PostgreSQL keyword/value connection strings.
The default is the empty string.
No
pglogical.synchronous_commit (Beta) boolean
on | off
default: on
Yes
pglogical.use_spi (Beta) boolean
on | off
default: off
Yes
pg_stat_statements.max integer
100 ... 2147483647
Yes
pg_stat_statements.save boolean
on | off
No
pg_stat_statements.track enumeration
none | top | all
No
pg_stat_statements.track_utility boolean
on | off
No
pgaudit.log enumeration
read | write | function | role | ddl | misc | misc_set | all|none

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.

No
pgaudit.log_catalog boolean
on | off

The default is on.

No
pgaudit.log_client boolean
on | off

The default is off.

No
pgaudit.log_level enumeration
debug5 | debug4 | debug3 | debug2 | debug1 | info | notice | warning | error | log

The default is log. Additionally, pgaudit.log_level is enabled only when pgaudit.log_client is on.

No
pgaudit.log_parameter boolean
on | off

The default is off.

No
pgaudit.log_relation boolean
on | off

The default is off.

No
pgaudit.log_statement_once boolean
on | off

The default is off.

No
pgaudit.role string

There is no default.

No
random_page_cost float
0.0 ... inf
No
replacement_sort_tuples integer
0 ... 2147483647
No
ssl_max_protocol_version enumeration
Postgres 12: 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.
No
ssl_min_protocol_version enumeration
Postgres 12: Sets the minimum SSL/TLS protocol version to use. Valid values are currently: TLSv1, TLSv1.1, TLSv1.2, TLSv1.3.

The default is TLSv1.

No
standard_conforming_strings boolean
on | off
No
synchronize_seqscans boolean
on | off
No
tcp_keepalives_count integer
0 ... 2147483647
No
tcp_keepalives_idle integer
0 ... 2147483647
No
tcp_keepalives_interval integer
0 ... 2147483647
No
temp_buffers integer
100 ... 1,073,741,823
Unit is 8 KB
No
temp_file_limit integer
1048576 ... 2147483647 KB
No
trace_notify boolean
on | off
No
trace_recovery_messages enumeration
debug5 | debug4 | debug3 | debug2 | debug1 | log | notice | warning | error
No
trace_sort boolean
on | off
No
track_activities boolean
on | off
No
track_activity_query_size integer
100 ... 102400
Yes
track_commit_timestamp boolean
on | off
Yes
track_counts boolean
on | off
No
track_functions enumeration
none | pl | all
No
track_io_timing boolean
on | off
No
vacuum_cost_delay integer
0 ... 100 ms
No
vacuum_cost_limit integer
1 ... 10000
No
vacuum_freeze_min_age integer
0 ... 1000000000
No
vacuum_freeze_table_age integer
0 ... 2000000000
No
vacuum_multixact_freeze_min_age integer
0 ... 1000000000
No
vacuum_multixact_freeze_table_age integer
0 ... 2000000000
No
wal_compression integer
64 ... 2147483647 KB
No
work_mem boolean
on | off
No

Troubleshooting

Click the links in the table for details:

For this problem... The issue might be... Try this...
There's no flag to set the time zone. A time zone flag is not supported. There are some workarounds.

There's no flag to set the time zone

PostgreSQL and SQL Server for Cloud SQL do not support a time zone flag to adjust timezone based on user needs.

The issue might be

A time zone flag is not supported.

Things to try

You can set the time zone per session, but this will expire when you log off. A better solution is to connect to the database and set the database timezone to the desired one either per user or per database:

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

These settings will remain even after the session is closed which will mimic the .conf configuration.

What's next