|
| perlgeek wrote:
| The description of preconditions seems a bit incomplete.
|
| To me it seems you need to avoid certain constructs, like UNIQUE
| constraints. Otherwise you might have a local insert plus a
| replicated one, both the same value in the unique column, and
| different nodes reject different inserts.
| smilliken wrote:
| Agreed. Also exclusion constraints (a generalization of unique
| constraints). Also foreign key constraints, because one
| database might delete a record simultaneously with another
| database adding a reference to it.
| hinkley wrote:
| Deleting from a database is a sucker's bet. If you add to a
| record and mark it dead there are fewer semantic problems to
| deal with.
|
| Partial indexes give you approximately the same speedup as
| deletes.
| egamirorrim wrote:
| I don't know that this article tackled the real elephant in the
| room for bi-di replication. What happens when the same insert
| happens on both primaries at once, who wins?
| singron wrote:
| They will each apply the insert locally. Then they will attempt
| to replicate that insert to each other. Each will attempt to
| apply the replicated conflicting insert, which will cause an
| error and halt replication for both nodes.
|
| If you update the same data on both nodes, this is a recipe for
| almost certain disaster. Postgres is not a distributed database
| and this doesn't make it one.
| cogman10 wrote:
| Yeah, read the docs here [1] and really does not seem like
| this would be a good system to work with. To make something
| fault tolerant you have to have a side subscription running
| watching for server errors so you can resolve these conflicts
| when they arise.
|
| pglogical gave you the option to determine the winner of a
| conflicting write (which I think is preferable). The inbuilt
| postgres stuff seems like it'd be rife with potential errors.
|
| [1] https://www.postgresql.org/docs/16/logical-replication-
| confl...
| simonw wrote:
| Right: Is the idea here that you use UUIDs for inserted rows,
| hence avoiding duplicate inserts?
|
| But what happens if multiple updates or deletes target the same
| existing row?
| grogers wrote:
| In the MySQL world, bidirectional replication is common, but
| with at-most-one of the two being writable (the replication
| user can still write even if the secondary is read-only). Maybe
| they meant it to be similar and not truly multi-master? It does
| seem like a glaring omission given they talk about having two
| primaries.
___________________________________________________________________
(page generated 2023-12-18 23:00 UTC) |