Re: Server goes to Recovery Mode when run a SQL - Mailing list pgsql-general

Well, now we have two queries which stops completelly our postgres server.
That problem occurs on 10.6 and 11.1 versions.
On both server the problem is the same.

Linux logs of old crash are:

Feb  1 18:39:53 fx-cloudserver kernel: [  502.405788] show_signal_msg: 5 callbacks suppressed
Feb  1 18:39:53 fx-cloudserver kernel: [  502.405791] postgres[10195]: segfault at 24 ip 0000555dc6a71cb0 sp 00007ffc5f91db38 error 4 in postgres[555dc69b4000+6db000]

Postgres log of old crash:

2019-02-01 18:39:53.229 -02,,,1523,,5c54aca9.5f3,3,,2019-02-01 18:31:37 -02,,0,LOG,00000,"server process (PID 10195) was terminated by signal 11: Segmentation fault","Failed process was running: WITH   StatusTrabalhando(Intkey) AS (SELECT Intkey FROM sys_Lookup WHERE Grupo = $$StatusDigitacao$$ AND Intkey in (SELECT unnest(string_to_array(substring(VarValue FROM 3),$$,$$)) FROM sys_Var WHERE Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$)),  StatusAgendados(Intkey) AS (SELECT Intkey FROM sys_Lookup WHERE Grupo = $$StatusVisita$$ AND Intkey in (SELECT unnest(string_to_array(substring(VarValue FROM 3),$$,$$)) FROM sys_Var WHERE Name = $$/Company/StatusDigitacaoReceptivoAgendados$$)                                                               AND Intkey NOT in (SELECT unnest(string_to_array(substring(VarValue FROM 3),$$,$$)) FROM sys_Var WHERE Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)),  Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade, Cadastro, Email, Idade, Obs,        Extra1, Extra2, Extra3, Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status",,,,,,,,""
2019-02-01 18:39:53.229 -02,,,1523,,5c54aca9.5f3,4,,2019-02-01 18:31:37 -02,,0,LOG,00000,"terminating any other active server processes",,,,,,,,,""

Postgres logs of new crash:

2019-02-08 17:21:16.650 -02,,,16321,,5c4f39b8.3fc1,25625,,2019-01-28 15:19:52 -02,,0,LOG,00000,"server process (PID 10321) was terminated by signal 11: Segmentation fault","Failed process was running: with
StatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusDigitacao$$ and
Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$))
from sys_Var where Name =
$$/Company/StatusDigitacaoReceptivoTrabalhando$$)),
StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusVisita$$ and Intkey
in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from
sys_Var where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) and Intkey not in (select unnest(string_to_array(substring(VarValue from
3),$$,$$)) from sys_Var where Name =
$$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)),
Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID,
Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo,
Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4,
Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus,
lkFonte, lkDigitador, lkPesquisador",,,,,,,,""
2019-02-08 17:21:16.650 -02,,,16321,,5c4f39b8.3fc1,25626,,2019-01-28 15:19:52 -02,,0,LOG,00000,"terminating any other active server processes",,,,,,,,,""
2019-02-08 17:21:16.650 -02,"postgres","f10db",10138,"74.125.92.65:44342",5c5dcd7d.279a,2,"idle",2019-02-08 16:42:05 -02,107/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,""
2019-02-08 17:21:16.650 -02,"postgres","f10db",9667,"173.194.101.228:63516",5c5dbdee.25c3,2,"idle",2019-02-08 15:35:42 -02,20/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,""
2019-02-08 17:21:16.650 -02,"postgres","f10db",10096,"74.125.92.68:50186",5c5dcd77.2770,2,"idle",2019-02-08 16:41:59 -02,48/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,""
2019-02-08 17:21:16.651 -02,"postgres","f10db",9696,"74.125.115.163:48542",5c5dbe07.25e0,2,"idle",2019-02-08 15:36:07 -02,49/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,""
2019-02-08 17:21:16.652 -02,"postgres","f10db",9597,"192.168.1.111:57271",5c5dbae2.257d,1,"idle",2019-02-08 15:22:42 -02,4/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,"PostgreSQL JDBC Driver"
2019-02-08 17:21:16.652 -02,,,9567,,5c5db777.255f,1,,2019-02-08 15:08:07 -02,1/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,""
2019-02-08 17:21:16.652 -02,"postgres","f10db",9670,"74.125.45.164:58116",5c5dbdf0.25c6,2,"idle",2019-02-08 15:35:44 -02,23/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,""
2019-02-08 17:21:16.652 -02,"postgres","f10db",9611,"177.92.53.2:61645",5c5dbc25.258b,1,"idle",2019-02-08 15:28:05 -02,5/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,"PostgreSQL JDBC Driver"
2019-02-08 17:21:16.655 -02,,,16321,,5c4f39b8.3fc1,25627,,2019-01-28 15:19:52 -02,,0,LOG,00000,"archiver process (PID 9568) exited with exit code 1",,,,,,,,,""
2019-02-08 17:21:16.705 -02,,,16321,,5c4f39b8.3fc1,25628,,2019-01-28 15:19:52 -02,,0,LOG,00000,"all server processes terminated; reinitializing",,,,,,,,,""
2019-02-08 17:21:16.934 -02,,,10329,,5c5dd6ac.2859,1,,2019-02-08 17:21:16 -02,,0,LOG,00000,"database system was interrupted; last known up at 2019-02-08 17:06:37 -02",,,,,,,,,""
2019-02-08 17:21:46.478 -02,,,10329,,5c5dd6ac.2859,2,,2019-02-08 17:21:16 -02,,0,LOG,00000,"recovered replication state of node 4 to 8F/2913B4C0",,,,,,,,,""
2019-02-08 17:21:46.478 -02,,,10329,,5c5dd6ac.2859,3,,2019-02-08 17:21:16 -02,,0,LOG,00000,"database system was not properly shut down; automatic recovery in progress",,,,,,,,,""
2019-02-08 17:21:46.650 -02,,,10329,,5c5dd6ac.2859,4,,2019-02-08 17:21:16 -02,,0,LOG,00000,"redo starts at EF/3DCF0C10",,,,,,,,,""
2019-02-08 17:21:48.129 -02,,,10329,,5c5dd6ac.2859,5,,2019-02-08 17:21:16 -02,,0,LOG,00000,"redo done at EF/41B6A618",,,,,,,,,""
2019-02-08 17:21:48.129 -02,,,10329,,5c5dd6ac.2859,6,,2019-02-08 17:21:16 -02,,0,LOG,00000,"last completed transaction was at log time 2019-02-08 17:21:14.151441-02",,,,,,,,,""
2019-02-08 17:22:36.479 -02,,,16321,,5c4f39b8.3fc1,25629,,2019-01-28 15:19:52 -02,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,,""
2019-02-08 17:22:36.533 -02,,,10340,,5c5dd6fc.2864,1,,2019-02-08 17:22:36 -02,3/2,0,LOG,00000,"logical replication apply worker for subscription ""sub_google_all"" has started",,,,,,,,,""

