1

The attempt of making changes to max_stack_depth using select set_config('max_stack_depth', '512MB', false); because of the error shown in postgresql log:

DETAIL:  "max_stack_depth" must not exceed 7680kB.
HINT:  Increase the platform's stack depth limit via "ulimit -s" or local equivalent.

actually, the below line exists in limits.conf. Have tried updating postgresql process using prlimit and even reboot the server(OS: Ubuntu). It still reports the same error.

*         soft stack   524288

if switch to the user postgres and verify the stack size with ulimit -s it is already set to 512MB.

This shows there is no ulimit -s to change the stack size under /etc.

$ sudo grep -r ulimit /etc/*
/etc/init.d/lxd:        ulimit -n 65536
/etc/vmware-tools/vm-support:runcmd "/tmp/ulimit-a.txt" ulimit -a

Not sure why postgresql still not seeing this change?

Improve this question
  • 1
    Perhaps your start-up script is doing something the lower the limit again. Can you start the PostgreSQL server manually from the same shell in which you checked ulimit -s and see if the problme persists? – jjanes Feb 26 '19 at 15:34
  • Yeah. If switching to user postgresl and start the pg manually then it works! But it still confuses me even start-up script has changed it during boot process, but after switching to the user postgres, the limit is takes effect only after user login. So what's the right approach to set stack size ulimit for postgresql on Linux? – canoe Feb 27 '19 at 4:26
  • I don't know. I think you might get more help over in serverfault or askubuntu. It seems like a question more about the OS than about the database. Are you using PostgreSQL as installed from their repository and with their start-up scripts? – jjanes Feb 27 '19 at 14:18
  • PostgreSQL is installed from its offical repo by using apt-get as suggested from the doc on postgresql.org. – canoe Feb 28 '19 at 2:50
2

This solution works on Fedora Linux, but should work on Ubuntu as well. The issue is, that in addition to replacing initd, systemd aims to take over security functions that were traditionally handled by things like /etc/security/limits.conf. (There are other areas where it interferes as well). When systemd starts PostgreSQL, it ignores /etc/security/limits.conf, and applies its own default limits. To change them, do the following:

mkdir /etc/systemd/system/postgresql.service.d
cat > /etc/systemd/system/postgresql.service.d/postgresql.conf << XYZZY
[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
LimitSTACK=infinity
XYZZY

systemctl daemon-reload

This method also has the added benefit that will not interfere with default changes to service unit files, should they be updated upstream, but will permanently maintain your settings override. This helpful post lists the other limits that may be set through systemd configuration.

I should note that, at first, I tried to set the limit to 131072, but when I attempted to start PostgreSQL, I got this error:

DETAIL:  "max_stack_depth" must not exceed -384kB.

Changing it to infinity allowed PostgreSQL to start.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.