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

On 24/01/18 12:28, Craig Ringer wrote:
> On 24 January 2018 at 22:41, Ryan Murphy <ryanfmurphy(at)gmail(dot)com
> <mailto:ryanfmurphy(at)gmail(dot)com>> wrote:
>
> Hello hackers,
>
> I'm experimenting with Logical Replication.
> (https://www.postgresql.org/docs/10/static/logical-replication.html
> <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.

Indeed.

> I thought that was documented as a caveat.

It is documented in CREATE SUBSCRIPTION docs.

> 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.

We didn't find clean way of detecting this (being sure this is the case
involves a lot of shmem gymnastics and process interlocking, not to
mention the code layering does not really work for it).

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