PostgreSQL 12のインストール・設定・DB操作 (CentOS 8)
Contents
はじめに
OSSのRDBMSであるPostgreSQLは、エンタープライズ系システムでOracle DBの代替製品として結構なシェアがあります。
個人的にはRDBMSはMySQLが使い慣れているのですが、今後PostgreSQLを使う機会が増えそうなので、基本的なインストール方法、設定方法、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バージョンが選択できます。また、プロファイルがclientとserverに分かれているので、インストール時にそれらの指定が必要です。
# 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
(略)
完了しました!
Streamが12の行で、client、server両プロファイルともに、[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 行)
