PostgreSQL のインストールと初期設定手順です。
yum コマンドで PostgreSQL サーバーをインストール後に初期設定をします。
インストール後の基本的な運用コマンドについては以下のページを参考にしてください。
【PostgreSQL】基本的な運用コマンド一覧
PostgreSQL 設計
最初に以下をどうするのか簡単に設計しておきます。
- バージョン ← 最新でいいのか？
- インストール方法 ← yum コマンドでインストールするのか？特定のバージョンのソースコードをコンパイルしてインストールするのか？
- インストールパッケージ ← どのパッケージをインストールするのか？サーバーだけでいいのか？クライアントも必要なのか？
- データディレクトリ ← データベースクラスタの場所は？デフォルトでいいのか？
- アカウント及びパスワード ← 必要なアカウントとそのパスワード
- アカウントの権限 ← 新規でアカウントを作成した場合の権限は？
PostgreSQL インストール手順
CentOS や Redhat の場合は、PostgreSQL のインストールは「yum」コマンドでできるので非常に簡単です。
インストール環境
今回は以下のように CentOS 7.4 にインストールしました。
|
[test@SAKURA_VPS ~]$ cat /etc/redhat-release |
PostgreSQLインストール手順
PostgreSQL は「yum」コマンドでインストールすることができます。
リポジトリ―は「CentOS-Base.repo」の「updates」なので、特にリポジトリを追加することなくインストールができます。
|
[root@SAKURA_VPS ~]# yum install postgresql postgresql-server
～ 省略 ～
=========================================================================================================== ～ 省略 ～ 完了しました! |
PostgreSQLのパッケージについて
結果的に以下の3つのパッケージがインストールされました。
- postgresql ← psql コマンド等のクライアントツール機能のパッケージです。
- postgresql-server ← PostgreSQL サーバー（DBサーバー）機能のパッケージです。
- postgresql-libs ← PostgreSQLのライブラリ群です。
最低限この3つのパッケージがインストールされていれば PostgreSQL は利用できます。
■他のパッケージ
- postgresql-contrib ← pgbench や pg_statstatement などのツールが入ったパッケージです。
- postgresql-devel ← PostgreSQL のヘッダーファイルなどが入った開発用に利用するパッケージです。
- postgresql-doc ← PostgreSQL のドキュメントが入ったパッケージです。
- postgresql-test ← PostgreSQL のテスト用のパッケージです。
※必要なパッケージはそれぞれ「yum install xxx」で指定すればインストールできます。
PostgreSQL のリポジトリは何を使っているのか？
リポジトリ―「updates」がどのリポジトリファイルの記載されているのか確認します。
下のコマンド結果を見ると、「CentOS-Base.repo」ファイルに「updates」がありました。
そのため改めて PostgreSQL のリポジトリダウンロードしてインストールする必要はありません。
|
[root@SAKURA_VPS yum.repos.d]# pwd |
PostgreSQLをインストールすると「postgres」アカウントが自動作成される
yum コマンドでインストール後に「/etc/passwd」ファイルを確認すると以下のように「postgres」アカウントが作成されています。
ログインシェルは「/bin/bash」です。（Apacheのように nologin アカウントではありません）
|
[test@SAKURA_VPS ~]$ cat /etc/passwd | grep “postgres” |
※ソースコードからコンパイルしてインストールした場合は「postgres」アカウントは自動で作成されないので別途手動で作成します。
PostgreSQL の初期セットアップをする
PostgreSQL をインストールしただけでは利用できません。
初期セットアップが必要です。
初期セットアップは「postgresql-setup initdb」コマンドで実行します。
|
[root@SAKURA_VPS data]# postgresql-setup initdb
[root@SAKURA_VPS data]# |
※コマンドは一瞬で終わります。
■コマンド実行前
|
[root@SAKURA_VPS ~]# cd /var/lib/pgsql/data/ |
■コマンド実行後
|
[root@SAKURA_VPS data]# pwd pg_snapshots pg_subtrans pg_twophase postgresql.conf pg_stat_tmp pg_tblspc pg_xlog ← 各種ファイルが作成されています。 |
※初期化が完了すると PostgreSQL サービスを起動できるようになります。
postgresql-setup initdb コマンドで何をしているのか？
「postgresql-setup initdb」コマンドを実行すると新規で「データベースクラスタ」が作成されます。
→今回の場合は「/var/lib/pgsql/data」ディレクトリにたくさんの設定ファイルやディレクトリが作成されました。
さらに yum でインストールした際に「postgres」アカウントが作成されましたが、このアカウントをデータベースのスーパーユーザー（一番権限が強い）に設定します。
PostgreSQL サービスの自動起動を設定する
インストールが完了したら、次に「PostgreSQL」の自動起動を有効にします。
自動起動を有効にすることで OS 再起動後でも自動で PostgreSQL が起動します。
|
[root@SAKURA_VPS ~]# systemctl list-unit-files | grep postgresql |
PostgreSQL サービスを起動する
PostgreSQL を起動します。
|
[root@SAKURA_VPS data]# systemctl start postgresql 3月 18 10:09:41 tk2-250-34861.vs.sakura.ne.jp systemd[1]: Starting PostgreSQL database server… |
PostgreSQL へログイン確認をする
サービスが起動したら PostgreSQL へログイン確認をします。
「su – postgres」コマンドで「postgres」アカウントにスイッチし、「psql」コマンドでログインします。
|
[root@SAKURA_VPS data]# su – postgres postgres=# ← 「postgres=#」プロンプトで PostgreSQL へログインしたことが分かります。 postgres=# help |
PostgreSQL 用アカウントを確認する
「pg_user」テーブルよりアカウントを確認します。
※「username」ではなく「usename」です。
|
postgres=# select usename from pg_user; postgres=# |
データベース一覧を表示する
インストール直後のデータベース一覧を確認します。
PostgreSQL へログインせずに、postgres アカウント上で下記のように「psql –list」コマンドを実行します。
|
-bash-4.2$ psql –list -bash-4.2$ |
Postgresql にログインしてからデータベース一覧を表示する
「\l」コマンドでデータベース一覧を表示できます。
|
template1=# \l template1=# |
SELECT 文でデータベース一覧を表示する
SELECT 文でもデータベース一覧を表示させることができます。
注意点は「大文字」で SQL 文を記載することです。「小文字」だとエラーになります。
|
template1=# SELECT * FROM pg_database; template1=# |
「小文字」だとエラーになります。
|
template1=# select * from pg_datadase; ← 小文字だとエラーになります。 |
特定のデータベースへ接続する
特定のデータベースへ接続します。
「psql <接続先データベース名>」で接続できます。
|
-bash-4.2$ psql template1 ← template1に接続します。 psql (9.2.23)
template1=# |
postgresユーザーのパスワードを変更
インストール直後の初期状態では「postgres」ユーザーは「パスワード」が設定されていません。
|
-bash-4.2$ psql -U postgres -c “ALTER USER postgres WITH PASSWORD ‘YOUR_PASSWORD’;” |
- YOUR_PASSWORD に設定したいパスワードを入力します。
psql コマンドのオプション「-c」
-c オプションを付けて、SQL 文をサーバーで実行することができます。
|
-c command, –command=command psqlが1つのコマンド文字列、コマンドを実行して終了することを指定します。 This is useful in shell scripts. これはシェルスクリプトで便利です。 Start-up files (psqlrc and ~/.psqlrc) are ignored with this option. 起動ファイル（psqlrcと〜/ .psqlrc）はこのオプションでは無視されます。 command must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single backslash command. <command>は、サーバによって完全に解析可能なコマンド文字列（つまり、psql特有の機能が含まれていないコマンド文字列）または単一のバックスラッシュコマンドのいずれかでなければなりません。 Thus you cannot mix SQL and psql meta-commands with this option. したがって、SQLとpsqlメタコマンドをこのオプションと混在させることはできません。 To achieve that, you could pipe the string into psql, for example: echo ‘\x \\ SELECT * FROM foo;’ | psql. (\\ is the separator meta-command.) これを実現するには、psqlに文字列をパイプすることができます（例：echo ‘\x \\ SELECT * FROM foo;’）| psql.（\\ はセパレータのメタコマンドです） If the command string contains multiple SQL commands, they are processed in a single transaction, unless there are explicit BEGIN/COMMIT commands included in the string to divide it into multiple transactions. コマンド文字列に複数のSQLコマンドが含まれている場合、文字列に含まれる明示的な BEGIN/COMMIT コマンドが複数のトランザクションに分割されている場合を除き、単一のトランザクションで処理されます。 This is different from the behavior when the same string is fed to psql’s standard input. これは、psqlの標準入力に同じ文字列が入力されたときの動作とは異なります。 Also, only the result of the last SQL command is returned. また、最後のSQLコマンドの結果のみが返されます。 Because of these legacy behaviors, putting more than one command in the -c string often has unexpected results. これらの従来の動作のために、複数のコマンドを -c に入れると、予期しない結果が生じることがよくあります。 It’s better to feed multiple commands to psql’s standard input, either using echo as illustrated above, or via a shell here-document, for example: 上記の echo コマンドを使用するか、ヒアドキュメント シェルを使用して、psqlの標準入力に複数のコマンドを入力する方が良いでしょう。 psql <<EOF |
psql コマンドのオプション「-U」
ユーザーを指定します。
「-U postgres」でユーザー「postgres」を指定しています。
新規ユーザーの作成
セキュリティやバグ対策のため、postgres ユーザーを使わずに別途ユーザーを新規作成し、必要最低限の権限を与えて利用する場合です。
|
$ psql -U postgres -c “CREATE USER YOUR_ACCOUNT WITH PASSWORD ‘YOUR_PASSWORD‘;” |
- YOUR_ACCOUNT に新規作成したいユーザー名を設定します。（例：pos001, poste001 など）
- YOUR_PASSWORD に設定したいパスワードを入力します。
データベースにアクセスしてユーザーを作る手順
OS のコマンドラインからではなくデータベースにアクセスしてからユーザーを作成する手順です。
|
-bash-4.2$ psql testdb ← testdbにアクセスします。
testdb=# testdb=# CREATE USER postest01; ← testdb にアクセス後、CREATE USER で「postest01」ユーザーを作成します。 testdb=# |
新規データベース作成
新規データベースを作成します。
|
-bash-4.2$ psql -U postgres -W -c “CREATE DATABASE testdb”; ← CREATE DATABASE で「testdb」を作成します。 -bash-4.2$ |
新規テーブルを作成する
データベースを作成したらテーブルを作成します。
|
testdb=# CREATE TABLE testtable (id integer, url text, title text, number integer); testdb=# |
新規アカウントに新規データベースの権限を割り当てる
新規データベースを作成し、テーブルを作成したら権限を割り当てます。
ここでは例として「postest01」アカウントに先ほど作成した「testtable」テーブルへの権限を割り当てます。
権限は「SELECT, INSERT, UPDATE, DELETE」（つまり ALL 権限）を割り当てます。
|
-bash-4.2$ psql -U postgres -W -c “GRANT SELECT, INSERT, UPDATE, DELETE ON testtable TO postest01”; |
or
|
-bash-4.2$ psql -U postgres -W -c “GRANT ALL ON testtable TO postest01”; |
データベースにアクセスをしてテーブルに権限を割り当てる手順
「test」ユーザーに「testtable」の操作の権限を割り当てます。
|
testdb=# GRANT ALL ON testtable TO test; testdb=# |
PostgreSQL サービスが起動しない場合のトラブルシューティング
以下のように「journalctl -x –no-pager | less」コマンドでログを表示します。
|
[root@SAKURA_VPS ~]# journalctl -x –no-pager | less
3月 18 08:02:51 tk2-250-34861.vs.sakura.ne.jp systemd[1]: Starting PostgreSQL database server… |
ログに対応方法が記載されていますが、「/var/lib/pgsql/data」ディレクトリ内が空の状態であり「postgresql-setup initdb」コマンドを実行していないため、まずは「postgresql-setup initdb」コマンドを実行して初期セットアップをします。
「\d」で「リレーションがありません。」と表示される（ERROR: リレーション”xxx”は存在しません）
コマンドを実行した際に「リレーションがありません。」や「ERROR: リレーション”xxx”は存在しません」などのエラーが表示されることがあります。
|
-bash-4.2$ psql -U postgres -W -c “GRANT SELECT, INSERT, UPDATE, DELETE ON testdb TO post01;” postgres=# \d |
原因
- 「大文字」「小文字」で異なるデータベースと認識されている可能性がある（ダブルクオーテーションで囲んで作成したなど）
- テーブルが作成されていない
- スキーマが異なる
【例】「SELECT * FROM pg_database;」だと表示されるが、「\d」コマンドだと「リレーションがありません。」と表示されます。
|
postgres=# SELECT * FROM pg_database; postgres=# \d |
現在のスキーマのパスを確認する
以下のコマンドで現在のスキーマのパスを確認します。
|
postgres=# SHOW search_path; postgres=# |
どうしても不明な場合は、一度 DROP で削除をして再度作り直してみます。
参考させていただいたもの
PostgreSQL を初めて触る初心者向けに解説しています。
私的にはもう少々データベースの運用管理分野の情報があると嬉しいです。
しかし PostgreSQL について丁寧に詳しく記述されているので最初の一冊（Kindle本ですが）にいいと思います。
オープンソースデータベース標準教科書 -PostgreSQL-
コメント