他のRDBを使ったことがある前提で。
また、SQL Server でいうところのsa(Server Administrator)である、postgres(スーパーユーザー)で設定を行ってしまっているので、その点は留意されたい。
バージョンとサポート期間
まず、バージョン管理ポリシーを押さえます。これから使うなら、少なくとも13以降ですね。
PostgreSQL グローバル開発グループは、最初のリリース後 5 年間、メジャー バージョンをサポートします。その後、最終マイナー バージョンがリリースされ、ソフトウェアはサポートされなくなります (サポート終了)。
| バージョン | 現在のマイナー | サポート | 最初のリリース | 最終リリース |
|---|---|---|---|---|
| 16 | 16.3 | はい | 2023年9月14日 | 2028年11月9日 |
| 15 | 15.7 | はい | 2022年10月13日 | 2027年11月11日 |
| 14 | 14.12 | はい | 2021年9月30日 | 2026年11月12日 |
| 13 | 13.15 | はい | 2020年9月24日 | 2025年11月13日 |
| 12 | 12.19 | はい | 2019年10月3日 | 2024年11月14日 |
ドキュメントが充実しているRHELを例に利用可能なバージョンを見てみます。
RHEL 9.0 は、この Application Stream の初期バージョンとして PostgreSQL 13 を提供します。これは、RPM パッケージとして簡単にインストールできます。
RHEL 9 のマイナーリリースで、ライフサイクルがより短い追加の PostgreSQL バージョンが、モジュールとして提供されます。
- RHEL 9.2 で、PostgreSQL 15 が postgresql:15 モジュールストリームとして導入されました。
- RHEL 9.4 で、PostgreSQL 16 が postgresql:16 モジュールストリームとして導入されました。
データベースサーバーの設定および使用 | Red Hat Product Documentation
また、PostgeSQLのサイトで、各プラットフォームでのインストール方法が参照できます。
インストール
ここでは、AlmaLinux 9.4 でのバイナリからのセットアップを行うことにします。
パッケージのインストール
PostgreSQL 13の場合、パッケージに含まれているので、そのまま、インストールできます。
dnf install postgresql-server
PostgreSQL 15または16の場合、postgresql モジュールから、バージョンとプロファイルを指定するそうです。
dnf module install postgresql:16/server
データベースクラスタの初期化
postgresql-setup --initdb
デフォルトで/var/lib/pgsql/dataがデータ格納用ディレクトリとして、初期化されます。
このディレクトリは、ここで作成されたUnixユーザpostgres、UnixグループpostgresがOwnerとなります。
サービスの開始と自動起動の設定
systemctl start postgresql.service systemctl enable postgresql.service
設定ファイル
最低限、知っておく必要があるファイルは2つです。
- postgresql.conf：全般の設定
- pg_hba.conf：認証関係の設定
ファイルは、locate等で探してもよいですが、基本的にデータ格納ディレクトリの直下(/var/lib/pgsql/data/)にあるはずです。
設定を反映するには、サービスの再起動が必要になります。
systemctl restart postgresql.service
postgresql.conf のうち、接続関係のここだけは確認しておく
| パラメータ | 説明 |
|---|---|
| listen_addresses | クライアント接続を許可するTCP/IPアドレスのリスト。デフォルトはlocalhot。 |
| port | listenするポート。デフォルトは5432。 |
| max_connections | 同時接続する最大数。デフォルトは100。 |
pg_hba.conf は完全に理解しなければならない
クライアント認証はデータベースクラスタのデータディレクトリ内の、伝統的にpg_hba.confという名前の設定ファイルで管理されています （HBAとは、host-based authentication: ホストベース認証の略です）。
以下は、PostgreSQL 13 の初期状態から、よくある編集を行った状態です。上から走査して、一致したものを用いられます。
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: -host all all 127.0.0.1/32 ident +host all all 127.0.0.1/32 md5 # IPv6 local connections: -host all all ::1/128 ident +host all all ::1/128 md5 # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer host replication all 127.0.0.1/32 ident host replication all ::1/128 ident
| 項目 | 説明 |
|---|---|
| TYPE | local: Unixドメインソケット。ローカル接続のみ可能。 host: TCP/IP接続。ホスト接続。 |
| DATABASE | 対応するデータベース名を指定。allはすべて。 |
| USER | 対応するデータベースユーザ名を指定。 allはすべて。 |
| ADDRESS | クライアントPCのアドレス。 |
| METHOD | このレコードに合致する認証方式を指定。 |
主なMETHODは以下のとおりです。
| METHOD | 説明 |
|---|---|
| peer | クライアントのオペレーティングシステムにおけるユーザ名をオペレーティングシステムから取得し、ユーザ名が要求されたデータベースユーザ名と一致するか検査します。 これはローカル接続の時にのみ使用可能です。 |
| ident | クライアントのオペレーティングシステムにおけるユーザ名をクライアント上のidentサーバに尋ねてユーザ名が要求されたデータベースユーザ名と一致するか検査します。 ident認証は、TCP/IP接続でのみ使用可能です。 |
| md5 | ユーザのパスワードを検証するために、SCRAM-SHA-256あるいはMD5認証を実行します。 |
| scram-sha-256 | ユーザのパスワードを検証するためにSCRAM-SHA-256認証を実行します。 |
基本的には、以下の段取りを踏むことが多いでしょう。
- pg_hba.conf を編集して、host接続のlocalhostからの認証方式を ident から md5 または scram-sha-256 に修正。
- postgresを再起動
systemctl restart postgresql.service - peer接続でログインして、データベースユーザ「postgres」のパスワード設定。
- psql でホスト接続可能になる。
peer接続して、パスワード設定する方法は以下のとおりです。ここで指定している postgres はUnixユーザです。
sudo -u postgres psql \password \quit
パスワード設定後は、ホスト接続できるようになります。ここで指定している postgres は データベースユーザです。区別できるように-Uが大文字になっているのでしょうか。
psql -h localhost -U postgres
昨今の環境では、IPv4とIPv6の両方が有効になっており、IPv6が優先して使われる場合があります。接続先をlocalhostにして、下記のエラーになる場合は、identが指定されている「::1」(IPv6)に繋ごうとして失敗しています。
psql: error: FATAL: Ident authentication failed for user "postgres"
この問題の対応は、IPv4で接続するようにpsql -h 127.0.0.1 -U postgresとするか、pg_hba.conf で、IPv4とIPv6のMETHODを揃えることです。
PostgreSQLのインストールで利用可能になるツール群
インストールで利用可能になるツール群のうち、よく使うものは以下のとおりです。
| ツール | 説明 |
|---|---|
| psql | PostgreSQLの対話的ターミナル |
| createdb | 新しいPostgreSQLデータベースを作成する |
| dropdb | PostgreSQLデータベースを削除する |
| pg_dump | PostgreSQLデータベースをスクリプトファイルまたは他のアーカイブファイルへ抽出する |
psqlとメタコマンド
psql 接続時のメタコマンドでよく使うものは以下のとおりです。
| コマンド | 説明 |
|---|---|
| \q | psqlの終了 |
| \c [database] | データベースへの接続 |
| \conninfo | 接続情報の表示 |
| \l | データベース一覧の表示 |
| \dt | テーブル一覧の表示 |
| \d [table] | テーブル定義の表示 |
| \du | ユーザの表示 |
| \copy [table] {from|to} [filename] using delimiters 'csv' header | ヘッダ行のあるCSVファイルの入出力 |
| \i [filename] | ファイルを読み取って実行 |
| \? | メタコマンドのヘルプ |
PostgreSQL: Documentation: 16: psql
バックアップとリストア
バックアップ
容量的に問題なければ、SQLスクリプトを取得する論理バックアップが手軽です。 論理バックアップではバックアップの要件を満たせないようであれば(未使用時間帯にバックアップが終わらない等)、物理バックアップを検討します。 データベースを指定してのバックアップは、下記のようになります。
pg_dump -h localhost -U postgres -d [databse] -f [filename]
みんな標準入出力が好きなのか、下記のようにリダイレクトで説明されていることが多い印象です。
pg_dump -h localhost -U postgres -d [databse] > [filename]
4.5. PostgreSQL データのバックアップ | Red Hat Product Documentation
リストア
バックアップで取得したファイルがあれば、以下の手順で、別サーバに復元できます。
createdb -h localhost -U postgres [database] psql -h localhost -U postgres -d [database] -f [filename]
認証情報の保存
ホームディレクトリに .pgpassファイルを作成し、以下の内容を登録しておくことで、psqlやpg_dump使用時に対話的なパスワード入力が不要になります。複数行登録可能です。
hostname:port:database:username:password
自分のみがアクセスできるようにするため、以下のとおりownerのみにReadWriteを設定します。
chmod 0600 ~/.pgpass
その他
データベース一覧
メタコマンドの\lで、データベースの一覧が表示でき、OwnerやEncodingが確認できます。
| Name | 説明 |
|---|---|
| postgres | システムデータベース。SQL Server での master に相当？。 |
| template0 | テンプレートデータベース。template0のテンプレート。 |
| template1 | テンプレートデータベース。ユーザーデータベースのテンプレート。SQL Server での model に相当？ |
データベースオブジェクト
データベース管理下のオブジェクトの説明も読んでおいた方がよいでしょう。
ロール、データベース、テーブル空間名のような少数のオブジェクトはクラスタレベルで定義されており、pg_globalテーブル空間に格納されています。 クラスタの中には複数のデータベースがあり、互いに分離されているもののクラスタレベルのオブジェクトにはアクセスできます。 各データベースの中には複数のスキーマがあり、スキーマはテーブルや関数などのオブジェクトを含みます。 したがって階層の全体像は、クラスタ、データベース、スキーマ、テーブル（や関数などの何らかのオブジェクト）となります。
SQL Serverだとオブジェクト階層は、インスタンス.データベース.スキーマ.オブジェクト となっていました。
- デフォルトスキマーは public です。SQL Server のデフォルトスキーマはdboでした。
- オブジェクト名は必要に応じてダブクォーテーションで括ります。SQL Server ではブラケット(角かっこ)で括っていました。
- 1つの接続で複数のデータベースにアクセスすることはできません。 SQL Server では、権限が許せば、複数のデータベースに同時にアクセス可能でした。
ロールとユーザー
メタコマンドの\duで、ロールの一覧が確認できます。
PostgreSQLは、ロールという概念を使用してデータベースへの接続承認を管理します。 ロールは、その設定方法に応じて、データベースユーザ、またはデータベースユーザのグループとみなすことができます。
ユーザーとロールは、システムテーブルから参照できます。
select * from pg_roles; select * from pg_user;
ログ
一度は、ログの設定を確認しておきましょう。
sudo cat /var/lib/pgsql/data/postgresql.conf|grep log
logging_collector = onとなっていることが、確認できます。
このパラメータはlogging collectorを有効にします。 それはstderrに送られたログメッセージを捕捉し、ログファイルにリダイレクトするバックグラウンドプロセスです。 この手法はsyslogへのログよりもしばしば有用です。 メッセージの一部の種類がsyslogでは出力されない可能性があるためです。
以下でログの一覧が確認できます。
sudo ls /var/lib/pgsql/data/log/
PostgreSQL 13だと曜日ごとのファイル名なので、木曜日だと以下のとおりです。
sudo cat /var/lib/pgsql/data/log/postgresql-Thu.log
公式文書
HTML版、PDF版、EPUB版とそろっているのがありがたいです。なお、PostgreSQL 16 のPDF版は長大で、3447ページとなっています。
