pg_createsubscriber: Allow Duplicate Publication Names

Core Problem

pg_createsubscriber is a utility that converts a physical standby into a logical replica by creating subscriptions on the subscriber side that connect back to the publisher. It accepts multiple --database options (one per database to replicate) along with corresponding --publication and --subscription options.

The tool historically rejected duplicate --publication names across all specified databases. This was a non-issue when publications were always auto-created by the tool, but became a real limitation with the PG19 feature "Support existing publications in pg_createsubscriber." In practice, users commonly define publications with identical names in different databases (e.g., app_pub FOR ALL TABLES in both db1 and db2). Since publications are database-local objects in PostgreSQL's catalog (pg_publication is a regular catalog table, not a shared one), there is no namespace collision — the duplicate-name check was overly restrictive.

Architectural Context

pg_createsubscriber maintains a positional mapping between its repeated options: the Nth --database pairs with the Nth --publication and Nth --subscription. The tool already enforces that database names cannot be duplicated (since it needs unique target databases). The publication-name uniqueness check was added as a general "safety" validation but conflates global uniqueness with per-database uniqueness.

The fix is straightforward: remove the duplicate publication name check. Since each publication name is scoped to its paired database, and duplicate databases are already rejected, duplicate publication names across different databases are perfectly valid.

Subscription Name Complexity

The thread explored whether the same relaxation should apply to --subscription names. While subscription names are also database-local objects, there's a critical complication: replication slot names are cluster-global. By default, pg_createsubscriber uses the subscription name as the replication slot name. If two databases specify the same subscription name without explicit --replication-slot overrides, the tool will attempt to create two replication slots with the same name on the publisher, which fails:

pg_createsubscriber: error: could not create replication slot "sub" in database "testdb": 
ERROR:  replication slot "sub" already exists

This could be worked around by specifying distinct --replication-slot names, but supporting this properly requires additional sanity checks (detecting the collision when --replication-slot is omitted and producing a clear error before attempting slot creation). The consensus was to defer this to PG20.

Design Decisions and Tradeoffs

1. Publication names: remove check entirely — No additional guard needed because publications are purely database-local with no cluster-wide side effects.

2. Subscription names: defer to PG20 — The replication slot coupling makes this more complex. While the error currently occurs before promotion (so users can retry), a proper solution requires detecting the implicit slot-name collision upfront and either erroring clearly or auto-generating distinct slot names.

3. Scope limitation for PG19 — The publication fix is an Open Items candidate because it blocks practical use of the new "reuse existing publications" feature. The subscription fix is explicitly deferred as non-blocking.

Patch Analysis

The v2 patch (0001 only) is minimal:

• Removes the loop in pg_createsubscriber.c that checks for duplicate publication names in the command-line option array
• Updates the commit message to explain why subscription names are intentionally left with the duplicate check (due to replication slot naming implications)

This is a safe, low-risk change that removes an unnecessary restriction without introducing new failure modes.