Re: Minimal logical decoding on standbys

On 07/10/2019 05:12 PM, Amit Khandekar wrote:
> All right. Will do that in the next patch set. For now, I have quickly
> done the below changes in a single patch again (attached), in order to
> get early comments if any.
Thanks Amit for your patch. i am able to see 1 issues  on Standby server
- (where  logical replication slot created ) ,
a)size of  pg_wal folder  is NOT decreasing even after firing
get_changes function
b)pg_wal files are not recycling  and every time it is creating new
files after firing get_changes function

Here are the detailed steps -
create a directory with the name 'archive_dir' under /tmp (mkdir
/tmp/archive_dir)
*SR setup -*
*Master*
.)Perform initdb  (./initdb -D master --wal-segsize=2)
.)Open postgresql.conf file and add these below parameters  at the end
of  file
wal_level='logical'
min_wal_size=4MB
max_wal_size=4MB
hot_standby_feedback = on
archive_mode=on
archive_command='cp %p /tmp/archive_dir/%f'
.)Start the server ( /pg_ctl -D master/ start -l logsM -c )
.)Connect to psql , create physical  slot
    ->SELECT * FROM
pg_create_physical_replication_slot('decoding_standby');
*Standby - *
.)Perform pg_basebackup ( ./pg_basebackup -D standby/
--slot=decoding_standby -R -v)
.)Open postgresql.conf file of standby and add these 2 parameters - at
the end of file
  port=5555
primary_slot_name = 'decoding_standby'
.)Start the Standby server  ( ./pg_ctl -D standby/ start -l logsS -c )
.)Connect to psql terminal and create logical replication slot
  ->SELECT * from   pg_create_logical_replication_slot('standby',
'test_decoding');

*MISC steps**-
*.)Connect to master and create table/insert rows ( create table t(n
int); insert into t (values (1);)
.)Connect to standby and fire get_changes function ( select * from
pg_logical_slot_get_changes('standby',null,null); )
.)Run pgbench ( ./pgbench -i -s 10 postgres)
.)Check the pg_wal directory size of  STANDBY
[centos(at)mail-arts bin]$ du -sch standby/pg_wal/
127M    standby/pg_wal/
127M    total
[centos(at)mail-arts bin]$

.)Connect to standby and fire get_changes function ( select * from
pg_logical_slot_get_changes('standby',null,null); )
.)Check the pg_wal directory size of STANDBY
[centos(at)mail-arts bin]$ du -sch standby/pg_wal/
127M    standby/pg_wal/
127M    total
[centos(at)mail-arts bin]$

.)Restart both master and standby ( ./pg_ctl -D master restart -l logsM
-c) and (./pg_ctl -D standby restart -l logsS -c )

.)Check the pg_wal directory size of STANDBY
[centos(at)mail-arts bin]$ du -sch standby/pg_wal/
127M    standby/pg_wal/
127M    total
[centos(at)mail-arts bin]$

and if we see the pg_wal files ,it is growing rampant and not reusing.

--
regards,tushar
EnterpriseDB https://www.enterprisedb.com/
The Enterprise PostgreSQL Company