Logical replication failed recovery

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

Logical replication failed recovery

Lou Tseng
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

[hidden email]
Advanced Medical Pricing Solutions
35 Technology Parkway South, Suite. 100
Peachtree Corners, GA 30092

Reply | Threaded
Open this post in threaded view
|

Re: Logical replication failed recovery

pavan95
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, <[hidden email]> 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

[hidden email]
Advanced Medical Pricing Solutions
35 Technology Parkway South, Suite. 100
Peachtree Corners, GA 30092

Reply | Threaded
Open this post in threaded view
|

Re: Logical replication failed recovery

Adrian Klaver-4
In reply to this post by Lou Tseng
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
>
> [hidden email] <mailto:[hidden email]>
> <http://www.advancedpricing.com/>
> Advanced Medical Pricing Solutions <http://advancedpricing.com/>
> 35 Technology Parkway South, Suite. 100
> Peachtree Corners, GA 30092
>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Logical replication failed recovery

Adrian Klaver-4
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
>
> [hidden email] <mailto:[hidden email]>
> <http://www.advancedpricing.com/>
> Advanced Medical Pricing Solutions <http://advancedpricing.com/>
> 35 Technology Parkway South, Suite. 100
> Peachtree Corners, GA 30092
>
>
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <[hidden email]>
> *Sent:* Sunday, April 7, 2019 10:05 AM
> *To:* Lou Tseng; [hidden email]
> *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
>>
>> [hidden email] <mailto:[hidden email]>
>> <http://www.advancedpricing.com/>
>> Advanced Medical Pricing Solutions <http://advancedpricing.com/>
>> 35 Technology Parkway South, Suite. 100
>> Peachtree Corners, GA 30092
>>
>>
>
>
> --
> Adrian Klaver
> [hidden email]


--
Adrian Klaver
[hidden email]