Wim Bertels schreef op do 14-05-2020 om 16:22 [+0200]: > Keith Fiske schreef op do 14-05-2020 om 10:08 [-0400]: > > It doesn't matter how small the dataset change is. The same WAL > > stream is used for both logical and physical replication so it has > > to > > keep all WAL files until all subscribers for that publication have > > confirmed they have received them. If even a single subscriber goes > > offline, all WAL will be kept until that subscriber reconnects. > > That is interesting, i assume this the WAL for the whole cluster, as > logical decoding is then used on this WAL for the logical > replication, > do you have an estimate of order of magnitude for the all WAL files? > > So far this seems ok over here (with one subscriber inactive for 2 > days): > # du -ch pg_logical/ pg_wal/ pg_replslot/ > 912K pg_logical/snapshots > 4,0K pg_logical/mappings > 924K pg_logical/ > 4,0K pg_wal/archive_status > 81M pg_wal/ > 8,0K pg_replslot/db2_sub > 8,0K pg_replslot/db2_sub1 > 8,0K pg_replslot/db2_sub2 > 28K pg_replslot/ > 81M totaal > this after two days of replication setup. > > assuming that students will be offline for at most 2 or 3 days, > this seems ok? Follow-up info after 14 days: (this is a typical usecase) the main stress seems to be the cpu, the load is 3-6 as high as normal, storage and memory are ok pg_logical/snapshots takes the most storage in this use case (little writes, many async log replication users), not pg_wal 8,4G pg_logical/snapshots 4,0K pg_logical/mappings 8,4G pg_logical/ 4,0K pg_wal/archive_status 161M pg_wal/ 8,0K pg_replslot/db2_sub 8,0K pg_replslot/db2_sub_ma 8,0K pg_replslot/db2_sub_r074 8,0K pg_replslot/db2_sub_si 8,0K pg_replslot/db2_sub_r0604 8,0K pg_replslot/db2_sub_r0761 8,0K pg_replslot/db2_r0653 8,0K pg_replslot/nook_subscription 8,0K pg_replslot/r0745 8,0K pg_replslot/db2_sub_ma 8,0K pg_replslot/db2_sub_an 8,0K pg_replslot/db2_sub_th 8,0K pg_replslot/r07456 8,0K pg_replslot/db2_sub5 8,0K pg_replslot/mysub 12K pg_replslot/db2_sub_mart 8,0K pg_replslot/sub_db2 8,0K pg_replslot/db2_r653 8,0K pg_replslot/sub_rube 8,0K pg_replslot/db2_sub_ 8,0K pg_replslot/db2_sub_b 8,0K pg_replslot/db2_sub2 8,0K pg_replslot/db2_sub_b 8,0K pg_replslot/db2_sub73 8,0K pg_replslot/db2_sub_r74 8,0K pg_replslot/db2_sub_andr 8,0K pg_replslot/db2_0653 8,0K pg_replslot/db2_sub_i 8,0K pg_replslot/db2_pub 240K pg_replslot/ 8,6G totaal simple user logging: a psql outfile is 17MB after 14 days (where the replication connections are appended each minute) >
