Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tRAEk-007iAC-0F for pgsql-general@arkaria.postgresql.org; Fri, 27 Dec 2024 13:18:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tRAEh-00A9az-VG for pgsql-general@arkaria.postgresql.org; Fri, 27 Dec 2024 13:18:39 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tRAEh-00A9XJ-6m for pgsql-general@lists.postgresql.org; Fri, 27 Dec 2024 13:18:39 +0000 Received: from mout02.posteo.de ([185.67.36.66]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tRAEc-000nqW-1t for pgsql-general@lists.postgresql.org; Fri, 27 Dec 2024 13:18:36 +0000 Received: from submission (posteo.de [185.67.36.169]) by mout02.posteo.de (Postfix) with ESMTPS id 0188A240103 for ; Fri, 27 Dec 2024 14:18:29 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=posteo.net; s=2017; t=1735305510; bh=YrikGOtLzR31AWBvnjg/b8xjtbyZl9byUijx3lreIFo=; h=Content-Type:Message-ID:Date:MIME-Version:Subject:To:Cc:From: From; b=n8404P5Coces118osbKTtas8h4VbGxB6WFkiotkHty5DEsW2qJ/R/1HaRs9vMCREH yD+qyOyVSn59dBU3pLNv/w6c57Of6CBRCbzwbwztAhYQlxxs6Jn9VINKR50VOENG/n mGUnq7sWqI5bJ9Gz8xuDwMr53+4k5YaRjdJn4dPqbUIPDotjqnASpHL6L79DIgjdpC hPVUzsgruelcMmuZoTx33aPYFYdIBdpm2KBkSdWjvC7nPlaDC2a+RzBWd2ln65Sasz VUFxq6imDt4ajByWq6Eo6tXPHeG6zp9v0jrz1yLF7bCitcg9Wbaj9Gr4E9ORJPPYme yPDeG6nRsyp5A== Received: from customer (localhost [127.0.0.1]) by submission (posteo.de) with ESMTPSA id 4YKQzn0KqZz9rxK; Fri, 27 Dec 2024 14:18:28 +0100 (CET) Content-Type: multipart/alternative; boundary="------------0aPX7dM6ezlDXGanNxxRdQkA" Message-ID: Date: Fri, 27 Dec 2024 13:18:27 +0000 MIME-Version: 1.0 Subject: Re: Starting logical replication at arbitrary point that's available in WAL To: "Zhijie Hou (Fujitsu)" Cc: "pgsql-general@lists.postgresql.org" References: Content-Language: en-US From: Alexander Uvizhev In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------0aPX7dM6ezlDXGanNxxRdQkA Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 12/27/24 13:20, Zhijie Hou (Fujitsu) wrote: > On Friday, December 27, 2024 7:39 PM Alexander Uvizhev wrote: > > Hi, > >> I'm doing a logical replication using streaming replication protocol and >> I'm trying to start a stream from a certain arbitrary point that's >> available in WAL. However, both CREATE_REPLICATION_SLOT and >> pg_create_logical_replication_slot() create slot with nearly last LSN/XID. >> >> Is it possible to create a replication slot with arbitrary LSN/XID values? > I think it's not supported to specify arbitrary LSN/XID values for a newly > created slot in core. However, if you have an existing slot with an older LSN/XID, you > can copy it using pg_copy_logical_replication_slot, and then advance the copied > slot to your desired position with pg_replication_slot_advance. Thanks for the idea, I've already discovered it and it requires me to implement some script to advance that dedicated slot, which I was hoping to avoid. >> Also pg_create_logical_replication_slot() for some reason gives >> different result than CREATE_REPLICATION_SLOT: new slot's `catalog_xmin` >> is set to the smallest `catalog_xmin` among already existing slots. >> Looks like a bug. > Could you provide a script to reproduce this issue ? > That would be helpful in diagnosing the reason. Unfortunately, I have no such script. But it looks like this: test=> select slot_name, catalog_xmin, restart_lsn, confirmed_flush_lsn from pg_replication_slots;   slot_name    | catalog_xmin | restart_lsn | confirmed_flush_lsn ---------------+--------------+-------------+---------------------  inactive_slot |        10073 | 0/1101DB88  | 0/1101DB88  active_slot |        42607 | 0/17A87410  | 0/17A87410  physical      |              | 0/17A875A0  | test=> select pg_create_logical_replication_slot('test_slot', 'decoderbufs');   slot_name    | catalog_xmin | restart_lsn | confirmed_flush_lsn ---------------+--------------+-------------+--------------------- inactive_slot|        10073 | 0/1101DB88  | 0/1101DB88 active_slot |        42607 | 0/17A87410  | 0/17A87410  physical      |              | 0/17A875D8  |  test_slot |        10073 | 0/17A875A0  | 0/17A875D8 While using CREATE_REPLICATION_SLOT would give me the same slot with `catalog_xmin` = 42607. Hope this helps. > Best Regards, > Hou zj -- AU --------------0aPX7dM6ezlDXGanNxxRdQkA Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

On 12/27/24 13:20, Zhijie Hou (Fujitsu) wrote:

On Friday, December 27, 2024 7:39 PM Alexander Uvizhev <uvizhe@posteo.net> wrote:

Hi,


I'm doing a logical replication using streaming replication protocol and
I'm trying to start a stream from a certain arbitrary point that's
available in WAL. However, both CREATE_REPLICATION_SLOT and
pg_create_logical_replication_slot() create slot with nearly last LSN/XID.

Is it possible to create a replication slot with arbitrary LSN/XID values?

I think it's not supported to specify arbitrary LSN/XID values for a newly
created slot in core. However, if you have an existing slot with an older LSN/XID, you
can copy it using pg_copy_logical_replication_slot, and then advance the copied
slot to your desired position with pg_replication_slot_advance.
Thanks for the idea, I've already discovered it and it requires me to implement some script to advance that dedicated slot, which I was hoping to avoid.

      

        
Also pg_create_logical_replication_slot() for some reason gives
different result than CREATE_REPLICATION_SLOT: new slot's `catalog_xmin`
is set to the smallest `catalog_xmin` among already existing slots.
Looks like a bug.


      

      
Could you provide a script to reproduce this issue ?
That would be helpful in diagnosing the reason.


    

    
Unfortunately, I have no such script. But
        it looks like this:

    test=> select slot_name, catalog_xmin,
      restart_lsn, confirmed_flush_lsn from pg_replication_slots;

      slot_name    | catalog_xmin | restart_lsn |
      confirmed_flush_lsn

    ---------------+--------------+-------------+---------------------

     inactive_slot |        10073 | 0/1101DB88  |
      0/1101DB88

     active_slot  
    |        42607 | 0/17A87410  |
      0/17A87410                           

     physical      |              | 0/17A875A0  |

    

    test=> select 
      pg_create_logical_replication_slot('test_slot', 'decoderbufs');

      slot_name    | catalog_xmin | restart_lsn |
      confirmed_flush_lsn

    
      ---------------+--------------+-------------+---------------------

    
       inactive_slot |        10073 | 0/1101DB88  | 0/1101DB88

     active_slot   |        42607 |
      0/17A87410  | 0/17A87410                           

    
       physical      |              | 0/17A875D8  |

       test_slot     |        10073 |
      0/17A875A0  | 0/17A875D8                           

    

    
While using CREATE_REPLICATION_SLOT
        would give me the same slot with `catalog_xmin` = 42607.

    
Hope this helps.

      

    

    

    

      
Best Regards,
Hou zj


    

    -- 

      AU

  


--------------0aPX7dM6ezlDXGanNxxRdQkA--