PostgreSQL 12のインストール・設定・DB操作 (CentOS 8)

はじめに

OSSのRDBMSであるPostgreSQLは、エンタープライズ系システムでOracle DBの代替製品として結構なシェアがあります。
個人的にはRDBMSはMySQLが使い慣れているのですが、いよいよ仕事でも避けて通れなくなってきたので、基本的なインストール方法、設定方法、DB操作方法について勉強してみました。

前提条件

以下の前提条件で検証しています。

  • ハードウェア: VirtualBox上の仮想マシン
  • OS: CentOS 8.1
  • ミドルウェア:
    • PostgreSQL: 12.1 (OS同梱版)
  • 構築対象サーバはインターネットと疎通が取れること
  • 各手順はroot権限を持つユーザで実施

OSは、CentOSインストールDVDのISOファイルから「最小限のインストール」を選択しインストールを行いました。
バージョン情報は以下の通りです。

# cat /etc/redhat-release
CentOS Linux release 8.1.1911 (Core) 

# uname -r
4.18.0-147.el8.x86_64

パッケージインストール

CentOS 8のAppStreamリポジトリでは、PostgreSQLは9.6、10、12の3バージョンが選択できます。また、プロファイルがclientserverに分かれているので、インストール時にそれらの指定が必要です。

# dnf module list postgresql
CentOS-8 - AppStream
Name             Stream      Profiles                Summary                                 
postgresql       9.6         client, server [d]      PostgreSQL server and client module     
postgresql       10 [d]      client, server [d]      PostgreSQL server and client module     
postgresql       12          client, server          PostgreSQL server and client module     

ヒント: [d]efault, [e]nabled, [x]disabled, [i]nstalled

モジュールストリーム機能を使用して、バージョンを12に指定してPostgreSQLをインストールします。

# dnf -y module install postgresql:12/server postgresql:12/client
(略)
=============================================================================================
 パッケージ            Arch       バージョン                             リポジトリー  サイズ
=============================================================================================
group/moduleパッケージをインストール:
 postgresql            x86_64     12.1-2.module_el8.1.0+273+979c16e6     AppStream     1.4 M
 postgresql-server     x86_64     12.1-2.module_el8.1.0+273+979c16e6     AppStream     5.5 M
依存関係のインストール:
 libpq                 x86_64     12.1-3.el8                             AppStream     195 k
 libicu                x86_64     60.3-2.el8_1                           BaseOS        8.8 M
モジュールプロファイルのインストール:
 postgresql/client                                                                          
 postgresql/server                                                                          
モジュールストリームの有効化:
 postgresql                       12                                                        

トランザクションの概要
=============================================================================================
インストール  4 パッケージ

ダウンロードサイズの合計: 16 M
インストール済みのサイズ: 62 M
(略)
完了しました!

Stream12の行で、clientserver両プロファイルともに、[i]が表示されていることを確認します。

# dnf module list postgresql
CentOS-8 - AppStream
Name            Stream     Profiles                   Summary                                
postgresql      9.6        client, server [d]         PostgreSQL server and client module    
postgresql      10 [d]     client, server [d]         PostgreSQL server and client module    
postgresql      12 [e]     client [i], server [i]     PostgreSQL server and client module    

ヒント: [d]efault, [e]nabled, [x]disabled, [i]nstalled

データベースクラスタの作成

PostgreSQLは、データベースクラスタと呼ばれるディレクトリで、設定ファイルやデータベースのデータをまとめて保管します。そのため、PostgreSQLをインストールしたら、まず最初にデータベースクラスタを作成します。
データベースクラスタを作成するときは、作業ユーザをpostgresに切り替えてから作業します。

# su - -c 'initdb --encoding=UTF8 --no-locale' postgres

--encoding=UTF8は、データベースクラスタ内で作成するデータベースのデフォルト文字コードをUTF8に指定しています。
--no-localeは、システムのデフォルトロケールを使わず、Cロケールを使用する設定です。C以外のロケールを指定すると性能に影響があるようで、Cロケールを使用することが一般的なようです。

