Initial load from standby in logical replication.

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Initial load from standby in logical replication.

Radoslav Nedyalkov
Hi All,
We want to setup a logical replica with initial load from physical standby. (all pg10.5)
Basically what we tested is:
1.on the master: create publication and logical slot.
2.on the standby: pause the stanby. (lsn from 1. is already replayed).
3.on the standby: get last replayed lsn, pg_last_wal_replay_lsn().
4.export/import the data into the logical replica.
5.on the logical replica: create subscription with pg_replication_origin_advance'd to the lsn from 3.
6.on the logical replica: enable subscription.

On a test setup with some bulk inserts results are encouraging. 
Still we're not that confident. Does the procedure looks okay?
What if standby is paused at COMMIT lsn?

Thanks!
Radoslav Nedyalkov






Reply | Threaded
Open this post in threaded view
|

Re: Initial load from standby in logical replication.

Achilleas Mantzios
On 31/1/19 3:55 μ.μ., Radoslav Nedyalkov wrote:

> Hi All,
> We want to setup a logical replica with initial load from physical standby. (all pg10.5)
> Basically what we tested is:
> 1.on the master: create publication and logical slot.
> 2.on the standby: pause the stanby. (lsn from 1. is already replayed).
> 3.on the standby: get last replayed lsn, pg_last_wal_replay_lsn().
> 4.export/import the data into the logical replica.
> 5.on the logical replica: create subscription with pg_replication_origin_advance'd to the lsn from 3.
> 6.on the logical replica: enable subscription.
>
> On a test setup with some bulk inserts results are encouraging.
> Still we're not that confident. Does the procedure looks okay?
> What if standby is paused at COMMIT lsn?
Who is gonna consume data from the primary's replication slot? If there is no subscriber connected and reading data, then the primary will think that the subscriber is down and will accumulate WALs
till some replication client is connected to it (which will not happen since the subscriber points to the standby).
>
> Thanks!
> Radoslav Nedyalkov
>
>
>
>
>
>


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


Reply | Threaded
Open this post in threaded view
|

Re: Initial load from standby in logical replication.

Radoslav Nedyalkov
On Thu, Jan 31, 2019 at 4:09 PM Achilleas Mantzios <[hidden email]> wrote:
On 31/1/19 3:55 μ.μ., Radoslav Nedyalkov wrote:
> Hi All,
> We want to setup a logical replica with initial load from physical standby. (all pg10.5)
> Basically what we tested is:
> 1.on the master: create publication and logical slot.
> 2.on the standby: pause the stanby. (lsn from 1. is already replayed).
> 3.on the standby: get last replayed lsn, pg_last_wal_replay_lsn().
> 4.export/import the data into the logical replica.
> 5.on the logical replica: create subscription with pg_replication_origin_advance'd to the lsn from 3.
> 6.on the logical replica: enable subscription.
>
> On a test setup with some bulk inserts results are encouraging.
> Still we're not that confident. Does the procedure looks okay?
> What if standby is paused at COMMIT lsn?
Who is gonna consume data from the primary's replication slot? If there is no subscriber connected and reading data, then the primary will think that the subscriber is down and will accumulate WALs
till some replication client is connected to it (which will not happen since the subscriber points to the standby).
Yes. that's clear. Primary will accumulate WALs until logical replica connects to it. 
The point is to offload the primary from massive initial copying.
 
> Thanks!
> Radoslav Nedyalkov
>
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt