SQL • 無限: 7月 2016

2016年7月22日星期五

PostgreSQL 監控程式資源

使用套件

POWA

使用原生程式

最佳化工具

PostgreSQL Wiki

部落客文章

https://www.datadoghq.com/blog/100x-faster-postgres-performance-by-changing-1-line/
http://www.butleranalytics.com/5-free-postgresql-dashboard-software/

官網記錄

https://www.postgresql.org/download/products/5-reporting-tools/

2016年7月18日星期一

PostgreSQL 備份與復原

目標設定：

PostgreSQL 的備份資料庫指令：pg_dump

pg_dump [資料庫名稱] -U [使用者帳號] -f [備份檔名稱.備份日期.sql]

PostgreSQL 的回復資料庫指令：psql

psql -f [備份檔名稱.備份日期.sql] [資料庫] [使用者名稱]

操作練習流程

先轉成 postgres 使用者：
```
# su - postgres
```

備份 student 資料庫：

$ pg_dump student -U student -f student_20160718.tar.gz

回復 student 資料庫：

$psql -f student_20160718.tar.gz student student

查看 student 資料庫：

$psql -U student
student=# \c student
student=# \d

PgHero 的安裝與使用

PgHero介紹：

用來分析與監控 PostgreSQL 內不良的 SQL 語法
- 軟體下載與安裝網址：https://github.com/ankane/pghero

快速安裝流程

下載安裝的認證 key：

# rpm --import https://rpm.packager.io/key

編寫 repo 檔案：

# vim /etc/yum.repos.d/pghero.repo
[pghero]
name=Repository for pghero/pghero application.
baseurl=https://rpm.packager.io/gh/pghero/pghero/centos7/master
enabled=1

安裝 PgHero ：
```
# yum install pghero
```

連結需要分析的資料庫：

# pghero config:set DATABASE_URL=postgres://user:password@hostname:5432/dbname
(例如：)
pghero config:set DATABASE_URL=postgres://postgres:123456@127.0.0.1:5432/student

2016年7月13日星期三

Pgpool-II 相關指令的使用

指令介紹：

利用 psql 指令的 show ，帶出 pgpool-II 的指令：
- pool_status
- pool_nodes
- pool_processes
- pool_pools
- pool_version

操作練習

登入 psql 操作介面：

#psql -p 5432 -h 192.168.1.63 -Upgpool postgres
(輸入密碼之後，出現 psql 提示字元)
#postgres=

顯示 pgpool-II 組態值：

postgres=# show pool_status;
 (會顯示所有 pgpool.conf 設定內容)

顯示 pool 內各節點所扮演的角色：

postgres=# show pool_nodes;
 node_id | hostname | port | status | lb_weight |  role  | select_cnt
---------+----------+------+--------+-----------+--------+------------
 0       | h4       | 5432 | 2      | 0.500000  | master | 386
 1       | h5       | 5432 | 2      | 0.500000  | slave  | 386
(2 rows)

※status 值說明：1:節點已啟動，但沒有連接。2:節點已啟動，有連接。3:節點 down。

顯示 pool 內，各 process 使用情形：

postgres=# show pool_processes;
 pool_pid |     start_time      | database | username |     create_time     | pool_counter
----------+---------------------+----------+----------+---------------------+--------------
   : (省略)
 17377    | 2016-07-13 15:19:05 | postgres | pgpool   | 2016-07-13 15:36:32 | 1
   : (省略)

顯示各 pool 使用情形：
```
postgres=# show pool_pools;
```

顯示 pgpool-II 版次：

postgres=# show pool_version;
   pool_version
-------------------
 3.5.3 (ekieboshi)
(1 row)

2016年7月11日星期一

PostgreSQL 修改表格語法(二)

目標說明

表格建立的SQL語法，請參考這一篇。
表格修改的SQL語法，請參考這一篇。
本篇著重於主鍵、外來健的新增、修改與刪除。

主健、外來健新增的方式如下：

CREATE TABLE table_name (
   column_name data_type column_constraint table_constraint,
   [ ... ]
)

其中 column_constraint 是:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

