This page describes how to configure database flags for Cloud SQL, and lists which flags you can set for your instance. You use database flags to adjust PostgreSQL parameters and options, to configure and tune your 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 an instance, the instance might be restarted. The flag value is then persisted for the instance until you remove it.
Configuring database flags
Setting a database flag
Console
- In the Google Cloud Console, create a new Cloud Console project, or open an existing project by selecting the project name.
- 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 ("=").
cURL
To set a flag for an existing instance:
gcloud auth login ACCESS_TOKEN="$(gcloud auth print-access-token)" curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \ --header 'Content-Type: application/json' \ --data '{"settings" : {"databaseFlags" : [{ "name": "<FLAG-NAME>", "value": "<FLAG-VALUE>" }]}}' \ -X PATCH \ https://www.googleapis.com/sql/v1beta4/projects/<PROJECT-ID>/instances/<INSTANCE_NAME>
If there are existing flags configured for the instance, 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
- In the Google Cloud Console, create a new Cloud Console project, or open an existing project by selecting the project name.
- 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.
cURL
To clear all flags for an existing instance:
gcloud auth login ACCESS_TOKEN="$(gcloud auth print-access-token)" curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \ --header 'Content-Type: application/json' \ --data '{"settings" : {"databaseFlags" : []}}' \ -X PATCH \ https://www.googleapis.com/sql/v1beta4/projects/<PROJECT-ID>/instances/<INSTANCE_NAME>
Viewing current values of database flags
To view all current values of the PostgreSQL settings, log into your instance with thepsql
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
- In the Google Cloud Console, create a new Cloud Console project, or open an existing project by selecting the project name.
- 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.
cURL
To list flags configured for an instance:
gcloud auth login ACCESS_TOKEN="$(gcloud auth print-access-token)" curl --header "Authorization: Bearer ${ACCESS_TOKEN}"
--header 'Content-Type: application/json'
-X GET
https://www.googleapis.com/sql/v1beta4/projects/<PROJECT-ID>/instances/<INSTANCE_NAME>?fields=settings
In the output, look for the databaseFlags
field.
Supported flags
For a given flag, Cloud SQL might support a different range than 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 | TypeAcceptable Values and Notes | Restart Required? |
In Beta? |
---|---|---|---|
autovacuum | boolean on | off |
No | No |
autovacuum_analyze_scale_factor | float 0 ... 100 |
No | No |
autovacuum_analyze_threshold | integer 0 ... 2147483647 |
No | No |
autovacuum_freeze_max_age | integer 100000 ... 2000000000 |
Yes | Yes |
autovacuum_max_workers | integer 1 ... varies (see note)
|
Yes | No |
autovacuum_multixact_freeze_max_age | integer 10000 ... 2000000000 |
Yes | Yes |
autovacuum_naptime | integer 1 ... 2147483 s |
No | No |
autovacuum_vacuum_cost_delay | integer 0 ... 100 ms, or -1 to use the vacuum_cost_delay value |
No | No |
autovacuum_vacuum_cost_limit | integer 0 ... 10000 , or -1 to use the vacuum_cost_limit value |
No | No |
autovacuum_vacuum_scale_factor | float 0 ... 100 |
No | No |
autovacuum_vacuum_threshold | integer 0 ... 2147483647 |
No | No |
autovacuum_work_mem | integer 0 ... 2147483647 KB, or -1 to use maintenance_work_mem value |
No | Yes |
checkpoint_completion_target | float 0.0 ... 1.0 |
No | No |
checkpoint_timeout | integer 30 ... 86400 s |
No | Yes |
checkpoint_warning | integer 0 ... 2147483647 s |
No | No |
commit_delay | integer 0 ... 100000 |
No | No |
commit_siblings | integer 0 ... 1000 |
No | No |
constraint_exclusion | enumeration partition | on | off |
No | No |
cpu_index_tuple_cost | float 0.0 ... inf |
No | No |
cpu_operator_cost | float 0.0 ... inf |
No | No |
cpu_tuple_cost | float 0.0 ... inf |
No | No |
cursor_tuple_fraction | float 0.0 ... 1.0 |
No | No |
deadlock_timeout | integer 1 ... 2147483647 ms |
No | No |
default_statistics_target | integer 1 ... 10000 |
No | No |
default_tablespace | string |
No | No |
default_transaction_deferrable | boolean on | off |
No | No |
default_transaction_isolation | enumeration serializable | 'repeatable read' | 'read committed' | 'read uncommitted' |
No | No |
enable_bitmapscan | boolean on | off |
No | No |
enable_hashagg | boolean on | off |
No | No |
enable_hashjoin | boolean on | off |
No | No |
enable_indexonlyscan | boolean on | off |
No | No |
enable_indexscan | boolean on | off |
No | No |
enable_material | boolean on | off |
No | No |
enable_mergejoin | boolean on | off |
No | No |
enable_nestloop | boolean on | off |
No | No |
enable_seqscan | boolean on | off |
No | No |
enable_sort | boolean on | off |
No | No |
enable_tidscan | boolean on | off |
No | No |
force_parallel_mode | enumeration off | on | regress |
No | No |
from_collapse_limit | integer 1 ... 2147483647 |
No | No |
geqo | boolean on | off |
No | No |
geqo_effort | integer 1 ... 10 |
No | No |
geqo_generations | integer 0 ... 2147483647 |
No | No |
geqo_pool_size | integer 0 ... 2147483647 |
No | No |
geqo_seed | float 0.0 ... 1.0 |
No | No |
geqo_selection_bias | float 1.5 ... 2.0 |
No | No |
geqo_threshold | integer 2 ... 2147483647 |
No | No |
gin_fuzzy_search_limit | integer 0 ... 2147483647 |
No | No |
gin_pending_list_limit | integer 64 ... 2147483647 KB |
No | No |
hot_standby_feedback | boolean on | off |
No | No |
idle_in_transaction_session_timeout | integer 0 ... 2147483647 ms |
No | No |
join_collapse_limit | integer 1 ... 2147483647 |
No | No |
lock_timeout | integer 0 ... 2147483647 ms |
No | No |
log_autovacuum_min_duration | integer 0 ... 2147483647 ms, or -1 to disable |
No | No |
log_checkpoints | boolean on | off |
No | No |
log_connections | boolean on | off |
No | No |
log_disconnections | boolean on | off |
No | No |
log_duration | boolean on | off |
No | No |
log_error_verbosity | enumeration terse | default | verbose |
No | No |
log_executor_stats | boolean on | off |
No | No |
log_hostname | boolean on | off |
No | No |
log_lock_waits | boolean on | off |
No | No |
log_min_duration_statement | integer -1 ... 2147483647 ms |
No | No |
log_min_error_statement | enumeration debug5 | debug4 | debug3 |
debug2 | debug1 | info |
notice | warning | error |
log | fatal | panic |
No | No |
log_min_messages | enumeration debug5 | debug4 | debug3 |
debug2 | debug1 | info |
notice | warning | error |
log | fatal | panic |
No | No |
log_parser_stats | boolean on | off |
No | No |
log_planner_stats | boolean on | off |
No | No |
log_replication_commands | boolean on | off |
No | No |
log_statement | enumeration none | ddl | mod | all |
No | No |
log_statement_stats | boolean May not be enabled together with log_parser_stats , log_planner_stats , or log_executor_stats . |
No | No |
log_temp_files | integer 0 ... 2147483647 KB, or -1 to disable |
No | No |
maintenance_work_mem | integer 1024 ... 2147483647 KB |
No | Yes |
max_connections | integer 14 ... varies (see note)
The value on replicas must be >= 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 | Yes |
max_locks_per_transaction | integer 10 ... 2147483647
The value on replicas must be >= 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 | Yes |
max_prepared_transactions | integer 0 ... 262143
The value on replicas must be >= 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 | Yes |
max_standby_archive_delay | integer 0 ... 2147483647 ms,
or -1 to wait forever |
No | No |
max_standby_streaming_delay | integer 0 ... 2147483647 ms, or -1 to wait forever |
No | No |
max_wal_size | integer 2 ... 2147483647
Unit is 16 MB for Postgres 9.6; unit default size is 1 MB for Postgres 11 (default) |
No | Yes |
min_parallel_relation_size | integer 0 ... 715827882
Unit is 8 KB |
No | No |
old_snapshot_threshold | integer 0 ... 86400 min, or -1 to disable |
Yes | No |
parallel_setup_cost | float 0.0 ... inf |
No | No |
parallel_tuple_cost | float 0.0 ... inf |
No | No |
pg_stat_statements.max | integer 100 ... 2147483647 |
Yes | No |
pg_stat_statements.save | boolean on | off |
No | No |
pg_stat_statements.track | enumeration none | top | all |
No | No |
pg_stat_statements.track_utility | boolean on | off |
No | No |
random_page_cost | float 0.0 ... inf |
No | No |
replacement_sort_tuples | integer 0 ... 2147483647 |
No | No |
standard_conforming_strings | boolean on | off |
No | No |
synchronize_seqscans | boolean on | off |
No | No |
temp_buffers | integer 100 ... 1073741823
Unit is 8 KB |
No | Yes |
temp_file_limit | integer 1048576 ... 2147483647 KB |
No | No |
trace_notify | boolean on | off |
No | No |
trace_recovery_messages | enumeration debug5 | debug4 | debug3 |
debug2 | debug1 | log |
notice | warning | error |
No | No |
trace_sort | boolean on | off |
No | No |
track_activities | boolean on | off |
No | No |
track_activity_query_size | integer 100 ... 102400 |
Yes | No |
track_commit_timestamp | boolean on | off |
Yes | No |
track_counts | boolean on | off |
No | No |
track_functions | enumeration none | pl | all |
No | No |
track_io_timing | boolean on | off |
No | No |
vacuum_cost_delay | integer 0 ... 100 ms |
No | No |
vacuum_cost_limit | integer 1 ... 10000 |
No | No |
vacuum_freeze_min_age | integer 0 ... 1000000000 |
No | No |
vacuum_freeze_table_age | integer 0 ... 2000000000 |
No | No |
vacuum_multixact_freeze_min_age | integer 0 ... 1000000000 |
No | No |
vacuum_multixact_freeze_table_age | integer 0 ... 2000000000 |
No | No |
work_mem | integer 64 ... 2147483647 KB |
No | Yes |
What's next
- Learn more about PostgreSQL server configuration.