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
- Dropped the logical replication slot.
- Created the logical replication slot again.
- 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