Re: logical replication resiliency

On 23/12/18 3:39 μ.μ., Istvan Soos wrote:
> I have a database with about 1 TB of data, mostly inserts, little
> updates and deletes, and I want to setup 2 logical replication for it.
> What should be my expectation with the replication?
>
> 1. What is the time-window that either of the nodes can be offline?
> - Will it survive if the node doing the publishing is offline for a
> few hours and then goes back online?
No problem with that.
> - Will it survive if the subscriber node is offline for a few hours
> and then goes back online?
There is a problem with that. Due to the replication slot, the subscriber will start keeping WALs waiting for the subscriber to wake up. It depends on the amount of WAL produced, peak time or not, and
any other special situations (business) that could influence the amount of writes to the DB. You can easily find out by looking at your archive. Note that once the space dedicated for WALs is filled
up, your publisher node will PANIC.
>
> 2. If the replication is aborted for whatever reason, is there a way
> to do it incrementally, or will it re-sync the 1TB at every such
> event?
It is done incrementally, provided there is WAL continuity.
>
> 3. What alternative tools are out there to e.g. periodically sync from
> one db to another?
PgSQL replication solutions in various forms, sizes and shapes are around since 2001. So plenty of tools out there + your custom scripts that you can always write. But since logical replication is in
the core (starting from pgsql 10.0) I see no reason not to use it.

You can have a look at an article I wrote here : https://severalnines.com/blog/using-postgresql-logical-replication-maintain-always-date-readwrite-test-server

>
> Thanks,
> Istvan
>

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt