Re: Enhance 'pg_createsubscriber' to retrieve databases automatically when no database is provided.

On Mon, 10 Feb 2025 at 22:14, vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> On Mon, 10 Feb 2025 at 20:36, Shubham Khanna
> <khannashubham1197(at)gmail(dot)com> wrote:
> >
> > The attached patch contains the suggested changes.
>
> If a new database is created on the primary server while
> pg_createsubscriber is running, the subscription will not be created
> for the new database.
> To reproduce this issue, follow these steps:
> Debug pg_createsubscriber and set a breakpoint after the
> fetch_source_databases function, where the databases would have been
> prepared.
> While execution of pg_createsubscriber is paused, create a new
> database on the primary node.
> You will observe that the database is created on the standby node, but
> the subscription will not be created for the newly created database.
> +fetch_source_databases(struct CreateSubscriberOptions *opt)
> +{
> + PGconn *conn;
> + PGresult *res;
> + int num_rows;
> +
> + /* Establish a connection to the PostgreSQL server */
> + conn = connect_database(opt->pub_conninfo_str, true);
> +
> + res = PQexec(conn, "SELECT datname FROM pg_database WHERE
> datistemplate = false AND datallowconn = true");
> +
> + /* Check for errors during query execution */
> + if (PQresultStatus(res) != PGRES_TUPLES_OK)

If we don't have a solution for this, how about the following approach:
a) Add a note in the documentation indicating that the publication and
subscriptions must be created manually for any new databases created
while pg_createsubscriber is running. b) At the end of the
pg_createsubscriber execution, identify any new databases that were
created and list them for the user.

Since this scenario might not be common, we could either implement
just option a) or both a) and b).

Regards,
Vignesh