[参考資料]
ロケール(国際化と地域化)

データベースクラスタとして/var/lib/pgsql/dataディレクトリが作成され、内部に設定ファイルやデータベースファイルが作成されていることを確認します。

# ls -l /var/lib/pgsql/data/
合計 52
-rw-------. 1 postgres postgres     3  5月 31 15:09 PG_VERSION
drwx------. 5 postgres postgres    41  5月 31 15:09 base
drwx------. 2 postgres postgres  4096  5月 31 15:09 global
drwx------. 2 postgres postgres     6  5月 31 15:09 pg_commit_ts
drwx------. 2 postgres postgres     6  5月 31 15:09 pg_dynshmem
-rw-------. 1 postgres postgres  4513  5月 31 15:09 pg_hba.conf
-rw-------. 1 postgres postgres  1636  5月 31 15:09 pg_ident.conf
drwx------. 4 postgres postgres    68  5月 31 15:09 pg_logical
drwx------. 4 postgres postgres    36  5月 31 15:09 pg_multixact
drwx------. 2 postgres postgres    18  5月 31 15:09 pg_notify
drwx------. 2 postgres postgres     6  5月 31 15:09 pg_replslot
drwx------. 2 postgres postgres     6  5月 31 15:09 pg_serial
drwx------. 2 postgres postgres     6  5月 31 15:09 pg_snapshots
drwx------. 2 postgres postgres     6  5月 31 15:09 pg_stat
drwx------. 2 postgres postgres     6  5月 31 15:09 pg_stat_tmp
drwx------. 2 postgres postgres    18  5月 31 15:09 pg_subtrans
drwx------. 2 postgres postgres     6  5月 31 15:09 pg_tblspc
drwx------. 2 postgres postgres     6  5月 31 15:09 pg_twophase
drwx------. 3 postgres postgres    60  5月 31 15:09 pg_wal
drwx------. 2 postgres postgres    18  5月 31 15:09 pg_xact
-rw-------. 1 postgres postgres    88  5月 31 15:09 postgresql.auto.conf
-rw-------. 1 postgres postgres 26589  5月 31 15:09 postgresql.conf

設定ファイルの変更

pg_hba.conf

pg_hba.confは、クライアント認証に関する設定ファイルです。
接続タイプ、接続先データベース、ロール名、接続元IPアドレスを指定して1行1ルールの形式で設定を記載します。ルールは上から順に評価され、どのルールにも該当しない場合はアクセスは拒否されます。

データベースへの接続がローカルからのみの場合はpg_hba.confのデフォルト設定で接続可能ですが、外部からの接続を許可する場合はルールを追加します。

viなどのテキストエディタでファイルを開きます。

# vi /var/lib/pgsql/data/pg_hba.conf

設定内容は以下の通りです。ファイルの末尾にルールを追加します。
以下の設定例では、testdbデータベースに対して、192.168.0.1/24のネットワークからtestuserロールを使用したパスワード認証を許可しています。

(略)
host    testdb             testuser             192.168.0.1/24                 md5

ルールの形式は<接続タイプ> <接続対象データベース名> <接続に使用するロール名> <認証方式>となります。外部からの接続は接続タイプにhostを指定します。接続元IPアドレスは、CIDR方式で範囲を指定します。パスワード認証を行う場合は、md5を指定します。

postgresql.conf

postgresql.confは、PostgreSQLの設定全般を扱うファイルです。
viなどのテキストエディタで以下のファイルを開きます。

# vi /var/lib/pgsql/data/postgresql.conf

変更内容は以下の通りです。
要件に関係なく共通で設定できそうな項目をピックアップしています。設定例のうち、メモリに関する項目はメモリ4GBのサーバの場合の例を記載しています。

