Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h0liC-0000lZ-8l for pgsql-hackers@arkaria.postgresql.org; Mon, 04 Mar 2019 11:24:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1h0li9-0007Aj-Ie for pgsql-hackers@arkaria.postgresql.org; Mon, 04 Mar 2019 11:24:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h0li9-0007Ac-5B for pgsql-hackers@lists.postgresql.org; Mon, 04 Mar 2019 11:24:45 +0000 Received: from mail-pg1-x544.google.com ([2607:f8b0:4864:20::544]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h0li5-0002dt-1Y for pgsql-hackers@postgresql.org; Mon, 04 Mar 2019 11:24:44 +0000 Received: by mail-pg1-x544.google.com with SMTP id m2so2903063pgl.5 for ; Mon, 04 Mar 2019 03:24:40 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb-com.20150623.gappssmtp.com; s=20150623; h=subject:to:cc:references:from:message-id:date:user-agent :mime-version:in-reply-to:content-language; bh=/QOboux1SUi/4DLcAKuEfHliiSBOPjMmGHAl2sckyDs=; b=UgSUa5Ixv8rRlMmfAj2Fzt8IBPHKWqwWor3ulsi78OtN1/V8msB/Yd440s0b26My79 j+86658fGDBWSopb7B9G80WEAADDnB+qRhGv8s1zH8KcxeBAbYwIzm2NIhc0lZQYYTjr vDhUtiZAyOw6oddLJSjAOyMwvKTpN+lN7pxj8Q+m8RRMI/wWPn5oV0QF65AORxapyici lYs1Mw6kwi5Lpm4kXhZ7GPCB0+L9rqLCfXIHxugTvmIv6xYMRNzpCDWn8RaMlsGO9mmv eBu6Fmc4cTcOeE3g8FNzDTj3zkf0ee+AC8e6eDFGlPj++qAS6ZveVz5f/jcBPg5pAIrL W+AQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:cc:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=/QOboux1SUi/4DLcAKuEfHliiSBOPjMmGHAl2sckyDs=; b=Lk9THlzUKvzfKarQ+1Z/M+Yq6SJpHEROqkWR/miHQCtdJPwyLQ48a9s43S2FMeghUt vg7fWKKj/YzuMwpMliwGWVvXLESs+bs5E5VEvXkayuLWLPugdP+C8+yBZ716H5IAuivt DGwCkrojSa6SZVBwTYyhk1Qfx3tndq5HAW+G2GiiKLUK6zlCEyaWqy5Ycwp8flLngm5j 2JmTkVJ7V59DOpZmYgXJRK60xo2VZ9KdzUdM9HkmQKqFVC6FEcp/QavDZ9n4p/bQA4vm KdJyrk2kqmDRlN8Ii7GQZTOJIh+BVefleaEIvqZ4jWc2G3BABZFTKk0NqPibkFq2CInx Qekw== X-Gm-Message-State: APjAAAVSLun4npw+xKE7jPGi0Fs+iYeIPglFiaNgnvpwwzFJGZ/CBCk8 ju2avTlEf1cuV5q0pSQK45xYjoZcz8q3TMlyfLlpPcwWzs/VTEz9+1bH9/Ldylsh0PseWwsi+om 4lyKHX/9wd0OCl9lIYlJQ1DLUlTqObgmij9OgCOwRL5sPhQGAR0eR22rRSaCd9C83l1LAtUUIu9 46WszpACU9Yw== X-Google-Smtp-Source: APXvYqxX1ggsPAnchOZs3p5/SEAxHJPpTRqh+l2OfB4wx7xZni14v6b09fLCTt+i2JTzq1yfGdOMLQ== X-Received: by 2002:a65:4784:: with SMTP id e4mr17792304pgs.12.1551698678033; Mon, 04 Mar 2019 03:24:38 -0800 (PST) Received: from localhost.localdomain ([114.143.109.178]) by smtp.gmail.com with ESMTPSA id i72sm12868698pfj.147.2019.03.04.03.24.35 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 04 Mar 2019 03:24:37 -0800 (PST) Subject: Re: Minimal logical decoding on standbys To: Andres Freund Cc: Petr Jelinek , Robert Haas , Craig Ringer , Petr Jelinek , pgsql-hackers@postgresql.org References: <20181212204154.nsxf3gzqv3gesl32@alap3.anarazel.de> <7e905539-7fdc-6b29-5a56-ba48d180fef8@2ndquadrant.com> <20190301174628.rjzoi7arvci5ldcm@alap3.anarazel.de> From: tushar Message-ID: Date: Mon, 4 Mar 2019 16:54:32 +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: <20190301174628.rjzoi7arvci5ldcm@alap3.anarazel.de> Content-Type: multipart/alternative; boundary="------------C3E65B59E024E49806C29923" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------C3E65B59E024E49806C29923 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 03/01/2019 11:16 PM, Andres Freund wrote: > So, if I understand correctly you do*not* have a phyiscal replication > slot for this standby? For the feature to work reliably that needs to > exist, and you need to have hot_standby_feedback enabled. Does having > that fix the issue? Ok, This time around  - I performed like this - .)Master cluster (set wal_level=logical and hot_standby_feedback=on in postgresql.conf) , start the server and create a physical replication slot postgres=# SELECT * FROM pg_create_physical_replication_slot('decoding_standby');     slot_name     | lsn ------------------+-----  decoding_standby | (1 row) .)Perform pg_basebackup using --slot=decoding_standby  with option -R . modify port=5555 , start the server .)Connect to slave and create a logical replication slot postgres=# create table t(n int); ERROR:  cannot execute CREATE TABLE in a read-only transaction postgres=# postgres=# SELECT * FROM pg_create_logical_replication_slot('standby_slot', 'test_decoding');   slot_name   |    lsn --------------+-----------  standby_slot | 0/2000060 (1 row) run pgbench (./pgbench -i -s 10 postgres) against  master and simultaneously- start  pg_recvlogical  , provide port=5555 ( slave cluster)  and specify slot=standby_slot ./pg_recvlogical -d postgres  -p 5555 -s 1 -F 1  -v --slot=standby_slot  --start -f - [centos@centos-cpula bin]$ ./pg_recvlogical -d postgres  -p 5555 -s 1 -F 1  -v --slot=standby_slot  --start -f - pg_recvlogical: starting log streaming at 0/0 (slot standby_slot) pg_recvlogical: streaming initiated pg_recvlogical: confirming write up to 0/0, flush to 0/0 (slot standby_slot) pg_recvlogical: confirming write up to 0/30194E8, flush to 0/30194E8 (slot standby_slot) pg_recvlogical: confirming write up to 0/3019590, flush to 0/3019590 (slot standby_slot) pg_recvlogical: confirming write up to 0/3019590, flush to 0/3019590 (slot standby_slot) pg_recvlogical: confirming write up to 0/3019590, flush to 0/3019590 (slot standby_slot) pg_recvlogical: confirming write up to 0/3019590, flush to 0/3019590 (slot standby_slot) pg_recvlogical: confirming write up to 0/3019590, flush to 0/3019590 (slot standby_slot) pg_recvlogical: confirming write up to 0/3019590, flush to 0/3019590 (slot standby_slot) pg_recvlogical: confirming write up to 0/3019590, flush to 0/3019590 (slot standby_slot) pg_recvlogical: confirming write up to 0/3019590, flush to 0/3019590 (slot standby_slot) pg_recvlogical: confirming write up to 0/3019590, flush to 0/3019590 (slot standby_slot) pg_recvlogical: confirming write up to 0/301D558, flush to 0/301D558 (slot standby_slot) BEGIN 476 COMMIT 476 pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot) pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot) pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot) pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot) pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot) pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot) pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot) pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot) pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot) pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot) pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot) pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot) pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot) pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot) pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot) BEGIN 477 COMMIT 477 If we do the same for the logical replication slot which created on Master cluster [centos@centos-cpula bin]$ ./pg_recvlogical -d postgres  -s 1 -F 1 -v --slot=master_slot  --start -f - pg_recvlogical: starting log streaming at 0/0 (slot master_slot) pg_recvlogical: streaming initiated pg_recvlogical: confirming write up to 0/0, flush to 0/0 (slot master_slot) table public.pgbench_accounts: INSERT: aid[integer]:65057 bid[integer]:1 abalance[integer]:0 filler[character]:' ' table public.pgbench_accounts: INSERT: aid[integer]:65058 bid[integer]:1 abalance[integer]:0 filler[character]:' ' table public.pgbench_accounts: INSERT: aid[integer]:65059 bid[integer]:1 abalance[integer]:0 filler[character]:' ' table public.pgbench_accounts: INSERT: aid[integer]:65060 bid[integer]:1 abalance[integer]:0 filler[character]:' ' table public.pgbench_accounts: INSERT: aid[integer]:65061 bid[integer]:1 abalance[integer]:0 filler[character]:' ' table public.pgbench_accounts: INSERT: aid[integer]:65062 bid[integer]:1 abalance[integer]:0 filler[character]:' ' table public.pgbench_accounts: INSERT: aid[integer]:65063 bid[integer]:1 abalance[integer]:0 filler[character]:' ' table public.pgbench_accounts: INSERT: aid[integer]:65064 bid[integer]:1 abalance[integer]:0 filler[character]:' ' -- regards,tushar EnterpriseDB https://www.enterprisedb.com/ The Enterprise PostgreSQL Company --------------C3E65B59E024E49806C29923 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
On 03/01/2019 11:16 PM, Andres Freund wrote:
So, if I understand correctly you do *not* have a phyiscal replication
slot for this standby? For the feature to work reliably that needs to
exist, and you need to have hot_standby_feedback enabled. Does having
that fix the issue?

