01 Source environment and baseline

Every command in this module runs from the lab directory. On Windows that is the Ubuntu terminal, in the checkout under /home.

cd "$(git rev-parse --show-toplevel)/workshops/postgres_migration"

Outcome

One RDS Postgres instance running with wal_level = logical, a shop database seeded server-side to several times the instance's shared_buffers, a writer committing orders continuously, a Grafana dashboard with eight hand-written SQL panels reading the same instance, and one measured baseline: what the dashboard costs the checkout path.

That last number is the point of the whole module. A single database serving both workloads does not fail by making dashboards slow. It fails by letting dashboards steal throughput from checkout. You are about to measure the theft.

Two legs, one product, two destination types

Before anything is provisioned, get this straight, because it is what the shape of the whole workshop follows from.

Both legs are ClickPipes. What differs is the destination type, and the two are configured from different places in the console: the migration leg from Data sources on your Managed Postgres service, the analytics leg as a Postgres CDC pipe on your ClickHouse service.

What you do need to keep straight is what each leg is for. The first moves the transactional workload — after it, your application writes somewhere else, and that is a cutover with a runbook and a window. The second makes an analytical copy and moves nothing: the application never notices it exists. Neither substitutes for the other, and this workshop builds them one at a time, in that order, so that any failure has exactly one candidate cause.

The road not taken

There is a shorter workshop hiding inside this one: keep the application writing to RDS, point ClickPipes straight at RDS, and route the dashboard to ClickHouse. Two modules instead of five, no cutover, no runbook, no sequence gotcha. It is the conventional ClickPipes tutorial, and for a team whose only problem is a slow dashboard it is the right answer — say so out loud when someone proposes it, because it often is.

It is not this workshop, for two reasons. It leaves the transactional workload on the engine whose write throughput PostgresBench measures at roughly a fifth of Managed Postgres at the same instance size, which is a number module 04 shows you. And it never teaches a cutover: the learnable, transferable, career-relevant part here is executing a runbook that moves a live write path between two Postgres instances with under a minute of write downtime, discovering that logical replication does not carry sequence values, and knowing what an orphaned replication slot does to a disk. You do not get any of that from a one-way CDC pipe. The order of the modules is therefore deliberate: transactional hop first and complete, analytical hop second, one replication leg live at a time so that every failure has exactly one possible cause.

Step 1 — Apply the Terraform

You validated this configuration in module 00 and wrote terraform.tfvars with your own address in subscriber_cidrs. Apply it now.

cd "$(git rev-parse --show-toplevel)/workshops/postgres_migration/terraform"
terraform apply

Read the plan before you type yes. It creates one db.m6g.large Postgres 17 instance with 200 GB of gp3, a custom DB parameter group, a DB subnet group and a security group. RDS takes several minutes to reach available.

PROVISIONAL: 8 to 12 minutes for terraform apply to return, replaced by a measured figure after the end-to-end run.

Capture the outputs into environment variables. Keep this shell for the rest of the workshop, or re-run these lines in any new one.

export AWS_REGION="$(terraform output -raw region)"
export RDS_ENDPOINT="$(terraform output -raw rds_endpoint)"
export RDS_HOST="${RDS_ENDPOINT%%:*}"
export RDS_INSTANCE_ID="$(terraform output -raw rds_instance_id)"
export PGPASSWORD="$(terraform output -raw rds_admin_password)"
export PGUSER=shopadmin
export PGDATABASE=shop
printf 'region=%s\ninstance=%s\nendpoint=%s\n' "$AWS_REGION" "$RDS_INSTANCE_ID" "$RDS_ENDPOINT"

All three lines must print a value. AWS_REGION is what points the aws CLI at the same region Terraform built in; RDS_INSTANCE_ID is the generated instance name, which the next step reboots and modules 03 and 07 both need.

The admin password is generated by Terraform and lives in the state file. That is why module 00 told you never to commit terraform.tfstate.

Choose the two role passwords now and keep them for the whole workshop. Module 03 creates the same two roles on the target instance with the same passwords, on purpose: it is what makes the connection-string host the only thing that changes at cutover.

