Stale connection from other unit

[honghan-wong]

Steps to reproduce

1. deploy landscape server and postgresql in 3 units
2. relate the applications
3. wait

Expected behavior

No oom happened

Actual behavior

postgresql did not cleanup replication process and keeping connection IDLE, causing OOM after some time.

Versions

Operating system: Ubuntu 22.04.5 LTS

Juju CLI: 3.5.7-genericlinux-amd64

Juju agent: 3.5.4

Charm revision:

landscape-server  latest/stable  121
postgresql        14/stable      468

LXD:

Log output

Juju debug log:

Additional context

output from different postgresql unit

ubuntu@prod-landscape-server-1:~$ ps -ef | grep postgres | grep idle | wc -l
131
ubuntu@prod-landscape-server-2:~$ ps -ef | grep postgres | grep idle | wc -l
3
ubuntu@prod-landscape-server-3:~$ ps -ef | grep postgres | grep idle | wc -l
18

top output from one of the postgresql server

ubuntu@prod-landscape-server-1:~$ top -b -n 1 -c -o +%MEM | head -n 50
top - 03:06:08 up 40 days, 24 min,  2 users,  load average: 6.58, 6.81, 6.85
Tasks: 336 total,   5 running, 331 sleeping,   0 stopped,   0 zombie
%Cpu(s): 71.6 us, 14.8 sy,  0.0 ni, 13.6 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
MiB Mem :  15939.2 total,    312.7 free,   7798.6 used,   7827.9 buff/cache
MiB Swap:      0.0 total,      0.0 free,      0.0 used.   3461.9 avail Mem 

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
3052501 snap_da+  20   0 4489328   4.1g   4.1g S   5.6  26.7  53:59.72 postgres: postgresql: checkpointer
3259078 snap_da+  20   0 4644852   3.1g   3.1g S   0.0  19.9   1:41.69 postgres: postgresql: landscape landscape-standalone-package 10.51.12.216(57146) idle
1317866 snap_da+  20   0 4673220   3.1g   3.0g S   0.0  19.7   1:11.57 postgres: postgresql: landscape landscape-standalone-package 10.51.12.214(58282) idle
3528608 snap_da+  20   0 4642912   3.0g   3.0g S   0.0  19.4   1:04.98 postgres: postgresql: landscape landscape-standalone-package 10.51.12.214(54604) idle
3513743 snap_da+  20   0 4644856   3.0g   3.0g S   0.0  19.3   1:40.51 postgres: postgresql: landscape landscape-standalone-package 10.51.12.214(37726) idle
3260014 snap_da+  20   0 4657068   3.0g   2.9g S   0.0  19.1   1:07.80 postgres: postgresql: landscape landscape-standalone-package 10.51.12.216(60630) idle
3578099 snap_da+  20   0 4648528   2.9g   2.8g S   0.0  18.3   1:07.44 postgres: postgresql: landscape landscape-standalone-package 10.51.12.214(42790) idle
3286018 snap_da+  20   0 4648876   2.8g   2.8g S   0.0  18.1   1:35.82 postgres: postgresql: landscape landscape-standalone-package 10.51.12.216(42346) idle
4053385 snap_da+  20   0 4640860   2.7g   2.7g S   0.0  17.6   0:35.40 postgres: postgresql: landscape landscape-standalone-package 10.51.12.216(60346) idle
4152484 snap_da+  20   0 4644588   2.7g   2.6g S   0.0  17.2   0:33.98 postgres: postgresql: landscape landscape-standalone-package 10.51.12.214(52184) idle
3286014 snap_da+  20   0 4589972   2.6g   2.5g S   0.0  16.7  16:39.16 postgres: postgresql: landscape landscape-standalone-resource-1 10.51.12.216(42344) idle
3513737 snap_da+  20   0 4579052   2.6g   2.5g S   0.0  16.6  18:40.90 postgres: postgresql: landscape landscape-standalone-resource-1 10.51.12.214(37710) idle
3259072 snap_da+  20   0 4589848   2.6g   2.5g S   0.0  16.5  16:09.84 postgres: postgresql: landscape landscape-standalone-resource-1 10.51.12.216(57142) idle
3258925 snap_da+  20   0 4588268   2.5g   2.4g S   0.0  16.2  13:36.75 postgres: postgresql: landscape landscape-standalone-resource-1 10.51.12.216(50724) idle
3260009 snap_da+  20   0 4581616   2.5g   2.4g S   0.0  16.2  13:53.37 postgres: postgresql: landscape landscape-standalone-resource-1 10.51.12.216(60624) idle
3528607 snap_da+  20   0 4571304   2.4g   2.4g S   0.0  15.7  12:53.51 postgres: postgresql: landscape landscape-standalone-resource-1 10.51.12.214(54590) idle
3578098 snap_da+  20   0 4579940   2.4g   2.4g S   0.0  15.6  12:32.76 postgres: postgresql: landscape landscape-standalone-resource-1 10.51.12.214(42780) idle
3258932 snap_da+  20   0 4644820   2.4g   2.3g S   0.0  15.2   1:08.37 postgres: postgresql: landscape landscape-standalone-package 10.51.12.216(57048) idle
2088419 snap_da+  20   0 4599980   2.4g   2.4g S   0.0  15.2   0:06.24 postgres: postgresql: landscape landscape-standalone-package 10.51.12.215(38720) idle
1317825 snap_da+  20   0 4540524   2.3g   2.2g S   0.0  14.6  12:28.23 postgres: postgresql: landscape landscape-standalone-resource-1 10.51.12.214(58278) idle
4052210 snap_da+  20   0 4579196   1.9g   1.9g S   0.0  12.5   6:06.86 postgres: postgresql: landscape landscape-standalone-resource-1 10.51.12.216(44528) idle
4152440 snap_da+  20   0 4587328   1.9g   1.8g S   0.0  12.2   5:17.51 postgres: postgresql: landscape landscape-standalone-resource-1 10.51.12.214(38588) idle
3948123 snap_da+  20   0 4600472   1.9g   1.9g S   0.0  12.2   0:06.67 postgres: postgresql: landscape landscape-standalone-package 10.51.12.214(58386) idle
3948115 landsca+  20   0 4575440   1.7g   3840 S   0.0  11.2  33:48.54 /opt/canonical/landscape/go/bin/packagesearch -config /etc/landscape/service.conf
3258922 snap_da+  20   0 4491884   1.4g   1.4g S   0.0   9.1  31:01.37 postgres: postgresql: landscape landscape-standalone-main 10.51.12.216(50710) idle
3259071 snap_da+  20   0 4491836   1.4g   1.4g S   0.0   8.9  35:56.80 postgres: postgresql: landscape landscape-standalone-main 10.51.12.216(57136) idle
3260003 snap_da+  20   0 4491508   1.4g   1.4g S   0.0   8.9  30:43.49 postgres: postgresql: landscape landscape-standalone-main 10.51.12.216(60614) idle
3286011 snap_da+  20   0 4491540   1.4g   1.4g S   0.0   8.8  36:36.45 postgres: postgresql: landscape landscape-standalone-main 10.51.12.216(42330) idle
3513736 snap_da+  20   0 4491496   1.4g   1.3g S   0.0   8.7  39:25.64 postgres: postgresql: landscape landscape-standalone-main 10.51.12.214(37700) idle
3578097 snap_da+  20   0 4491600   1.4g   1.3g S   0.0   8.7  30:08.05 postgres: postgresql: landscape landscape-standalone-main 10.51.12.214(42776) idle
3528606 snap_da+  20   0 4491460   1.3g   1.3g S   0.0   8.7  30:02.31 postgres: postgresql: landscape landscape-standalone-main 10.51.12.214(54588) idle
3259606 snap_da+  20   0 4491780   1.3g   1.3g S   0.0   8.4  19:26.66 postgres: postgresql: landscape landscape-standalone-main 10.51.12.216(38084) idle
1317824 snap_da+  20   0 4491804   1.3g   1.3g S   0.0   8.4  29:58.80 postgres: postgresql: landscape landscape-standalone-main 10.51.12.214(58274) idle
3259710 snap_da+  20   0 4491700   1.3g   1.3g S   0.0   8.3  19:37.26 postgres: postgresql: landscape landscape-standalone-main 10.51.12.216(38116) idle
3260263 snap_da+  20   0 4492356   1.3g   1.3g S   0.0   8.3  19:34.69 postgres: postgresql: landscape landscape-standalone-main 10.51.12.216(45932) idle
4052168 snap_da+  20   0 4491332   1.3g   1.2g S   0.0   8.0  13:31.58 postgres: postgresql: landscape landscape-standalone-main 10.51.12.216(44518) idle
4152430 snap_da+  20   0 4491840   1.2g   1.2g S   0.0   8.0  12:53.97 postgres: postgresql: landscape landscape-standalone-main 10.51.12.214(38562) idle
3260304 snap_da+  20   0 4492348   1.2g   1.2g S   0.0   8.0  19:30.56 postgres: postgresql: landscape landscape-standalone-main 10.51.12.216(45962) idle
2094300 snap_da+  20   0 4491704   1.2g   1.2g S   0.0   7.5   0:01.77 postgres: postgresql: landscape landscape-standalone-main 10.51.12.215(51270) idle
2094428 snap_da+  20   0 4491664   1.2g   1.2g S   0.0   7.5   0:01.64 postgres: postgresql: landscape landscape-standalone-main 10.51.12.215(51290) idle
2093376 snap_da+  20   0 4491688   1.2g   1.2g S   0.0   7.5   0:01.50 postgres: postgresql: landscape landscape-standalone-main 10.51.12.215(38044) idle
2093393 snap_da+  20   0 4491684   1.2g   1.2g S   0.0   7.5   0:01.50 postgres: postgresql: landscape landscape-standalone-main 10.51.12.215(38074) idle
2092747 snap_da+  20   0 4491648   1.0g   1.0g S   0.0   6.5   0:06.46 postgres: postgresql: landscape landscape-standalone-main 10.51.12.215(35082) idle

[syncronize-issues-to-jira]

Thank you for reporting your feedback to us!

The internal ticket has been created: https://warthogs.atlassian.net/browse/DPE-6926.

This message was autogenerated

[dragomirp]

Hi, @honghan-wong, did you set the experimental_max_connections configuration on the Postgresql charm? If not, Landscape is using a lot of connections so the default 100 is not enough. Currently the landscape-scalable bundle sets it to 500.

[honghan-wong]

Hi, yes the postgresql charm has this configuration set to 500

$ juju config postgresql experimental_max_connections
500

[dragomirp]

HI, @honghan-wong, can you try to use the latest 14/stable revision (rev. 553)? Latest update fixes bugs with secrets, that may be the cause of the stuck connection.