Ok, This time around  - I performed like this -

.)Master cluster (set wal_level=logical and  hot_standby_feedback=on in postgresql.conf) , start the server  and create a physical replication slot

postgres=# SELECT * FROM pg_create_physical_replication_slot('decoding_standby');
    slot_name     | lsn
------------------+-----
 decoding_standby |
(1 row)

.)Perform pg_basebackup using --slot=decoding_standby  with option -R . modify port=5555 , start the server

.)Connect to slave and create a logical replication slot

postgres=# create table t(n int);
ERROR:  cannot execute CREATE TABLE in a read-only transaction
postgres=#

postgres=# SELECT * FROM pg_create_logical_replication_slot('standby_slot', 'test_decoding');
  slot_name   |    lsn   
--------------+-----------
 standby_slot | 0/2000060
(1 row)

run pgbench (./pgbench -i -s 10 postgres) against  master and  simultaneously- start  pg_recvlogical  , provide port=5555 ( slave cluster)  and specify slot=standby_slot
./pg_recvlogical -d postgres  -p 5555 -s 1 -F 1  -v --slot=standby_slot  --start -f -


[centos@centos-cpula bin]$ ./pg_recvlogical -d postgres  -p 5555 -s 1 -F 1  -v --slot=standby_slot  --start -f -
pg_recvlogical: starting log streaming at 0/0 (slot standby_slot)
pg_recvlogical: streaming initiated
pg_recvlogical: confirming write up to 0/0, flush to 0/0 (slot standby_slot)
pg_recvlogical: confirming write up to 0/30194E8, flush to 0/30194E8 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3019590, flush to 0/3019590 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3019590, flush to 0/3019590 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3019590, flush to 0/3019590 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3019590, flush to 0/3019590 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3019590, flush to 0/3019590 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3019590, flush to 0/3019590 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3019590, flush to 0/3019590 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3019590, flush to 0/3019590 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3019590, flush to 0/3019590 (slot standby_slot)
pg_recvlogical: confirming write up to 0/301D558, flush to 0/301D558 (slot standby_slot)
BEGIN 476
COMMIT 476
pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot)
pg_recvlogical: confirming write up to 0/3034B40, flush to 0/3034B40 (slot standby_slot)
BEGIN 477
COMMIT 477

If we do the same for the logical replication slot which created on Master cluster 

[centos@centos-cpula bin]$ ./pg_recvlogical -d postgres  -s 1 -F 1  -v --slot=master_slot  --start -f -
pg_recvlogical: starting log streaming at 0/0 (slot master_slot)
pg_recvlogical: streaming initiated
pg_recvlogical: confirming write up to 0/0, flush to 0/0 (slot master_slot)
table public.pgbench_accounts: INSERT: aid[integer]:65057 bid[integer]:1 abalance[integer]:0 filler[character]:'                                                                                    '
table public.pgbench_accounts: INSERT: aid[integer]:65058 bid[integer]:1 abalance[integer]:0 filler[character]:'                                                                                    '
table public.pgbench_accounts: INSERT: aid[integer]:65059 bid[integer]:1 abalance[integer]:0 filler[character]:'                                                                                    '
table public.pgbench_accounts: INSERT: aid[integer]:65060 bid[integer]:1 abalance[integer]:0 filler[character]:'                                                                                    '
table public.pgbench_accounts: INSERT: aid[integer]:65061 bid[integer]:1 abalance[integer]:0 filler[character]:'                                                                                    '
table public.pgbench_accounts: INSERT: aid[integer]:65062 bid[integer]:1 abalance[integer]:0 filler[character]:'                                                                                    '
table public.pgbench_accounts: INSERT: aid[integer]:65063 bid[integer]:1 abalance[integer]:0 filler[character]:'                                                                                    '
table public.pgbench_accounts: INSERT: aid[integer]:65064 bid[integer]:1 abalance[integer]:0 filler[character]:'                                                                                    '

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