Linux Log of new crash, which takes several minutes to stop:

Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643121] postgres invoked oom-killer: gfp_mask=0x24280ca, order=0, oom_score_adj=0
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643123] postgres cpuset=/ mems_allowed=0
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643127] CPU: 0 PID: 9399 Comm: postgres Not tainted 4.4.0-138-generic #164-Ubuntu
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643128] Hardware name: Dell Inc. OptiPlex 7010/0773VG, BIOS A25 05/10/2017
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643129]  0000000000000286 02ce01de1fa33dca ffff8800de3cfaf8 ffffffff81404fe3
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643131]  ffff8800de3cfcb0 ffff880408d80000 ffff8800de3cfb68 ffffffff8121425e
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643133]  0000000000000015 0000000000000000 ffff8804083f49c0 ffff880408cbd400
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643134] Call Trace:
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643140]  [] dump_stack+0x63/0x90
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643144]  [] dump_header+0x5a/0x1c5
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643147]  [] ? apparmor_capable+0xc4/0x1b0
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643149]  [] oom_kill_process+0x202/0x3c0
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643151]  [] out_of_memory+0x219/0x460
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643154]  [] __alloc_pages_slowpath.constprop.88+0x943/0xaf0
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643156]  [] __alloc_pages_nodemask+0x288/0x2a0
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643158]  [] alloc_pages_vma+0xad/0x250
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643161]  [] handle_mm_fault+0x1420/0x1b70
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643164]  [] __do_page_fault+0x1a4/0x410
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643165]  [] do_page_fault+0x22/0x30
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643169]  [] page_fault+0x28/0x30
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643170] Mem-Info:
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643173] active_anon:3535478 inactive_anon:424526 isolated_anon:32
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643173]  active_file:679 inactive_file:594 isolated_file:0
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643173]  unevictable:0 dirty:0 writeback:45 unstable:0
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643173]  slab_reclaimable:43397 slab_unreclaimable:8228
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643173]  mapped:529649 shmem:851835 pagetables:26133 bounce:0
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643173]  free:33778 free_pcp:35 free_cma:0
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643175] Node 0 DMA free:15880kB min:64kB low:80kB high:96kB active_anon:0kB inactive_anon:0kB active_file:0kB inactive_file:0kB unevictable:0kB isolated(anon):0kB isolated(file):0kB present:15984kB managed:15896kB mlocked:0kB dirty:0kB writeback:0kB mapped:0kB shmem:0kB slab_reclaimable:0kB slab_unreclaimable:16kB kernel_stack:0kB pagetables:0kB unstable:0kB bounce:0kB free_pcp:0kB local_pcp:0kB free_cma:0kB writeback_tmp:0kB pages_scanned:0 all_unreclaimable? yes
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643179] lowmem_reserve[]: 0 3437 15947 15947 15947
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643181] Node 0 DMA32 free:64520kB min:14552kB low:18188kB high:21828kB active_anon:2842836kB inactive_anon:568288kB active_file:640kB inactive_file:504kB unevictable:0kB isolated(anon):0kB isolated(file):0kB present:3640260kB managed:3559444kB mlocked:0kB dirty:0kB writeback:64kB mapped:628652kB shmem:682620kB slab_reclaimable:41856kB slab_unreclaimable:7976kB kernel_stack:736kB pagetables:21384kB unstable:0kB bounce:0kB free_pcp:0kB local_pcp:0kB free_cma:0kB writeback_tmp:0kB pages_scanned:7160 all_unreclaimable? yes
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643185] lowmem_reserve[]: 0 0 12510 12510 12510
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643186] Node 0 Normal free:54712kB min:52964kB low:66204kB high:79444kB active_anon:11299076kB inactive_anon:1129816kB active_file:2076kB inactive_file:1872kB unevictable:0kB isolated(anon):128kB isolated(file):0kB present:13074432kB managed:12810364kB mlocked:0kB dirty:0kB writeback:116kB mapped:1489944kB shmem:2724720kB slab_reclaimable:131732kB slab_unreclaimable:24920kB kernel_stack:2480kB pagetables:83148kB unstable:0kB bounce:0kB free_pcp:140kB local_pcp:20kB free_cma:0kB writeback_tmp:0kB pages_scanned:33152 all_unreclaimable? yes
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643190] lowmem_reserve[]: 0 0 0 0 0
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643192] Node 0 DMA: 0*4kB 1*8kB (U) 0*16kB 0*32kB 2*64kB (U) 1*128kB (U) 1*256kB (U) 0*512kB 1*1024kB (U) 1*2048kB (M) 3*4096kB (M) = 15880kB
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643199] Node 0 DMA32: 2099*4kB (UME) 1396*8kB (UME) 1396*16kB (UME) 469*32kB (UME) 106*64kB (UME) 17*128kB (E) 0*256kB 0*512kB 0*1024kB 0*2048kB 0*4096kB = 65868kB
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643205] Node 0 Normal: 13326*4kB (UEH) 0*8kB 14*16kB (H) 2*32kB (H) 6*64kB (H) 5*128kB (H) 0*256kB 0*512kB 0*1024kB 0*2048kB 0*4096kB = 54616kB
Feb  8 15:06:40 fxReplicationServer kernel: [1363901.643211] Node 0 hugepages_total=0 hugepages_free=0 hugepages_surp=0 hugepages_size=2048kB
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643212] 853390 total pagecache pages
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643213] 330 pages in swap cache
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643214] Swap cache stats: add 580602, delete 580272, find 30643927/30685696
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643215] Free swap  = 0kB
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643216] Total swap = 999420kB
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643216] 4182669 pages RAM
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643217] 0 pages HighMem/MovableOnly
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643218] 86243 pages reserved
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643218] 0 pages cma reserved
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643219] 0 pages hwpoisoned
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643220] [ pid ]   uid  tgid total_vm      rss nr_ptes nr_pmds swapents oom_score_adj name
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643223] [  309]     0   309    10866     2186      25       3       49             0 systemd-journal
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643225] [  356]     0   356    25742        0      18       3       65             0 lvmetad
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643227] [  377]     0   377    11394       11      24       3      497         -1000 systemd-udevd
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643228] [  634]   100   634    25081        0      20       3       71             0 systemd-timesyn
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643230] [  735]   104   735    64098       38      28       3      168             0 rsyslogd
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643231] [  745]     0   745    68967       74      38       3      112             0 accounts-daemon
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643233] [  749]     0   749     7253       19      20       3       50             0 cron
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643234] [  751]     0   751     7155       37      18       3       43             0 systemd-logind
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643236] [  752]   106   752    10722       51      26       3       59          -900 dbus-daemon
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643237] [  818]     0   818     4892       27      15       3       36             0 irqbalance
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643239] [  877]     0   877     4030       51      11       3      164             0 dhclient
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643240] [  922]     0   922     3985        0      13       3       38             0 agetty
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643242] [  926]     0   926    16377       29      36       4      150         -1000 sshd
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643244] [16321]   109 16321  1127906    21773     138       5      306          -900 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643245] [16322]   109 16322    41437      162      63       4      321             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643247] [ 5603]   109  5603    11320        0      26       3      207             0 systemd
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643248] [ 5605]   109  5605    15315       18      31       3      455             0 (sd-pam)
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643250] [22681]   109 22681  1130447   520116    2165       8      860             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643251] [22682]   109 22682  1128404   516014    2156       8      362             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643253] [22683]   109 22683  1127906     2732      75       5      316             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643254] [22684]   109 22684  1128158      345      79       5      366             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643256] [22685]   109 22685    41967      171      64       5      313             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643257] [22686]   109 22686    47349     3760      73       5      334             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643259] [22687]   109 22687  1128120      426      79       5      381             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643260] [ 2507]     0  2507    23207       46      52       3      185             0 sshd
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643262] [ 2528]   109  2528    23207       60      49       3      178             0 sshd
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643264] [ 9079]   109  9079  1193045   562876    2298       8     6844             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643265] [ 9081]   109  9081  1128224     1614      98       6      457             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643267] [26521]   109 26521  1128285     5423     111       8      513             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643268] [26522]   109 26522  1128285     4679     135       8      487             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643270] [ 5194]   109  5194  1129913    16312     287       8      828             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643271] [ 7101]   109  7101  1157957   286907    2223       8      324             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643273] [ 8936]   109  8936  1129795    27817     506       8      248             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643274] [ 8979]   109  8979  1128946    16452     376       8      265             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643276] [ 9008]   109  9008  1128850    16359     375       8      266             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643277] [ 9101]   109  9101  1129373    12880     244       8      245             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643279] [ 9328]     0  9328    23207      234      50       3        0             0 sshd
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643280] [ 9349]   109  9349    23207      240      49       3        0             0 sshd
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643282] [ 9351]   109  9351     5555      362      16       3        0             0 bash
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643283] [ 9362]   109  9362     6649      320      19       3        0             0 htop
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643285] [ 9365]   109  9365     5554      365      16       3        0             0 bash
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643286] [ 9399]   109  9399  4129624  3008886    6087      21      267             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643288] [ 9438]   109  9438  1128844    16432     381       8      229             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643289] [ 9441]   109  9441  1128801    16306     375       8      265             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643291] [ 9443]   109  9443  1129663    21678     433       8      247             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643292] [ 9449]   109  9449  1128833    16359     378       8      265             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643294] [ 9452]   109  9452  1128945    16437     377       8      265             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643295] [ 9484]   109  9484  1128850    16401     378       8      266             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643296] [ 9485]   109  9485  1128827    16380     377       8      265             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643298] [ 9486]   109  9486  1128801    16381     378       8      231             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643299] [ 9487]   109  9487  1128841    16342     376       8      265             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643301] [ 9488]   109  9488  1128801    16336     375       8      265             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643302] [ 9489]   109  9489  1129799    28325     506       8      248             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643304] [ 9490]   109  9490  1129662    21623     432       8      247             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643305] [ 9491]   109  9491  1128785    16300     373       8      267             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643307] [ 9492]   109  9492  1128945    16445     377       8      265             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643308] [ 9493]   109  9493  1128833    16388     377       8      265             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643309] [ 9494]   109  9494  1128830    16370     375       8      265             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643311] [ 9495]   109  9495  1129018    16529     378       8      265             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643312] [ 9496]   109  9496  1128832    16385     376       8      265             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643314] [ 9498]   109  9498  1128866    16371     376       8      265             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643315] [ 9547]   109  9547  1132672    63812     490       8      283             0 postgres
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643316] Out of memory: Kill process 9399 (postgres) score 693 or sacrifice child
Feb  8 15:06:41 fxReplicationServer kernel: [1363901.643368] Killed process 9399 (postgres) total-vm:16518496kB, anon-rss:11997448kB, file-rss:38096kB
Feb  8 17:21:16 fxReplicationServer kernel: [1371977.845728] postgres[10321]: segfault at 10 ip 00005567a6069752 sp 00007ffed70be970 error 4 in postgres[5567a5e1a000+727000]

