Feedback-ID: i0c21471d:Fastmail
MIME-Version: 1.0
Date: Tue, 22 Apr 2025 22:57:53 -0300
From: "Euler Taveira" <euler@eulerto.com>
To: duffieldzane@gmail.com, pgsql-bugs@lists.postgresql.org
Message-Id: <86e60caf-d994-4b97-b504-22db9c825c52@app.fastmail.com>
In-Reply-To: <18897-d3db67535860dddb@postgresql.org>
References: <18897-d3db67535860dddb@postgresql.org>
Subject: Re: BUG #18897: Logical replication conflict after using
 pg_createsubscriber
 under heavy load
Content-Type: multipart/alternative;
 boundary=2ad8e923d1f549008ffd5cf8c42c6a83
Archived-At: 
 <https://www.postgresql.org/message-id/86e60caf-d994-4b97-b504-22db9c825c52%40app.fastmail.com>
Precedence: bulk

--2ad8e923d1f549008ffd5cf8c42c6a83
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Wed, Apr 16, 2025, at 8:14 PM, PG Bug reporting form wrote:
> I'm in the process of converting our databases from pglogical logical
> replication to the native logical replication implementation on PostgreSQL
> 17. One of the bugs we encountered and had to work around with pglogical was
> the plugin dropping records while converting to a streaming replica to
> logical via pglogical_create_subscriber (reported
> https://github.com/2ndQuadrant/pglogical/issues/349). I was trying to
> confirm that the native logical replication implementation did not have this
> problem, and I've found that it might have a different problem.

pg_createsubscriber uses a different approach than pglogical. While pglogical
uses a restore point, pg_createsubscriber uses the LSN from the latest
replication slot as a replication start point. The restore point approach is
usually suitable to physical replication but might not cover all scenarios for
logical replication (such as when there are in progress transactions). Since
creating a logical replication slot does find a consistent decoding start
point, it is a natural choice to start the logical replication (that also needs
to find a decoding start point).

> I should say that I've been operating under the assumption that
> pg_createsubscriber is designed for use on a replica for a *live* primary
> database, if this isn't correct then someone please let me know.

pg_createsubscriber expects a physical replica that is preferably stopped
before running it.

> I have a script that I've been using to reproduce the issue (pasted at end
> of email, because this bug reporting page doesn't seem to support
> attachments). It basically performs a loop that sets up a primary and a
> physical replica, generates some load, converts the replica to logical,
> waits, and makes sure the row counts are the same.

If I run your tests, it reports

$ NUM_THREADS=40 INSERT_WIDTH=1000 /tmp/logical_stress_test.sh
.
.

*** Successfully started logical replica on port 5341.
*** ALL INSERT LOOPS FINISHED
SOURCE COUNT =  916000
DEST COUNT   =  768000
ERROR: record count mismatch

but after some time

$ psql -X -p 5340 -c "SELECT count(f1) FROM test_table" -d test_db
count  
--------
916000
(1 row)

$ psql -X -p 5341 -c "SELECT count(f1) FROM test_table" -d test_db
count  
--------
916000
(1 row)

I also checked the data

$ pg_dump -t test_table -p 5340 -d test_db -f - | sort > /tmp/p.out
$ pg_dump -t test_table -p 5341 -d test_db -f - | sort > /tmp/r.out
$ diff -q /tmp/p.out /tmp/r.out
$ echo $?
0

Your script is not waiting enough time until it applies the backlog. Unless,
you are seeing a different symptom, there is no bug.

You should have used something similar to wait_for_subscription_sync routine
(Cluster.pm) before counting the rows. That's what is used in the
pg_createsubscriber tests. It guarantees the subscriber has caught up.


--
Euler Taveira
EDB   https://www.enterprisedb.com/

--2ad8e923d1f549008ffd5cf8c42c6a83
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html><html><head><title></title></head><body><div>On Wed, Apr =
16, 2025, at 8:14 PM, PG Bug reporting form wrote:</div><blockquote type=
=3D"cite" id=3D"qt" style=3D""><div>I'm in the process of converting our=
 databases from pglogical logical</div><div>replication to the native lo=
