SELECT * FROM pglogical_regress_variables();

provider_dsn | subscriber_dsn

--------------+----------------

regression | postgres

(1 row)

\gset

\c :provider_dsn

SET client_min_messages = 'warning';

DROP ROLE IF EXISTS nonreplica;

CREATE USER nonreplica;

CREATE EXTENSION IF NOT EXISTS pglogical;

GRANT ALL ON SCHEMA pglogical TO nonreplica;

GRANT ALL ON ALL TABLES IN SCHEMA pglogical TO nonreplica;

\c :subscriber_dsn

SET client_min_messages = 'warning';

CREATE EXTENSION IF NOT EXISTS pglogical;

-- fail (local node not existing)

SELECT * FROM pglogical.create_subscription(

subscription_name := 'test_subscription',

provider_dsn := 'dbname=regression user=nonreplica',

forward_origins := '{}');

ERROR: local node not found

-- succeed

SELECT * FROM pglogical.create_node(node_name := 'test_subscriber', dsn := 'dbname=postgres user=nonreplica');

create_node

-------------

1755434425

(1 row)

-- fail (can't connect to remote)

SELECT * FROM pglogical.create_subscription(

subscription_name := 'test_subscription',

provider_dsn := 'dbname=regression user=nonexisting',

forward_origins := '{}');

ERROR: could not connect to the postgresql server: FATAL: role "nonexisting" does not exist

DETAIL: dsn was: dbname=regression user=nonexisting fallback_application_name='create_subscription'

-- fail (remote node not existing)

SELECT * FROM pglogical.create_subscription(

subscription_name := 'test_subscription',

provider_dsn := 'dbname=regression user=nonreplica',

forward_origins := '{}');

ERROR: could fetch remote node info: ERROR: local node not found

\c :provider_dsn

-- succeed

SELECT * FROM pglogical.create_node(node_name := 'test_provider', dsn := 'dbname=postgres user=nonreplica');

create_node

-------------

2689511696

(1 row)

\c :subscriber_dsn

-- fail (can't connect with replication connection to remote)

SELECT * FROM pglogical.create_subscription(

subscription_name := 'test_subscription',

provider_dsn := 'dbname=regression user=nonreplica',

forward_origins := '{}');

ERROR: could not connect to the postgresql server in replication mode: FATAL: must be superuser or replication role to start walsender

DETAIL: dsn was: dbname=regression user=nonreplica replication=database fallback_application_name='create_subscription'

-- cleanup

SELECT * FROM pglogical.drop_node('test_subscriber');

drop_node

-----------

t

(1 row)

\c :provider_dsn

SELECT * FROM pglogical.drop_node('test_provider');

drop_node

-----------

t

(1 row)

SET client_min_messages = 'warning';

DROP OWNED BY nonreplica;

DROP ROLE IF EXISTS nonreplica;