CentOS/RHELにPostgreSQLをインストールして構成する完全ガイド

本記事では、Linux CentOS 7にPostgreSQL 11をインストールし、基本的な初期設定、主要な設定ファイルのパラメータ、そしてパフォーマンスチューニングの手法について解説します。PostgreSQLは広く利用されている無料のオブジェクトリレーショナルデータベース管理システム（ORDBMS）です。MySQL/MariaDBほど普及してはいませんが、最もプロフェッショナルなデータベースの一つとして高く評価されています。

PostgreSQLの主な強み

• SQL標準への高い準拠性
• MVCC（マルチバージョン同時実行制御）による高性能な処理
• 優れたスケーラビリティ（高負荷環境で広く採用）
• 多数のプログラミング言語に対応
• 堅牢なトランザクションおよびレプリケーション機構
• JSONデータ型のサポート

CentOS/RHELへのPostgreSQLのインストール手順

PostgreSQLはCentOSの標準リポジトリからもインストールできますが、ここでは常に最新のパッケージバージョンが入手できる開発者向けリポジトリを追加してインストールします。

まず、PostgreSQL公式リポジトリを追加します。

# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

このリポジトリには最新版だけでなく、過去のバージョンのPostgreSQLも含まれています。リポジトリの情報は以下のように確認できます。

次に、yumを使ってPostgreSQL 11をインストールします。

# yum install postgresql11-server -y

PostgreSQLサーバーと必要なライブラリがインストールされます。

Installing : libicu-50.2-3.el7.x86_64 1/4
Installing : postgresql11-libs-11.5-1PGDG.rhel7.x86_64 2/4
Installing : postgresql11-11.5-1PGDG.rhel7.x86_64 3/4
Installing : postgresql11-server-11.5-1PGDG.rhel7.x86_64 4/4

パッケージのインストール完了後、データベースクラスタを初期化する必要があります。

# /usr/pgsql-11/bin/postgresql-11-setup initdb

続いて、systemctlコマンドでPostgreSQLデーモンを有効化し、自動起動を設定します。

# systemctl enable postgresql-11
# systemctl start postgresql-11

サービスの状態を確認しましょう。

# systemctl status postgresql-11

● postgresql-11.service - PostgreSQL 11 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2020-10-18 16:02:15 +06; 26s ago
Docs: https://www.postgresql.org/docs/11/static/
Process: 8714 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 8719 (postmaster)
CGroup: /system.slice/postgresql-11.service
├─8719 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
├─8721 postgres: logger
├─8723 postgres: checkpointer
├─8724 postgres: background writer
├─8725 postgres: walwriter
├─8726 postgres: autovacuum launcher
├─8727 postgres: stats collector
└─8728 postgres: logical replication launcher
Oct 18 16:02:16 host1.woshub.com systemd[1]: Starting PostgreSQL 11 database server...

外部からPostgreSQLへアクセスしたい場合は、CentOSのデフォルトファイアウォール（firewalld）でTCPポート5432を開放します。

# firewall-cmd --get-active-zones

public
interfaces: eth0

# firewall-cmd --zone=public --add-port=5432/tcp --permanent
# firewall-cmd --reload

iptablesを使用している場合は以下のコマンドを実行します。

# iptables -A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
# service iptables restart

SELinuxが有効になっている場合は、次のコマンドも実行しておきましょう。

# setsebool -P httpd_can_network_connect_db 1

psqlを使ったデータベース作成・ユーザー作成・権限付与

PostgreSQLをインストールした直後は、システムに「postgres」というユーザーのみが存在します。このアカウントを日常的なデータベース操作に使用することは推奨されません。データベースごとに個別のユーザーを作成するのが良い習慣です。

postgresサーバーに接続するには、以下のコマンドを実行します。

# sudo -u postgres psql

psql (11.5)
Type "help" for help.

postgres=#

PostgreSQLのコンソールが表示されます。以降では、psqlコンソールからの基本的な管理操作の例をいくつか紹介します。

まず、デフォルトのpostgresユーザーのパスワードを変更します。

ALTER ROLE postgres WITH PASSWORD 's3tPa$$w0rd!';

新しいデータベースとユーザーを作成し、そのユーザーに新規データベースへのフルアクセス権限を付与します。

postgres=# CREATE DATABASE newdbtest;
postgres=# CREATE USER mydbuser WITH password '!123456789';
postgres=# GRANT ALL PRIVILEGES ON DATABASE newdbtest TO mydbuser;

データベースへ接続するには：

postgres=# \c databasename

テーブル一覧を表示するには：

postgres=# \dt

データベースへの接続一覧を表示するには：

postgres=# select * from pg_stat_activity where datname='dbname'

データベースへの全接続を切断するには：

postgres=# select pg_terminate_backend(pid) from pg_stat_activity where datname = 'dbname'

現在のセッション情報を取得するには：

postgres=# \conninfo

psqlコンソールを終了するには、以下のコマンドを実行します。

postgres=# \q

お気づきの通り、これらの構文はMariaDBやMySQLと似ています。また、WebインターフェースからPostgreSQLデータベースをより便利に管理したい場合は、pgAdmin4（PythonとJavaScript/jQueryで開発）の使用をおすすめします。多くのWeb開発者にとって馴染みのあるphpMyAdminに相当するツールです。

PostgreSQLの主要設定パラメータ

PostgreSQLの設定ファイルは /var/lib/pgsql/11/data ディレクトリに格納されています。

