checkpoint process is hang

Hello~

     I had a database which the last checkpoint time was Sep 29 14:25:49. And now the checkpoint is still hang.The postgresql’s version is 10.3.The host‘s version is Red Hat Enterprise Linux Server release 6.8 (Santiago)。

2019-09-29 14:20:46.157 CST,,,37651,,5d8a3322.9313,2665,,2019-09-24 23:15:46 CST,,0,LOG,00000,"checkpoint starting: time",,,,,,,,,""

2019-09-29 14:24:16.084 CST,,,37651,,5d8a3322.9313,2666,,2019-09-24 23:15:46 CST,,0,LOG,00000,"checkpoint complete: wrote 697421 buffers (13.3%); 0 WAL file(s) added, 0 removed, 3 recycled; write=209.903 s, sync=0.014 s, total=209.927 s; sync files=130, longest=0.001 s, average=0.000 s; distance=4959020 kB, estimate=5181294 kB",,,,,,,,,""

2019-09-29 14:25:46.179 CST,,,37651,,5d8a3322.9313,2667,,2019-09-24 23:15:46 CST,,0,LOG,00000,"checkpoint starting: time",,,,,,,,,""

The database has long transaction in pg_stat_activity.

The download is database process status. A lot of old process did not normal stoped.

Pgagent status is 110.

Timing is on.

PG is in recovery: false

Time: 1.276 ms

postgres  1610 37573  0 Sep27 ?        00:00:36 postgres: pgvicco: bgworker: parallel worker for PID 39675  

postgres  1611 37573  0 Sep27 ?        00:00:36 postgres: pgvicco: bgworker: parallel worker for PID 39675  

postgres  2780 37573  0 Oct08 ?        00:00:26 postgres: pgvicco: devsup01 pgvicco  SELECT

postgres  3679 37573  0 Sep29 ?        00:00:04 postgres: pgvicco: devsup01 pgvicco  SELECT

postgres  4119 37573  0 Oct05 ?        00:07:04 postgres: pgvicco: devsup01 pgvicco  SELECT

postgres  5130 37573  0 Sep29 ?        00:00:04 postgres: pgvicco: devsup01 pgvicco  SELECT

postgres  7309 37573  0 Sep27 ?        00:00:54 postgres: pgvicco: devsup01 pgvicco SELECT

postgres  8549 37573  0 Sep29 ?        00:00:00 postgres: pgvicco: devsup01 pgvicco  SELECT

postgres  8804 37573  0 Sep29 ?        00:00:07 postgres: pgvicco: devsup01 pgvicco SELECT

postgres 12061 37573  0 Oct08 ?        00:00:29 postgres: pgvicco: devsup01 pgvicco SELECT

postgres 12627 37573  0 Sep27 ?        00:00:42 postgres: pgvicco: bgworker: parallel worker for PID 7309  

postgres 12628 37573  0 Sep27 ?        00:00:42 postgres: pgvicco: bgworker: parallel worker for PID 7309  

postgres 13610 37573  0 Oct08 ?        00:00:05 postgres: pgvicco: devsup01 pgvicco  SELECT

postgres 13925 37573  0 Oct08 ?        00:02:36 postgres: pgvicco: devsup01 pgvicco SELECT

postgres 14275 37573  0 Sep30 ?        00:02:50 postgres: pgvicco: autovacuum worker process   pgvicco   

postgres 15691 37573  0 Oct09 ?        00:00:50 postgres: pgvicco: wal sender process repuser  sending backup "pg_basebackup base backup"

postgres 16662 37573  0 Oct10 ?        00:01:55 postgres: pgvicco: devsup01 pgvicco SELECT

postgres 16776 37573  0 Oct08 ?        00:00:00 postgres: pgvicco: lbdpimssqp pgvicco DELETE

postgres 18486 37573  0 Oct09 ?        00:00:14 postgres: pgvicco: devsup01 pgvicco SELECT

postgres 19094 37573  0 Sep29 ?        00:00:04 postgres: pgvicco: devsup01 pgvicco SELECT

postgres 19355 37573  0 Oct09 ?        00:34:30 postgres: pgvicco: devsup01 pgvicco  SELECT

postgres 20080 37573  0 Oct09 ?        00:01:48 postgres: pgvicco: devsup01 pgvicco  SELECT

postgres 21420 37573  0 Sep29 ?        00:00:03 postgres: pgvicco: devsup01 pgvicco  SELECT

postgres 21473 37573  0 Oct12 ?        00:00:18 postgres: pgvicco: lbdpimssqp pgvicco  SELECT

postgres 21475 37573  0 Oct12 ?        00:00:19 postgres: pgvicco: lbdpimssqp pgvicco  SELECT

postgres 21478 37573  0 Oct12 ?        00:00:18 postgres: pgvicco: lbdpimssqp pgvicco  SELECT

postgres 21482 37573  0 Oct12 ?        00:00:18 postgres: pgvicco: lbdpimssqp pgvicco  SELECT

postgres 22955 37573  0 Oct10 ?        00:03:45 postgres: pgvicco: devsup01 pgvicco  SELECT

postgres 24551 37573  0 Sep29 ?        00:00:35 postgres: pgvicco: devsup01 pgvicco  SELECT

postgres 24729 37573  0 Oct05 ?        00:06:24 postgres: pgvicco: autovacuum worker process   pgvicco   

postgres 26663 37573  0 Sep29 ?        00:00:00 postgres: pgvicco: bgworker: parallel worker for PID 21420  

postgres 26664 37573  0 Sep29 ?        00:00:00 postgres: pgvicco: bgworker: parallel worker for PID 21420   

