由于pg_dump出现了”out of shared memory”错误，我想要验证一下是否与表的数量有关
首先
我在工作中经常使用pg_dump定期备份模式，但有一天突然间……
错误：共享内存不足
提示：您可能需要增加 max_locks_per_transaction。
发生了类似的错误。
我可以增加 max_locks_per_transaction 这个参数的值，但在这之前，我想知道如果锁的数量成为问题，是否取决于表的数量。因此，我想进行验证。
有些事我想要记录下来，所以环境设置变得很长。
另外，为了方便试验的结果，使用了一个老版本的 PostgreSQL 9.2 进行验证。
(工作上使用 9.x 系列所以没问题)
环境建设
使用Docker准备容器。
参考にしながら、DockerでCentOS7の環境を作ってみる。
PS C:\data\dev\docker\centos7> docker pull centos:centos7
centos7: Pulling from library/centos
2d473b07cdd5: Pull complete
Digest: sha256:be65f488b7764ad3638f236b7b515b3678369a5124c47b8d32916d6487418ea4
Status: Downloaded newer image for centos:centos7
docker.io/library/centos:centos7
PS C:\data\dev\docker\centos7> docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
getting-started latest f543cfcab108 6 days ago 265MB
docker101tutorial latest dcd472fd6898 6 days ago 47.2MB
<none> <none> dea1eaa1499f 6 days ago 47.2MB
<none> <none> b7e6ba19ed04 6 days ago 47.2MB
<none> <none> 866713c08ea6 6 days ago 47.2MB
<none> <none> b1c4ce75d6fa 6 days ago 265MB
<none> <none> a1729d3e2187 6 days ago 265MB
docker/getting-started latest 3e4394f6b72f 4 months ago 47MB
centos centos7 eeb6ee3f44bd 19 months ago 204MB
启动CentOS容器
PS C:\data\dev\docker\centos7> docker run -it -d --name centos7 centos:centos7
1d5b192caaec79f2bba99200a797e032984f9798c2000341ad77688e2569665b
在中文中，可以有以下一种表达方式：
开启Bash
PS C:\data\dev\docker\centos7> docker exec -it centos7 /bin/bash
安装PostgreSQL。
我记得我在工作中安装PostgreSQL时是从源代码构建的，但我没有记住通过yum安装的经历。
为了方便起见，我想通过yum安装。
我将参考这里的步骤进行操作。
首先添加PostgreSQL的存储库。
[root@1d5b192caaec /]# yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Loaded plugins: fastestmirror, ovl
pgdg-redhat-repo-latest.noarch.rpm | 8.6 kB 00:00:00
Examining /var/tmp/yum-root-cMmWZV/pgdg-redhat-repo-latest.noarch.rpm: pgdg-redhat-repo-42.0-32.noarch
Marking /var/tmp/yum-root-cMmWZV/pgdg-redhat-repo-latest.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package pgdg-redhat-repo.noarch 0:42.0-32 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
========================================================================================================================
Package Arch Version Repository Size
========================================================================================================================
Installing:
pgdg-redhat-repo noarch 42.0-32 /pgdg-redhat-repo-latest.noarch 13 k
Transaction Summary
========================================================================================================================
Install 1 Package
Total size: 13 k
Installed size: 13 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : pgdg-redhat-repo-42.0-32.noarch 1/1
Verifying : pgdg-redhat-repo-42.0-32.noarch 1/1
Installed:
pgdg-redhat-repo.noarch 0:42.0-32
Complete!
[root@1d5b192caaec /]# yum list postgres
Loaded plugins: fastestmirror, ovl
Loading mirror speeds from cached hostfile
* base: mirror.aktkn.sg
* extras: mirror.aktkn.sg
* updates: mirror.aktkn.sg
pgdg-common/7/x86_64/signature | 198 B 00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-32.noarch (@/pgdg-redhat-repo-latest.noarch)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]: y
pgdg-common/7/x86_64/signature | 2.9 kB 00:00:06 !!!
pgdg11/7/x86_64/signature | 198 B 00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-32.noarch (@/pgdg-redhat-repo-latest.noarch)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]: y
pgdg11/7/x86_64/signature | 3.6 kB 00:00:02 !!!
pgdg12/7/x86_64/signature | 198 B 00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-32.noarch (@/pgdg-redhat-repo-latest.noarch)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]: y
pgdg12/7/x86_64/signature | 3.6 kB 00:00:01 !!!
pgdg13/7/x86_64/signature | 198 B 00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-32.noarch (@/pgdg-redhat-repo-latest.noarch)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]: y
pgdg13/7/x86_64/signature | 3.6 kB 00:00:01 !!!
pgdg14/7/x86_64/signature | 198 B 00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-32.noarch (@/pgdg-redhat-repo-latest.noarch)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]: y
pgdg14/7/x86_64/signature | 3.6 kB 00:00:01 !!!
pgdg15/7/x86_64/signature | 198 B 00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-32.noarch (@/pgdg-redhat-repo-latest.noarch)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]: y
pgdg15/7/x86_64/signature | 3.6 kB 00:00:00 !!!
(1/11): pgdg11/7/x86_64/group_gz | 245 B 00:00:01
(2/11): pgdg12/7/x86_64/group_gz | 245 B 00:00:01
(3/11): pgdg13/7/x86_64/group_gz | 246 B 00:00:00
(4/11): pgdg14/7/x86_64/group_gz | 244 B 00:00:00
(5/11): pgdg12/7/x86_64/primary_db | 366 kB 00:00:02
(6/11): pgdg13/7/x86_64/primary_db | 280 kB 00:00:01
(7/11): pgdg11/7/x86_64/primary_db | 481 kB 00:00:02
(8/11): pgdg14/7/x86_64/primary_db | 185 kB 00:00:01
(9/11): pgdg15/7/x86_64/group_gz | 246 B 00:00:00
(10/11): pgdg15/7/x86_64/primary_db | 97 kB 00:00:00
(11/11): pgdg-common/7/x86_64/primary_db | 173 kB 00:00:03
安装PostgreSQL
[root@1d5b192caaec /]# yum -y install postgresql-server
Loaded plugins: fastestmirror, ovl
Loading mirror speeds from cached hostfile
* base: mirror.aktkn.sg
* extras: mirror.aktkn.sg
* updates: mirror.aktkn.sg
Resolving Dependencies
--> Running transaction check
---> Package postgresql-server.x86_64 0:9.2.24-8.el7_9 will be installed
--> Processing Dependency: postgresql-libs(x86-64) = 9.2.24-8.el7_9 for package: postgresql-server-9.2.24-8.el7_9.x86_64
--> Processing Dependency: postgresql(x86-64) = 9.2.24-8.el7_9 for package: postgresql-server-9.2.24-8.el7_9.x86_64
--> Processing Dependency: systemd-sysv for package: postgresql-server-9.2.24-8.el7_9.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql-server-9.2.24-8.el7_9.x86_64
--> Running transaction check
---> Package postgresql.x86_64 0:9.2.24-8.el7_9 will be installed
---> Package postgresql-libs.x86_64 0:9.2.24-8.el7_9 will be installed
---> Package systemd-sysv.x86_64 0:219-78.el7_9.7 will be installed
--> Processing Dependency: systemd = 219-78.el7_9.7 for package: systemd-sysv-219-78.el7_9.7.x86_64
--> Running transaction check
---> Package systemd.x86_64 0:219-78.el7 will be updated
---> Package systemd.x86_64 0:219-78.el7_9.7 will be an update
--> Processing Dependency: systemd-libs = 219-78.el7_9.7 for package: systemd-219-78.el7_9.7.x86_64
--> Running transaction check
---> Package systemd-libs.x86_64 0:219-78.el7 will be updated
---> Package systemd-libs.x86_64 0:219-78.el7_9.7 will be an update
--> Finished Dependency Resolution
Dependencies Resolved
========================================================================================================================
Package Arch Version Repository Size
========================================================================================================================
Installing:
postgresql-server x86_64 9.2.24-8.el7_9 updates 3.8 M
Installing for dependencies:
postgresql x86_64 9.2.24-8.el7_9 updates 3.0 M
postgresql-libs x86_64 9.2.24-8.el7_9 updates 235 k
systemd-sysv x86_64 219-78.el7_9.7 updates 97 k
Updating for dependencies:
systemd x86_64 219-78.el7_9.7 updates 5.1 M
systemd-libs x86_64 219-78.el7_9.7 updates 419 k
Transaction Summary
========================================================================================================================
Install 1 Package (+3 Dependent packages)
Upgrade ( 2 Dependent packages)
Total download size: 13 M
Downloading packages:
Delta RPMs disabled because /usr/bin/applydeltarpm not installed.
warning: /var/cache/yum/x86_64/7/updates/packages/postgresql-libs-9.2.24-8.el7_9.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Public key for postgresql-libs-9.2.24-8.el7_9.x86_64.rpm is not installed
(1/6): postgresql-libs-9.2.24-8.el7_9.x86_64.rpm | 235 kB 00:00:00
(2/6): postgresql-9.2.24-8.el7_9.x86_64.rpm | 3.0 MB 00:00:00
(3/6): postgresql-server-9.2.24-8.el7_9.x86_64.rpm | 3.8 MB 00:00:01
(4/6): systemd-libs-219-78.el7_9.7.x86_64.rpm | 419 kB 00:00:00
(5/6): systemd-sysv-219-78.el7_9.7.x86_64.rpm | 97 kB 00:00:00
(6/6): systemd-219-78.el7_9.7.x86_64.rpm | 5.1 MB 00:00:02
------------------------------------------------------------------------------------------------------------------------
Total 4.4 MB/s | 13 MB 00:00:02
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
Importing GPG key 0xF4A80EB5:
Userid : "CentOS-7 Key (CentOS 7 Official Signing Key) <security@centos.org>"
Fingerprint: 6341 ab27 53d7 8a78 a7c2 7bb1 24c6 a8a7 f4a8 0eb5
Package : centos-release-7-9.2009.0.el7.centos.x86_64 (@CentOS)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql-libs-9.2.24-8.el7_9.x86_64 1/8
Installing : postgresql-9.2.24-8.el7_9.x86_64 2/8
Updating : systemd-libs-219-78.el7_9.7.x86_64 3/8
Updating : systemd-219-78.el7_9.7.x86_64 4/8
Failed to get D-Bus connection: Operation not permitted
Installing : systemd-sysv-219-78.el7_9.7.x86_64 5/8
Installing : postgresql-server-9.2.24-8.el7_9.x86_64 6/8
Cleanup : systemd-219-78.el7.x86_64 7/8
Cleanup : systemd-libs-219-78.el7.x86_64 8/8
Verifying : postgresql-libs-9.2.24-8.el7_9.x86_64 1/8
Verifying : systemd-libs-219-78.el7_9.7.x86_64 2/8
Verifying : postgresql-9.2.24-8.el7_9.x86_64 3/8
Verifying : systemd-219-78.el7_9.7.x86_64 4/8
Verifying : postgresql-server-9.2.24-8.el7_9.x86_64 5/8
Verifying : systemd-sysv-219-78.el7_9.7.x86_64 6/8
Verifying : systemd-libs-219-78.el7.x86_64 7/8
Verifying : systemd-219-78.el7.x86_64 8/8
Installed:
postgresql-server.x86_64 0:9.2.24-8.el7_9
Dependency Installed:
postgresql.x86_64 0:9.2.24-8.el7_9 postgresql-libs.x86_64 0:9.2.24-8.el7_9 systemd-sysv.x86_64 0:219-78.el7_9.7
Dependency Updated:
systemd.x86_64 0:219-78.el7_9.7 systemd-libs.x86_64 0:219-78.el7_9.7
Complete!
确认版本
[root@1d5b192caaec /]# pg_ctl --version
pg_ctl (PostgreSQL) 9.2.24
老了，但总体来说，暂且这样吧。
初始化和启动PostgreSQL
我要进行PostgreSQL的初始化了。
[root@1d5b192caaec /]# su - postgres
-bash-4.2$ initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "C".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".
fixing permissions on existing directory /var/lib/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /var/lib/pgsql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
postgres -D /var/lib/pgsql/data
or
pg_ctl -D /var/lib/pgsql/data -l logfile start
启动PostgreSQL服务。
[root@1d5b192caaec /]# systemctl status postgresql
Failed to get D-Bus connection: Operation not permitted
从未见过的错误！当我查询了一下，似乎发现systemctl被禁用了。
停止容器。
PS C:\data\dev\docker\centos7> docker stop centos7
centos7
–听说要加上privileged选项来启动。
PS C:\data\dev\docker\centos7> docker run -it -d --privileged --name centos7 centos:centos7
docker: Error response from daemon: Conflict. The container name "/centos7" is already in use by container "1d5b192caaec79f2bba99200a797e032984f9798c2000341ad77688e2569665b". You have to remove (or rename) that container to be able to reuse that name.
See 'docker run --help'.
因为遇到了错误，所以我要重新初始化PostgreSQL。
PS C:\data\dev\docker\centos7> docker rm centos7
centos7
因为听说要加上 –privileged 选项来启动，所以进行启动。也稍微修改一下选项。
PS C:\data\dev\docker\centos7> docker run --privileged -d --name centos7 centos:centos7 /sbin/init
54ab4f90d9e74d1b7d08aee6189e5b97fa35c44997d304ce32bb694c95fd75bb
启动Bash
PS C:\data\dev\docker\centos7> docker exec -it centos7 /bin/bash
使用systemctl来确认
[root@54ab4f90d9e7 /]# systemctl status postgresql
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
Active: inactive (dead)
Apr 22 01:43:05 54ab4f90d9e7 systemd[1]: Collecting postgresql.service
由于确认可以使用systemctl，因此启动了PostgreSQL。
[root@54ab4f90d9e7 /]# systemctl start postgresql
[root@54ab4f90d9e7 /]# systemctl status postgresql
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
Active: active (running) since Sat 2023-04-22 01:44:24 UTC; 1s ago
Process: 372 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
Process: 367 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 374 (postgres)
CGroup: /docker/54ab4f90d9e74d1b7d08aee6189e5b97fa35c44997d304ce32bb694c95fd75bb/docker/54ab4f90d9e74d1b7d08aee6189e5b97fa35c44997d304ce32bb694c95fd75bb/system.slice/postgresql.service
├─374 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
├─375 postgres: logger process
├─377 postgres: checkpointer process
├─378 postgres: writer process
├─379 postgres: wal writer process
├─380 postgres: autovacuum launcher process
└─381 postgres: stats collector process
‣ 374 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
Apr 22 01:44:24 54ab4f90d9e7 systemd[1]: Watching non detached 377.
Apr 22 01:44:24 54ab4f90d9e7 systemd[1]: Watching 377 through watch_pids1.
Apr 22 01:44:24 54ab4f90d9e7 systemd[1]: Watching non detached 378.
Apr 22 01:44:24 54ab4f90d9e7 systemd[1]: Watching 378 through watch_pids1.
Apr 22 01:44:24 54ab4f90d9e7 systemd[1]: Watching non detached 379.
Apr 22 01:44:24 54ab4f90d9e7 systemd[1]: Watching 379 through watch_pids1.
Apr 22 01:44:24 54ab4f90d9e7 systemd[1]: Watching non detached 380.
Apr 22 01:44:24 54ab4f90d9e7 systemd[1]: Watching 380 through watch_pids1.
Apr 22 01:44:24 54ab4f90d9e7 systemd[1]: Watching non detached 381.
Apr 22 01:44:24 54ab4f90d9e7 systemd[1]: Watching 381 through watch_pids1.
在PostgreSQL中创建数据库。
既然这次只是进行验证，我们就不需要创建专属用户，直接创建数据库即可。
[root@54ab4f90d9e7 /]# su - postgres
Last login: Sat Apr 22 01:43:18 UTC 2023 on pts/0
-bash-4.2$ createdatabase test
-bash: createdatabase: command not found
-bash-4.2$ createdb test
-bash-4.2$
连接到数据库
-bash-4.2$ psql test
psql (9.2.24)
Type "help" for help.
test=# create table test(name text, age numeric);
CREATE TABLE
test=# select * from pg_tables where tablename='test';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+-----------+------------+------------+------------+----------+-------------
public | test | postgres | | f | f | f
(1 row)
确认可用性
验证
由于最大事务锁数（max_locks_per_transaction）的影响，首先将其设置为最小值，然后创建大量的表并确认是否会出现错误。
对于PostgreSQL的配置更改
确认 postgresql.conf 文件的存在
[root@54ab4f90d9e7 /]# ls /var/lib/pgsql/data/
PG_VERSION pg_clog pg_log pg_serial pg_subtrans pg_xlog postmaster.pid
base pg_hba.conf pg_multixact pg_snapshots pg_tblspc postgresql.conf
global pg_ident.conf pg_notify pg_stat_tmp pg_twophase postmaster.opts
编辑postgresql.conf文件
添加max_locks_per_transaction = 10
[root@54ab4f90d9e7 /]# vi /var/lib/pgsql/data/postgresql.conf
#max_locks_per_transaction = 64 # min 10
# (change requires restart)
max_locks_per_transaction = 10
重新启动PostgreSQL
[root@54ab4f90d9e7 /]# systemctl restart postgresql
[root@54ab4f90d9e7 /]# systemctl status postgresql
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
Active: active (running) since Sat 2023-04-22 02:01:33 UTC; 8s ago
Process: 465 ExecStop=/usr/bin/pg_ctl stop -D ${PGDATA} -s -m fast (code=exited, status=0/SUCCESS)
Process: 471 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
Process: 466 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 473 (postgres)
CGroup: /docker/54ab4f90d9e74d1b7d08aee6189e5b97fa35c44997d304ce32bb694c95fd75bb/docker/54ab4f90d9e74d1b7d08aee6189e5b97fa35c44997d304ce32bb694c95fd75bb/system.slice/postgresql.service
├─473 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
├─474 postgres: logger process
├─476 postgres: checkpointer process
├─477 postgres: writer process
├─478 postgres: wal writer process
├─479 postgres: autovacuum launcher process
└─480 postgres: stats collector process
‣ 473 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
Apr 22 02:01:33 54ab4f90d9e7 systemd[1]: Watching non detached 476.
Apr 22 02:01:33 54ab4f90d9e7 systemd[1]: Watching 476 through watch_pids1.
Apr 22 02:01:33 54ab4f90d9e7 systemd[1]: Watching non detached 477.
Apr 22 02:01:33 54ab4f90d9e7 systemd[1]: Watching 477 through watch_pids1.
Apr 22 02:01:33 54ab4f90d9e7 systemd[1]: Watching non detached 478.
Apr 22 02:01:33 54ab4f90d9e7 systemd[1]: Watching 478 through watch_pids1.
Apr 22 02:01:33 54ab4f90d9e7 systemd[1]: Watching non detached 479.
Apr 22 02:01:33 54ab4f90d9e7 systemd[1]: Watching 479 through watch_pids1.
Apr 22 02:01:33 54ab4f90d9e7 systemd[1]: Watching non detached 480.
Apr 22 02:01:33 54ab4f90d9e7 systemd[1]: Watching 480 through watch_pids1.
每个事务的最大锁定数为10。
确认设置的反映
[root@54ab4f90d9e7 /]# psql test -U postgres
psql (9.2.24)
Type "help" for help.
test=# select name, setting from pg_settings where name='max_locks_per_transaction';
name | setting
---------------------------+---------
max_locks_per_transaction | 10
(1 row)```
### 100テーブル生成してpg_dump確認
テーブルを作るシェルを作成する
```powershell
[root@54ab4f90d9e7 /]# cd /tmp
[root@54ab4f90d9e7 tmp]# vi maketable.sh
#!/bin/bash
for i in `seq 1 100`
do
psql -U postgres -c "CREATE TABLE test_$i(name text, age numeric, address text)" -d test
done
[root@54ab4f90d9e7 tmp]# bash ./maketable.sh
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
・・・中略・・・
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
我试着进行pg_dump
[root@54ab4f90d9e7 tmp]# pg_dump -s -U postgres -f test01.sql test
没有错误。
生成3000个附加表，并通过pg_dump进行确认。
毅然决然地，通过添加3000个表格来生成。
[root@54ab4f90d9e7 tmp]# vi maketable.sh
#!/bin/bash
for i in `seq 1 3000`
do
psql -U postgres -c "CREATE TABLE test2_$i(name text, age numeric, address text)" -d test
done
[root@54ab4f90d9e7 tmp]# bash ./maketable.sh
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
・・・
CREATE TABLE
CREATE TABLE
备份数据库
[root@54ab4f90d9e7 tmp]# pg_dump -s -U postgres -f test01.sql test
pg_dump: WARNING: out of shared memory
pg_dump: [archiver (db)] query failed: ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
pg_dump: [archiver (db)] query was: LOCK TABLE public.test2_2575 IN ACCESS SHARE MODE
发生了！
在生成3000个表的情况下，将max_locks_per_transaction设置为64，并进行pg_dump确认。
修改 max_locks_per_transaction
[root@54ab4f90d9e7 tmp]# vi /var/lib/pgsql/data/postgresql.conf
max_locks_per_transaction = 64 # min 10
#max_locks_per_transaction = 10 # min 10
# (change requires restart)
重新启动PostgreSQL
[root@54ab4f90d9e7 tmp]# systemctl restart postgresql
[root@54ab4f90d9e7 tmp]#
尝试使用pg_dump
[root@54ab4f90d9e7 tmp]# pg_dump -s -U postgres -f test01.sql test
[root@54ab4f90d9e7 tmp]#
取得目标
删除1000个表并将max_locks_per_transaction设置为10，然后进行pg_dump确认。
修改 max_locks_per_transaction
[root@54ab4f90d9e7 tmp]# vi /var/lib/pgsql/data/postgresql.conf
#max_locks_per_transaction = 64 # min 10
max_locks_per_transaction = 10 # min 10
# (change requires restart)
重新启动PostgreSQL。
[root@54ab4f90d9e7 tmp]# systemctl restart postgresql
[root@54ab4f90d9e7 tmp]#
我试着进行pg_dump。
[root@54ab4f90d9e7 tmp]# pg_dump -s -U postgres -f test01.sql test
pg_dump: WARNING: out of shared memory
pg_dump: [archiver (db)] query failed: ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
pg_dump: [archiver (db)] query was: LOCK TABLE public.test2_2575 IN ACCESS SHARE MODE
错误。创建减少表格的Shell脚本。
[root@54ab4f90d9e7 tmp]# vi droptable.sh
#!/bin/bash
for i in `seq 1 1000`
do
psql -U postgres -c "DROP TABLE IF EXISTS test2_$i;" -d test
done
[root@54ab4f90d9e7 tmp]# bash ./droptable.sh
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
・・・中略・・・
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
[root@54ab4f90d9e7 tmp]#
尝试进行pg_dump
[root@54ab4f90d9e7 tmp]# pg_dump -s -U postgres -f test01.sql test
[root@54ab4f90d9e7 tmp]#
取得了目标。
总结一下
如果在执行pg_dump时出现以下错误，可以考虑两种解决方法。
pg_dump: WARNING: out of shared memory
pg_dump: [archiver (db)] query failed: ERROR: out of shared memory
-
- 增加 max_locks_per_transaction 的设定值
- 减少表的数量
如果桌子太多，首先应优先考虑减少桌子的数量。
如果有无用的桌子，或者存在包含过去数据且再也不会使用的子表，应该将其删除！
（有太多无用的桌子本身就是个问题）
如果无法减少表的数量，只能增加max_locks_per_transaction的设置值。
然而，如果更改max_locks_per_transaction，还会增加共享内存的消耗。
请参考
-
- DockerでCentOS 7のイメージを利用してみよう
-
- PostgreSQL を CentOS にインストールするには
- CentOS7上に、Docker CentOS7、PostgreSQL 10.5コンテナを立てる