gical replication implementation on PostgreSQL</div><div>17. One of the =
bugs we encountered and had to work around with pglogical was</div><div>=
the plugin dropping records while converting to a streaming replica to</=
div><div>logical via pglogical_create_subscriber (reported</div><div><a =
href=3D"https://github.com/2ndQuadrant/pglogical/issues/349">https://git=
hub.com/2ndQuadrant/pglogical/issues/349</a>). I was trying to</div><div=
>confirm that the native logical replication implementation did not have=
 this</div><div>problem, and I've found that it might have a different p=
roblem.</div></blockquote><div><br></div><div>pg_createsubscriber uses a=
 different approach than pglogical. While pglogical</div><div>uses a res=
tore point, pg_createsubscriber uses the LSN from the latest</div><div>r=
eplication slot as a replication start point. The restore point approach=
 is</div><div>usually suitable to physical replication but might not cov=
er all scenarios for</div><div>logical replication (such as when there a=
re in progress transactions). Since</div><div>creating a logical replica=
tion slot does find a consistent decoding start</div><div>point, it is a=
 natural choice to start the logical replication (that also needs</div><=
div>to find a decoding start point).<br></div><div><br></div><blockquote=
 type=3D"cite" id=3D"qt" style=3D""><div>I should say that I've been ope=
rating under the assumption that</div><div>pg_createsubscriber is design=
ed for use on a replica for a *live* primary</div><div>database, if this=
 isn't correct then someone please let me know.</div></blockquote><div><=
br></div><div>pg_createsubscriber expects a physical replica that is pre=
ferably stopped</div><div>before running it.</div><div><br></div><blockq=
uote type=3D"cite" id=3D"qt" style=3D""><div>I have a script that I've b=
een using to reproduce the issue (pasted at end</div><div>of email, beca=
use this bug reporting page doesn't seem to support</div><div>attachment=
s). It basically performs a loop that sets up a primary and a</div><div>=
physical replica, generates some load, converts the replica to logical,<=
/div><div>waits, and makes sure the row counts are the same.</div></bloc=
kquote><div><br></div><div>If I run your tests, it reports</div><div><br=
></div><div>$ NUM_THREADS=3D40 INSERT_WIDTH=3D1000 /tmp/logical_stress_t=
est.sh</div><div>.</div><div>.</div><div><br></div><div>*** Successfully=
 started logical replica on port 5341.</div><div>*** ALL INSERT LOOPS FI=
NISHED</div><div>SOURCE COUNT =3D&nbsp; 916000</div><div>DEST COUNT&nbsp=
;&nbsp; =3D&nbsp; 768000</div><div>ERROR: record count mismatch</div><di=
v><br></div><div>but after some time</div><div><br></div><div>$ psql -X =
-p 5340 -c "SELECT count(f1) FROM test_table" -d test_db</div><div>count=
&nbsp;&nbsp;</div><div>--------</div><div>916000</div><div>(1 row)</div>=
<div><br></div><div>$ psql -X -p 5341 -c "SELECT count(f1) FROM test_tab=
le" -d test_db</div><div>count&nbsp;&nbsp;</div><div>--------</div><div>=
916000</div><div>(1 row)</div><div><br></div><div>I also checked the dat=
a</div><div><br></div><div>$ pg_dump -t test_table -p 5340 -d test_db -f=
 - | sort &gt; /tmp/p.out</div><div>$ pg_dump -t test_table -p 5341 -d t=
est_db -f - | sort &gt; /tmp/r.out</div><div>$ diff -q /tmp/p.out /tmp/r=
.out</div><div>$ echo $?</div><div>0</div><div><br></div><div>Your scrip=
t is not waiting enough time until it applies the backlog. Unless,</div>=
<div>you are seeing a different symptom, there is no bug.</div><div><br>=
</div><div>You should have used something similar to wait_for_subscripti=
on_sync routine</div><div>(Cluster.pm) before counting the rows. That's =
what is used in the</div><div>pg_createsubscriber tests. It guarantees t=
he subscriber has caught up.</div><div><br></div><div><br></div><div id=3D=
"sig112409080"><div class=3D"signature">--</div><div class=3D"signature"=
>Euler Taveira</div><div class=3D"signature">EDB&nbsp;&nbsp; <a href=3D"=
https://www.enterprisedb.com/">https://www.enterprisedb.com/</a></div></=
div><div><br></div></body></html>
--2ad8e923d1f549008ffd5cf8c42c6a83--