checkpoint starting: time (insert statement hangs

From: Yannick Collette <yannickcollette(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: checkpoint starting: time (insert statement hangs
Date: 2020-04-21 16:10:06
Message-ID: CADTm7M97VqCXhB5Ku+OPGH=yVe6F7J0m4DPW_nHSiDUdwN0qWg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Laurenz Albe 2020-04-21 19:39:05 Re: checkpoint starting: time (insert statement hangs
Previous Message pinker 2020-04-21 10:43:21 Re: Exporting TBs of data in Oracle schema/database into CSV format to migrate into PostgreSQL