並且 table_constraint 是:

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
  UNIQUE ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  EXCLUDE [ USING index_method ] ( exclude_element WITH 運算子 [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ...
] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

主健、外來健修改的方式如下：

ALTER TABLE action [, ... ];

其中 action 是:
    ADD [ COLUMN ] column_name data_type CHECK column_constraint
    ADD table_constraint [ NOT VALID ]
    ADD table_constraint_using_index
    DROP CONSTRAINT column_constraint

並且 table_constraint_using_index 是:

    [ CONSTRAINT constraint_name ]
    { UNIQUE | PRIMARY KEY } USING INDEX index_name
    [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

操作練習

登入 psql 操作介面：
```
#su - postgres
$psql
#postgres=
```

連結 student 資料庫：

postgres=# \c student
已經連線到資料庫 "student"，使用者 "postgres"。

修改 score 表內的 student_id 為 Primary key：

student=# ALTER TABLE score ADD PRIMARY KEY (student_id);
注意:  ALTER TABLE / ADD PRIMARY KEY 將會建立隱含索引 "score_pkey" (適用於資料表 "score")
ALTER TABLE
student=#

將 score 表內的 student_id 關聯至 baseData表格內的 id：

student=# ALTER TABLE score ADD FOREIGN KEY (student_id) REFERENCES basedata(id);
ALTER TABLE

查詢 score 表格目前情狀：

student=# \d score
            資料表 "public.score"
    欄位    |         型別          | 修飾詞
------------+-----------------------+---------
 student_id | character varying(11) | 非 Null
 subject_id | character varying(11) |
 score      | integer               |
索引:
    "score_pkey" PRIMARY KEY, btree (student_id)
檢查條件約束
    "score_check" CHECK (score >= 0 AND score <= 100)
外鍵條件約束:
    "score_student_id_fkey" FOREIGN KEY (student_id) REFERENCES basedata(id)

student=#

刪除 score 表格主鍵：

student=# ALTER TABLE score DROP CONSTRAINT "score_pkey";
ALTER TABLE

新增成績資料到 score 表格：

student=# INSERT INTO score(student_id,subject_id,score)
student-# VALUES('S0001','AT0001',89);
INSERT 0 1
student=# INSERT INTO score(student_id,subject_id,score)
student-# VALUES('S0001','AT0002',90);
INSERT 0 1

查詢 score 資料表內容:

student=# SELECT * FROM score;
 student_id | subject_id | score
------------+------------+-------
 S0001      | AT0001     |    89
 S0001      | AT0002     |    90
(2 筆資料列)

新增不存在的學生成績資料到 score 表格：

student=# INSERT INTO score(student_id,subject_id,score)
VALUES('S0005','AT0002',90);
錯誤:  資料表 "score" 的插入或更新違反外鍵限制 "score_student_id_fkey"
詳細資料: 索引鍵 (student_id)=(S0005) 沒有出現在資料表 "basedata"。
student=#

PostgreSQL 修改表格語法(一)

基本語法

查詢資料表中的資料 SQL 語法：

ALTER TABLE [ IF EXISTS ] [ ONLY ] 名稱 [ * ]
    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] 名稱 [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] 名稱 [ * ]
    RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] 名稱
    RENAME TO new_name
ALTER TABLE [ IF EXISTS ] 名稱
    SET SCHEMA new_schema

其中 action 是:

    ADD [ COLUMN ] column_name data_type [ COLLATE 定序 ] [ column_constraint [ ... ] ]
    DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
    ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE 定序 ] [ USING expression ]
    ALTER [ COLUMN ] column_name SET DEFAULT expression
    ALTER [ COLUMN ] column_name DROP DEFAULT
    ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
    ALTER [ COLUMN ] column_name SET STATISTICS 整數
    ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
    ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
    ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    ADD table_constraint [ NOT VALID ]
    ADD table_constraint_using_index
    VALIDATE CONSTRAINT constraint_name
    DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
    DISABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE REPLICA TRIGGER trigger_name
    ENABLE ALWAYS TRIGGER trigger_name
    DISABLE RULE rewrite_rule_name
    ENABLE RULE rewrite_rule_name
    ENABLE REPLICA RULE rewrite_rule_name
    ENABLE ALWAYS RULE rewrite_rule_name
    CLUSTER ON index_name
    SET WITHOUT CLUSTER
    SET WITH OIDS
    SET WITHOUT OIDS
    SET ( storage_parameter = value [, ... ] )
    RESET ( storage_parameter [, ... ] )
    INHERIT parent_table
    NO INHERIT parent_table
    OF type_name
    NOT OF
    OWNER TO new_owner
    SET TABLESPACE new_tablespace

