Re: Scriptable way to validate a pg_dump restore ?

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