Database 管理
# PostgreSQL Database 概念
在 PostgreSQL 中，資料儲存結構通常可分為：
```text
PostgreSQL Server / Cluster
└── Database
├── Schema
│ ├── Table
│ ├── View
│ └── Function
└── ...
```
## 1. Database 是什麼？
PostgreSQL 的 **Database** 是一個邏輯上的資料庫容器，內含：
- Schema
- Table
- View
- Index
- Function
- Trigger
- Sequence
- 其他資料庫物件
同一個 PostgreSQL Server 或 Cluster 中，可以同時存在多個 Database，例如：
```text
postgres cluster
├── postgres
├── company
├── testdb
└── accounting
```
使用者連線到某一個 Database 後，通常只能直接操作該 Database 內的物件。若要存取另一個 Database，一般需要重新建立連線。
> Database 與 Schema 不同：
> Database 是較外層的容器；Schema 是 Database 內的命名空間。
---
# 一、新增 PostgreSQL Database
## 1. 使用 SQL 指令新增
基本語法：
```sql
CREATE DATABASE database_name;
```
例如：
```sql
CREATE DATABASE company;
```
執行後會建立名為 `company` 的 Database。
通常必須具備以下權限之一：
- PostgreSQL Superuser
- 擁有 `CREATEDB` 權限的使用者
可以用以下指令授予建立 Database 的權限：
```sql
ALTER ROLE username CREATEDB;
```
例如：
```sql
ALTER ROLE alice CREATEDB;
```
---
## 2. 指定擁有者
```sql
CREATE DATABASE company
OWNER = alice;
```
此 Database 的擁有者會設定為 `alice`。
---
## 3. 指定編碼與語系
```sql
CREATE DATABASE company
WITH
OWNER = alice
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8';
```
常見設定如下：
| 選項 | 說明 |
|---|---|
| `OWNER` | Database 擁有者 |
| `ENCODING` | 文字編碼，常見為 `UTF8` |
| `LC_COLLATE` | 字串排序規則 |
| `LC_CTYPE` | 字元分類規則 |
| `TABLESPACE` | 儲存資料的 Tablespace |
| `TEMPLATE` | 建立時所複製的範本 Database |
> `ENCODING`、`LC_COLLATE`、`LC_CTYPE` 等設定通常應在建立 Database 時決定，建立後不容易直接修改。
---
## 4. 使用特定 Template 建立
PostgreSQL 建立 Database 時，通常會複製 `template1`：
```sql
CREATE DATABASE testdb
TEMPLATE template1;
```
也可以使用其他 Template：
```sql
CREATE DATABASE testdb
TEMPLATE my_template;
```
Database 也可以被設定成 Template：
```sql
ALTER DATABASE my_template IS_TEMPLATE true;
```
設定後，具備適當權限的使用者可以利用它建立新的 Database。
---
## 5. 使用 `createdb` 指令
在作業系統命令列中，也可以使用 PostgreSQL 提供的工具：
```bash
createdb company
```
指定擁有者：
```bash
createdb -O alice company
```
指定主機、埠號與登入帳號：
```bash
createdb \
-h localhost \
-p 5432 \
-U postgres \
-O alice \
company
```
---
## 6. 注意事項
`CREATE DATABASE` 通常不能在交易區塊中執行，例如：
```sql
BEGIN;
CREATE DATABASE company;
COMMIT;
```
這通常會產生錯誤。應直接單獨執行：
```sql
CREATE DATABASE company;
```
---
# 二、修改 PostgreSQL Database
修改 Database 可使用：
```sql
ALTER DATABASE database_name ...
```
## 1. 修改 Database 擁有者
```sql
ALTER DATABASE company
OWNER TO alice;
```
只有 Superuser 或原本的 Database 擁有者等具備適當權限者，才能進行此操作。
---
## 2. 修改 Database 名稱
```sql
ALTER DATABASE company
RENAME TO company_prod;
```
如果目前正連線到 `company`，通常必須先連線到其他 Database，例如 `postgres`，再執行更名：
```bash
psql -U postgres -d postgres
```
接著執行：
```sql
ALTER DATABASE company
RENAME TO company_prod;
```
---
## 3. 修改 Tablespace
```sql
ALTER DATABASE company
SET TABLESPACE fast_storage;
```
這會將 Database 的物件移到指定的 Tablespace。實際執行時可能需要符合 Tablespace 權限，且 Database 不應有其他使用者正在使用。
---
## 4. 設定連線數上限
```sql
ALTER DATABASE company
CONNECTION LIMIT 50;
```
表示最多允許 50 個連線。
若要取消限制，可設定為：
```sql
ALTER DATABASE company
CONNECTION LIMIT -1;
```
---
## 5. 設定是否允許連線
禁止新的使用者連線：
```sql
ALTER DATABASE company
ALLOW_CONNECTIONS false;
```
恢復允許連線：
```sql
ALTER DATABASE company
ALLOW_CONNECTIONS true;
```
注意：禁止連線通常不一定會立即中斷目前已建立的連線。
---
## 6. 設定 Database 層級的參數
可以針對某個 Database 設定 PostgreSQL 參數，例如設定時區：
```sql
ALTER DATABASE company
SET timezone TO 'Asia/Taipei';
```
設定預設 schema 搜尋路徑：
```sql
ALTER DATABASE company
SET search_path TO public;
```
重設某個設定：
```sql
ALTER DATABASE company
RESET timezone;
```
這些設定通常只套用到連線至該 Database 的 Session。
---
## 7. 設定或取消 Template 屬性
設定為 Template：
```sql
ALTER DATABASE company_template
IS_TEMPLATE true;
```
取消 Template：
```sql
ALTER DATABASE company_template
IS_TEMPLATE false;
```
---
## 8. 使用 `psql` 查詢 Database 清單
在 `psql` 中執行：
```sql
\l
```
或：
```sql
\list
```
查看某個 Database 的詳細資訊：
```sql
\l+ company
```
也可以使用 SQL：
```sql
SELECT
datname,
datdba::regrole AS owner,
encoding,
datcollate,
datctype,
datallowconn,
datconnlimit
FROM pg_database;
```
---
# 三、刪除 PostgreSQL Database
## 1. 使用 SQL 指令刪除
基本語法：
```sql
DROP DATABASE database_name;
```
例如：
```sql
DROP DATABASE testdb;
```
刪除 Database 會一併刪除其中所有的：
- Table
- View
- Index
- Function
- Schema
- Data
- 其他物件
此操作通常無法復原，執行前應確認是否已完成備份。
---
## 2. 使用 `IF EXISTS`
若不確定 Database 是否存在，可以使用：
```sql
DROP DATABASE IF EXISTS testdb;
```
若 Database 不存在，PostgreSQL 只會顯示提示，而不會產生錯誤。
---
## 3. 不能刪除目前正在使用的 Database
例如目前連線到 `company`，不能直接執行：
```sql
DROP DATABASE company;
```
通常會出現類似錯誤：
```text
ERROR: cannot drop the currently open database
```
應先連線到其他 Database，例如 `postgres`：
```bash
psql -U postgres -d postgres
```
然後執行：
```sql
DROP DATABASE company;
```
---
## 4. 仍有其他連線時無法刪除
如果其他使用者仍連線到該 Database，可能會出現：
```text
ERROR: database "company" is being accessed by other users
```
可以先查詢目前連線：
```sql
SELECT
pid,
usename,
client_addr,
application_name,
state
FROM pg_stat_activity
WHERE datname = 'company';
```
在確認可以中斷連線後，可以終止其他 Session：
```sql
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'company'
AND pid <> pg_backend_pid();
```
之後再刪除：
```sql
DROP DATABASE company;
```
---
## 5. 使用 `WITH (FORCE)` 強制刪除
在支援此語法的 PostgreSQL 版本中，可以使用：
```sql
DROP DATABASE company WITH (FORCE);
```
這會嘗試終止連線到該 Database 的 Session，再刪除 Database。
也可以搭配 `IF EXISTS`：
```sql
DROP DATABASE IF EXISTS company WITH (FORCE);
```
使用前應非常小心，因為其他使用者的連線可能會被強制中斷。
---
## 6. 使用 `dropdb` 指令
命令列中可使用：
```bash
dropdb testdb
```
若 Database 可能不存在：
```bash
dropdb --if-exists testdb
```
指定連線資訊：
```bash
dropdb \
-h localhost \
-p 5432 \
-U postgres \
testdb
```
某些 PostgreSQL 版本支援強制刪除：
```bash
dropdb --force testdb
```
---
# 四、常用完整範例
## 建立測試 Database
```sql
CREATE DATABASE demo
WITH
OWNER = postgres
ENCODING = 'UTF8';
```
## 修改名稱與連線數
```sql
ALTER DATABASE demo
RENAME TO demo_test;
ALTER DATABASE demo_test
CONNECTION LIMIT 20;
```
## 設定時區
```sql
ALTER DATABASE demo_test
SET timezone TO 'Asia/Taipei';
```
## 刪除 Database
先連線到其他 Database：
```bash
psql -U postgres -d postgres
```
再執行：
```sql
DROP DATABASE demo_test;
```
---
# 五、重要注意事項
1. **刪除 Database 是高風險操作**
Database 內所有資料都會被刪除，應先備份。
2. **不能刪除目前連線中的 Database**
必須先連線到其他 Database。
3. **修改 Database 名稱不會自動修改應用程式設定**
應用程式的連線字串仍需同步更新。
4. **Database 的編碼與排序規則應在建立時規劃**
不應隨意依賴事後修改。
5. **Database、Schema、Table 是不同層級**
若只是要分隔資料表或應用程式命名空間，可能只需要建立 Schema，而不必建立新的 Database。
6. **生產環境刪除前應確認連線、備份與相依應用程式**
特別是使用 `DROP DATABASE ... WITH (FORCE)` 時，可能造成使用者工作中斷。
相關學習地圖、教學課程
Python 資料工程