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
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