Subject: Re: speed up a logical replica setup

From: vignesh C <[email-masked]>
Date: Mon, 1 Jan 2024 15:44:21 +0530

On Wed, 6 Dec 2023 at 12:53, Euler Taveira <[email-masked]> wrote:
>
> On Thu, Nov 9, 2023, at 8:12 PM, Michael Paquier wrote:
>
> On Thu, Nov 09, 2023 at 03:41:53PM +0100, Peter Eisentraut wrote:
> > On 08.11.23 00:12, Michael Paquier wrote:
> >> - Should the subdirectory pg_basebackup be renamed into something more
> >> generic at this point?  All these things are frontend tools that deal
> >> in some way with the replication protocol to do their work.  Say
> >> a replication_tools?
> >
> > Seems like unnecessary churn.  Nobody has complained about any of the other
> > tools in there.
>
> Not sure.  We rename things across releases in the tree from time to
> time, and here that's straight-forward.
>
>
> Based on this discussion it seems we have a consensus that this tool should be
> in the pg_basebackup directory. (If/when we agree with the directory renaming,
> it could be done in a separate patch.) Besides this move, the v3 provides a dry
> run mode. It basically executes every routine but skip when should do
> modifications. It is an useful option to check if you will be able to run it
> without having issues with connectivity, permission, and existing objects
> (replication slots, publications, subscriptions). Tests were slightly improved.
> Messages were changed to *not* provide INFO messages by default and --verbose
> provides INFO messages and --verbose --verbose also provides DEBUG messages. I
> also refactored the connect_database() function into which the connection will
> always use the logical replication mode. A bug was fixed in the transient
> replication slot name. Ashutosh review [1] was included. The code was also indented.
>
> There are a few suggestions from Ashutosh [2] that I will reply in another
> email.
>
> I'm still planning to work on the following points:
>
> 1. improve the cleanup routine to point out leftover objects if there is any
>    connection issue.
> 2. remove the physical replication slot if the standby is using one
>    (primary_slot_name).
> 3. provide instructions to promote the logical replica into primary, I mean,
>    stop the replication between the nodes and remove the replication setup
>    (publications, subscriptions, replication slots). Or even include another
>    action to do it. We could add both too.
>
> Point 1 should be done. Points 2 and 3 aren't essential but will provide a nice
> UI for users that would like to use it.

1) This Assert can fail if source is shutdown:
+static void
+drop_replication_slot(PGconn *conn, LogicalRepInfo *dbinfo, const
char *slot_name)
+{
+       PQExpBuffer str = createPQExpBuffer();
+       PGresult   *res;
+
+       Assert(conn != NULL);

I could simulate it by shutting the primary while trying to reach the
consistent state:
pg_subscriber: postmaster reached the consistent state
pg_subscriber: error: connection to database failed: connection to
server at "localhost" (127.0.0.1), port 5432 failed: Connection
refused
Is the server running on that host and accepting TCP/IP connections?
pg_subscriber: error: connection to database failed: connection to
server at "localhost" (127.0.0.1), port 5432 failed: Connection
refused
Is the server running on that host and accepting TCP/IP connections?
pg_subscriber: error: connection to database failed: connection to
server at "localhost" (127.0.0.1), port 5432 failed: Connection
refused
Is the server running on that host and accepting TCP/IP connections?
pg_subscriber: pg_subscriber.c:692: drop_replication_slot: Assertion
`conn != ((void *)0)' failed.
Aborted

2) Should we have some checks to see if the max replication slot
configuration is ok based on the number of slots that will be created,
we have similar checks in upgrade replication slots in
check_new_cluster_logical_replication_slots

3) Should we check if wal_level is set to logical, we have similar
checks in upgrade replication slots in
check_new_cluster_logical_replication_slots

4) The physical replication slot that was created will still be
present in the primary node, I felt this should be removed.

5) I felt the target server should be started before completion of
pg_subscriber:
+       /*
+        * Stop the subscriber.
+        */
+       pg_log_info("stopping the subscriber");
+
+       pg_ctl_cmd = psprintf("\"%s\" stop -D \"%s\" -s", pg_ctl_path,
subscriber_dir);
+       rc = system(pg_ctl_cmd);
+       pg_ctl_status(pg_ctl_cmd, rc, 0);
+
+       /*
+        * Change system identifier.
+        */
+       modify_sysid(pg_resetwal_path, subscriber_dir);
+
+       success = true;
+
+       pg_log_info("Done!");
+
+       return 0;

Regards,
Vignesh

---

From: "Euler Taveira" <[email-masked]>
Date: Thu, 04 Jan 2024 00:21:49 -0300

> +       PQExpBuffer str = createPQExpBuffer();

Oops. I'll remove it.

> check_new_cluster_logical_replication_slots

That's a good idea.

> check_new_cluster_logical_replication_slots

That's a good idea.

> present in the primary node, I felt this should be removed.

My proposal is to remove it [1]. It'll be include in the next version.

> 5) I felt the target server should be started before completion of

Why? The initial version had an option to stop the subscriber. I decided to
remove the option and stop the subscriber by default mainly because (1) it is
an extra step to start the server (another point is that the WAL retention
doesn't happen due to additional (synchronized?) replication slots on
subscriber -- point 2). It was a conservative choice. If point 2 isn't an
issue, imo point 1 is no big deal.

[1] https://www.postgresql.org/message-id/e02a2c17-22e5-4ba6-b788-de696ab74f1e%40app.fastmail.com

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

---

From: Amit Kapila <[email-masked]>
Date: Thu, 4 Jan 2024 11:35:51 +0530

On Thu, Jan 4, 2024 at 8:52 AM Euler Taveira <[email-masked]> wrote:
> Why?

Won't it be a better user experience that after setting up the target
server as a logical replica (subscriber), it started to work
seamlessly without user intervention?

> The initial version had an option to stop the subscriber. I decided to

By point 2, do you mean to have a check for "max replication slots"?
It so, the one possibility is to even increase that config, if the
required max_replication_slots is low.

-- 
With Regards,
Amit Kapila.

---

From: Shlok Kyal <[email-masked]>
Date: Thu, 4 Jan 2024 12:21:54 +0530

Hi,
I was testing the patch with following test cases:

Test 1 :
- Create a 'primary' node
- Setup physical replica using pg_basebackup  "./pg_basebackup –h
localhost –X stream –v –R –W –D ../standby "
- Insert data before and after pg_basebackup
- Run pg_subscriber and then insert some data to check logical
replication "./pg_subscriber –D ../standby -S “host=localhost
port=9000 dbname=postgres” -P “host=localhost port=9000
dbname=postgres” -d postgres"
- Also check pg_publication, pg_subscriber and pg_replication_slots tables.

Observation:
Data is not lost. Replication is happening correctly. Pg_subscriber is
working as expected.

Test 2:
- Create a 'primary' node
- Use normal pg_basebackup but don’t set up Physical replication
"./pg_basebackup –h localhost –v –W –D ../standby"
- Insert data before and after pg_basebackup
- Run pg_subscriber

Observation:
Pg_subscriber command is not completing and is stuck with following
log repeating:
LOG: waiting for WAL to become available at 0/3000168
LOG: invalid record length at 0/3000150: expected at least 24, got 0

Test 3:
- Create a 'primary' node
- Use normal pg_basebackup but don’t set up Physical replication
"./pg_basebackup –h localhost –v –W –D ../standby"
-Insert data before pg_basebackup but not after pg_basebackup
-Run pg_subscriber

Observation:
Pg_subscriber command is not completing and is stuck with following
log repeating:
LOG: waiting for WAL to become available at 0/3000168
LOG: invalid record length at 0/3000150: expected at least 24, got 0

I was not clear about how to use pg_basebackup in this case, can you
let me know if any changes need to be made for test2 and test3.

Thanks and regards
Shlok Kyal

---

From: Amit Kapila <[email-masked]>
Date: Thu, 4 Jan 2024 16:46:28 +0530

> LOG: invalid record length at 0/3000150: expected at least 24, got 0

I think probably the required WAL is not copied. Can you use the -X
option to stream WAL as well and then test? But I feel in this case
also, we should wait for some threshold time and then exit with
failure, removing new objects created, if any.

> LOG: invalid record length at 0/3000150: expected at least 24, got 0

This is similar to the previous test and you can try the same option
here as well.

-- 
With Regards,
Amit Kapila.

---

From: "Euler Taveira" <[email-masked]>
Date: Thu, 04 Jan 2024 12:57:15 -0300

> seamlessly without user intervention?

If we have an option to control the replication slot removal (default is on),
it seems a good UI. Even if the user decides to disable the replication slot
removal, it should print a message saying that these replication slots can
cause WAL retention.

> required max_replication_slots is low.

By point 2, I mean WAL retention (sentence inside parenthesis).

---

From: Amit Kapila <[email-masked]>
Date: Fri, 5 Jan 2024 10:34:16 +0530

> removal, it should print a message saying that these replication slots can

As pointed out in the previous response, I think we should not proceed
with such a risk of WAL retention and other nodes dependency, we
should either give an ERROR (default) or remove slots, if the user
provides an option. If we do so, do you think by default we can keep
the server started or let the user start it later? I think one
advantage of letting the user start it later is that she gets a chance
to adjust config parameters in postgresql.conf and by default we won't
be using system resources.

-- 
With Regards,
Amit Kapila.

---

From: Shlok Kyal <[email-masked]>
Date: Fri, 5 Jan 2024 12:19:14 +0530

> failure, removing new objects created, if any.

I have tested with -X stream option in pg_basebackup as well. In this
case also the pg_subscriber command is getting stuck.
logs:
2024-01-05 11:49:34.436 IST [61948] LOG:  invalid resource manager ID
102 at 0/3000118
2024-01-05 11:49:34.436 IST [61948] LOG:  waiting for WAL to become
available at 0/3000130

> This is similar to the previous test and you can try the same option
For this test as well tried with -X stream option  in pg_basebackup.
It is getting stuck here as well with similar log.

Will investigate the issue further.

Thanks and regards
Shlok Kyal

---

From: Shlok Kyal <[email-masked]>
Date: Wed, 10 Jan 2024 10:03:51 +0530

> Will investigate the issue further.

I noticed that the pg_subscriber get stuck when we run it on node
which is not a standby. It is because the of the code:
+   conn = connect_database(dbinfo[0].pubconninfo);
+   if (conn == NULL)
+       exit(1);
+   consistent_lsn = create_logical_replication_slot(conn, &dbinfo[0],
+                                                    temp_replslot);
+
.....
+else
+   {
+       appendPQExpBuffer(recoveryconfcontents, "recovery_target_lsn = '%s'\n",
+                         consistent_lsn);
+       WriteRecoveryConfig(conn, subscriber_dir, recoveryconfcontents);
+   }

Here the standby node would be waiting for the 'consistent_lsn' wal
during recovery but this wal will not be present on standby if no
physical replication is setup. Hence the command will be waiting
infinitely for the wal.
To solve this added a timeout of 60s for the recovery process and also
added a check so that pg_subscriber would give a error when it called
for node which is not in physical replication.
Have attached the patch for the same. It is a top-up patch of the
patch shared by Euler at [1].

Please review the changes and merge the changes if it looks ok.

[1] - https://www.postgresql.org/message-id/e02a2c17-22e5-4ba6-b788-de696ab74f1e%40app.fastmail.com

Thanks and regards
Shlok Kyal

---

From: "Euler Taveira" <[email-masked]>
Date: Wed, 10 Jan 2024 23:29:14 -0300

> physical replication is setup. Hence the command will be waiting

Hmm. Some validations are missing.

> patch shared by Euler at [1].

If the user has a node that is not a standby and it does not set the GUCs to
start the recovery process from a backup, the initial setup is broken. (That's
the case you described.) A good UI is to detect this scenario earlier.
Unfortunately, there isn't a reliable and cheap way to do it. You need to start
the recovery and check if it is having some progress. (I don't have a strong
opinion about requiring a standby to use this tool. It would reduce the
complexity about checking if the setup has all requirements to run this tool.)

---

From: Amit Kapila <[email-masked]>
Date: Thu, 11 Jan 2024 12:11:03 +0530

> complexity about checking if the setup has all requirements to run this tool.)

Right, such a check will reduce some complexity. So, +1 for the check
as proposed by Shlok. Also, what are your thoughts on a timeout during
the wait? I think it is okay to wait for 60s by default but there
should be an option for users to wait for longer.

-- 
With Regards,
Amit Kapila.

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Thu, 11 Jan 2024 12:18:01 +0000

Dear hackers,

I have been concerned that the patch has not been tested by cfbot due to the
application error. Also, some comments were raised. Therefore, I created a patch
to move forward.
I also tried to address some comments which is not so claimed by others.
They were included in 0003 patch.

* 0001 patch
It is almost the same as v3-0001, which was posted by Euler.
An unnecessary change for Mkvcbuild.pm (this file was removed) was ignored.

* 0002 patch
This contains small fixes to keep complier quiet.

* 0003 patch
This addresses comments posted to -hackers. For now, this does not contain a doc.
Will add if everyone agrees these idea.

1.
An option --port was added to control the port number for physical standby.
Users can specify a port number via the option, or an environment variable PGSUBPORT.
If not specified, a fixed value (50111) would be used.

SOURCE: [1]

2.
A FATAL error would be raised if --subscriber-conninfo specifies non-local server.

SOURCE: [2]

3. 
Options -o/-O were added to specify options for publications/subscriptions.

SOURCE: [2]

4. 
Made standby to save their output to log file.

SOURCE: [2]

5. 
Unnecessary Assert in drop_replication_slot() was removed.

SOURCE: [3]

How do you think?
Thanks Shlok and Vignesh to work with me offline.

[1]: https://www.postgresql.org/message-id/TY3PR01MB988978C7362A101927070D29F56A2%40TY3PR01MB9889.jpnprd01.prod.outlook.com
[2]: https://www.postgresql.org/message-id/TY3PR01MB9889593399165B9A04106741F5662%40TY3PR01MB9889.jpnprd01.prod.outlook.com
[3]: https://www.postgresql.org/message-id/CALDaNm098Jkbh%2Bye6zMj9Ro9j1bBe6FfPV80BFbs1%3DpUuTJ07g%40mail.gmail.com

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

---

From: "Euler Taveira" <[email-masked]>
Date: Thu, 11 Jan 2024 19:15:22 -0300

> application error. Also, some comments were raised. Therefore, I created a patch

Let me send an updated patch to hopefully keep the CF bot happy. The following
items are included in this patch:

* drop physical replication slot if standby is using one [1].
* cleanup small changes (copyright, .gitignore) [2][3]
* fix getopt_long() options [2]
* fix format specifier for some messages
* move doc to Server Application section [4]
* fix assert failure
* ignore duplicate database names [2]
* store subscriber server log into a separate file
* remove MSVC support

I'm still addressing other reviews and I'll post another version that includes
it soon.

[1] https://www.postgresql.org/message-id/e02a2c17-22e5-4ba6-b788-de696ab74f1e%40app.fastmail.com
[2] https://www.postgresql.org/message-id/CALDaNm1joke42n68LdegN5wCpaeoOMex2EHcdZrVZnGD3UhfNQ%40mail.gmail.com
[3] https://www.postgresql.org/message-id/TY3PR01MB98895BA6C1D72CB8582CACC4F5682%40TY3PR01MB9889.jpnprd01.prod.outlook.com
[4] https://www.postgresql.org/message-id/TY3PR01MB988978C7362A101927070D29F56A2%40TY3PR01MB9889.jpnprd01.prod.outlook.com

--
Euler Taveira
EDB   https://www.enterprisedb.com/
From 2341ef3dc26d48b51b13ad01ac38c79d24b1e461 Mon Sep 17 00:00:00 2001
From: Euler Taveira <[email-masked]>
Date: Mon, 5 Jun 2023 14:39:40 -0400
Subject: [PATCH v5] Creates a new logical replica from a standby server

A new tool called pg_subscriber can convert a physical replica into a
logical replica. It runs on the target server and should be able to
connect to the source server (publisher) and the target server
(subscriber).

The conversion requires a few steps. Check if the target data directory
has the same system identifier than the source data directory. Stop the
target server if it is running as a standby server. Create one
replication slot per specified database on the source server. One
additional replication slot is created at the end to get the consistent
LSN (This consistent LSN will be used as (a) a stopping point for the
recovery process and (b) a starting point for the subscriptions). Write
recovery parameters into the target data directory and start the target
server (Wait until the target server is promoted). Create one
publication (FOR ALL TABLES) per specified database on the source
server. Create one subscription per specified database on the target
server (Use replication slot and publication created in a previous step.
Don't enable the subscriptions yet). Sets the replication progress to
the consistent LSN that was got in a previous step. Enable the
subscription for each specified database on the target server. Remove
the additional replication slot that was used to get the consistent LSN.
Stop the target server. Change the system identifier from the target
server.

Depending on your workload and database size, creating a logical replica
couldn't be an option due to resource constraints (WAL backlog should be
available until all table data is synchronized). The initial data copy
and the replication progress tends to be faster on a physical replica.
The purpose of this tool is to speed up a logical replica setup.
---
 doc/src/sgml/ref/allfiles.sgml                |    1 +
 doc/src/sgml/ref/pg_subscriber.sgml           |  284 +++
 doc/src/sgml/reference.sgml                   |    1 +
 src/bin/pg_basebackup/.gitignore              |    1 +
 src/bin/pg_basebackup/Makefile                |    8 +-
 src/bin/pg_basebackup/meson.build             |   19 +
 src/bin/pg_basebackup/pg_subscriber.c         | 1657 +++++++++++++++++
 src/bin/pg_basebackup/t/040_pg_subscriber.pl  |   44 +
 .../t/041_pg_subscriber_standby.pl            |  139 ++
 9 files changed, 2153 insertions(+), 1 deletion(-)
 create mode 100644 doc/src/sgml/ref/pg_subscriber.sgml
 create mode 100644 src/bin/pg_basebackup/pg_subscriber.c
 create mode 100644 src/bin/pg_basebackup/t/040_pg_subscriber.pl
 create mode 100644 src/bin/pg_basebackup/t/041_pg_subscriber_standby.pl

diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index 4a42999b18..3862c976d7 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -214,6 +214,7 @@ Complete list of usable sgml source files in this directory.
 <!ENTITY pgResetwal         SYSTEM "pg_resetwal.sgml">
 <!ENTITY pgRestore          SYSTEM "pg_restore.sgml">
 <!ENTITY pgRewind           SYSTEM "pg_rewind.sgml">
+<!ENTITY pgSubscriber       SYSTEM "pg_subscriber.sgml">
 <!ENTITY pgVerifyBackup     SYSTEM "pg_verifybackup.sgml">
 <!ENTITY pgtestfsync        SYSTEM "pgtestfsync.sgml">
 <!ENTITY pgtesttiming       SYSTEM "pgtesttiming.sgml">
diff --git a/doc/src/sgml/ref/pg_subscriber.sgml b/doc/src/sgml/ref/pg_subscriber.sgml
new file mode 100644
index 0000000000..553185c35f
--- /dev/null
+++ b/doc/src/sgml/ref/pg_subscriber.sgml
@@ -0,0 +1,284 @@
+<!--
+doc/src/sgml/ref/pg_subscriber.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="app-pgsubscriber">
+ <indexterm zone="app-pgsubscriber">
+  <primary>pg_subscriber</primary>
+ </indexterm>
+
+ <refmeta>
+  <refentrytitle><application>pg_subscriber</application></refentrytitle>
+  <manvolnum>1</manvolnum>
+  <refmiscinfo>Application</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+  <refname>pg_subscriber</refname>
+  <refpurpose>create a new logical replica from a standby server</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+  <cmdsynopsis>
+   <command>pg_subscriber</command>
+   <arg rep="repeat"><replaceable>option</replaceable></arg>
+  </cmdsynopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+  <title>Description</title>
+  <para>
+   <application>pg_subscriber</application> takes the publisher and subscriber
+   connection strings, a cluster directory from a standby server and a list of
+   database names and it sets up a new logical replica using the physical
+   recovery process.
+  </para>
+
+  <para>
+   The <application>pg_subscriber</application> should be run at the target
+   server. The source server (known as publisher server) should accept logical
+   replication connections from the target server (known as subscriber server).
+   The target server should accept local logical replication connection.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Options</title>
+
+   <para>
+    <application>pg_subscriber</application> accepts the following
+    command-line arguments:
+
+    <variablelist>
+     <varlistentry>
+      <term><option>-D <replaceable class="parameter">directory</replaceable></option></term>
+      <term><option>--pgdata=<replaceable class="parameter">directory</replaceable></option></term>
+      <listitem>
+       <para>
+        The target directory that contains a cluster directory from a standby
+        server.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>-P  <replaceable class="parameter">conninfo</replaceable></option></term>
+      <term><option>--publisher-conninfo=<replaceable class="parameter">conninfo</replaceable></option></term>
+      <listitem>
+       <para>
+        The connection string to the publisher. For details see <xref linkend="libpq-connstring"/>.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>-S <replaceable class="parameter">conninfo</replaceable></option></term>
+      <term><option>--subscriber-conninfo=<replaceable class="parameter">conninfo</replaceable></option></term>
+      <listitem>
+       <para>
+        The connection string to the subscriber. For details see <xref linkend="libpq-connstring"/>.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>-d <replaceable class="parameter">dbname</replaceable></option></term>
+      <term><option>--database=<replaceable class="parameter">dbname</replaceable></option></term>
+      <listitem>
+       <para>
+        The database name to create the subscription. Multiple databases can be
+        selected by writing multiple <option>-d</option> switches.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>-n</option></term>
+      <term><option>--dry-run</option></term>
+      <listitem>
+       <para>
+        Do everything except actually modifying the target directory.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>-v</option></term>
+      <term><option>--verbose</option></term>
+      <listitem>
+       <para>
+        Enables verbose mode. This will cause
+        <application>pg_subscriber</application> to output progress messages
+        and detailed information about each step.
+       </para>
+      </listitem>
+     </varlistentry>
+    </variablelist>
+   </para>
+
+   <para>
+    Other options are also available:
+
+    <variablelist>
+     <varlistentry>
+       <term><option>-V</option></term>
+       <term><option>--version</option></term>
+       <listitem>
+       <para>
+       Print the <application>pg_subscriber</application> version and exit.
+       </para>
+       </listitem>
+     </varlistentry>
+
+     <varlistentry>
+       <term><option>-?</option></term>
+       <term><option>--help</option></term>
+       <listitem>
+       <para>
+       Show help about <application>pg_subscriber</application> command
+       line arguments, and exit.
+       </para>
+       </listitem>
+     </varlistentry>
+
+    </variablelist>
+   </para>
+
+ </refsect1>
+
+ <refsect1>
+  <title>Notes</title>
+
+  <para>
+   The transformation proceeds in the following steps:
+  </para>
+
+  <procedure>
+   <step>
+    <para>
+     <application>pg_subscriber</application> checks if the given target data
+     directory has the same system identifier than the source data directory.
+     Since it uses the recovery process as one of the steps, it starts the
+     target server as a replica from the source server. If the system
+     identifier is not the same, <application>pg_subscriber</application> will
+     terminate with an error.
+    </para>
+   </step>
+
+   <step>
+    <para>
+     <application>pg_subscriber</application> checks if the target data
+     directory is used by a standby server. Stop the standby server if it is
+     running. One of the next steps is to add some recovery parameters that
+     requires a server start. This step avoids an error.
+    </para>
+   </step>
+
+   <step>
+    <para>
+     <application>pg_subscriber</application> creates one replication slot for
+     each specified database on the source server. The replication slot name
+     contains a <literal>pg_subscriber</literal> prefix. These replication
+     slots will be used by the subscriptions in a future step.  Another
+     replication slot is used to get a consistent start location. This
+     consistent LSN will be used as a stopping point in the <xref
+     linkend="guc-recovery-target-lsn"/> parameter and by the
+     subscriptions as a replication starting point. It guarantees that no
+     transaction will be lost.
+    </para>
+   </step>
+
+   <step>
+    <para>
+     <application>pg_subscriber</application> writes recovery parameters into
+     the target data directory and start the target server. It specifies a LSN
+     (consistent LSN that was obtained in the previous step) of write-ahead
+     log location up to which recovery will proceed. It also specifies
+     <literal>promote</literal> as the action that the server should take once
+     the recovery target is reached. This step finishes once the server ends
+     standby mode and is accepting read-write operations.
+    </para>
+   </step>
+
+   <step>
+    <para>
+     Next, <application>pg_subscriber</application> creates one publication
+     for each specified database on the source server. Each publication
+     replicates changes for all tables in the database. The publication name
+     contains a <literal>pg_subscriber</literal> prefix. These publication
+     will be used by a corresponding subscription in a next step.
+    </para>
+   </step>
+
+   <step>
+    <para>
+     <application>pg_subscriber</application> creates one subscription for
+     each specified database on the target server. Each subscription name
+     contains a <literal>pg_subscriber</literal> prefix. The replication slot
+     name is identical to the subscription name. It does not copy existing data
+     from the source server. It does not create a replication slot. Instead, it
+     uses the replication slot that was created in a previous step. The
+     subscription is created but it is not enabled yet. The reason is the
+     replication progress must be set to the consistent LSN but replication
+     origin name contains the subscription oid in its name. Hence, the
+     subscription will be enabled in a separate step.
+    </para>
+   </step>
+
+   <step>
+    <para>
+     <application>pg_subscriber</application> sets the replication progress to
+     the consistent LSN that was obtained in a previous step. When the target
+     server started the recovery process, it caught up to the consistent LSN.
+     This is the exact LSN to be used as a initial location for each
+     subscription.
+    </para>
+   </step>
+
+   <step>
+    <para>
+     Finally, <application>pg_subscriber</application> enables the subscription
+     for each specified database on the target server. The subscription starts
+     streaming from the consistent LSN.
+    </para>
+   </step>
+
+   <step>
+    <para>
+     <application>pg_subscriber</application> removes the additional replication
+     slot that was used to get the consistent LSN on the source server.
+    </para>
+   </step>
+
+   <step>
+    <para>
+     <application>pg_subscriber</application> stops the target server to change
+     its system identifier.
+    </para>
+   </step>
+  </procedure>
+ </refsect1>
+
+ <refsect1>
+  <title>Examples</title>
+
+  <para>
+   To create a logical replica for databases <literal>hr</literal> and
+   <literal>finance</literal> from a standby server at <literal>foo</literal>:
+<screen>
+<prompt>$</prompt> <userinput>pg_subscriber -D /usr/local/pgsql/data -P "host=foo" -S "host=localhost" -d hr -d finance</userinput>
+</screen>
+  </para>
+
+ </refsect1>
+
+ <refsect1>
+  <title>See Also</title>
+
+  <simplelist type="inline">
+   <member><xref linkend="app-pgbasebackup"/></member>
+  </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index aa94f6adf6..266f4e515a 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -285,6 +285,7 @@
    &pgCtl;
    &pgResetwal;
    &pgRewind;
+   &pgSubscriber;
    &pgtestfsync;
    &pgtesttiming;
    &pgupgrade;
diff --git a/src/bin/pg_basebackup/.gitignore b/src/bin/pg_basebackup/.gitignore
index 26048bdbd8..0e5384a1d5 100644
--- a/src/bin/pg_basebackup/.gitignore
+++ b/src/bin/pg_basebackup/.gitignore
@@ -1,5 +1,6 @@
 /pg_basebackup
 /pg_receivewal
 /pg_recvlogical
+/pg_subscriber
 
 /tmp_check/
diff --git a/src/bin/pg_basebackup/Makefile b/src/bin/pg_basebackup/Makefile
index abfb6440ec..f6281b7676 100644
--- a/src/bin/pg_basebackup/Makefile
+++ b/src/bin/pg_basebackup/Makefile
@@ -44,7 +44,7 @@ BBOBJS = \
 	bbstreamer_tar.o \
 	bbstreamer_zstd.o
 
-all: pg_basebackup pg_receivewal pg_recvlogical
+all: pg_basebackup pg_receivewal pg_recvlogical pg_subscriber
 
 pg_basebackup: $(BBOBJS) $(OBJS) | submake-libpq submake-libpgport submake-libpgfeutils
 	$(CC) $(CFLAGS) $(BBOBJS) $(OBJS) $(LDFLAGS) $(LDFLAGS_EX) $(LIBS) -o $@$(X)
@@ -55,10 +55,14 @@ pg_receivewal: pg_receivewal.o $(OBJS) | submake-libpq submake-libpgport submake
 pg_recvlogical: pg_recvlogical.o $(OBJS) | submake-libpq submake-libpgport submake-libpgfeutils
 	$(CC) $(CFLAGS) pg_recvlogical.o $(OBJS) $(LDFLAGS) $(LDFLAGS_EX) $(LIBS) -o $@$(X)
 
+pg_subscriber: $(WIN32RES) pg_subscriber.o | submake-libpq submake-libpgport submake-libpgfeutils
+	$(CC) $(CFLAGS) $^ $(LDFLAGS) $(LDFLAGS_EX) $(LIBS) -o $@$(X)
+
 install: all installdirs
 	$(INSTALL_PROGRAM) pg_basebackup$(X) '$(DESTDIR)$(bindir)/pg_basebackup$(X)'
 	$(INSTALL_PROGRAM) pg_receivewal$(X) '$(DESTDIR)$(bindir)/pg_receivewal$(X)'
 	$(INSTALL_PROGRAM) pg_recvlogical$(X) '$(DESTDIR)$(bindir)/pg_recvlogical$(X)'
+	$(INSTALL_PROGRAM) pg_subscriber$(X) '$(DESTDIR)$(bindir)/pg_subscriber$(X)'
 
 installdirs:
 	$(MKDIR_P) '$(DESTDIR)$(bindir)'
@@ -67,10 +71,12 @@ uninstall:
 	rm -f '$(DESTDIR)$(bindir)/pg_basebackup$(X)'
 	rm -f '$(DESTDIR)$(bindir)/pg_receivewal$(X)'
 	rm -f '$(DESTDIR)$(bindir)/pg_recvlogical$(X)'
+	rm -f '$(DESTDIR)$(bindir)/pg_subscriber$(X)'
 
 clean distclean:
 	rm -f pg_basebackup$(X) pg_receivewal$(X) pg_recvlogical$(X) \
 		$(BBOBJS) pg_receivewal.o pg_recvlogical.o \
+		pg_subscriber$(X) pg_subscriber.o \
 		$(OBJS)
 	rm -rf tmp_check
 
diff --git a/src/bin/pg_basebackup/meson.build b/src/bin/pg_basebackup/meson.build
index f7e60e6670..ccfd7bb7a5 100644
--- a/src/bin/pg_basebackup/meson.build
+++ b/src/bin/pg_basebackup/meson.build
@@ -75,6 +75,23 @@ pg_recvlogical = executable('pg_recvlogical',
 )
 bin_targets += pg_recvlogical
 
+pg_subscriber_sources = files(
+  'pg_subscriber.c'
+)
+
+if host_system == 'windows'
+  pg_subscriber_sources += rc_bin_gen.process(win32ver_rc, extra_args: [
+	'--NAME', 'pg_subscriber',
+	'--FILEDESC', 'pg_subscriber - create a new logical replica from a standby server',])
+endif
+
+pg_subscriber = executable('pg_subscriber',
+  pg_subscriber_sources,
+  dependencies: [frontend_code, libpq],
+  kwargs: default_bin_args,
+)
+bin_targets += pg_subscriber
+
 tests += {
   'name': 'pg_basebackup',
   'sd': meson.current_source_dir(),
@@ -89,6 +106,8 @@ tests += {
       't/011_in_place_tablespace.pl',
       't/020_pg_receivewal.pl',
       't/030_pg_recvlogical.pl',
+      't/040_pg_subscriber.pl',
+      't/041_pg_subscriber_standby.pl',
     ],
   },
 }
diff --git a/src/bin/pg_basebackup/pg_subscriber.c b/src/bin/pg_basebackup/pg_subscriber.c
new file mode 100644
index 0000000000..e998c29f9e
--- /dev/null
+++ b/src/bin/pg_basebackup/pg_subscriber.c
@@ -0,0 +1,1657 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_subscriber.c
+ *	  Create a new logical replica from a standby server
+ *
+ * Copyright (C) 2024, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ *		src/bin/pg_subscriber/pg_subscriber.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres_fe.h"
+
+#include <signal.h>
+#include <sys/stat.h>
+#include <sys/time.h>
+#include <sys/wait.h>
+#include <time.h>
+
+#include "access/xlogdefs.h"
+#include "catalog/pg_control.h"
+#include "common/connect.h"
+#include "common/controldata_utils.h"
+#include "common/file_perm.h"
+#include "common/file_utils.h"
+#include "common/logging.h"
+#include "fe_utils/recovery_gen.h"
+#include "fe_utils/simple_list.h"
+#include "getopt_long.h"
+#include "utils/pidfile.h"
+
+#define	PGS_OUTPUT_DIR	"pg_subscriber_output.d"
+
+typedef struct LogicalRepInfo
+{
+	Oid			oid;			/* database OID */
+	char	   *dbname;			/* database name */
+	char	   *pubconninfo;	/* publication connection string for logical
+								 * replication */
+	char	   *subconninfo;	/* subscription connection string for logical
+								 * replication */
+	char	   *pubname;		/* publication name */
+	char	   *subname;		/* subscription name (also replication slot
+								 * name) */
+
+	bool		made_replslot;	/* replication slot was created */
+	bool		made_publication;	/* publication was created */
+	bool		made_subscription;	/* subscription was created */
+} LogicalRepInfo;
+
+static void cleanup_objects_atexit(void);
+static void usage();
+static char *get_base_conninfo(char *conninfo, char *dbname,
+							   const char *noderole);
+static bool get_exec_path(const char *path);
+static bool check_data_directory(const char *datadir);
+static char *concat_conninfo_dbname(const char *conninfo, const char *dbname);
+static LogicalRepInfo *store_pub_sub_info(const char *pub_base_conninfo, const char *sub_base_conninfo);
+static PGconn *connect_database(const char *conninfo);
+static void disconnect_database(PGconn *conn);
+static uint64 get_sysid_from_conn(const char *conninfo);
+static uint64 get_control_from_datadir(const char *datadir);
+static void modify_sysid(const char *pg_resetwal_path, const char *datadir);
+static char *use_primary_slot_name(void);
+static bool create_all_logical_replication_slots(LogicalRepInfo *dbinfo);
+static char *create_logical_replication_slot(PGconn *conn, LogicalRepInfo *dbinfo,
+											 char *slot_name);
+static void drop_replication_slot(PGconn *conn, LogicalRepInfo *dbinfo, const char *slot_name);
+static void pg_ctl_status(const char *pg_ctl_cmd, int rc, int action);
+static void wait_for_end_recovery(const char *conninfo);
+static void create_publication(PGconn *conn, LogicalRepInfo *dbinfo);
+static void drop_publication(PGconn *conn, LogicalRepInfo *dbinfo);
+static void create_subscription(PGconn *conn, LogicalRepInfo *dbinfo);
+static void drop_subscription(PGconn *conn, LogicalRepInfo *dbinfo);
+static void set_replication_progress(PGconn *conn, LogicalRepInfo *dbinfo, const char *lsn);
+static void enable_subscription(PGconn *conn, LogicalRepInfo *dbinfo);
+
+#define	USEC_PER_SEC	1000000
+#define	WAIT_INTERVAL	1		/* 1 second */
+
+/* Options */
+static const char *progname;
+
+static char *subscriber_dir = NULL;
+static char *pub_conninfo_str = NULL;
+static char *sub_conninfo_str = NULL;
+static SimpleStringList database_names = {NULL, NULL};
+static char *primary_slot_name = NULL;
+static bool dry_run = false;
+
+static bool success = false;
+
+static char *pg_ctl_path = NULL;
+static char *pg_resetwal_path = NULL;
+
+static LogicalRepInfo *dbinfo;
+static int	num_dbs = 0;
+
+static char temp_replslot[NAMEDATALEN] = {0};
+static bool made_transient_replslot = false;
+
+enum WaitPMResult
+{
+	POSTMASTER_READY,
+	POSTMASTER_STANDBY,
+	POSTMASTER_STILL_STARTING,
+	POSTMASTER_FAILED
+};
+
+
+/*
+ * Cleanup objects that were created by pg_subscriber if there is an error.
+ *
+ * Replication slots, publications and subscriptions are created. Depending on
+ * the step it failed, it should remove the already created objects if it is
+ * possible (sometimes it won't work due to a connection issue).
+ */
+static void
+cleanup_objects_atexit(void)
+{
+	PGconn	   *conn;
+	int			i;
+
+	if (success)
+		return;
+
+	for (i = 0; i < num_dbs; i++)
+	{
+		if (dbinfo[i].made_subscription)
+		{
+			conn = connect_database(dbinfo[i].subconninfo);
+			if (conn != NULL)
+			{
+				drop_subscription(conn, &dbinfo[i]);
+				disconnect_database(conn);
+			}
+		}
+
+		if (dbinfo[i].made_publication || dbinfo[i].made_replslot)
+		{
+			conn = connect_database(dbinfo[i].pubconninfo);
+			if (conn != NULL)
+			{
+				if (dbinfo[i].made_publication)
+					drop_publication(conn, &dbinfo[i]);
+				if (dbinfo[i].made_replslot)
+					drop_replication_slot(conn, &dbinfo[i], NULL);
+				disconnect_database(conn);
+			}
+		}
+	}
+
+	if (made_transient_replslot)
+	{
+		conn = connect_database(dbinfo[0].pubconninfo);
+		if (conn != NULL)
+		{
+			drop_replication_slot(conn, &dbinfo[0], temp_replslot);
+			disconnect_database(conn);
+		}
+	}
+}
+
+static void
+usage(void)
+{
+	printf(_("%s creates a new logical replica from a standby server.\n\n"),
+		   progname);
+	printf(_("Usage:\n"));
+	printf(_("  %s [OPTION]...\n"), progname);
+	printf(_("\nOptions:\n"));
+	printf(_(" -D, --pgdata=DATADIR                location for the subscriber data directory\n"));
+	printf(_(" -P, --publisher-conninfo=CONNINFO   publisher connection string\n"));
+	printf(_(" -S, --subscriber-conninfo=CONNINFO  subscriber connection string\n"));
+	printf(_(" -d, --database=DBNAME               database to create a subscription\n"));
+	printf(_(" -n, --dry-run                       stop before modifying anything\n"));
+	printf(_(" -v, --verbose                       output verbose messages\n"));
+	printf(_(" -V, --version                       output version information, then exit\n"));
+	printf(_(" -?, --help                          show this help, then exit\n"));
+	printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
+	printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
+}
+
+/*
+ * Validate a connection string. Returns a base connection string that is a
+ * connection string without a database name plus a fallback application name.
+ * Since we might process multiple databases, each database name will be
+ * appended to this base connection string to provide a final connection string.
+ * If the second argument (dbname) is not null, returns dbname if the provided
+ * connection string contains it. If option --database is not provided, uses
+ * dbname as the only database to setup the logical replica.
+ * It is the caller's responsibility to free the returned connection string and
+ * dbname.
+ */
+static char *
+get_base_conninfo(char *conninfo, char *dbname, const char *noderole)
+{
+	PQExpBuffer buf = createPQExpBuffer();
+	PQconninfoOption *conn_opts = NULL;
+	PQconninfoOption *conn_opt;
+	char	   *errmsg = NULL;
+	char	   *ret;
+	int			i;
+
+	pg_log_info("validating connection string on %s", noderole);
+
+	conn_opts = PQconninfoParse(conninfo, &errmsg);
+	if (conn_opts == NULL)
+	{
+		pg_log_error("could not parse connection string: %s", errmsg);
+		return NULL;
+	}
+
+	i = 0;
+	for (conn_opt = conn_opts; conn_opt->keyword != NULL; conn_opt++)
+	{
+		if (strcmp(conn_opt->keyword, "dbname") == 0 && conn_opt->val != NULL)
+		{
+			if (dbname)
+				dbname = pg_strdup(conn_opt->val);
+			continue;
+		}
+
+		if (conn_opt->val != NULL && conn_opt->val[0] != '\0')
+		{
+			if (i > 0)
+				appendPQExpBufferChar(buf, ' ');
+			appendPQExpBuffer(buf, "%s=%s", conn_opt->keyword, conn_opt->val);
+			i++;
+		}
+	}
+
+	if (i > 0)
+		appendPQExpBufferChar(buf, ' ');
+	appendPQExpBuffer(buf, "fallback_application_name=%s", progname);
+
+	ret = pg_strdup(buf->data);
+
+	destroyPQExpBuffer(buf);
+	PQconninfoFree(conn_opts);
+
+	return ret;
+}
+
+/*
+ * Get the absolute path from other PostgreSQL binaries (pg_ctl and
+ * pg_resetwal) that is used by it.
+ */
+static bool
+get_exec_path(const char *path)
+{
+	int			rc;
+
+	pg_ctl_path = pg_malloc(MAXPGPATH);
+	rc = find_other_exec(path, "pg_ctl",
+						 "pg_ctl (PostgreSQL) " PG_VERSION "\n",
+						 pg_ctl_path);
+	if (rc < 0)
+	{
+		char		full_path[MAXPGPATH];
+
+		if (find_my_exec(path, full_path) < 0)
+			strlcpy(full_path, progname, sizeof(full_path));
+		if (rc == -1)
+			pg_log_error("The program \"%s\" is needed by %s but was not found in the\n"
+						 "same directory as \"%s\".\n"
+						 "Check your installation.",
+						 "pg_ctl", progname, full_path);
+		else
+			pg_log_error("The program \"%s\" was found by \"%s\"\n"
+						 "but was not the same version as %s.\n"
+						 "Check your installation.",
+						 "pg_ctl", full_path, progname);
+		return false;
+	}
+
+	pg_log_debug("pg_ctl path is: %s", pg_ctl_path);
+
+	pg_resetwal_path = pg_malloc(MAXPGPATH);
+	rc = find_other_exec(path, "pg_resetwal",
+						 "pg_resetwal (PostgreSQL) " PG_VERSION "\n",
+						 pg_resetwal_path);
+	if (rc < 0)
+	{
+		char		full_path[MAXPGPATH];
+
+		if (find_my_exec(path, full_path) < 0)
+			strlcpy(full_path, progname, sizeof(full_path));
+		if (rc == -1)
+			pg_log_error("The program \"%s\" is needed by %s but was not found in the\n"
+						 "same directory as \"%s\".\n"
+						 "Check your installation.",
+						 "pg_resetwal", progname, full_path);
+		else
+			pg_log_error("The program \"%s\" was found by \"%s\"\n"
+						 "but was not the same version as %s.\n"
+						 "Check your installation.",
+						 "pg_resetwal", full_path, progname);
+		return false;
+	}
+
+	pg_log_debug("pg_resetwal path is: %s", pg_resetwal_path);
+
+	return true;
+}
+
+/*
+ * Is it a cluster directory? These are preliminary checks. It is far from
+ * making an accurate check. If it is not a clone from the publisher, it will
+ * eventually fail in a future step.
+ */
+static bool
+check_data_directory(const char *datadir)
+{
+	struct stat statbuf;
+	char		versionfile[MAXPGPATH];
+
+	pg_log_info("checking if directory \"%s\" is a cluster data directory",
+				datadir);
+
+	if (stat(datadir, &statbuf) != 0)
+	{
+		if (errno == ENOENT)
+			pg_log_error("data directory \"%s\" does not exist", datadir);
+		else
+			pg_log_error("could not access directory \"%s\": %s", datadir, strerror(errno));
+
+		return false;
+	}
+
+	snprintf(versionfile, MAXPGPATH, "%s/PG_VERSION", datadir);
+	if (stat(versionfile, &statbuf) != 0 && errno == ENOENT)
+	{
+		pg_log_error("directory \"%s\" is not a database cluster directory", datadir);
+		return false;
+	}
+
+	return true;
+}
+
+/*
+ * Append database name into a base connection string.
+ *
+ * dbname is the only parameter that changes so it is not included in the base
+ * connection string. This function concatenates dbname to build a "real"
+ * connection string.
+ */
+static char *
+concat_conninfo_dbname(const char *conninfo, const char *dbname)
+{
+	PQExpBuffer buf = createPQExpBuffer();
+	char	   *ret;
+
+	Assert(conninfo != NULL);
+
+	appendPQExpBufferStr(buf, conninfo);
+	appendPQExpBuffer(buf, " dbname=%s", dbname);
+
+	ret = pg_strdup(buf->data);
+	destroyPQExpBuffer(buf);
+
+	return ret;
+}
+
+/*
+ * Store publication and subscription information.
+ */
+static LogicalRepInfo *
+store_pub_sub_info(const char *pub_base_conninfo, const char *sub_base_conninfo)
+{
+	LogicalRepInfo *dbinfo;
+	SimpleStringListCell *cell;
+	int			i = 0;
+
+	dbinfo = (LogicalRepInfo *) pg_malloc(num_dbs * sizeof(LogicalRepInfo));
+
+	for (cell = database_names.head; cell; cell = cell->next)
+	{
+		char	   *conninfo;
+
+		/* Publisher. */
+		conninfo = concat_conninfo_dbname(pub_base_conninfo, cell->val);
+		dbinfo[i].pubconninfo = conninfo;
+		dbinfo[i].dbname = cell->val;
+		dbinfo[i].made_replslot = false;
+		dbinfo[i].made_publication = false;
+		dbinfo[i].made_subscription = false;
+		/* other struct fields will be filled later. */
+
+		/* Subscriber. */
+		conninfo = concat_conninfo_dbname(sub_base_conninfo, cell->val);
+		dbinfo[i].subconninfo = conninfo;
+
+		i++;
+	}
+
+	return dbinfo;
+}
+
+static PGconn *
+connect_database(const char *conninfo)
+{
+	PGconn	   *conn;
+	PGresult   *res;
+	const char *rconninfo;
+
+	/* logical replication mode */
+	rconninfo = psprintf("%s replication=database", conninfo);
+
+	conn = PQconnectdb(rconninfo);
+	if (PQstatus(conn) != CONNECTION_OK)
+	{
+		pg_log_error("connection to database failed: %s", PQerrorMessage(conn));
+		return NULL;
+	}
+
+	/* secure search_path */
+	res = PQexec(conn, ALWAYS_SECURE_SEARCH_PATH_SQL);
+	if (PQresultStatus(res) != PGRES_TUPLES_OK)
+	{
+		pg_log_error("could not clear search_path: %s", PQresultErrorMessage(res));
+		return NULL;
+	}
+	PQclear(res);
+
+	return conn;
+}
+
+static void
+disconnect_database(PGconn *conn)
+{
+	Assert(conn != NULL);
+
+	PQfinish(conn);
+}
+
+/*
+ * Obtain the system identifier using the provided connection. It will be used
+ * to compare if a data directory is a clone of another one.
+ */
+static uint64
+get_sysid_from_conn(const char *conninfo)
+{
+	PGconn	   *conn;
+	PGresult   *res;
+	uint64		sysid;
+
+	pg_log_info("getting system identifier from publisher");
+
+	conn = connect_database(conninfo);
+	if (conn == NULL)
+		exit(1);
+
+	res = PQexec(conn, "IDENTIFY_SYSTEM");
+	if (PQresultStatus(res) != PGRES_TUPLES_OK)
+	{
+		pg_log_error("could not send replication command \"%s\": %s",
+					 "IDENTIFY_SYSTEM", PQresultErrorMessage(res));
+		PQclear(res);
+		disconnect_database(conn);
+		exit(1);
+	}
+	if (PQntuples(res) != 1 || PQnfields(res) < 3)
+	{
+		pg_log_error("could not identify system: got %d rows and %d fields, expected %d rows and %d or more fields",
+					 PQntuples(res), PQnfields(res), 1, 3);
+
+		PQclear(res);
+		disconnect_database(conn);
+		exit(1);
+	}
+
+	sysid = strtou64(PQgetvalue(res, 0, 0), NULL, 10);
+
+	pg_log_info("system identifier is %llu on publisher", (unsigned long long) sysid);
+
+	disconnect_database(conn);
+
+	return sysid;
+}
+
+/*
+ * Obtain the system identifier from control file. It will be used to compare
+ * if a data directory is a clone of another one. This routine is used locally
+ * and avoids a replication connection.
+ */
+static uint64
+get_control_from_datadir(const char *datadir)
+{
+	ControlFileData *cf;
+	bool		crc_ok;
+	uint64		sysid;
+
+	pg_log_info("getting system identifier from subscriber");
+
+	cf = get_controlfile(datadir, &crc_ok);
+	if (!crc_ok)
+	{
+		pg_log_error("control file appears to be corrupt");
+		exit(1);
+	}
+
+	sysid = cf->system_identifier;
+
+	pg_log_info("system identifier is %llu on subscriber", (unsigned long long) sysid);
+
+	pfree(cf);
+
+	return sysid;
+}
+
+/*
+ * Modify the system identifier. Since a standby server preserves the system
+ * identifier, it makes sense to change it to avoid situations in which WAL
+ * files from one of the systems might be used in the other one.
+ */
+static void
+modify_sysid(const char *pg_resetwal_path, const char *datadir)
+{
+	ControlFileData *cf;
+	bool		crc_ok;
+	struct timeval tv;
+
+	char	   *cmd_str;
+	int			rc;
+
+	pg_log_info("modifying system identifier from subscriber");
+
+	cf = get_controlfile(datadir, &crc_ok);
+	if (!crc_ok)
+	{
+		pg_log_error("control file appears to be corrupt");
+		exit(1);
+	}
+
+	/*
+	 * Select a new system identifier.
+	 *
+	 * XXX this code was extracted from BootStrapXLOG().
+	 */
+	gettimeofday(&tv, NULL);
+	cf->system_identifier = ((uint64) tv.tv_sec) << 32;
+	cf->system_identifier |= ((uint64) tv.tv_usec) << 12;
+	cf->system_identifier |= getpid() & 0xFFF;
+
+	if (!dry_run)
+		update_controlfile(datadir, cf, true);
+
+	pg_log_info("system identifier is %llu on subscriber", (unsigned long long) cf->system_identifier);
+
+	pg_log_info("running pg_resetwal on the subscriber");
+
+	cmd_str = psprintf("\"%s\" -D \"%s\"", pg_resetwal_path, datadir);
+
+	pg_log_debug("command is: %s", cmd_str);
+
+	if (!dry_run)
+	{
+		rc = system(cmd_str);
+		if (rc == 0)
+			pg_log_info("subscriber successfully changed the system identifier");
+		else
+			pg_log_error("subscriber failed to change system identifier: exit code: %d", rc);
+	}
+
+	pfree(cf);
+}
+
+/*
+ * Return a palloc'd slot name if the replication is using one.
+ */
+static char *
+use_primary_slot_name(void)
+{
+	PGconn	   *conn;
+	PGresult   *res;
+	PQExpBuffer str = createPQExpBuffer();
+	char	   *slot_name;
+
+	conn = connect_database(dbinfo[0].subconninfo);
+	if (conn == NULL)
+		exit(1);
+
+	res = PQexec(conn, "SELECT setting FROM pg_settings WHERE name = 'primary_slot_name'");
+	if (PQresultStatus(res) != PGRES_TUPLES_OK)
+	{
+		pg_log_error("could not obtain parameter information: %s", PQresultErrorMessage(res));
+		return NULL;
+	}
+
+	/*
+	 * If primary_slot_name is an empty string, the current replication
+	 * connection is not using a replication slot, bail out.
+	 */
+	if (strcmp(PQgetvalue(res, 0, 0), "") == 0)
+	{
+		PQclear(res);
+		return NULL;
+	}
+
+	slot_name = pg_strdup(PQgetvalue(res, 0, 0));
+	PQclear(res);
+
+	disconnect_database(conn);
+
+	conn = connect_database(dbinfo[0].pubconninfo);
+	if (conn == NULL)
+		exit(1);
+
+	appendPQExpBuffer(str,
+					  "SELECT 1 FROM pg_replication_slots r INNER JOIN pg_stat_activity a ON (r.active_pid = a.pid) WHERE slot_name = '%s'", slot_name);
+
+	pg_log_debug("command is: %s", str->data);
+
+	res = PQexec(conn, str->data);
+	if (PQresultStatus(res) != PGRES_TUPLES_OK)
+	{
+		pg_log_error("could not obtain replication slot information: %s", PQresultErrorMessage(res));
+		return NULL;
+	}
+
+	if (PQntuples(res) != 1)
+	{
+		pg_log_error("could not obtain replication slot information: got %d rows, expected %d row",
+					 PQntuples(res), 1);
+		return NULL;
+	}
+
+	PQclear(res);
+	disconnect_database(conn);
+
+	return slot_name;
+}
+
+static bool
+create_all_logical_replication_slots(LogicalRepInfo *dbinfo)
+{
+	int			i;
+
+	for (i = 0; i < num_dbs; i++)
+	{
+		PGconn	   *conn;
+		PGresult   *res;
+		char		replslotname[NAMEDATALEN];
+
+		conn = connect_database(dbinfo[i].pubconninfo);
+		if (conn == NULL)
+			exit(1);
+
+		res = PQexec(conn,
+					 "SELECT oid FROM pg_catalog.pg_database WHERE datname = current_database()");
+		if (PQresultStatus(res) != PGRES_TUPLES_OK)
+		{
+			pg_log_error("could not obtain database OID: %s", PQresultErrorMessage(res));
+			return false;
+		}
+
+		if (PQntuples(res) != 1)
+		{
+			pg_log_error("could not obtain database OID: got %d rows, expected %d rows",
+						 PQntuples(res), 1);
+			return false;
+		}
+
+		/* Remember database OID. */
+		dbinfo[i].oid = strtoul(PQgetvalue(res, 0, 0), NULL, 10);
+
+		PQclear(res);
+
+		/*
+		 * Build the replication slot name. The name must not exceed
+		 * NAMEDATALEN - 1. This current schema uses a maximum of 36
+		 * characters (14 + 10 + 1 + 10 + '\0'). System identifier is included
+		 * to reduce the probability of collision. By default, subscription
+		 * name is used as replication slot name.
+		 */
+		snprintf(replslotname, sizeof(replslotname),
+				 "pg_subscriber_%u_%d",
+				 dbinfo[i].oid,
+				 (int) getpid());
+		dbinfo[i].subname = pg_strdup(replslotname);
+
+		/* Create replication slot on publisher. */
+		if (create_logical_replication_slot(conn, &dbinfo[i], replslotname) != NULL || dry_run)
+			pg_log_info("create replication slot \"%s\" on publisher", replslotname);
+		else
+			return false;
+
+		disconnect_database(conn);
+	}
+
+	return true;
+}
+
+/*
+ * Create a logical replication slot and returns a consistent LSN. The returned
+ * LSN might be used to catch up the subscriber up to the required point.
+ *
+ * CreateReplicationSlot() is not used because it does not provide the one-row
+ * result set that contains the consistent LSN.
+ */
+static char *
+create_logical_replication_slot(PGconn *conn, LogicalRepInfo *dbinfo,
+								char *slot_name)
+{
+	PQExpBuffer str = createPQExpBuffer();
+	PGresult   *res = NULL;
+	char	   *lsn = NULL;
+	bool		transient_replslot = false;
+
+	Assert(conn != NULL);
+
+	/*
+	 * If no slot name is informed, it is a transient replication slot used
+	 * only for catch up purposes.
+	 */
+	if (slot_name[0] == '\0')
+	{
+		snprintf(slot_name, NAMEDATALEN, "pg_subscriber_%d_startpoint",
+				 (int) getpid());
+		transient_replslot = true;
+	}
+
+	pg_log_info("creating the replication slot \"%s\" on database \"%s\"", slot_name, dbinfo->dbname);
+
+	appendPQExpBuffer(str, "CREATE_REPLICATION_SLOT \"%s\"", slot_name);
+	appendPQExpBufferStr(str, " LOGICAL \"pgoutput\" NOEXPORT_SNAPSHOT");
+
+	pg_log_debug("command is: %s", str->data);
+
+	if (!dry_run)
+	{
+		res = PQexec(conn, str->data);
+		if (PQresultStatus(res) != PGRES_TUPLES_OK)
+		{
+			pg_log_error("could not create replication slot \"%s\" on database \"%s\": %s", slot_name, dbinfo->dbname,
+						 PQresultErrorMessage(res));
+			return lsn;
+		}
+	}
+
+	/* for cleanup purposes */
+	if (transient_replslot)
+		made_transient_replslot = true;
+	else
+		dbinfo->made_replslot = true;
+
+	if (!dry_run)
+	{
+		lsn = pg_strdup(PQgetvalue(res, 0, 1));
+		PQclear(res);
+	}
+
+	destroyPQExpBuffer(str);
+
+	return lsn;
+}
+
+static void
+drop_replication_slot(PGconn *conn, LogicalRepInfo *dbinfo, const char *slot_name)
+{
+	PQExpBuffer str = createPQExpBuffer();
+	PGresult   *res;
+
+	Assert(conn != NULL);
+
+	pg_log_info("dropping the replication slot \"%s\" on database \"%s\"", slot_name, dbinfo->dbname);
+
+	appendPQExpBuffer(str, "DROP_REPLICATION_SLOT \"%s\"", slot_name);
+
+	pg_log_debug("command is: %s", str->data);
+
+	if (!dry_run)
+	{
+		res = PQexec(conn, str->data);
+		if (PQresultStatus(res) != PGRES_COMMAND_OK)
+			pg_log_error("could not drop replication slot \"%s\" on database \"%s\": %s", slot_name, dbinfo->dbname,
+						 PQerrorMessage(conn));
+
+		PQclear(res);
+	}
+
+	destroyPQExpBuffer(str);
+}
+
+/*
+ * Reports a suitable message if pg_ctl fails.
+ */
+static void
+pg_ctl_status(const char *pg_ctl_cmd, int rc, int action)
+{
+	if (rc != 0)
+	{
+		if (WIFEXITED(rc))
+		{
+			pg_log_error("pg_ctl failed with exit code %d", WEXITSTATUS(rc));
+		}
+		else if (WIFSIGNALED(rc))
+		{
+#if defined(WIN32)
+			pg_log_error("pg_ctl was terminated by exception 0x%X", WTERMSIG(rc));
+			pg_log_error_detail("See C include file \"ntstatus.h\" for a description of the hexadecimal value.");
+#else
+			pg_log_error("pg_ctl was terminated by signal %d: %s",
+						 WTERMSIG(rc), pg_strsignal(WTERMSIG(rc)));
+#endif
+		}
+		else
+		{
+			pg_log_error("pg_ctl exited with unrecognized status %d", rc);
+		}
+
+		pg_log_error_detail("The failed command was: %s", pg_ctl_cmd);
+		exit(1);
+	}
+
+	if (action)
+		pg_log_info("postmaster was started");
+	else
+		pg_log_info("postmaster was stopped");
+}
+
+/*
+ * Returns after the server finishes the recovery process.
+ */
+static void
+wait_for_end_recovery(const char *conninfo)
+{
+	PGconn	   *conn;
+	PGresult   *res;
+	int			status = POSTMASTER_STILL_STARTING;
+
+	pg_log_info("waiting the postmaster to reach the consistent state");
+
+	conn = connect_database(conninfo);
+	if (conn == NULL)
+		exit(1);
+
+	for (;;)
+	{
+		bool		in_recovery;
+
+		res = PQexec(conn, "SELECT pg_catalog.pg_is_in_recovery()");
+
+		if (PQresultStatus(res) != PGRES_TUPLES_OK)
+		{
+			pg_log_error("could not obtain recovery progress");
+			exit(1);
+		}
+
+		if (PQntuples(res) != 1)
+		{
+			pg_log_error("unexpected result from pg_is_in_recovery function");
+			exit(1);
+		}
+
+		in_recovery = (strcmp(PQgetvalue(res, 0, 0), "t") == 0);
+
+		PQclear(res);
+
+		/*
+		 * Does the recovery process finish? In dry run mode, there is no
+		 * recovery mode. Bail out as the recovery process has ended.
+		 */
+		if (!in_recovery || dry_run)
+		{
+			status = POSTMASTER_READY;
+			break;
+		}
+
+		/* Keep waiting. */
+		pg_usleep(WAIT_INTERVAL * USEC_PER_SEC);
+	}
+
+	disconnect_database(conn);
+
+	if (status == POSTMASTER_STILL_STARTING)
+	{
+		pg_log_error("server did not end recovery");
+		exit(1);
+	}
+
+	pg_log_info("postmaster reached the consistent state");
+}
+
+/*
+ * Create a publication that includes all tables in the database.
+ */
+static void
+create_publication(PGconn *conn, LogicalRepInfo *dbinfo)
+{
+	PQExpBuffer str = createPQExpBuffer();
+	PGresult   *res;
+
+	Assert(conn != NULL);
+
+	/* Check if the publication needs to be created. */
+	appendPQExpBuffer(str,
+					  "SELECT puballtables FROM pg_catalog.pg_publication WHERE pubname = '%s'",
+					  dbinfo->pubname);
+	res = PQexec(conn, str->data);
+	if (PQresultStatus(res) != PGRES_TUPLES_OK)
+	{
+		pg_log_error("could not obtain publication information: %s",
+					 PQresultErrorMessage(res));
+		PQclear(res);
+		PQfinish(conn);
+		exit(1);
+	}
+
+	if (PQntuples(res) == 1)
+	{
+		/*
+		 * If publication name already exists and puballtables is true, let's
+		 * use it. A previous run of pg_subscriber must have created this
+		 * publication. Bail out.
+		 */
+		if (strcmp(PQgetvalue(res, 0, 0), "t") == 0)
+		{
+			pg_log_info("publication \"%s\" already exists", dbinfo->pubname);
+			return;
+		}
+		else
+		{
+			/*
+			 * Unfortunately, if it reaches this code path, it will always
+			 * fail (unless you decide to change the existing publication
+			 * name). That's bad but it is very unlikely that the user will
+			 * choose a name with pg_subscriber_ prefix followed by the exact
+			 * database oid in which puballtables is false.
+			 */
+			pg_log_error("publication \"%s\" does not replicate changes for all tables",
+						 dbinfo->pubname);
+			pg_log_error_hint("Consider renaming this publication.");
+			PQclear(res);
+			PQfinish(conn);
+			exit(1);
+		}
+	}
+
+	PQclear(res);
+	resetPQExpBuffer(str);
+
+	pg_log_info("creating publication \"%s\" on database \"%s\"", dbinfo->pubname, dbinfo->dbname);
+
+	appendPQExpBuffer(str, "CREATE PUBLICATION %s FOR ALL TABLES", dbinfo->pubname);
+
+	pg_log_debug("command is: %s", str->data);
+
+	if (!dry_run)
+	{
+		res = PQexec(conn, str->data);
+		if (PQresultStatus(res) != PGRES_COMMAND_OK)
+		{
+			pg_log_error("could not create publication \"%s\" on database \"%s\": %s",
+						 dbinfo->pubname, dbinfo->dbname, PQerrorMessage(conn));
+			PQfinish(conn);
+			exit(1);
+		}
+	}
+
+	/* for cleanup purposes */
+	dbinfo->made_publication = true;
+
+	if (!dry_run)
+		PQclear(res);
+
+	destroyPQExpBuffer(str);
+}
+
+/*
+ * Remove publication if it couldn't finish all steps.
+ */
+static void
+drop_publication(PGconn *conn, LogicalRepInfo *dbinfo)
+{
+	PQExpBuffer str = createPQExpBuffer();
+	PGresult   *res;
+
+	Assert(conn != NULL);
+
+	pg_log_info("dropping publication \"%s\" on database \"%s\"", dbinfo->pubname, dbinfo->dbname);
+
+	appendPQExpBuffer(str, "DROP PUBLICATION %s", dbinfo->pubname);
+
+	pg_log_debug("command is: %s", str->data);
+
+	if (!dry_run)
+	{
+		res = PQexec(conn, str->data);
+		if (PQresultStatus(res) != PGRES_COMMAND_OK)
+			pg_log_error("could not drop publication \"%s\" on database \"%s\": %s", dbinfo->pubname, dbinfo->dbname, PQerrorMessage(conn));
+
+		PQclear(res);
+	}
+
+	destroyPQExpBuffer(str);
+}
+
+/*
+ * Create a subscription with some predefined options.
+ *
+ * A replication slot was already created in a previous step. Let's use it. By
+ * default, the subscription name is used as replication slot name. It is
+ * not required to copy data. The subscription will be created but it will not
+ * be enabled now. That's because the replication progress must be set and the
+ * replication origin name (one of the function arguments) contains the
+ * subscription OID in its name. Once the subscription is created,
+ * set_replication_progress() can obtain the chosen origin name and set up its
+ * initial location.
+ */
+static void
+create_subscription(PGconn *conn, LogicalRepInfo *dbinfo)
+{
+	PQExpBuffer str = createPQExpBuffer();
+	PGresult   *res;
+
+	Assert(conn != NULL);
+
+	pg_log_info("creating subscription \"%s\" on database \"%s\"", dbinfo->subname, dbinfo->dbname);
+
+	appendPQExpBuffer(str,
+					  "CREATE SUBSCRIPTION %s CONNECTION '%s' PUBLICATION %s "
+					  "WITH (create_slot = false, copy_data = false, enabled = false)",
+					  dbinfo->subname, dbinfo->pubconninfo, dbinfo->pubname);
+
+	pg_log_debug("command is: %s", str->data);
+
+	if (!dry_run)
+	{
+		res = PQexec(conn, str->data);
+		if (PQresultStatus(res) != PGRES_COMMAND_OK)
+		{
+			pg_log_error("could not create subscription \"%s\" on database \"%s\": %s",
+						 dbinfo->subname, dbinfo->dbname, PQerrorMessage(conn));
+			PQfinish(conn);
+			exit(1);
+		}
+	}
+
+	/* for cleanup purposes */
+	dbinfo->made_subscription = true;
+
+	if (!dry_run)
+		PQclear(res);
+
+	destroyPQExpBuffer(str);
+}
+
+/*
+ * Remove subscription if it couldn't finish all steps.
+ */
+static void
+drop_subscription(PGconn *conn, LogicalRepInfo *dbinfo)
+{
+	PQExpBuffer str = createPQExpBuffer();
+	PGresult   *res;
+
+	Assert(conn != NULL);
+
+	pg_log_info("dropping subscription \"%s\" on database \"%s\"", dbinfo->subname, dbinfo->dbname);
+
+	appendPQExpBuffer(str, "DROP SUBSCRIPTION %s", dbinfo->subname);
+
+	pg_log_debug("command is: %s", str->data);
+
+	if (!dry_run)
+	{
+		res = PQexec(conn, str->data);
+		if (PQresultStatus(res) != PGRES_COMMAND_OK)
+			pg_log_error("could not drop subscription \"%s\" on database \"%s\": %s", dbinfo->subname, dbinfo->dbname, PQerrorMessage(conn));
+
+		PQclear(res);
+	}
+
+	destroyPQExpBuffer(str);
+}
+
+/*
+ * Sets the replication progress to the consistent LSN.
+ *
+ * The subscriber caught up to the consistent LSN provided by the temporary
+ * replication slot. The goal is to set up the initial location for the logical
+ * replication that is the exact LSN that the subscriber was promoted. Once the
+ * subscription is enabled it will start streaming from that location onwards.
+ * In dry run mode, the subscription OID and LSN are set to invalid values for
+ * printing purposes.
+ */
+static void
+set_replication_progress(PGconn *conn, LogicalRepInfo *dbinfo, const char *lsn)
+{
+	PQExpBuffer str = createPQExpBuffer();
+	PGresult   *res;
+	Oid			suboid;
+	char		originname[NAMEDATALEN];
+	char		lsnstr[17 + 1]; /* MAXPG_LSNLEN = 17 */
+
+	Assert(conn != NULL);
+
+	appendPQExpBuffer(str,
+					  "SELECT oid FROM pg_catalog.pg_subscription WHERE subname = '%s'", dbinfo->subname);
+
+	res = PQexec(conn, str->data);
+	if (PQresultStatus(res) != PGRES_TUPLES_OK)
+	{
+		pg_log_error("could not obtain subscription OID: %s",
+					 PQresultErrorMessage(res));
+		PQclear(res);
+		PQfinish(conn);
+		exit(1);
+	}
+
+	if (PQntuples(res) != 1 && !dry_run)
+	{
+		pg_log_error("could not obtain subscription OID: got %d rows, expected %d rows",
+					 PQntuples(res), 1);
+		PQclear(res);
+		PQfinish(conn);
+		exit(1);
+	}
+
+	if (dry_run)
+	{
+		suboid = InvalidOid;
+		snprintf(lsnstr, sizeof(lsnstr), "%X/%X", LSN_FORMAT_ARGS((XLogRecPtr) InvalidXLogRecPtr));
+	}
+	else
+	{
+		suboid = strtoul(PQgetvalue(res, 0, 0), NULL, 10);
+		snprintf(lsnstr, sizeof(lsnstr), "%s", lsn);
+	}
+
+	/*
+	 * The origin name is defined as pg_%u. %u is the subscription OID. See
+	 * ApplyWorkerMain().
+	 */
+	snprintf(originname, sizeof(originname), "pg_%u", suboid);
+
+	PQclear(res);
+
+	pg_log_info("setting the replication progress (node name \"%s\" ; LSN %s) on database \"%s\"",
+				originname, lsnstr, dbinfo->dbname);
+
+	resetPQExpBuffer(str);
+	appendPQExpBuffer(str,
+					  "SELECT pg_catalog.pg_replication_origin_advance('%s', '%s')", originname, lsnstr);
+
+	pg_log_debug("command is: %s", str->data);
+
+	if (!dry_run)
+	{
+		res = PQexec(conn, str->data);
+		if (PQresultStatus(res) != PGRES_TUPLES_OK)
+		{
+			pg_log_error("could not set replication progress for the subscription \"%s\": %s",
+						 dbinfo->subname, PQresultErrorMessage(res));
+			PQfinish(conn);
+			exit(1);
+		}
+
+		PQclear(res);
+	}
+
+	destroyPQExpBuffer(str);
+}
+
+/*
+ * Enables the subscription.
+ *
+ * The subscription was created in a previous step but it was disabled. After
+ * adjusting the initial location, enabling the subscription is the last step
+ * of this setup.
+ */
+static void
+enable_subscription(PGconn *conn, LogicalRepInfo *dbinfo)
+{
+	PQExpBuffer str = createPQExpBuffer();
+	PGresult   *res;
+
+	Assert(conn != NULL);
+
+	pg_log_info("enabling subscription \"%s\" on database \"%s\"", dbinfo->subname, dbinfo->dbname);
+
+	appendPQExpBuffer(str, "ALTER SUBSCRIPTION %s ENABLE", dbinfo->subname);
+
+	pg_log_debug("command is: %s", str->data);
+
+	if (!dry_run)
+	{
+		res = PQexec(conn, str->data);
+		if (PQresultStatus(res) != PGRES_COMMAND_OK)
+		{
+			pg_log_error("could not enable subscription \"%s\": %s", dbinfo->subname,
+						 PQerrorMessage(conn));
+			PQfinish(conn);
+			exit(1);
+		}
+
+		PQclear(res);
+	}
+
+	destroyPQExpBuffer(str);
+}
+
+int
+main(int argc, char **argv)
+{
+	static struct option long_options[] =
+	{
+		{"help", no_argument, NULL, '?'},
+		{"version", no_argument, NULL, 'V'},
+		{"pgdata", required_argument, NULL, 'D'},
+		{"publisher-conninfo", required_argument, NULL, 'P'},
+		{"subscriber-conninfo", required_argument, NULL, 'S'},
+		{"database", required_argument, NULL, 'd'},
+		{"dry-run", no_argument, NULL, 'n'},
+		{"verbose", no_argument, NULL, 'v'},
+		{NULL, 0, NULL, 0}
+	};
+
+	int			c;
+	int			option_index;
+	int			rc;
+
+	char	   *pg_ctl_cmd;
+
+	char	   *base_dir;
+	char	   *server_start_log;
+
+	char		timebuf[128];
+	struct timeval time;
+	time_t		tt;
+	int			len;
+
+	char	   *pub_base_conninfo = NULL;
+	char	   *sub_base_conninfo = NULL;
+	char	   *dbname_conninfo = NULL;
+
+	uint64		pub_sysid;
+	uint64		sub_sysid;
+	struct stat statbuf;
+
+	PGconn	   *conn;
+	char	   *consistent_lsn;
+
+	PQExpBuffer recoveryconfcontents = NULL;
+
+	char		pidfile[MAXPGPATH];
+
+	int			i;
+
+	pg_logging_init(argv[0]);
+	pg_logging_set_level(PG_LOG_WARNING);
+	progname = get_progname(argv[0]);
+	set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pg_subscriber"));
+
+	if (argc > 1)
+	{
+		if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
+		{
+			usage();
+			exit(0);
+		}
+		else if (strcmp(argv[1], "-V") == 0
+				 || strcmp(argv[1], "--version") == 0)
+		{
+			puts("pg_subscriber (PostgreSQL) " PG_VERSION);
+			exit(0);
+		}
+	}
+
+	atexit(cleanup_objects_atexit);
+
+	/*
+	 * Don't allow it to be run as root. It uses pg_ctl which does not allow
+	 * it either.
+	 */
+#ifndef WIN32
+	if (geteuid() == 0)
+	{
+		pg_log_error("cannot be executed by \"root\"");
+		pg_log_error_hint("You must run %s as the PostgreSQL superuser.",
+						  progname);
+		exit(1);
+	}
+#endif
+
+	while ((c = getopt_long(argc, argv, "D:P:S:d:nv",
+							long_options, &option_index)) != -1)
+	{
+		switch (c)
+		{
+			case 'D':
+				subscriber_dir = pg_strdup(optarg);
+				break;
+			case 'P':
+				pub_conninfo_str = pg_strdup(optarg);
+				break;
+			case 'S':
+				sub_conninfo_str = pg_strdup(optarg);
+				break;
+			case 'd':
+				/* Ignore duplicated database names. */
+				if (!simple_string_list_member(&database_names, optarg))
+				{
+					simple_string_list_append(&database_names, optarg);
+					num_dbs++;
+				}
+				break;
+			case 'n':
+				dry_run = true;
+				break;
+			case 'v':
+				pg_logging_increase_verbosity();
+				break;
+			default:
+				/* getopt_long already emitted a complaint */
+				pg_log_error_hint("Try \"%s --help\" for more information.", progname);
+				exit(1);
+		}
+	}
+
+	/*
+	 * Any non-option arguments?
+	 */
+	if (optind < argc)
+	{
+		pg_log_error("too many command-line arguments (first is \"%s\")",
+					 argv[optind]);
+		pg_log_error_hint("Try \"%s --help\" for more information.", progname);
+		exit(1);
+	}
+
+	/*
+	 * Required arguments
+	 */
+	if (subscriber_dir == NULL)
+	{
+		pg_log_error("no subscriber data directory specified");
+		pg_log_error_hint("Try \"%s --help\" for more information.", progname);
+		exit(1);
+	}
+
+	/*
+	 * Parse connection string. Build a base connection string that might be
+	 * reused by multiple databases.
+	 */
+	if (pub_conninfo_str == NULL)
+	{
+		/*
+		 * TODO use primary_conninfo (if available) from subscriber and
+		 * extract publisher connection string. Assume that there are
+		 * identical entries for physical and logical replication. If there is
+		 * not, we would fail anyway.
+		 */
+		pg_log_error("no publisher connection string specified");
+		pg_log_error_hint("Try \"%s --help\" for more information.", progname);
+		exit(1);
+	}
+	pub_base_conninfo = get_base_conninfo(pub_conninfo_str, dbname_conninfo,
+										  "publisher");
+	if (pub_base_conninfo == NULL)
+		exit(1);
+
+	if (sub_conninfo_str == NULL)
+	{
+		pg_log_error("no subscriber connection string specified");
+		pg_log_error_hint("Try \"%s --help\" for more information.", progname);
+		exit(1);
+	}
+	sub_base_conninfo = get_base_conninfo(sub_conninfo_str, NULL, "subscriber");
+	if (sub_base_conninfo == NULL)
+		exit(1);
+
+	if (database_names.head == NULL)
+	{
+		pg_log_info("no database was specified");
+
+		/*
+		 * If --database option is not provided, try to obtain the dbname from
+		 * the publisher conninfo. If dbname parameter is not available, error
+		 * out.
+		 */
+		if (dbname_conninfo)
+		{
+			simple_string_list_append(&database_names, dbname_conninfo);
+			num_dbs++;
+
+			pg_log_info("database \"%s\" was extracted from the publisher connection string",
+						dbname_conninfo);
+		}
+		else
+		{
+			pg_log_error("no database name specified");
+			pg_log_error_hint("Try \"%s --help\" for more information.", progname);
+			exit(1);
+		}
+	}
+
+	/*
+	 * Get the absolute path of pg_ctl and pg_resetwal on the subscriber.
+	 */
+	if (!get_exec_path(argv[0]))
+		exit(1);
+
+	/* rudimentary check for a data directory. */
+	if (!check_data_directory(subscriber_dir))
+		exit(1);
+
+	/* Store database information for publisher and subscriber. */
+	dbinfo = store_pub_sub_info(pub_base_conninfo, sub_base_conninfo);
+
+	/*
+	 * Check if the subscriber data directory has the same system identifier
+	 * than the publisher data directory.
+	 */
+	pub_sysid = get_sysid_from_conn(dbinfo[0].pubconninfo);
+	sub_sysid = get_control_from_datadir(subscriber_dir);
+	if (pub_sysid != sub_sysid)
+	{
+		pg_log_error("subscriber data directory is not a copy of the source database cluster");
+		exit(1);
+	}
+
+	/*
+	 * Create the output directory to store any data generated by this tool.
+	 */
+	base_dir = (char *) pg_malloc0(MAXPGPATH);
+	len = snprintf(base_dir, MAXPGPATH, "%s/%s", subscriber_dir, PGS_OUTPUT_DIR);
+	if (len >= MAXPGPATH)
+	{
+		pg_log_error("directory path for subscriber is too long");
+		exit(1);
+	}
+
+	if (mkdir(base_dir, pg_dir_create_mode) < 0 && errno != EEXIST)
+	{
+		pg_log_error("could not create directory \"%s\": %m", base_dir);
+		exit(1);
+	}
+
+	/* subscriber PID file. */
+	snprintf(pidfile, MAXPGPATH, "%s/postmaster.pid", subscriber_dir);
+
+	/*
+	 * Stop the subscriber if it is a standby server. Before executing the
+	 * transformation steps, make sure the subscriber is not running because
+	 * one of the steps is to modify some recovery parameters that require a
+	 * restart.
+	 */
+	if (stat(pidfile, &statbuf) == 0)
+	{
+		/*
+		 * Since the standby server is running, check if it is using an
+		 * existing replication slot for WAL retention purposes. This
+		 * replication slot has no use after the transformation, hence, it
+		 * will be removed at the end of this process.
+		 */
+		primary_slot_name = use_primary_slot_name();
+		if (primary_slot_name != NULL)
+			pg_log_info("primary has replication slot \"%s\"", primary_slot_name);
+
+		pg_log_info("subscriber is up and running");
+		pg_log_info("stopping the server to start the transformation steps");
+
+		pg_ctl_cmd = psprintf("\"%s\" stop -D \"%s\" -s", pg_ctl_path, subscriber_dir);
+		rc = system(pg_ctl_cmd);
+		pg_ctl_status(pg_ctl_cmd, rc, 0);
+	}
+
+	/*
+	 * Create a replication slot for each database on the publisher.
+	 */
+	if (!create_all_logical_replication_slots(dbinfo))
+		exit(1);
+
+	/*
+	 * Create a logical replication slot to get a consistent LSN.
+	 *
+	 * This consistent LSN will be used later to advanced the recently created
+	 * replication slots. We cannot use the last created replication slot
+	 * because the consistent LSN should be obtained *after* the base backup
+	 * finishes (and the base backup should include the logical replication
+	 * slots).
+	 *
+	 * XXX we should probably use the last created replication slot to get a
+	 * consistent LSN but it should be changed after adding pg_basebackup
+	 * support.
+	 *
+	 * A temporary replication slot is not used here to avoid keeping a
+	 * replication connection open (depending when base backup was taken, the
+	 * connection should be open for a few hours).
+	 */
+	conn = connect_database(dbinfo[0].pubconninfo);
+	if (conn == NULL)
+		exit(1);
+	consistent_lsn = create_logical_replication_slot(conn, &dbinfo[0],
+													 temp_replslot);
+
+	/*
+	 * Write recovery parameters.
+	 *
+	 * Despite of the recovery parameters will be written to the subscriber,
+	 * use a publisher connection for the follwing recovery functions. The
+	 * connection is only used to check the current server version (physical
+	 * replica, same server version). The subscriber is not running yet. In
+	 * dry run mode, the recovery parameters *won't* be written. An invalid
+	 * LSN is used for printing purposes.
+	 */
+	recoveryconfcontents = GenerateRecoveryConfig(conn, NULL);
+	appendPQExpBuffer(recoveryconfcontents, "recovery_target_inclusive = true\n");
+	appendPQExpBuffer(recoveryconfcontents, "recovery_target_action = promote\n");
+
+	if (dry_run)
+	{
+		appendPQExpBuffer(recoveryconfcontents, "# dry run mode");
+		appendPQExpBuffer(recoveryconfcontents, "recovery_target_lsn = '%X/%X'\n",
+						  LSN_FORMAT_ARGS((XLogRecPtr) InvalidXLogRecPtr));
+	}
+	else
+	{
+		appendPQExpBuffer(recoveryconfcontents, "recovery_target_lsn = '%s'\n",
+						  consistent_lsn);
+		WriteRecoveryConfig(conn, subscriber_dir, recoveryconfcontents);
+	}
+	disconnect_database(conn);
+
+	pg_log_debug("recovery parameters:\n%s", recoveryconfcontents->data);
+
+	/*
+	 * Start subscriber and wait until accepting connections.
+	 */
+	pg_log_info("starting the subscriber");
+
+	/* append timestamp with ISO 8601 format. */
+	gettimeofday(&time, NULL);
+	tt = (time_t) time.tv_sec;
+	strftime(timebuf, sizeof(timebuf), "%Y%m%dT%H%M%S", localtime(&tt));
+	snprintf(timebuf + strlen(timebuf), sizeof(timebuf) - strlen(timebuf),
+			 ".%03d", (int) (time.tv_usec / 1000));
+
+	server_start_log = (char *) pg_malloc0(MAXPGPATH);
+	len = snprintf(server_start_log, MAXPGPATH, "%s/%s/server_start_%s.log", subscriber_dir, PGS_OUTPUT_DIR, timebuf);
+	if (len >= MAXPGPATH)
+	{
+		pg_log_error("log file path is too long");
+		exit(1);
+	}
+
+	pg_ctl_cmd = psprintf("\"%s\" start -D \"%s\" -s -l \"%s\"", pg_ctl_path, subscriber_dir, server_start_log);
+	rc = system(pg_ctl_cmd);
+	pg_ctl_status(pg_ctl_cmd, rc, 1);
+
+	/*
+	 * Waiting the subscriber to be promoted.
+	 */
+	wait_for_end_recovery(dbinfo[0].subconninfo);
+
+	/*
+	 * Create a publication for each database. This step should be executed
+	 * after promoting the subscriber to avoid replicating unnecessary
+	 * objects.
+	 */
+	for (i = 0; i < num_dbs; i++)
+	{
+		char		pubname[NAMEDATALEN];
+
+		/* Connect to publisher. */
+		conn = connect_database(dbinfo[i].pubconninfo);
+		if (conn == NULL)
+			exit(1);
+
+		/*
+		 * Build the publication name. The name must not exceed NAMEDATALEN -
+		 * 1. This current schema uses a maximum of 35 characters (14 + 10 +
+		 * '\0').
+		 */
+		snprintf(pubname, sizeof(pubname), "pg_subscriber_%u", dbinfo[i].oid);
+		dbinfo[i].pubname = pg_strdup(pubname);
+
+		create_publication(conn, &dbinfo[i]);
+
+		disconnect_database(conn);
+	}
+
+	/*
+	 * Create a subscription for each database.
+	 */
+	for (i = 0; i < num_dbs; i++)
+	{
+		/* Connect to subscriber. */
+		conn = connect_database(dbinfo[i].subconninfo);
+		if (conn == NULL)
+			exit(1);
+
+		create_subscription(conn, &dbinfo[i]);
+
+		/* Set the replication progress to the correct LSN. */
+		set_replication_progress(conn, &dbinfo[i], consistent_lsn);
+
+		/* Enable subscription. */
+		enable_subscription(conn, &dbinfo[i]);
+
+		disconnect_database(conn);
+	}
+
+	/*
+	 * The transient replication slot is no longer required. Drop it.
+	 *
+	 * If the physical replication slot exists, drop it.
+	 *
+	 * XXX we might not fail here. Instead, we provide a warning so the user
+	 * eventually drops the replication slot later.
+	 */
+	conn = connect_database(dbinfo[0].pubconninfo);
+	if (conn == NULL)
+	{
+		pg_log_warning("could not drop transient replication slot \"%s\" on publisher", temp_replslot);
+		pg_log_warning_hint("Drop this replication slot soon to avoid retention of WAL files.");
+		if (primary_slot_name != NULL)
+			pg_log_warning("could not drop replication slot \"%s\" on primary", primary_slot_name);
+	}
+	else
+	{
+		drop_replication_slot(conn, &dbinfo[0], temp_replslot);
+		if (primary_slot_name != NULL)
+			drop_replication_slot(conn, &dbinfo[0], primary_slot_name);
+		disconnect_database(conn);
+	}
+
+	/*
+	 * Stop the subscriber.
+	 */
+	pg_log_info("stopping the subscriber");
+
+	pg_ctl_cmd = psprintf("\"%s\" stop -D \"%s\" -s", pg_ctl_path, subscriber_dir);
+	rc = system(pg_ctl_cmd);
+	pg_ctl_status(pg_ctl_cmd, rc, 0);
+
+	/*
+	 * Change system identifier.
+	 */
+	modify_sysid(pg_resetwal_path, subscriber_dir);
+
+	/*
+	 * Remove log file generated by this tool, if it runs successfully.
+	 * Otherwise, file is kept that may provide useful debugging information.
+	 */
+	unlink(server_start_log);
+
+	success = true;
+
+	pg_log_info("Done!");
+
+	return 0;
+}
diff --git a/src/bin/pg_basebackup/t/040_pg_subscriber.pl b/src/bin/pg_basebackup/t/040_pg_subscriber.pl
new file mode 100644
index 0000000000..4ebff76b2d
--- /dev/null
+++ b/src/bin/pg_basebackup/t/040_pg_subscriber.pl
@@ -0,0 +1,44 @@
+# Copyright (c) 2024, PostgreSQL Global Development Group
+
+#
+# Test checking options of pg_subscriber.
+#
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+program_help_ok('pg_subscriber');
+program_version_ok('pg_subscriber');
+program_options_handling_ok('pg_subscriber');
+
+my $datadir = PostgreSQL::Test::Utils::tempdir;
+
+command_fails(['pg_subscriber'],
+	'no subscriber data directory specified');
+command_fails(
+	[
+		'pg_subscriber',
+		'--pgdata', $datadir
+	],
+	'no publisher connection string specified');
+command_fails(
+	[
+		'pg_subscriber',
+		'--dry-run',
+		'--pgdata', $datadir,
+		'--publisher-conninfo', 'dbname=postgres'
+	],
+	'no subscriber connection string specified');
+command_fails(
+	[
+		'pg_subscriber',
+		'--verbose',
+		'--pgdata', $datadir,
+		'--publisher-conninfo', 'dbname=postgres',
+		'--subscriber-conninfo', 'dbname=postgres'
+	],
+	'no database name specified');
+
+done_testing();
diff --git a/src/bin/pg_basebackup/t/041_pg_subscriber_standby.pl b/src/bin/pg_basebackup/t/041_pg_subscriber_standby.pl
new file mode 100644
index 0000000000..fbcd0fc82b
--- /dev/null
+++ b/src/bin/pg_basebackup/t/041_pg_subscriber_standby.pl
@@ -0,0 +1,139 @@
+# Copyright (c) 2024, PostgreSQL Global Development Group
+
+#
+# Test using a standby server as the subscriber.
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node_p;
+my $node_f;
+my $node_s;
+my $result;
+
+# Set up node P as primary
+$node_p = PostgreSQL::Test::Cluster->new('node_p');
+$node_p->init(allows_streaming => 'logical');
+$node_p->start;
+
+# Set up node F as about-to-fail node
+# The extra option forces it to initialize a new cluster instead of copying a
+# previously initdb's cluster.
+$node_f = PostgreSQL::Test::Cluster->new('node_f');
+$node_f->init(allows_streaming => 'logical', extra => [ '--no-instructions' ]);
+$node_f->start;
+
+# On node P
+# - create databases
+# - create test tables
+# - insert a row
+$node_p->safe_psql(
+	'postgres', q(
+	CREATE DATABASE pg1;
+	CREATE DATABASE pg2;
+));
+$node_p->safe_psql('pg1', 'CREATE TABLE tbl1 (a text)');
+$node_p->safe_psql('pg1', "INSERT INTO tbl1 VALUES('first row')");
+$node_p->safe_psql('pg2', 'CREATE TABLE tbl2 (a text)');
+
+# Set up node S as standby linking to node P
+$node_p->backup('backup_1');
+$node_s = PostgreSQL::Test::Cluster->new('node_s');
+$node_s->init_from_backup($node_p, 'backup_1', has_streaming => 1);
+$node_s->append_conf('postgresql.conf', 'log_min_messages = debug2');
+$node_s->set_standby_mode();
+$node_s->start;
+
+# Insert another row on node P and wait node S to catch up
+$node_p->safe_psql('pg1', "INSERT INTO tbl1 VALUES('second row')");
+$node_p->wait_for_replay_catchup($node_s);
+
+# Run pg_subscriber on about-to-fail node F
+command_fails(
+	[
+		'pg_subscriber', '--verbose',
+		'--pgdata', $node_f->data_dir,
+		'--publisher-conninfo', $node_p->connstr('pg1'),
+		'--subscriber-conninfo', $node_f->connstr('pg1'),
+		'--database', 'pg1',
+		'--database', 'pg2'
+	],
+	'subscriber data directory is not a copy of the source database cluster');
+
+# dry run mode on node S
+command_ok(
+	[
+		'pg_subscriber', '--verbose', '--dry-run',
+		'--pgdata', $node_s->data_dir,
+		'--publisher-conninfo', $node_p->connstr('pg1'),
+		'--subscriber-conninfo', $node_s->connstr('pg1'),
+		'--database', 'pg1',
+		'--database', 'pg2'
+	],
+	'run pg_subscriber --dry-run on node S');
+
+# PID sets to undefined because subscriber was stopped behind the scenes.
+# Start subscriber
+$node_s->{_pid} = undef;
+$node_s->start;
+# Check if node S is still a standby
+is($node_s->safe_psql('postgres', 'SELECT pg_is_in_recovery()'),
+	't', 'standby is in recovery');
+
+# Run pg_subscriber on node S
+command_ok(
+	[
+		'pg_subscriber', '--verbose',
+		'--pgdata', $node_s->data_dir,
+		'--publisher-conninfo', $node_p->connstr('pg1'),
+		'--subscriber-conninfo', $node_s->connstr('pg1'),
+		'--database', 'pg1',
+		'--database', 'pg2'
+	],
+	'run pg_subscriber on node S');
+
+# Insert rows on P
+$node_p->safe_psql('pg1', "INSERT INTO tbl1 VALUES('third row')");
+$node_p->safe_psql('pg2', "INSERT INTO tbl2 VALUES('row 1')");
+
+# PID sets to undefined because subscriber was stopped behind the scenes.
+# Start subscriber
+$node_s->{_pid} = undef;
+$node_s->start;
+
+# Get subscription names
+$result = $node_s->safe_psql(
+	'postgres', qq(
+	SELECT subname FROM pg_subscription WHERE subname ~ '^pg_subscriber_'
+));
+my @subnames = split("\n", $result);
+
+# Wait subscriber to catch up
+$node_s->wait_for_subscription_sync($node_p, $subnames[0]);
+$node_s->wait_for_subscription_sync($node_p, $subnames[1]);
+
+# Check result on database pg1
+$result = $node_s->safe_psql('pg1', 'SELECT * FROM tbl1');
+is( $result, qq(first row
+second row
+third row),
+	'logical replication works on database pg1');
+
+# Check result on database pg2
+$result = $node_s->safe_psql('pg2', 'SELECT * FROM tbl2');
+is( $result, qq(row 1),
+	'logical replication works on database pg2');
+
+# Different system identifier?
+my $sysid_p = $node_p->safe_psql('postgres', 'SELECT system_identifier FROM pg_control_system()');
+my $sysid_s = $node_s->safe_psql('postgres', 'SELECT system_identifier FROM pg_control_system()');
+ok($sysid_p != $sysid_s, 'system identifier was changed');
+
+# clean up
+$node_p->teardown_node;
+$node_s->teardown_node;
+
+done_testing();
-- 
2.30.2

---

From: "Euler Taveira" <[email-masked]>
Date: Thu, 11 Jan 2024 20:00:18 -0300

> They were included in 0003 patch.

[I removed the following part in the previous email and couldn't reply to it...]

> An unnecessary change for Mkvcbuild.pm (this file was removed) was ignored.

v5 removes the MSVC support.

> This contains small fixes to keep complier quiet.

I applied it. Although, I used a different approach for format specifier.

> Will add if everyone agrees these idea.

I didn't review all items but ...

> If not specified, a fixed value (50111) would be used.

My first reaction as a new user would be: why do I need to specify a port if my
--subscriber-conninfo already contains a port? Ugh. I'm wondering if we can do
it behind the scenes. Try a range of ports.

> A FATAL error would be raised if --subscriber-conninfo specifies non-local server.

Extra protection is always good. However, let's make sure this code path is
really useful. I'll think a bit about it.

> Options -o/-O were added to specify options for publications/subscriptions.

Flexibility is cool. However, I think the cost benefit of it is not good. You
have to parse the options to catch preliminary errors. Things like publish only
delete and subscription options that conflicts with the embedded ones are
additional sources of failure.

> Made standby to save their output to log file.

It was already done in v5. I did in a different way.

> Unnecessary Assert in drop_replication_slot() was removed.

Instead, I fixed the code and keep the assert.

---

From: Amit Kapila <[email-masked]>
Date: Fri, 12 Jan 2024 09:05:54 +0530

> additional sources of failure.

Yeah, I am also not sure we need those. Did we discussed about that
previously? OTOH, we may consider to enhance this tool later if we
have user demand for such options.

BTW, I think we need some way to at least drop the existing
subscriptions otherwise the newly created subscriber will attempt to
fetch the data which may not be intended. Ashutosh made an argument
above thread that we need an option for publications as well.

-- 
With Regards,
Amit Kapila.

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Fri, 12 Jan 2024 05:08:57 +0000

Dear Amit, Euler,

> have user demand for such options.

OK, so let's drop it once and consider later as an enhancement.
As Euler said, it leads additional ERRORs.

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Fri, 12 Jan 2024 06:32:33 +0000

Dear Euler,

Sorry for disturbing your work and thanks for updates.
I will review your patch again.

* 0001 patch

Confirmed that the patch could be applied.

* 0002 patch

Good, all warnings were removed. However, the patch failed to pass tests on FreeBSD twice.
 I'm quite not sure the ERROR, but is it related with us?

* 0003 patch

1.

My initial motivation of the setting was to avoid establishing connections
during the pg_subscriber. While considering more, I started to think that
--subscriber-conninfo may not be needed. pg_upgrade does not requires the
string: it requries username, and optionally port number (which would be used
during the upgrade) instead. The advantage of this approach is that we do not
have to parse the connection string.
How do you think?

2.

OK, I can wait your consideration. Note that if we follow the pg_ugprade style,
we may able to reuse check_pghost_envvar().

3. 

As I already replied, let's stop doing it once. We can resume based on the requirement.

4. 

Good. I felt that yours were better. BTW, can we record outputs by pg_subscriber to a file as well?
pg_upgrade did similar thing. Thought?

5. 

Cool.

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Fri, 12 Jan 2024 10:31:01 +0000

Dear Euler,

Here are comments for your v5 patch.

01.
In the document, two words target/standby are used as almost the same meaning.
Can you unify them?

02.
```
 <refsynopsisdiv>
  <cmdsynopsis>
   <command>pg_subscriber</command>
   <arg rep="repeat"><replaceable>option</replaceable></arg>
  </cmdsynopsis>
 </refsynopsisdiv>
```

There are some mandatory options like -D/-S/-P. It must be listed in Synopsis chapter.

03.
```
  <para>
   <application>pg_subscriber</application> takes the publisher and subscriber
   connection strings, a cluster directory from a standby server and a list of
   database names and it sets up a new logical replica using the physical
   recovery process.
  </para>
```

I briefly checked other pages and they do not describe accepted options here.
A summary of the application should be mentioned. Based on that, how about:

```
pg_subscriber creates a new <link linkend="logical-replication-subscription">
subscriber</link> from a physical standby server. This allows users to quickly
set up logical replication system.
```

04.
```
  <para>
   The <application>pg_subscriber</application> should be run at the target
   server. The source server (known as publisher server) should accept logical
   replication connections from the target server (known as subscriber server).
   The target server should accept local logical replication connection.
  </para>
```

I'm not native speaker, but they are not just recommmendations - they are surely
required. So, should we replace s/should/has to/?

05.
```
     <varlistentry>
      <term><option>-S <replaceable class="parameter">conninfo</replaceable></option></term>
      <term><option>--subscriber-conninfo=<replaceable class="parameter">conninfo</replaceable></option></term>
      <listitem>
       <para>
        The connection string to the subscriber. For details see <xref linkend="libpq-connstring"/>.
       </para>
      </listitem>
     </varlistentry>
```

I became not sure whether it is "The connection string to the subscriber.".
The server is still physical standby at that time.

06.
```
 * IDENTIFICATION
 *		src/bin/pg_subscriber/pg_subscriber.c
```

The identification is not correct.

07.
I felt that there were too many global variables and LogicalRepInfo should be
refactored. Because...

* Some info related with clusters(e.g., subscriber_dir, conninfo, ...) should be
  gathered in one struct.
* pubconninfo/subsconninfo are stored per db, but it is not needed if we have
  one base_conninfo.
* pubname/subname are not needed because we have fixed naming rule.
* pg_ctl_path and pg_resetwal_path can be conbimed into one bindir.
* num_dbs should not be alone.
...

Based on above, how about using structures like below?

```
typedef struct LogicalRepPerdbInfo
{
	Oid		oid;
	char   *dbname;
	bool	made_replslot;	/* replication slot was created */
	bool	made_publication;	/* publication was created */
	bool	made_subscription;	/* subscription was created */
} LogicalRepPerdbInfo;

typedef struct PrimaryInfo
{
	char   *base_conninfo;
	bool	made_transient_replslot;
} PrimaryInfo;

typedef struct StandbyInfo
{
	char   *base_conninfo;
	char   *bindir;
	char   *pgdata;
	char   *primary_slot_name;
} StandbyInfo;

typedef struct LogicalRepInfo
{
	int						num_dbs;
	LogicalRepPerdbInfo	   *perdb;
	PrimaryInfo			   *primary;
	StandbyInfo			   *standby;
} LogicalRepInfo;
```

08.
```
	char	   *subconninfo;	/* subscription connection string for logical
								 * replication */
```

Not sure how we should notate because the target has not been subscriber yet.

09.
```
enum WaitPMResult
{
	POSTMASTER_READY,
	POSTMASTER_STANDBY,
	POSTMASTER_STILL_STARTING,
	POSTMASTER_FAILED
};
```

This enum has been already defined in pg_ctl.c. Not sure we can use the same name.
Can we rename to PGSWaitPMResult. or export pre-existing one?

10.
```
/* Options */
static const char *progname;
```

I think it is not an option.

11.
```
/*
 * Validate a connection string. Returns a base connection string that is a
 * connection string without a database name plus a fallback application name.
 * Since we might process multiple databases, each database name will be
 * appended to this base connection string to provide a final connection string.
 * If the second argument (dbname) is not null, returns dbname if the provided
 * connection string contains it. If option --database is not provided, uses
 * dbname as the only database to setup the logical replica.
 * It is the caller's responsibility to free the returned connection string and
 * dbname.
 */
static char *
get_base_conninfo(char *conninfo, char *dbname, const char *noderole)
```
Just FYI - adding fallback_application_name may be too optimisitic. Currently
the output was used by both pg_subscriber and subscription connection. 

12.
Can we add an option not to remove log files even operations were succeeded.

13.
```
		/*
		 * Since the standby server is running, check if it is using an
		 * existing replication slot for WAL retention purposes. This
		 * replication slot has no use after the transformation, hence, it
		 * will be removed at the end of this process.
		 */
		primary_slot_name = use_primary_slot_name();
```

Now primary_slot_name is checked only when the server have been started, but
it should be checked in any cases.

14.
```
	consistent_lsn = create_logical_replication_slot(conn, &dbinfo[0],
													 temp_replslot);
```

Can we create a temporary slot here?

15.
I found that subscriptions cannot be started if tuples are inserted on publisher
after creating temp_replslot. After starting a subscriber, I got below output on the log.

```
ERROR:  could not receive data from WAL stream: ERROR:  publication "pg_subscriber_5" does not exist
CONTEXT:  slot "pg_subscriber_5_3632", output plugin "pgoutput", in the change callback, associated LSN 0/30008A8
LOG:  background worker "logical replication apply worker" (PID 3669) exited with exit code 1
```

But this is strange. I confirmed that the specified publication surely exists.
Do you know the reason?

```
publisher=# SELECT pubname FROM pg_publication;
     pubname     
-----------------
 pg_subscriber_5
(1 row)
```

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

---

From: Nazir Bilal Yavuz <[email-masked]>
Date: Fri, 12 Jan 2024 21:32:44 +0300

Hi,

>  I'm quite not sure the ERROR, but is it related with us?

FreeBSD errors started after FreeBSD's CI image was updated [1]. I do
not think error is related to this.

[1] https://cirrus-ci.com/task/4700394639589376

-- 
Regards,
Nazir Bilal Yavuz
Microsoft

---

From: Amit Kapila <[email-masked]>
Date: Sat, 13 Jan 2024 16:32:47 +0530

> have to parse the connection string.

+1. This seems worth considering. I think unless we have a good reason
to have this parameter, we should try to avoid it.

-- 
With Regards,
Amit Kapila.

---

From: Junwang Zhao <[email-masked]>
Date: Sun, 14 Jan 2024 09:42:46 +0800

Hi Euler,

+ <refnamediv>
+  <refname>pg_subscriber</refname>
+  <refpurpose>create a new logical replica from a standby server</refpurpose>
+ </refnamediv>
I'm a bit confused about this wording because we are converting a standby
to a logical replica but not creating a new logical replica and leaving the
standby as is. How about:

<refpurpose>convert a standby replica to a logical replica</refpurpose>

+  <para>

What is *local logical replication*? I can't find any clue in the patch, can you
give me some hint?

-- 
Regards
Junwang Zhao

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Wed, 17 Jan 2024 07:28:55 +0000

Dear Euler, hackers,

I found that some bugs which have been reported by Shlok were not fixed, so
made a top-up patch. 0001 was not changed, and 0002 contains below:

* Add a timeout option for the recovery option, per [1]. The code was basically ported from pg_ctl.c.
* Reject if the target server is not a standby, per [2]
* Raise FATAL error if --subscriber-conninfo specifies non-local server, per [3]
  (not sure it is really needed, so feel free reject the part.)

Feel free to merge parts of 0002 if it looks good to you.
Thanks Shlok to make a part of patch.

[1]: https://www.postgresql.org/message-id/CANhcyEUCt-g4JLQU3Q3ofFk_Vt-Tqh3ZdXoLcpT8fjz9LY_-ww%40mail.gmail.com
[2]: https://www.postgresql.org/message-id/CANhcyEUCt-g4JLQU3Q3ofFk_Vt-Tqh3ZdXoLcpT8fjz9LY_-ww%40mail.gmail.com
[3]: https://www.postgresql.org/message-id/TY3PR01MB98895BA6C1D72CB8582CACC4F5682%40TY3PR01MB9889.jpnprd01.prod.outlook.com

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

---

From: Shlok Kyal <[email-masked]>
Date: Wed, 17 Jan 2024 14:19:16 +0530

Hi,

I have some comments for the v5 patch:

1.
```
+ base_dir = (char *) pg_malloc0(MAXPGPATH);
+ len = snprintf(base_dir, MAXPGPATH, "%s/%s", subscriber_dir, PGS_OUTPUT_DIR);
```
Before these lines, I think we should use
'canonicalize_path(subscriber_dir)' to remove extra unnecessary
characters. This function is used in many places like initdb.c,
pg_ctl.c, pg_basebakup.c, etc

2.
I also feels that there are many global variables and can be arranged
as structures as suggested by Kuroda-san in [1]

[1] https://www.postgresql.org/message-id/TY3PR01MB9889C362FF76102C88FA1C29F56F2%40TY3PR01MB9889.jpnprd01.prod.outlook.com

Thanks and Regards
Shlok Kyal

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 18 Jan 2024 10:18:58 +0100

On 11.01.24 23:15, Euler Taveira wrote:
> connect to the source server (publisher) and the target server (subscriber).

Can we have a discussion on the name?

I find the name pg_subscriber too general.

The replication/backup/recovery tools in PostgreSQL are usually named 
along the lines of "verb - object".  (Otherwise, they would all be 
called "pg_backup"??)  Moreover, "pg_subscriber" also sounds like the 
name of the program that runs the subscriber itself, like what the 
walreceiver does now.

Very early in this thread, someone mentioned the name 
pg_create_subscriber, and of course there is pglogical_create_subscriber 
as the historical predecessor.  Something along those lines seems better 
to me.  Maybe there are other ideas.

---

From: Amit Kapila <[email-masked]>
Date: Thu, 18 Jan 2024 15:07:04 +0530

> to me.  Maybe there are other ideas.

The other option could be pg_createsubscriber on the lines of
pg_verifybackup and pg_combinebackup. Yet other options could be
pg_buildsubscriber, pg_makesubscriber as 'build' or 'make' in the name
sounds like we are doing some work to create the subscriber which I
think is the case here.

-- 
With Regards,
Amit Kapila.

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 22 Jan 2024 07:55:04 +0100

On 18.01.24 10:37, Amit Kapila wrote:
> pg_verifybackup and pg_combinebackup.

Yes, that spelling would be more consistent.

> Yet other options could be

I see your point here.  pg_createsubscriber is not like createuser in 
that it just runs an SQL command.  It does something different than 
CREATE SUBSCRIBER.  So a different verb would make that clearer.  Maybe 
something from here: https://www.thesaurus.com/browse/convert

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Mon, 22 Jan 2024 07:06:50 +0000

Dear hackers,

> publisher=# SELECT pubname FROM pg_publication;

I analyzed and found a reason. This is because publications are invisible for some transactions.

As the first place, below operations were executed in this case.
Tuples were inserted after getting consistent_lsn, but before starting the standby.
After doing the workload, I confirmed again that the publication was created.

1. on primary, logical replication slots were created.
2. on primary, another replication slot was created.
3. ===on primary, some tuples were inserted. ===
4. on standby, a server process was started
5. on standby, the process waited until all changes have come.
6. on primary, publications were created.
7. on standby, subscriptions were created.
8. on standby, a replication progress for each subscriptions was set to given LSN (got at step2).
=====pg_subscriber finished here=====
9. on standby, a server process was started again
10. on standby, subscriptions were enabled. They referred slots created at step1.
11. on primary, decoding was started but ERROR was raised.

In this case, tuples were inserted *before creating publication*.
So I thought that the decoded transaction could not see the publication because
it was committed after insertions.

One solution is to create a publication before creating a consistent slot.
Changes which came before creating the slot were surely replicated to the standby,
so upcoming transactions can see the object. We are planning to patch set to fix
the issue in this approach.

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Mon, 22 Jan 2024 09:08:06 +0000

Dear Peter,

> something from here: https://www.thesaurus.com/browse/convert

I read the link and found a good verb "switch". So, how about using "pg_switchsubscriber"?

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

---

From: Amit Kapila <[email-masked]>
Date: Mon, 22 Jan 2024 14:52:17 +0530

On Mon, Jan 22, 2024 at 2:38 PM Hayato Kuroda (Fujitsu)
<[email-masked]> wrote:
> > CREATE SUBSCRIBER.

Right.

  So a different verb would make that clearer.  Maybe
> I read the link and found a good verb "switch". So, how about using "pg_switchsubscriber"?

I also initially thought on these lines and came up with a name like
pg_convertsubscriber but didn't feel strongly about it as that would
have sounded meaningful if we use a name like
pg_convertstandbytosubscriber. Now, that has become too long. Having
said that, I am not opposed to it having a name on those lines. BTW,
another option that occurred to me today is pg_preparesubscriber. We
internally create slots and then wait for wal, etc. which makes me
sound like adding 'prepare' in the name can also explain the purpose.

-- 
With Regards,
Amit Kapila.

---

From: Shlok Kyal <[email-masked]>
Date: Mon, 22 Jan 2024 15:00:05 +0530

Dear Euler, hackers,

We fixed some of the comments posted in the thread. We have created it
as top-up patch 0002 and 0003.

0002 patch contains the following changes:
* Add a timeout option for the recovery option, per [1]. The code was
basically ported from pg_ctl.c.
* Reject if the target server is not a standby, per [2]
* Raise FATAL error if --subscriber-conninfo specifies non-local server, per [3]
  (not sure it is really needed, so feel free reject the part.)
* Add check for max_replication_slots and wal_level; as per [4]
* Add -u and -p options; as per [5]
* Addressed comment except 5 and 8 in [6] and comment in [7]

0003 patch contains fix for bug reported in [8].

Feel free to merge parts of 0002 and 0003 if it looks good to you.
Thanks Kuroda-san to make patch 0003 and a part of patch 0002.

[1]: https://www.postgresql.org/message-id/CANhcyEUCt-g4JLQU3Q3ofFk_Vt-Tqh3ZdXoLcpT8fjz9LY_-ww%40mail.gmail.com
[2]: https://www.postgresql.org/message-id/CANhcyEUCt-g4JLQU3Q3ofFk_Vt-Tqh3ZdXoLcpT8fjz9LY_-ww%40mail.gmail.com
[3]: https://www.postgresql.org/message-id/TY3PR01MB98895BA6C1D72CB8582CACC4F5682%40TY3PR01MB9889.jpnprd01.prod.outlook.com
[4]: https://www.postgresql.org/message-id/CALDaNm098Jkbh%2Bye6zMj9Ro9j1bBe6FfPV80BFbs1%3DpUuTJ07g%40mail.gmail.com
[5]: https://www.postgresql.org/message-id/CAA4eK1JB_ko7a5JMS3WfAn583RadAKCDhiE9JgmfMA8ZZ5xcQw%40mail.gmail.com
[6]: https://www.postgresql.org/message-id/TY3PR01MB9889C362FF76102C88FA1C29F56F2%40TY3PR01MB9889.jpnprd01.prod.outlook.com
[7]: https://www.postgresql.org/message-id/CANhcyEXjGmryoZPACS_i-joqvcz5e6Zb3u4g38SAy_iSTGhShg%40mail.gmail.com
[8]: https://www.postgresql.org/message-id/TY3PR01MB9889C5D55206DDD978627D07F5752%40TY3PR01MB9889.jpnprd01.prod.outlook.com

Thanks and regards
Shlok Kyal

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Tue, 23 Jan 2024 02:11:26 +0000

Dear hackers,

> as top-up patch 0002 and 0003.

I found that the CFbot raised an ERROR. Also, it may not work well in case
of production build.
PSA the fixed patch set.

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

---

From: "Euler Taveira" <[email-masked]>
Date: Tue, 23 Jan 2024 20:44:15 -0300

> > > that it just runs an SQL command.  It does something different than

Subscriber has a different meaning of subscription. Subscription is an SQL
object. Subscriber is the server (node in replication terminology) where the
subscription resides. Having said that pg_createsubscriber doesn't seem a bad
name because you are creating a new subscriber. (Indeed, you are transforming /
converting but "create" seems closer and users can infer that it is a tool to
build a new logical replica.

> sound like adding 'prepare' in the name can also explain the purpose.

I think "convert" and "transform" fit for this case. However, "create",
"convert" and "transform" have 6, 7 and 9 characters,  respectively. I suggest
that we avoid long names (subscriber already has 10 characters). My preference
is pg_createsubscriber.

---

From: "Euler Taveira" <[email-masked]>
Date: Tue, 23 Jan 2024 20:58:11 -0300

> 11. on primary, decoding was started but ERROR was raised.

Good catch! It is a design flaw.

> the issue in this approach.

I'll include a similar code in the next patch and also explain why we should
create the publication earlier. (I'm renaming
create_all_logical_replication_slots to setup_publisher and calling
create_publication from there and also adding the proposed GUC checks in it.)

---

From: "Euler Taveira" <[email-masked]>
Date: Tue, 23 Jan 2024 22:29:09 -0300

Cool.

> * Addressed comment except 5 and 8 in [6] and comment in [7]

My suggestion is that you create separate patches for each change. It helps
with review and alternative proposals. Some of these items conflict with what I
have in my local branch and removing one of them is time consuming. For this
one, I did the job but let's avoid rework.

> 0003 patch contains fix for bug reported in [8].

LGTM. As I said in the other email, I included it.

I'll post a new one soon.

---

From: Shubham Khanna <[email-masked]>
Date: Wed, 24 Jan 2024 11:41:01 +0530

> I found that the CFbot raised an ERROR. Also, it may not work well in case

 Segmentation fault was found after testing the given command(There is
an extra '/' between 'new_standby2' and '-P') '$ gdb --args
./pg_subscriber -D ../new_standby2 / -P "host=localhost
port=5432 dbname=postgres" -d postgres'
While executing the above command, I got the following error:
pg_subscriber: error: too many command-line arguments (first is "/")
pg_subscriber: hint: Try "pg_subscriber --help" for more information.

Program received signal SIGSEGV, Segmentation fault.
0x0000555555557e5b in cleanup_objects_atexit () at pg_subscriber.c:173
173            if (perdb->made_subscription)
(gdb) p perdb
$1 = (LogicalRepPerdbInfo *) 0x0

Thanks and Regards,
Shubham Khanna.

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Wed, 24 Jan 2024 11:43:00 +0000

Dear Shubham,

> $1 = (LogicalRepPerdbInfo *) 0x0

Good catch, I could reproduce the issue. This crash was occurred because the
cleanup function was called before initialization memory.

There are several ways to fix it, but I chose to move the callback registration
behind. The function does actual tasks only after database objects are created.
So 0004 registers the function just before doing them. The memory allocation has
been done at that time. If required, Assert() can be added in the callback.

Can you test it and confirm the issue was solved?

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Thu, 25 Jan 2024 09:05:14 +0000

Dear hackers,

Here are comments for v8 patch set. I may revise them by myself,
but I want to post here to share all of them.

01.
```
/* Options */
static char *pub_conninfo_str = NULL;
static SimpleStringList database_names = {NULL, NULL};
static int	wait_seconds = DEFAULT_WAIT;
static bool retain = false;
static bool dry_run = false;
```

Just to confirm - is there a policy why we store the specified options? If you
want to store as global ones, username and port should follow (my fault...).
Or, should we have a structure to store them?

02.
```
{"subscriber-conninfo", required_argument, NULL, 'S'},
```

This is my fault, but "--subscriber-conninfo" is still remained. It should be
removed if it is not really needed.

03.
```
{"username", required_argument, NULL, 'u'},
```

Should we accept 'U' instead of 'u'?

04.
```
{"dry-run", no_argument, NULL, 'n'},
```

I'm not sure why the dry_run mode exists. In terms pg_resetwal, it shows the
which value would be changed based on the input. As for the pg_upgrade, it checks
whether the node can be upgraded for now. I think, we should have the checking
feature, so it should be renamed to --check. Also, the process should exit earlier
at that time.

05.
I felt we should accept some settings from enviroment variables, like pg_upgrade.
Currently, below items should be acceted.

- data directory
- username
- port
- timeout

06.
```
pg_logging_set_level(PG_LOG_WARNING);
```

If the default log level is warning, there are no ways to output debug logs.
(-v option only raises one, so INFO would be output)
I think it should be PG_LOG_INFO.

07.
Can we combine verifications into two functions, e.g., check_primary() and check_standby/check_subscriber()?

08.
Not sure, but if we want to record outputs by pg_subscriber, the sub-directory
should be created. The name should contain the timestamp.

09.
Not sure, but should we check max_slot_wal_keep_size of primary server? It can
avoid to fail starting of logical replicaiton.

10.
```
	nslots_new = nslots_old + dbarr.ndbs;

	if (nslots_new > max_replication_slots)
	{
		pg_log_error("max_replication_slots (%d) must be greater than or equal to "
					 "the number of replication slots required (%d)", max_replication_slots, nslots_new);
		exit(1);
	}
```

I think standby server must not have replication slots. Because subsequent
pg_resetwal command discards all the WAL file, so WAL records pointed by them
are removed. Currently pg_resetwal does not raise ERROR at that time.

11.
```
	/*
	 * Stop the subscriber if it is a standby server. Before executing the
	 * transformation steps, make sure the subscriber is not running because
	 * one of the steps is to modify some recovery parameters that require a
	 * restart.
	 */
	if (stat(pidfile, &statbuf) == 0)
```

I kept just in case, but I'm not sure it is still needed. How do you think?
Removing it can reduce an inclusion of pidfile.h.

12.
```
		pg_ctl_cmd = psprintf("\"%s/pg_ctl\" stop -D \"%s\" -s",
							  standby.bindir, standby.pgdata);
		rc = system(pg_ctl_cmd);
		pg_ctl_status(pg_ctl_cmd, rc, 0);
```

There are two places to stop the instance. Can you divide it into a function?

13.
```
	 * A temporary replication slot is not used here to avoid keeping a
	 * replication connection open (depending when base backup was taken, the
	 * connection should be open for a few hours).
	 */
	conn = connect_database(primary.base_conninfo, dbarr.perdb[0].dbname);
	if (conn == NULL)
		exit(1);
	consistent_lsn = create_logical_replication_slot(conn, true,
													 &dbarr.perdb[0]);
```

I didn't notice the comment, but still not sure the reason. Why we must reserve
the slot until pg_subscriber finishes? IIUC, the slot would be never used, it
is created only for getting a consistent_lsn. So we do not have to keep.
Also, just before, logical replication slots for each databases are created, so
WAL records are surely reserved.

14.

```
	pg_log_info("starting the subscriber");
	start_standby_server(&standby, subport, server_start_log);
```

This info should be in the function.

15.
```
	/*
	 * Create a subscription for each database.
	 */
	for (i = 0; i < dbarr.ndbs; i++)
```

This can be divided into a function, like create_all_subscriptions().

16.
My fault: usage() must be updated.

17. use_primary_slot_name
```
	if (PQntuples(res) != 1)
	{
		pg_log_error("could not obtain replication slot information: got %d rows, expected %d row",
					 PQntuples(res), 1);
		return NULL;
	}
```

Error message should be changed. I think this error means the standby has wrong primary_slot_name, right?

18. misc
Sometimes the pid of pg_subscriber is referred. It can be stored as global variable.

19.
C99-style has been allowed, so loop variables like "i" can be declared in the for-statement, like

```
for (int i = 0; i < MAX; i++)
```

20.
Some comments, docs, and outputs must be fixed when the name is changed.

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 25 Jan 2024 10:27:21 +0100

> build a new logical replica.

That makes sense.

(Also, the problem with "convert" etc. is that "convertsubscriber" would 
imply that you are converting an existing subscriber to something else. 
It would need to be something like "convertbackup" then, which doesn't 
seem helpful.)

> that we avoid long names (subscriber already has 10 characters). My 

That seems best to me.

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Thu, 25 Jan 2024 10:16:34 +0000

Dear Peter,

Thanks for giving your idea!

> > that we avoid long names (subscriber already has 10 characters). My

Just FYI - I'm ok to change the name to pg_createsubscriber.

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

---

From: "Euler Taveira" <[email-masked]>
Date: Thu, 25 Jan 2024 20:34:46 -0300

> I'll post a new one soon.

I'm attaching another patch that fixes some of the issues pointed out by
Hayato, Shlok, and Junwang.

* publication doesn't exist. The analysis [1] was done by Hayato but I didn't
  use the proposed patch. Instead I refactored the code a bit [2] and call it
  from a new function (setup_publisher) that is called before the promotion.
* fix wrong path name in the initial comment [3]
* change terminology: logical replica -> physical replica [3]
* primary / standby is ready for logical replication? setup_publisher() and
  setup_subscriber() check if required GUCs are set accordingly. For primary,
  it checks wal_level = logical, max_replication_slots has remain replication
  slots for the proposed setup and also max_wal_senders available. For standby,
  it checks max_replication_slots for replication origin and also remain number
  of background workers to start the subscriber.
* retain option: I extracted this one from Hayato's patch [4]
* target server must be a standby. It seems we agree that this restriction
  simplifies the code a bit but can be relaxed in the future (if/when base
  backup support is added.)
* recovery timeout option: I decided to include it but I think the use case is
  too narrow. It helps in broken setups. However, it can be an issue in some
  scenarios like time-delayed replica, large replication lag, slow hardware,
  slow network. I didn't use the proposed patch [5]. Instead, I came up with a
  simple one that defaults to forever. The proposed one defaults to 60 seconds
  but I'm afraid that due to one of the scenarios I said in a previous
  sentence, we cancel a legitimate case. Maybe we should add a message during
  dry run saying that due to a replication lag, it will take longer to run.
* refactor primary_slot_name code. With the new setup_publisher and
  setup_subscriber functions, I splitted the function that detects the
  primary_slot_name use into 2 pieces just to avoid extra connections to have
  the job done.
* remove fallback_application_name as suggested by Hayato [5] because logical
  replication already includes one.

I'm still thinking about replacing --subscriber-conninfo with separate items
(username, port, password?, host = socket dir). Maybe it is an overengineering.
The user can always prepare the environment to avoid unwanted and/or external
connections.

I didn't change the name from pg_subscriber to pg_createsubscriber yet but if I
didn't hear objections about it, I'll do it in the next patch.

[1] https://www.postgresql.org/message-id/TY3PR01MB9889C5D55206DDD978627D07F5752%40TY3PR01MB9889.jpnprd01.prod.outlook.com
[2] https://www.postgresql.org/message-id/73ab86ca-3fd5-49b3-9c80-73d1525202f1%40app.fastmail.com
[3] https://www.postgresql.org/message-id/TY3PR01MB9889678E47B918F4D83A6FD8F57B2%40TY3PR01MB9889.jpnprd01.prod.outlook.com
[4] https://www.postgresql.org/message-id/TY3PR01MB9889678E47B918F4D83A6FD8F57B2%40TY3PR01MB9889.jpnprd01.prod.outlook.com
[5] https://www.postgresql.org/message-id/TY3PR01MB9889593399165B9A04106741F5662%40TY3PR01MB9889.jpnprd01.prod.outlook.com

--
Euler Taveira
EDB   https://www.enterprisedb.com/
From d9ef01a806c3d8697faa444283f19c2deaa58850 Mon Sep 17 00:00:00 2001
From: Euler Taveira <[email-masked]>
Date: Mon, 5 Jun 2023 14:39:40 -0400
Subject: [PATCH v9] Creates a new logical replica from a standby server

 doc/src/sgml/ref/pg_subscriber.sgml           |  305 +++
 src/bin/pg_basebackup/pg_subscriber.c         | 1805 +++++++++++++++++
 9 files changed, 2322 insertions(+), 1 deletion(-)

index 0000000000..99d4fcee49
@@ -0,0 +1,305 @@
+  <refpurpose>convert a physical replica into a new logical replica</refpurpose>
+   connection strings, a cluster directory from a physical replica and a list of
+        The target directory that contains a cluster directory from a physical
+        replica.
+      <term><option>-r</option></term>
+      <term><option>--retain</option></term>
+        Retain log file even after successful completion.
+       <term><option>-t <replaceable class="parameter">seconds</replaceable></option></term>
+       <term><option>--timeout=<replaceable class="parameter">seconds</replaceable></option></term>
+        The maximum number of seconds to wait for recovery to end. Setting to 0
+        disables. The default is 0.
+     directory is used by a physical replica. Stop the physical replica if it is
+   <literal>finance</literal> from a physical replica at <literal>foo</literal>:
 
 /tmp_check/

 
 
 installdirs:
 
 clean distclean:

index 0000000000..cb97dbda5e
@@ -0,0 +1,1805 @@
+ *		src/bin/pg_basebackup/pg_subscriber.c
+static bool setup_publisher(LogicalRepInfo *dbinfo);
+static bool retain = false;
+static int	recovery_timeout = 0;
+	printf(_(" -t, --recovery-timeout=SECS         seconds to wait for recovery to end\n"));
+	printf(_(" -r, --retain                        retain log file after success\n"));
+ * connection string without a database name.
+ * Is the source server ready for logical replication? If so, create the
+ * publications and replication slots in preparation for logical replication.
+setup_publisher(LogicalRepInfo *dbinfo)
+	char	   *wal_level;
+	int			max_repslots;
+	int			cur_repslots;
+	int			max_walsenders;
+	int			cur_walsenders;
+	pg_log_info("checking settings on publisher");
+	 * Logical replication requires a few parameters to be set on publisher.
+	 * Since these parameters are not a requirement for physical replication,
+	 * we should check it to make sure it won't fail.
+	 * wal_level = logical
+	 * max_replication_slots >= current + number of dbs to be converted
+	 * max_wal_senders >= current + number of dbs to be converted
+				 "WITH wl AS (SELECT setting AS wallevel FROM pg_settings WHERE name = 'wal_level'),"
+				 "     total_mrs AS (SELECT setting AS tmrs FROM pg_settings WHERE name = 'max_replication_slots'),"
+				 "     cur_mrs AS (SELECT count(*) AS cmrs FROM pg_replication_slots),"
+				 "     total_mws AS (SELECT setting AS tmws FROM pg_settings WHERE name = 'max_wal_senders'),"
+				 "     cur_mws AS (SELECT count(*) AS cmws FROM pg_stat_activity WHERE backend_type = 'walsender')"
+				 "SELECT wallevel, tmrs, cmrs, tmws, cmws FROM wl, total_mrs, cur_mrs, total_mws, cur_mws");
+		pg_log_error("could not obtain publisher settings: %s", PQresultErrorMessage(res));
+	wal_level = strdup(PQgetvalue(res, 0, 0));
+	max_repslots = atoi(PQgetvalue(res, 0, 1));
+	cur_repslots = atoi(PQgetvalue(res, 0, 2));
+	max_walsenders = atoi(PQgetvalue(res, 0, 3));
+	cur_walsenders = atoi(PQgetvalue(res, 0, 4));
+	pg_log_debug("subscriber: wal_level: %s", wal_level);
+	pg_log_debug("subscriber: max_replication_slots: %d", max_repslots);
+	pg_log_debug("subscriber: current replication slots: %d", cur_repslots);
+	pg_log_debug("subscriber: max_wal_senders: %d", max_walsenders);
+	pg_log_debug("subscriber: current wal senders: %d", cur_walsenders);
+	 * If standby sets primary_slot_name, check if this replication slot is in
+	 * use on primary for WAL retention purposes. This replication slot has no
+	 * use after the transformation, hence, it will be removed at the end of
+	 * this process.
+	if (primary_slot_name)
+						  "SELECT 1 FROM pg_replication_slots WHERE active AND slot_name = '%s'", primary_slot_name);
+			pg_free(primary_slot_name); /* it is not being used. */
+			primary_slot_name = NULL;
+	if (strcmp(wal_level, "logical") != 0)
+		pg_log_error("publisher requires wal_level >= logical");
+	if (max_repslots - cur_repslots < num_dbs)
+		pg_log_error("publisher requires %d replication slots, but only %d remain", num_dbs, max_repslots - cur_repslots);
+		pg_log_error_hint("Consider increasing max_replication_slots to at least %d.", cur_repslots + num_dbs);
+	if (max_walsenders - cur_walsenders < num_dbs)
+		pg_log_error("publisher requires %d wal sender processes, but only %d remain", num_dbs, max_walsenders - cur_walsenders);
+		pg_log_error_hint("Consider increasing max_wal_senders to at least %d.", cur_walsenders + num_dbs);
+	for (int i = 0; i < num_dbs; i++)
+		 * Create publication on publisher. This step should be executed
+		 * *before* promoting the subscriber to avoid any transactions between
+		 * consistent LSN and the new publication rows (such transactions
+		 * wouldn't see the new publication rows resulting in an error).
+ * Is the target server ready for logical replication?
+setup_subscriber(LogicalRepInfo *dbinfo)
+	int			max_lrworkers;
+	int			max_repslots;
+	int			max_wprocs;
+	pg_log_info("checking settings on subscriber");
+	 * Logical replication requires a few parameters to be set on subscriber.
+	 * Since these parameters are not a requirement for physical replication,
+	 * we should check it to make sure it won't fail.
+	 * max_replication_slots >= number of dbs to be converted
+	 * max_logical_replication_workers >= number of dbs to be converted
+	 * max_worker_processes >= 1 + number of dbs to be converted
+				 "SELECT setting FROM pg_settings WHERE name IN ('max_logical_replication_workers', 'max_replication_slots', 'max_worker_processes', 'primary_slot_name') ORDER BY name");
+		pg_log_error("could not obtain subscriber settings: %s", PQresultErrorMessage(res));
+	max_lrworkers = atoi(PQgetvalue(res, 0, 0));
+	max_repslots = atoi(PQgetvalue(res, 1, 0));
+	max_wprocs = atoi(PQgetvalue(res, 2, 0));
+	if (strcmp(PQgetvalue(res, 3, 0), "") != 0)
+		primary_slot_name = pg_strdup(PQgetvalue(res, 3, 0));
+	pg_log_debug("subscriber: max_logical_replication_workers: %d", max_lrworkers);
+	pg_log_debug("subscriber: max_replication_slots: %d", max_repslots);
+	pg_log_debug("subscriber: max_worker_processes: %d", max_wprocs);
+	pg_log_debug("subscriber: primary_slot_name: %s", primary_slot_name);
+	if (max_repslots < num_dbs)
+		pg_log_error("subscriber requires %d replication slots, but only %d remain", num_dbs, max_repslots);
+		pg_log_error_hint("Consider increasing max_replication_slots to at least %d.", num_dbs);
+	if (max_lrworkers < num_dbs)
+		pg_log_error("subscriber requires %d logical replication workers, but only %d remain", num_dbs, max_lrworkers);
+		pg_log_error_hint("Consider increasing max_logical_replication_workers to at least %d.", num_dbs);
+	if (max_wprocs < num_dbs + 1)
+		pg_log_error("subscriber requires %d worker processes, but only %d remain", num_dbs + 1, max_wprocs);
+		pg_log_error_hint("Consider increasing max_worker_processes to at least %d.", num_dbs + 1);
+ * If recovery_timeout option is set, terminate abnormally without finishing
+ * the recovery process. By default, it waits forever.
+	int			timer = 0;
+		 * Bail out after recovery_timeout seconds if this option is set.
+		if (recovery_timeout > 0 && timer >= recovery_timeout)
+			pg_log_error("recovery timed out");
+		timer += WAIT_INTERVAL;
+		{"recovery-timeout", required_argument, NULL, 't'},
+		{"retain", no_argument, NULL, 'r'},
+			case 'r':
+				retain = true;
+			case 't':
+				recovery_timeout = atoi(optarg);
+	 * The standby server must be running. That's because some checks will be
+	 * done (is it ready for a logical replication setup?). After that, stop
+	 * the subscriber in preparation to modify some recovery parameters that
+	 * require a restart.
+		 * Check if the standby server is ready for logical replication.
+		if (!setup_subscriber(dbinfo))
+		 * Check if the primary server is ready for logical replication and
+		 * create the required objects for each database on publisher. This
+		 * step is here mainly because if we stop the standby we cannot verify
+		 * if the primary slot is in use. We could use an extra connection for
+		 * it but it doesn't seem worth.
+		if (!setup_publisher(dbinfo))
+		pg_log_info("standby is up and running");
+		pg_log_error("standby is not running");
+		pg_log_error_hint("Start the standby and try again.");
+	 * The log file is kept if retain option is specified or this tool does
+	 * not run successfully. Otherwise, log file is removed.
+	if (!retain)

---

From: "Euler Taveira" <[email-masked]>
Date: Thu, 25 Jan 2024 21:28:45 -0300

> Or, should we have a structure to store them?

It is a matter of style I would say. Check other client applications. Some of
them also use global variable. There are others that group options into a
struct. I would say that since it has a short lifetime, I don't think the
current style is harmful.

> feature, so it should be renamed to --check. Also, the process should exit earlier

It is extremely useful because (a) you have a physical replication setup and
don't know if it is prepared for logical replication, (b) check GUCs (is
max_wal_senders sufficient for this pg_subscriber command? Or is
max_replication_slots sufficient to setup the logical replication even though I
already have some used replication slots?), (c) connectivity and (d)
credentials.

> Currently, below items should be acceted.

Maybe PGDATA.

> I think it should be PG_LOG_INFO.

You need to specify multiple -v options.

> Can we combine verifications into two functions, e.g., check_primary() and check_standby/check_subscriber()?

I think v9 does it.

> should be created. The name should contain the timestamp.

The log file already contains the timestamp. Why?

> avoid to fail starting of logical replicaiton.

A broken physical replication *before* running this tool is its responsibility?
Hmm. We might add another check that can be noticed during dry run mode.

> are removed. Currently pg_resetwal does not raise ERROR at that time.

Again, dry run mode might provide a message for it.

> Removing it can reduce an inclusion of pidfile.h.

Are you suggesting another way to check if the standby is up and running?

> There are two places to stop the instance. Can you divide it into a function?

Yes.

> WAL records are surely reserved.

This comment needs to be updated. It was written at the time I was pursuing
base backup support too. It doesn't matter if you remove this transient
replication slot earlier because all of the replication slots created to the
subscriptions were created *before* the one for the consistent LSN. Hence, no
additional WAL retention due to this transient replication slot.

> This info should be in the function.

Ok.

> This can be divided into a function, like create_all_subscriptions().

Ok.

> Error message should be changed. I think this error means the standby has wrong primary_slot_name, right?

I refactored this code a bit but the message is the same. It detects 2 cases:
(a) you set primary_slot_name but you don't have a replication slot with the
same name and (b) a cannot-happen bug that provides > 1 rows. It is a broken
setup so maybe a hint saying so is enough.

> Sometimes the pid of pg_subscriber is referred. It can be stored as global variable.

I prefer to keep getpid() call.

> for (int i = 0; i < MAX; i++)

v9 does it.

> Some comments, docs, and outputs must be fixed when the name is changed.

Next patch.

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Fri, 26 Jan 2024 05:51:49 +0000

Dear Euler,

Thanks for updating the patch! Before reading yours, I wanted to reply some of comments.

For me, required amount of fixes are not so different from current one. How about
others?

Yeah, it is useful for verification purpose, so let's keep this option.
But I still think the naming should be "--check". Also, there are many
`if (!dry_run)` but most of them can be removed if the process exits earlier.
Thought?

Maybe PGDATA.

Sorry, I cannot follow this. Did you mean that the target data directory should
be able to be specified by PGDATA? OF so, +1.

Hmm. I felt the specification was bit strange...but at least it must be
described on the documentation. pg_dump.sgml has similar lines.

This comment assumed outputs by pg_subscriber were also recorded to a file.
In this case and if the file also has the same timestamp, I think they can be
gathered in the same place. No need if outputs are not recorded.

I thought that we should not generate any broken objects, but indeed, not sure
it is our scope. How do other think?

Running `pg_ctl stop` itself can detect whether the process has been still alive.
It would exit with 1 when the process is not there.

> WAL records are surely reserved.

I want to confirm the conclusion - will you remove the creation of a transient slot?

Also, not tested, I'm now considering that we can reuse the primary_conninfo value.
We are assuming that the target server is standby and the current upstream one will
convert to publisher. In this case, the connection string is already specified as
primary_conninfo so --publisher-conninfo may not be needed. The parameter does
not contain database name, so --databases is still needed. I imagine like:

1. Parse options
2. Turn on standby
3. Verify the standby
4. Turn off standby
5. Get primary_conninfo from standby
6. Connect to primary (concat of primary_conninfo and an option is used)
7. Verify the primary
...

How do you think?

Best Regards,
Hayato Kuroda
FUJITSU LIMITED
https://www.fujitsu.com/global/

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Fri, 26 Jan 2024 07:55:51 +0000

Dear Euler,

Again, thanks for updating the patch! There are my random comments for v9.

01.
I cannot find your replies for my comments#7 [1] but you reverted related changes.
I'm not sure you are still considering it or you decided not to include changes.
Can you clarify your opinion?
(It is needed because changes are huge so it quite affects other developments...)

02.
```

But source codes required `--recovery-timeout`. Please update either of them,

03.
```

Junwang pointed out to change here but the change was reverted [2]
Can you clarify your opinion as well?

04.
```
+/*

But this function verifies the source server. I felt they should be in the
different function.

05.
```
+/*
````

Actually, this function does not set up subscriber. It just verifies whether the
target can become a subscriber, right? If should be renamed.

06.
```

The registration of the cleanup function is too early. This sometimes triggers
a core-dump. E.g., 

```
$ pg_subscriber --publisher-conninfo --subscriber-conninfo 'user=postgres port=5432' --verbose --database 'postgres' --pgdata data_N2/
pg_subscriber: error: too many command-line arguments (first is "user=postgres port=5432")
pg_subscriber: hint: Try "pg_subscriber --help" for more information.
Segmentation fault (core dumped)

$ gdb ...
(gdb) bt
#0  cleanup_objects_atexit () at pg_subscriber.c:131
#1  0x00007fb982cffce9 in __run_exit_handlers () from /lib64/libc.so.6
#2  0x00007fb982cffd37 in exit () from /lib64/libc.so.6
#3  0x00000000004054e6 in main (argc=9, argv=0x7ffc59074158) at pg_subscriber.c:1500
(gdb) f 3
#3  0x00000000004054e6 in main (argc=9, argv=0x7ffc59074158) at pg_subscriber.c:1500
1500                    exit(1);
(gdb) list
1495            if (optind < argc)
1496            {
1497                    pg_log_error("too many command-line arguments (first is \"%s\")",
1498                                             argv[optind]);
1499                    pg_log_error_hint("Try \"%s --help\" for more information.", progname);
1500                    exit(1);
1501            }
1502 
1503            /*
1504             * Required arguments
```

I still think it should be done just before the creation of objects [3].

07.
Missing a removal of publications on the standby.

08.
Missing registration of LogicalRepInfo in the typedefs.list.

09
```
+ <refsynopsisdiv>
+  <cmdsynopsis>

Can you reply my comment#2 [4]? I think mandatory options should be written.

10.
Just to confirm - will you implement start_standby/stop_standby functions in next version?

[1]: https://www.postgresql.org/message-id/TY3PR01MB9889C362FF76102C88FA1C29F56F2%40TY3PR01MB9889.jpnprd01.prod.outlook.com
[2]: https://www.postgresql.org/message-id/CAEG8a3%2BwL_2R8n12BmRz7yBP3EBNdHDhmdgxQFA9vS%2BzPR%2B3Kw%40mail.gmail.com
[3]: https://www.postgresql.org/message-id/TY3PR01MB9889678E47B918F4D83A6FD8F57B2%40TY3PR01MB9889.jpnprd01.prod.outlook.com
[4]: https://www.postgresql.org/message-id/TY3PR01MB9889C362FF76102C88FA1C29F56F2%40TY3PR01MB9889.jpnprd01.prod.outlook.com

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

---

From: "Euler Taveira" <[email-masked]>
Date: Sun, 28 Jan 2024 22:10:45 -0300

> Again, thanks for updating the patch! There are my random comments for v9.

Thanks for checking v9. I already incorporated some of the points below into
the next patch. Give me a couple of hours to include some important points.

> (It is needed because changes are huge so it quite affects other developments...)

It is still on my list. As I said in a previous email I'm having a hard time
reviewing pieces from your 0002 patch because you include a bunch of things
into one patch.

> But source codes required `--recovery-timeout`. Please update either of them,

Oops. Fixed. My preference is --recovery-timeout because someone can decide to
include a --timeout option for this tool.

> Can you clarify your opinion as well?

I'll review the documentation once I fix the code. Since the tool includes the
*create* verb into its name, it seems strange use another verb (convert) in the
description. Maybe we should just remove the word *new* and a long description
explains the action is to turn the standby (physical replica) into a logical
replica.

> But this function verifies the source server. I felt they should be in the

I split setup_publisher() into check_publisher() and setup_publisher().

> target can become a subscriber, right? If should be renamed.

I renamed setup_subscriber() -> check_subscriber().

> The registration of the cleanup function is too early. This sometimes triggers

I forgot to apply the atexit() patch.

> Missing a removal of publications on the standby.

It was on my list to do. It will be in the next patch.

> Missing registration of LogicalRepInfo in the typedefs.list.

Done.

> Can you reply my comment#2 [4]? I think mandatory options should be written.

I included the mandatory options into the synopsis.

> Just to confirm - will you implement start_standby/stop_standby functions in next version?

It is still on my list.

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Mon, 29 Jan 2024 09:57:44 +0000

Dear Euler,

I understood that patches we posted were bad. Sorry for inconvenience. 
So I extracted changes only related with them. Can you review them and include
If it seems OK?

v10-0001: same as v9-0001.
0002: not related with our changes, but this fixed a small bug.
          The third argument of getopt_long was not correct.
0003: adds start_standby/stop_standby funcitons
0004: tries to refactor some structures.

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

---

From: "Euler Taveira" <[email-masked]>
Date: Mon, 29 Jan 2024 23:01:38 -0300

> the next patch. Give me a couple of hours to include some important points.

Here it is another patch that includes the following changes:

* rename the tool to pg_createsubscriber: it was the name with the most votes
  [1].
* fix recovery-timeout option [2]
* refactor: split setup_publisher into check_publisher (that contains only GUC
  checks) and setup_publisher (that does what the name suggests) [2]
* doc: verbose option can be specified multiple times [2]
* typedefs.list: add LogicalRepInfo [2]
* fix: register cleanup routine after the data structure (dbinfo) is assigned
  [2]
* doc: add mandatory options to synopsis [3]
* refactor: rename some options (such as publisher-conninfo to
  publisher-server) to use the same pattern as pg_rewind.
* feat: remove publications from subscriber [2]
* feat: use temporary replication slot to get consistent LSN [3]
* refactor: move subscriber setup to its own function
* refactor: stop standby server to its own function [2]
* refactor: start standby server to its own function [2]
* fix: getopt options [4]

There is a few open items in my list. I included v10-0002. I had already
included a refactor to include start/stop functions so I didn't include
v10-0003. I'll check v10-0004 tomorrow.

One open item that is worrying me is how to handle the pg_ctl timeout. This
patch does nothing and the user should use PGCTLTIMEOUT environment variable to
avoid that the execution is canceled after 60 seconds (default for pg_ctl).
Even if you set a high value, it might not be enough for cases like
time-delayed replica. Maybe pg_ctl should accept no timeout as --timeout
option. I'll include this caveat into the documentation but I'm afraid it is
not sufficient and we should provide a better way to handle this situation.

[1] https://www.postgresql.org/message-id/b315c7da-7ab1-4014-a2a9-8ab6ae26017c%40app.fastmail.com
[2] https://www.postgresql.org/message-id/TY3PR01MB98895A551923953B3DA3C7C8F5792%40TY3PR01MB9889.jpnprd01.prod.outlook.com
[3] https://www.postgresql.org/message-id/[email-masked]
[4] https://www.postgresql.org/message-id/TY3PR01MB98891E7735141FE8760CEC4AF57E2%40TY3PR01MB9889.jpnprd01.prod.outlook.com

--
Euler Taveira
EDB   https://www.enterprisedb.com/
From 67c6e63cf34885bdd687c06e1e071d15f42cdf2a Mon Sep 17 00:00:00 2001
From: Euler Taveira <[email-masked]>
Date: Mon, 5 Jun 2023 14:39:40 -0400
Subject: [PATCH v11] Creates a new logical replica from a standby server

A new tool called pg_createsubscriber can convert a physical replica
into a logical replica. It runs on the target server and should be able
to connect to the source server (publisher) and the target server
(subscriber).

subscription for each specified database on the target server.  Stop the
target server. Change the system identifier from the target server.

Depending on your workload and database size, creating a logical replica
couldn't be an option due to resource constraints (WAL backlog should be
available until all table data is synchronized). The initial data copy
and the replication progress tends to be faster on a physical replica.
The purpose of this tool is to speed up a logical replica setup.
---
 doc/src/sgml/ref/allfiles.sgml                |    1 +
 doc/src/sgml/ref/pg_createsubscriber.sgml     |  322 +++
 doc/src/sgml/reference.sgml                   |    1 +
 src/bin/pg_basebackup/.gitignore              |    1 +
 src/bin/pg_basebackup/Makefile                |    8 +-
 src/bin/pg_basebackup/meson.build             |   19 +
 src/bin/pg_basebackup/pg_createsubscriber.c   | 1852 +++++++++++++++++
 .../t/040_pg_createsubscriber.pl              |   44 +
 .../t/041_pg_createsubscriber_standby.pl      |  139 ++
 src/tools/pgindent/typedefs.list              |    1 +
 10 files changed, 2387 insertions(+), 1 deletion(-)
 create mode 100644 doc/src/sgml/ref/pg_createsubscriber.sgml
 create mode 100644 src/bin/pg_basebackup/pg_createsubscriber.c
 create mode 100644 src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
 create mode 100644 src/bin/pg_basebackup/t/041_pg_createsubscriber_standby.pl

diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index 4a42999b18..a2b5eea0e0 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -214,6 +214,7 @@ Complete list of usable sgml source files in this directory.
 <!ENTITY pgResetwal         SYSTEM "pg_resetwal.sgml">
 <!ENTITY pgRestore          SYSTEM "pg_restore.sgml">
 <!ENTITY pgRewind           SYSTEM "pg_rewind.sgml">
+<!ENTITY pgCreateSubscriber SYSTEM "pg_createsubscriber.sgml">
 <!ENTITY pgVerifyBackup     SYSTEM "pg_verifybackup.sgml">
 <!ENTITY pgtestfsync        SYSTEM "pgtestfsync.sgml">
 <!ENTITY pgtesttiming       SYSTEM "pgtesttiming.sgml">
diff --git a/doc/src/sgml/ref/pg_createsubscriber.sgml b/doc/src/sgml/ref/pg_createsubscriber.sgml
new file mode 100644
index 0000000000..1c78ff92e0
--- /dev/null
+++ b/doc/src/sgml/ref/pg_createsubscriber.sgml
@@ -0,0 +1,322 @@
+<!--
+doc/src/sgml/ref/pg_createsubscriber.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="app-pgcreatesubscriber">
+ <indexterm zone="app-pgcreatesubscriber">
+  <primary>pg_createsubscriber</primary>
+ </indexterm>
+
+ <refmeta>
+  <refentrytitle><application>pg_createsubscriber</application></refentrytitle>
+  <manvolnum>1</manvolnum>
+  <refmiscinfo>Application</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+  <refname>pg_createsubscriber</refname>
+  <refpurpose>convert a physical replica into a new logical replica</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+  <cmdsynopsis>
+   <command>pg_createsubscriber</command>
+   <arg rep="repeat"><replaceable>option</replaceable></arg>
+   <group choice="plain">
+    <group choice="req">
+     <arg choice="plain"><option>-D</option> </arg>
+     <arg choice="plain"><option>--pgdata</option></arg>
+    </group>
+    <replaceable>datadir</replaceable>
+    <group choice="req">
+     <arg choice="plain"><option>-P</option></arg>
+     <arg choice="plain"><option>--publisher-server</option></arg>
+    </group>
+    <replaceable>connstr</replaceable>
+    <group choice="req">
+     <arg choice="plain"><option>-S</option></arg>
+     <arg choice="plain"><option>--subscriber-server</option></arg>
+    </group>
+    <replaceable>connstr</replaceable>
+    <group choice="req">
+     <arg choice="plain"><option>-d</option></arg>
+     <arg choice="plain"><option>--database</option></arg>
+    </group>
+    <replaceable>dbname</replaceable>
+   </group>
+  </cmdsynopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+  <title>Description</title>
+  <para>
+   <application>pg_createsubscriber</application> takes the publisher and subscriber
+   connection strings, a cluster directory from a physical replica and a list of
+   database names and it sets up a new logical replica using the physical
+   recovery process.
+  </para>
+
+  <para>
+   The <application>pg_createsubscriber</application> should be run at the target
+   server. The source server (known as publisher server) should accept logical
+   replication connections from the target server (known as subscriber server).
+   The target server should accept local logical replication connection.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Options</title>
+
+   <para>
+    <application>pg_createsubscriber</application> accepts the following
+    command-line arguments:
+
+    <variablelist>
+     <varlistentry>
+      <term><option>-D <replaceable class="parameter">directory</replaceable></option></term>
+      <term><option>--pgdata=<replaceable class="parameter">directory</replaceable></option></term>
+      <listitem>
+       <para>
+        The target directory that contains a cluster directory from a physical
+        replica.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>-P  <replaceable class="parameter">connstr</replaceable></option></term>
+      <term><option>--publisher-server=<replaceable class="parameter">connstr</replaceable></option></term>
+      <listitem>
+       <para>
+        The connection string to the publisher. For details see <xref linkend="libpq-connstring"/>.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>-S <replaceable class="parameter">connstr</replaceable></option></term>
+      <term><option>--subscriber-server=<replaceable class="parameter">connstr</replaceable></option></term>
+      <listitem>
+       <para>
+        The connection string to the subscriber. For details see <xref linkend="libpq-connstring"/>.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>-d <replaceable class="parameter">dbname</replaceable></option></term>
+      <term><option>--database=<replaceable class="parameter">dbname</replaceable></option></term>
+      <listitem>
+       <para>
+        The database name to create the subscription. Multiple databases can be
+        selected by writing multiple <option>-d</option> switches.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>-n</option></term>
+      <term><option>--dry-run</option></term>
+      <listitem>
+       <para>
+        Do everything except actually modifying the target directory.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>-r</option></term>
+      <term><option>--retain</option></term>
+      <listitem>
+       <para>
+        Retain log file even after successful completion.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+       <term><option>-t <replaceable class="parameter">seconds</replaceable></option></term>
+       <term><option>--recovery-timeout=<replaceable class="parameter">seconds</replaceable></option></term>
+       <listitem>
+       <para>
+        The maximum number of seconds to wait for recovery to end. Setting to 0
+        disables. The default is 0.
+       </para>
+       </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>-v</option></term>
+      <term><option>--verbose</option></term>
+      <listitem>
+       <para>
+        Enables verbose mode. This will cause
+        <application>pg_createsubscriber</application> to output progress messages
+        and detailed information about each step to standard error.
+        Repeating the option causes additional debug-level messages to appear on
+        standard error.
+       </para>
+      </listitem>
+     </varlistentry>
+    </variablelist>
+   </para>
+
+   <para>
+    Other options are also available:
+
+    <variablelist>
+     <varlistentry>
+       <term><option>-V</option></term>
+       <term><option>--version</option></term>
+       <listitem>
+       <para>
+       Print the <application>pg_createsubscriber</application> version and exit.
+       </para>
+       </listitem>
+     </varlistentry>
+
+     <varlistentry>
+       <term><option>-?</option></term>
+       <term><option>--help</option></term>
+       <listitem>
+       <para>
+       Show help about <application>pg_createsubscriber</application> command
+       line arguments, and exit.
+       </para>
+       </listitem>
+     </varlistentry>
+
+    </variablelist>
+   </para>
+
+ </refsect1>
+
+ <refsect1>
+  <title>Notes</title>
+
+  <para>
+   The transformation proceeds in the following steps:
+  </para>
+
+  <procedure>
+   <step>
+    <para>
+     <application>pg_createsubscriber</application> checks if the given target data
+     directory has the same system identifier than the source data directory.
+     Since it uses the recovery process as one of the steps, it starts the
+     target server as a replica from the source server. If the system
+     identifier is not the same, <application>pg_createsubscriber</application> will
+     terminate with an error.
+    </para>
+   </step>
+
+   <step>
+    <para>
+     <application>pg_createsubscriber</application> checks if the target data
+     directory is used by a physical replica. Stop the physical replica if it is
+     running. One of the next steps is to add some recovery parameters that
+     requires a server start. This step avoids an error.
+    </para>
+   </step>
+
+   <step>
+    <para>
+     <application>pg_createsubscriber</application> creates one replication slot for
+     each specified database on the source server. The replication slot name
+     contains a <literal>pg_createsubscriber</literal> prefix. These replication
+     slots will be used by the subscriptions in a future step.  A temporary
+     replication slot is used to get a consistent start location. This
+     consistent LSN will be used as a stopping point in the <xref
+     linkend="guc-recovery-target-lsn"/> parameter and by the
+     subscriptions as a replication starting point. It guarantees that no
+     transaction will be lost.
+    </para>
+   </step>
+
+   <step>
+    <para>
+     <application>pg_createsubscriber</application> writes recovery parameters into
+     the target data directory and start the target server. It specifies a LSN
+     (consistent LSN that was obtained in the previous step) of write-ahead
+     log location up to which recovery will proceed. It also specifies
+     <literal>promote</literal> as the action that the server should take once
+     the recovery target is reached. This step finishes once the server ends
+     standby mode and is accepting read-write operations.
+    </para>
+   </step>
+
+   <step>
+    <para>
+     Next, <application>pg_createsubscriber</application> creates one publication
+     for each specified database on the source server. Each publication
+     replicates changes for all tables in the database. The publication name
+     contains a <literal>pg_createsubscriber</literal> prefix. These publication
+     will be used by a corresponding subscription in a next step.
+    </para>
+   </step>
+
+   <step>
+    <para>
+     <application>pg_createsubscriber</application> creates one subscription for
+     each specified database on the target server. Each subscription name
+     contains a <literal>pg_createsubscriber</literal> prefix. The replication slot
+     name is identical to the subscription name. It does not copy existing data
+     from the source server. It does not create a replication slot. Instead, it
+     uses the replication slot that was created in a previous step. The
+     subscription is created but it is not enabled yet. The reason is the
+     replication progress must be set to the consistent LSN but replication
+     origin name contains the subscription oid in its name. Hence, the
+     subscription will be enabled in a separate step.
+    </para>
+   </step>
+
+   <step>
+    <para>
+     <application>pg_createsubscriber</application> sets the replication progress to
+     the consistent LSN that was obtained in a previous step. When the target
+     server started the recovery process, it caught up to the consistent LSN.
+     This is the exact LSN to be used as a initial location for each
+     subscription.
+    </para>
+   </step>
+
+   <step>
+    <para>
+     Finally, <application>pg_createsubscriber</application> enables the subscription
+     for each specified database on the target server. The subscription starts
+     streaming from the consistent LSN.
+    </para>
+   </step>
+
+   <step>
+    <para>
+     <application>pg_createsubscriber</application> stops the target server to change
+     its system identifier.
+    </para>
+   </step>
+  </procedure>
+ </refsect1>
+
+ <refsect1>
+  <title>Examples</title>
+
+  <para>
+   To create a logical replica for databases <literal>hr</literal> and
+   <literal>finance</literal> from a physical replica at <literal>foo</literal>:
+<screen>
+<prompt>$</prompt> <userinput>pg_createsubscriber -D /usr/local/pgsql/data -P "host=foo" -S "host=localhost" -d hr -d finance</userinput>
+</screen>
+  </para>
+
+ </refsect1>
+
+ <refsect1>
+  <title>See Also</title>
+
+  <simplelist type="inline">
+   <member><xref linkend="app-pgbasebackup"/></member>
+  </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index aa94f6adf6..c5edd244ef 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -285,6 +285,7 @@
    &pgCtl;
    &pgResetwal;
    &pgRewind;
+   &pgCreateSubscriber;
    &pgtestfsync;
    &pgtesttiming;
    &pgupgrade;
diff --git a/src/bin/pg_basebackup/.gitignore b/src/bin/pg_basebackup/.gitignore
index 26048bdbd8..b3a6f5a2fe 100644
--- a/src/bin/pg_basebackup/.gitignore
+++ b/src/bin/pg_basebackup/.gitignore
@@ -1,5 +1,6 @@
 /pg_basebackup
 /pg_receivewal
 /pg_recvlogical
+/pg_createsubscriber
 
 /tmp_check/
index abfb6440ec..ded434b683 100644
 
-all: pg_basebackup pg_receivewal pg_recvlogical
+all: pg_basebackup pg_receivewal pg_recvlogical pg_createsubscriber

+pg_createsubscriber: $(WIN32RES) pg_createsubscriber.o | submake-libpq submake-libpgport submake-libpgfeutils
+	$(CC) $(CFLAGS) $^ $(LDFLAGS) $(LDFLAGS_EX) $(LIBS) -o $@$(X)
+
 install: all installdirs
 	$(INSTALL_PROGRAM) pg_basebackup$(X) '$(DESTDIR)$(bindir)/pg_basebackup$(X)'
 	$(INSTALL_PROGRAM) pg_receivewal$(X) '$(DESTDIR)$(bindir)/pg_receivewal$(X)'
 	$(INSTALL_PROGRAM) pg_recvlogical$(X) '$(DESTDIR)$(bindir)/pg_recvlogical$(X)'
+	$(INSTALL_PROGRAM) pg_createsubscriber$(X) '$(DESTDIR)$(bindir)/pg_createsubscriber$(X)'
 
 installdirs:
+	rm -f '$(DESTDIR)$(bindir)/pg_createsubscriber$(X)'
 
 clean distclean:
 	rm -f pg_basebackup$(X) pg_receivewal$(X) pg_recvlogical$(X) \
 		$(BBOBJS) pg_receivewal.o pg_recvlogical.o \
+		pg_createsubscriber$(X) pg_createsubscriber.o \
 		$(OBJS)
 	rm -rf tmp_check
 
index f7e60e6670..345a2d6fcd 100644
 
+pg_createsubscriber_sources = files(
+  'pg_createsubscriber.c'
+)
+
+  pg_createsubscriber_sources += rc_bin_gen.process(win32ver_rc, extra_args: [
+	'--NAME', 'pg_createsubscriber',
+	'--FILEDESC', 'pg_createsubscriber - create a new logical replica from a standby server',])
+pg_createsubscriber = executable('pg_createsubscriber',
+  pg_createsubscriber_sources,
+bin_targets += pg_createsubscriber
+      't/040_pg_createsubscriber.pl',
+      't/041_pg_createsubscriber_standby.pl',
diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c
index 0000000000..478560b3e4
+++ b/src/bin/pg_basebackup/pg_createsubscriber.c
@@ -0,0 +1,1852 @@
+ * pg_createsubscriber.c
+ *		src/bin/pg_basebackup/pg_createsubscriber.c
+#define	PGS_OUTPUT_DIR	"pg_createsubscriber_output.d"
+static bool check_publisher(LogicalRepInfo *dbinfo);
+static bool check_subscriber(LogicalRepInfo *dbinfo);
+static bool setup_subscriber(LogicalRepInfo *dbinfo, const char *consistent_lsn);
+static char *server_logfile_name(const char *datadir);
+static void start_standby_server(const char *pg_ctl_path, const char *datadir, const char *logfile);
+static void stop_standby_server(const char *pg_ctl_path, const char *datadir);
+ * Cleanup objects that were created by pg_createsubscriber if there is an error.
+	printf(_(" -P, --publisher-server=CONNSTR      publisher connection string\n"));
+	printf(_(" -S, --subscriber-server=CONNSTR     subscriber connection string\n"));
+ * Create the publications and replication slots in preparation for logical
+ * replication.
+		 * 1. This current schema uses a maximum of 31 characters (20 + 10 +
+		snprintf(pubname, sizeof(pubname), "pg_createsubscriber_%u", dbinfo[i].oid);
+		 * NAMEDATALEN - 1. This current schema uses a maximum of 42
+		 * characters (20 + 10 + 1 + 10 + '\0'). PID is included to reduce the
+		 * probability of collision. By default, subscription name is used as
+		 * replication slot name.
+				 "pg_createsubscriber_%u_%d",
+ * Is the primary server ready for logical replication?
+check_publisher(LogicalRepInfo *dbinfo)
+ * Is the standby server ready for logical replication?
+check_subscriber(LogicalRepInfo *dbinfo)
+ * Create the subscriptions, adjust the initial location for logical replication and
+ * enable the subscriptions. That's the last step for logical repliation setup.
+setup_subscriber(LogicalRepInfo *dbinfo, const char *consistent_lsn)
+		 * Since the publication was created before the consistent LSN, it is
+		 * available on the subscriber when the physical replica is promoted.
+		 * Remove publications from the subscriber because it has no use.
+		snprintf(slot_name, NAMEDATALEN, "pg_createsubscriber_%d_startpoint",
+		appendPQExpBufferStr(str, " TEMPORARY");
+	if (!transient_replslot)
+server_logfile_name(const char *datadir)
+	char	   *filename;
+	filename = (char *) pg_malloc0(MAXPGPATH);
+	len = snprintf(filename, MAXPGPATH, "%s/%s/server_start_%s.log", datadir, PGS_OUTPUT_DIR, timebuf);
+	return filename;
+start_standby_server(const char *pg_ctl_path, const char *datadir, const char *logfile)
+	pg_ctl_cmd = psprintf("\"%s\" start -D \"%s\" -s -l \"%s\"", pg_ctl_path, datadir, logfile);
+stop_standby_server(const char *pg_ctl_path, const char *datadir)
+	pg_ctl_cmd = psprintf("\"%s\" stop -D \"%s\" -s", pg_ctl_path, datadir);
+			stop_standby_server(pg_ctl_path, subscriber_dir);
+		 * use it. A previous run of pg_createsubscriber must have created
+		 * this publication. Bail out.
+			 * choose a name with pg_createsubscriber_ prefix followed by the
+			 * exact database oid in which puballtables is false.
+		{"publisher-server", required_argument, NULL, 'P'},
+		{"subscriber-server", required_argument, NULL, 'S'},
+	char		temp_replslot[NAMEDATALEN] = {0};
+	set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pg_createsubscriber"));
+			puts("pg_createsubscriber (PostgreSQL) " PG_VERSION);
+	while ((c = getopt_long(argc, argv, "D:P:S:d:nrt:v",
+	/* Register a function to clean up objects in case of failure. */
+	server_start_log = server_logfile_name(subscriber_dir);
+		if (!check_subscriber(dbinfo))
+		 * Check if the primary server is ready for logical replication. This
+		 * routine checks if a replication slot is in use on primary so it
+		 * relies on check_subscriber() to obtain the primary_slot_name.
+		 * That's why it is called after it.
+		if (!check_publisher(dbinfo))
+		 * Create the required objects for each database on publisher. This
+		/* Stop the standby server. */
+		stop_standby_server(pg_ctl_path, subscriber_dir);
+	 * Create a temporary logical replication slot to get a consistent LSN.
+	 * replication slots. It is ok to use a temporary replication slot here
+	 * because it will have a short lifetime and it is only used as a mark to
+	 * start the logical replication.
+	start_standby_server(pg_ctl_path, subscriber_dir, server_start_log);
+	 * Create the subscription for each database on subscriber. It does not
+	 * enable it immediately because it needs to adjust the logical
+	 * replication start point to the LSN reported by consistent_lsn (see
+	 * set_replication_progress). It also cleans up publications created by
+	 * this tool and replication to the standby.
+	if (!setup_subscriber(dbinfo, consistent_lsn))
+	 * If the primary_slot_name exists on primary, drop it.
+	 * eventually drops this replication slot later.
+	stop_standby_server(pg_ctl_path, subscriber_dir);
diff --git a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
index 0000000000..0f02b1bfac
+++ b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
+# Test checking options of pg_createsubscriber.
+program_help_ok('pg_createsubscriber');
+program_version_ok('pg_createsubscriber');
+program_options_handling_ok('pg_createsubscriber');
+command_fails(['pg_createsubscriber'],
+		'pg_createsubscriber',
+		'pg_createsubscriber',
+		'--publisher-server', 'dbname=postgres'
+		'pg_createsubscriber',
+		'--publisher-server', 'dbname=postgres',
+		'--subscriber-server', 'dbname=postgres'
diff --git a/src/bin/pg_basebackup/t/041_pg_createsubscriber_standby.pl b/src/bin/pg_basebackup/t/041_pg_createsubscriber_standby.pl
index 0000000000..534bc53a76
+++ b/src/bin/pg_basebackup/t/041_pg_createsubscriber_standby.pl
+# Run pg_createsubscriber on about-to-fail node F
+		'pg_createsubscriber', '--verbose',
+		'--publisher-server', $node_p->connstr('pg1'),
+		'--subscriber-server', $node_f->connstr('pg1'),
+		'pg_createsubscriber', '--verbose', '--dry-run',
+		'--publisher-server', $node_p->connstr('pg1'),
+		'--subscriber-server', $node_s->connstr('pg1'),
+	'run pg_createsubscriber --dry-run on node S');
+# Run pg_createsubscriber on node S
+		'pg_createsubscriber', '--verbose',
+		'--publisher-server', $node_p->connstr('pg1'),
+		'--subscriber-server', $node_s->connstr('pg1'),
+	'run pg_createsubscriber on node S');
+	SELECT subname FROM pg_subscription WHERE subname ~ '^pg_createsubscriber_'
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 90b37b919c..2c5725e18d 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1505,6 +1505,7 @@ LogicalRepBeginData
 LogicalRepCommitData
 LogicalRepCommitPreparedTxnData
 LogicalRepCtxStruct