並且 table_constraint_using_index 是:

    [ CONSTRAINT constraint_name ]
    { UNIQUE | PRIMARY KEY } USING INDEX index_name
    [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

操作練習

登入 psql 操作介面：
```
#su - postgres
$psql
#postgres=
```

連結 student 資料庫：

postgres=# \c student
已經連線到資料庫 "student"，使用者 "postgres"。

查詢資料庫內的資料表：

student=# \d
             relation 清單
 架構模式 |   名稱   | 型別  |  擁有者
----------+----------+-------+----------
 public   | baselist | table | postgres
(1 筆資料列)

查詢資料表 baselist 的欄位：

student=# \d baselist
          資料表 "public.baselist"
  欄位   |          型別          | 修飾詞
---------+------------------------+---------
 id      | character varying(12)  | 非 Null
 name    | character varying(50)  | 非 Null
 phones  | character varying(11)  | 非 Null
 address | character varying(200) | 非 Null
索引:
    "baselist_pkey" PRIMARY KEY, btree (id)

新增欄位 age ，並且加入限制 0~200 條件：

student=# ALTER TABLE baselist ADD COLUMN age int CHECK (age >=0 AND age <= 200);
ALTER TABLE

查詢資料表 baselist 的欄位：

student=# \d baselist
          資料表 "public.baselist"
  欄位   |          型別          | 修飾詞
---------+------------------------+---------
 id      | character varying(12)  | 非 Null
 name    | character varying(50)  | 非 Null
 phones  | character varying(11)  | 非 Null
 address | character varying(200) | 非 Null
 age     | integer                |
索引:
    "baselist_pkey" PRIMARY KEY, btree (id)
檢查條件約束
    "baselist_age_check" CHECK (age >= 0 AND age <= 200)

故意加入一個超出範圍值的資料：

student=# UPDATE baselist SET age=201 WHERE id='S0001';
錯誤:  關係 "baselist" 的新資料列違反檢查限制 "baselist_age_check"
詳細資料: Failing row contains (S0001, Peter, 0912345678, Hd. Rd., No. 4321, 201).

加入一個合理範圍值的資料：

student=# UPDATE baselist SET age=20 WHERE id='S0001';
UPDATE 1
student=# SELECT * FROM baselist WHERE id='S0001';
  id   | name  |   phones   |      address      | age
-------+-------+------------+-------------------+-----
 S0001 | Peter | 0912345678 | Hd. Rd., No. 4321 |  20
(1 筆資料列)

修改表格欄位名稱 age ，成為 era ：

student=# ALTER TABLE baselist RENAME COLUMN age TO era;
ALTER TABLE
student=# \d baselist;
          資料表 "public.baselist"
  欄位   |          型別          | 修飾詞
---------+------------------------+---------
 id      | character varying(12)  | 非 Null
 name    | character varying(50)  | 非 Null
 phones  | character varying(11)  | 非 Null
 address | character varying(200) | 非 Null
 era     | integer                |
索引:
    "baselist_pkey" PRIMARY KEY, btree (id)
檢查條件約束
    "baselist_age_check" CHECK (era >= 0 AND era <= 200)

刪除檢查限制：

student=# ALTER TABLE baselist DROP CONSTRAINT "baselist_age_check";
ALTER TABLE

刪除 baselist 表格內的 era 欄位：

student=# ALTER TABLE baselist DROP COLUMN era;
ALTER TABLE

查詢 baselist 表格內容：

student=# SELECT * FROM baselist;
  id   |  name   |   phones   |      address
-------+---------+------------+-------------------
 S0002 | Jupiter | 0987654321 | Rd. Le, No. 123
 S0001 | Peter   | 0912345678 | Hd. Rd., No. 4321
(2 筆資料列)

修改表格 baselist 名稱，成為 baseData 表格名稱：

student=# ALTER TABLE baselist RENAME TO baseData;
ALTER TABLE
student=# \d baseData
          資料表 "public.basedata"
  欄位   |          型別          | 修飾詞
---------+------------------------+---------
 id      | character varying(12)  | 非 Null
 name    | character varying(50)  | 非 Null
 phones  | character varying(11)  | 非 Null
 address | character varying(200) | 非 Null
索引:
    "baselist_pkey" PRIMARY KEY, btree (id)

PostgreSQL 查詢資料語法

基本語法

查詢資料表中的資料 SQL 語法：

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ [ AS ] output_name ] [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING 運算子 ] [ NULLS { FIRST | LAST }
 ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

其中 from_item 可以是:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...]
 | column_definition [, ...] ) ]
    function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
    from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join
