Обсуждение: Is it valid to have logical replication between 2 databases on thesame postgres server?
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.
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?
Thanks a lot!
Best,
Ryan
On 24 January 2018 at 22:41, Ryan Murphy <ryanfmurphy@gmail.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_subCONNECTION '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.
--
On 24/01/18 12:28, Craig Ringer wrote: > On 24 January 2018 at 22:41, Ryan Murphy <ryanfmurphy@gmail.com > <mailto:ryanfmurphy@gmail.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
Thanks all! I'll try creating the replication slot manually.