Re: Logical replication in the same cluster

On 26/04/17 18:59, Bruce Momjian wrote:
> I tried setting up logical replication on the same server between two
> different databases, and got, from database test:
>
> test=> CREATE TABLE test (x INT PRIMARY KEY);
> CREATE TABLE
> test=>
> test=> INSERT INTO test VALUES (1);
> INSERT 0 1
> test=> CREATE PUBLICATION mypub FOR TABLE test;
> CREATE PUBLICATION
>
> then from database test2:
>
> test2=> CREATE TABLE test (x INT PRIMARY KEY);
> CREATE TABLE
> test2=> CREATE SUBSCRIPTION mysub CONNECTION 'dbname=test port=5432'
> PUBLICATION mypub;
> NOTICE: synchronized table states
>
> and it just hangs. My server logs say:
>
> 2017-04-26 12:50:53.694 EDT [29363] LOG: logical decoding found initial
> starting point at 0/15FF3E0
> 2017-04-26 12:50:53.694 EDT [29363] DETAIL: 1 transaction needs to
> finish.
>
> Is this expected? I can get it working from two different clusters.
>

Yes that's result of how logical replication slots work, the transaction
that needs to finish is your transaction. It can be worked around by
creating the slot manually via the SQL interface for example and create
the subscription using WITH (NOCREATE SLOT, SLOT NAME = 'your slot') .

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services