export WRITER_PASSWORD="$(openssl rand -hex 16)"
export ANALYTICS_PASSWORD="$(openssl rand -hex 16)"
printf 'WRITER_PASSWORD=%s\nANALYTICS_PASSWORD=%s\n' "$WRITER_PASSWORD" "$ANALYTICS_PASSWORD" > ../.env.local

.env.local is for your own recovery if a terminal dies. Do not commit it.

Step 2 — Reboot for wal_level, then prove it

rds.logical_replication is a static parameter. The Terraform sets it to 1 with apply_method = "pending-reboot", which means the parameter group carries the value and the running engine does not. Until the instance restarts, wal_level is replica and no publication can stream a byte.

This is the workshop's first piece of planned downtime, and it is deliberately placed here, before you have anything to lose.

Reboot the instance you exported in Step 1. A new terminal has none of those variables, so re-run that export block first if this is one.

aws rds reboot-db-instance --db-instance-identifier "$RDS_INSTANCE_ID" > /dev/null
aws rds wait db-instance-available --db-instance-identifier "$RDS_INSTANCE_ID"

Now verify all three RDS prerequisites in one session. They are required together, and two of the three fail silently if they are missing.

psql -h "$RDS_HOST" -c "SHOW wal_level;" \
     -c "SELECT name, setting FROM pg_settings WHERE name = 'rds.logical_replication';" \
     -c "SELECT name, setting FROM pg_settings WHERE name IN ('max_replication_slots','max_wal_senders');"

 wal_level
-----------
 logical

Then confirm automated backups are on, which is what gives RDS long-term WAL retention:

aws rds describe-db-instances --db-instance-identifier "$RDS_INSTANCE_ID" \
  --query "DBInstances[0].[BackupRetentionPeriod,DBParameterGroups[0].ParameterApplyStatus]" \
  --output table

BackupRetentionPeriod must be 1 or more and the parameter group's apply status must read in-sync, not pending-reboot.

Step 3 — Create the schema and the two roles

cd "$(git rev-parse --show-toplevel)/workshops/postgres_migration"
psql -h "$RDS_HOST" \
  -v writer_password="$WRITER_PASSWORD" \
  -v analytics_password="$ANALYTICS_PASSWORD" \
  -f sql/01_schema.sql

Read the file's comments while it runs. Two things in it are load-bearing rather than housekeeping:

• Two roles, not one. shop_writer is the application: it inserts and updates orders and its search_path is never touched by anything in this workshop. shop_analytics is the reporting path, and in module 06 its search_path alone gets shadowed so that an unqualified table name resolves to a ClickHouse-backed foreign table. Collapsing them into one role would make the claim "the application did not change" false rather than merely hard to prove.
• The guarded GRANT rds_replication. AWS requires that role to create and stream from a logical slot. The master user holds rds_superuser, which is what permits the grant, but rds_superuser does not imply rds_replication. This is the third of the three prerequisites; the other two were the parameter and the backup retention.

Confirm all four tables and both roles exist:

psql -h "$RDS_HOST" -c '\dt' -c "SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname LIKE 'shop%' ORDER BY rolname;"

Step 4 — Start the seed, in the background

sql/02_seed.sql generates 500,000 customers, 20,000 products, 12,000,000 orders and about 48,000,000 order items — entirely inside the instance, with generate_series. Nothing crosses the network. Seeding this from a laptop would be bounded by conference wifi; this is bounded by the instance's disk, which is the point: the fact table has to outgrow shared_buffers or the dashboard queries are a cache-hit measurement rather than a disk-bound one. Measured on db.m6g.large, shared_buffers is 1,884 MB and order_items finishes at 5,241 MB — 3,510 MB of heap plus 1,731 MB across its two indexes — so the fact table alone is about 2.8x the buffer cache and all four tables total 6,707 MB.

Start it detached, with its output in a log you can tail.

