一歩前進

プログラミングに関する雑多なメモ

MacでPostgreSQL

MacPorts経由でMacPostgreSQLをインストールしたときのメモです。ここでは、サーバ運用は考慮せず、シングルユーザでの利用を想定しています。

通常ユーザのプロンプトを%、postgresユーザのプロンプトをpostgres$で示します。

インストール

% sudo port install postgresql93 +universal
% sudo port install postgresql93-server

ユーザ環境の設定

postgresユーザの設定

PostgreSQLをインストールするとpostgresというユーザが自動的に作成されます。

postgresユーザを有効化せずに、su - postgres -c コマンドによって間接的にpostgresユーザとしてコマンドを実行する方法もあるのですが、頻繁に管理作業を行う場合はコマンドが煩雑になるため、postgresユーザを有効にしておきます。

パスワードの設定

postgresユーザにパスワードを設定して、アカウントを有効にします。

% sudo passwd postgres
Changing password for postgres.
New password: パスワードを入力
Retype new password: パスワードを再度入力
環境変数を設定する

.profileを作成して、各種環境変数を設定します。

なお、postgresユーザのホームディレクトリは/opt/local/var/db/postgresql93です。

% cd /opt/local/var/db/postgresql93
% sudo vi .profile

以下のような内容でパスおよびPGLIB, PGDATAを設定します。

export POSTGRES_HOME=/opt/local/lib/postgresql93
export PATH=${POSTGRES_HOME}/bin:${PATH}
export PGLIB=${POSTGRES_HOME}
export PGDATA=/opt/local/var/db/postgresql93/defaultdb

保存したら所有権を変更しておきます。

% sudo chown postgres:postgres .profile

一般ユーザの設定

次に、普段使用しているユーザの設定を行います。

パスを通す

MacPortsPostgreSQLパッケージは、postgresql93やpostgresql84等、バージョンごとにパッケージが分かれています。そのため、コマンドの格納先が/opt/local/postgresql93/binとなっていますが、port selectコマンドを使うことで/opt/local/binにコマンドへのシンボリックリンクを貼ることが出来ます。

postgresqlのバージョンをpostgresql93にする:

% sudo port select --set postgresql postgresql93
Selecting 'postgresql93' for 'postgresql' succeeded. 'postgresql93' is now active.

確認する:

% port select --list postgresql
    none
    postgresql93 (active)

元に戻す場合:

% sudo port select --set postgresql none
Selecting 'none' for 'postgresql' succeeded. 'none' is now active.

設定の再読込み:

port select --setを使ったらターミナルを再起動するか、.zshrc.bashrcを読み込みます。

% source ~/.zshrc

これでpg_ctlpsqlコマンドにパスが通りました。

% which pg_ctl
/opt/local/bin/pg_ctl
環境変数の設定

環境変数PGDATA.zshrc.bashrcに設定します(コマンドへのパスは通っているので、POSTGRES_HOME等は設定しません)。 後述するデータベース・クラスタの名前はdefaultdbにしてします。

export PGDATA=/opt/local/var/db/postgresql93/defaultdb

ターミナルを再起動するか、設定を再ロードします。

% source ~/.zshrc

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

データベース・クラスタはディスク上のデータベース格納領域であり、1つのサーバインスタンスで管理されるデータベースの集合とのこと。Oracleでいうデータベース(≠データファイル)に相当するものでしょうか。

