topic Re: Replication Slot size increasing rapidly for postgresql in Qlik Replicate

Replication Slot size increasing rapidly for postgresql

gayatri235 — Thu, 22 Feb 2024 19:07:53 GMT

Hi Team,

So here is the scenario,

We have created Full Load and Apply Changes task for source POSTGRESQL and target SYNAPSE.

Task was running smoothly, but suddenly the replication slot started to grew in GBs/min.

checked logs at verbose level,

I saw this "wal_table_filter_reptask(...) Table tablename does not match task's capture pattern" .

the tablename mentioned above is not there in the selected table for replication, why is this table present in the logs ?

What is causing the latency or replication slot growth ?

Any suggestions what parameter needs to be checked at source and target end ?

Thank you !!

Re: Replication Slot size increasing rapidly for postgresql

DesmondWOO — Fri, 23 Feb 2024 01:32:58 GMT

Hi @gayatri235 ,

Thank you for reaching out to the Qlik Community.

"wal_table_filter_reptask(...) Table tablename does not match task's capture pattern", this message should be printed when verbose logging is enabled. This means that Replicate can capture events for this table, but is naturally ignoring them because this table is not in your captured list.

Regards,
Desmond

Re: Replication Slot size increasing rapidly for postgresql

DesmondWOO — Fri, 23 Feb 2024 02:02:50 GMT

Hi @gayatri235 ,

Regarding "Replication Slot size increasing rapidly",

please check whether there is a maintenance job which generates a lot of changes
on the Replicate console, please check "Incoming Changes Details" to see whether changes are queued on Disk, as it may indicate a performance issue. If a slot is not advanced quickly enough, the disk space it uses (slot size) could continue to increase.

To comprehend the events that have occurred in your environment, we need to examine your task log. Should the issue continue to persist, I would suggest to submit a support ticket.

Regards.
Desmond

Re: Replication Slot size increasing rapidly for postgresql

john_wang — Fri, 23 Feb 2024 02:27:04 GMT

Hello @gayatri235 ,

I'm assuming you are meaning the WAL size (rather than slot size). Besides @DesmondWOO comments:

While using the PostgreSQL Logical Replication Slots, we must be careful enough at the WAL file size as an orphaned slot, or inactive slot, or a slow slot cannot reply the WAL fast enough, or other slot exceptions may lead the WAL files size increasing and impacts the whole PostgreSQL database. So monitor the WAL files sizes and the storage spaces is important for the DBA.
Here are a few limitations of PostgreSQL Replication Slots:

Orphaned Replication Slot: The WAL files are retained by the master when the replica disconnects. This also means that the pg_wal directory may run out of space. Imagine a scenario where the replica fails forever and cannot be recovered (an orphaned replication slot), or when a replica cannot replay the WAL segments fast enough.
Manually Monitoring pg_wal Directory: The WAL files will just pile up. So you need to monitor the slots and manually drop them. Only when you do that will the master delete anything from the pg_wal directory. You will see how you can monitor and drop replication slots later. So while WAL retention is taken care of without manual settings, pg_wal directory space needs to be manually monitored.

Some suggestions to avoid the storage spaces issues:
1. Allocate reasonable free spaces for WAL files, depends on the peak transactions volume size of the system
2. Enable the WAL heartbeat in the PostgreSQL source endpoint

3. Limit the size of a slot in PostgreSQL (You can apply max_slot_wal_keep_size on the source database in PostgreSQL 13 and later) ( AWS Database Migration Service (AWS DMS) is OEM of Qlik Replicate).

4. Delete the useless slot(s). Stop the Qlik Replicate task will not drop the slot, the slot should be dropped by the DBA.

BTW, if the slot is dropped to free up the spaces, then the slot should be created again. All the data changes during the previous task failure to the new slot creation cannot be captured anymore. The task should be reload and start capture changes from the slot creation time.

Hope this helps.
John.

Re: Replication Slot size increasing rapidly for postgresql

SushilKumar — Mon, 26 Feb 2024 06:00:49 GMT

Hello Team,

If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer.

Regards,

Sushil Kumar