Prepare and test your skills

Prepare and test your skills

Free sample question with answers and detailed explanation below. The Professional Cloud Database Engineer practice bank has an additional 769 practice questions.Try 10 free realistic questions: instant score, no credit card, and no account required.
Your organization recently migrated a mission-critical database to a standalone Cloud SQL for PostgreSQL primary instance. To support a robust fallback strategy after cutover, you need to configure continuous reverse replication from the Cloud SQL primary instance back to the original on-premises PostgreSQL server using pglogical. Your configuration must satisfy these requirements:
pglogical extension within Cloud SQL's managed constraints.Which configuration should you apply to the Cloud SQL for PostgreSQL primary instance?
cloudsql.logical_decoding and cloudsql.enable_pglogical flags to on, configure max_replication_slots, max_worker_processes, and max_wal_senders, restart the instance, create the pglogical extension, and initialize a provider node.Configuring reverse replication from a Cloud SQL for PostgreSQL instance to an external PostgreSQL server relies on logical decoding and the pglogical extension. Because Cloud SQL is a managed service, it restricts direct access to certain low-level server configuration files and uses dedicated cloudsql.* database flags to safely expose logical replication capabilities.
cloudsql.logical_decoding to on instructs PostgreSQL to write additional logical change information to the Write-Ahead Log (WAL). Setting cloudsql.enable_pglogical to on automatically loads the pglogical library on startup without requiring direct modification of shared_preload_libraries.max_replication_slots, max_worker_processes, and max_wal_senders ensures PostgreSQL reserves enough logical replication slots and background processes to track consumer progress and transmit decoded WAL events to the external subscriber.CREATE EXTENSION pglogical; and calling pglogical.create_node with a provider role establishes the Cloud SQL primary as the replication source.INSERT, UPDATE, and DELETE operations independently of physical storage, supporting replication even across different PostgreSQL major versions.pglogical replication sets, you can selectively replicate critical tables back to the on-premises environment to minimize recovery time during a fallback.Unlike standard self-managed PostgreSQL configurations or MySQL binary log setups, Cloud SQL for PostgreSQL requires these specific managed flags and a provider node on the primary instance. Without restarting the instance and initializing the extension and node, the external server cannot establish a pglogical subscription to receive continuous updates.
In Cloud SQL, automated backups and point-in-time recovery (PITR) capture periodic snapshots and continuous transaction logs so an instance can be restored to a specific timestamp. In Cloud SQL for MySQL, enabling PITR and automated backups is indeed the mechanism that turns on binary logging (binlog), which is required for MySQL read replicas and external replicas. However, PostgreSQL operates differently.
pglogical subscribers.pglogical with cloudsql.logical_decoding).cloudsql.enable_pglogical, max_replication_slots, and a pglogical provider node, the on-premises server has no endpoint or logical change stream to consume.cloudsql.logical_decoding=on and cloudsql.enable_pglogical=on (followed by a restart) to emit SQL-level changes to external replicas.gtid_mode=ON on the external replica are used instead of logical decoding.You were configuring reverse replication from a Cloud SQL for MySQL primary instance to an external MySQL replica, where enabling PITR and automated backups activates the required binary logs.
cloudsql.logical_decoding and cloudsql.enable_pglogical only on the read replica, and point the on-premises PostgreSQL server to replicate from the read replica.A Cloud SQL read replica is an asynchronous copy of a primary instance used primarily to offload read-only queries, reporting, and analytics traffic. While offloading replication overhead to a replica can seem attractive in a fallback architecture, Cloud SQL imposes strict architectural rules on external replication sources.
pglogical replication, logical decoding must be enabled on the primary instance generating the writes, not solely on a read replica.pglogical subscribers because they lack a consistent outgoing IP address.You were creating cascading Cloud SQL read replicas within Google Cloud to distribute internal read traffic across regions, rather than replicating to an external on-premises server.
wal_level to logical and add pglogical to shared_preload_libraries on the Cloud SQL instance, configure max_replication_slots, and create a pglogical subscription on the Cloud SQL instance.In self-managed PostgreSQL environments, administrators enable logical replication by editing postgresql.conf to set wal_level = logical and adding pglogical to shared_preload_libraries. Meanwhile, a pglogical subscription is created on the target (subscriber) database to pull changes from an upstream provider.
wal_level or shared_preload_libraries. Instead, Cloud SQL abstracts these settings behind managed flags (cloudsql.logical_decoding and cloudsql.enable_pglogical).pglogical subscription on the Cloud SQL primary instance would configure Cloud SQL to consume changes from another server rather than stream changes outward. For reverse replication, Cloud SQL must be initialized as the provider node, while the external on-premises server creates the subscriber node and subscription.max_wal_senders and max_worker_processes leaves the instance without sufficient process capacity for pglogical workers and replication connections.wal_level and shared_preload_libraries are managed internally by Cloud SQL to prevent misconfiguration and preserve SLA stability.pglogical can be created.pglogical nodes (provider and subscriber) must be created on their respective databases before a subscription can begin replicating.You were configuring an unmanaged or self-hosted PostgreSQL source (such as on a Compute Engine VM) as the provider, and creating the subscription on the target replica receiving the data.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated Validated