Logical replication - initial data synchronization

On Sat, May 18, 2024 at 09:02:11PM +0000, PG Doc comments form wrote:

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/16/logical-replication-subscription.html
Description:

I'm reading up on Logical Replication and have been reading the pages in
order.

The first 2 pages:
https://www.postgresql.org/docs/current/logical-replication.html and
https://www.postgresql.org/docs/current/logical-replication-publication.html
both speak of the requirement to set up a snapshot and explain that
publication will then send further updates as they happen to subscribers.

But the 3rd page,
https://www.postgresql.org/docs/current/logical-replication-subscription.html
now mentions this: "Additional replication slots may be required for the
initial data synchronization of pre-existing table data and those will be
dropped at the end of data synchronization."

For me, reading the first 2 pages implied that I would have to perform some
manual command that starts the creation of a snapshot of pre-existing table
data, and unpack this on the subscriber node somehow.

The text on the "Subscription" page sounds to me like this is actually
something the publisher<-> subscriber model of the postgres software can
manage on its own. As opposed to a snapshot, which feels more like the
concept of a basebackup.

Regardless of that being correct or not, my current impression is that the
description isn't consistent across pages. Maybe the text is obvious for
people who've performed setup of logical replication before, but I have
never done this. To me, the description on the first 2 pages seems
inconsistent with the description I just encountered on the 3rd page. I was
under the impression there was no such thing as "initial data
synchronization of pre-existing table data" in terms of postgres doing this
by itself.

Am I missing something extremely simple, or can the description of the
involved operations be made more consistent across documentation pages?

Is the attached patch an improvement?

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

When a patient asks the doctor, "Am I going to die?", he means
"Am I going to die soon?"

Hello Bruce, thanks for picking this up.

Personally, I would make explicit mention of the fact that creating the
snapshot and copying the data is taken care of by Postgres itself. Those
are the points that had me confused early on, wondering if I had to perform
the copy once the snapshot was ready.

Having used LR for months now, that seems weird as I write it, but I
remember it being part of my initial confusion.

Instead of:
" Internally logical replication of a table starts by taking a snapshot
of the data on the publisher database and copying that to the
subscriber."

I would say:
"When logical replication is started for a table, Postgres internally
takes a snapshot of the table data on the publisher database,
and then copies that data to the subscriber."

Also, I would change:

"Once complete, the changes on the publisher are sent to the subscriber"

To:

"Once complete, any changes on the publisher since the initial copy are
sent to the subscriber"

This is more explicit and clear, I feel.

And then to be consistent I'd also use this wording in the last change,
changing:

"publisher database. Once complete, changes on the publisher are sent"

to

"publisher database. Once complete, any changes on the publisher since the
initial copy are sent"

Hope that's ok.

Thanks for looking into this.

Regards,
Koen De Groote

On Thu, Oct 17, 2024 at 3:20 AM Bruce Momjian <bruce@momjian.us> wrote:

On Thu, Oct 17, 2024 at 10:59:51AM +0200, Koen De Groote wrote:

Hello Bruce, thanks for picking this up.

Personally, I would make explicit mention of the fact that creating the
snapshot and copying the data is taken care of by Postgres itself. Those are
the points that had me confused early on, wondering if I had to perform the
copy once the snapshot was ready.

Updated patch attached. I tried to tighten up the wording and add more
detail. I didn't see the point in repeating the same paragraph later on
so I removed it.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

When a patient asks the doctor, "Am I going to die?", he means
"Am I going to die soon?"

I would still add that the snapshot and data copying is handled by postgres
itself. That was the big question I was stuck with:"Who is taking this
snapshot? Do I have to do it? Will they explain how?"

Because it's left in the middle who does it. Once you know how logical
replication works, it's obvious, re-reading the documentation I know what
to expect, but that's only because I've already done it a few time snow.

As someone just starting and reading the documentation, it was a stumbling
block for me.

To me,

When logical replication of a table typically starts, a snapshot is
taken of the table's data on the publisher database and copied to the
subscriber

Does not clarify that.

It's the reason I created this mail: I would like it stated explicitly that
the database process takes care of this for us.

Regards,
Koen De Groote

On Thu, Oct 17, 2024 at 3:08 PM Bruce Momjian <bruce@momjian.us> wrote:

On Thu, Oct 17, 2024 at 09:57:04PM +0200, Koen De Groote wrote:

I would still add that the snapshot and data copying is handled by postgres
itself. That was the big question I was stuck with:"Who is taking this
snapshot? Do I have to do it? Will they explain how?"

