PostgreSQL のインストールと初期設定の手順のメモです。

環境

• OS: Ubuntu 20.04
• DB: PostgreSQL 14

PostgreSQL のインストール

公式ドキュメントに従って、apt を使ってインストールします。

shell

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

$ sudo apt-get install curl ca-certificates
$ curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ sudo apt update
$ sudo apt upgrade
$ sudo apt install postgresql-14

初回ログイン

インストール時に postgres というユーザーが OS に作成されますので、インストール後に初めて PostgreSQL にアクセスする場合は、この OS ユーザーに切り替えてアクセスします。

$ sudo su postgres
$ psql
postgres=#   -- ここで必要な操作を行います

認証方式の設定

PostgreSQL の認証方式は pg_hba.conf というファイルで設定します。

デフォルトでは以下のように記載されています。local に peer と設定されているのに注目してください。

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

これは、Unix ソケットを使ったローカル接続では Peer 認証を使うということを意味しています。

Peer 認証とはOS ユーザーを使って認証する方式です。PostgreSQL ではインストール時に postgres という OS ユーザーが作成され、PostgreSQL 内でも対応するユーザーが作成されています。

しかし PostgreSQL 内に作成された postgres ユーザーのパスワードはランダムに生成され、こちらには通知されません。そのためインストール直後に PostgreSQL へ接続するためには使えず、 OS ユーザーを postgres ユーザーに切り替えた上で、Peer 認証を使ってアクセスする必要がありました。

通常のユースケースでは、PostgreSQL 内にアプリケーション用のユーザーを作って、ユーザー名とパスワードを使ってアクセスする場合がほとんどだと思います。

ユーザー名とパスワードを使って認証する方式は scram-sha-256 (古いバージョンでは md5) です。ローカル アクセスの場合もユーザー名とパスワードで認証できるよう、設定ファイルを更新します。

またデフォルトでは localhost (127.0.0.1) からのアクセスしか許可されていませんので、リモートアクセスを有効にする場合は、アクセス元の IP アドレス範囲 (以下の例では 192.168.1.0/24) に書き換えます。

shell

> nano /etc/postgresql/14/main/pg_hba.conf
---
# 以下の行を変更
# local   all             all                                 peer
local   all             all                                 scram-sha-256

# リモートマシンからアクセスする場合は以下を変更。
# IP アドレスにはアクセス元の IP アドレス範囲を入力。
# host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             192.168.1.0/24            scram-sha-256
...
---

# PostgreSQL を再起動
> sudo systemctl restart postgresql

PostgreSQL の推奨設定

PostgreSQL の多くの設定は postgresql.conf ファイルで行います (今回の場合、/etc/postgresql/14/main/postgresql.conf にあります)。

今回紹介する推奨設定は以下の通りです。各項目について説明します。

/etc/postgresql/14/main/postgresql.conf

# リモートアクセスの許可
listen_addresses = '*'

# WAL およびアーカイブログ設定
wal_level = logical
archive_mode = on
archive_command = 'cp %p /backup/archivedir/%f'  # WAL をコピーするためのコマンド
fsync = on
wal_sync_method = fdatasync

# パフォーマンス関連設定
max_connections = 1024
shared_buffers = 2GB    # サーバーに搭載されているメモリの 25% 程度
effective_cache_size = 4GB    # サーバーに搭載されているメモリの 50% 程度
work_mem = 64MB
maintenance_work_mem = 256MB

# チェックポイント関連設定
max_wal_size = 32GB
checkpoint_timeout = 15min 
checkpoint_completion_target = 0.8

リモートアクセスの許可

デフォルトではリモートからのアクセスは有効になっていませんので、リモートからアクセスできるよう設定します。

listen_addresses = * と設定することで、サーバーの IP アドレスが何であれ、そのサーバーへのリクエストを受信するようになります。

WAL および アーカイブログ設定

PostgreSQL のトランザクションログは WAL (Write Ahead Log) と呼ばれます。トランザクションログは各データ変更を記録したもので、バックアップやレプリケーションの他、データベースが障害等で壊れてしまった場合の自動復旧などにも使用されます。

WAL の種類にはいくつかありますが、将来的にレプリケーションを行う可能性がある場合は、柔軟性の高い論理レプリケーションに対応した種類が良いと思うため、今回は論理レプリケーションに対応した種類で設定します。

PostgreSQL では、複数の WAL ファイルが順番に使用され、ファイルが1巡すると古いファイルが上書きされます。上書きされた分のトランザクションログは削除されますので、バックアップのためにどこかに保持しておくのが推奨されます。

WAL を保存するためにコピーしたものを アーカイブログ と呼びます。今回はアーカイブログを保存するための設定も行います。

なお、アーカイブログは PostgreSQL のプロセスでは管理されないため、cron などを使って自分で定期的に削除する必要がある点注意してください。

WAL および アーカイブログに関する主な設定項目は以下です。

パフォーマンス関連設定

チェックポイント関連設定

データを読み書きする時、ディスクへのアクセスは遅いため、読み取りするデータを一旦 “バッファ” や “キャッシュ” と呼ばれるメモリ上の領域にロードし、クエリ実行時はメモリ上のデータを読み書きすることでパフォーマンスを向上させています。

多くのデータベースでこのようなデータ読み書きの方法が取られており、PostgreSQL でもバッファが活用されています。

データを更新する時も、クエリ実行時はメモリ上のデータのみを更新します。その後一定のタイミングで変更されたデータを全てディスクに書き込む処理が実行されており、この処理を “チェックポイント” と言います。

チェックポイント処理は頻発するとディスクへの書き込みが増加しパフォーマンスが悪化しますが、少なすぎると耐障害性が損なわれます。

個人的にはデータベースはデータを安全に保存するのが一番の役割だと考えるため、この記事ではパフォーマンスを意識しつつも、より耐障害性を優先した設定を紹介します。