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 1rUaec-00Boaf-87 for pgsql-general@arkaria.postgresql.org; Mon, 29 Jan 2024 23:03:03 +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 1rUaeb-0016RF-Ft for pgsql-general@arkaria.postgresql.org; Mon, 29 Jan 2024 23:03:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rUaea-0016R7-Ik for pgsql-general@lists.postgresql.org; Mon, 29 Jan 2024 23:03:01 +0000 Received: from wout2-smtp.messagingengine.com ([64.147.123.25]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rUaeW-004Pvg-F3 for pgsql-general@lists.postgresql.org; Mon, 29 Jan 2024 23:02:59 +0000 Received: from compute6.internal (compute6.nyi.internal [10.202.2.47]) by mailout.west.internal (Postfix) with ESMTP id 422ED3200A9C; Mon, 29 Jan 2024 18:02:53 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute6.internal (MEProxy); Mon, 29 Jan 2024 18:02:53 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:content-type:date :date:from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm2; t=1706569372; x=1706655772; bh=PH0B5XIK9ngI7D7XwGZZGd2bfQVTceQ/rMa/vFPkkNw=; b= kmTakPL27d+me8xhQlJRy0ItSIMo3GNHJqnhQ2aCF+0mpXmVeu1eXVN2qp8HYCfe UO+FHpgNvJfyrinjp0KbD3rprN6NC16qv2visx8CHDZtC6f/sAni5irDYeJZFY2f 3lnlCy2Lky7BRJ+sod1vGATcY9t2bmA1pskbH3fkE48lSqvrwA8jiyNbJW4LlHV0 m/mpdT1aINX8aZ4OC3hMIGN8ODaUm67xuV90pNlYbC16+n/RCqvNH88b0g6m72Ao JfXe6Ni6Yaa1PHHZr1E6k+W3/L+gQMUigHC+SME14hErU2AHQZQ66sRFw/rJ74I9 XANkLXbTvzKCYTBD6G8DjA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm3; t=1706569372; x= 1706655772; bh=PH0B5XIK9ngI7D7XwGZZGd2bfQVTceQ/rMa/vFPkkNw=; b=v luEi5jQO76+ioMRoAno6OevVlnqugK/lxmrWIeVEwJesW/4PPWXSleD3iXu3uEAE 5h5drhYmu+vfDf+Bi2p1YvD3QPPB73kdIklW8vtigesfyzxdvjROis9bw1VAybKG X0T0rjp7ale3bURVX5jYR1deCxsy5GghyO/8TANEmfNnzd7V18b9zk6V9fGk/eU3 /RQg8NuSYfNa5eNBPK/fe9OIxf4xqM/D3FtazVaHoeCxWUx+LtdZc5JlP4c9X6Uz ChViyWVWurVXT4zYz5vHw7huVkq8YFf+lD+cLvhGRMlpVRVNDWAv1kc5rUXe21bM 9iprBS+owovlJTybFGFBg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrfedthedgtdeiucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvvehfhfgjtgfgsehtjeertddtvdejnecuhfhrohhmpeetughr ihgrnhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtg homheqnecuggftrfgrthhtvghrnhepuedufeejjefggfdttdeghefgkeeuveekkeeiteet tdekffehiedvtefhveffgeeunecuffhomhgrihhnpehpohhsthhgrhgvshhqlhdrohhrgh enucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughr ihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Mon, 29 Jan 2024 18:02:52 -0500 (EST) Message-ID: Date: Mon, 29 Jan 2024 15:02:51 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Scriptable way to validate a pg_dump restore ? To: Shaheed Haque Cc: Laura Smith , postgre References: <7ad098f4-9844-4051-b100-8d3e2292be59@aklaver.com> Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 1/29/24 11:35, Shaheed Haque wrote: > > > On Tue, 30 Jan 2024, 00:27 Adrian Klaver, > wrote: > > On 1/29/24 10:12, Shaheed Haque wrote: > > > > > > Yes. But I was under the impression that the initial copy of logical > > replication was the same? > > > > Are you taking about the copy_data option to WITH? > > If so yes and no. > > Yes as it uses COPY to transfer the data. > > > Yes, this is what I meant. > > No as what COPY transfers can be affected by WHERE clauses on the > publisher. Also if you have cascading publishers/subscriptions the > 'original' data maybe upstream of the publisher you are comparing to. > > > Good points, understood. For the next bit, let's assume neither of these > are in play. > > Finally logical replication is generally not static so there is the > issue of determining a point in time for the check. > > > Indeed. I currently have a static source db but would eventually like to > eliminate the implied downtime. What I'd like to provide my user is some Implied downtime of what? > indication of progress initially during the copy_data phase, and for the > future, of the anticipated incremental convergence. > > And, as per my other note, I would ideally like to be able to do this > using only a connection to one db. > > I was assuming that logical replication needed "something" similar > internally, and was hoping the LSNs were that "something". I'm going to say up front I am no expert on the internals of logical replication. Will point you at: https://www.postgresql.org/docs/current/protocol-message-formats.html A quick look at that indicates to me it is more involved then you think. > > Thanks, Shaheed > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com