PostgreSQL 10.6 (Ubuntu 10.6-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit
PostgreSQL 11.1 (Ubuntu 11.1-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit

And this new query which stops the server too but differently because the old one stops immediatelly instead of this, as you can see images.

This query runs for aproximately 5 minutes. See link above with images and logs and you´ll see how memory will grow. Memory use starts with 8gb e grows until use them all. When all memory is in use then it starts to swap. When all swap is allocated then it gets the "out of memory" and stops completelly. You'll see that this query has generate_series, with, recursive and lateral statements. It was bad written because that generate_series generates a enormous quantity of records, probably millions of them. But a wrong query couldn´t stop the entire server, could ?

images and logs are available on: https://drive.google.com/open?id=18zIvkV3ew4aZ1_cxI-EmIPVql7ydvEwi

EXPLAIN ANALYSE
WITH feriados as (    select dia, (sum(repete) > 0) repete from (           select data dia, repete from sys_feriado FeriadoPeriodo where FeriadoPeriodo.repete = 0 and FeriadoPeriodo.tipo in (2, 3) UNION all           select to_date(to_char(current_date, 'yyyy-')|| to_char(data, 'mm-dd'), 'yyyy-mm-dd') dia, repete           from sys_feriado FeriadoAnual where FeriadoAnual.repete = 1 and FeriadoAnual.tipo in (2, 3) ) feriados group by 1 order by 1
), materias (turma_id, materia_id,                           materia, sequencia, previsto,                    dataini,                    datafim, tempoatividade, minutosaula, minutosrestantes) AS (   select        593,      11091, 'AAC - Ética e Segurança Digital',         9,     120, cast('2019/01/30' as data), cast('2019/01/30' as data),             60,         120,                 0 union   select 593, 11085, 'AAC - Computação nas Nuvens', 12, 60, cast(null as data), cast(null as data), 60, 120, 60
), aulasporsemana (turma_id, quantidade) as ( select turma_id, count(*) from col_diasaula WHERE turma_id = (select turma_id from materias limit 1) group by 1
), assistidas (turma_id, sequencia, conteudo_id, conteudo, prevista, aularealizada, tempoatividade, aulasNoDia, dia, minutoaula, minutosassistidos, cargaconteudo, cargarestante) as ( SELECT materias.turma_id, materias.sequencia, materias.materia_id, materias.materia, coalesce(realizada.prevista, 1), realizada.aularealizada, materias.tempoatividade, (realizada.minutosassistidos / materias.tempoatividade), realizada.dia, materias.minutosaula, realizada.minutosassistidos, materias.previsto, coalesce(materias.previsto - (row_number() OVER AulaDaMateria * realizada.minutosassistidos), materias.previsto) restante
FROM materias LEFT JOIN LATERAL (   SELECT     true aularealizada,     tsrange(col_aula.data, (col_aula.data + (col_aula.tempo|| ' minute')::interval)) dia, 0 prevista,     (extract(EPOCH FROM col_aula.tempo) / 60) minutosassistidos   FROM col_aula   WHERE col_aula.materia_id = materias.materia_id AND col_aula.turma_id = materias.turma_id   order by col_aula.data, sequencia ) realizada ON TRUE WINDOW AulaDaMateria as (PARTITION BY materias.materia_id ORDER BY materias.turma_id, materias.datafim NULLS LAST, materias.dataini NULLS LAST, materias.sequencia, materias.materia_id), AulaDia as (PARTITION BY materias.materia_id, realizada.dia) ORDER BY turma_id, datafim NULLS LAST, dataini NULLS LAST, sequencia, materia_id
)
SELECT
*
FROM ( with recursive aulas as (   SELECT     turma_id,     aularealizada,     coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1) over aulas_realizar, 0) > cargaconteudo irregular,     coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1) over aulas_realizar, 0) assistido_ate_agora,     CASE       WHEN prevista = 0 AND NOT (coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1) OVER aulas_realizar, 0) > cargaconteudo) THEN         (cargaconteudo / tempoatividade) - (lag(aulasNoDia, 1) OVER aulas_realizar + aulasNoDia)       WHEN prevista = 1 THEN         (cargaconteudo / tempoatividade)       ELSE 0     END aulas,     case       when prevista = 0 and not (coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1) over aulas_realizar, 0) > cargaconteudo) then         (cargaconteudo / tempoatividade) - (lag(aulasNoDia, 1) over aulas_realizar + aulasNoDia)       else 1     END proxima,     prevista,     upper(dia) ultimadata,     conteudo_id,     conteudo,     cargaconteudo,     cargarestante,     tempoatividade,     dia,     minutosassistidos,     minutoaula,     sequencia   FROM assistidas   JOIN      aulasporsemana USING (turma_id)   WINDOW aulas_realizar AS (PARTITION BY conteudo_id)   UNION   select     turma_id, datas.aularealizada, irregular, assistido_ate_agora, aulas, proxima, prevista, ultimadata, datas.conteudo_id, conteudo, cargaconteudo, datas.cargarestante, tempoatividade, dia, datas.minutosassistidos, minutoaula, sequencia     from aulas c    JOIN LATERAL (     select       Format('%s week', coalesce(c.aulas, 0)) semanas,       false aularealizada,       c.conteudo_id,       tsrange(generate_series, generate_series + (minutoaula|| ' minute')::interval) diacalculado,       cargarestante - (row_number() OVER () * (extract(epoch from col_diasaula.tempoaula) / 60)) cargarestante,       (case c.prevista when 1 then row_number() OVER () else 1 end * (extract(epoch from col_diasaula.tempoaula) / c.tempoatividade)) minutosassistidos     from       generate_series(c.ultimadata - interval '1 day', (c.ultimadata + Format('%s week', coalesce(proxima, 0))::INTERVAL), '1 day')     join col_diasaula on col_diasaula.dia = (extract(dow from generate_series) +1) and col_diasaula.turma_id = c.turma_id     ) datas on TRUE   where datas.conteudo_id = c.conteudo_id and c.aulas is not null and coalesce(c.proxima, -1) >= 0
) select * from aulas
) valores;