Add logical replication as an alternative follow mode

[marcocitus]

For migration scenarios where the source is at PostgreSQL 10 or newer, it would be useful if pgcopydb follow mode could use logical replication (CREATE PUBLICATION & CREATE SUBSCRIPTION).

A benefit of using logical replication is that tables can be added over time, even after the initial snapshot. A new snapshot & replication slot are used for each table, after initial data copy the changes for that table are consumed separately, and then the table is handed off to the main apply worker. Another benefit is that the apply side is relatively efficient.

[shubhamdhama]

If we chose SUBSCRIPTION on target side then we would lose the benefit of prefetch. Sort of a tradeoff between simplicity of Logical Replication and benefits of prefetch. Which might be fine for smaller databases where offline copy doesn't take much time. I haven't much used Logical Replication, but IIUC we won't be able to set endpos for follow mode in pgcopydb. So it might be completely different follow mode.

[dimitri]

If we chose SUBSCRIPTION on target side then we would lose the benefit of prefetch. Sort of a tradeoff between simplicity of Logical Replication and benefits of prefetch. Which might be fine for smaller databases where offline copy doesn't take much time.

See https://www.postgresql.org/docs/current/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-WITH : we can also use the option copy_data and include the initial COPY of the data within the logical replication framework, and let Postgres handle the prefetch trade-offs.

[shubhamdhama]

I can start with the simplest approach of create publication/subscription using FOR ALL TABLES, then we can iterate on this for non-super user. For the latter I'm trying out combinations of multiple publication-subscription and investigating it's performance vs for-all-tables.