pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Shaheed Haque <shaheedhaque@gmail.com>
Cc: Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.ch>
Cc: postgre <pgsql-general@lists.postgresql.org>
Subject: Re: Scriptable way to validate a pg_dump restore ?
Date: Mon, 29 Jan 2024 15:02:51 -0800
Message-ID: <d6d8d8d9-ceb0-48f2-afb3-4d4d254cebf3@aklaver.com> (raw)
In-Reply-To: <CAHAc2jetWnGO+i6We2S0vxjrFYLfD8M9aRY9s2K6RrDzP=E3_w@mail.gmail.com>
References: <kvT0Fu6tCy1S4kBbdka9EPDXqE4fV45KEfNB8zV5Kk82XzJ2cnhDDm60k_0KHm5MqzqdjoilVIvk76R_WjnHJYbLKDpaPdfMMJVPnebTQjA=@protonmail.ch>
	<e0fa9915-9356-45e6-891f-d24b6a274e7e@aklaver.com>
	<CAHAc2jfmPh1u1hpw2dNS+VcLQsapAU9D6sdSf1FDS5WgF-Wvpg@mail.gmail.com>
	<7ad098f4-9844-4051-b100-8d3e2292be59@aklaver.com>
	<CAHAc2jefssD1twugPeGJUnnonYMwhTNEbH=PMerh1sX6MtBwqw@mail.gmail.com>
	<ce54a2b0-dbbc-4915-b5d6-63839092d399@aklaver.com>
	<CAHAc2jetWnGO+i6We2S0vxjrFYLfD8M9aRY9s2K6RrDzP=E3_w@mail.gmail.com>

On 1/29/24 11:35, Shaheed Haque wrote:
> 
> 
> On Tue, 30 Jan 2024, 00:27 Adrian Klaver, <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> 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 <mailto:adrian.klaver@aklaver.com>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com






view thread (13+ messages)  latest in thread

Message-ID: <d6d8d8d9-ceb0-48f2-afb3-4d4d254cebf3@aklaver.com>
Permalink:  ../d6d8d8d9-ceb0-48f2-afb3-4d4d254cebf3@aklaver.com/
Also on:    postgresql.org/message-id/d6d8d8d9-ceb0-48f2-afb3-4d4d254cebf3@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, shaheedhaque@gmail.com, n5d9xq3ti233xiyif2vp@protonmail.ch
  Subject: Re: Scriptable way to validate a pg_dump restore ?
  In-Reply-To: <d6d8d8d9-ceb0-48f2-afb3-4d4d254cebf3@aklaver.com>
  References: <kvT0Fu6tCy1S4kBbdka9EPDXqE4fV45KEfNB8zV5Kk82XzJ2cnhDDm60k_0KHm5MqzqdjoilVIvk76R_WjnHJYbLKDpaPdfMMJVPnebTQjA=@protonmail.ch> <e0fa9915-9356-45e6-891f-d24b6a274e7e@aklaver.com> <CAHAc2jfmPh1u1hpw2dNS+VcLQsapAU9D6sdSf1FDS5WgF-Wvpg@mail.gmail.com> <7ad098f4-9844-4051-b100-8d3e2292be59@aklaver.com> <CAHAc2jefssD1twugPeGJUnnonYMwhTNEbH=PMerh1sX6MtBwqw@mail.gmail.com> <ce54a2b0-dbbc-4915-b5d6-63839092d399@aklaver.com> <CAHAc2jetWnGO+i6We2S0vxjrFYLfD8M9aRY9s2K6RrDzP=E3_w@mail.gmail.com> <d6d8d8d9-ceb0-48f2-afb3-4d4d254cebf3@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox