Thread: logical replication problem

logical replication problem

From

Thomas Schweikle

Date:

28 January 2019, 13:12:18

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

Re: logical replication problem

From

Thomas Schweikle

Date:

28 January 2019, 13:19:40

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

Re: logical replication problem

From

Pavan Teja

Date:

28 January 2019, 13:27:05

And that's the reason for the error. In the subscriber no need of any data. Jus structure sync is enough. Try truncating the database and start the replication from fresh ull get rid of the errors

Regards,

Pavan

On Mon 28 Jan, 2019, 3:50 PM Thomas Schweikle <tschweikle@gmail.com wrote:

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

Re: logical replication problem

From

Achilleas Mantzios

Date:

28 January 2019, 13:29:25

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