Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
gayatri235
Partner - Contributor III
Partner - Contributor III

Replication Slot size increasing rapidly for postgresql

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 !!

 

4 Replies
DesmondWOO
Support
Support

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

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
DesmondWOO
Support
Support

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

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
john_wang
Support
Support

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

         

john_wang_0-1708655168792.png

 

    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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
SushilKumar
Support
Support

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