本篇介紹如何在 Ubuntu Linux 18.04 的環境下,安裝與使用 PostgreSQL 資料庫。

安裝 PostgreSQL

在 Ubuntu 官方的套件庫中已經有收錄 PostgreSQL 的相關套件,安裝前先更新套件庫資訊:

# 更新套件庫
sudo apt update

安裝 PostgreSQL 相關套件:

# 安裝 PostgreSQL
sudo apt install postgresql postgresql-contrib

安裝好之後,PostgreSQL 預設會自動啟動,使用 systemctl 指令查看一下 PostgreSQL 的服務是否有正常啟動:

# 查看 PostgreSQL 服務狀態
systemctl status postgresql.service
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Tue 2019-05-07 16:32:14 CST; 6h left
 Main PID: 1835 (code=exited, status=0/SUCCESS)
    Tasks: 0 (limit: 4915)
   CGroup: /system.slice/postgresql.service

 5月 07 16:32:14 gtwang-pc systemd[1]: Starting PostgreSQL RDBMS...
 5月 07 16:32:14 gtwang-pc systemd[1]: Started PostgreSQL RDBMS.

Active 欄位顯示為 active,就表示 PostgreSQL 資料庫服務已經正常啟動了。

PostgreSQL 官方套件庫

如果想要直接使用 PostgreSQL 官方提供的套件庫來安裝,可以改用以下的安裝方式,使用這種方式比較麻煩,但是可以有更多種 PostgreSQL 版本可以選擇,而且還有提供 pgAdmin 4 的套件。

# 安裝必要套件
sudo apt install wget ca-certificates

# 新增 PostgreSQL 套件庫
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# 匯入 PostgreSQL 套件的金鑰
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# 更新套件庫資訊
sudo apt update

# 安裝 PostgreSQL 套件
sudo apt install postgresql-10 pgadmin4

psql 管理介面

psql 是 PostgreSQL 資料庫的命令列操作介面,可以用來執行一般的 SQL 指令或是 psql 自己的 meta 指令,對於資料庫的管理以及大量工作的自動化處理非常有用。

若要進行 PostgreSQL 資料庫的設定與管理工作,必須切換成具有 PostgreSQL 管理權限的使用者(預設是 postgre),然後再執行 psql 進入 PostgreSQL 的指令操作介面:

# 切換成 postgres 使用者
sudo -i -u postgres

# 進入 PostgreSQL 指令介面
psql

PostgreSQL 指令操作介面

另一種方式是使用 sudo 切換成 postgres 只用者之後,直接執行 psql,這樣的話指令會更簡潔:

# 切換成 postgres 使用者,並進入 PostgreSQL 指令介面
sudo -u postgres psql

進入 psql 的環境之後,就可以執行各種指令,進行資料庫的管理工作,關於 psql 的詳細用法,可以參考 psql 的官方說明文件

若要離開 PostgreSQL 指令介面,可以按下 Ctrl + d 這個快速鍵,或是執行 \q 指令:

-- 離開 PostgreSQL 指令介面
\q

新增 PostgreSQL 使用者帳號與資料庫

在命令列的環境下,通常管理者都會使用 psql 來管理 PostgreSQL 資料庫,但是對於新增使用者與資料庫這種常見的工作,PostgreSQL 有另外提供包裝好的 Linux 指令可用。

若要在 PostgreSQL 資料庫中新增使用者,可以用有新增帳號權限的使用者來執行 createuser 這個 Linux 指令:

# 新增 PostgreSQL 使用者
sudo -u postgres createuser gtwang

事實上 createuser 這個工具只是幫助我們產生 SQL 的指令,並送到 PostgreSQL 伺服器上執行而已,若想要觀看實際執行的 SQL 指令,可以加上 -e 參數。

若在 Linux 系統上透過本機的 Unix-domain socket 連到 PostgreSQL 資料庫,預設會使用 peer 的認證方式(直接根據 Linux 統帳號來認證,請參考 PostgreSQL 官方文件),所以不需要設定密碼,如果需要改用 TCP/IP socket 連線的話,在建立使用者時就要加上 -P 參數來設定密碼。

