Re: Scriptable way to validate a pg_dump restore ?

On Tue, 30 Jan 2024, 00:27 Adrian Klaver, <adrian(dot)klaver(at)aklaver(dot)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
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".

Thanks, Shaheed

> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>