On Tue, 2020-04-21 at 12:10 -0400, Yannick Collette wrote: > 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 > > 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. How does pg_stat_activity look? Is there an entry with "granted = FALSE" in pg_locks? What is the PostgreSQL query and the Oracle query? What is the state of the statement in Oracle? Is it running, blocked or waiting for the client? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
