Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pyvZz-0007xU-W9 for pgsql-general@arkaria.postgresql.org; Tue, 16 May 2023 14:23:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pyvZy-0002cH-LP for pgsql-general@arkaria.postgresql.org; Tue, 16 May 2023 14:23:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pyvZy-0002bf-92 for pgsql-general@lists.postgresql.org; Tue, 16 May 2023 14:23:06 +0000 Received: from lana.depesz.com ([88.198.49.178] helo=depesz.com) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pyvZu-0009WG-SG for pgsql-general@lists.postgresql.org; Tue, 16 May 2023 14:23:05 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=depesz.com; s=20170201; h=Content-Type:MIME-Version:Reply-To:Message-ID:Subject:To:Sender :From:Date:Cc:Content-Transfer-Encoding:Content-ID:Content-Description: Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc:Resent-Message-ID: In-Reply-To:References:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=Q4Aa3IbU7UvJZpwI1GZGIpO3WCEt0nRXFvvfqlIC0TE=; b=rs3DRVUKY2rKHB1MTUnbeS8SFR Kr0ryCoTdFGjJIP7BEeOPD/usnQ93lSTfa+NS0gu1d6W8YiSjGnxbugsVUkcKzjIz/DnHsaGXlM5r hsTuORW5+N6258Vp3DuLDifQsSuHJgWC1RcjnulubVrc3l9kJusjQu5yMbgm/HoyOn+4=; Received: from depesz by depesz.com with local (Exim 4.94.2) (envelope-from ) id 1pyvZu-00AskH-2r for pgsql-general@lists.postgresql.org; Tue, 16 May 2023 16:23:02 +0200 Date: Tue, 16 May 2023 16:23:02 +0200 From: hubert depesz lubaczewski Sender: depesz@depesz.com To: pgsql-general Subject: How can I change replication slot's restart_lsn from SQL? Message-ID: Reply-To: depesz@depesz.com MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, I'm working on a workaround for a bug in Pg (https://www.postgresql.org/message-id/flat/Yz2hivgyjS1RfMKs%40depesz.com) I want to create replication slot, and advance is manually, keeping it always a bit lagging behind real replication slot. I can create slot, no problem: select pg_create_logical_replication_slot('depesz', 'test_decoding'); and then I can, theoretically, advance it to whatever position with select * from pg_replication_slot_advance('depesz', '...'); *BUT* - it changes only confirmed_flush_lsn, leaving restart_lsn as it was. How can I advance restart_lsn of a slot? Generally my idea is to get lsn from real replication slot, subtract, let's say 1GB from it, and advance my "fake slot" to this value, this keeping always buffer of 1GB in case the bug with removed wal happened again. I will be doing this on Pg12, which can limit my options, but perhaps there is some way to do it via plain(ish) SQL ? Best regards, depesz