はじめに

仕事で定期的にpg_dumpでスキーマをダンプしてるんですが、ある日突然

ERROR: out of shared memory
HINT: You may need to increase max_locks_per_transaction.

というようなエラーが発生。

max_locks_per_transaction を大きいくしてもいいんだけど、その前に、ロックの数の問題ならテーブルの数が依存するんだよな？
というのが気になり検証したくなりました。

やったことを記録しておきたいこともあり、環境構築が長くなってしまいました。
あと、お手軽に試した結果PostgreSQL 9.2という古いバージョンでの検証になりました。
(仕事的には9.x系使ってるのでいいんですけど)

環境構築

Dockerでコンテナを用意

慣れないDockerでCentosOS7の環境を作成
とりあえずこちらを参考に。

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

CenntOSのコンテナを起動

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にDBを作る

今回は検証するだけなので、専用のユーザーは作らずにいきなりDBを作成する

[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$

DBに接続

-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 が影響するとのことなので、まずは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.

max_locks_per_transaction = 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

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

発生！

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

エラー。テーブルを減らすシェルの作成

[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 を変更すると共有メモリの消費も大きくなります。

参考