On 24 January 2018 at 22:41, Ryan Murphy <ryanfmur...@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_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
