Обсуждение: Logical replication failed

Поиск
Список
Период
Сортировка

Logical replication failed

От
Srinivasarao Oguri
Дата:
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 ?

Re: Logical replication failed

От
Ninad Shah
Дата:
What's your max_replication_workers and max_parallel_workers set to?

Thanks,

--

Ninad Shah
PostgreSQL DBA I, Managed Services

e: ninad.shah@percona.com

 w: www.percona.com

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 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 ?

Re: Logical replication failed

От
Srinivasarao Oguri
Дата:
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.com

 w: www.percona.com

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 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 ?