Subject: Re: Minimal logical decoding on standbys
To: Amit Khandekar <amitdkhan.pg@gmail.com>,
 Andres Freund <andres@anarazel.de>
Cc: Robert Haas <robertmhaas@gmail.com>,
 Petr Jelinek <petr.jelinek@2ndquadrant.com>,
 Craig Ringer <craig@2ndquadrant.com>, Petr Jelinek <petr@2ndquadrant.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
References: 
 <CAJ3gD9dg0eQs5o1cJ_MSVVAy5EoiVsnez8nrZM0hWf6hYGHBww@mail.gmail.com>
 <CAJ3gD9dDb+Y88et1eNYd3t4gK3WnAFp5riEEzmwEB0inr+AvOw@mail.gmail.com>
 <20190604155853.6oiirwpbvcpubr5s@alap3.anarazel.de>
 <CAJ3gD9d0=Hs+PDWjA=3nEOzdgoKYgD74ZSa5hiCeJFUEV1f25A@mail.gmail.com>
 <CAJ3gD9csOr0LoYoMK9NnfBk0RZmvHXcJAFWFd2EuL=NOfz7PVA@mail.gmail.com>
 <CAJ3gD9eoPH4j-hMbq+_F9c5P=26tokkZGjB1eK4gjr1rcG7a4Q@mail.gmail.com>
 <20190619190138.rufzsld5xgcgshmh@alap3.anarazel.de>
 <CAJ3gD9f_HjQ6qP=+1jwzwy77fwcbT4-M3UvVsqpAzsY-jqM8nw@mail.gmail.com>
 <CA+Tgmoa6GxDQJ4G7zjyXOD4HEzPM7CWwsTcKPdRDnZ9NV4D13Q@mail.gmail.com>
 <CAJ3gD9end8xLBvZ07nbK2SVNOoeMRSye6+6r6=bp2wVvoa9ijw@mail.gmail.com>
 <20190710031424.dfhzlqj2tpvznibe@alap3.anarazel.de>
 <CAJ3gD9d4oN6ik+0ORrgVTOoO=Z3jsEGsfLLZTwtvUK1ZpF7YWQ@mail.gmail.com>
From: tushar <tushar.ahuja@enterprisedb.com>
Message-ID: <5a80cbcb-185c-fb81-02f2-7f26a8ea8efc@enterprisedb.com>
Date: Fri, 12 Jul 2019 14:53:21 +0530
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.3.0
MIME-Version: 1.0
In-Reply-To: 
 <CAJ3gD9d4oN6ik+0ORrgVTOoO=Z3jsEGsfLLZTwtvUK1ZpF7YWQ@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------6DADFC08A420C5A8528F421A"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------6DADFC08A420C5A8528F421A
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

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@mail-arts bin]$ du -sch standby/pg_wal/
127M    standby/pg_wal/
127M    total
[centos@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@mail-arts bin]$ du -sch standby/pg_wal/
127M    standby/pg_wal/
127M    total
[centos@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@mail-arts bin]$ du -sch standby/pg_wal/
127M    standby/pg_wal/
127M    total
[centos@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


--------------6DADFC08A420C5A8528F421A
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <div class="moz-cite-prefix">On 07/10/2019 05:12 PM, Amit Khandekar
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAJ3gD9d4oN6ik+0ORrgVTOoO=Z3jsEGsfLLZTwtvUK1ZpF7YWQ@mail.gmail.com">
      <pre wrap="">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.</pre>
    </blockquote>
    Thanks Amit for your patch. i am able to see 1 issues  on Standby
    server - (where  logical replication slot created ) ,  <br>
    a)size of  pg_wal folder  is NOT decreasing even after firing
    get_changes function <br>
    b)pg_wal files are not recycling  and every time it is creating new
    files after firing get_changes function<br>
    <br>
    Here are the detailed steps -<br>
    create a directory with the name 'archive_dir' under /tmp (mkdir
    /tmp/archive_dir)<br>
    <b>SR setup -</b><br>
    <b>Master</b><br>
    .)Perform initdb  (./initdb -D master --wal-segsize=2)<br>
    .)Open postgresql.conf file and add these below parameters  at the
    end of  file <br>
    wal_level='logical'<br>
    min_wal_size=4MB<br>
    max_wal_size=4MB<br>
    hot_standby_feedback = on<br>
    archive_mode=on<br>
    archive_command='cp %p /tmp/archive_dir/%f'<br>
    .)Start the server ( /pg_ctl -D master/ start -l logsM -c )<br>
    .)Connect to psql , create physical  slot<br>
        -&gt;SELECT * FROM
    pg_create_physical_replication_slot('decoding_standby'); <br>
    <b>Standby - </b><br>
    .)Perform pg_basebackup ( ./pg_basebackup -D standby/
    --slot=decoding_standby -R -v)<br>
    .)Open postgresql.conf file of standby and add these 2 parameters -
    at the end of file<br>
      port=5555<br>
    primary_slot_name = 'decoding_standby'<br>
    .)Start the Standby server  ( ./pg_ctl -D standby/ start -l logsS -c
    )<br>
    .)Connect to psql terminal and create logical replication slot <br>
      -&gt;SELECT * from   pg_create_logical_replication_slot('standby',
    'test_decoding');<br>
    <br>
    <b>MISC steps</b><b>- <br>
    </b>.)Connect to master and create table/insert rows ( create table
    t(n int); insert into t (values (1);) <br>
    .)Connect to standby and fire get_changes function ( select * from
    pg_logical_slot_get_changes('standby',null,null); ) <br>
    .)Run pgbench ( ./pgbench -i -s 10 postgres)<br>
    .)Check the pg_wal directory size of  STANDBY<br>
    [centos@mail-arts bin]$ du -sch standby/pg_wal/<br>
    127M    standby/pg_wal/<br>
    127M    total<br>
    [centos@mail-arts bin]$<br>
     
    <br>
    .)Connect to standby and fire get_changes function ( select * from
    pg_logical_slot_get_changes('standby',null,null); ) <br>
    .)Check the pg_wal directory size of STANDBY<br>
    [centos@mail-arts bin]$ du -sch standby/pg_wal/<br>
    127M    standby/pg_wal/<br>
    127M    total<br>
    [centos@mail-arts bin]$<br>
    <br>
    <br>
    .)Restart both master and standby ( ./pg_ctl -D master restart -l
    logsM -c) and (./pg_ctl -D standby restart -l logsS -c ) <br>
    <br>
    .)Check the pg_wal directory size of STANDBY<br>
    [centos@mail-arts bin]$ du -sch standby/pg_wal/<br>
    127M    standby/pg_wal/<br>
    127M    total<br>
    [centos@mail-arts bin]$<br>
    <br>
    and if we see the pg_wal files ,it is growing rampant and not
    reusing. <br>
    <pre class="moz-signature" cols="72">-- 
regards,tushar
EnterpriseDB  <a class="moz-txt-link-freetext" href="https://www.enterprisedb.com/">https://www.enterprisedb.com/</a>
The Enterprise PostgreSQL Company
</pre>
  </body>
</html>

--------------6DADFC08A420C5A8528F421A--