Set up BigQuery CDC via Datastream to replicate Postgres or MySQL changes continuously

Steps

1. Create a Datastream connection profile for the source database (Postgres or MySQL), providing host, port, credentials, and SSL configuration; test the connectivity from the Datastream UI or gcloud CLI.
2. Enable the required source prerequisites: for Postgres, set wal_level=logical and create a replication slot and publication; for MySQL, enable binary logging with binlog_format=ROW.
3. Create a Datastream stream, selecting the connection profile, the target BigQuery dataset, and the tables to replicate; choose backfill mode (automatic for initial snapshot, or manual to skip history).
4. Datastream writes change events into BigQuery using the Storage Write API in a merge-mode destination table; each table gets _metadata_timestamp, _metadata_operation, and other CDC system columns alongside the row data.
5. Enable the BigQuery destination merge mode to have Datastream automatically consolidate inserts, updates, and deletes into a single up-to-date view of the table rather than an append-only changelog.
6. Monitor stream status, latency, and error logs in the Datastream console; set up Cloud Monitoring alerts on throughput drops or replication lag exceeding your SLA.