はじめに
仕事で定期的に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 を変更すると共有メモリの消費も大きくなります。