過去に似たような記事がたくさん作ってしまっているが、これが現状最新。
インストールから初期設定
まずは環境構築。ここではMacOSにPostgreSQL(v14)をインストールしていく。Dockerとかでもいいんだけども。
## 過去のファイルを削除 $ brew uninstall postgresql $ rm -rf /usr/local/var/postgres/ ## brewでインストール $ brew install postgresql $ psql --version psql (PostgreSQL) 14.1
サーバーの起動・停止コマンドは下記の通り。
$ brew services start postgresql ==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql) # サーバーの停止コマンド # brew services stop postgresql # Stopping `postgresql`... (might take a while) # ==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql)
.bash_profileに下記のPATHを追加して、更新しておく。PATHはPostgreSQLコマンドのパスで、PGDATAはデータベースクラスタのパス。
# vimとかでもなんでもよい $ open ~/.bash_profile #----------------------------------- # Setting PATH for postgres14.1.1 export PATH=/usr/local/Cellar/postgresql/14.1_1/bin/:$PATH export PGDATA=/usr/local/var/postgres #----------------------------------- $ source ~/.bash_profile
データベースクラスタというのはPostgreSQLのデータの記憶領域のこと。ファイルシステム上にディレクトリが作成され管理されていく。
initdbで初期化
initdbでデータベースクラスタを作成。初期のデータベースクラスタはpostgres、template0、template1という3つのデータベースしか存在していない。-Eはエンコーディングの設定。
$ initdb /usr/local/var/postgres -E utf8 ## initdb したときにエラーになる場合は/usr/local/var/postgresディレクトリを削除 ## 最初にpostgresディレクトリをまるごと削除しているのになんでだろうか。 $ ls $PGDATA PG_VERSION pg_commit_ts pg_ident.conf pg_notify pg_snapshots pg_subtrans pg_wal postgresql.conf base pg_dynshmem pg_logical pg_replslot pg_stat pg_tblspc pg_xact postmaster.opts global pg_hba.conf pg_multixact pg_serial pg_stat_tmp pg_twophase postgresql.auto.conf postmaster.pid
postgresql.confの中身の確認
今回の環境構築では必要ないけれど、postgresql.confには、リッスンアドレスやポート番号、ログの設定などが記載されているのでメモ。
$ cat $PGDATA/postgresql.conf # ----------------------------- # PostgreSQL configuration file # ----------------------------- # 【略】
インストール直後はMACのユーザ名でスーパユーザー(aki)が作成される。下記に書いてある。
## MACのユーザ名でスーパーユーザーが作られる
## $ echo $USER
## aki
## DBにアクセスする際のオプション
## `-h`はホスト、`-p`はポート番号、`-U`はユーザー名、`-d`は接続先DB、`-W`はパスワードの指定。
## psql -h 127.0.0.1 -p 5432 -U aki -d postgres
$ psql -U aki -d postgres
psql (14.1)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
aki | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
スーパーユーザー(aki)しかいないので、管理用のロール(worker)を作成する。とりあえず、workerには、Create role, Create DBを付与しておく。
postgres=# CREATE ROLE worker WITH createdb createrole LOGIN PASSWORD '<YOUR PASSWORD>';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
aki | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres |Create role, Create DB | {}
## ロールの削除はDROP ROLE
## postgres=# DROP ROLE worker;
## DROP ROLE
この状態だとスーパーユーザーはpsql -U aki -d postgresでデータベースにアクセスできる状態なので、スーパーユーザーにもパスワード認証を要求するようにする。そのためにpg_hba.confの中身を書き換えるが、先にスーパーユーザーにパスワードを付与しておく。
postgres=# ALTER USER aki WITH PASSWORD '<YOUR PASSWORD>'; ALTER ROLE postgres-# \q ## 下記の部分を変更する peer → md5 $vim /usr/local/var/postgres/pg_hba.conf # "local" is for Unix domain socket connections only local all all peer ↓ # "local" is for Unix domain socket connections only local all all md5 # データベースを再起動 $ brew services restart postgresql # スーパーユーザーで入ろうとするとパスワードが要求される $ psql -U aki -d postgres Password for user aki:<YOUR PASSWORD> psql (14.1)
DBの作成
workerでpostgresデータベースにアクセス。ニューヨークのタクシーのトリップデータ用のサンプルデータベースnytaxiを作成する。workerで作成したので、Ownerがworkerになっている。
$ psql -U worker -d postgres -W
Password:
psql (14.1)
Type "help" for help.
postgres=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
postgres | aki | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
template0 | aki | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/aki +
| | | | | aki=CTc/aki
template1 | aki | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/aki +
| | | | | aki=CTc/aki
(3 rows)
postgres=> CREATE DATABASE nytaxi;
CREATE DATABASE
postgres=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+--------+----------+-------------+-------------+-------------------
nytaxi | worker | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
postgres | aki | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
template0 | aki | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/aki +
| | | | | aki=CTc/aki
template1 | aki | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/aki +
| | | | | aki=CTc/aki
(4 rows)
\c [database name]でデータベースを変更できるので、postgresからnytaxiに切り替え。
postgres=> \c nytaxi; Password: You are now connected to database "nytaxi" as user "worker". nytaxi=> ## ログアウト状態からは下記で`nytaxi`データベースに接続する。オプションは必要であれば。 ## $psql -h 127.0.0.1 -p 5432 -U worker -d nytaxi -W
テーブルの作成
ここではpublicスキーマの中にテーブルtripdataを作成する。まずはテーブル定義を作成。
nytaxi=> CREATE TABLE public.tripdata(
VendorID int,
tpep_pickup_datetime timestamp,
tpep_dropoff_datetime timestamp,
passnger_count int,
trip_distance real,
RatecodeID int,
store_and_fwd_flag varchar(10),
PULocationID int,
DOLocationID int,
payment_type int,
fare_amount real,
extra real,
mta_tax real,
tip_amount real,
tolls_amount real,
improvement_surcharge real,
total_amount real,
congestion_surcharge real);
CREATE TABLE
nytaxi=> \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+----------+-------+--------+-------------+---------------+---------+-------------
public | tripdata | table | worker | permanent | heap | 0 bytes |
(1 row)
テーブルへのデータ投入はCOPYコマンドで行なう。
# CSV importの場合 nytaxi=> \copy public.tripdata from '/Users/aki/Desktop/log_2021_01_01.csv' (encoding 'utf8', format csv, header true); COPY 24827 nytaxi=> \copy public.tripdata from '/Users/aki/Desktop/log_2021_01_02.csv' (encoding 'utf8', format csv, header true); COPY 34309 nytaxi=> select count(1) from public.tripdata; count ------- 59136 (1 row)
ターミナルからCSV exportの場合
テーブルのデータをエクスポートしようとすると、pg_write_server_filesのメンバーである必要があるとのこと。
$ psql -U worker -d nytaxi -c "COPY (SELECT * FROM public.tripdata limit 10) to '/Users/aki/Desktop/data.csv' WITH CSV HEADER;" ERROR: must be superuser or a member of the pg_write_server_files role to COPY to a file HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
GRANTで権限を付与しておく。
nytaxi=> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-------------------------
aki | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
worker | Create role, Create DB | {}
postgres=> GRANT pg_write_server_files TO worker;
GRANT ROLE
# readに関するエラーが出ればこれで対応
postgres=> GRANT pg_read_server_files TO worker;
GRANT ROLE
nytaxi=> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-------------------------
aki | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
worker | Create role, Create DB | {pg_write_server_files}
$ cat /Users/aki/Desktop/data.csv
vendorid,tpep_pickup_datetime,tpep_dropoff_datetime,passnger_count,trip_distance,ratecodeid,store_and_fwd_flag,pulocationid,dolocationid,payment_type,fare_amount,extra,mta_tax,tip_amount,tolls_amount,improvement_surcharge,total_amount,congestion_surcharge
1,2021-01-01 00:30:10,2021-01-01 00:36:12,1,2.1,1,N,142,43,2,8,3,0.5,0,0,0.3,11.8,2.5
1,2021-01-01 00:51:20,2021-01-01 00:52:19,1,0.2,1,N,238,151,2,3,0.5,0.5,0,0,0.3,4.3,0
1,2021-01-01 00:43:30,2021-01-01 01:11:06,1,14.7,1,N,132,165,1,42,0.5,0.5,8.65,0,0.3,51.95,0
1,2021-01-01 00:15:48,2021-01-01 00:31:01,0,10.6,1,N,138,132,1,29,0.5,0.5,6.05,0,0.3,36.35,0
2,2021-01-01 00:31:49,2021-01-01 00:48:21,1,4.94,1,N,68,33,1,16.5,0.5,0.5,4.06,0,0.3,24.36,2.5
1,2021-01-01 00:16:29,2021-01-01 00:24:30,1,1.6,1,N,224,68,1,8,3,0.5,2.35,0,0.3,14.15,2.5
1,2021-01-01 00:00:28,2021-01-01 00:17:28,1,4.1,1,N,95,157,2,16,0.5,0.5,0,0,0.3,17.3,0
1,2021-01-01 00:12:29,2021-01-01 00:30:34,1,5.7,1,N,90,40,2,18,3,0.5,0,0,0.3,21.8,2.5
1,2021-01-01 00:39:16,2021-01-01 01:00:13,1,9.1,1,N,97,129,4,27.5,0.5,0.5,0,0,0.3,28.8,0
1,2021-01-01 00:26:12,2021-01-01 00:39:46,2,2.7,1,N,263,142,1,12,3,0.5,3.15,0,0.3,18.95,2.5
## 権限を削除する場合
## nytaxi=> REVOKE pg_write_server_files FROM worker;
## REVOKE ROLE
おまけ
SaaSのサンプルデータを投入する方法は下記のポストの末尾参照。