(参考:データベースクラスタの作成

(1) ディレクトリの作成

defaultdbという名前でディレクトリを作成し、パーミッションpostgresにします。 既に${PGDATA}にディレクトリパス/opt/local/var/db/postgresql93/defaultdbを設定しているので、これを利用します。

% sudo mkdir -p ${PGDATA}
% sudo chown postgres:postgres ${PGDATA}

(2) initdbの実行

次に、先ほど作成したdefaultdbディレクトリを指定してinitdbコマンドを実行します。initdbはpostgresユーザで実行する必要あります。su - postgresのようにハイフンを指定することで、現ユーザの環境を引き継がないようにして余計なトラブルを回避します。

日本語を扱えるようにするため、initdbのオプションに--encoding=UTF-8 --locale=ja_JP.UTF-8を指定しています。他のエンコーディングを使いたい場合はそれを指定してください。ただし、encodingとlocaleが異なるとデータを破壊する原因になるようです。(参考:ロケール(国際化と地域化) — Let's Postgres)

なお、postgresユーザ環境に環境変数PGDATAを設定しているため、-D /opt/local/var/db/postgresql93/defaultdbのようにディレクトリへのパスを指定する必要はありません。

% su - postgres
(postgresユーザのパスワード入力)
postgres$ initdb --encoding=UTF-8 --locale=ja_JP.UTF-8
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "ja_JP.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "ja_JP.UTF-8"
The default text search configuration will be set to "simple".
...

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    postgres -D /opt/local/var/db/postgresql93/defaultdb
or
    pg_ctl -D /opt/local/var/db/postgresql93/defaultdb -l logfile start

WARNING:の部分では、デフォルトではTrust認証を使っているので注意ってことらしい。ここではシングルユーザ(個人)で使うだけなのでデフォルトのままにしておきます。

(参考:https://www.postgresql.jp/document/9.3/html/auth-methods.html

起動と停止

起動:

postgres$ pg_ctl start
もしくは
% sudo su - postgres -c "pg_ctl -D ${PGDATA} start"

停止:

postgres$ pg_ctl stop
もしくは
% sudo su - postgres -c "pg_ctl -D ${PGDATA} start"

(参考:pg_ctlコマンド https://www.postgresql.jp/document/9.3/html/app-pg-ctl.html

サポートスクリプトを使う場合

MacPortsでは、/opt/local/etc/LaunchDaemons/org.macports.postgresql93-serverディレクトリに起動・停止のためのサポートスクリプトがインストールされていました。

これを使って.zshrcあたりにエイリアスを設定しておくと、postgres以外のユーザから起動・停止がしやすくなります。

alias pg_start='sudo /opt/local/etc/LaunchDaemons/org.macports.postgresql93-server/postgresql93-server.wrapper start'
alias pg_stop='sudo /opt/local/etc/LaunchDaemons/org.macports.postgresql93-server/postgresql93-server.wrapper stop'

データベースに対する一般ユーザの作成

現在のdefaultdbには管理ユーザpostgresしかいないため、一般ユーザを作成しておきます。(ここでのユーザはOSのユーザではなく、PostgreSQLdefaultdbクラスタに登録されたユーザのことを指します)

ここで作成する一般ユーザの名前を普段使用しているOSのユーザ名と同じにしておくと、psql等のコマンドを実行する際に-U 接続ユーザ名の指定を省略できるようになります。 しかし、CやJavaのプログラムからPostgreSQLサーバに接続して使用することを想定するため、異なるユーザ名を指定します。

# サーバ起動
postgres$ pg_ctl start
# 対話環境の起動
postgres$ psql
psql (9.3.5)
Type "help" for help.

postgres=#

# 別のユーザが対話環境を起動する場合:
% psql -U postgres ← 接続ユーザを指定する

ユーザ一覧の確認:

\duコマンドでユーザの一覧を確認します。

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}

現在はpostgresユーザしかいません。

ユーザの作成:

別のターミナルを立ち上げ、createuserコマンドでuser1というユーザを追加します。

% createuser -U postgres -P user1
Enter password for new role: (パスワードを入力)
Enter it again:(パスワードを入力)

スーパーユーザ権限を与えるなどのオプションは--helpオプションで確認できます。 再度\duコマンドで確認するとuser1ユーザが作成されています。

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}
 user1     |                                                | {}

データベースの作成

先ほど作成したuser1ユーザを所有者にして、exampleというデータベースを作成します。

% createdb -U postgres --owner=user1 example

psqlから\lコマンドでデータベースの一覧を確認すると、exampleデータベースが表示されます。

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 example   | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 postgres  | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 template0 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

テーブルの作成

まず、\c データベース名 ユーザ名で作成先のデータベースに切り替えます。テーブルの所有者をuser1で作成するため、ユーザ名にuser1を指定しています。

postgres=# \c example user1
You are now connected to database "example" as user "user1".

ここからは通常のSQLコマンドを実行していくだけです。

example=# CREATE TABLE customers
example-# ( id INTEGER PRIMARY KEY,
example(# name VARCHAR(20) NOT NULL,
example(# address VARCHAR(20)); ← セミコロンで文を閉じる
CREATE TABLE

SQLファイルを直接実行する場合

# 作成するテーブルの所有者:user1
# 作成先のデータベース:example
% psql -U user1 example -f setup.sql 

さらなる設定

そのほかの設定はここでは割愛しますが、postgresql.confpg_hba.conf等の各種設定ファイルは/opt/local/var/db/postgresql93/defaultdb直下にあります。

また、参考になるリンクをご紹介しておきます。

コマンドを簡略化するため、postgresアカウントを有効にしましたが、ひと通りの管理作業が終わったらpostgresアカウントを無効(初期状態)に戻すとセキュリティ的に安心です。初期状態に戻すにはpasswdコマンドで空のパスワードを設定します。

コマンドまとめ

コマンド 説明
pg_ctl start サーバ起動
pg_ctl stop サーバ停止
createuser -P ユーザ名 ユーザ追加
dropuser ユーザ名 ユーザ削除
createdb --owner=所有者名 データベース名 データベース追加
dropdb データベース名 データベース削除
psql -U 接続ユーザ名 データベース名 -f SQLファイル SQLファイルを処理する
  • pg_ctlコマンドはpostgresユーザから実行
  • その他のコマンドはPostgreSQLユーザ以外のユーザから操作する場合は、-U 接続ユーザ名オプションを付加

(上記以外のコマンド: https://www.postgresql.jp/document/9.3/html/reference-client.html)

psqlメタコマンド:

コマンド 説明
\l データベースを列挙する
\du データベースのユーザ(ロール)を一覧表示する
\z テーブル、ビュー、シーケンスの一覧を表示する
\c データベース名 ユーザ名 現在のデータベースを切り替える
\cd ディレクトリ 作業ディレクトリを変更する
\h SQLコマンド SQLの構文に関するヘルプを表示する
\o ファイル名 クエリの結果をファイルに出力する
\r コンソールバッファをクリアする
\q 対話環境を終了する

(上記以外のコマンド : https://www.postgresql.jp/document/9.3/html/app-psql.html)