インストールから初期設定
まずは環境構築。ここではMacOSにPostgreSQL(v13.0)をインストールしていく。Dockerとかでもいいんだけども。
## 過去のゴミを削除 $ brew uninstall postgresql $ rm -rf /usr/local/var/postgres/ $ brew install postgresql $ psql --version psql (PostgreSQL) 13.0
サーバーの起動・停止コマンドは下記の通り。
➜ 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) # ~ took 6s # # ➜ brew services start postgresql # ==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)
.bash_profileに下記のPATHを追加して、更新しておく。PATHはPostgreSQLコマンドのパスで、PGDATAはデータベースクラスタのパス。
➜ open ~/.bash_profile #----------------------------------- # Setting PATH for postgres13.0 export PATH=/usr/local/Cellar/postgresql/13.0/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 ➜ 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のユーザ名でスーパユーザーが作成される。ここではpostgresという名前のロールを作成。
➜ psql -h 127.0.0.1 -p 5432 -U aki -d postgres
psql (13.0)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
aki | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
スーパーユーザーのakiしかいないので、作業用のロールpostgresを作成する。とりあえず、Create role, Create DBを付与しておく。-hはホスト、-pはポート番号、-Uはユーザー名、-dは接続先DB、-Wはパスワードの指定。
postgres=# create role postgres with createdb createrole login password 'postgres';
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 | {}
postgres-# \q
➜ psql -h 127.0.0.1 -p 5432 -U postgres -d postgres -W
Password:
psql (13.0)
Type "help" for help.
postgres=>
postgresでpostgresデータベースにアクセスしている。適当にデータベースtestを作成する。postgresで作成したので、Ownerがpostgresになっている。
postgres=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
postgres | aki | UTF8 | C | C |
template0 | aki | UTF8 | C | C | =c/aki +
| | | | | aki=CTc/aki
template1 | aki | UTF8 | C | C | =c/aki +
| | | | | aki=CTc/aki
(3 rows)
postgres=> create database test;
CREATE DATABASE
postgres=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-------------------
postgres | aki | UTF8 | C | C |
template0 | aki | UTF8 | C | C | =c/aki +
| | | | | aki=CTc/aki
template1 | aki | UTF8 | C | C | =c/aki +
| | | | | aki=CTc/aki
test | postgres | UTF8 | C | C |
(4 rows)
\c [database name]でデータベースを変更。publicスキーマーが確認できる。
postgres=> \c test; Password for user postgres: You are now connected to database "test" as user "postgres". test=> \dn List of schemas Name | Owner --------+------- public | aki (1 row)
testデータベースができたので、一度ログアウトしてから、testデータベースに接続する。
postgres=# \q
~
➜ psql -h 127.0.0.1 -p 5432 -U postgres -d test -W
Password:
psql (13.0)
Type "help" for help.
test=# \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+-------+-------------------+------------------------
public | aki | aki=UC/aki +| standard public schema
| | =UC/aki |
(1 row)
PostgreSQLでもデータはテーブルに格納される。テーブルはもちろん複数作成することが可能で、テーブルをまとめているのがデータベース…のはずが、PostgreSQLには「スキーマ」と呼ばれるものがある。スキーマは、データベースに作成されるテーブルや関数などのオブジェクトをグループ化するもので、スキーマが異なれば、同じテーブル名でテーブルを作成することができる。データベースを作成すると、publicスキーマが作成される。このスキーマは、デフォルトですべてのロールにアクセス権限とcreate権限が付与されており、publicスキーマの中にどのロールでもテーブルを作成することができる公共施設のような扱いのスキーマ。もちろんpublic以外にも自由にスキーマ作成することはできる。データベースクラスタのイメージは下記の通り。
DATABASE CLUSTER ├─test │ ├─test_schema ※SCHEMA │ │ ├─tableA │ │ ├─tableB │ │ └─tableC │ └─public ※SCHEMA │ └─public_table ├─postgres │ └─public ※SCHEMA ├─template └─template0
create schemaコマンドでtest_schemaを作成できる。
test=# create schema test_schema;
CREATE SCHEMA
test=# \dn+
List of schemas
Name | Owner | Access privileges | Description
-------------+----------+-------------------+------------------------
public | aki | aki=UC/aki +| standard public schema
| | =UC/aki |
test_schema | postgres | |
(2 rows)
スキーマーに権限を付与する場合は、grantコマンドで行う。
test=# grant create on schema test_schema to postgres;
GRANT
test=# \dn+
List of schemas
Name | Owner | Access privileges | Description
-------------+----------+----------------------+------------------------
public | aki | aki=UC/aki +| standard public schema
| | =UC/aki |
test_schema | postgres | postgres=UC/postgres |
(2 rows)
このあと色々と作業や検証ができるように、データベースecを作ってpublic.ordersテーブルに3200万行くらいのECのサンプル注文データと10万行くらいの会員マスタpublic.usersをインポートしておく。
test=# create database ec;
CREATE DATABASE
test=# \c ec;
Password for user postgres:
ec=#
create table public.orders (
order_id varchar(10),
order_datetime timestamp,
user_id varchar(10),
branch_num varchar(5),
price int,
product_id varchar(10)
);
## 1~2分くらいで完了する
ec=# \copy public.orders from '/Users/aki/Desktop/orders_30m.csv' (encoding 'utf8', format csv, header true);
COPY 32434489
ec=# select count(1) from public.orders;
count
----------
32434489
(1 row)
ec=# select * from public.orders limit 10;
order_id | order_datetime | user_id | branch_num | price | product_id
----------+---------------------+---------+------------+-------+------------
2 | 2003-12-20 09:10:59 | 53605 | 1 | 200 | 33120
2 | 2003-12-20 09:10:59 | 53605 | 2 | 110 | 28985
2 | 2003-12-20 09:10:59 | 53605 | 3 | 410 | 9327
2 | 2003-12-20 09:10:59 | 53605 | 4 | 230 | 45918
2 | 2003-12-20 09:10:59 | 53605 | 5 | 330 | 30035
2 | 2003-12-20 09:10:59 | 53605 | 6 | 120 | 17794
2 | 2003-12-20 09:10:59 | 53605 | 7 | 410 | 40141
2 | 2003-12-20 09:10:59 | 53605 | 8 | 460 | 1819
2 | 2003-12-20 09:10:59 | 53605 | 9 | 320 | 43668
3 | 2006-12-29 09:21:27 | 561254 | 1 | 400 | 33754
(10 rows)
ec=#
create table public.users (
user_id varchar(10),
gender varchar(10),
area varchar(10),
product_id int
);
ec=# \copy public.users from '/Users/aki/Desktop/cust_mst_1m.csv' (encoding 'utf8', format csv, header true);
COPY 959676
ec=# select count(1) from public.users;
count
--------
959676
(1 row)
ec=# select * from public.users limit 10;
user_id | gender | area | product_id
---------+--------+--------+------------
53605 | Male | area_A | 39
561254 | Female | area_G | 40
971141 | Female | area_C | 24
431258 | Female | area_B | 34
826277 | Male | area_D | 45
159213 | Male | area_A | 36
671844 | Male | area_A | 30
762894 | Female | area_B | 61
767441 | | area_A | 42
560816 | Female | area_C | 37
(10 rows)
