Setting the file. One moment.
Subchapter 74.3
references/bluegreen-ddl-postgresql.mdMarkdown4 KBView on GitHub
RDS PostgreSQL Blue/Green uses logical replication (not binlog). This has different compatibility characteristics than MySQL’s binlog-based approach.
References
Bluegreen Advisor WorkflowPostgreSQL Blue/Green creates a logical replication slot on blue and subscribes on green. Logical replication replicates row-level changes (INSERT, UPDATE, DELETE) but does NOT replicate DDL. This means:
| Operation | Notes |
|---|---|
| ADD COLUMN at end with static default | e.g., ADD COLUMN status TEXT DEFAULT 'active'. Safe. |
| ADD COLUMN at end with NULL default | Safe. Replicated rows get NULL for new column. |
| CREATE INDEX / DROP INDEX | No schema change to row format. Safe. |
| CREATE INDEX CONCURRENTLY | Safe and recommended on green to avoid locks. |
| ADD/DROP CHECK constraint | No row format change. Safe. |
| ADD/DROP NOT NULL constraint | Safe if existing data complies. |
| ANALYZE / VACUUM | Maintenance only. Safe. |
| COMMENT ON | Metadata only. Safe. |
| Operation | Why It Breaks | What To Do |
|---|---|---|
| ALTER COLUMN TYPE (type change) | Table rewrite. Logical replication can’t apply old-type rows to new-type column. | Apply on green, switchover immediately. |
| ADD COLUMN with volatile DEFAULT | e.g., DEFAULT now(), DEFAULT gen_random_uuid(). Each replicated row would need to evaluate the default, causing mismatches. | Use static default or NULL, then backfill after switchover. |
| RENAME TABLE | Logical replication subscription references the old table name. Slot breaks. | Switchover immediately after rename. |
| RENAME COLUMN | Logical replication uses column names (not positions like binlog). Rename breaks mapping. | Switchover immediately. |
| DROP COLUMN on green | Replicated rows still contain the dropped column. Logical replication fails. | Switchover immediately, or drop column after switchover. |
SELECT setval('my_table_id_seq', (SELECT MAX(id) FROM my_table));lo_* types)lo or oid references to large objects, Blue/Green may not capture all databytea or text columns insteadtext, jsonb, or bytea columns stored in TOASTBefore switchover, check replication lag on blue:
SELECT slot_name, confirmed_flush_lsn, pg_current_wal_lsn(),
(pg_current_wal_lsn() - confirmed_flush_lsn) AS lag_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical';Switchover will wait for lag to reach zero. If lag is large, switchover takes longer.
SELECT setval(...) for all serial/identity columnsANALYZE on modified tables