Re: Logical replication in the same cluster

On Wed, Apr 26, 2017 at 11:41:51PM +0200, Petr Jelinek wrote:
> 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') .

Oh, OK. Is there a way we can give users a hint do that if they try
what I did?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +