0

On AWS RDS PostgreSQL 10.7, I turned on logical replication for a one specific table .

on publisher:

CREATE PUBLICATION mypublication FOR TABLE <mytable>;

on subscriber:

CREATE SUBSCRIPTION mysub
         CONNECTION 'host=... port=... user=foo dbname=foodb'
        PUBLICATION mypublication;

However, this led to some interesting errors where the WAL files generated ate up > 100 times the size of the table I replicated and I had to shut it down. I started following the AWS guiding for troubleshooting this: https://aws.amazon.com/premiumsupport/knowledge-center/diskfull-error-rds-postgresql/

on AWS RDS, this was indicated by TransactionLogsDiskUsage metric.

It seems almost like it's trying to generate WAL files for any changes to the DB instead of just that table. This was indicated by OldestReplicationSlotLag increasing when there are write to any tables instead of the specified table.

I tried looking at postgres error logs on RDS but still can't really understand the cause. Is there way to limit the WAL files created by logical replication?

  • You mean that the pg_wal directory is growing in size, right? Look at the log file of the standby server. – Laurenz Albe Oct 7 at 2:51
1

The database generates WAL for the entire database instance. It is the logical sender's job to sort them out to restrict and construct what gets sent. I don't think there is a way to get the logical sender to work ahead. You should look at why it is falling behind, not why there is a lot of WAL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.