Обсуждение: Logical replication failed
Hi,
-- Removed the table from publication
Can someone please help me why it is not working ? Is this a bug ?
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"
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
What's your max_replication_workers and max_parallel_workers set to?
Thanks,
--
Ninad Shah | |
Databases Run Better With Percona |
On Sat, Dec 2, 2023 at 7:23 PM Srinivasarao Oguri <srinivasoguri7@gmail.com> wrote:
Hi,I am trying to configure logical replication and below are the steps I followed.1. Create publication2. Add the tables from user schemas to publication3. Disable users from login4. Create a clone of production machine (RDS PostgreSQL)5. Create subscription on clone serverAfter sometime I got below error2023-12-02 13:09:16 UTC::@:[23692]:LOCATION: ApplyWorkerMain, worker.c:16942023-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 FULL2023-12-02 13:09:16 UTC::@:[23692]:LOCATION: check_relation_updatable, worker.c:6782023-12-02 13:09:16 UTC::@:[527]:LOG: 00000: background worker "logical replication worker" (PID 23692) exited with exit code 12023-12-02 13:09:16 UTC::@:[527]:LOCATION: LogChildExit, postmaster.c:4218I have enabled the replica identity as full in both the primary and standbyrecon=> alter table XXXX replica identity full;ALTER TABLErecon=> alter table XXXX replica identity full;ALTER TABLErecon=> 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-- Removed the table from publication"Still getting the same error"recon=> alter publication recon_pub drop table XXXXXXXX;ALTER PUBLICATIONrecon=> select * from pg_stat_replication;-[ RECORD 1 ]----+------------------------------pid | 12742usesysid | 16399usename | XXXXapplication_name | XXXXXclient_addr | XXXXXXXXXXXXclient_hostname |client_port | 58152backend_start | 2023-12-02 13:20:23.028634+00backend_xmin |state | catchupsent_lsn | 0/58724C8write_lsn | 0/4E3ED78flush_lsn | 0/4E3ED78replay_lsn | 0/4E3ED78write_lag |flush_lag |replay_lag |sync_priority | 0sync_state | asyncreply_time | 2023-12-02 13:20:23.04346+00After 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.Can someone please help me why it is not working ? Is this a bug ?recon=> alter subscription XXXX refresh publication;ALTER SUBSCRIPTION
max_logical_replication_workers | 4
max_parallel_workers | 8
On Mon, Dec 4, 2023 at 4:47 PM Ninad Shah <ninad.shah@percona.com> wrote:
What's your max_replication_workers and max_parallel_workers set to?Thanks,--
Ninad Shah
PostgreSQL DBA I, Managed Services
e: ninad.shah@percona.comDatabases Run Better With Percona
On Sat, Dec 2, 2023 at 7:23 PM Srinivasarao Oguri <srinivasoguri7@gmail.com> wrote:Hi,I am trying to configure logical replication and below are the steps I followed.1. Create publication2. Add the tables from user schemas to publication3. Disable users from login4. Create a clone of production machine (RDS PostgreSQL)5. Create subscription on clone serverAfter sometime I got below error2023-12-02 13:09:16 UTC::@:[23692]:LOCATION: ApplyWorkerMain, worker.c:16942023-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 FULL2023-12-02 13:09:16 UTC::@:[23692]:LOCATION: check_relation_updatable, worker.c:6782023-12-02 13:09:16 UTC::@:[527]:LOG: 00000: background worker "logical replication worker" (PID 23692) exited with exit code 12023-12-02 13:09:16 UTC::@:[527]:LOCATION: LogChildExit, postmaster.c:4218I have enabled the replica identity as full in both the primary and standbyrecon=> alter table XXXX replica identity full;ALTER TABLErecon=> alter table XXXX replica identity full;ALTER TABLErecon=> 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-- Removed the table from publication"Still getting the same error"recon=> alter publication recon_pub drop table XXXXXXXX;ALTER PUBLICATIONrecon=> select * from pg_stat_replication;-[ RECORD 1 ]----+------------------------------pid | 12742usesysid | 16399usename | XXXXapplication_name | XXXXXclient_addr | XXXXXXXXXXXXclient_hostname |client_port | 58152backend_start | 2023-12-02 13:20:23.028634+00backend_xmin |state | catchupsent_lsn | 0/58724C8write_lsn | 0/4E3ED78flush_lsn | 0/4E3ED78replay_lsn | 0/4E3ED78write_lag |flush_lag |replay_lag |sync_priority | 0sync_state | asyncreply_time | 2023-12-02 13:20:23.04346+00After 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.Can someone please help me why it is not working ? Is this a bug ?recon=> alter subscription XXXX refresh publication;ALTER SUBSCRIPTION