PostgreSQL 的使用者帳號在登入後,預設會使用跟帳號名稱相同的資料庫,我們可以利用 createdb 這個 Linux 指令建立一個跟帳號名稱相同的資料庫:

# 新增 PostgreSQL 資料庫
sudo -u postgres createdb gtwang

建立好帳號與資料庫之後,就可以使用該 Linux 帳號執行 psql 進入 PostgreSQL 資料庫使用了。

一般使用者 PostgreSQL 指令操作介面

接下來所有的 PostgreSQL 資料庫操作,都會在 psql 之中進行。

建立資料表

在使用資料庫時,首先要依照自己的資料型態,並參考 PostgreSQL 的各種資料類型,建立資料表:

-- 建立資料表
CREATE TABLE demo (
  id serial PRIMARY KEY,
  name varchar (30) NOT NULL,
  gender char(1) check (gender in ('M', 'F')),
  birthday date
);

建立好資料表之後,可以使用 psql\d 這個 meta 指令,列出目前已經建立的資料表:

-- 列出資料表
\d
            List of relations
 Schema |    Name     |   Type   | Owner  
--------+-------------+----------+--------
 public | demo        | table    | gtwang
 public | demo_id_seq | sequence | gtwang
(2 rows)

這裡的 demo_id_seq 是用來紀錄 demo 資料表中 id 欄位的序號用的,PostgreSQL 要靠著這個來判斷下一個序號是多少,如果不想列出 sequence,可以改用 \dt 指令。

\d 亦可用來查看資料表的結構(相當於 MySQL 的 DESCRIBE):

-- 檢查資料表結構
\d demo
                                    Table "public.demo"
  Column  |         Type          | Collation | Nullable |             Default              
----------+-----------------------+-----------+----------+----------------------------------
 id       | integer               |           | not null | nextval('demo_id_seq'::regclass)
 name     | character varying(30) |           | not null | 
 gender   | character(1)          |           |          | 
 birthday | date                  |           |          | 
Indexes:
    "demo_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "demo_gender_check" CHECK (gender = ANY (ARRAY['M'::bpchar, 'F'::bpchar]))

新增資料

新增資料可用 INSERT 這個 SQL 指令:

-- 新增資料
INSERT INTO
  demo (name, gender, birthday)
  VALUES ('Steven', 'M', '2012-03-21');

亦可一次新增多筆資料:

-- 新增多筆資料
INSERT INTO
  demo (name, gender, birthday)
  VALUES ('Mary', 'F', '2011-10-12'),
  ('Tom', 'M', '2008-01-02');

查詢資料

查詢資料則用 SELECT 指令:

-- 查詢資料
SELECT * FROM demo;
 id |  name  | gender |  birthday  
----+--------+--------+------------
  1 | Steven | M      | 2012-03-21
  2 | Mary   | F      | 2011-10-12
  3 | Tom    | M      | 2008-01-02
(3 rows)

更改資料

若要刪除指定的資料,可用 DELETE 指令:

-- 刪除資料
DELETE FROM demo WHERE id = 3;

若要更改指定的資料,可用 UPDATE 指令:

-- 更改資料
UPDATE demo SET birthday = '2011-09-12' WHERE id = 2;

若要更改資料表的結構,可用 ALTER 指令:

-- 更改資料表,新增欄位
ALTER TABLE demo ADD weight real;

-- 更改資料表,刪除欄位
ALTER TABLE demo DROP weight;

刪除帳號、資料庫、資料表

若要刪除資料表,可用 DROP 指令:

-- 刪除資料表
DROP TABLE demo;

若要移除 PostgreSQL 的使用者帳號與資料庫,可以使用 dropuserdropdb 這兩個 Linux 指令。

# 刪除 PostgreSQL 資料庫
sudo -u postgres dropdb gtwang

# 刪除 PostgreSQL 使用者
sudo -u postgres dropuser gtwang

參考資料:TecmintDigitalOceanDigitalOceanDigitalOcean