☰

无法备份 postgres 数据库

tag-icon • tag-icon ubuntu postgresql-9.1
无法备份 postgres 数据库

我们在 Ubuntu 机器上运行了 postgres 9.1。postgres 数据库大小为 155GB，大约有 13200 个表。Ubuntu 机器有 120GB RAM。Ubuntu 机器是一个 docker 容器。

当尝试备份数据库时，失败并显示：

pg_dump: WARNING:  out of shared memory
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.

因此我们将的值增加到max_locks_per_transaction1024 postgresql.conf，但 postgres 却无法启动。

root@abc:# /etc/init.d/postgresql restart
 * Restarting PostgreSQL 9.1 database server
 * The PostgreSQL server failed to start. Please check the log output:
2018-02-04 03:27:22 UTC FATAL:  could not create shared memory segment: Invalid argument
2018-02-04 03:27:22 UTC DETAIL:  Failed system call was shmget(key=5432001, size=70123520, 03600).
2018-02-04 03:27:22 UTC HINT:  This error usually means that PostgreSQL's request for a shared memory 
segment exceeded your kernel's SHMMAX parameter.  You can either reduce the request size or reconfigure 
the kernel with larger SHMMAX.  To reduce the request size (currently 70123520 bytes), reduce PostgreSQL's 
shared memory usage, perhaps by reducing shared_buffers or max_connections. If the request size is 
already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising 
the request size or reconfiguring SHMMIN is called for. The PostgreSQL documentation contains more information 
about shared memory configuration.

因此，当我们尝试改变的值时SHMMAX，它失败了：

# echo 1134217728 >/proc/sys/kernel/shmmax
bash: /proc/sys/kernel/shmmax: Read-only file system

知道哪里出了问题以及如何处理吗？

答案1

Ubuntu 盒子是一个 docker 容器。

容器设置内存限制并使用IPC 命名空间，并且默认的 shm 限制远远不足以运行大型数据库。您需要在运行命令中配置更高的限制。如果容器有超过一百 GB 的内存可供使用，您可以将 shm 限制设置得相当高，可能是 8 GB。

docker run -m 120G  --sysctl kernel.shmmax=8589934592 --sysctl kernel.shmall=2097152

PostgreSQL 文档管理内核资源是一个很好的参考，但没有提到容器。

相关内容