Re: viewing connectioninfo used by subscriber on the publication server when inactive

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)

>