Thread: Logical replication failed recovery

Logical replication failed recovery

From
Lou Tseng
Date:
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?

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
Advanced Medical Pricing Solutions
35 Technology Parkway South, Suite. 100
Peachtree Corners, GA 30092

Re: Logical replication failed recovery

From
Pavan Teja
Date:
Hi Lou,

Try setting wal_keep_segments parameter to a higher value. I think it will suffice to solve the problem.

Regards, 
Pavan Teja, 
9841380956 

On Sun, 7 Apr, 2019, 6:53 PM Lou Tseng, <ltseng@advancedpricing.com> 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?

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
Advanced Medical Pricing Solutions
35 Technology Parkway South, Suite. 100
Peachtree Corners, GA 30092

Re: Logical replication failed recovery

From
Adrian Klaver
Date:
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



Re: Logical replication failed recovery

From
Adrian Klaver
Date:
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