How do i disable or limit Postgres Replication creating causing too many WAL files generated?

Question

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?

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

Answer 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.