_column [, ...] ) ]

並且 with_query 是:

    with_query_name [ ( column_name [, ...] ) ] AS ( select | values | insert |
update | delete )

TABLE [ ONLY ] table_name [ * ]

操作練習

登入 psql 操作介面：
```
#su - postgres
$psql
#postgres=
```

連結 student 資料庫：

postgres=# \c student
已經連線到資料庫 "student"，使用者 "postgres"。

查詢資料庫內的資料表：

student=# \d
             relation 清單
 架構模式 |   名稱   | 型別  |  擁有者
----------+----------+-------+----------
 public   | baselist | table | postgres
(1 筆資料列)

查詢資料表 baselist 的欄位：

student=# \d baselist
          資料表 "public.baselist"
  欄位   |          型別          | 修飾詞
---------+------------------------+---------
 id      | character varying(12)  | 非 Null
 name    | character varying(50)  | 非 Null
 phones  | character varying(11)  | 非 Null
 address | character varying(200) | 非 Null
索引:
    "baselist_pkey" PRIMARY KEY, btree (id)

查詢資料表 baselist 的全部資料：

student=# SELECT * FROM baselist;
  id   |  name   |   phones   |      address
-------+---------+------------+-------------------
 S0001 | Peter   | 0912345678 | Hd. Rd., No. 4321
 S0002 | Jupiter | 0987654321 | Rd. Le, No. 123
(2 筆資料列)

以 phones 欄位逆向排序，查詢資料表 baselist 的全部資料：

student=# SELECT * FROM baselist ORDER BY phones DESC;
  id   |  name   |   phones   |      address
-------+---------+------------+-------------------
 S0002 | Jupiter | 0987654321 | Rd. Le, No. 123
 S0001 | Peter   | 0912345678 | Hd. Rd., No. 4321
(2 筆資料列)

以 name 欄位為查詢條件，查詢資料表 baselist 內的資料：

student=# SELECT * FROM baselist WHERE name='Peter';
  id   | name  |   phones   |      address
-------+-------+------------+-------------------
 S0001 | Peter | 0912345678 | Hd. Rd., No. 4321
(1 筆資料列)

使用萬用字元 '%' 的查詢方式：

student=# SELECT * FROM baselist WHERE name LIKE 'Pe%';
  id   | name  |   phones   |      address
-------+-------+------------+-------------------
 S0001 | Peter | 0912345678 | Hd. Rd., No. 4321
(1 筆資料列)

只列出部份欄位的查詢方式：

student=# SELECT name,phones FROM baselist WHERE name LIKE 'Pe%';
 name  |   phones
-------+------------
 Peter | 0912345678
(1 筆資料列)

PostgreSQL 新增、修改與刪除資料

基本語法

新增資料表中的資料 SQL 語法：

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ ( column_name [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

刪除資料表中的資料 SQL 語法：

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    [ USING using_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

更新資料表中的資料 SQL 語法：

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

操作練習

登入 psql 操作介面：
```
#su - postgres
$psql
#postgres=
```

連結 student 資料庫：

postgres=# \c student
已經連線到資料庫 "student"，使用者 "postgres"。

查詢資料庫內的資料表：

student=# \d
             relation 清單
 架構模式 |   名稱   | 型別  |  擁有者
----------+----------+-------+----------
 public   | baselist | table | postgres
(1 筆資料列)

查詢資料表 baselist 的欄位：

student=# \d baselist
          資料表 "public.baselist"
  欄位   |          型別          | 修飾詞
---------+------------------------+---------
 id      | character varying(12)  | 非 Null
 name    | character varying(50)  | 非 Null
 phones  | character varying(11)  | 非 Null
 address | character varying(200) | 非 Null
索引:
    "baselist_pkey" PRIMARY KEY, btree (id)

插入一筆資料：

student=# INSERT INTO baselist(id,name,phones,address)
student-# VALUES('s0002','Jupiter','0987654321','test Rd., No. 123');

INSERT 0 1

查詢 baselist 資料表內容：

student=# SELECT * FROM baselist;
  id   |  name   |   phones   |      address
-------+---------+------------+-------------------
 S0001 | Peter   | 0912345678 | test. No. 32
 s0002 | Jupiter | 0987654321 | test Rd., No. 123
(2 筆資料列)

刪除一筆 id 為 s0002 的資料：

student=# DELETE FROM baselist WHERE id = 's0002';
DELETE 1

更新除一筆 id 為 S0001 的資料內容：

student=# UPDATE baselist SET address='Hd. Rd., No. 4321' WHERE id = 'S0001';
UPDATE 1

查詢 baselist 資料表內容：

student=# SELECT * FROM baselist;
  id   | name  |   phones   |      address
-------+-------+------------+-------------------
 S0001 | Peter | 0912345678 | Hd. Rd., No. 4321
(1 筆資料列)

參考文獻：

https://www.howtoforge.com/tutorial/virtual-hosting-with-vsftpd-and-mysql-on-ubuntu-15.10/

2016年7月7日星期四

PostgreSQL 監控、分析與統計工具

原始工具：

https://wiki.postgresql.org/wiki/9.1%E7%AC%AC%E4%BA%8C%E5%8D%81%E4%B8%83%E7%AB%A0
http://twpug.net/docs/postgresql-doc-8.0-zh_TW/monitoring-stats.html
pghero -- https://github.com/ankane/pghero
pgFouine -- http://pgfouine.projects.pgfoundry.org/

商用工具：

https://docs.citusdata.com/en/v5.1/installation/production_rhel.html
https://www.compose.com/articles/loading-google-analytics-data-to-postgresql-using-python/
http://chuansong.me/n/615176
http://www.it610.com/article/2662277.htm
http://itindex.net/detail/47304-postgres-%E6%95%B0%E6%8D%AE%E5%BA%93-%E5%88%86%E6%9E%90

問題與討論

索引問題：http://stackoverflow.com/questions/3318727/postgresql-index-usage-analysis

2016年7月5日星期二

PostgreSQL 有用的文章

http://conbar.pixnet.net/blog/post/9618904-(%E6%95%B4%E7%90%86%E4%B8%AD)enterprisedb-vs.-oracle%E8%B3%87%E6%96%99%E5%BA%AB%E7%9B%B8%E7%95%B0%E8%99%95%E6%AF%94%E8%BC%83--
http://postgresql-chinese.blogspot.tw/2007/03/postgresql-tablespace.html
http://postgresql.wisdomfish.org/zi-liao-ku-de-he-xin-li-lun-yu-shi-wu/ch-zi-liao-ku-lun-shu

2016年7月4日星期一

Psql 基本操作方式(一)

基本操作方式(一)-- 外部操作

切換成 postgres 使用者：
```
#su - postgres
```
資料庫查詢：
```
$psql -l
```

直接連結指定的資料庫：

(指定的格式： psql -h [主機IP或名稱] -p [port 號] [資料庫名稱] [使用者名稱])
$psql -h 192.168.5.104 -p 5432 student postgres
用戶 postgres 的密碼:
psql (9.2.15)
輸入 "help" 顯示說明。

student=#

另一種直接連結指定資料庫的方式：

$export PGDATABASE=student
$export PGHOST=192.168.5.104
$export PGPORT=5432
$export PGUSER=postgres
$psql
密碼:
psql (9.2.15)
輸入 "help" 顯示說明。

student=#

基本操作方式(二)-- Psql 介面操作

進入 psql 操作介面方式：

#su - postgres
$psql
postgres=#

(上列的 postgres 是指 postgres 資料庫！)

利用 help 可顯示說明：

postgres=# help
您正在使用 PostgreSQL 指令列介面 psql。
輸入: \copyright 顯示發行條款
       \h 顯示 SQL 指令的說明
       \? 顯示 psql 指令的說明
       \g 或者以分號 (;) 結尾以執行查詢
       \q 結束
postgres=#

切換資料庫：

postgres=# \c student
已經連線到資料庫 "student"，使用者 "postgres"。

student=#

資料表查詢：

student=# \d baselist
          資料表 "public.baselist"
  欄位   |          型別          | 修飾詞
---------+------------------------+---------
 id      | character varying(12)  | 非 Null
 name    | character varying(50)  | 非 Null
 phones  | character varying(11)  | 非 Null
 address | character varying(200) | 非 Null
索引:
    "baselist_pkey" PRIMARY KEY, btree (id)

索引 Key 查詢：

student=# \d baselist_pkey
     索引 "public.baselist_pkey"
 欄位 |         型別          | 定義
------+-----------------------+------
 id   | character varying(12) | id
主鍵, btree, 給資料表 "public.baselist"

可配合萬用字元查詢：

student=# \d b*
          資料表 "public.baselist"
  欄位   |          型別          | 修飾詞
---------+------------------------+---------
 id      | character varying(12)  | 非 Null
 name    | character varying(50)  | 非 Null
 phones  | character varying(11)  | 非 Null
 address | character varying(200) | 非 Null
索引:
    "baselist_pkey" PRIMARY KEY, btree (id)

     索引 "public.baselist_pkey"
 欄位 |         型別          | 定義
------+-----------------------+------
 id   | character varying(12) | id
主鍵, btree, 給資料表 "public.baselist"

student=#

開啟計時功能：

student=# \timing on
啟用計時功能.
student=# select * from baselist;
 id | name | phones | address
----+------+--------+---------
(0 筆資料列)

時間: 1.482 ms

列出所有可用的 schema：

student=# \dn
    schema 清單
  名稱  |  擁有者
--------+----------
 public | postgres
(1 筆資料列)

顯示可用的 tablespace ：

student=# \db
           表空間列表
    名稱    |  擁有者  | 所在地
------------+----------+--------
 pg_default | postgres |
 pg_global  | postgres |
(2 筆資料列)

顯示角色、使用者：

student=# \du
                        角色清單
 角色名稱 |               屬性                | 成員屬於
----------+-----------------------------------+----------
 postgres | 超級用戶, 建立角色, 建立 DB, 複製 | {}

student=# \dg
                        角色清單
 角色名稱 |               屬性                | 成員屬於
----------+-----------------------------------+----------
 postgres | 超級用戶, 建立角色, 建立 DB, 複製 | {}

表格存取權限情況：

student=# \dp baselist
                        存取權限
 架構模式 |   名稱   | 型別  | 存取權限 | 資料行存取權限
----------+----------+-------+----------+----------------
 public   | baselist | table |          |
(1 筆資料列)

離開 psql 操作介面：
```
postgres=# \q
```

PostgreSQL 新增、刪除資料表

基本語法

建立資料表基本 SQL 語法：

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name (
 [
  { column_name data_type [ COLLATE 定序 ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] 
   }
    [, ... ]
 ] )

[ INHERITS ( parent_table [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    OF type_name [ (
  { column_name WITH OPTIONS [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ]

[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

其中 column_constraint 是:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

並且 table_constraint 是:

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
  UNIQUE ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  EXCLUDE [ USING index_method ] ( exclude_element WITH 運算子 [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ...
] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

並且 like_option 是:

{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL }

UNIQUE, PRIMARY KEY, and EXCLUDE 條件約束的 index_parameters 是:

[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

EXCLUDE 約束條件的 exclude_element 是:

{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

刪除資料表基本 SQL 語法：

DROP TABLE [ IF EXISTS ] 名稱 [, ...] [ CASCADE | RESTRICT ]

操作練習

登入 psql 操作介面：
```
#su - postgres
$psql
#postgres=
```

新增 test 資料表：

postgres=# CREATE TABLE test(
postgres(# id varchar(10),
postgres(# name varchar(100),
postgres(# score int
postgres(# );

(請注意 "=" 與 '(' 的不同)

新增完之後，可查詢資料表：
```
postgres=# \d

postgres=# \d test
```
刪除 test 資料表：
```
postgres=# \d test
```

新增 score 資料表：

student=# CREATE TABLE score(
student(#student_id varchar(11),
student(#subject_id varchar(11),
student(#score int CONSTRAINT score_check CHECK (score >=0 AND score <=100));

CREATE TABLE
student=#

參考文獻：

http://netkiller.sourceforge.net/postgresql/key.html

2016年7月3日星期日

PostgreSQL 建立、刪除資料庫

基本語法

建立資料庫基本 SQL 語法：

CREATE DATABASE name
   [  [WITH] [OWNER [=] user_name]
      [TEMPLATE [=] template]
      [ENCODING [=] encoding]
      [LC_COLATE [=] lc_collate]
      [TABLESPACE [=] tablespace]
      [CONNECTION LIMIT [=] connlimit]
   ]

刪除資料庫基本 SQL 語法：
```
DROP DATABASE [ IF EXISTS ] 名稱
```

操作練習

登入 psql 操作介面：
```
#su - postgres
$psql
#postgres=
```

新增 student 資料庫：

postgres=# CREATE DATABASE student
postgres-# TEMPLATE = postgres ;

(請注意 "=" 與 '-' 的不同)

新增完之後，可查詢資料庫：
```
postgres=# \l
```
刪除 student 資料庫：
```
DROP DATABASE student;
```

參考文獻：

https://www.howtoforge.com/tutorial/virtual-hosting-with-vsftpd-and-mysql-on-ubuntu-15.10/

PostgreSQL 9.2 相關設定檔

基本設定檔

/var/lib/pgsql/data/pg_hba.conf --> 客戶端(帳密)驗證檔
查看內容：

#vim /var/lib/pgsql/data/pg_hba.conf
(設定格式：)
[連線類型] [資料庫名稱] [使用者名稱] [連線位置] [驗證方式] [其他選項]
(範例：)
host     all      all        127.0.0.1/32       md5
host     all      all        ::1/128            md5

/var/lib/pgsql/data/postgresql.conf --> PostgreSQL Server 運作組態檔
查看內容：

#vim /var/lib/pgsql/data/postgresql.conf
(參數說明：)
listen_addresses = '*'   ##監聴本機上的 IP 位置
port = 5432              ##監聴本機上的 port 號
max_connections = 2000   ##最大連線數設定值
shared_buffers = 256MB
work_mem = 32MB
maintenance_work_mem = 128MB
max_stack_depth = 6MB

※基本安裝：

請參考這一篇文件，進行基本安裝。
請參考這一篇文件，進行 phpPgAdmin 安裝。
請參考這一篇文件，安裝 pgAdmin III。

若有啟動錯誤，必須修正下列項目：

#vim /etc/sysctl.conf
kernel.sem = 250 32000 100 512

(存檔後，執行下列項目：)
#sysctl -p

參考文獻：

https://www.howtoforge.com/tutorial/virtual-hosting-with-vsftpd-and-mysql-on-ubuntu-15.10/

訂閱：文章 (Atom)

2016年7月22日 星期五

2016年7月18日 星期一

2016年7月13日 星期三

2016年7月11日 星期一

2016年7月7日 星期四

2016年7月5日 星期二

2016年7月4日 星期一

2016年7月3日 星期日

2016年7月22日星期五

2016年7月18日星期一

2016年7月13日星期三

2016年7月11日星期一

2016年7月7日星期四

2016年7月5日星期二

2016年7月4日星期一

2016年7月3日星期日