Re: Logical replication failed

max_logical_replication_workers | 4
max_parallel_workers | 8

On Mon, Dec 4, 2023 at 4:47 PM Ninad Shah <ninad(dot)shah(at)percona(dot)com> wrote:

> What's your max_replication_workers and max_parallel_workers set to?
>
> Thanks,
>
> --
>
> <https://www.percona.com/>
>
> Ninad Shah
> PostgreSQL DBA I, Managed Services
>
> e: ninad(dot)shah(at)percona(dot)com
>
> w: www.percona.com
>
> Databases Run Better With Percona
>
>
> On Sat, Dec 2, 2023 at 7:23 PM Srinivasarao Oguri <
> srinivasoguri7(at)gmail(dot)com> 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 ?
>>
>