0

I have an issue against which I seek some advise. I have set up logical replication between two databases. The database was/is syncing perfectly well. However - there is one huge table that started giving error due to FK constraint. I have eliminated the FK constraint error. After that I was trying to import the rows from the source to the destination. The import was successful. However the replication stopped working. To fix this I did the following

  1. Dropped the logical replication slot.
  2. Created the logical replication slot again.
  3. Attached the replication slot to the subscription back again.

After this the replication started working for other tables. However it still does not work for the huge table where we had the FK issue. The log does not say anything about something missing about the replication. I do not mind losing the data, but I would like this table to be replicated as well. I have also restarted the pgsql service.

I see this in the log -

2024-04-30 13:57:02.633 CEST [6220] LOG: logical replication apply worker for subscription "central_quality_data_rep_mirror_subscription" has started 2024-04-30 13:57:02.703 CEST [10596] LOG: C/8081E400 has been already streamed, forwarding to C/808206B0 2024-04-30 13:57:02.703 CEST [10596] STATEMENT: START_REPLICATION SLOT "central_quality_data_rep_mirror_subscription" LOGICAL C/8081E400 (proto_version '4', streaming 'parallel', origin 'any', publication_names '"central_quality_data_rep_publish_all"') 2024-04-30 13:57:02.705 CEST [10596] LOG: starting logical decoding for slot "central_quality_data_rep_mirror_subscription" 2024-04-30 13:57:02.705 CEST [10596] DETAIL: Streaming transactions committing after C/808206B0, reading WAL from C/808206B0. 2024-04-30 13:57:02.705 CEST [10596] STATEMENT: START_REPLICATION SLOT "central_quality_data_rep_mirror_subscription" LOGICAL C/8081E400 (proto_version '4', streaming 'parallel', origin 'any', publication_names '"central_quality_data_rep_publish_all"') 2024-04-30 13:57:02.705 CEST [10596] LOG: logical decoding found consistent point at C/808206B0 2024-04-30 13:57:02.705 CEST [10596] DETAIL: There are no running transactions. 2024-04-30 13:57:02.705 CEST [10596] STATEMENT: START_REPLICATION SLOT "central_quality_data_rep_mirror_subscription" LOGICAL C/8081E400 (proto_version '4', streaming 'parallel', origin 'any', publication_names '"central_quality_data_rep_publish_all"') 2024-04-30 14:02:02.473 CEST [12312] LOG: checkpoint starting: time 2024-04-30 14:02:03.814 CEST [12312] LOG: checkpoint complete: wrote 15 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=1.321 s, sync=0.010 s, total=1.342 s; sync files=14, longest=0.002 s, average=0.001 s; distance=49 kB, estimate=49 kB; lsn=C/8082CB78, redo lsn=C/8082CB40

Improve this question
5
  • How did you resolve the fk issue?
    – Vérace
    Commented Apr 30 at 12:56
  • One removed the FK from both source and the replica.
    – Sabyasachi Mukherjee
    Commented Apr 30 at 13:21
  • OK - what is a "huge table"?
    – Vérace
    Commented Apr 30 at 13:53
  • Table with lots of rows :)
    – Sabyasachi Mukherjee
    Commented Apr 30 at 13:59
  • OK, Mr. Smart Guy, how many rows and how many GB?
    – Vérace
    Commented Apr 30 at 14:30

1 Answer 1

Reset to default
0

On thing that needs to be checked is whether the publication table has a primary key or a unique key.

You can monitor the progress of the replicated data by query the metadata table about logical replication pg_subscription_rel to see the status of replicated tables. See this pg_subscription_rel documentation

Also please share the error log about the FK constraint because the current logs that you shared, don't explain anything related to the issue.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.