I suspect quite a few of you are using PG logical replication… how do you solve the thing where is doesn’t handle DDL? @vignesh @Andrew Atkinson ?

We do use Logical Replication with PostgreSQL 13 for regular and partitioned tables. The short answer where I work is that we use Fivetran to manage schema changes. https://www.fivetran.com/blog/schema-changes Our analytical database receives row modifications via replication from our PostgreSQL databases. We have a separate data team so I don’t end up working on it much at all as I’m more application focused.

Is it OSS?

At a past company we were responsible for keeping our application schema in sync with a downstream Redshift database which was a poorly staffed and supported analytical database, and we use ….gasp…manual SQL modifications to modify the Redshift DB (old version of PG, so mostly the same DDL statements like

ALTER TABLE ... ADD COLUMN ..

) If two systems were homogenous databases (e.g. Postgres) we could use a Migrations systems like Active Record or similar to make schema changes on both sides and automate that with the release process. @Gwen Shapira No it’s a commercial SaaS tool. But now I’m wondering what an open source Fivetran might be. 🤔

Yeah, I was thinking that applying migrations on both is a reasonable way to go

For Migrations systems, I just learned about dbmate (Go) https://github.com/amacneil/dbmate and there are also things like Liquibase and Flyway you may be familiar with. In a past job we used both of them with Java projects to manage incremental changes. pgsql phriday #009 covered this topic as well (community blog post series) Roundup blog post 👉 https://programming.dev/post/417258?scrollToComments=true if anyone is looking for migrations (incremental schema changes) management tools or approaches.

lol 😂 sorry

Schema migration involves the ability to automatically and incrementally propagate schema changes from your source database to your destination data warehouse.

https://estuary.dev/change-data-capture-landscape/ # 5 on this list has to do with automating the propagation of schema changes from the source database. They list several options including Fivetran with higher degrees of automation around schema changes, and some with less options. The ones with higher levels of automation all look they are commercial offerings. Curious what others have to say on this! Debezium says it detects schema changes but not having tried to connect it it to an analytical database like Snowflake, I don’t know if that also means it can replay them in a compatible way. In general I guess it’s really about a combination of connectors (the databases they’re using) that might limit off the shelf choices.

Debezium's support for schema changes in PG is very limited, IMO

I've been wanting to try out https://github.com/bytebase/bytebase/ for a while. Haven't been able to make the time.

Sorry for the delay. First, we don't use logical replication at all. Not that we don't want but haven't ran into a good use case for it. Regarding DDL propagation, it seems fairly common that a lot of even the commercial tools doesn't provide that (shareplex from 2016 days didn't provide).

even sequences are propagated through logical changes. My first reaction was that’s very limiting.

@vignesh Could you expand on that? The sequences for a table that might supply the primary key id value for a row, would be used as the row is inserted. Then if the table is part of a publication and logical replication, that row will be published for any subscribers.

Sorry for the delay @Andrew Atkinson. First congratulations on the book release. I meant to say the sequence data itself not propagated through logical replication. [...] Sequence data is not replicated. Reference https://www.postgresql.org/docs/current/logical-replication-restrictions.html