nohup psql -h "$RDS_HOST" -f sql/02_seed.sql > /tmp/seed.log 2>&1 &
echo $! > /tmp/seed.pid
sleep 5 && cat /tmp/seed.log

Leave $! unquoted. Quoting it makes your shell read the ! as a history reference, and the PID file is never written.

That cat is a start check, not a monitor — it prints the log once and exits, which is what you want here. Expect the Seeding customers=... products=... line, which proves psql connected and the row-count variables resolved. Anything else at this point is a connection or credentials problem worth fixing now rather than in twenty minutes:

Seeding customers=500000 products=20000 orders=12000000 items_per_order=4

The log then gains one line per completed statement, so it is a poor progress signal and a good completion signal. tail -f /tmp/seed.log will sit silent for the twenty-odd minutes order_items is being written and then print INSERT 0 48000000 all at once. Silence there is the seed working.

Measured: 29 minutes for the full seed on db.m6g.large with default gp3 IOPS, with the writer and the dashboard both running against the same instance.

Watch progress from a second connection — but watch the file size, not the row count:

psql -h "$RDS_HOST" -c "SELECT pg_size_pretty(pg_total_relation_size('order_items')) AS loaded;"
psql -h "$RDS_HOST" -c "SELECT now() - query_start AS elapsed, wait_event_type, wait_event
  FROM pg_stat_activity WHERE query LIKE 'INSERT INTO order_items%';"

order_items grows to about 5,241 MB, so the first query is a usable progress bar and the second gives you an elapsed time to extrapolate from.

Row counts will not work here, and reading them is the fastest way to conclude the seed has hung when it has not. Each table is loaded by one INSERT ... SELECT, so no other session sees a single row of it until that statement commits — count(*) on order_items reports zero for the twenty-odd minutes the largest statement runs, then reports all 48,000,000 at once. Once the writer is running it reports the writer's own rows climbing by a few hundred a second, which looks like progress and is not. The only thing that finishes the seed is /tmp/seed.log ending with ANALYZE:

tail -2 /tmp/seed.log

What the numbers should be

Every count in this workshop is one of two kinds. The seed's rows are fixed. The writer's rows move.

order_items is 12,000,000 × 4 on the nose, which is why the log's last insert reads INSERT 0 48000000 and not a round-ish number. So any total above 48,000,000 is expected as soon as the writer is running, and the excess is the writer's, not a duplicate. The seed gives every row it creates an order_id of 12,000,000 or less, so you can always separate the two:

psql -h "$RDS_HOST" -c "SELECT count(*) FILTER (WHERE order_id <= 12000000) AS from_seed,
                               count(*) FILTER (WHERE order_id  > 12000000) AS from_writer,
                               count(*) AS total
                        FROM order_items;"

from_seed reads exactly 48,000,000 and never changes again. Only from_writer moves, and it grows for as long as the writer runs. A sanity check on it: divide from_writer by the writer's orders rows and you should get between 1 and 4, averaging near 2.5, because that is the range the writer picks from per order.

If from_seed is not 48,000,000, that is worth stopping for — below it means the seed did not finish, above it means it ran twice.

Step 5 — Take the baseline

The baseline is two measurements of the same writer, taken minutes apart on the same instance: once with nothing else running, once with eight dashboard sessions against it. The difference between them is the entire point of the module, so the only thing that matters here is that the two readings are comparable. Everything below exists to keep them that way.

Do module 02 first if the seed is still running. A baseline against a partly loaded fact table is not a baseline; it is a cache-hit measurement, and it will make module 06's comparison meaningless. Worse, a writer measured while the seed is still running is competing with it for the same disk, so the unloaded reading would be depressed by the seed rather than by anything you are trying to measure. The seed is done when /tmp/seed.log ends with ANALYZE — that is the only signal, for the reasons in Step 4.

tail -2 /tmp/seed.log
psql -h "$RDS_HOST" -c "SELECT count(*) FROM order_items;"

Expect INSERT 0 48000000 then ANALYZE, and a count of exactly 48,000,000 — no writer has run yet, so nothing has been added on top. If it reads higher, a writer is running that you did not start; find it before you measure, because it is load the config block will not record.