Because it's left in the middle who does it. Once you know how logical
replication works, it's obvious, re-reading the documentation I know what to
expect, but that's only because I've already done it a few time snow.

As someone just starting and reading the documentation, it was a stumbling
block for me.

To me,

   When logical replication of a table typically starts, a snapshot is
   taken of the table's data on the publisher database and copied to the
  subscriber

Does not clarify that.

It's the reason I created this mail: I would like it stated explicitly that the
database process takes care of this for us.

Well, you are the first person to report this confusion, and we can't go
around explaining what Postgres does and does not do in each section. I
would need to hear from other people that this is confusing before
making it explicit.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

When a patient asks the doctor, "Am I going to die?", he means
"Am I going to die soon?"

On Thu, 2024-10-17 at 16:00 -0400, Bruce Momjian wrote:

To me,

   When logical replication of a table typically starts, a snapshot is
   taken of the table's data on the publisher database and copied to the
  subscriber

Does not clarify that.

It's the reason I created this mail: I would like it stated explicitly that the
database process takes care of this for us.

Well, you are the first person to report this confusion, and we can't go
around explaining what Postgres does and does not do in each section.  I
would need to hear from other people that this is confusing before
making it explicit.

I for one would have interpreted the passive voice here as meaning that the
database does that automatically. But perhaps active voice can make it even
clearer:

Ordinarily, when logical replication of a table starts,
<productname>PostgreSQL</productname> takes a snapshot of the table's
data on the publisher database and copies these data to the subscriber

Yours,
Laurenz Albe

But perhaps active voice can make it even clearer:

That would be nice. It immediately clarifies the actor, instead of
basically saying "it's expected that an action was performed".

To give some context, jobs over the past decade have taught me to work on a
default setting of "If it isn't explicitly stated, it cannot be assumed and
has to be tested first. Lack of explicit statement is lack of a guarantee,
and if you don't test it, you'll run into issues down the line."

To Bruce: I get that concern in general. But I feel like people don't
complain because at some point they figure it out, and then it's no longer
an issue for them. But it was initially, and it did cost them time. Most
people will work with PostgreSQL for their job and their job will demand
timely delivery from them. Sending mails like this is probably something
most people aren't going to take time for.

At least, that's how I see the situation. It may be presumptive of me.

I realize this has taken a bit of time already, and for my part, I would
like the more explicit statement, as shown by Laurenz, but I'm not going to
keep going on about it if it doesn't make its way in.

Regards,
Koen De Groote

On Fri, Oct 18, 2024 at 10:11 AM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On Fri, 2024-10-18 at 13:05 +0200, Koen De Groote wrote:

I feel like people don't complain because at some point they figure it out,
and then it's no longer an issue for them. But it was initially, and it did
cost them time. Most people will work with PostgreSQL for their job and
their job will demand timely delivery from them. Sending mails like this
is probably something most people aren't going to take time for.

It is the goal of the documentation to be factually correct and complete
while still being comprehensible. We don't always succeed in that.
PostgreSQL depends on people that contribute, and your contribution
is valued.

Yours,
Laurenz Albe

On Fri, Oct 18, 2024 at 7:11 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Thu, 2024-10-17 at 16:00 -0400, Bruce Momjian wrote:

To me,

When logical replication of a table typically starts, a snapshot is
taken of the table's data on the publisher database and copied to the
subscriber

Does not clarify that.

It's the reason I created this mail: I would like it stated explicitly that the
database process takes care of this for us.

Well, you are the first person to report this confusion, and we can't go
around explaining what Postgres does and does not do in each section. I
would need to hear from other people that this is confusing before
making it explicit.

I for one would have interpreted the passive voice here as meaning that the
database does that automatically. But perhaps active voice can make it even
clearer:

Ordinarily, when logical replication of a table starts,
<productname>PostgreSQL</productname> takes a snapshot of the table's
data on the publisher database and copies these data to the subscriber

+1 to say that it is PostgreSQL that does this.

It seems to me the same clarification could be achieved just by adding
1 word ("PostgreSQL") to the original text. e.g.

BEFORE
Logical replication of a table typically starts with taking a snapshot
of the data on the publisher database and copying that to the
subscriber.

AFTER #1 (I added "PostgreSQL")
Logical replication of a table typically starts with PostgreSQL taking
a snapshot of the data on the publisher database and copying that to
the subscriber.

Or, AFTER #2 (I added "PostgreSQL internally")
Logical replication of a table typically starts with PostgreSQL
internally taking a snapshot of the data on the publisher database and
copying that to the subscriber.

======
Kind Regards,
Peter Smith.
Fujitsu Australia

