I am trying to run a PostgreSQL (10.6) logical replication between two servers on one table only. The table has Id(int2) as a primary key. This is intentional and the table acts as a rolling window for some IoT time series data. It is heavy on writing on the Master node. The whole table has roughly 10 minutes worth of sensor data. And that is the design we like to keep.
Logical replication between Master and Replica nodes works great until there is a network outage lasting more than 1 hour. In the meantime, PostgreSQL on Master node is collecting WAL files with step by step insert/update on the table. So effectively WAL files might contain even hours of data which we are not interested in and they take forever to replay, step by step, from Master -> Replica when the connection restores. It is basically replaying records which long time don't exist in the database table!
How can I set it up so that only relevant data got replayed? If that is hard to do is there a way to throw away WAL files older than, say 10 minutes, so that they simply won't be sent?
I have tried to play with postgresql.conf settings. I am not sure if there is a flag I can limit the WAL files storing in a case of replication slot disconnect.
This is how the table looks like:
CREATE TABLE iot_ts (id int2 not null, time timestamp(0) not null, value real, primary key(id));
I would like to have a logical replication of such table set up so that when a long internet outage occurs the restoration is fast and contains only the most recent data.
