Re: Logical replication failed

[Srinivasarao Oguri <srinivasoguri7@xxxxxxxxx>]

max_logical_replication_workers | 4

max_parallel_workers | 8

On Mon, Dec 4, 2023 at 4:47 PM Ninad Shah <ninad.shah@xxxxxxxxxxx> wrote:

What's your max_replication_workers and max_parallel_workers set to?

Thanks,

--

	Ninad Shah PostgreSQL DBA I, Managed Services e: ninad.shah@xxxxxxxxxxx  w: www.percona.com
Databases Run Better With Percona

On Sat, Dec 2, 2023 at 7:23 PM Srinivasarao Oguri <srinivasoguri7@xxxxxxxxx> wrote:

Hi,

I am trying to configure logical replication and below are the steps I followed.

1. Create publication

2. Add the tables from user schemas to publication

3. Disable users from login

4. Create a clone of production machine (RDS PostgreSQL)

5. Create subscription on clone server

After sometime I got below error

2023-12-02 13:09:16 UTC::@:[23692]:LOCATION: ApplyWorkerMain, worker.c:1694

2023-12-02 13:09:16 UTC::@:[23692]:ERROR: 55000: logical replication target relation "" has neither REPLICA IDENTITY index nor PRIMARY KEY and published relation does not have REPLICA IDENTITY FULL

2023-12-02 13:09:16 UTC::@:[23692]:LOCATION: check_relation_updatable, worker.c:678

2023-12-02 13:09:16 UTC::@:[527]:LOG: 00000: background worker "logical replication worker" (PID 23692) exited with exit code 1

2023-12-02 13:09:16 UTC::@:[527]:LOCATION: LogChildExit, postmaster.c:4218

I have enabled the replica identity as full in both the primary and standby

recon=> alter table XXXX replica identity full;

ALTER TABLE

recon=> alter table XXXX replica identity full;

ALTER TABLE

recon=> select relreplident FROM pg_class where relname='XXXXXXXXXX';

 relreplident

--------------

 f

(1 row)

recon=> select relreplident FROM pg_class where relname='XXXXXXXXXXXX';

 relreplident

--------------

 f

(1 row)

recon=>  alter subscription test_sub refresh publication ;

ALTER SUBSCRIPTION

"Still getting the same error"

-- Removed the table from publication

recon=> alter publication recon_pub drop table XXXXXXXX;

ALTER PUBLICATION

recon=> select * from pg_stat_replication;

-[ RECORD 1 ]----+------------------------------

pid              | 12742

usesysid         | 16399

usename          | XXXX

application_name | XXXXX

client_addr      | XXXXXXXXXXXX

client_hostname  |

client_port      | 58152

backend_start    | 2023-12-02 13:20:23.028634+00

backend_xmin     |

state            | catchup

sent_lsn         | 0/58724C8

write_lsn        | 0/4E3ED78

flush_lsn        | 0/4E3ED78

replay_lsn       | 0/4E3ED78

write_lag        |

flush_lag        |

replay_lag       |

sync_priority    | 0

sync_state       | async

reply_time       | 2023-12-02 13:20:23.04346+00

After sometime the replication broke again, I tried to refresh the subscription. But this time no error message in the log file and the replication is not working.

recon=> alter subscription XXXX refresh publication;

ALTER SUBSCRIPTION

Can someone please help me why it is not working ? Is this a bug ?