On Fri, 2024-10-25 at 17:22 +1100, Peter Smith wrote:

It seems to me the same clarification could be achieved just by adding
1 word ("PostgreSQL") to the original text. e.g.

BEFORE
Logical replication of a table typically starts with taking a snapshot
of the data on the publisher database and copying that to the
subscriber.

AFTER #1 (I added "PostgreSQL")
Logical replication of a table typically starts with PostgreSQL taking
a snapshot of the data on the publisher database and copying that to
the subscriber.

+1 on this version. Either this way or the way I suggested, it doesn't
matter as far as I am concerned.

Perhaps Koen wants to chime in and say what sounds best to him.

Or, AFTER #2 (I added "PostgreSQL internally")
Logical replication of a table typically starts with PostgreSQL
internally taking a snapshot of the data on the publisher database and
copying that to the subscriber.

Yours,
Laurenz Albe

Number 2 would be perfect, I think. It leaves no doubt at all.

Regards,
Koen De Groote

On Fri, Oct 25, 2024 at 9:32 AM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On Fri, Oct 25, 2024 at 11:48:16PM +0200, Koen De Groote wrote:

Number 2 would be perfect, I think. It leaves no doubt at all.

I went with even more direct wording, patch attached. Glad we could
improve this together --- the feedback from everyone was helpful.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

When a patient asks the doctor, "Am I going to die?", he means
"Am I going to die soon?"

And my thanks to everyone who took part.

Regards,
Koen De Groote

On Thu, Oct 31, 2024 at 4:16 PM Bruce Momjian <bruce@momjian.us> wrote:

On Fri, Nov 1, 2024 at 2:16 AM Bruce Momjian <bruce@momjian.us> wrote:

On Fri, Oct 25, 2024 at 11:48:16PM +0200, Koen De Groote wrote:

Number 2 would be perfect, I think. It leaves no doubt at all.

I went with even more direct wording, patch attached. Glad we could
improve this together --- the feedback from everyone was helpful.

One small nit -- I think "PostgreSQL" typically would have SGML markup:

<productname>PostgreSQL</productname>

======
Kind Regards,
Peter Smith.
Fujitsu Australia

On Fri, Nov 1, 2024 at 09:07:38AM +1100, Peter Smith wrote:

On Fri, Nov 1, 2024 at 2:16 AM Bruce Momjian <bruce@momjian.us> wrote:

On Fri, Oct 25, 2024 at 11:48:16PM +0200, Koen De Groote wrote:

Number 2 would be perfect, I think. It leaves no doubt at all.

I went with even more direct wording, patch attached. Glad we could
improve this together --- the feedback from everyone was helpful.

One small nit -- I think "PostgreSQL" typically would have SGML markup:

<productname>PostgreSQL</productname>

Yes, I thought so too, but if you look at the SGML, it uses PostgreSQL
with no markup in many places close to this sentence, e.g. "PostgreSQL
supports both mechanisms concurrently".

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

When a patient asks the doctor, "Am I going to die?", he means
"Am I going to die soon?"

On Thu, Oct 31, 2024 at 06:52:34PM -0400, Bruce Momjian wrote:

On Fri, Nov 1, 2024 at 09:07:38AM +1100, Peter Smith wrote:

On Fri, Nov 1, 2024 at 2:16 AM Bruce Momjian <bruce@momjian.us> wrote:

On Fri, Oct 25, 2024 at 11:48:16PM +0200, Koen De Groote wrote:

Number 2 would be perfect, I think. It leaves no doubt at all.

I went with even more direct wording, patch attached. Glad we could
improve this together --- the feedback from everyone was helpful.

One small nit -- I think "PostgreSQL" typically would have SGML markup:

<productname>PostgreSQL</productname>

Yes, I thought so too, but if you look at the SGML, it uses PostgreSQL
with no markup in many places close to this sentence, e.g. "PostgreSQL
supports both mechanisms concurrently".

Patch applied to master. :-)

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

When a patient asks the doctor, "Am I going to die?", he means
"Am I going to die soon?"

Thanks for letting us know, will be happy to see it appear.

Regards,
Koen De Groote

On Thu, Nov 21, 2024 at 11:15 PM Bruce Momjian <bruce@momjian.us> wrote:

On Fri, Nov 22, 2024 at 9:15 AM Bruce Momjian <bruce@momjian.us> wrote:

On Thu, Oct 31, 2024 at 06:52:34PM -0400, Bruce Momjian wrote:

On Fri, Nov 1, 2024 at 09:07:38AM +1100, Peter Smith wrote:

On Fri, Nov 1, 2024 at 2:16 AM Bruce Momjian <bruce@momjian.us> wrote:

On Fri, Oct 25, 2024 at 11:48:16PM +0200, Koen De Groote wrote:

Number 2 would be perfect, I think. It leaves no doubt at all.

I went with even more direct wording, patch attached. Glad we could
improve this together --- the feedback from everyone was helpful.

One small nit -- I think "PostgreSQL" typically would have SGML markup:

<productname>PostgreSQL</productname>

Yes, I thought so too, but if you look at the SGML, it uses PostgreSQL
with no markup in many places close to this sentence, e.g. "PostgreSQL
supports both mechanisms concurrently".

Patch applied to master. :-)

Sorry for the late nit, but when I read the re-worded text that was
pushed [1]https://github.com/postgres/postgres/commit/4c4aaa19a6fed39e0eb0247625331c3df34d8211, I felt the word "typically" was misplaced.

Maybe it is a personal opinion, but OTOH Chat-GPT has the same opinion as me:

------
Me:
Is "typically" in the correct place below?
When logical replication of a table typically starts, PostgreSQL takes
a snapshot of the table's data on the publisher database and copies it
to the subscriber.

ChatGPT said:
The placement of "typically" in your sentence is grammatically
correct, but it may cause slight confusion about what "typically" is
modifying. It seems to suggest that the timing of when logical
replication starts varies, which might not be the intended meaning.

If you mean that it is the process of taking a snapshot that typically
happens (not the timing of when replication starts), then "typically"
would be better placed after "PostgreSQL." Here's a revised version
for clarity:

"When logical replication of a table starts, PostgreSQL typically
takes a snapshot of the table's data on the publisher database and
copies it to the subscriber."

This makes it clear that "typically" refers to the snapshot-taking
process, not the timing of replication's start.
------

======
[1]: https://github.com/postgres/postgres/commit/4c4aaa19a6fed39e0eb0247625331c3df34d8211

Kind Regards,
Peter Smith.
Fujitsu Australia

On Mon, Nov 25, 2024 at 11:55 AM Peter Smith <smithpb2250@gmail.com> wrote:

On Fri, Nov 22, 2024 at 9:15 AM Bruce Momjian <bruce@momjian.us> wrote:

On Thu, Oct 31, 2024 at 06:52:34PM -0400, Bruce Momjian wrote:

On Fri, Nov 1, 2024 at 09:07:38AM +1100, Peter Smith wrote:

On Fri, Nov 1, 2024 at 2:16 AM Bruce Momjian <bruce@momjian.us> wrote:

On Fri, Oct 25, 2024 at 11:48:16PM +0200, Koen De Groote wrote:

Number 2 would be perfect, I think. It leaves no doubt at all.

I went with even more direct wording, patch attached. Glad we could
improve this together --- the feedback from everyone was helpful.

One small nit -- I think "PostgreSQL" typically would have SGML markup:

<productname>PostgreSQL</productname>

Yes, I thought so too, but if you look at the SGML, it uses PostgreSQL
with no markup in many places close to this sentence, e.g. "PostgreSQL
supports both mechanisms concurrently".

Patch applied to master. :-)

Sorry for the late nit, but when I read the re-worded text that was
pushed [1], I felt the word "typically" was misplaced.

Maybe it is a personal opinion, but OTOH Chat-GPT has the same opinion as me:

------
Me:
Is "typically" in the correct place below?
When logical replication of a table typically starts, PostgreSQL takes
a snapshot of the table's data on the publisher database and copies it
to the subscriber.

ChatGPT said:
The placement of "typically" in your sentence is grammatically
correct, but it may cause slight confusion about what "typically" is
modifying. It seems to suggest that the timing of when logical
replication starts varies, which might not be the intended meaning.

If you mean that it is the process of taking a snapshot that typically
happens (not the timing of when replication starts), then "typically"
would be better placed after "PostgreSQL." Here's a revised version
for clarity:

"When logical replication of a table starts, PostgreSQL typically
takes a snapshot of the table's data on the publisher database and
copies it to the subscriber."

This makes it clear that "typically" refers to the snapshot-taking
process, not the timing of replication's start.
------

======
[1] https://github.com/postgres/postgres/commit/4c4aaa19a6fed39e0eb0247625331c3df34d8211

Hi Bruce, There was no reply yet to my 3-week-old post above regarding
(what I thought was) the misplaced word "typically" so I am bumping
this thread, just in case that post was accidentally overlooked. OTOH,
if you disagree and/or don't plan to modify it, please let me know so
I can take this thread off my watch list.

======
Kind Regards,
Peter Smith.
Fujitsu Australia