On 12/8/18 11:26 PM, Mike Lissner wrote:
Hi, first time poster.
I just ran into a rather messy problem when doing a schema migration
with logical replication. I'm not entirely sure what went wrong, why, or
how to prevent it in the future. The migration I ran was pretty simple
(though auto-generated by Django):
And after running this migration, I started getting this error on the
subscriber:
|2018-12-09 05:59:45 UTC::@:[13373]:LOG: logical replication apply
worker for subscription "replicasubscription" has started 2018-12-09
05:59:45 UTC::@:[13373]:ERROR: null value in column
"recap_sequence_number" violates not-null constraint 2018-12-09 05:59:45
UTC::@:[13373]:DETAIL: Failing row contains (48064261, 2018-12-07
04:48:40.388377+00, 2018-12-07 04:48:40.388402+00, null, 576, , 4571214,
null, null). 2018-12-09 05:59:45 UTC::@:[6342]:LOG: worker process:
logical replication worker for subscription 18390 (PID 13373) exited
with exit code 1|
So, my migration created a new column with a null constraint and somehow
the subscriber got data that violated that. I don't know how that's
possible since this was a new column and it was never nullable.
The above seems to be the crux of the problem, how did NULL get into the
column data?
The DDL migration did what it was supposed to do.
I applied the above migration simultaneously on my publisher and
subscriber thinking that postgresql was smart enough to do the right
thing. I think the subscriber finished first (it has less traffic).
And it did as far as the schema changes where concerned.
The docs hint that postgresql might be smart enough to not worry about
the order you do migrations:
> /Logical replication is robust when schema definitions change in a
live database:/ When the schema is changed on the publisher and
replicated data starts arriving at the subscriber but does not fit into
the table schema, replication will error until the schema is updated.
And it even hints that doing a migration on the subscriber first is a
good thing in some cases:
> In many cases, intermittent errors can be avoided by applying
additive schema changes to the subscriber first.
But I'm now supremely skeptical that doing anything at the subscriber
first is a good idea. Are the docs wrong? Does the above error make
sense? Is the process for schema migrations documented somewhere beyond
the above?
I have lots of questions because I thought this would have gone smoother
than it did.
As for the fix: I made the column nullable on the subscriber and I'm
waiting for it to catch up. Once it does I'll re-sync its schema with
the publisher. Anybody interested in following along with all this (or
finding this later and having questions) can follow the issue here:
https://github.com/freelawproject/courtlistener/issues/919
Thank you for the lovely database! I hope this is helpful.
Mike
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
