public inbox for [email protected]
help / color / mirror / Atom feedFrom: Hayato Kuroda (Fujitsu) <[email protected]>
To: 'Amit Kapila' <[email protected]>
Cc: Alexander Lakhin <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Euler Taveira <[email protected]>
Cc: Shlok Kyal <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: Bharath Rupireddy <[email protected]>
Cc: [email protected] <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Ashutosh Bapat <[email protected]>
Cc: FabrÃzio de Royes Mello <[email protected]>
Cc: vignesh C <[email protected]>
Subject: RE: speed up a logical replica setup
Date: Wed, 3 Jul 2024 07:12:38 +0000
Message-ID: <OSBPR01MB2552440DCE1AE5EDC51BAEC6F5DD2@OSBPR01MB2552.jpnprd01.prod.outlook.com> (raw)
In-Reply-To: <CAA4eK1+Qmc34cooSNm2=U6YsySSjZTn2_eD_deDFEAZv+aj-AA@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<TYCPR01MB12077907C052EC329C45AE198F5362@TYCPR01MB12077.jpnprd01.prod.outlook.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CAA4eK1KPoLUUGzjw9eEQd56LUAf2QQj8BqMcsoSe6vycB5CYUQ@mail.gmail.com>
<[email protected]>
<CAA4eK1JJq_ER6Kq_H=jKHR75QPRd8y9_D=RtYw=aPYKMfqLi9A@mail.gmail.com>
<CAA4eK1KdCb+5sjYu6qCMXXdCX1y_ihr8kFzMozq0=P=auYxgog@mail.gmail.com>
<CANhcyEV6q1Vhd37i1axUeScLi0UAGVxta1LDa0BV0Eh--TcPMg@mail.gmail.com>
<CAA4eK1JaQcAZ=n513-4VDW4ujE9T9f007fczNCpVDbg1smhT4g@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<OSBPR01MB25528300C71FDD83EA1DCA12F5DD2@OSBPR01MB2552.jpnprd01.prod.outlook.com>
<CAA4eK1+Qmc34cooSNm2=U6YsySSjZTn2_eD_deDFEAZv+aj-AA@mail.gmail.com>
Dear Amit,
> Your analysis looks correct to me. The test could fail due to
> autovacuum. See the following comment in
> 040_standby_failover_slots_sync.
>
> # Disable autovacuum to avoid generating xid during stats update as otherwise
> # the new XID could then be replicated to standby at some random point making
> # slots at primary lag behind standby during slot sync.
> $publisher->append_conf('postgresql.conf', 'autovacuum = off');
>
Oh, I could not find the comment. I felt it should be added even in
040_pg_createsubscriber.pl. Done.
> > # Descriptions for attached files
> >
> > An attached script can be used to reproduce the first failure without
> pg_createsubscriber.
> > It requires to modify the code like [1].
>
> > 0003 patch disables autovacuum for node_p and node_s. I think node_p is
> enough, but did
> > like that just in case. This fixes a second failure.
> >
>
> Disabling on the primary node should be sufficient. Let's do the
> minimum required to stabilize this test.
+1, removed.
PSA new version. 0001 has not been changed yet. A comment was added
in 0002 to clarify why we must wait. For 0003, a comment was added and
setting for standby was reverted.
Best Regards,
Hayato Kuroda
FUJITSU LIMITED
https://www.fujitsu.com/
Attachments:
[application/octet-stream] v3-0001-emit-dummy-message-while-setting-up-the-publisher.patch (1.6K, ../OSBPR01MB2552440DCE1AE5EDC51BAEC6F5DD2@OSBPR01MB2552.jpnprd01.prod.outlook.com/2-v3-0001-emit-dummy-message-while-setting-up-the-publisher.patch)
download | inline diff:
From e66878760f87687c072a3ff902cba3bfed298b15 Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <[email protected]>
Date: Wed, 3 Jul 2024 04:53:26 +0000
Subject: [PATCH v3 1/3] emit dummy message while setting up the publisher
---
src/bin/pg_basebackup/pg_createsubscriber.c | 26 +++++++++++++++++++++
1 file changed, 26 insertions(+)
diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c
index 21dd50f808..499f26cf38 100644
--- a/src/bin/pg_basebackup/pg_createsubscriber.c
+++ b/src/bin/pg_basebackup/pg_createsubscriber.c
@@ -781,6 +781,32 @@ setup_publisher(struct LogicalRepInfo *dbinfo)
disconnect_database(conn, false);
}
+ /*
+ * pg_create_logical_replication_slot () returns the end of the
+ * RUNNING_XACT record. If we use the returned value as recovery_target_lsn
+ * as-is, however, we must wait for additional WAL generation because the
+ * parameter requires that the replicated WAL overtake a certain point.
+ * Insert a dummy WAL record to avoid unnecessary waits.
+ */
+ if (!dry_run)
+ {
+ PGconn *conn;
+ PGresult *res = NULL;
+
+ conn = connect_database(dbinfo[0].pubconninfo, true);
+
+ /* Insert dummy data to WAL to move forward the WAL record */
+ res = PQexec(conn,
+ "SELECT pg_catalog.pg_logical_emit_message(true, 'pg_createsubscriber', 'dummy data', true);");
+
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ {
+ pg_log_error("could not run CHECKPOINT: %s",
+ PQresultErrorMessage(res));
+ disconnect_database(conn, true);
+ }
+ }
+
return lsn;
}
--
2.43.0
[application/octet-stream] v3-0002-wait-until-RUNNING_XACT-is-replicated.patch (1.1K, ../OSBPR01MB2552440DCE1AE5EDC51BAEC6F5DD2@OSBPR01MB2552.jpnprd01.prod.outlook.com/3-v3-0002-wait-until-RUNNING_XACT-is-replicated.patch)
download | inline diff:
From b43f599e802d3a92ef520d1ac6fa3df4e8c3746c Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <[email protected]>
Date: Wed, 3 Jul 2024 05:03:11 +0000
Subject: [PATCH v3 2/3] wait until RUNNING_XACT is replicated
---
src/bin/pg_basebackup/t/040_pg_createsubscriber.pl | 3 +++
1 file changed, 3 insertions(+)
diff --git a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
index 80002c5a17..f21a253211 100644
--- a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
+++ b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
@@ -293,6 +293,9 @@ $node_p->safe_psql($db1,
"SELECT pg_create_logical_replication_slot('$fslotname', 'pgoutput', false, false, true)"
);
$node_s->start;
+# Wait for the standby to catch up so that the standby is not lagging behind
+# the failover slot.
+$node_p->wait_for_replay_catchup($node_s);
$node_s->safe_psql('postgres', "SELECT pg_sync_replication_slots()");
my $result = $node_s->safe_psql('postgres',
"SELECT slot_name FROM pg_replication_slots WHERE slot_name = '$fslotname' AND synced AND NOT temporary"
--
2.43.0
[application/octet-stream] v3-0003-disable-autovacuum-while-testing.patch (1.1K, ../OSBPR01MB2552440DCE1AE5EDC51BAEC6F5DD2@OSBPR01MB2552.jpnprd01.prod.outlook.com/4-v3-0003-disable-autovacuum-while-testing.patch)
download | inline diff:
From 5a15ad746f22fd49570e3d14f3cfe0c8acfafed6 Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <[email protected]>
Date: Wed, 3 Jul 2024 05:07:18 +0000
Subject: [PATCH v3 3/3] disable autovacuum while testing
---
src/bin/pg_basebackup/t/040_pg_createsubscriber.pl | 4 ++++
1 file changed, 4 insertions(+)
diff --git a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
index f21a253211..74b90d9a91 100644
--- a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
+++ b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
@@ -116,6 +116,10 @@ command_fails(
my $node_p = PostgreSQL::Test::Cluster->new('node_p');
my $pconnstr = $node_p->connstr;
$node_p->init(allows_streaming => 'logical');
+# Disable autovacuum to avoid generating xid during stats update as otherwise
+# the new XID could then be replicated to standby at some random point making
+# slots at primary lag behind standby during slot sync.
+$node_p->append_conf('postgresql.conf', 'autovacuum = off');
$node_p->start;
# Set up node F as about-to-fail node
--
2.43.0
view thread (107+ messages) latest in thread
reply
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 using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: RE: speed up a logical replica setup
In-Reply-To: <OSBPR01MB2552440DCE1AE5EDC51BAEC6F5DD2@OSBPR01MB2552.jpnprd01.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox