Re: Is it valid to have logical replication between 2 databases on the same postgres server?

On 24 January 2018 at 22:41, Ryan Murphy <ryanfmurphy(at)gmail(dot)com> wrote:

> Hello hackers,
>
> I'm experimenting with Logical Replication.
> (https://www.postgresql.org/docs/10/static/logical-replication.html)
> What I'm trying to do may be impossible l but just wanted to ask here.
>
> I'm trying to do logical replication from one database to another within
> the same server.
> Ultimately I want to do it between servers but was just trying this for
> connection simplicity (same host etc).
>
> *Details:*
>
> I have a postgres server running (11devel) with a database, call it db1.
> I created a new database on the same server, call it db2.
> I also created a test table, call it table1:
> CREATE TABLE table1 (id serial primary key, name text, tags text[]);
>
> On both databases I ran:
> ALTER SYSTEM SET wal_level = logical;
>
> (not sure if that's redundant because it's the same server?)
>
> On db1 I ran:
> CREATE PUBLICATION test_pub FOR TABLE table1;
> On db2 I ran:
> CREATE SUBSCRIPTION test_sub
> CONNECTION 'host=127.0.0.1 dbname=db1 user=xxxxxx password=xxxxxx'
> PUBLICATION test_pub;
>
> It just hangs.
>

You must create the replication slot manually in advance.

I thought that was documented as a caveat. For that matter, wasn't there
discussion of detecting that situation and telling the user about the
problem gracefully? Don't know if we ever got to doing that but seems not.

> I'm imagining that this is because WAL is at the server level, not the db
> level, and it's impossible for the same server to logically replicate to
> itself, even though it's 2 separate databases. Am I right that that's the
> problem? Could someone help me get pointed in the right direction?
>
>
Nope, that's not it at all.

Logical decoding and logical rep works fine within a db instance.

It's a problem with logical decoding setup needing to see all txns open at
the time of slot creation complete before it can return. But there's a txn
open waiting for the slot creation to finish. So they get stuck waiting for
each other and nothing progresses.

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