Re: Enhance pg_createsubscriber to create required standby. - Mailing list pgsql-hackers

On Thu, 5 Jun 2025 at 01:50, Peter Eisentraut <peter@eisentraut.org> wrote:
>
> On 04.06.25 11:56, Amit Kapila wrote:
> >> It's not clear to me how this change now would substantially improve the
> >> user experience.  The number of characters you type is approximately the
> >> same.  You still need to support the old mode because the backup might
> >> not come from pg_basebackup.
> >
> > In the current functionality, the user must first manually create a
> > standby or use an existing standby to make it a subscriber. I thought
> > saving this step for users would be quite helpful. It also helps
> > streamline the process into a single, cohesive workflow.
>
> Unless I'm missing something, doesn't this merely replace
>
> pg_basebackup && pg_createsubscriber
>
> with
>
> pg_createsubscriber --create-standby
>
> I mean, as I'm typing this out, this is literally the same number of
> characters.  Is the second one easier somehow?  It's not clear.

I believe adding support for standby creation directly within
pg_createsubscriber offers several key advantages over the manual
two-step approach of running pg_basebackup followed by
pg_createsubscriber. The main benefits include:
1) Improved Progress Reporting: pg_createsubscriber can be enhanced to
provide a clear, step-by-step progress indicator that shows both the
total number of steps and the current step being executed. For
example:
[Step 1/5] Executing pg_basebackup...
[Step 2/5] Validating prerequisites...
[Step 3/5] Creating publication and replication slot...
[Step 4/5] Waiting for standby to reach consistent state...
[Step 5/5] Creating subscription...
2) Graceful Handling of Interruptions: pg_createsubscriber can be
enhanced to handle user interruptions (e.g., Ctrl+C) more gracefully
by cleaning up any partially created directories, such as the base
backup target directory. This can be achieved by registering an atexit
handler for safe cleanup during early termination.
3) Cleanup on Partial Failure: If pg_basebackup completes but a
subsequent step (e.g., subscription or replication slot creation)
fails, pg_createsubscriber could offer the option to automatically
clean up the standby contents. In contrast, with the manual
pg_basebackup && pg_createsubscriber approach, failures after the
backup step leave residual data on disk, as there’s no built-in
cleanup mechanism.
4) Simplified Option Handling: The --create-standby option streamlines
the workflow by removing the need to manually pass options like -D and
-R. With this option, the user can simply run:
pg_createsubscriber -D <target_dir> -P <primary_connstr>  -d
<target_database> --create-standby
In the combined commands approach:
#primary is on the different server
pg_basebackup -D <target_dir> -d <primary_connstr> -R &&
pg_createsubscriber -d <target_database> -D <target_dir> -P
<primary_connstr>
#primary is on the same server
pg_basebackup -D <target_dir> -R && pg_createsubscriber -d
<target_database> -D <target_dir> -P <primary_connstr>
The integrated --create-standby option reduces the risk of user error,
misconfiguration, and is shorter to write for users.

Based on the above points I felt the create-standby option through
pg_createsubscriber will be helpful to users. Your thoughts?

Regards,
Vignesh