Examkin

Question

Difficulty: Very hardManaging Storage and Database Solutions

An operations engineer is managing a production Cloud SQL for PostgreSQL database instance. To analyze query performance bottlenecks, the engineer needs to enable the `pg_stat_statements` extension and adjust database configuration flags. Modifying `shared_preload_libraries` requires a database restart to take effect. Which sequence of administrative actions must the engineer execute to properly apply this configuration using GCP standard practices?

  1. Execute `gcloud sql instances patch` specifying `--database-flags=shared_preload_libraries=pg_stat_statements`, allow the automatic restart to complete, and then connect to the database to run `CREATE EXTENSION pg_stat_statements;`.Answer
  2. B
    Export a service account private JSON key, upload the `pg_stat_statements` library file using `gcloud storage cp` directly into the underlying instance filesystem, and execute `gcloud sql instances restart`.
  3. C
    Migrate the PostgreSQL database tables to a Cloud Bigtable instance using `gcloud bigtable instances update` so that PostgreSQL extensions can be loaded dynamically without database downtime.
  4. D
    Execute `gsutil setmeta` on the underlying database storage bucket to set `shared_preload_libraries=pg_stat_statements`, then run `gcloud sql flags list` to refresh database settings.

Answer

The engineer must run `gcloud sql instances patch` with the `--database-flags` parameter specifying `shared_preload_libraries=pg_stat_statements`, wait for the instance to restart, and then execute `CREATE EXTENSION pg_stat_statements;` within a SQL client session.
In Cloud SQL for PostgreSQL, configuring database flags that alter preloaded shared libraries requires updating instance flags via `gcloud sql instances patch`. Cloud SQL handles the required instance restart automatically when static flags are modified. Once restarted, running `CREATE EXTENSION pg_stat_statements;` inside a SQL connection enables the extension features.

Step-by-Step Solution

1
Configure the Cloud SQL instance database flag using the gcloud CLI tool.
The command `gcloud sql instances patch INSTANCE_NAME --database-flags shared_preload_libraries=pg_stat_statements` updates the instance configuration.
Cloud SQL manages engine configuration flags through instance patch operations.
2
Allow the managed instance restart to take place.
The database instance restarts automatically because `shared_preload_libraries` is a static PostgreSQL parameter requiring initialization at boot.
Static parameters in PostgreSQL cannot be loaded into memory without restarting the database daemon.
3
Connect to the database and register the extension.
Executing `CREATE EXTENSION pg_stat_statements;` enables the query monitoring views.
PostgreSQL requires explicit SQL DDL registration of preloaded shared libraries within the target database.

Key Concept

Cloud SQL Database Flag Management and Extension Lifecycle
Rate this question

Topics

Managing Storage and Database Solutions