Hi guys, thank you very much for all information. I learned my lesson regarding cronjob cleaning old WAL logs...
There is one other interesting problem I have found today and I would like to ask you about you opinion.
On logical master I found this morning big flood of these messages in postgresql log:
2019-01-23 08:17:42.338 UTC [1310] WARNING: oldest xmin is far in the past
2019-01-23 08:17:42.338 UTC [1310] HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
2019-01-23 08:17:42.338 UTC [1310] HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
Since this is still testing environment I dropped subscription and publication and logical replication slot and messages stopped.
But do you know what was wrong? Why these warnings? Did logical replication slot blocked autovacuum or something else happened?
Can I prevent it?
As mentioned before we have quite huge amount of data, around 500 millions of records every day, most of them inserted in separate transactions or in only small blocks so I guess xmin value can grow very quickly...
Thanks
jm
On Tue, 22 Jan 2019 at 20:03, Jeremy Finzel <finzelj@xxxxxxxxx> wrote:
Note replication slots only prevent old *catalog* rows from being
removed, not old row versions in user created tables.Thank you for that clarification. I can see this is noted clearly in the CAUTION statement here: https://www.postgresql.org/docs/current/logicaldecoding-explanation.html#LOGICALDECODING-REPLICATION-SLOTSThanks,Jeremy
