Обсуждение: logical replication problem

Hi!

Setup:

- db-server_A on port 5432
- db-server_B on port 5433

on db-server_A:
postgres=# \dRp
                     Liste der Publikationen
 Name | Eigentümer | Alle Tabellen | Inserts | Updates | Deletes
------+------------+---------------+---------+---------+---------
 mig1 | postgres   | t             | t       | t       | t

on db-server_B:
postgres=# \dRs
            Liste der Subskriptionen
 Name | Eigentümer | Eingeschaltet | Publikation
------+------------+---------------+-------------
 sub1 | postgres   | t             | {mig1}

in db-server_A pg_hba.conf:
local   replication     postgres                                peer
host    replication     postgres        127.0.0.1/32            ident
host    replication     postgres        ::1/128                 ident

on db-server_A:
postgres# CREATE PUBLICATION mig1 FOR ALL TABLES;
CREATE PUBLICATION

the command on db-server_B:
postgres# CREATE SUBSCRIPTION sub1 CONNECTION 'host=127.0.0.1
port=5432 dbname=mydb user=postgres PUBLICATION mig1;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION

worked as expected.
But: instead of starting replication I find Errors within db-server_B logs:
2019-01-24 10:57:58.549 CET [28956] LOG:  Apply-Worker für logische
Replikation für Subskription »sub1« hat gestartet
2019-01-24 10:57:58.553 CET [28956] FEHLER:  konnte keine Daten vom
WAL-Stream empfangen: FEHLER:  Publikation »mig1« existiert nicht
        KONTEXT:  Slot »sub1«, Ausgabe-Plugin »pgoutput«, im Callback
change, zugehörige LSN 47/B4BCA2A8
2019-01-24 10:57:58.554 CET [5982] LOG:  Background-Worker »logical
replication worker« (PID 28956) beendete mit Status 1

Configuration was set on both servers to include
wal_level = logical

Any ideas why this does not work as expected? Any further ideas what
to prove on db-server_A and db-server_B?


--
Thomas

On Mon, Jan 28, 2019 at 11:15 AM Pavan Teja <pavan.postgresdba@gmail.com> wrote:
>
> Does the db_server_B has data in it??

Yes, it has -- about 51GiByte ... Changed frequently.

> Regards,
> Pavan
>
> On Mon 28 Jan, 2019, 3:42 PM Thomas Schweikle <tschweikle@gmail.com wrote:
>>
>> Hi!
>>
>> Setup:
>>
>> - db-server_A on port 5432
>> - db-server_B on port 5433
>>
>> on db-server_A:
>> postgres=# \dRp
>>                      Liste der Publikationen
>>  Name | Eigentümer | Alle Tabellen | Inserts | Updates | Deletes
>> ------+------------+---------------+---------+---------+---------
>>  mig1 | postgres   | t             | t       | t       | t
>>
>> on db-server_B:
>> postgres=# \dRs
>>             Liste der Subskriptionen
>>  Name | Eigentümer | Eingeschaltet | Publikation
>> ------+------------+---------------+-------------
>>  sub1 | postgres   | t             | {mig1}
>>
>> in db-server_A pg_hba.conf:
>> local   replication     postgres                                peer
>> host    replication     postgres        127.0.0.1/32            ident
>> host    replication     postgres        ::1/128                 ident
>>
>> on db-server_A:
>> postgres# CREATE PUBLICATION mig1 FOR ALL TABLES;
>> CREATE PUBLICATION
>>
>> the command on db-server_B:
>> postgres# CREATE SUBSCRIPTION sub1 CONNECTION 'host=127.0.0.1
>> port=5432 dbname=mydb user=postgres PUBLICATION mig1;
>> NOTICE:  created replication slot "sub1" on publisher
>> CREATE SUBSCRIPTION
>>
>> worked as expected.
>> But: instead of starting replication I find Errors within db-server_B logs:
>> 2019-01-24 10:57:58.549 CET [28956] LOG:  Apply-Worker für logische
>> Replikation für Subskription »sub1« hat gestartet
>> 2019-01-24 10:57:58.553 CET [28956] FEHLER:  konnte keine Daten vom
>> WAL-Stream empfangen: FEHLER:  Publikation »mig1« existiert nicht
>>         KONTEXT:  Slot »sub1«, Ausgabe-Plugin »pgoutput«, im Callback
>> change, zugehörige LSN 47/B4BCA2A8
>> 2019-01-24 10:57:58.554 CET [5982] LOG:  Background-Worker »logical
>> replication worker« (PID 28956) beendete mit Status 1
>>
>> Configuration was set on both servers to include
>> wal_level = logical
>>
>> Any ideas why this does not work as expected? Any further ideas what
>> to prove on db-server_A and db-server_B?
>>
>>
>> --
>> Thomas
>>


--
Thomas

On 28/1/19 12:12 μ.μ., Thomas Schweikle wrote:
> Hi!
>
> Setup:
>
> - db-server_A on port 5432
> - db-server_B on port 5433
>
> on db-server_A:
> postgres=# \dRp
>                       Liste der Publikationen
>   Name | Eigentümer | Alle Tabellen | Inserts | Updates | Deletes
> ------+------------+---------------+---------+---------+---------
>   mig1 | postgres   | t             | t       | t       | t
>
> on db-server_B:
> postgres=# \dRs
>              Liste der Subskriptionen
>   Name | Eigentümer | Eingeschaltet | Publikation
> ------+------------+---------------+-------------
>   sub1 | postgres   | t             | {mig1}
>
> in db-server_A pg_hba.conf:
> local   replication     postgres                                peer
> host    replication     postgres        127.0.0.1/32            ident
> host    replication     postgres        ::1/128                 ident
>
> on db-server_A:
> postgres# CREATE PUBLICATION mig1 FOR ALL TABLES;
> CREATE PUBLICATION
>
> the command on db-server_B:
> postgres# CREATE SUBSCRIPTION sub1 CONNECTION 'host=127.0.0.1
> port=5432 dbname=mydb user=postgres PUBLICATION mig1;
> NOTICE:  created replication slot "sub1" on publisher
> CREATE SUBSCRIPTION
>
> worked as expected.
> But: instead of starting replication I find Errors within db-server_B logs:
> 2019-01-24 10:57:58.549 CET [28956] LOG:  Apply-Worker für logische
> Replikation für Subskription »sub1« hat gestartet
> 2019-01-24 10:57:58.553 CET [28956] FEHLER:  konnte keine Daten vom
> WAL-Stream empfangen: FEHLER:  Publikation »mig1« existiert nicht
>          KONTEXT:  Slot »sub1«, Ausgabe-Plugin »pgoutput«, im Callback
> change, zugehörige LSN 47/B4BCA2A8
> 2019-01-24 10:57:58.554 CET [5982] LOG:  Background-Worker »logical
> replication worker« (PID 28956) beendete mit Status 1
It could help if your logs were in English, anyways any error prior to that? Closely monitoring the subscription node
isvery important. Any error could stall / pause logical replication.
 
>
> Configuration was set on both servers to include
> wal_level = logical
>
> Any ideas why this does not work as expected? Any further ideas what
> to prove on db-server_A and db-server_B?
>
>


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt