0

I'm facing problem within postgres12 FDW and can't understand where is the issue. In DWH we are replicating tables from two difference sources (for example source1 and source2) but there is this once table to whom replication from source keep crashing. Table structures and data types are identical except that on source2 we have additional column which is also in target db (DWH) within default value "0" so data from source1 could also be replicated. As I know, that's not a problem that in target table are more columns than source, but issue is that subscription process synchronizing only with source2. Within source1 it's synchronize once, on alter subscription source1 refresh publication and then it's stops and data are not replicated anymore (but subscriptions keeps working for other tables, problem is only within this particular table). There are no error messages on log file or anything that could help to resolve it by myself. I tried to drop table in DWH and recreate it, but it wont help. There are no duplicate entries or anything that could crash replication.

select 
   b.subname
   ,c.relname
   ,case 
       when a.srsubstate = 'i' then 'initialize'
       when a.srsubstate = 'd' then 'data is being copied'
       when a.srsubstate = 's' then 'synchronized'
       when a.srsubstate = 'r' then 'ready (normal replication)'
       else null
       end srsubstate
from pg_subscription_rel a
left join pg_subscription b on a.srsubid = b.oid
left join pg_class c on a.srrelid = c.oid
where c.relname ='table_name'; 


RESULT:
"source2"   "table_name"    "ready (normal replication)"
"source1"   "table_name"    "synchronized"

REPLICA IDENTITY for tables in source and target = INDEX

INDEX in DWH and source db's are the same: "CREATE UNIQUE INDEX table_name_idx ON public.table_name USING btree (id, country)"

Also altered table: alter table table_name replica identity using index table_name_idx;

I guess DB links works correctly as other tables from both sources are replicated correctly.

PROBLEM: Data on DWH from source1 keep synchronized only once - on alter subscription refresh publication....

Jānis Freibergs
  • 36
  • 3
  • I get that you have some kind of problem with logical replication and foreign data wrappers, but you failed to describe the exact architecture, nor did you say what exactly fails and what the error message is. I also see no relevant extracts from the log file. – Laurenz Albe Mar 21 '22 at 09:54
  • select subname, received_lsn, latest_end_lsn from pg_catalog.pg_stat_subscription where subname like '%na_to_dwh%'; "na_to_dwh_az_w_country" null null – Jānis Freibergs Mar 21 '22 at 10:04
  • FDW and logical replication are different features. You don't describe any usage of FDW here – jjanes Mar 21 '22 at 11:51
  • Sorry for that. FDW use foreign servers to collect data. But in my opinion it's not the problem as data keeps replicating for other tables. – Jānis Freibergs Mar 21 '22 at 12:38

0 Answers0