Thread: PostgreSQL logical replication depends on WAL segments?

Hello, I already tried to ask on stackoverflow but so far without success.

(https://stackoverflow.com/questions/54292816/postgresql-logical-replication-depends-on-wal-segments)

Could someone help me please?

****

I am successfully using logical replication between 2 PG 11 cloud VMs for latest data. But I tried to publish also some older tables to transfer data between databases and got strange error about missing WAL segment.

These older partitions contain data 5-6 days old. I successfully published them on master and refreshed subscription on logical replica. But now I am getting these strange error messages on logical replica:

2019-01-21 15:03:14.713 UTC [17203] LOG:  logical replication table synchronization worker for subscription "mysubscription", table "mytable_20190115" has finished
2019-01-21 15:03:19.768 UTC [18877] LOG:  logical replication apply worker for subscription "mysubscription" has started
2019-01-21 15:03:19.797 UTC [18877] ERROR:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000098E000000CB has already been removed
2019-01-21 15:03:19.799 UTC [29534] LOG:  background worker "logical replication worker" (PID 18877) exited with exit code 1
2019-01-21 15:03:24.806 UTC [18910] LOG:  logical replication apply worker for subscription "mysubscription" has started
2019-01-21 15:03:24.824 UTC [18911] LOG:  logical replication table synchronization worker for subscription "mysubscription", table "mytable_20190116" has started
2019-01-21 15:03:24.831 UTC [18910] ERROR:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000098E000000CB has already been removed
2019-01-21 15:03:24.834 UTC [29534] LOG:  background worker "logical replication worker" (PID 18910) exited with exit code 1

if you have WAL archiving enabled then try to find the missing WALs and copy them inside your data/pg_wal directory .
Normally the replication slot will retain all logs that have not been applied to the subscriber yet. So what you describe is not normal. Do you have any cron task that touches files in this dir?

Which is confusing for me. I tried to find some info but did not find anything about logical replication depending on WAL segments.

There is no streaming replication running on that particular master and these error messages I see on both master and replica connected with only logical replication.

Am I doing something wrong? Is there some special way how to publish older data? For newer data and latest data all works without problems.

Of course since I published like ~20 tables it took some time for replica to process all tables - currently it processes always 2 at the time. But I still do not understand why it should depend on WAL segments... Thank you very much.

I tried to unpublished and unsubscribe these older tables and publish and subscribe them again but getting still the same error message for the exactly the same WAL segment number.

I unpublished and unsubscribed those problematic tables and error messages stopped so they were definitely related to logical replication. Could they be caused by snapshot?

I even made additional strange experience with WAL segments errors - my logical replica had only quite small disk and during all that fiddling I forgot to check disk usage. So postgresql on logical replica crashed due to full disk. Since I use GCE I just resized root disk and after restart of the instance got more space. But I also got back missing WAL segments errors in connections with logical replication. My postgresql log on replica is now full of sequence of these 3 lines:

2019-01-22 09:47:14.408 UTC [1946] LOG:  logical replication apply worker for subscription "mysubscription" has started
2019-01-22 09:47:14.429 UTC [1946] ERROR:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000099D0000007A has already been removed
2019-01-22 09:47:14.431 UTC [737] LOG:  background worker "logical replication worker" (PID 1946) exited with exit code 1

Why logical replication depends on some old WAL segments? Today's data seem to work perfectly although there cannot be all WAL segments for today available on the logical master. But I am unable to publish older data...

Thanks for help.

Josef Machytka

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

On 1/22/19 5:18 AM, Josef Machytka wrote:
> Hello, I already tried to ask on stackoverflow but so far without success.
> (https://stackoverflow.com/questions/54292816/postgresql-logical-replication-depends-on-wal-segments)
> 
> Could someone help me please?
> 
> ****
> 
> I am successfully using logical replication between 2 PG 11 cloud VMs 
> for latest data. But I tried to publish also some older tables to 
> transfer data between databases and got strange error about missing WAL 
> segment.
> 
> These older partitions contain data 5-6 days old. I successfully 
> published them on master and refreshed subscription on logical replica. 
> But now I am getting these strange error messages on logical replica:
> 

> 
> Which is confusing for me. I tried to find some info but did not find 
> anything about logical replication depending on WAL segments.

https://www.postgresql.org/docs/11/logical-replication-architecture.html


> Thanks for help.
> 
> Josef Machytka
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

https://www.postgresql.org/docs/11/logical-replication-architecture.html

Thanks, I see... So if I understand it correctly - since I have quite big partitions like ~30 GB each in one parent table and from ~1GB to ~5 GB in several others I presume I had to set wal_keep_segments to some really high number and stop our security cronjob cleaning old WAL segments (because we already had some problems with almost full disk due to old WAL segments)

I asked you if you had any cron jobs messing around with your WALs. The WALs in pg_wal dir is PostgreSQL's business to manage, not your scripts. Dont do that. Defining wal_keep_segments to a very large value or using replication slots (as with logical repl) won't help if your crons are deleting the WALs.
Now what you have to do :
- read up about the basic concepts of pgsql administration : https://www.postgresql.org/docs/11/admin.html
- understand how postgresql replication slots work (logical repl is based on those)
- understand how WAL archiving works
- understand how streaming replication works
- understand how logical replication works
- understand how the various runtime configuration params act on the number of WALs kept

until the whole transfer of snapshot is done. Because only after the whole snapshot is transferred logical replication workers start to transfer WAL logs reflecting changes done from the moment snapshot was taken...

jm

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

On 1/22/19 9:10 AM, Jeremy Finzel wrote:
>     Thanks, I see... So if I understand it correctly - since I have
>     quite big partitions like ~30 GB each in one parent table and from
>     ~1GB to ~5 GB in several others I presume I had to set
>     wal_keep_segments to some really high number and stop our security
>     cronjob cleaning old WAL segments (because we already had some
>     problems with almost full disk due to old WAL segments) until the
>     whole transfer of snapshot is done. Because only after the whole
>     snapshot is transferred logical replication workers start to
>     transfer WAL logs reflecting changes done from the moment snapshot
>     was taken...
> 
>     jm
> 
> 
> Understand there are other downsides to just keeping around a huge 
> amount of WAL segments apart from only taking up disk space.  None of 
> the data held in those WAL segments can be vacuumed away while they are 
> left around, which can lead to significant bloat and performance issues 
> over time.

That is news to me. Can you provide a citation for this?

> 
> I'm not exactly clear on your use case, but if you need to just 
> resychronize data for a single table, there is a built-in way to do that 
> (actually would be nice if the docs spelled this out).
> 
> On publisher:
> 
> ALTER PUBLICATION mypub DROP TABLE old_data_table;
> 
> On subscriber:
> 
> ALTER SUBSCRIPTION mysub REFRESH PUBLICATION WITH ( COPY_DATA = true);
> 
> On publisher:
> 
> ALTER PUBLICATION mypub ADD TABLE old_data_table;
> 
> On subscriber:
> 
> ALTER SUBSCRIPTION mysub REFRESH PUBLICATION WITH ( COPY_DATA = true);
> 
> The last command will resync the table from the current table data, 
> regardless of the WAL file situation.  This is the "normal" way you 
> would go about resynchronizing data between clusters when a long time 
> has passed, rather than trying to keep all that WAL around!
> 
> So far as I can tell from testing, above pattern is the easiest way to 
> do this, and it will not resynchronize any of the other tables in your 
> subscription.
> 
> P.S. do heed the advice of the others and get more familiar with the 
> docs around WAL archiving.
> 
> Thanks,
> Jeremy


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi,

On 2019-01-22 14:18:12 +0100, Josef Machytka wrote:
> Hello, I already tried to ask on stackoverflow but so far without success.
> (
> https://stackoverflow.com/questions/54292816/postgresql-logical-replication-depends-on-wal-segments
> )
> 
> Could someone help me please?
> 
> ****
> 
> I am successfully using logical replication between 2 PG 11 cloud VMs for
> latest data. But I tried to publish also some older tables to transfer data
> between databases and got strange error about missing WAL segment.

That ought not to happen, logical replication won't start in the past,
and the slot will prevent necessary WAL from being removed.  Are you
manually removing WAL from pg_wal/?


Greetings,

Andres Freund

Hi,

On 2019-01-22 11:10:27 -0600, Jeremy Finzel wrote:
> P.S. do heed the advice of the others and get more familiar with the docs
> around WAL archiving.

Logical replication doesn't normally interact with WAL archiving in any
way, so that seems orthogonal.

Greetings,

Andres Freund

Hi,

On 2019-01-22 11:57:00 -0600, Jeremy Finzel wrote:
> >
> > That is news to me. Can you provide a citation for this?
> >
> 
> I can see the confusion in what I said.  To clarify, I really meant that in
> order to retrieve that data that you need in really old WAL segments, you
> need to keep your replication slot in a position that will hold that WAL in
> place.  And that is what will prevent old rows from being vacuumed
> away.

Note replication slots only prevent old *catalog* rows from being
removed, not old row versions in user created tables.

Greetings,

Andres Freund

On 22/1/19 8:59 μ.μ., Jeremy Finzel wrote:

On Tue, Jan 22, 2019 at 12:52 PM Andres Freund <andres@anarazel.de> wrote:

Hi,

On 2019-01-22 11:10:27 -0600, Jeremy Finzel wrote:
> P.S. do heed the advice of the others and get more familiar with the docs
> around WAL archiving.

Logical replication doesn't normally interact with WAL archiving in any
way, so that seems orthogonal.

But he is manually removing WAL files via cron which should normally be held until the replication slot doesn't need it anymore.  I do believe that is why he is getting errors.  The suggestion is to rely on Postgres' WAL archiving rather than removing WAL files manually...

wal retention in pg_wal and wal archiving are different things. The OP got problems cause he deliberately went on to delete files in pg_wal which means he used the wrong method to address a wrong situation.

However, if the OP has still those WAL files archived he can use them to bring logical replication back in synch. Don't ask me how I know : https://www.postgresql.org/message-id/ae8812c3-d138-73b7-537a-a273e15ef6e1%40matrix.gatewaynet.com

Thanks,
Jeremy 

On Tue, Jan 22, 2019 at 2:16 PM Jeremy Finzel <finzelj@gmail.com> wrote:

any one of those WAL files will not be archived and recycled as long as it no longer needed by a replication slot

:(.  s/no longer/still.  I meant to say any one of those WAL files will not be archived and recycled as long as it still needed by a replication slot.

A WAL segment will be archived as soon as it is completed. https://www.postgresql.org/docs/11/continuous-archiving.html


On Tue, Jan 22, 2019 at 8:19 PM Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

On 22/1/19 10:18 μ.μ., Jeremy Finzel wrote:

On Tue, Jan 22, 2019 at 2:16 PM Jeremy Finzel <finzelj@gmail.com> wrote:

any one of those WAL files will not be archived and recycled as long as it no longer needed by a replication slot

:(.  s/no longer/still.  I meant to say any one of those WAL files will not be archived and recycled as long as it still needed by a replication slot.

A WAL segment will be archived as soon as it is completed. https://www.postgresql.org/docs/11/continuous-archiving.html

Yep, you’re right. Let me try this again. It will be archived, but the segment won’t be reused or removed as long as the slot needs it. Is that an accurate statement?

That's better :) Also it depends on the previous checkpoint.

Thanks,

Jeremy 

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