+LogicalRepInfo
 LogicalRepMsgType
 LogicalRepPartMapEntry
 LogicalRepPreparedTxnData
-- 
2.30.2

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Tue, 30 Jan 2024 09:26:59 +0000

Dear Euler,

Thanks for updating the patch!

> not sufficient and we should provide a better way to handle this situation.

I felt you might be confused a bit. Even if the recovery_min_apply_delay is set,
e.g., 10h., the pg_ctl can start and stop the server. This is because the
walreceiver serialize changes as soon as they received. The delay is done by the
startup process. There are no unflushed data, so server instance can be turned off.
If you meant the combination of recovery-timeout and time-delayed replica, yes,
it would be likely to occur. But in the case, using like --no-timeout option is
dangerous. I think we should overwrite recovery_min_apply_delay to zero. Thought?

Below part contains my comments for v11-0001. Note that the ordering is random.

01. doc
```
    <group choice="req">
     <arg choice="plain"><option>-D</option> </arg>
     <arg choice="plain"><option>--pgdata</option></arg>
    </group>
```

According to other documentation like pg_upgrade, we do not write both longer
and shorter options in the synopsis section.

02. doc
```
  <para>
   <application>pg_createsubscriber</application> takes the publisher and subscriber
   connection strings, a cluster directory from a physical replica and a list of
   database names and it sets up a new logical replica using the physical
   recovery process.
  </para>

```

