はじめに

PostgresSQLの環境構築で行うことをまとめておく。

古いPostgresSQLの削除

昔に入れっぱなしとかで、古いPostgresSQLがあるかもしれないのでとりあえず削除。

$ brew uninstall postgresql
$ rm -rf /usr/local/var/postgres/

PostgresSQLのインストール

brewでインストールして、文字コードの設定を行う。

$ brew install postgresql
$ psql --version
psql (PostgreSQL) 12.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)

PostgresSQLのインストールが完了すると、PostgresSQLサーバー用の管理ユーザーpostgresが自動で作成される。psqlコマンドのパスを通すために.bash_profileを編集。

# $ vim ~/.bash_profileでもよしなに
$ open ~/.bash_profile

# 下記postgresのパスを追記
export PATH=/usr/local/Cellar/postgresql/12.1/bin/:$PATH
export PGDATA=/usr/local/var/postgres
export PATH=${POSTGRES_HOME}/bin:${PATH}

bash_profileを読み込みなおす。

$ source ~/.bash_profile

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

データベースクラスタとはPostgresSQLが管理するデータを実際に記憶する領域のこと。インストール直後であれば、DB情報やユーザー情報などのグローバルデータ、template0、template1、postgresというデータベースが作られている。というよりも、initdbコマンドで作成している。initdbコマンドでは複数のファイルが作られていることがわかる。

$ initdb /usr/local/var/postgres -E utf8

$ cd /usr/local/var/postgres
$ ls
PG_VERSION            pg_hba.conf           pg_replslot/          pg_subtrans/          postgresql.auto.conf
base/                 pg_ident.conf         pg_serial/            pg_tblspc/            postgresql.conf
global/               pg_logical/           pg_snapshots/         pg_twophase/          postmaster.opts
pg_commit_ts/         pg_multixact/         pg_stat/              pg_wal/               postmaster.pid
pg_dynshmem/          pg_notify/            pg_stat_tmp/          pg_xact/

例えばpostgresql.confにはポートなどの接続情報が記載されている。

$ vim postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'     # what IP address(es) to listen on;
                    # comma-separated list of addresses;
                    # defaults to 'localhost'; use '*' for all
                    # (change requires restart)
#port = 5432                # (change requires restart)

ユーザーの作成

initdbコマンドを実行した後は、スーパーユーザーのユーザーのpostgresしかいないので、createuserコマンドで作成。Macであればユーザー名がスーパユーザ名として設定される。ユーザー作成は下記の通り。-sでスーパユーザーでの作成。createdbでデータベースの作成が可能。

$ createuser -P testuser
Enter password for new role: testuser
Enter it again: testuser

createdbコマンドと-Oオプションをつけてデータベースの所有者を指定しながらデータベースを作成する。

$ createdb -O testuser testdb
$ psql -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
 testdb    | testuser | UTF8     | C       | C     | 
(4 rows)

データベースにアクセスして、テストテーブルを作成。

$ psql -d testdb -U testuser
psql (12.1)
Type "help" for help.

testdb=>
CREATE TABLE meibo(
  id SERIAL,
  name TEXT,
  zip CHAR(8),
  address TEXT,
  birth DATE,
  sex BOOLEAN
);

testdb=>
INSERT INTO meibo(name, zip, address, birth, sex) VALUES ('宮本 詩織', '111-1111', '東京都', '2000/01/01', FALSE);
INSERT INTO meibo(name, zip, address, birth, sex) VALUES ('長友 玲那', '111-1211', '神奈川件', '1961/6/1', TRUE);
INSERT INTO meibo(name, zip, address, birth, sex) VALUES ('村上 だん吉', '111-1231', '大阪府', '1989/1/22', FALSE);
INSERT INTO meibo(name, zip, address, birth, sex) VALUES ('松本 剛基', '111-1234', '京都府', '2010/01/01', TRUE);
INSERT INTO meibo(name, zip, address, birth, sex) VALUES ('鈴木花子', '121-1111', '石川県', '1964/5/10', FALSE);
INSERT INTO meibo(name, zip, address, birth, sex) VALUES ('織田 隆太', '123-1111', '北海道', '1956/8/1', TRUE);
INSERT INTO meibo(name, zip, address, birth, sex) VALUES ('田淵 一徳', '111-4111', '青森県', '1969/8/20', TRUE);
INSERT INTO meibo(name, zip, address, birth, sex) VALUES ('関 ノブヒコ', '111-4511', '沖縄県', '1939/7/9', FALSE);
INSERT INTO meibo(name, zip, address, birth, sex) VALUES ('田島 真悠子 ', '111-4561', '大分県', '1968/12/26', TRUE);
INSERT INTO meibo(name, zip, address, birth, sex) VALUES ('宇佐美 芳正 ', '111-4567', NULL,NULL, FALSE);
INSERT INTO meibo(name, zip, address, birth, sex) VALUES ('角 鉄洋', '111-9999', '愛知県',NULL, FALSE);


testdb=> select * from meibo;
 id |    name     |   zip    | address  |   birth    | sex 
----+-------------+----------+----------+------------+-----
  1 | 宮本 詩織   | 111-1111 | 東京都   | 2000-01-01 | f
  2 | 長友 玲那   | 111-1211 | 神奈川件 | 1961-06-01 | t
  3 | 村上 だん吉 | 111-1231 | 大阪府   | 1989-01-22 | f
  4 | 松本 剛基   | 111-1234 | 京都府   | 2010-01-01 | t
  5 | 鈴木花子    | 121-1111 | 石川県   | 1964-05-10 | f
  6 | 織田 隆太   | 123-1111 | 北海道   | 1956-08-01 | t
  7 | 田淵 一徳   | 111-4111 | 青森県   | 1969-08-20 | t
  8 | 関 ノブヒコ | 111-4511 | 沖縄県   | 1939-07-09 | f
  9 | 田島 真悠子 | 111-4561 | 大分県   | 1968-12-26 | t
 10 | 宇佐美 芳正 | 111-4567 |          |            | f
 11 | 角 鉄洋     | 111-9999 | 愛知県   |            | f
(11 rows)

# インサートでアドレス誤字があるので更新処理
testdb=> UPDATE meibo SET address='神奈川県' where name='長友 玲那';

下記でサーバの起動と停止を行う。

testdb=> \q

$ brew services stop postgresql
Stopping `postgresql`... (might take a while)
==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql)

便利なコマンド

copyコマンドでcsvをインポートする。スーパーユーザーか、pg_read_server、pg_write_server、pg_excute_serverの権限が必要。

testdb=#
testdb=# CREATE TABLE orders(
  Time DATE,
  CustID VARCHAR(20),
  Age VARCHAR(1),
  Area VARCHAR(1),
  ProductSubClass VARCHAR(20),
  ProductID VARCHAR(20),
  Amount INTEGER,
  Asset INTEGER,
  SalesPrice INTEGER
);

CREATE TABLE

$ head -n10 /Users/user/Desktop/orders.csv
Time,CustID,Age,Area,ProductSubClass,ProductID,Amount,Asset,SalesPrice
2000/11/01,00046855,D,E,110411,4710085120468,3,51,57
2000/11/01,00539166,E,E,130315,4714981010038,2,56,48
2000/11/01,00663373,F,E,110217,4710265847666,1,180,135
2000/11/01,00340625,A,E,110411,4710085120697,1,17,24
2000/11/01,00236645,D,H,712901,8999002568972,2,128,170
2000/11/01,01704129,B,E,110407,4710734000011,1,38,46
2000/11/01,00841528,C,E,110102,4710311107102,1,20,28
2000/11/01,00768566,K,E,110401,4710088410382,1,44,55
2000/11/01,00217361,F,E,130401,4711587809011,1,76,90

## Read from csv
testdb=# \COPY orders FROM '/Users/user/Desktop/orders.csv' csv header;
COPY 10000

testdb=# select * from orders limit 10;
    time    |  custid  | age | area | productsubclass |   productid   | amount | asset | salesprice 
------------+----------+-----+------+-----------------+---------------+--------+-------+------------
 2000-11-01 | 00046855 | D   | E    | 110411          | 4710085120468 |      3 |    51 |         57
 2000-11-01 | 00539166 | E   | E    | 130315          | 4714981010038 |      2 |    56 |         48
 2000-11-01 | 00663373 | F   | E    | 110217          | 4710265847666 |      1 |   180 |        135
 2000-11-01 | 00340625 | A   | E    | 110411          | 4710085120697 |      1 |    17 |         24
 2000-11-01 | 00236645 | D   | H    | 712901          | 8999002568972 |      2 |   128 |        170
 2000-11-01 | 01704129 | B   | E    | 110407          | 4710734000011 |      1 |    38 |         46
 2000-11-01 | 00841528 | C   | E    | 110102          | 4710311107102 |      1 |    20 |         28
 2000-11-01 | 00768566 | K   | E    | 110401          | 4710088410382 |      1 |    44 |         55
 2000-11-01 | 00217361 | F   | E    | 130401          | 4711587809011 |      1 |    76 |         90
 2000-11-01 | 02007052 | D   | E    | 110504          | 4710323168054 |      1 |    17 |         20
(10 rows)

testdb=# select count(1) from orders;
 count 
-------
 10000
(1 row)

## Write out csv
testdb=# \COPY orders TO '/Users/user/Desktop/orders_writeout.csv' csv header;
COPY 10000

$ head -n10 /Users/user/Desktop/orders_writeout.csv
time,custid,age,area,productsubclass,productid,amount,asset,salesprice
2000-11-01,00046855,D,E,110411,4710085120468,3,51,57
2000-11-01,00539166,E,E,130315,4714981010038,2,56,48
2000-11-01,00663373,F,E,110217,4710265847666,1,180,135
2000-11-01,00340625,A,E,110411,4710085120697,1,17,24
2000-11-01,00236645,D,H,712901,8999002568972,2,128,170
2000-11-01,01704129,B,E,110407,4710734000011,1,38,46
2000-11-01,00841528,C,E,110102,4710311107102,1,20,28
2000-11-01,00768566,K,E,110401,4710088410382,1,44,55
2000-11-01,00217361,F,E,130401,4711587809011,1,76,90

MySQLのこと。

MySQLのことについてまとめているブログ。他人に見せる用でもなく、自分の勉強備忘録。検索インデックスも外してるので、辿りついた方・・・ようこそ。そんな大した情報ないですよ?!たまにアルゴリズムの練習も

PostgresSQLのインストール

はじめに

古いPostgresSQLの削除

PostgresSQLのインストール

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

ユーザーの作成

便利なコマンド