Re: Identify missing publications from publisher while create/alter subscription.

On Wed, Feb 9, 2022, at 12:06 PM, Ashutosh Sharma wrote:
> Just wondering if we should also be detecting the incorrect conninfo
> set with ALTER SUBSCRIPTION command as well. See below:
>
> -- try creating a subscription with incorrect conninfo. the command fails.
> postgres=# create subscription sub1 connection 'host=localhost
> port=5490 dbname=postgres' publication pub1;
> ERROR: could not connect to the publisher: connection to server at
> "localhost" (::1), port 5490 failed: Connection refused
> Is the server running on that host and accepting TCP/IP connections?
> connection to server at "localhost" (127.0.0.1), port 5490 failed:
> Connection refused
> Is the server running on that host and accepting TCP/IP connections?
That's because by default 'connect' parameter is true.

The important routine for all SUBSCRIPTION commands that handle connection
string is to validate the connection string e.g. check if all parameters are
correct. See walrcv_check_conninfo that calls PQconninfoParse.

The connection string is syntactically correct. Hence, no error. It could be
the case that the service is temporarily down. It is a useful and common
scenario that I wouldn't want to be forbid.

> -- reset the connninfo in the subscription to some wrong value. the
> command succeeds.
> postgres=# alter subscription sub1 connection 'host=localhost
> port=5490 dbname=postgres';
> ALTER SUBSCRIPTION
> postgres=#
>
> postgres=# drop subscription sub1;
> ERROR: could not connect to publisher when attempting to drop
> replication slot "sub1": connection to server at "localhost" (::1),
> port 5490 failed: Connection refused
> Is the server running on that host and accepting TCP/IP connections?
> connection to server at "localhost" (127.0.0.1), port 5490 failed:
> Connection refused
> Is the server running on that host and accepting TCP/IP connections?
> HINT: Use ALTER SUBSCRIPTION ... SET (slot_name = NONE) to
> disassociate the subscription from the slot.
Again, dropping a subscription that is associated with a replication slot
requires a connection to remove the replication slot. If the publisher is gone
(and so the replication slot), follow the HINT advice.

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