I found that you did not include my suggestion without saying [1]. Do you dislike
the comment or still considering?

03. doc
```
      <term><option>-P  <replaceable class="parameter">connstr</replaceable></option></term>
```

Too many blank after -P.

04. doc
```
    <para>
     <application>pg_createsubscriber</application> checks if the target data
     directory is used by a physical replica. Stop the physical replica if it is
     running. One of the next steps is to add some recovery parameters that
     requires a server start. This step avoids an error.
    </para>
```

I think doc is not updated. Currently (not sure it is good) the physical replica
must be running before doing pg_createsubscriber.

05. doc
```
     each specified database on the source server. The replication slot name
     contains a <literal>pg_createsubscriber</literal> prefix. These replication
     slots will be used by the subscriptions in a future step.  A temporary
```

According to the "Replication Slot Management" subsection in logical-replication.sgml,
the format of names should be described expressly, e.g.:

```
These replication slots have generated names:"pg_createsubscriber_%u_%d" (parameters: Subscription oid, Process id pid).
```

Publications, a temporary slot, and subscriptions should be described as well.

06. doc
```
     Next, <application>pg_createsubscriber</application> creates one publication
     for each specified database on the source server. Each publication
     replicates changes for all tables in the database. The publication name

```

Missing update. Publications are created before creating replication slots.

07. general
I think there are some commenting conversions in PG, but this file breaks it.

* Comments must be one-line as much as possible
* Periods must not be added when the comment is one-line.
* Initial character must be capital.

08. general
Some pg_log_error() + exit(1) can be replaced with pg_fatal().

09. LogicalRepInfo
```

As I posted in comment#8[2], I don't think it is "subscription connection". Also,
"for logical replication" is bit misreading because it would not be passed to
workers.

10. get_base_conninfo
```
static char *
get_base_conninfo(char *conninfo, char *dbname, const char *noderole)
...
		/*
		 * If --database option is not provided, try to obtain the dbname from
		 * the publisher conninfo. If dbname parameter is not available, error
		 * out.
		 */

```

I'm not sure getting dbname from the conninfo improves user-experience. I felt
it may trigger an unintended targeting.
(I still think the publisher-server should be removed)

11. check_data_directory
```
/*
 * Is it a cluster directory? These are preliminary checks. It is far from
 * making an accurate check. If it is not a clone from the publisher, it will
 * eventually fail in a future step.
 */
static bool
check_data_directory(const char *datadir)
```

We shoud also check whether pg_createsubscriber can create a file and a directory.
GetDataDirectoryCreatePerm() verifies it.

12. main
```
	/* Register a function to clean up objects in case of failure. */
	atexit(cleanup_objects_atexit);
```

According to the manpage, callback functions would not be called when it exits
due to signals:

> Functions  registered  using atexit() (and on_exit(3)) are not called if a
> process terminates abnormally because of the delivery of a signal.

Do you have a good way to handle the case? One solution is to output created
objects in any log level, but the consideration may be too much. Thought?

13, main
```
	/*
	 * Create a temporary logical replication slot to get a consistent LSN.
```

Just to clarify - I still think the process exits before here in case of dry run.
In case of pg_resetwal, the process exits before doing actual works like
RewriteControlFile().

14. main
```
	 * XXX we might not fail here. Instead, we provide a warning so the user
	 * eventually drops this replication slot later.
```

But there are possibilities to exit(1) in drop_replication_slot(). Is it acceptable?

15. wait_for_end_recovery
```
		/*
		 * Bail out after recovery_timeout seconds if this option is set.
		 */
		if (recovery_timeout > 0 && timer >= recovery_timeout)
```

Hmm, IIUC, it should be enabled by default [3]. Do you have anything in your mind?

16. main
```
	/*
	 * Create the subscription for each database on subscriber. It does not
	 * enable it immediately because it needs to adjust the logical
	 * replication start point to the LSN reported by consistent_lsn (see
	 * set_replication_progress). It also cleans up publications created by
	 * this tool and replication to the standby.
	 */
	if (!setup_subscriber(dbinfo, consistent_lsn))
