Thread: Logical replication failed recovery

On 4/7/19 6:22 AM, Lou Tseng wrote:
> Hi folks,
> 
> Is there a good tutorial to recover from logical replication out of 
> sync?  We ran into this error stating WAL has been removed and now 
> replica is out of sync.  Also, is there a way to increase the number of 
> WAL kept on the master?

I have to believe there is more to the story. When a subscription is 
enabled it sets up a replication 
slot(www.postgresql.org/docs/11/warm-standby.html#STREAMING-REPLICATION-SLOTS) 
that should ensure that the WAL is not recycled until it is used. Did 
you stop a Subscription then
restart it? Or one of the other scenarios shown here?:

https://www.postgresql.org/docs/11/logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-SLOT

> 
> Thanks!
> 
> 2019-04-07 12:28:37.180 UTC [22292] ERROR:could not receive data from 
> WAL stream: ERROR:requested WAL segment 0000000100005208000000EB has 
> already been removed
> 
> 2019-04-07 12:28:37.182 UTC [114186] LOG:worker process: logical 
> replication worker for subscription 8907992 (PID 22292) exited with exit 
> code 1
> 
> 
> 
> Lou Tseng
> 
> ltseng@advancedpricing.com <mailto:ltseng@advancedpricing.com>
> <http://www.advancedpricing.com/>
> Advanced Medical Pricing Solutions <http://advancedpricing.com/>
> 35 Technology Parkway South, Suite. 100
> Peachtree Corners, GA 30092
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 4/9/19 6:08 AM, Lou Tseng wrote:

Please reply to list also.
Ccing list.

> Hi adrian,
> 
> You are right.  Increasing the wal_kept_segments didn't solve the 
> problem. However, I didn't find any scenario in the documentation 
> applied to my case.
> Is there a way to get the databases resynced?

It is still not clear to me how you got into this state. You might want 
to spend some time looking at the logs to see if you can figure that 
out, to prevent a recurrence.

In the meantime I would say the way to get re-synced is to start over. 
In other words empty the table(s) on the subscriber side and start the 
subscriptions over again.

> 
> Thanks!
> 
> Lou Tseng
> 
> ltseng@advancedpricing.com <mailto:ltseng@advancedpricing.com>
> <http://www.advancedpricing.com/>
> Advanced Medical Pricing Solutions <http://advancedpricing.com/>
> 35 Technology Parkway South, Suite. 100
> Peachtree Corners, GA 30092
> 
> 
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <adrian.klaver@aklaver.com>
> *Sent:* Sunday, April 7, 2019 10:05 AM
> *To:* Lou Tseng; pgsql-general@lists.postgresql.org
> *Subject:* Re: Logical replication failed recovery
> On 4/7/19 6:22 AM, Lou Tseng wrote:
>> Hi folks,
>> 
>> Is there a good tutorial to recover from logical replication out of 
>> sync?  We ran into this error stating WAL has been removed and now 
>> replica is out of sync.  Also, is there a way to increase the number of 
>> WAL kept on the master?
> 
> I have to believe there is more to the story. When a subscription is
> enabled it sets up a replication
> slot(www.postgresql.org/docs/11/warm-standby.html#STREAMING-REPLICATION-SLOTS 
> <http://www.postgresql.org/docs/11/warm-standby.html#STREAMING-REPLICATION-SLOTS>) 
> 
> that should ensure that the WAL is not recycled until it is used. Did
> you stop a Subscription then
> restart it? Or one of the other scenarios shown here?:
> 
> https://www.postgresql.org/docs/11/logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-SLOT
> 
>> 
>> Thanks!
>> 
>> 2019-04-07 12:28:37.180 UTC [22292] ERROR:could not receive data from 
>> WAL stream: ERROR:requested WAL segment 0000000100005208000000EB has 
>> already been removed
>> 
>> 2019-04-07 12:28:37.182 UTC [114186] LOG:worker process: logical 
>> replication worker for subscription 8907992 (PID 22292) exited with exit 
>> code 1
>> 
>> 
>> 
>> Lou Tseng
>> 
>> ltseng@advancedpricing.com <mailto:ltseng@advancedpricing.com>
>> <http://www.advancedpricing.com/>
>> Advanced Medical Pricing Solutions <http://advancedpricing.com/>
>> 35 Technology Parkway South, Suite. 100
>> Peachtree Corners, GA 30092
>> 
>> 
> 
> 
> -- 
> Adrian Klaver
> adrian.klaver@aklaver.com


-- 
Adrian Klaver
adrian.klaver@aklaver.com