3 ms·

Couple of questions, maybe someone here can answer them. If DDL is not supported, can you not sync schema changes automatically? If true, what is the recommend

Couple of questions, maybe someone here can answer them. If DDL is not supported, can you not sync schema changes automatically? If true, what is the recommended way? If a stored procedure is triggered on the publisher and it mutates data… is that mutation what is sent? Or would the sproc also execute on the subscriber? Does it do a good job of syncing, or is it common to reach a state where you have to blast out the subscribers to get them back in a good synced state with the publisher?

jjice 4y ago
I really like this comment because I'm curious about all of these too. For the second one, I'm wondering if it's like MySQLs binlog replication where functions should be deterministic, which makes me think that they get reran on the replicated server.
- evanelias 4y ago
  > if it's like MySQLs binlog replication where functions should be deterministic, which makes me think that they get reran on the replicated server. For DML, MySQL replication actually allows you to choose between replicating the raw SQL statement (statement-based replication) vs replicating its effect (row-based replication). The former requires functions to be deterministic, but the latter does not. Both modes are still technically logical replication though, as it's a separate higher-level abstraction which is independent from each storage engine's physical transaction logs.
  - jjice 4y ago
    Oh very cool - thanks for filling me in!
phamilton 4y ago
Schema does not change automatically. We generally add new columns to the subscriber before adding them to the publisher if we want zero impact. But if we don't, there's no data loss we just end up with replication held up until the column is added. Stored procedures, triggers, etc. don't exist on the subscriber. They just get any mutations on any tables they are subscribed to.
boromisp 4y ago
PostgreSQL logical replication only cares about the data. It doesn't require that you have the same database schema - or even a database - on the receiving end. It doesn't know about stored procedures. You would typically use it for situations where you don't want to replicate your whole database. You can subscribe to a narrow/specific set of changes (for example if you only care about inserts to a specific table, no other changes). It has some significant limitations that means, it can't really be used as "Kafka Streams lite": You can't have different permissions for different publications. If a subscriber can read one, it can read them all. There is no separate storage for the publications or the subscribers: if any subscriber is slow/stuck/offline, it will hold back all your WAL for the entire database cluster. -- If you want an exact replica of your database, you have to use the "physical replication". This still doesn't specifically care about stored procedures, but it replicates data and schema change exactly.

jjice 4y ago

I really like this comment because I'm curious about all of these too. For the second one, I'm wondering if it's like MySQLs binlog replication where functions should be deterministic, which makes me think that they get reran on the replicated server.

evanelias 4y ago
> if it's like MySQLs binlog replication where functions should be deterministic, which makes me think that they get reran on the replicated server. For DML, MySQL replication actually allows you to choose between replicating the raw SQL statement (statement-based replication) vs replicating its effect (row-based replication). The former requires functions to be deterministic, but the latter does not. Both modes are still technically logical replication though, as it's a separate higher-level abstraction which is independent from each storage engine's physical transaction logs.
- jjice 4y ago
  Oh very cool - thanks for filling me in!

phamilton 4y ago

Schema does not change automatically. We generally add new columns to the subscriber before adding them to the publisher if we want zero impact. But if we don't, there's no data loss we just end up with replication held up until the column is added. Stored procedures, triggers, etc. don't exist on the subscriber. They just get any mutations on any tables they are subscribed to.

boromisp 4y ago

PostgreSQL logical replication only cares about the data. It doesn't require that you have the same database schema - or even a database - on the receiving end. It doesn't know about stored procedures. You would typically use it for situations where you don't want to replicate your whole database. You can subscribe to a narrow/specific set of changes (for example if you only care about inserts to a specific table, no other changes). It has some significant limitations that means, it can't really be used as "Kafka Streams lite": You can't have different permissions for different publications. If a subscriber can read one, it can read them all. There is no separate storage for the publications or the subscribers: if any subscriber is slow/stuck/offline, it will hold back all your WAL for the entire database cluster. -- If you want an exact replica of your database, you have to use the "physical replication". This still doesn't specifically care about stored procedures, but it replicates data and schema change exactly.