On 22/1/19 3:18 μ.μ., 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:
|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
