From: | Ninad Shah <ninad(dot)shah(at)percona(dot)com> |
---|---|
To: | Srinivasarao Oguri <srinivasoguri7(at)gmail(dot)com> |
Cc: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Logical replication failed |
Date: | 2023-12-04 11:16:57 |
Message-ID: | CAMtEjObQ4iQ7OgmyzQaoRFdweLo9bHRJRjuiFtm=7iriSoUPqQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
What's your max_replication_workers and max_parallel_workers set to?
Thanks,
--
Ninad Shah
PostgreSQL DBA I, Managed Services
e: ninad(dot)shah(at)percona(dot)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 ?
>
From | Date | Subject | |
---|---|---|---|
Next Message | Srinivasarao Oguri | 2023-12-04 12:53:04 | Re: Logical replication failed |
Previous Message | Srinivasarao Oguri | 2023-12-02 13:52:07 | Logical replication failed |