mac mini （macOS Mojave）にてpostgresqlを構築する。（Homebrew編）

1. はじめに

様々なシステムを構築する場合、データベースは必要不可欠です。今、SHOWROOMのデータ抽出システムを構築したいと考えている私は、自分で利用実績のあるpostgresqlを採用することにしました。
一からコンパイルするか、それともHomebrewを用いるか迷いましたが、Homebrewを用いることにいたしました。
今回は環境構築について書きます。

2. 目次

1. はじめに
2. 目次
3. インストールに向けた準備
  3.1. ユーザ"postgres"の追加
  3.2. データベースクラスタの場所を作成
  3.3. ユーザ"postgres"における環境変数の設定
4. インストール
5. データベースクラスタの初期化
6. データベースの作成
7. pgAdmin4のインストールと確認
8. rubyからのアクセス
9. さいごに
10. 参考サイト

3. インストールに向けた準備

今回、環境構築は以下の通りにいたします。

項目	値
実行OSユーザ	postgres
バージョン	11
データベースクラスタの場所	/udd/database/postgres/11/data/

3.1 ユーザ"postgres"の追加

まずは、postgresグループ

postgresユーザを作成します。
ここでは、@matsudoさんのMac OS Xでコマンドラインからユーザ追加 - Qiitaを参考に、以下のスクリプトを作成して実行しました。

/root/bin/add_user.zsh

# !/bin/zsh

TARGETUSER=postgres
PASSWORD="postgres"
GID=1000
STAFFGID=`dscl . list groups gid|grep \^staff | tail  -1  | awk '{print $2}'`

dscl . -create /Groups/$TARGETUSER PrimaryGroupID $GID
dscl . -create /Users/$TARGETUSER
dscl . -create /Users/$TARGETUSER UserShell /bin/bash
dscl . -create /Users/$TARGETUSER RealName $TARGETUSER

maxid=$(dscl . -list /Users UniqueID | awk '{print $2}' | sort -ug | tail -1)
newid=$((maxid+1))

dscl . -create /Users/$TARGETUSER UniqueID         $newid
dscl . -create /Users/$TARGETUSER PrimaryGroupID   $GID
dscl . -create /Users/$TARGETUSER NFSHomeDirectory /Users/$TARGETUSER

cp -a /System/Library/User\ Template/English.lproj /Users/$TARGETUSER
chown -R $TARGETUSER\:staff /Users/$TARGETUSER
chmod 701 /Users/$TARGETUSER
dscl . -passwd /Users/$TARGETUSER $PASSWORD

sudo dscl . -append /Groups/staff GroupMembership postgres

$ sudo su -
# mkdir bin
# cd bin
# vim add_user.zsh
(上記を作成)
# chmod +x add_user.zsh
# ./add_user.zsh

ユーザ"postgres"の確認

$ id postgres
uid=502(postgres) gid=1000(postgres) groups=1000(postgres),12(everyone),61(localaccounts),701(com.apple.sharepoint.group.1),100(_lpoperator),702(com.apple.sharepoint.group.2)

3.2. データベースクラスタの場所を作成

データベースクラスタの場所を作成します。
今回は/udd/database/postgres/11/dataに作成します。

$ sudo su -
# mkdir -p /udd/database/postgres
# chown postgres:postgres /udd/database/postgres
# su - postgres
$ mkdir -p /udd/database/postgres/11/data
$ ls -al /udd/database/postgres/11/data
total 0
drwxr-xr-x  2 postgres  postgres  64 Sep 13 00:08 .
drwxr-xr-x  3 postgres  postgres  96 Sep 13 00:08 ..

3.3. ユーザ"postgres"における環境変数の設定

PostgreSQLを動作させるにあたり、実行ユーザに環境変数を設定しておけば、変数に設定した値を用いてサービスが実行されるため、引数でいちいち与える必要が無くなります。

ここでは、以下のように設定します。

環境変数名	値
POSTGRES_HOME	/usr/local
PGLIB	$POSTGRES_HOME/lib
PGDATA	/udd/database/postgres/11/data
MANPATH	"$MANPATH":$POSTGRES_HOME/man
LD_LIBRARY_PATH	"$LD_LIBRARY_PATH":"$PGLIB"
PATH	"$PATH":$POSTGRES_HOME/bin
PGOPT	5411

.bash_profileに記載します。

$ sudo su -
# su - postgres
$ touch .bash_profile
$ vim .bash_profile

.bash_profile

export POSTGRES_HOME=/usr/local/opt/postgres
export PGLIB=$POSTGRES_HOME/lib
export PGDATA=/udd/database/postgres/11/data
export MANPATH="$MANPATH":$POSTGRES_HOME/man
export LD_LIBRARY_PATH="$PD_LIBRARY_PATH":"$PGLIB"
export PATH="$PATH":$POSTGRES_HOME/bin
export PGOPT=5411

$ exec $SHELL -l
$ env
MANPATH=:/usr/local/man
PYENV_ROOT=/opt/anyenv/envs/pyenv
TERM=xterm-256color
SHELL=/bin/bash
PGOPT=5411
USER=postgres
PGLIB=/usr/local/lib
RBENV_ROOT=/opt/anyenv/envs/rbenv
POSTGRES_HOME=/usr/local
PATH=/opt/anyenv/envs/rbenv/shims:/opt/anyenv/envs/rbenv/bin:/opt/anyenv/envs/pyenv/shims:/opt/anyenv/envs/pyenv/bin:/opt/anyenv/envs/phpenv/shims:/opt/anyenv/envs/phpenv/bin:/opt/anyenv/bin:/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin:/Applications/Server.app/Contents/ServerRoot/usr/bin:/Applications/Server.app/Contents/ServerRoot/usr/sbin:/usr/local/bin
PWD=/Users/postgres
ANYENV_ROOT=/opt/anyenv
RBENV_SHELL=bash
PYENV_SHELL=bash
SHLVL=1
HOME=/Users/postgres
PHPENV_ROOT=/opt/anyenv/envs/phpenv
PGDATA=/udd/database/postgres/11/data
_=/usr/bin/env

環境変数が設定されたのを確認できたら、シェルから抜けます。

$ exit
# exit
$ 

4. インストール

いよいよPostgreSQLのインストールをします。

Homebrewからインストールします。
必ずHomebrewを構築したユーザで実行してください。

$ brew install postgresql
==> Installing dependencies for postgresql: icu4c
==> Installing postgresql dependency: icu4c
==> Downloading https://homebrew.bintray.com/bottles/icu4c-64.2.mojave.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/e8/e858556120acc0c2d52b8fb572b677856724cc28d24a7e1b2762d458a2977c8e?__gda__=exp=15683034
######################################################################## 100.0%
==> Pouring icu4c-64.2.mojave.bottle.tar.gz
==> Caveats
icu4c is keg-only, which means it was not symlinked into /usr/local,
because macOS provides libicucore.dylib (but nothing else).

If you need to have icu4c first in your PATH run:
  echo 'export PATH="/usr/local/opt/icu4c/bin:$PATH"' >> ~/.bash_profile
  echo 'export PATH="/usr/local/opt/icu4c/sbin:$PATH"' >> ~/.bash_profile

For compilers to find icu4c you may need to set:
  export LDFLAGS="-L/usr/local/opt/icu4c/lib"
  export CPPFLAGS="-I/usr/local/opt/icu4c/include"

==> Summary
🍺  /usr/local/Cellar/icu4c/64.2: 257 files, 69.2MB
==> Installing postgresql
==> Downloading https://homebrew.bintray.com/bottles/postgresql-11.5_1.mojave.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/46/463c6a192a0b6a5d1359b68db24003b2dac6895cdb86c827c41bf03fffd856d6?__gda__=exp=15683035
######################################################################## 100.0%
==> Pouring postgresql-11.5_1.mojave.bottle.tar.gz
==> /usr/local/Cellar/postgresql/11.5_1/bin/initdb --locale=C -E UTF-8 /usr/local/var/postgres
==> Caveats
To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database

To have launchd start postgresql now and restart at login:
  brew services start postgresql
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/postgres start
==> Summary
🍺  /usr/local/Cellar/postgresql/11.5_1: 3,189 files, 35.4MB
==> Caveats
==> icu4c
icu4c is keg-only, which means it was not symlinked into /usr/local,
because macOS provides libicucore.dylib (but nothing else).

If you need to have icu4c first in your PATH run:
  echo 'export PATH="/usr/local/opt/icu4c/bin:$PATH"' >> ~/.bash_profile
  echo 'export PATH="/usr/local/opt/icu4c/sbin:$PATH"' >> ~/.bash_profile

For compilers to find icu4c you may need to set:
  export LDFLAGS="-L/usr/local/opt/icu4c/lib"
  export CPPFLAGS="-I/usr/local/opt/icu4c/include"

==> postgresql
To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database

To have launchd start postgresql now and restart at login:
  brew services start postgresql
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/postgres start

インストールされたかどうかの確認を行います。

$ which postgres
/usr/local/bin/postgres

5. データベースクラスタの初期化

データベースクラスタを初期化します。

$ sudo su -
# su - postgres
$ id  
uid=502(postgres) gid=1000(postgres) groups=1000(postgres),12(everyone),20(staff),61(localaccounts),701(com.apple.sharepoint.group.1),100(_lpoperator),702(com.apple.sharepoint.group.2)

$ initdb --no-locale -U postgres -D $PGDATA
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 "C".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /udd/database/postgres/11/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Asia/Tokyo
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

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:

    pg_ctl -D /udd/database/postgres/11/data -l logfile start

動作確認します。

$ pg_ctl -D $PGDATA -l test.log start
waiting for server to start.... done
server started
$ pg_ctl status
pg_ctl: server is running (PID: 23191)
/usr/local/Cellar/postgresql/11.5_1/bin/postgres "-D" "/udd/database/postgres/11/data"
s$ pg_ctl stop
waiting for server to shut down.... done
server stopped

6. サービス化

サービス化を行います。
サービス化するのに必要なplistファイルは、Homebrewのインストール先/usr/local/opt/postgres/homebrew.mxcl/postgresql.plistに格納されています。
それを/Library/LaunchDaemon/にコピーし、編集して使用します。

$ exit
# cp /usr/local/opt/postgres/homebrew.mxcl.postgresql.plist /Library/LaunchDaemons/

plistファイルを編集します。

# cd /Library/LaunchDeamons/
# vi homebrew.mxcl.postgresql.plist

homebrew.mxcl.postgresql.plist

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
  <key>KeepAlive</key>
  <true/>
  <key>Label</key>
  <string>homebrew.mxcl.postgresql</string>
  <key>Username</key>
  <string>postgres</string>
  <key>ProgramArguments</key>
  <array>
    <string>/usr/local/opt/postgresql/bin/postgres</string>
    <string>-p</string>
    <string>5411</string>
    <string>-D</string>
    <string>/udd/database/postgres/11/data</string>
  </array>
  <key>RunAtLoad</key>
  <true/>
  <key>WorkingDirectory</key>
  <string>/usr/local</string>
  <key>StandardOutPath</key>
  <string>/usr/local/var/log/postgres/postgres.log</string>
  <key>StandardErrorPath</key>
  <string>/usr/local/var/log/postgres/postgres.log</string>
</dict>
</plist>

ログ出力先ディレクトリを作成します。

$ sudo mkdir /usr/local/var/log/postgres
$ sudo chown postgres:postgres /usr/local/var/log/postgres
$ ls -dl /usr/local/var/log/postgres
drwxr-xr-x  2 postgres  postgres  64 Sep 13 01:14 /usr/local/var/log/postgres```

では、サービスを起動してみましょう。

# launchctl load -w /Library/LaunchDaemons/homebrew.mxcl.postgresql.plist 
# launchctl list | grep postgres
208	0	com.apple.DeviceManagement.postgres
23327	0	homebrew.mxcl.postgresql

プロセスが動いていることを確認できました。

7. データベースの作成

データベースを作成します。
以下の要件で作成します。

項目	値
PGUSER	postgres
Owner	sruser
template	template0
database_name	showroom_data
encode	UTF-8

まずはユーザを作成します。

$ sudo su -
# su - postgres
$ createuser -p 5411 --interactive
Enter name of role to add: sruser
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
$

確認します。

$ psql -p 5411
psql (11.5)
Type "help" for help.

postgres=# \u
Invalid command \u. Try \? for help.
postgres=# \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \crosstabview [COLUMNS] execute query and display results in crosstab
  \errverbose            show most recent error message at maximum verbosity
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \gdesc                 describe result of query, without executing it
  \gexec                 execute query, then execute each value in its result
  \gset [PREFIX]         execute query and store results in psql variables
  \gx [FILE]             as \g, but forces expanded output mode
  \q                     quit psql
  \watch [SEC]           execute query every SEC seconds

Help
  \? [commands]          show help on backslash commands
  \? options             show help on psql command-line options
  \? variables           show help on special variables
  \h [NAME]              help on syntax of SQL commands, * for all commands

Query Buffer
  \e [FILE] [LINE]       edit the query buffer (or file) with external editor
  \ef [FUNCNAME [LINE]]  edit function definition with external editor
  \ev [VIEWNAME [LINE]]  edit view definition with external editor
  \p                     show the contents of the query buffer
  \r                     reset (clear) the query buffer
  \s [FILE]              display history or save it to file
  \w FILE                write query buffer to file

Input/Output
  \copy ...              perform SQL COPY with data stream to the client host
  \echo [STRING]         write string to standard output
  \i FILE                execute commands from file
  \ir FILE               as \i, but relative to location of current script
  \o [FILE]              send all query results to file or |pipe
  \qecho [STRING]        write string to query output stream (see \o)

Conditional
  \if EXPR               begin conditional block
  \elif EXPR             alternative within current conditional block
  \else                  final alternative within current conditional block
  \endif                 end conditional block

Informational
  (options: S = show system objects, + = additional detail)
  \d[S+]                 list tables, views, and sequences
  \d[S+]  NAME           describe table, view, sequence, or index
  \da[S]  [PATTERN]      list aggregates
  \dA[+]  [PATTERN]      list access methods
  \db[+]  [PATTERN]      list tablespaces
  \dc[S+] [PATTERN]      list conversions
  \dC[+]  [PATTERN]      list casts
  \dd[S]  [PATTERN]      show object descriptions not displayed elsewhere
  \dD[S+] [PATTERN]      list domains
  \ddp    [PATTERN]      list default privileges
  \dE[S+] [PATTERN]      list foreign tables
  \det[+] [PATTERN]      list foreign tables
  \des[+] [PATTERN]      list foreign servers
  \deu[+] [PATTERN]      list user mappings
  \dew[+] [PATTERN]      list foreign-data wrappers
  \df[anptw][S+] [PATRN] list [only agg/normal/procedures/trigger/window] functions
  \dF[+]  [PATTERN]      list text search configurations
  \dFd[+] [PATTERN]      list text search dictionaries
  \dFp[+] [PATTERN]      list text search parsers
  \dFt[+] [PATTERN]      list text search templates
  \dg[S+] [PATTERN]      list roles
  \di[S+] [PATTERN]      list indexes
  \dl                    list large objects, same as \lo_list
  \dL[S+] [PATTERN]      list procedural languages
  \dm[S+] [PATTERN]      list materialized views
  \dn[S+] [PATTERN]      list schemas
  \do[S]  [PATTERN]      list operators
  \dO[S+] [PATTERN]      list collations
  \dp     [PATTERN]      list table, view, and sequence access privileges
  \drds [PATRN1 [PATRN2]] list per-database role settings
  \dRp[+] [PATTERN]      list replication publications
  \dRs[+] [PATTERN]      list replication subscriptions
  \ds[S+] [PATTERN]      list sequences
  \dt[S+] [PATTERN]      list tables
  \dT[S+] [PATTERN]      list data types
postgres=# \dg
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 sruser    | Create DB                                                  | {}

postgres=# \q

次にデータベースを作成します。

$ createdb -p 5411 -E UTF-8 -T template0 -O sruser showroom_data

確認します。

$ psql -p 5411 -U sruser -d showroom_data
psql (11.5)
Type "help" for help.

showroom_data=> ¥q
$

8. pgAdmin4のインストールと確認

pgAdmin4をインストールします。

8.1. pgAdmin4とは

グラフィカルなユーザー・インターフェイスを持つ、 PostgreSQL専用のデータベース管理クライアントソフトウェアです。 黒い画面じゃなくて、マウスで操作できるやつ。 DBとかスキーマとかテーブルとか作ったり、入っているデータをみたりできます。

8.2. インストール

Homebrew Caskでインストールします。

$ brew cask install pgadmin4
==> Satisfying dependencies
==> Downloading https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v4.12/macos/pgadmin4-4.12.dmg
######################################################################## 100.0%
==> Verifying SHA-256 checksum for Cask 'pgadmin4'.
==> Installing Cask pgadmin4
==> Moving App 'pgAdmin 4.app' to '/Applications/pgAdmin 4.app'.
🍺  pgadmin4 was successfully installed!

8.3. 動作確認と設定

LaunchPadにpgAdmin4がインストールされています。
これを実行します。

実行すると、Passwordを訊いてきます。

適当に新しいパスワード入れます。
入れてOKを押すと、ウィンドウが開いてきます。

サーバの設定をします。

作成したデータベースへの接続を確認。

9. さいごに

データベースの構築自体は、Homebrewを用いることで比較的簡単に行うことができました。
それよりも、データベースを構築する際は、あらかじめ決めておくべき事がたくさんあり、それを事前にしておかないとあとで変更が効かない場合が多々あるので注意が必要です。

10. 参考サイト

10.1. OSのユーザ作成周り

MacでCLIから新しくuserとgroupを作成 - Qiita
Espresso & Onigiri
Mac OS Xでコマンドラインからユーザ追加 - Qiita

10.2. PostgreSQL関係

スキーマ
PostgreSQLの環境をmacのローカルに作成＆SQL操作（＆ついでにDataGripへの接続） - Qiita