1. postgresql.conf — PostgreSQL本体の設定ファイル
2. pg_hba.conf — アクセス制御の設定ファイル。ユーザーごとの制限やデータベース接続ポリシーを設定できます
3. pg_ident.conf — identプロトコルによるクライアント認証用の設定ファイル

ローカルユーザーが認証なしでpostgresにログインできないようにするには、pg_hba.confに以下を指定します。

local all all md5
host all all 127.0.0.1/32 md5

postgresql.confの重要なパラメータを見ていきましょう。

• listen_addresses — サーバーがクライアント接続を受け付けるIPアドレスを指定します。デフォルトはlocalhostで、ローカル接続のみ可能です。すべてのIPv4インターフェースで待ち受ける場合は「0.0.0.0」を指定します
• max_connections — データベースサーバーへの最大同時接続数
• temp_buffers — 一時バッファの最大サイズ
• shared_buffers — データベースサーバーが使用する共有メモリのサイズ。通常はサーバー総RAMの25%程度が推奨されます
• effective_cache_size — PostgreSQLプランナーがローカルディスク上でキャッシュに使えるメモリ量を判断するためのパラメータです。通常はサーバー総RAMの50〜75%に設定します
• work_mem — ORDER BY、DISTINCT、ソート・マージ処理などの内部ソート操作で使用されるメモリサイズ
• maintenance_work_mem — VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEYなどの保守操作で使用されるメモリサイズ
• fsync — 有効にすると、DBMSはデータの物理的なディスク書き込み完了を待ちます。fsyncを有効にしておくと、システム障害やハードウェア障害発生後のデータベース復旧が容易になります。当然ながら性能は低下しますが、保存の信頼性は向上します。無効化する場合はfull_page_writesも無効化することが推奨されます
• max_stack_depth — スタックの最大サイズ（デフォルトは2MB）
• max_fsm_pages — サーバー上の空きディスク領域を管理するパラメータです。テーブルからデータを削除しても領域は即座に解放されず、フリースペースマップ上で空きとしてマークされ、新規登録時に再利用されます。データの追加・削除が頻繁なサーバーでは、この値を大きくすると性能向上が期待できます
• wal_buffers — WALデータを保持するために使用される共有メモリ（shared_buffers）のサイズ
• wal_writer_delay — WALをディスクに書き込む連続した周期間の遅延時間
• commit_delay — トランザクションのWALバッファへの書き込みから、ディスクへの書き込み（ライトスルー）までの遅延
• synchronous_commit — WALデータが物理的にディスクへ書き込まれた後にトランザクション成功の結果を返すかどうかを設定するパラメータです

PostgreSQLデータベースのバックアップとリストア

PostgreSQLデータベースのバックアップには複数の方法がありますが、ここでは最も簡単な方法を紹介します。

まず、サーバー上で稼働しているデータベースを確認します。

postgres=# \list

4つのデータベースがあり、そのうち3つはシステム用（postgresとtemplate）です。

先ほど作成したmydbtestデータベースをバックアップしてみましょう。

PostgreSQLデータベースのバックアップには pg_dump ツールを使用します。

# sudo -u postgres pg_dump mydbtest > /root/dupm.sql

postgresユーザーでコマンドを実行し、対象データベース名とダンプファイルの保存先パスを指定します。バックアップシステムがこのダンプファイルを取り込んだり、Webサーバー環境であれば接続済みのクラウドストレージへ送信したりすることも可能です。

ダンプをデータベースへリストアするには、psql を使用します。

# sudo -u postgres psql mydbtest < /root/dupm.sql

専用のダンプ形式でバックアップを作成し、gzipで圧縮することもできます。

# sudo -u postgres pg_dump -Fc mydbtest > /root/dumptest.sql

この形式のダンプは、pg_restoreツールを使って復元します。

# sudo -u postgres pg_restore -d mydbtest /root/dumptest.sql

PostgreSQLのパフォーマンスチューニングと最適化

以前のMariaDB関連記事では、tunerツールを使ってmy.cnfの設定パラメータを最適化する方法を紹介しました。PostgreSQLにも同様の PgTune というツールがありましたが、残念ながら長期間更新されていません。その代わり、PostgreSQLの設定を最適化できるオンラインサービスが数多く存在します。筆者のおすすめは PGTune（pgtune.leopard.in.ua） です。

インターフェースは非常にシンプルです。サーバーのスペック（プロファイル、CPU数、メモリ容量、ディスクタイプ）を入力して「Generate」をクリックするだけです。主要なPostgreSQLパラメータの推奨値を含むpostgresql.confの案が生成されます。

例えば、RAM 4GB・4 vCPUのSSD搭載VPSサーバー向けには、以下のようなpostgresql.conf設定が推奨されます。

# DB Version: 11
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 4 GB
# CPUs num: 4
# Connections num: 100
# Data Storage: ssd
max_connections = 100
shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 256MB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 5242kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

執筆時点でこのようなサービスは他にも存在します。類似のサービスとしては以下があります。

• Cybertec PostgreSQL Configurator
• PostgreSQL Configuration Tool

これらのサービスを活用すれば、ハードウェア構成と用途に合わせたPostgreSQLの基本パラメータを素早く設定できます。その後は、サーバーリソースだけでなく、実際のデータベースの運用状況、データベースサイズ、接続数などを分析しながら、PostgreSQLのパラメータをさらに微調整していくことが可能です。