postgres 26689 37573  0 Sep24 ?        00:00:02 postgres: pgvicco: bgworker: pglogical manager 17597     

postgres 26804 37573  0 Sep29 ?        00:00:16 postgres: pgvicco: devsup01 pgvicco  SELECT

postgres 27077 37573  0 Oct09 ?        00:00:08 postgres: pgvicco: devsup01 pgvicco  SELECT

postgres 27882 37573  0 Oct09 ?        00:02:14 postgres: pgvicco: devsup01 pgvicco  SELECT

postgres 27894 37573  0 Oct05 ?        00:00:08 postgres: pgvicco: pgviccodata pgvicco  DELETE

postgres 28468 37573  0 Oct09 ?        00:06:02 postgres: pgvicco: devsup01 pgvicco  SELECT

postgres 28496 37573  0 Oct05 ?        00:00:08 postgres: pgvicco: pgviccodata pgvicco  DELETE

postgres 29044 37573  0 Oct10 ?        00:00:16 postgres: pgvicco: devsup01 pgvicco  SELECT

postgres 29444 37573  0 Oct08 ?        00:00:05 postgres: pgvicco: devsup01 pgvicco SELECT

postgres 29717 37573  0 Sep29 ?        00:03:05 postgres: pgvicco: devsup01 pgvicco  SELECT

postgres 29788 37573  0 Oct05 ?        00:00:11 postgres: pgvicco: lbdpimssqp pgvicco  DELETE

postgres 31267 37573  0 Sep30 ?        00:00:00 postgres: pgvicco: lbdpimssqp pgvicco  DELETE

postgres 33786 37573  0 Sep29 ?        00:00:10 postgres: pgvicco: devsup01 pgvicco  SELECT

postgres 35747 37573  0 Sep29 ?        00:00:00 postgres: pgvicco: bgworker: parallel worker for PID 37579  

postgres 35748 37573  0 Sep29 ?        00:00:00 postgres: pgvicco: bgworker: parallel worker for PID 37579  

postgres 37369 37573  0 Oct10 ?        00:00:38 postgres: pgvicco: devsup01 pgvicco  SELECT

postgres 37573     1  0 Sep24 ?        00:02:39 /paic/postgres/base/10.3/bin/postgres -D /paic/pgxxxx/data

postgres 37579 37573  0 Sep29 ?        00:00:12 postgres: pgvicco: devsup01 pgvicco xxxx SELECT

postgres 37580 37573  0 Sep24 ?        00:02:38 postgres: pgvicco: logger process                        

postgres 37651 37573  0 Sep24 ?        00:20:02 postgres: pgvicco: checkpointer process                  

postgres 37652 37573  0 Sep24 ?        01:01:09 postgres: pgvicco: writer process                        

postgres 37653 37573  0 Sep24 ?        00:12:25 postgres: pgvicco: wal writer process                    

postgres 37654 37573  0 Sep24 ?        00:00:18 postgres: pgvicco: autovacuum launcher process           

postgres 37655 37573  0 Sep24 ?        00:00:15 postgres: pgvicco: archiver process   last was 0000000100002FB200000003

postgres 37656 37573  1 Sep24 ?        05:18:27 postgres: pgvicco: stats collector process               

postgres 37657 37573  0 Sep24 ?        00:00:00 postgres: pgvicco: bgworker: pglogical supervisor        

postgres 37659 37573  0 Sep24 ?        00:00:01 postgres: pgvicco: bgworker: logical replication launcher  

postgres 38167 37573  0 Oct09 ?        00:00:24 postgres: pgvicco: devsup01 pgvicco xxxx SELECT

postgres 39558 37573  0 Sep29 ?        00:00:10 postgres: pgvicco: lbdpimssqp pgvicco xxxx(xxx) INSERT

postgres 39675 37573  0 Sep27 ?        00:00:36 postgres: pgvicco: devsup01 pgvicco xxx (xxx) SELECT

postgres 39927 37573  0 Oct05 ?        00:06:58 postgres: pgvicco: autovacuum worker process   pgvicco 

1, We’d try command checkpoint,but It’s timeout.Strace the command ,checkpoint will send the kill sign to checkpoint process,which is timeout.

2, We’d find a bug ,the parallel query will make the query hang.

https://www.postgresql.org/message-id/flat/14580491539874286%40myt5-96980ae04900.qloud-c.yandex.net#b81db528ef322e1b153deaccbcfd03fc

For a database the checkpoint is so important .So I want to know why the checkpoint hangs? Did the had relation on the bug?

The stack of the checkpoint.

[postgres@xxxx:pgvicco:xxx ~]$ gstack 37651

#0  0x0000003bc480da00 in sem_wait () from /lib64/libpthread.so.0

#1  0x000000000069d890 in PGSemaphoreLock ()

#2  0x000000000070c3a5 in LWLockAcquire ()

#3  0x00000000006ee553 in SyncOneBuffer ()

#4  0x00000000006ee8d9 in BufferSync ()

#5  0x00000000006ee966 in CheckPointBuffers ()

#6  0x00000000004ea2cc in CheckPointGuts ()

#7  0x00000000004f2456 in CreateCheckPoint ()

#8  0x00000000006a4152 in CheckpointerMain ()

#9  0x00000000005020ca in AuxiliaryProcessMain ()

#10 0x00000000006ac127 in StartChildProcess ()

#11 0x00000000006ae73a in reaper ()

#12 <signal handler called>

#13 0x0000003bc44e1503 in __select_nocancel () from /lib64/libc.so.6

#14 0x00000000006b03fa in PostmasterMain ()

#15 0x0000000000631e70 in main ()