Subject: Re: Minimal logical decoding on standbys
To: Amit Khandekar <amitdkhan.pg@gmail.com>,
 Robert Haas <robertmhaas@gmail.com>
Cc: Andres Freund <andres@anarazel.de>,
 Petr Jelinek <petr.jelinek@2ndquadrant.com>,
 Craig Ringer <craig@2ndquadrant.com>, Petr Jelinek <petr@2ndquadrant.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
References: <20190527135602.dw6cp5vttbdb76i7@alap3.anarazel.de>
 <CAJ3gD9f11mhBURoOTAEJXoFAUF74BzSz=XRjL01EBB3SkLdVHA@mail.gmail.com>
 <20190530141723.ox5y4wda6alvo4uj@alap3.anarazel.de>
 <CAJ3gD9czydP5VnYPTW_TSLK8p26TOzHx3hPTPT-hdkLVasFVVQ@mail.gmail.com>
 <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>
From: tushar <tushar.ahuja@enterprisedb.com>
Message-ID: <4447fb6d-b308-3914-0f6b-d2eb5f7ca56d@enterprisedb.com>
Date: Thu, 4 Jul 2019 15:52: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: 
 <CAJ3gD9end8xLBvZ07nbK2SVNOoeMRSye6+6r6=bp2wVvoa9ijw@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------DEA66640FBAAB50751232DA4"
Content-Language: en-US
Precedence: bulk

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

On 07/01/2019 11:04 AM, Amit Khandekar wrote:
> Also, in the updated patch (v11), I have added some scenarios that
> verify that slot is dropped when either master wal_level is
> insufficient, or when slot is conflicting. Also organized the test
> file a bit.

One scenario where replication slot removed even after fixing the 
problem (which Error message suggested to do)

Please refer this below scenario

Master cluster-
postgresql,conf file
wal_level=logical
hot_standby_feedback = on
port=5432

Standby cluster-
postgresql,conf file
wal_level=logical
hot_standby_feedback = on
port=5433

both Master/Slave cluster are up and running and are in SYNC with each other
Create a logical replication slot on SLAVE ( SELECT * from 
pg_create_logical_replication_slot('m', 'test_decoding'); )

change wal_level='hot_standby' on Master postgresql.conf file / restart 
the server
Run get_changes function on Standby -
postgres=# select * from pg_logical_slot_get_changes('m',null,null);
ERROR:  logical decoding on standby requires wal_level >= logical on master

Correct it on Master postgresql.conf file ,i.e set wal_level='logical'  
again / restart the server
and again fire  get_changes function on Standby -
postgres=# select * from pg_logical_slot_get_changes('m',null,null);
*ERROR:  replication slot "m" does not exist

*This looks little weird as slot got dropped/removed internally . i 
guess it should get invalid rather than removed automatically.
Lets user's  delete the slot themself rather than automatically removed  
as a surprise.

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


--------------DEA66640FBAAB50751232DA4
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/01/2019 11:04 AM, Amit Khandekar
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAJ3gD9end8xLBvZ07nbK2SVNOoeMRSye6+6r6=bp2wVvoa9ijw@mail.gmail.com">
      <pre wrap="">Also, in the updated patch (v11), I have added some scenarios that
verify that slot is dropped when either master wal_level is
insufficient, or when slot is conflicting. Also organized the test
file a bit.</pre>
    </blockquote>
    <p>One scenario where replication slot removed even after fixing the
      problem (which Error message suggested to do)<br>
    </p>
    <p>Please refer this below scenario<br>
    </p>
    Master cluster-<br>
    postgresql,conf file <br>
    wal_level=logical<br>
    hot_standby_feedback = on<br>
    port=5432<br>
    <br>
    Standby cluster-<br>
    postgresql,conf file <br>
    wal_level=logical<br>
    hot_standby_feedback = on<br>
    port=5433<br>
    <br>
    both Master/Slave cluster are up and running and are in SYNC with
    each other<br>
    Create a logical replication slot on SLAVE ( SELECT * from  
    pg_create_logical_replication_slot('m', 'test_decoding'); )<br>
    <br>
    change wal_level='hot_standby' on Master postgresql.conf file /
    restart the server <br>
    Run get_changes function on Standby -<br>
    postgres=# select * from pg_logical_slot_get_changes('m',null,null);
    <br>
    ERROR:  logical decoding on standby requires wal_level &gt;= logical
    on master<br>
    <br>
    Correct it on Master postgresql.conf file ,i.e set 
    wal_level='logical'  again / restart the server <br>
    and again fire  get_changes function on Standby -<br>
    postgres=# select * from pg_logical_slot_get_changes('m',null,null);
    <br>
    <b>ERROR:  replication slot "m" does not exist<br>
      <br>
    </b>This looks little weird as slot got dropped/removed internally .
    i guess it should get invalid rather than removed automatically. <br>
    Lets user's  delete the slot themself rather than automatically
    removed  as a surprise.<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>

--------------DEA66640FBAAB50751232DA4--