```

Subscriptions would be created and replication origin would be moved forward here,
but latter one can be done only by the superuser. I felt that this should be
checked in check_subscriber().

17. main
```
	/*
	 * Change system identifier.
	 */
	modify_sysid(pg_resetwal_path, subscriber_dir);
```

Even if I executed without -v option, an output from pg_resetwal command appears.
It seems bit strange.

```
$ pg_createsubscriber -D data_N2/ -P "port=5431 user=postgres" -S "port=5432 user=postgres" -d postgres
Write-ahead log reset
$
```

[1]: https://www.postgresql.org/message-id/TY3PR01MB9889C362FF76102C88FA1C29F56F2%40TY3PR01MB9889.jpnprd01.prod.outlook.com
[2]: https://www.postgresql.org/message-id/TY3PR01MB9889C362FF76102C88FA1C29F56F2%40TY3PR01MB9889.jpnprd01.prod.outlook.com
[3]: https://www.postgresql.org/message-id/CAA4eK1JRgnhv_ySzuFjN7UaX9qxz5Hqcwew7Fk%3D%2BAbJbu0Kd9w%40mail.gmail.com

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Wed, 31 Jan 2024 12:52:23 +0000

Dear Euler,

I extracted some review comments which may require many efforts. I hope this makes them
easy to review.

0001: not changed from yours.
0002: avoid to use replication connections. Source: comment #3[1]
0003: Remove -P option and use primary_conninfo instead. Source: [2]
0004: Exit earlier when dry_run is specified. Source: [3]
0005: Refactor data structures. Source: [4]

[1]: https://www.postgresql.org/message-id/TY3PR01MB9889593399165B9A04106741F5662%40TY3PR01MB9889.jpnprd01.prod.outlook.com
[2]: https://www.postgresql.org/message-id/TY3PR01MB98897C85700C6DF942D2D0A3F5792%40TY3PR01MB9889.jpnprd01.prod.outlook.com
[3]: https://www.postgresql.org/message-id/TY3PR01MB98897C85700C6DF942D2D0A3F5792%40TY3PR01MB9889.jpnprd01.prod.outlook.com
[4]: https://www.postgresql.org/message-id/TY3PR01MB9889C362FF76102C88FA1C29F56F2%40TY3PR01MB9889.jpnprd01.prod.outlook.com

Best Regards,
Hayato Kuroda
FUJITSU LIMITED
https://www.fujitsu.com/

---

From: Fabrízio de Royes Mello <[email-masked]>
Date: Wed, 31 Jan 2024 10:03:54 -0300

> to me.  Maybe there are other ideas.

I've mentioned it upthread because of this pet project [1] that is one of
the motivations behind upstream this facility.

[1] https://github.com/fabriziomello/pg_create_subscriber

-- 
Fabrízio de Royes Mello

---

From: Fabrízio de Royes Mello <[email-masked]>
Date: Wed, 31 Jan 2024 11:25:06 -0300

On Wed, Jan 31, 2024 at 9:52 AM Hayato Kuroda (Fujitsu) <
[email-masked]> wrote:
> [1]:
https://www.postgresql.org/message-id/TY3PR01MB9889593399165B9A04106741F5662%40TY3PR01MB9889.jpnprd01.prod.outlook.com
> [2]:
https://www.postgresql.org/message-id/TY3PR01MB98897C85700C6DF942D2D0A3F5792%40TY3PR01MB9889.jpnprd01.prod.outlook.com
> [3]:
https://www.postgresql.org/message-id/TY3PR01MB98897C85700C6DF942D2D0A3F5792%40TY3PR01MB9889.jpnprd01.prod.outlook.com
> [4]:
https://www.postgresql.org/message-id/TY3PR01MB9889C362FF76102C88FA1C29F56F2%40TY3PR01MB9889.jpnprd01.prod.outlook.com

Hey folks,

Jumping into this a bit late here... I'm trying a simple
pg_createsubscriber but getting an error:

~/pgsql took 19s
✦ ➜ pg_createsubscriber -d fabrizio -r -D /tmp/replica5434 -S 'host=/tmp
port=5434'
pg_createsubscriber: error: could not create subscription
"pg_createsubscriber_16384_695617" on database "fabrizio": ERROR:  syntax
error at or near "/"
LINE 1: ..._16384_695617 CONNECTION 'user=fabrizio passfile='/home/fabr...
                                                             ^
pg_createsubscriber: error: could not drop replication slot
"pg_createsubscriber_16384_695617" on database "fabrizio":
pg_createsubscriber: error: could not drop replication slot
"pg_subscriber_695617_startpoint" on database "fabrizio": ERROR:
 replication slot "pg_subscriber_695617_startpoint" does not exist

And the LOG contains the following:

~/pgsql took 12s
✦ ➜ cat
/tmp/replica5434/pg_createsubscriber_output.d/server_start_20240131T110318.730.log

2024-01-31 11:03:19.138 -03 [695632] LOG:  starting PostgreSQL 17devel on
x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0,
64-bit
2024-01-31 11:03:19.138 -03 [695632] LOG:  listening on IPv6 address "::1",
port 5434
2024-01-31 11:03:19.138 -03 [695632] LOG:  listening on IPv4 address
"127.0.0.1", port 5434
2024-01-31 11:03:19.158 -03 [695632] LOG:  listening on Unix socket
"/tmp/.s.PGSQL.5434"
2024-01-31 11:03:19.179 -03 [695645] LOG:  database system was shut down in
recovery at 2024-01-31 11:03:18 -03
2024-01-31 11:03:19.180 -03 [695645] LOG:  entering standby mode
2024-01-31 11:03:19.192 -03 [695645] LOG:  redo starts at 0/4000028
2024-01-31 11:03:19.198 -03 [695645] LOG:  consistent recovery state
reached at 0/504DB08
2024-01-31 11:03:19.198 -03 [695645] LOG:  invalid record length at
0/504DB08: expected at least 24, got 0
2024-01-31 11:03:19.198 -03 [695632] LOG:  database system is ready to
accept read-only connections
2024-01-31 11:03:19.215 -03 [695646] LOG:  started streaming WAL from
primary at 0/5000000 on timeline 1
2024-01-31 11:03:29.587 -03 [695645] LOG:  recovery stopping after WAL
location (LSN) "0/504F260"
2024-01-31 11:03:29.587 -03 [695645] LOG:  redo done at 0/504F260 system
usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 10.39 s
2024-01-31 11:03:29.587 -03 [695645] LOG:  last completed transaction was
at log time 2024-01-31 11:03:18.761544-03
2024-01-31 11:03:29.587 -03 [695646] FATAL:  terminating walreceiver
process due to administrator command
2024-01-31 11:03:29.598 -03 [695645] LOG:  selected new timeline ID: 2
2024-01-31 11:03:29.680 -03 [695645] LOG:  archive recovery complete
2024-01-31 11:03:29.690 -03 [695643] LOG:  checkpoint starting:
end-of-recovery immediate wait
2024-01-31 11:03:29.795 -03 [695643] LOG:  checkpoint complete: wrote 51
buffers (0.3%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.021 s,
sync=0.034 s, total=0.115 s; sync files=17, longest=0.011 s, average=0.002
s; distance=16700 kB, estimate=16700 kB; lsn=0/504F298, redo lsn=0/504F298
2024-01-31 11:03:29.805 -03 [695632] LOG:  database system is ready to
accept connections
2024-01-31 11:03:30.332 -03 [695658] ERROR:  syntax error at or near "/" at
character 90
2024-01-31 11:03:30.332 -03 [695658] STATEMENT:  CREATE SUBSCRIPTION
pg_createsubscriber_16384_695617 CONNECTION 'user=fabrizio
passfile='/home/fabrizio/.pgpass' channel_binding=prefer host=localhost
port=5432 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1
ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres
gssdelegation=0 target_session_attrs=any load_balance_hosts=disable
dbname=fabrizio' PUBLICATION pg_createsubscriber_16384 WITH (create_slot =
false, copy_data = false, enabled = false)

Seems we need to escape connection params similar we do in dblink [1]

Regards,

[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/dblink/dblink.c;h=19a362526d21dff5d8b1cdc68b15afebe7d40249;hb=HEAD#l2882

-- 
Fabrízio de Royes Mello

---

From: "Euler Taveira" <[email-masked]>
Date: Wed, 31 Jan 2024 11:34:57 -0300

On Wed, Jan 31, 2024, at 11:25 AM, Fabrízio de Royes Mello wrote:
> Jumping into this a bit late here... I'm trying a simple pg_createsubscriber but getting an error:

Try v11. It seems v12-0002 is not correct.

> Seems we need to escape connection params similar we do in dblink [1]

I think it is a consequence of v12-0003. I didn't review v12 yet but although I
have added a comment saying it might be possible to use primary_conninfo, I'm
not 100% convinced that's the right direction.

        /*
         * TODO use primary_conninfo (if available) from subscriber and
         * extract publisher connection string. Assume that there are
         * identical entries for physical and logical replication. If there is
         * not, we would fail anyway.
         */

---

From: Fabrízio de Royes Mello <[email-masked]>
Date: Wed, 31 Jan 2024 11:55:59 -0300

> Try v11. It seems v12-0002 is not correct.

Using v11 I'm getting this error:

~/pgsql took 22s
✦ ➜ pg_createsubscriber -d fabrizio -r -D /tmp/replica5434 -S 'host=/tmp
port=5434' -P 'host=/tmp port=5432'
NOTICE:  changed the failover state of replication slot
"pg_createsubscriber_16384_706609" on publisher to false
pg_createsubscriber: error: could not drop replication slot
"pg_createsubscriber_706609_startpoint" on database "fabrizio": ERROR:
 replication slot "pg_createsubscriber_706609_startpoint" does not exist
Write-ahead log reset

Attached the output log.

Regards,

-- 
Fabrízio de Royes Mello

---

From: "Euler Taveira" <[email-masked]>
Date: Wed, 31 Jan 2024 12:37:52 -0300

On Wed, Jan 31, 2024, at 11:55 AM, Fabrízio de Royes Mello wrote:
> ✦ ➜ pg_createsubscriber -d fabrizio -r -D /tmp/replica5434 -S 'host=/tmp port=5434' -P 'host=/tmp port=5432'
> NOTICE:  changed the failover state of replication slot "pg_createsubscriber_16384_706609" on publisher to false
> pg_createsubscriber: error: could not drop replication slot "pg_createsubscriber_706609_startpoint" on database "fabrizio": ERROR:  replication slot "pg_createsubscriber_706609_startpoint" does not exist
> Write-ahead log reset

Hmm. I didn't try it with the failover patch that was recently applied. Did you
have any special configuration on primary?

---

From: Fabrízio de Royes Mello <[email-masked]>
Date: Wed, 31 Jan 2024 14:39:28 -0300

> have any special configuration on primary?

Nothing special, here the configurations I've changed after bootstrap:

port = '5432'
wal_level = 'logical'
max_wal_senders = '8'
max_replication_slots = '6'
hot_standby_feedback = 'on'
max_prepared_transactions = '10'
max_locks_per_transaction = '512'

Regards,

--
Fabrízio de Royes Mello

---

From: "Euler Taveira" <[email-masked]>
Date: Wed, 31 Jan 2024 21:04:55 -0300

> dangerous. I think we should overwrite recovery_min_apply_delay to zero. Thought?

I didn't provide the whole explanation. I'm envisioning the use case that pg_ctl
doesn't reach the consistent state and the timeout is reached (the consequence
is that pg_createsubscriber aborts the execution). It might occur on a busy
server. The probability that it occurs with the current code is low (LSN gap
for recovery is small). Maybe I'm anticipating issues when the base backup
support is added but better to raise concerns during development.

> Below part contains my comments for v11-0001. Note that the ordering is random.

Hayato, thanks for reviewing v11.

> and shorter options in the synopsis section.

pg_upgrade doesn't but others do like pg_rewind, pg_resetwal, pg_controldata,
pg_checksums. It seems newer tools tend to provide short and long options.

> the comment or still considering?

Documentation is on my list. I didn't fix the documentation since some design
decisions were changed. I'm still working on it.

>       <term><option>-P  <replaceable class="parameter">connstr</replaceable></option></term>

Fixed.

[documentation related items will be addressed later...]

> I think there are some commenting conversions in PG, but this file breaks it.

It is on my list.

> Some pg_log_error() + exit(1) can be replaced with pg_fatal().

Done. I kept a few pg_log_error() + exit() because there is no
pg_fatal_and_hint() function.

Done.

s/publication/publisher/
s/subscription/subscriber/

> (I still think the publisher-server should be removed)

Why not? Unique database is a common setup. It is unintended if you don't
document it accordingly. I'll make sure it is advertised in the --database and
the --publisher-server options.

> GetDataDirectoryCreatePerm() verifies it.

Good point. It is included in the next patch.

> objects in any log level, but the consideration may be too much. Thought?

Nothing? If you interrupt the execution, there will be objects left behind and
you, as someone that decided to do it, have to clean things up. What do you
expect this tool to do? The documentation will provide some guidance informing
the object name patterns this tool uses and you can check for leftover objects.
Someone can argue that is a valid feature request but IMO it is not one in the
top of the list.

> In case of pg_resetwal, the process exits before doing actual works like

Why? Are you suggesting that the dry run mode covers just the verification
part? If so, it is not a dry run mode. I would expect it to run until the end
(or until it accomplish its goal) but *does not* modify data. For pg_resetwal,
the modification is one of the last steps and the other ones (KillFoo
functions) that are skipped modify data. It ends the dry run mode when it
accomplish its goal (obtain the new control data values). If we stop earlier,
some of the additional steps won't be covered by the dry run mode and a failure
can happen but could be detected if you run a few more steps.

> But there are possibilities to exit(1) in drop_replication_slot(). Is it acceptable?

No, there isn't.

> Hmm, IIUC, it should be enabled by default [3]. Do you have anything in your mind?

Why? See [1]. I prefer the kind mode (always wait until the recovery ends) but
you and Amit are proposing a more aggressive mode. The proposal (-t 60) seems
ok right now, however, if the goal is to provide base backup support in the
future, you certainly should have to add the --recovery-timeout in big clusters
or those with high workloads because base backup is run between replication slot
creation and consistent LSN. Of course, we can change the default when base
backup support is added.

> checked in check_subscriber().

Good point. I included a check for pg_create_subscription role and CREATE
privilege on the specified database.

> Even if I executed without -v option, an output from pg_resetwal command appears.

The pg_resetwal is using a printf and there is no prefix that identifies that
message is from pg_resetwal. That's message has been bothering me for a while
so let's send it to /dev/null. I'll include it in the next patch.

    RewriteControlFile();
    KillExistingXLOG();
    KillExistingArchiveStatus();
    KillExistingWALSummaries();
    WriteEmptyXLOG();

    printf(_("Write-ahead log reset\n"));
    return 0;

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Thu, 1 Feb 2024 02:09:55 +0000

Dear Euler,

Thanks for giving comments! I want to reply some of them.

Hmm, actually I didn't know the case. Thanks for explanation. I want to see
how you describe on the doc.

Oh, you are right.

OK, so let's keep current style.

Yes, it was my expectation. I'm still not sure which operations can detect by the
dry_run, but we can keep it for now.

Sorry, I was missing your previous post. Let's keep yours.

Not sure, but can we do the replication origin functions by these privilege?
According to the doc[1], these ones seem not to be related.

[1]: https://www.postgresql.org/docs/devel/functions-admin.html#FUNCTIONS-REPLICATION

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

---

From: "Euler Taveira" <[email-masked]>
Date: Thu, 01 Feb 2024 00:05:23 -0300

> dry_run, but we can keep it for now.

The main goal is to have information for troubleshooting.

> According to the doc[1], these ones seem not to be related.

Hmm. No. :( Better add this check too.