Nothing else may touch the instance while you measure. No writer and no Grafana have been started yet; the dashboard comes up in Step 6, after both readings. Confirm it anyway, because an orphan from an earlier attempt is invisible until it corrupts a measurement:

pgrep -f 'writer.py --dsn' && echo "STOP: a writer is running" || echo "no writer running"
docker ps --filter name=grafana --format '{{.Names}} {{.Status}}'

The pgrep must find nothing and docker ps must print nothing.

Every panel's SQL is byte-identical to a file in bench/queries/, and a script enforces that, because "the application's SQL did not change" has to be checkable rather than asserted:

cd "$(git rev-parse --show-toplevel)/workshops/postgres_migration"
./scripts/preflight.sh && echo "panel SQL matches bench/queries"

Measurement 1 — the writer alone

This is the number the whole module is compared against.

cd "$(git rev-parse --show-toplevel)/workshops/postgres_migration/app"
python3 -m pip install -r requirements.txt
nohup python3 writer.py \
  --dsn "postgresql://shop_writer:${WRITER_PASSWORD}@${RDS_HOST}:5432/shop?sslmode=require" \
  --rate 200 --concurrency 16 --report-seconds 10 \
  > /tmp/writer.log 2>&1 &
echo $! > /tmp/writer.pid
sleep 90 && tail -4 /tmp/writer.log

One line of JSON per ten seconds, one transaction per order — an orders row and one to four order_items rows:

{"committed": 1987, "failed": 0, "tps": 198.7, "p95_ms": 41.2}

Write down the tps from the last line. That is "writer TPS with no dashboard load" in the table below, and nothing else in the workshop produces it. Ninety seconds rather than twenty-five because the first interval or two are cold; you want a settled figure.

--rate is a target, not a guarantee. When the database cannot keep up, tps falls below 200 and p95_ms climbs. That degradation is the measurement, not a malfunction. If the writer prints writer: first failure: on stderr, read it now rather than watching failed climb: it is almost always the DSN or a missing grant.

Then stop it, and verify that it stopped:

kill "$(cat /tmp/writer.pid)" && sleep 3 && tail -1 /tmp/writer.log
pgrep -f 'writer.py --dsn' && echo "STOP: a writer survived" || echo "stopped"

The last line is the writer's final summary, printed after in-flight transactions drain:

{"final": true, "committed": 41230, "failed": 0, "p95_ms": 44.1, "elapsed_seconds": 2081.4}

If you get an interval line instead of "final": true, the process you killed was not the one writing to that log — check pgrep output before going further, because two writers sharing one log file produce a file that is actively misleading rather than merely incomplete.

Measurement 2 — the writer under simulated dashboard load

The dashboard load here does not come from Grafana. bench/run.py supplies both loads itself: it spawns its own app/writer.py subprocess given --writer-dsn, and opens its own eight concurrent sessions running the panel SQL you just verified — at concurrencies pinned in run.py so that every participant measures the same experiment.

That is the reason Grafana stays down. Running it as well would add eight more panel queries a minute on top of the harness's own sessions, and none of that extra load appears in the config block the run prints. A benchmark whose recorded configuration is wrong is worse than no benchmark. Grafana is for you to look at; the harness is for measuring.

Dashboard queries go to the analytics role, the OLTP load to the writer role, both on RDS:

cd "$(git rev-parse --show-toplevel)/workshops/postgres_migration/bench"
python3 -m pip install -r requirements.txt
python3 run.py \
  --dsn "postgresql://shop_analytics:${ANALYTICS_PASSWORD}@${RDS_HOST}:5432/shop?sslmode=require" \
  --writer-dsn "postgresql://shop_writer:${WRITER_PASSWORD}@${RDS_HOST}:5432/shop?sslmode=require" \
  --label before \
  --out results/before.json

The run takes about five and a half minutes: 30 seconds of warm-up that is executed and discarded, then 300 measured seconds. Do not pass your own --dashboard-concurrency or --duration-seconds. Those defaults are pinned in run.py so that every participant's two tables are the same experiment; a run at concurrency 32 and a run at concurrency 8 are different quantities with the same name.

Check the exit status before you believe anything it printed:

Read the WARNINGS block too. Comparable query set | NO and Writer held the load for the whole run | NO both mean the numbers on screen are not a measurement under load.

The baseline

Your figures will differ; what must match is the shape. Every number below is provisional and is replaced by a measured one after the end-to-end run.

The third row against the second is the one to sit with, and the reason both measurements were taken minutes apart on a settled instance is so that comparing them is legitimate. The writer was asked for 200 orders a second and delivered it while nothing else touched the database. Add eight concurrent dashboard sessions and it delivers roughly half that, on the same instance, with the same --rate and the same seeded data. Nobody changed the checkout path. Nobody deployed anything. A dashboard someone built for a weekly review is now rejecting orders' worth of throughput every second, and the only visible symptom is that p95_ms in the writer's log went up.

That is the failure this workshop fixes, and it is why the headline number at the end is writer TPS rather than panel latency. Panel latency is what the person watching the dashboard notices. Writer TPS is what the business loses.

Keep results/before.json and the printed config block together. Module 06 quotes both.

Step 6 — Start the writer and the dashboard, and leave them running

Everything from here to the end of the workshop runs against a live write path and a live dashboard. Both start exactly once, here, and neither is stopped again in this module — module 03 cuts them over, module 06 reroutes the dashboard's queries.

The writer

The cutover in module 04 has to happen against a live write path, or the window measures nothing.

cd "$(git rev-parse --show-toplevel)/workshops/postgres_migration/app"
nohup python3 writer.py \
  --dsn "postgresql://shop_writer:${WRITER_PASSWORD}@${RDS_HOST}:5432/shop?sslmode=require" \
  --rate 200 --concurrency 16 --report-seconds 10 \
  > /tmp/writer.log 2>&1 &
echo $! > /tmp/writer.pid
sleep 25 && tail -2 /tmp/writer.log
pgrep -c -f 'writer.py --dsn'

pgrep -c must print 1. More than one means Measurement 1's writer outlived its kill, and two writers sharing /tmp/writer.log corrupt it into something actively misleading rather than merely incomplete.

The dashboard

One Grafana, one Postgres datasource, eight hand-written SQL panels. The datasource's url is the only thing that will ever change about this dashboard, and it changes exactly once, at cutover.

cd "$(git rev-parse --show-toplevel)/workshops/postgres_migration/grafana"
export SHOP_DB_HOST="$RDS_ENDPOINT"
export SHOP_ANALYTICS_PASSWORD="$ANALYTICS_PASSWORD"
docker compose up -d
docker compose logs --tail 20 grafana

Open http://localhost:3000 and find the Shop operations dashboard. Anonymous access is enabled, so there is no login.

Eight panels, over windows of 7, 30 and 90 days:

Every panel is fully populated the moment it loads: the seed put 90 days of history behind it, so there is no warm-up period and nothing fills in as you watch.

Done when

• SHOW wal_level returns logical, BackupRetentionPeriod is at least 1, and the parameter group reports in-sync;
• sql/01_schema.sql has run: four tables, shop_writer and shop_analytics both able to log in;
• the seed has finished — /tmp/seed.log ends with ANALYZE — and order_items holds exactly 48,000,000 rows with order_id <= 12000000, plus however many the writer has added since;
• both baseline readings are written down: the writer's TPS alone, and its TPS under the harness's dashboard load;
• the standing writer from Step 6 is committing at roughly its target rate with failed at zero, and pgrep -f 'writer.py --dsn' finds exactly one;
• the Shop operations dashboard renders all eight panels, and ./scripts/preflight.sh confirms every panel's SQL matches its file in bench/queries/; and
• results/before.json exists, from a run that exited 0, with its config block saved alongside.

Next: module 02, which you may already have done, is where you write the runbook that module 03 executes. Do not skip it and improvise the cutover — the sequence step is the one nobody improvises correctly.