AWS RDS/Aurora PostgreSQL → Datastream → BigQuery
A detailed, explained implementation guide — every command with what it does and why.
On this page
- 0. Why are we doing this at all? (The big picture)
- 0.1 Why not just add an AWS read replica? (The alternative we rejected)
- 1. Prerequisites & decisions — and why each matters
- 2. Phase 1 — Prepare the PostgreSQL source
- 2.1 Enable logical replication
- Command 1 — create a custom parameter group
- Command 2 — turn on logical replication inside the group
- Command 3 — attach the group to the instance
- Command 4 — reboot to activate
- 🖱️ Same thing in the AWS Console (UI)
- 🔍 Diagnostic — which of the four steps is missing?
- 🖱️ How to run the SQL steps §2.2–§2.5 (there is no cloud‑console button for these)
- 2.2 Create a least‑privilege replication user
- 2.3 Create a publication
- 2.4 Create a logical replication slot
- 2.4b Where these two names go — the Datastream “Configure source” screen
- 2.5 Ensure tables have primary keys (REPLICA IDENTITY)
- 2.1 Enable logical replication
- 3. Phase 2 — Network connectivity (security + cost reasoning)
- 4. Phase 3 — Google Cloud setup
- 5. Phase 4 — Connection profiles (why two of them)
- 6. Phase 5 — Create & start the stream (decisions + why)
- 7. Phase 6 — Backfill + CDC (why two stages)
- 8. Phase 7 — Validation & cost levers (with the why)
- 9. Phase 8 — Monitoring & alerting (what & why)
- 10. Phase 9 — Security hardening (the reasoning)
- 11. Troubleshooting (symptom → why → fix)
- 12. Go‑live checklist
- Appendix A — Fix‑up cheat sheet (dropping & recreating objects)
Purpose: Not just how to set up the pipeline, but why each command exists and what each flag does — so the team understands the moving parts, can troubleshoot confidently, and can defend the design.
0. Why are we doing this at all? (The big picture)
The problem. Our production data lives in PostgreSQL on AWS RDS/Aurora. That database is built for OLTP — fast, small, transactional reads/writes for the app (insert an order, update a user). It is not built for analytics — scanning millions of rows for dashboards, reports, or ML. Running heavy analytical queries directly on production would:
- Slow down the app (analytics competes with live traffic for CPU/IO),
- Be slow and expensive (row‑based storage is bad at “sum this column across 50M rows”),
- Risk outages (a runaway report can lock or exhaust the production DB).
The solution. Continuously copy data, in near‑real‑time, into BigQuery — purpose‑built for analytics:
- Columnar storage → reading one column across millions of rows is fast/cheap.
- Separated storage & compute → analytics never touches the production DB.
- Massively parallel → dashboards/ML on huge datasets in seconds.
Why CDC and not a nightly dump? A nightly pg_dump means data is up to 24h stale and re‑copies the whole DB every night. Instead we use Change Data Capture (CDC): we read PostgreSQL’s write‑ahead log (WAL) — the log Postgres already writes for crash recovery — and stream only the changes (insert/update/delete) as they happen. Fresh within seconds–minutes, moving only the delta.
Why Datastream? Serverless (no Kafka/servers), native to BigQuery (auto‑MERGE handles updates/deletes), cheapest at our scale (~$0.018/GB change, no fixed minimum), and it lives in the same GCP project BigQuery already requires — so it adds no new cloud to maintain.
┌──────────────────────┐ reads WAL ┌──────────────────────┐ native MERGE ┌──────────────────────┐
│ RDS/Aurora Postgres │ ────────────▶ │ Datastream (GCP, │ ──────────────▶ │ BigQuery │
│ (OLTP, production) │ logical │ serverless CDC) │ upsert/delete │ (OLAP, analytics) │
└──────────────────────┘ replication └──────────────────────┘ └──────────────────────┘
source of truth delta only no servers dashboards, BI, ML
0.1 Why not just add an AWS read replica? (The alternative we rejected)
This is the first question anyone asks: “We already run RDS. Why not spin up a read replica and point the departments at it?” It is the right question — a replica is less work than a whole CDC pipeline. Here is the reasoning, so we can defend the choice.
What each option actually charges you for.
| RDS read replica | BigQuery via Datastream CDC | |
|---|---|---|
| Cost shape | Fixed. A replica is a second full instance — you pay its hourly rate 24/7, whether anyone queries it or not, plus a full second copy of the storage, plus provisioned IOPS. Cross-AZ replication traffic is billed too. | Variable. Storage is cheap per GB-month; compute is billed per query. A department that runs nothing that week costs ~nothing. |
| Scaling to more departments | Add users → add connections. Postgres allocates a backend process per connection; heavy analytical sessions are expensive. Enough demand and you need a second replica — another full instance. | Concurrency is elastic and shared. 5 analysts or 50 is the same infrastructure. |
| Speed of the actual work | Row-based storage. A replica moves analytics off the primary, but “sum this column across 50M rows” is still just as slow — it now merely saturates the replica instead. | Columnar + massively parallel. That same scan reads one column and returns in seconds. |
| Access control | Postgres GRANTs, per role per database. You are issuing DB credentials to an instance that shares its lineage with production. | Per-dataset IAM via service accounts — one scoped credentials.json per consumer (see the companion guide). |
The failure mode that decides it. Long analytical queries on a Postgres replica collide with replication replay. You get one of two bad outcomes, and you must pick one:
- Leave the defaults → the replica cancels the department’s report mid-flight:
ERROR: canceling statement due to conflict with recovery(max_standby_streaming_delay). - Turn on
hot_standby_feedback→ the replica tells the primary to hold back vacuum while that report runs. Now a slow query in the finance department causes table bloat on production.
That is the crux: a read replica never fully decouples analytics from the primary. BigQuery does — nothing an analyst runs can reach back and touch the OLTP database.
Being fair to the replica. It is not always wrong:
- It is transactionally consistent and current; our pipeline is minutes stale (§8’s freshness dial). If a department needs read-your-writes or point lookups on live state, a replica is the correct tool.
- If the dataset is small and query volume is low but constant, a small replica can genuinely be cheaper than streaming egress + MERGE costs.
- CDC is not free: Datastream per-GB of change (~$0.018/GB), AWS egress (~$0.09/GB), plus BigQuery storage and MERGE. §8 exists because those dials matter.
- CDC adds an operational duty a replica does not have: a replication slot that stops being consumed retains WAL and can fill the primary’s disk (§11). It must be monitored.
So — does it actually cost less? Yes. Worked example, for a ~500 GB database with a moderate change rate, serving a handful of departments. (All prices are list, us-east-1 / US multi-region, and are approximate — verify against current AWS and GCP pricing pages before quoting them to finance.)
| Option A — RDS read replica | Monthly |
|---|---|
db.r6g.large instance (2 vCPU / 16 GB), on-demand, 730 hrs | ~$190 |
| Replica storage — a full second copy, 500 GB gp3 | ~$58 |
| Cross-AZ replication traffic | ~$10–30 |
| Total — charged whether or not anyone queries | ~$260 – $280 / month |
| Option B — Datastream → BigQuery | Monthly |
|---|---|
| Datastream change processing, ~30 GB of delta @ ~$0.018/GB | ~$0.55 |
| AWS egress, ~30 GB @ ~$0.09/GB | ~$2.70 |
| BigQuery storage, 500 GB active @ ~$0.02/GB (less if compressed/long-term) | ~$10 |
| BigQuery compute — the MERGE jobs Datastream runs, driven by the §8 freshness dial | ~$5–30 |
| Department queries, on-demand @ ~$6.25/TB scanned (first 1 TB/month free) | ~$0–25 |
| Total — and most of it scales down when usage is quiet | ~$20 – $70 / month |
Roughly a 4–10× difference, and the gap widens with every additional department, because Option A’s answer to “more users” is a bigger instance or a second replica, while Option B’s is nothing at all.
What would flip the answer to “no”:
| Condition | Why it flips |
|---|---|
| Very high change volume (hundreds of GB of WAL delta/month) | Egress at ~$0.09/GB and MERGE compute both scale with the delta — 500 GB of change is ~$45 egress alone. Fix: replicate fewer tables (§6), skip churny audit/session logs. |
| Very aggressive freshness (seconds instead of minutes) | More frequent MERGEs, each re-scanning partitions. This is the #1 cost dial (§8). |
| Departments running big unpartitioned scans repeatedly | On-demand billing is per TB scanned — a 2 TB table scanned daily is ~$375/month by itself. Fix: partition + cluster, set maximum bytes billed, or move to a flat-rate reservation. |
| Tiny database, low but constant query volume | Fixed cost amortizes well; a small replica may genuinely win. Not our case. |
Note that two of those four are things we control by configuration, not fate — which is the other half of the argument: Option B has cost dials, Option A has an instance size.
The rule of thumb. Fixed cost wins when usage is heavy, constant, and small-scoped. Variable cost wins when usage is bursty, spread across many consumers, and scans are large — which is exactly the “several departments, occasional reports and dashboards” shape we have. And once the requirement includes “different departments should see different data,” BigQuery wins on governance regardless of the cost arithmetic: scoped dataset access is a JSON key, not a bespoke set of production DB grants.
1. Prerequisites & decisions — and why each matters
| Decision | Why it matters |
|---|---|
| Engine = PostgreSQL (confirmed) | CDC = logical replication via WAL (not MySQL binlog). All steps below are Postgres‑specific. |
| Engine version (10+) | Logical replication + the pgoutput plugin require Postgres 10+. Older versions can’t do native logical decoding. |
| Which tables to replicate | Each replicated table = egress + storage + MERGE cost. Replicate only what analytics needs → cheaper and avoids leaking sensitive tables. |
| Latency target (seconds vs minutes) | Sets the freshness window — the single biggest cost lever (§8). |
| Connectivity method | Decides security posture and egress cost (§3). |
You need: rights to edit the RDS parameter group + security group, a DB superuser/rds_superuser to create the publication/slot, the gcloud CLI, and a psql client.
2. Phase 1 — Prepare the PostgreSQL source
2.1 Enable logical replication
Background — what a “parameter group” is. A normal PostgreSQL server keeps its settings in a file called postgresql.conf (wal_level, max_connections, etc.). On RDS you can’t touch that file — AWS manages the OS. Instead AWS exposes those settings through a parameter group: a named bundle of DB settings you attach to your instance. It is RDS’s replacement for postgresql.conf. To change any engine setting, you change the parameter group. That’s why turning on one setting takes four commands.
Command 1 — create a custom parameter group
aws rds create-db-parameter-group \
--db-parameter-group-name datastream-pg \
--db-parameter-group-family postgres15 \
--description "Datastream CDC params"
- What it does: creates a new, empty, editable settings bundle named
datastream-pgfor the Postgres 15 engine family. - Why we need it: every RDS instance starts on a default parameter group (e.g.
default.postgres15) that AWS locks — you cannot edit it (a guardrail so you don’t break a shared baseline). To change any setting you must first create your own group. This command only creates the container; it changes no setting yet. - Flag notes:
--db-parameter-group-family postgres15must match your engine version (usepostgres14on PG14, etc. — check viaaws rds describe-db-instances).--descriptionis just a human label.
Command 2 — turn on logical replication inside the group
aws rds modify-db-parameter-group \
--db-parameter-group-name datastream-pg \
--parameters "ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot"
- What it does: sets
rds.logical_replication = 1(on) inside the group you just made. - Why we need it: this is the actual goal.
rds.logical_replication=1makes Postgres raise its internalwal_leveltological, which makes the WAL detailed enough to decode each row change (“this row in this table changed from X to Y”). Without it, the WAL only supports crash recovery — not CDC. Datastream literally cannot work without this. - Why
ApplyMethod=pending-reboot(not a typo, not optional):wal_levelis a static parameter — Postgres can only read it at startup, never while running. So AWS can’t apply it live; it stages the change until a reboot. (Dynamic parameters can useApplyMethod=immediate; this one physically cannot.) At this point the setting is configured but not yet active, and the group isn’t even attached to the instance.
Command 3 — attach the group to the instance
aws rds modify-db-instance \
--db-instance-identifier YOUR_DB_INSTANCE \
--db-parameter-group-name datastream-pg --apply-immediately
- What it does: swaps the instance’s attached parameter group from the default to your
datastream-pg. - Why we need it: commands 1–2 created/configured the group, but the instance is still using the default group until you point it at the new one. Until this runs,
rds.logical_replication=1has zero effect — it lives in a group nothing is using. - Flag note:
--apply-immediatelyassociates the group now rather than at the next maintenance window. (The association applies, but the static parameter inside still needs the reboot below.)
Command 4 — reboot to activate
aws rds reboot-db-instance --db-instance-identifier YOUR_DB_INSTANCE
- What it does: restarts the database instance.
- Why we need it: because
wal_levelis static, Postgres only loads it when the process starts. The reboot is the moment the engine re‑reads config, seeswal_level=logical, and begins writing the richer WAL. No reboot = the setting stays “pending” = logical replication never turns on, and Datastream validation fails with “logical replication not enabled.” - ⚠️ A reboot is a brief outage (tens of seconds to ~2 min; less with Multi‑AZ). Schedule it in a low‑traffic window.
Aurora PostgreSQL: set
rds.logical_replication=1on the cluster parameter group instead of the instance one.
🖱️ Same thing in the AWS Console (UI)
The four CLI commands above map 1:1 to these clicks:
- Create the group → AWS Console → RDS → left nav Parameter groups → Create parameter group.
- Parameter group family:
postgres15(match your version) · Type: DB Parameter Group · Name:datastream-pg→ Create. (= Command 1)
- Parameter group family:
- Turn on the setting → click
datastream-pg→ in the search box typerds.logical_replication→ select it → Edit → set Value =1→ Save changes. (= Command 2; it’s automatically “pending‑reboot” because it’s a static parameter) - Attach to the instance → RDS → Databases → select your instance → Modify → scroll to Additional configuration → Database options → DB parameter group → choose
datastream-pg→ Continue → select Apply immediately → Modify DB instance. (= Command 3) - Reboot → RDS → Databases → select instance → Actions → Reboot → confirm. (= Command 4; causes the brief outage)
Aurora: in step 1 choose DB Cluster Parameter Group, and in step 3 modify the cluster (not the instance) to attach it.
Verify it worked:
SHOW wal_level; -- must return: logical
- Why: this is the single source of truth that the four commands succeeded.
logical= ready for CDC. Stillreplica= reboot incomplete or group not attached → recheck commands 3 & 4.
🔍 Diagnostic — which of the four steps is missing?
SHOW wal_level tells you it’s broken but not why. This does:
SELECT name, setting, pending_restart
FROM pg_settings
WHERE name IN ('wal_level', 'rds.logical_replication');
| What you see | What it means | What to do |
|---|---|---|
wal_level = logical | ✅ Done — commands 1–4 all landed | Proceed to §2.2 |
wal_level = replica, pending_restart = t | Parameter is set and attached; only the reboot is missing | Run command 4 only |
wal_level = replica, pending_restart = f, and rds.logical_replication shows off/0 | The group was never attached to this instance (or was never created/edited) | Run commands 1–3, then 4 |
rds.logical_replication row missing entirely | Not an RDS/Aurora instance, or an engine version that predates the parameter | Check you’re on RDS and Postgres 10+ |
Cross‑check from the AWS side — ParameterApplyStatus names the same state:
aws rds describe-db-instances --db-instance-identifier YOUR_DB_INSTANCE \
--query 'DBInstances[].DBParameterGroups'
pending-reboot = staged, needs command 4. Still showing default.postgres1X = command 3 never ran.
💡 Splitting the outage on a production DB.
--apply-immediatelyin command 3 does not restart anything — only command 4 does. So you can safely run commands 1–3 during business hours and defer the reboot to a low‑traffic window. Nothing takes effect until the reboot, so there’s no half‑applied state to worry about in between.
Symptom this prevents:
ERROR: logical decoding requires "wal_level" >= "logical"(SQLSTATE 55000) when you reach §2.4. That error means only this — the slot command itself is fine.
The four commands in one sentence: RDS won’t let you edit default settings, so you (1) create your own editable settings bundle, (2) switch on logical replication inside it, (3) attach it to your database, (4) reboot so Postgres actually loads it — after which the WAL is detailed enough for Datastream.
🖱️ How to run the SQL steps §2.2–§2.5 (there is no cloud‑console button for these)
Steps §2.2–§2.5 are SQL commands executed inside PostgreSQL — creating a DB user, publication, slot, and checking primary keys. These are not AWS/GCP console actions (the cloud console has no UI for “create a Postgres role”). You run them through a database client connected to your RDS endpoint as an admin/
rds_superuser. Your UI options:
- pgAdmin (free Postgres GUI) → connect to the RDS endpoint → open the Query Tool (right‑click the database → Query Tool) → paste the SQL → Execute (▶ / F5). You can also create the role visually under Login/Group Roles → Create, but SQL is faster and copy‑pasteable.
- DBeaver (free, multi‑DB GUI) → new PostgreSQL connection → open a SQL Editor → paste → Execute.
- RDS Query Editor → AWS Console → RDS → Query Editor — note: only works for Aurora (Serverless/compatible), not standard RDS PostgreSQL. For standard RDS, use pgAdmin/DBeaver/
psql.psql(CLI) →psql "host=YOUR_RDS_ENDPOINT port=5432 dbname=YOUR_DB user=admin sslmode=require"then paste the SQL.The commands below are identical regardless of which client you use — the “UI” here is just where you paste and run the SQL.
2.2 Create a least‑privilege replication user
-- ⚠️ Note: NO "WITH REPLICATION" here — on RDS/Aurora that clause always fails (see below).
CREATE USER datastream WITH LOGIN PASSWORD 'STRONG_PASSWORD_HERE';
GRANT USAGE ON SCHEMA public TO datastream;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO datastream;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO datastream;
-- ⚠️ RDS / Aurora ONLY — REQUIRED. This is what actually grants replication
-- capability. Without it Datastream's "replication slot status" check FAILS.
GRANT rds_replication TO datastream;
Line‑by‑line — what it does / why:
CREATE USER datastream WITH LOGIN ...- What: creates a dedicated login for the pipeline. A dedicated user (not your app/admin user) isolates the pipeline and makes audit logs unambiguous.
- ❗ Why NOT
WITH REPLICATION— this clause fails on RDS/Aurora, always. On a self‑managed Postgres you would addWITH REPLICATIONto grant the attribute that permits opening a replication stream. On RDS you cannot: Postgres only lets a role hand out the REPLICATION attribute if it holds that attribute itself, and on managed RDS/Aurora no role has it — not even the master user. Attempting it errors out:ERROR: permission denied to create role DETAIL: Only roles with the REPLICATION attribute may create roles with the REPLICATION attribute.(That is the PostgreSQL 16+ wording. On PG 15 and earlier the same situation reads
ERROR: must be superuser to create replication users.) - What to do instead: create the user plain, then grant
rds_replication(next bullet). The capability arrives through role membership rather than the attribute — so nothing is lost by omitting the clause. On RDSWITH REPLICATIONwouldn’t have stuck even if it were permitted.
GRANT rds_replication TO datastream;— ⚠️ AWS RDS / Aurora only, and easy to miss- What: grants the AWS‑managed
rds_replicationrole to your user. - Why: on managed RDS/Aurora you are not a true Postgres superuser, so you cannot set the
rolreplicationattribute directly (only a real superuser can) — which is exactly whyWITH REPLICATIONis rejected above. AWS instead grants replication capability through membership in therds_replicationrole. Without this grant, Datastream’s replication slot status validation fails even though the slot itself is fine. This single missing grant is the most common cause of that exact failure. - Who can run it: the RDS master user (it holds
rds_replicationwith admin option). If this grant itself fails, you’re connected as someone else — check withSELECT current_user;andSELECT pg_has_role(current_user,'rds_replication','USAGE');. - ❗ Don’t be fooled by
rolreplication = f: on RDS this column staysfeven when correctly configured, because the capability comes from the role membership, not the attribute. So the meaningful check is membership, not the flag:-- The check that actually matters on RDS — want to see 'rds_replication' in the output: SELECT r.rolname AS granted_role FROM pg_auth_members m JOIN pg_roles r ON r.oid = m.roleid JOIN pg_roles u ON u.oid = m.member WHERE u.rolname = 'datastream';(
SELECT rolreplication FROM pg_roles …returningfis normal on RDS and is not a failure — ignore it.) - Already created the user without it? No need to drop/recreate — just run
GRANT rds_replication TO datastream;.
- What: grants the AWS‑managed
GRANT USAGE ON SCHEMA public- What: lets the user “see into” the
publicschema. - Why: without schema USAGE, the user can’t reference any table inside it — every later grant would be meaningless.
- What: lets the user “see into” the
GRANT SELECT ON ALL TABLES ...- What: read permission on existing tables.
- Why: the initial backfill copies existing rows by running
SELECTagainst them. (CDC itself comes from the WAL, but the one‑time snapshot needs SELECT.) Read‑only = this account can never modify data, limiting blast radius if creds leak.
ALTER DEFAULT PRIVILEGES ... GRANT SELECT ...- What: auto‑grants SELECT on tables created in the future.
- Why: otherwise a newly created table silently fails to replicate until someone remembers to re‑grant. This makes the pipeline “just work” as the schema evolves.
2.3 Create a publication
-- Recommended: only the tables analytics needs
CREATE PUBLICATION datastream_pub FOR TABLE public.orders, public.customers, public.payments;
-- (Alternative: FOR ALL TABLES;)
- What it does: defines a publication — Postgres’s native list of tables whose changes it will publish to a logical subscriber (Datastream subscribes to this).
- Why we need it: it’s the on‑DB allow‑list of “what gets streamed.”
- Why the explicit table list is preferred over
FOR ALL TABLES:- Cost — every published table’s changes are streamed (egress $0.09/GB + BigQuery cost). Don’t pay to replicate churny tables you never analyze (audit/session logs).
- Security — keeps PII tables you don’t want in analytics out of BigQuery entirely.
- Predictability —
FOR ALL TABLESsilently auto‑includes future tables, which can be surprising; an explicit list is deterministic.
2.4 Create a logical replication slot
-- Name it after the database it belongs to, e.g. for banana-club-production-erp-db:
SELECT pg_create_logical_replication_slot('datastream_slot_erp', 'pgoutput');
- What it does: creates a replication slot — a server‑side bookmark named
datastream_slot_erpusing thepgoutputdecoder. - 📛 Naming rules (enforced by Postgres — get these wrong and the command errors):
- Lowercase letters, digits, and underscores only. No hyphens, no spaces, no uppercase. Max 63 characters.
- This matters because RDS database names often contain hyphens:
datastream_slot_banana-club-production-erp-dbis rejected. Use a short underscore form likedatastream_slot_erp. - Unique across the whole RDS instance — not per database. Two databases cannot both use
datastream_slot. - Pick a name that identifies the database (
datastream_slot_<short_db_name>), because §9 monitoring shows slots instance‑wide and you need to tell them apart at a glance.
- Why we need it: the slot records “the consumer has read WAL up to position X,” and Postgres then guarantees it won’t recycle/delete WAL beyond that point until the consumer confirms it processed it. So if Datastream disconnects (network blip, maintenance), the changes during the outage are still in the WAL on reconnect → no data loss / no gaps.
- Why
pgoutput: it’s Postgres’s built‑in logical‑decoding plugin (no extension to install) that turns raw WAL into structured row changes — exactly what Datastream reads. - ⚠️ The sharp edge (why §9 monitoring is mandatory): because the slot prevents WAL cleanup, a slot nobody reads (stream paused/broken for days) makes WAL pile up until the source disk fills, which can crash production. The slot is a safety feature you must monitor.
Verify it was created on the right database:
SELECT slot_name, database, active FROM pg_replication_slots;
active = f is expected at this point — it flips to t when the stream starts consuming.
2.4b Where these two names go — the Datastream “Configure source” screen
Datastream does not create the publication or the slot. You create them here in §2.3–§2.4, and the console form just asks which existing ones to attach to. Two different objects, two different fields:
| Datastream field | Value from above | What it actually is | Scope |
|---|---|---|---|
| Publication name | datastream_pub (§2.3) | The table allow‑list — which tables get streamed | Lives inside one database; the name may repeat in other databases |
| Replication slot name | datastream_slot_erp (§2.4) | The read position bookmark + the WAL‑retention guarantee | Unique across the entire instance; bound to the DB it was created in |
The distinction in one line: the publication decides what is sent; the slot tracks how far you’ve read. The slot has no idea which tables you want, and the publication has no idea what’s been consumed — Datastream needs both.
⚠️ One slot per database, one consumer per slot. Never point a second stream at an existing slot: a slot only decodes the database it was created in, so the stream will appear to run normally while BigQuery stays empty. Adding another database on this instance? See datastream-add-second-database-guide.md.
2.5 Ensure tables have primary keys (REPLICA IDENTITY)
-- Verify a primary key exists:
SELECT conname FROM pg_constraint
WHERE conrelid = 'public.orders'::regclass AND contype = 'p';
-- No PK but a unique column? Use it as the identity:
ALTER TABLE public.some_table REPLICA IDENTITY USING INDEX some_unique_idx;
-- Last resort (heavier WAL):
ALTER TABLE public.some_table REPLICA IDENTITY FULL;
- What it does: checks/sets how Postgres identifies which row an UPDATE/DELETE touched (its REPLICA IDENTITY, default = primary key).
- Why we need it: for UPDATE/DELETE, Postgres must tell the subscriber which row changed. With a PK it logs just the key. No PK → Postgres can’t identify the old row → Datastream can’t upsert/delete‑merge that table → it falls back to append‑only (the BigQuery copy won’t mirror updates/deletes).
- Why avoid
REPLICA IDENTITY FULLwhen possible: FULL logs the entire old row into the WAL to identify it — correct, but it bloats the WAL (more bytes = more egress/cost). Prefer a PK or unique index.
3. Phase 2 — Network connectivity (security + cost reasoning)
Datastream (GCP) must reach the RDS endpoint (AWS). Pick one:
| Method | What it is | Why you’d choose it |
|---|---|---|
| IP allowlist | Allow Datastream’s GCP IP ranges inbound on 5432 in the RDS security group | Fastest for a POC. Why caution: implies the DB is reachable from outside AWS — only OK with strict allowlist + TLS. |
| SSH bastion (forward tunnel) | Datastream SSHes into a small EC2 bastion that forwards to private RDS | Keeps the DB private (no public endpoint); cheap; good middle ground. |
| Private connectivity (VPC peering + VPN/interconnect) | A private network path between GCP and AWS VPCs | Production/compliance. Data never touches the public internet; can also lower egress cost at high volume. |
Always enable TLS/SSL. Why: the WAL stream contains your real row data; encrypting it in transit prevents interception, especially over any public path.
🖱️ IP‑allowlist method in the AWS Console (UI)
Once the Datastream source connection profile (§5) shows you its IP ranges, allowlist them on the DB:
- AWS Console → RDS → Databases → click your instance → Connectivity & security tab → click the VPC security group link (opens EC2 → Security Groups).
- Select the security group → Inbound rules tab → Edit inbound rules → Add rule.
- Type: PostgreSQL (auto‑fills port 5432) · Source: Custom → paste a Datastream IP range (add one rule per range) → Save rules.
- Ensure the DB is reachable on the chosen path. (For SSH‑bastion or private connectivity, you instead configure the EC2 bastion / VPN‑peering here rather than opening 5432 to Datastream IPs.)
Why this lives in AWS, not GCP: the firewall guarding the database is the RDS security group, an AWS object — so the allowlisting click‑path is in the AWS Console, even though the IPs you paste come from the GCP/Datastream side.
Why egress is a real line item: every GB of change data leaving AWS costs ~$0.09/GB — which is exactly why §2.3 (publish only needed tables) and §8 (tuning) directly cut the bill.
4. Phase 3 — Google Cloud setup
gcloud config set project YOUR_GCP_PROJECT_ID
gcloud services enable datastream.googleapis.com bigquery.googleapis.com
bq --location=US mk --dataset \
--description "Datastream replica of AWS RDS Postgres" \
YOUR_GCP_PROJECT_ID:rds_replica
gcloud config set project ...- What: points the CLI at your project. Why: so every following command bills/creates resources in the right place (avoids accidentally provisioning in the wrong project).
gcloud services enable datastream... bigquery...- What: turns on the Datastream and BigQuery APIs. Why: GCP services are off by default; they can’t be used or billed until explicitly enabled. This is a one‑time per‑project step.
bq ... mk --dataset ... rds_replica- What: creates the destination BigQuery dataset. Why: a dedicated dataset keeps the mirror logically separate from other analytics data, makes permissions easy to scope, and signals “this is a replica — don’t hand‑edit it.”
- Why
--locationnear your AWS region: putting the dataset geographically close to the source reduces latency and can reduce cross‑region/egress cost; keeping all GCP resources in one region avoids cross‑region query fees.
🖱️ Same thing in the Google Cloud Console (UI)
- Pick the project → top bar project picker → select your project. (=
gcloud config set project) - Enable the APIs → ☰ menu → APIs & Services → Enabled APIs & services → + Enable APIs and services → search “Datastream API” → Enable; repeat for “BigQuery API”. (Shortcut: just open Datastream from the menu — if the API is off it shows an Enable button.) (=
gcloud services enable) - Create the dataset → ☰ menu → BigQuery → in the Explorer panel click the ⋮ next to your project → Create dataset → Dataset ID:
rds_replica· Location type/region: match your choice · Create dataset. (=bq mk --dataset)
5. Phase 4 — Connection profiles (why two of them)
# SOURCE — AWS Postgres
gcloud datastream connection-profiles create aws-rds-pg-source \
--location=us-central1 --type=postgresql \
--postgresql-hostname=YOUR_RDS_ENDPOINT.amazonaws.com \
--postgresql-port=5432 \
--postgresql-username=datastream \
--postgresql-password=STRONG_PASSWORD_HERE \
--postgresql-database=YOUR_DB \
--display-name="AWS RDS Postgres source"
# DESTINATION — BigQuery
gcloud datastream connection-profiles create bigquery-dest \
--location=us-central1 --type=bigquery \
--display-name="BigQuery destination"
- What it does: defines two reusable, named endpoint/credential objects — one describing how to reach Postgres, one describing where to write (BigQuery).
- Why two separate profiles: separating source and destination lets you reuse the same source for multiple streams, rotate credentials in one place, and run a connectivity test before building the stream — catching network/permission problems early instead of at stream‑start.
- Flag notes: the source flags are just the connection details for the
datastreamuser from §2.2;--postgresql-databasenames the DB to connect to. The BigQuery profile needs no credentials because it authenticates via the project’s IAM/service account.
🖱️ Same thing in the Google Cloud Console (UI)
Source profile (PostgreSQL):
- ☰ menu → Datastream → Connection profiles → + Create profile.
- Choose PostgreSQL as the profile type.
- Fill in: Profile name
aws-rds-pg-source· Regionus-central1· Hostname (your RDS endpoint) · Port5432· Usernamedatastream· Password · Database. - Define connectivity method → pick IP allowlist, Forward‑SSH tunnel, or Private connectivity (matches §3). For IP allowlist, the console shows the Datastream IP ranges here — copy them into your RDS security group.
- Run test → confirms Datastream can actually reach the DB → Create.
Destination profile (BigQuery):
- Connection profiles → + Create profile → BigQuery.
- Profile name
bigquery-dest· Regionus-central1→ Create. (No credentials needed — it uses the project’s service account, and the console wires the IAM for you.)
Why the UI is nice here: it surfaces the exact Datastream IP ranges to allowlist, runs the connectivity test inline, and auto‑creates the IAM bindings the BigQuery destination needs — things you’d otherwise do by hand with the CLI.
6. Phase 5 — Create & start the stream (decisions + why)
This phase is already a Console (UI) flow — here’s the click path, then the decision reasoning.
🖱️ In the Google Cloud Console (UI)
- ☰ menu → Datastream → Streams → + Create stream.
- Stream details: name
rds-to-bq, regionus-central1, source type PostgreSQL, destination BigQuery → Continue. - Define source: pick the
aws-rds-pg-sourceprofile → Run test. - Configure source: select the schemas/tables to include (only what analytics needs).
- Define destination: pick the
bigquery-destprofile. - Configure destination: choose the target dataset (
rds_replica), write mode (Merge vs Append‑only), and the data freshness / staleness window (start 5–15 min). - Review → Validate (checks
wal_level, grants, PKs, connectivity) → fix anything flagged. - Create → then Start the stream.
The decisions on that screen, and why:
| Choice | Recommended | Why |
|---|---|---|
| Source/destination profiles | the two above | Ties the pipeline together; runs validation |
| Objects to include | only needed schemas/tables | Cost + security (same reasoning as the publication) |
| Write mode | Merge (upsert) | BigQuery table mirrors Postgres — updates/deletes reflected. Requires PKs (§2.5). Use Append‑only only if you want full change history/audit. |
| Data freshness / staleness | start 5–15 min | The #1 cost dial — lower = fresher but more frequent (costly) MERGEs. Most analytics is fine with minutes. |
| Backfill | all, or exclude huge cold tables | Backfill = one‑time bulk SELECT copy; skipping giant unneeded tables saves time/egress |
Why run validation first: it checks the prerequisites (wal_level=logical, user rights, table PKs, port reachable) before the stream runs, so you fix config instead of debugging a half‑working pipeline.
7. Phase 6 — Backfill + CDC (why two stages)
- Backfill (snapshot) — one‑time
SELECTcopy of existing rows. Why: the WAL/slot only contains changes from the moment the slot was created onward; backfill brings the pre‑existing history so BigQuery starts complete. - CDC (streaming) — continuous WAL decoding of every commit. Why: this is the ongoing near‑real‑time sync that keeps BigQuery current forever after.
The two reconcile automatically (changes during backfill aren’t lost). Watch per‑table progress (backfill % → “CDC”) in the stream’s Objects view.
8. Phase 7 — Validation & cost levers (with the why)
-- Parity check
SELECT COUNT(*) FROM orders; -- source
SELECT COUNT(*) FROM `YOUR_GCP_PROJECT_ID.rds_replica.orders`; -- BigQuery
-- Live-change test (in merge mode the row should appear, change, then disappear in BigQuery)
INSERT INTO orders (id, status) VALUES (999999, 'TEST');
UPDATE orders SET status='TEST2' WHERE id=999999;
DELETE FROM orders WHERE id=999999;
- Why these tests: the count proves backfill landed everything; the insert/update/delete proves CDC + merge are working end‑to‑end within your freshness window (and that deletes propagate).
Cost levers — why each works:
| Lever | Why it lowers cost |
|---|---|
| Replicate only needed tables | Fewer bytes crossing AWS→GCP (egress $0.09/GB) + less BigQuery storage |
| Raise the freshness window | Datastream MERGEs per window; fewer, larger MERGEs scan/rewrite far less than many tiny ones → cuts MERGE compute 5–10× |
| Partition + cluster BigQuery tables | MERGE/queries scan only relevant partitions, not the whole table |
Avoid REPLICA IDENTITY FULL | FULL logs the whole old row into WAL → more bytes to stream |
| Skip high‑churn noise tables | A table updated thousands/sec but never queried is pure cost, no value |
Mental model: cost ≈ (bytes of change streamed) + (how often we MERGE) + (how much each MERGE scans). Every lever attacks one of those three terms.
9. Phase 8 — Monitoring & alerting (what & why)
| Watch | Why it’s critical |
|---|---|
| Stream freshness/latency | The core health metric — tells you if BigQuery is falling behind SLA |
| Stream state ≠ RUNNING | A paused/failed stream = stale data and a growing slot (next row) |
| Replication‑slot lag / retained WAL | ⚠️ The one that can crash production — a stalled stream + slot piles up WAL until the source disk fills. Alert early; have a runbook to restart the stream / drop orphan slots |
| BigQuery storage growth + query bytes | Catch runaway cost; set a billing budget alert |
| Datastream “unsupported events” count | Surfaces data types/DDL it couldn’t handle so you address them deliberately |
🖱️ Setting alerts in the Console (UI)
- Stream health at a glance: ☰ → Datastream → Streams → click your stream → the Monitoring tab shows throughput, freshness, and unsupported events.
- Create an alert: ☰ → Monitoring → Alerting → + Create policy → Select a metric → search
datastream(for freshness/state) orbigquery(for storage/bytes) → set a threshold (e.g. freshness > 15 min) → add a notification channel (email/Slack/PagerDuty) → Create policy. - Billing budget: ☰ → Billing → Budgets & alerts → Create budget → scope to the project → set an amount and alert thresholds (e.g. 50/90/100%).
- Postgres slot/WAL (the disk‑fill risk): this lives on the AWS side — watch RDS → your instance → Monitoring for
FreeStorageSpace, and querypg_replication_slotsfor slot lag.
10. Phase 9 — Security hardening (the reasoning)
- Least‑privilege user (read‑only + replication) → leaked creds can’t modify data.
- Password in Secret Manager, not scripts → secrets stay out of git/shell history.
- Private connectivity + TLS → the WAL carries real row data; keep it off the public internet and encrypted.
- Scope BigQuery IAM to the one dataset (
roles/bigquery.dataEditoronrds_replica) → the pipeline writes its mirror and nothing else. - Enable audit logs → provable record of what the pipeline accessed, for compliance.
11. Troubleshooting (symptom → why → fix)
| Symptom | Why it happens | Fix |
|---|---|---|
CREATE USER: “permission denied to create role” / “Only roles with the REPLICATION attribute may create roles with the REPLICATION attribute” (PG16+; PG≤15 says “must be superuser to create replication users”) | You used WITH REPLICATION. On RDS/Aurora no role holds that attribute — not even the master user — so no role can grant it. | Drop the clause: CREATE USER datastream WITH LOGIN PASSWORD '…'; then GRANT rds_replication TO datastream; (§2.2). Nothing is lost — on RDS the capability comes from that membership, not the attribute. |
GRANT rds_replication also fails | You’re not connected as the RDS master user. | SELECT current_user; and SELECT pg_has_role(current_user,'rds_replication','USAGE'); → reconnect as master. |
| Validation: “replication slot status: Failed” | The rds_replication role was never granted to the user (RDS doesn’t honor WITH REPLICATION alone). Slot can be perfectly fine and this still fails. | GRANT rds_replication TO datastream; → verify membership (not the rolreplication flag, which stays f on RDS): SELECT r.rolname FROM pg_auth_members m JOIN pg_roles r ON r.oid=m.roleid JOIN pg_roles u ON u.oid=m.member WHERE u.rolname='datastream'; should list rds_replication → re‑run validation. |
| Validation: “backfill permissions: Failed” | Missing/partial SELECT grant, or table created after the grant, or no schema USAGE. | Re‑run GRANT USAGE + GRANT SELECT ON ALL TABLES + ALTER DEFAULT PRIVILEGES … GRANT SELECT; verify SELECT has_table_privilege('datastream','public.orders','SELECT'); → t. Run on the correct database, not postgres. |
Slot shows active = false | Not an error — just means Datastream hasn’t connected/consumed yet. Becomes true once the stream starts. | No action needed before go‑live. |
| Validation: “logical replication not enabled” | wal_level still replica (no reboot) | Confirm rds.logical_replication=1, reboot, recheck SHOW wal_level; |
Slot creation: logical decoding requires "wal_level" >= "logical" (SQLSTATE 55000) | Same root cause — §2.1 incomplete. The slot command is correct; the engine simply isn’t in logical mode yet. | Run the §2.1 diagnostic to see whether you need commands 1–3 or just the reboot. Re‑run the slot command after SHOW wal_level; → logical. |
| Connectivity test fails | Security group blocks Datastream IPs / wrong path | Allowlist Datastream IPs on 5432, or fix SSH/private path |
| Table is append‑only unexpectedly | No PK → no REPLICA IDENTITY | Add a PK or REPLICA IDENTITY USING INDEX |
| Source disk filling up | Stalled stream + slot retaining WAL | Restart/fix stream; drop orphan slots; alert sooner |
| New tables not replicating | Not in publication / no SELECT grant | Add to publication; ensure ALTER DEFAULT PRIVILEGES was set |
| High BigQuery bill | MERGE too frequent (freshness too low) | Increase the freshness window |
| Rows never update in BigQuery | Append‑only mode or no PK | Confirm merge mode + PK on the table |
12. Go‑live checklist
rds.logical_replication=1set and instance rebooted (SHOW wal_level;→logical)- Least‑privilege
datastreamuser created withoutWITH REPLICATION(that clause always errors on RDS), with SELECT + default privileges, andGRANT rds_replicationrun as the master user → user is a member ofrds_replication(note:rolreplicationstaysfon RDS — that’s fine) - Publication created (explicit table list preferred)
- Logical replication slot created (
pgoutput) with a DB‑identifying, instance‑unique name — lowercase/digits/underscores only, no hyphens (e.g.datastream_slot_erp), verified on the right DB viapg_replication_slots - Publication name and slot name both entered on the stream’s Configure source screen (§2.4b) — they are two different objects, not one
- All replicated tables have a primary key (or deliberate REPLICA IDENTITY)
- Network path chosen (allowlist / SSH / private) + TLS enabled
- GCP APIs enabled; BigQuery dataset created in the right region
- Source + BigQuery connection profiles pass connectivity tests
- Stream created: merge mode + sensible freshness window; validation passed
- Backfill complete; row counts match; live insert/update/delete verified
- Alerts set: freshness, stream state, replication‑slot/WAL size, billing budget
- Secrets in Secret Manager; IAM scoped to the dataset; audit logs on
- Runbook written for “restart stream / clean up slot”
One‑paragraph summary (why this design is right for us)
We keep PostgreSQL as the system of record for the app and continuously mirror it into BigQuery for analytics using Datastream’s WAL‑based CDC — so analytics never burdens production, dashboards are fresh within minutes, and we only ever move the changed data. Datastream is serverless, native to BigQuery, lives in the GCP project we already need, and is the cheapest option at our scale. The few rules we must respect — logical replication on, primary keys present, the replication slot monitored, and the freshness window tuned — are exactly the levers that keep it correct, safe, and cost‑effective.
Appendix A — Fix‑up cheat sheet (dropping & recreating objects)
Setup is rarely clean on the first pass — you create a publication with the wrong scope, or a slot with the wrong name. These are the corrective commands. All of them are cheap and safe before the stream starts, and progressively less so afterwards.
A.1 Inspect what actually exists
Always start here — most “errors” are objects sitting in the wrong database.
SELECT current_database(); -- are you even on the right DB?
SELECT current_user; -- do you have the rights for what follows?
-- Publications (per-database)
SELECT pubname, puballtables, pubowner::regrole FROM pg_publication;
SELECT schemaname, tablename FROM pg_publication_tables WHERE pubname = 'datastream_pub';
-- Slots (instance-wide — shows every database's slots)
SELECT slot_name, database, active,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;
A.2 Drop & recreate a publication
DROP PUBLICATION IF EXISTS datastream_pub;
SELECT current_database(); -- confirm before recreating!
CREATE PUBLICATION datastream_pub FOR ALL TABLES;
- Who can: the publication’s owner, or the RDS master user.
must be owner of publication …→ checkpubowner::regrolein A.1 and reconnect as that role. FOR ALL TABLESstill needsrds_superuser— dropping first does not work around that error. If that was your original failure, use the explicit list generator in §A.4 instead.- Dropping a publication does NOT affect the replication slot. They are independent objects; the slot survives untouched.
A.3 Drop & recreate (i.e. “rename”) a slot
PostgreSQL has no ALTER … RENAME for replication slots. Renaming = drop + recreate.
SELECT pg_drop_replication_slot('old_slot_name');
SELECT pg_create_logical_replication_slot('datastream_slot_erp', 'pgoutput');
- Only works while
active = f. Postgres refuses to drop a slot with a live consumer — so stop the stream first if one is attached. - Free before the first stream start, lossy after. An unconsumed slot holds no meaningful read position, so recreating loses nothing. Once a stream has been consuming, dropping discards the position and changes since then are gone — you’d need a fresh backfill.
- Drop orphan slots immediately. A slot with no consumer pins WAL forever and grows until it fills the instance disk (§2.4). Two slots on one database means one of them is an orphan — get rid of it.
A.4 Generate an explicit FOR TABLE list (no superuser needed)
SELECT 'CREATE PUBLICATION datastream_pub FOR TABLE '
|| string_agg(format('%I.%I', schemaname, tablename), ', ') || ';'
FROM pg_tables WHERE schemaname = 'public';
-- copy the generated statement, then run it
A.5 Safety rules
| Situation | Publication | Slot |
|---|---|---|
| Before the stream is created | Drop/recreate freely | Drop/recreate freely |
| Stream created but never started | Safe | Safe |
| Stream RUNNING | ⚠️ Dropping breaks the stream — stop it first | ⚠️ Can’t drop (active); stopping + dropping forces a full re-backfill |
The recurring root cause: publications, slots, and grants are all per‑database, and you operate on whichever database you’re currently connected to.
SELECT current_database();before every one of these commands — it costs nothing and prevents the majority of these fix‑ups in the first place.