PGDATA为挂载卷时pg_restore执行失败的问题求助

场景描述 #

执行以下命令恢复PostgreSQL数据：

$ podman exec -u postgres mycontainer bash -c "pg_restore -v -U app -d mydb --no-owner < /tmp/backup.pgsql"

当未将PGDATA设置为挂载卷时，命令可正常执行；但使用以下命令挂载PGDATA目录后：

podman run -dt -v ./dev_data/postgres/:/var/lib/postgresql/data/ docker.io/library/postgres:13-alpine

pg_restore执行失败，出现大量文件描述符不足的错误，最终容器会在7-8分钟后退出。

注：挂载PGDATA卷后，常规psql命令可正常执行，说明容器能读写MacOS主机目录，且容器可保持存活、应用容器能正常连接。

错误日志 #

LOG:  out of file descriptors: No file descriptors available; release and retry
STATEMENT:  ALTER TABLE ONLY public.account_emailaddress
        ADD CONSTRAINT account_emailaddress_email_key UNIQUE (email);
...
...
...
ERROR:  could not stat directory "base/16386": No file descriptors available
STATEMENT:  ALTER TABLE ONLY public.account_emailaddress
        ADD CONSTRAINT account_emailaddress_email_key UNIQUE (email);
...
...
...
... [7-8分钟后]
...
...
LOG:  database system was interrupted; last known up at 2022-11-17 20:18:43 UTC
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 0/15D4980
FATAL:  could not stat directory "base/16386": No file descriptors available
CONTEXT:  WAL redo at 0/163D050 for Storage/CREATE: base/16386/16519
LOG:  startup process (PID 171) exited with exit code 1
LOG:  aborting startup due to startup process failure
LOG:  database system is shut down

环境信息 #

• Podman版本：4.3.1
• 容器（postgres:13-alpine）内ulimit -a输出：

core file size (blocks)         (-c) unlimited
data seg size (kb)              (-d) unlimited
scheduling priority             (-e) 0
file size (blocks)              (-f) unlimited
pending signals                 (-i) 7257
max locked memory (kb)          (-l) 8192
max memory size (kb)            (-m) unlimited
open files                      (-n) 1048576
POSIX message queues (bytes)    (-q) 819200
real-time priority              (-r) 0
stack size (kb)                 (-s) 8192
cpu time (seconds)              (-t) unlimited
max user processes              (-u) 4194304
virtual memory (kb)             (-v) unlimited
file locks                      (-x) unlimited

• Podman虚拟机内ulimit -a输出：

real-time non-blocking time  (microseconds, -R) unlimited
core file size              (blocks, -c) unlimited
data seg size               (kbytes, -d) unlimited
scheduling priority                 (-e) 0
file size                   (blocks, -f) unlimited
pending signals                     (-i) 7257
max locked memory           (kbytes, -l) 8192
max memory size             (kbytes, -m) unlimited
open files                          (-n) 1024
pipe size                (512 bytes, -p) 8
POSIX message queues         (bytes, -q) 819200
real-time priority                  (-r) 0
stack size                  (kbytes, -s) 8192
cpu time                   (seconds, -t) unlimited
max user processes                  (-u) 7257
virtual memory              (kbytes, -v) unlimited
file locks                          (-x) unlimited

• MacOS Ventura 13主机ulimit -a输出：

-t: cpu time (seconds)              unlimited
-f: file size (blocks)              unlimited
-d: data seg size (kbytes)          unlimited
-s: stack size (kbytes)             8176
-c: core file size (blocks)         0
-v: address space (kbytes)          unlimited
-l: locked-in-memory size (kbytes)  unlimited
-u: processes                       5333
-n: file descriptors                256

原因分析与解决方法 #

问题根源在于MacOS主机的文件描述符上限（仅256）过低，Podman虚拟机的上限（1024）也不足以支撑pg_restore时的大量文件操作。虽然容器内部设置了极高的文件描述符上限，但虚拟机和主机的上限会形成瓶颈。

解决步骤：

1. 提高MacOS主机的文件描述符上限：
   • 临时生效（重启后失效）：执行ulimit -n 4096
   • 永久生效：编辑/etc/sysctl.conf，添加或修改kern.maxfiles=65536和kern.maxfilesperproc=65536，执行sudo sysctl -w kern.maxfiles=65536和sudo sysctl -w kern.maxfilesperproc=65536，之后重启终端或系统。
2. 提高Podman虚拟机的文件描述符上限：
   • 停止Podman虚拟机：podman machine stop
   • 修改虚拟机配置：podman machine set --ulimit nofile=4096:4096
   • 启动虚拟机：podman machine start
3. 重新创建PostgreSQL容器，确保挂载卷后容器能继承更高的文件描述符上限，再执行pg_restore命令。

内容的提问来源于stack exchange，提问作者Amrit