Postgresql 10 create subscription hangs

On my local machine, I just upgraded to the latest postgres.app to get version 10. Before doing this, I ran pg_dumpall on the 9.6 database and then after installing version 10, I executed the script to re-create the databases, users, etc... I also changed the wal_level to logical after running the script from dumpall and updated some logging settings.

I first created a subscription in the dot database (as superuser)

bill=# \connect dot;
You are now connected to database "dot" as user "bill".
dot=# create publication foo for table dot.item;
CREATE PUBLICATION

There are about 39k rows in the item table, so it shouldn't take that long to sync the data.

Next, I create a new database called dot2 owned by the same user as the dot database, and I created an empty item table in the same schema as it exists in the dot database, and then I tried to subscribe to the the publication in the other database (also as superuser).

bill=# \connect dot2;
You are now connected to database "dot2" as user "bill".
dot2=# create subscription bar connection 'host=localhost port=5432 dbname=dot connect_timeout=10' PUBLICATION foo;

However, this just hangs. It does not return.

In my log, all I see is this...

2017-11-26 21:36:52.828 EST bill@dot2 54567 [local] STATEMENT:  create subscription bar connection 'host=localhost port=5432 dbname=dot connect_timeout=10' PUBLICATION foo;
2017-11-26 21:36:55.122 EST bill@dot 54978 ::1(61990) LOG:  duration: 9.328 ms  statement: SELECT DISTINCT t.schemaname, t.tablename
  FROM pg_catalog.pg_publication_tables t
 WHERE t.pubname IN ('foo')
2017-11-26 21:36:55.126 EST bill@dot 54978 ::1(61990) LOG:  logical decoding found initial starting point at 0/4584A5A8
2017-11-26 21:36:55.126 EST bill@dot 54978 ::1(61990) DETAIL:  Waiting for transactions (approximately 1) older than 1613 to end.

Attempting to look in pg_stat_activity (disclaimer -- I don't know what I'm doing here) and I see this in the publish database...

e

From the docs:


Creating a subscription that connects to the same database cluster
(for example, to replicate between databases in the same cluster or to
replicate within the same database) will only succeed if the
replication slot is not created as part of the same command.
Otherwise, the CREATE SUBSCRIPTION call will hang. To make this work,
create the replication slot separately (using the function
pg_create_logical_replication_slot with the plugin name pgoutput) and
create the subscription using the parameter create_slot = false. This
is an implementation restriction that might be lifted in a future
release.

StackExchange Database Administrators Q#191755, answer score: 10