Hello,
I'm having an issue with Insert statement (through oracle_fdw) that hangs after checkpoint starting is "time" instead of "xlog". see end of log below.
Migrating data from Oracle (Windows 2008 R2) to Postgresql (Windows 2012 R2). Both systems are virtual machines residing on same host.
Over 200 migrations have been done without any issues. Trying to understand what's happening here...
2020-04-20 14:02:36.103 EDT,5e9dbe0e.990,126,,,,00000,LOG: checkpoint complete: wrote 112333 buffers (28.6%); 0 trans
2020-04-20 14:07:37.793 EDT,5e9dbe0e.990,131,,,,00000,LOG: checkpoint starting: xlog
2020-04-20 14:09:50.222 EDT,5e9dbe0e.990,132,,,,00000,LOG: checkpoint complete: wrote 112903 buffers (28.7%); 0 transaction log file(s) added, 0 removed, 44 recycled; write=132.240 s, sync=0.078 s, total=132.428 s; sync files=8, longest=0.034 s, average=0.010 s; distance=720594 kB, estimate=722102 kB
2020-04-20 14:10:03.574 EDT,5e9dbe0e.990,133,,,,00000,LOG: checkpoint starting: xlog
2020-04-20 14:19:03.160 EDT,5e9dbe0e.990,134,,,,00000,LOG: checkpoint complete: wrote 113066 buffers (28.8%); 0 transaction log file(s) added, 0 removed, 44 recycled; write=539.492 s, sync=0.000 s, total=539.586 s; sync files=8, longest=0.004 s, average=0.001 s; distance=721542 kB, estimate=722046 kB
2020-04-20 14:20:03.172 EDT,5e9dbe0e.990,135,,,,00000,LOG: checkpoint starting: time
2020-04-20 14:29:03.255 EDT,5e9dbe0e.990,136,,,,00000,LOG: checkpoint complete: wrote 97752 buffers (24.9%); 0 transaction log file(s) added, 0 removed, 44 recycled; write=539.911 s, sync=0.000 s, total=540.083 s; sync files=7, longest=0.004 s, average=0.001 s; distance=623883 kB, estimate=712230 kB
2020-04-20 14:07:37.793 EDT,5e9dbe0e.990,131,,,,00000,LOG: checkpoint starting: xlog
2020-04-20 14:09:50.222 EDT,5e9dbe0e.990,132,,,,00000,LOG: checkpoint complete: wrote 112903 buffers (28.7%); 0 transaction log file(s) added, 0 removed, 44 recycled; write=132.240 s, sync=0.078 s, total=132.428 s; sync files=8, longest=0.034 s, average=0.010 s; distance=720594 kB, estimate=722102 kB
2020-04-20 14:10:03.574 EDT,5e9dbe0e.990,133,,,,00000,LOG: checkpoint starting: xlog
2020-04-20 14:19:03.160 EDT,5e9dbe0e.990,134,,,,00000,LOG: checkpoint complete: wrote 113066 buffers (28.8%); 0 transaction log file(s) added, 0 removed, 44 recycled; write=539.492 s, sync=0.000 s, total=539.586 s; sync files=8, longest=0.004 s, average=0.001 s; distance=721542 kB, estimate=722046 kB
2020-04-20 14:20:03.172 EDT,5e9dbe0e.990,135,,,,00000,LOG: checkpoint starting: time
2020-04-20 14:29:03.255 EDT,5e9dbe0e.990,136,,,,00000,LOG: checkpoint complete: wrote 97752 buffers (24.9%); 0 transaction log file(s) added, 0 removed, 44 recycled; write=539.911 s, sync=0.000 s, total=540.083 s; sync files=7, longest=0.004 s, average=0.001 s; distance=623883 kB, estimate=712230 kB
The transaction still active in pg_stat_activity but not writing on disk since last checkpoint complete. Non errors after last checkpoint. Also no errors in logs on Oracle side.
Any help or hints would be greatly appreciated.
Thank in advance!
Yannick
