Re: Allow logical replication to copy tables in binary format

Euler Taveira <euler(at)eulerto(dot)com>, 11 Ağu 2022 Per, 20:16 tarihinde şunu
yazdı:

> My main concern is to break a scenario that was previously working (14 ->
> 15) but after a subscriber upgrade
> it won't (14 -> 16).
>
Fair concern. Some cases that might break the logical replication with
version upgrade would be:
1- Usage of different binary formats between publisher and subscriber. As
stated here [1], binary format has been changed after v7.4.
But I don't think this would be a concern, since we wouldn't even have
logical replication with 7.4 and earlier versions.
2- Lack (or mismatch) of binary send/receive functions for custom data
types would cause failures. This case can already cause failures with
current logical replication, regardless of binary copy. Stated here [2].
3- Copying in binary format would work with the same schemas. Currently,
logical replication does not require the exact same schemas in publisher
and subscriber.
This is an additional restriction that comes with the COPY command.

If a logical replication has been set up with different schemas and
subscription is created with the binary option, then yes this would break
things.
This restriction can be clearly stated and wouldn't be unexpected though.

I'm also okay with allowing binary copy only for v16 or later, if you think
it would be safer and no one disagrees with that.
What are your thoughts?

I would say that you should test some scenarios:
> 014_binary.pl and also custom data types, same column with different data
> types, etc.
>
I added scenarios in two tests to test binary copy:
014_binary.pl: This was already testing subscriptions with binary option
enabled. I added an extra step to insert initial data before creating the
subscription.
So that we can test initial table sync with binary copy.

002_types.pl: This file was already testing more complex data types. I
added an extra subscriber node to create a subscription with binary option
enabled.
This way, it now tests binary copy with different custom types.

Do you think these would be enough in terms of testing?

Attached patch also includes some additions to the doc along with the
tests.

Thanks,
Melih

[1] https://www.postgresql.org/docs/devel/sql-copy.html
[2] https://www.postgresql.org/docs/devel/sql-createsubscription.html