### リッスンIPアドレス
### 外部からの接続を受け付ける場合、* または特定のNICのIPアドレスを指定する
#listen_addresses = 'localhost'
↓
listen_addresses = '*'

### 共有バッファ
### 搭載メモリの25%程度を目処に指定する
shared_buffers = 128MB
↓
shared_buffers = 1GB

### コネクションごとのソートバッファ
### work_mem * max_connections(デフォルト値100)が搭載メモリの20%程度になるよう指定する
#work_mem = 4MB
↓
work_mem = 8MB

### OSが使用できるディスクキャッシュサイズ
### 搭載メモリの50%程度を指定する。
#effective_cache_size = 4GB
↓
effective_cache_size = 2GB

### スロークエリログの出力閾値
#log_min_duration_statement = -1
↓
log_min_duration_statement = 5s

### ログ出力先ディレクトリ
#log_directory = 'log'
↓
log_directory = '/var/log/postgresql'

### ログファイル名
### postgresql-YYMMDD.logの形式を指定
log_filename = 'postgresql-%a.log'
↓
log_filename = 'postgresql-%Y%m%d.log'

### チェックポイントログの有効化
#log_checkpoints = off
↓
log_checkpoints = on

### データベース接続ログの有効化
#log_connections = off
↓
log_connections = on

### データベース切断ログの有効化
#log_disconnections = off
↓
log_disconnections = on

### スロークエリログの有効化
#log_duration = off
↓
log_duration = on

ログ出力先ディレクトリの作成

デフォルト設定ではデータベースクラスタ内にログファイルが出力される設定になっていますが、運用しにくいので一般的なログ出力先ディレクトリである/var/log配下にpostgresqlディレクトリを作成します。

# mkdir /var/log/postgresql
# chown postgres:postgres /var/log/postgresql
# chmod 750 /var/log/postgresql

サービスの起動

サービスの自動起動設定と起動を行います。

# systemctl enable postgresql.service
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.
# systemctl start postgresql.service

データベース操作

データベース接続、切断

データベースへ接続するには、postgresユーザ権限でpsqlコマンドを実行します。

# su - -c 'psql' postgres

psqlに何も引数を付けないと、postgresデータベースへ接続されます。特定のデータベースへ接続する場合は、以下の引数を指定します。

# su - -c 'psql -U <ロール名> -d <データベース名>' postgres

データベースの切断は、\qで行います。

=# \q

ロール作成

ロールの作成は、CREATE ROLEで行います。

=# CREATE ROLE <ロール名> CREATEDB LOGIN PASSWORD '<パスワード文字列>';

作成したロールの一覧は\duで表示できます。

=# CREATE ROLE testuesr LOGIN PASSWORD 'testpass'
CREATE ROLE
=# \du
                                             ロール一覧
 ロール名 |                                   属性                                   | 所属グ
ループ 
----------+--------------------------------------------------------------------------+-------
-------
 postgres | スーパユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}
 testuser | DB作成可                                                                 | {}

データベース作成

データベースの作成は、CREATE DATABASEで行います。データベースを作成するときは、所有権を持つロールを指定します。

=# CREATE DATABASE <データベース名> OWNER <ユーザ名>;

作成したデータベースの一覧は\lで表示できます。

# CREATE DATABASE testdb OWNER testuser;
CREATE DATABASE
postgres=# \l
                                        データベース一覧
   名前    |  所有者  | エンコーディング | 照合順序 | Ctype(変換演算子) |     アクセス権限   

-----------+----------+------------------+----------+-------------------+--------------------
---
 postgres  | postgres | UTF8             | C        | C                 | 
 template0 | postgres | UTF8             | C        | C                 | =c/postgres        
  +
           |          |                  |          |                   | postgres=CTc/postgr
es
 template1 | postgres | UTF8             | C        | C                 | =c/postgres        
  +
           |          |                  |          |                   | postgres=CTc/postgr
es
 testdb    | testuser | UTF8             | C        | C                 | 
(4 行)

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です