### [Connecting to Postgresql in a docker container from outside](https://stackoverflow.com/questions/37694987/connecting-to-postgresql-in-a-docker-container-from-outside)
```
psql postgresql://postgres:1234@localhost:5432/postgres
```
```bash
# 連接到資料庫：使用以下指令連接到 PostgreSQL 資料庫
psql -U 用戶名 -d 資料庫名 -h 主機名 -p 連接埠
\l # 列出所有 PostgreSQL 中的資料庫
# 切換資料庫
\c 資料庫名
# 查看資料庫中的 Table
\d # 用於顯示當前資料庫中所有關聯對象（包括表格、視圖、索引、序列等）的命令
\dt # 僅顯示當前資料庫中的所有表格（關聯表）
\du # 列出 PostgreSQL 中的所有使用者
\d+ 顯示資料表的描述 (表結構)
```
### SQL
```sql
-- 取得最靠近現在時間的五筆資料
SELECT *
FROM your_table
ORDER BY ABS(EXTRACT(epoch FROM (your_timestamp_column - NOW())))
LIMIT 5;
-- 使用 EXTRACT(epoch FROM (your_timestamp_column - NOW())) 來計算每筆資料的時間差，以秒為單位。
-- LIMIT 5 限制結果只返回前五筆資料
```
### char(5) to varchar(5)
```sql
-- Step 1: 建立新的 varchar(5)欄位
ALTER TABLE order_list ADD COLUMN currency_new varchar(5);
-- Step 2: 複製數據至新欄位
UPDATE order_list SET currency_new = currency;
-- Step 3: 刪除原始欄位
ALTER TABLE order_list DROP COLUMN currency;
-- Step 4: 重新命名新欄位
ALTER TABLE order_list RENAME COLUMN currency_new TO currency;
```
### 使用者配置
```sql
-- 列出 PostgreSQL 中的所有使用者
SELECT usename FROM pg_user;
-- 執行以下命令來創建一個新的使用者
CREATE USER username WITH PASSWORD 'password';
-- 請將 `username` 更換為你想要的使用者名稱，將 `password` 更換為你想要設置的密碼。
-- 新增一個資料庫
CREATE DATABASE dbname;
-- 授予使用者適當的權限，例如讓使用者擁有對特定資料庫的訪問權限
GRANT ALL PRIVILEGES ON DATABASE database_name TO username;
-- 請將 `database_name` 更換為你想要給予存取權限的資料庫名稱。
-- 完成後，可以使用以下命令檢查新建使用者是否成功添加
\du
-- 移除某個使用者對某個資料庫的權限
REVOKE ALL PRIVILEGES ON DATABASE database_name FROM username;
-- 請將 `database_name` 替換為欲移除權限的資料庫名稱，將 `username` 替換為欲移除權限的使用者名稱。
```
# pg_dump 筆記
## 先搞懂它在幹嘛
`pg_dump` 本質上是把一個資料庫「拍照」下來,拍出來的照片格式可以是純文字 SQL、也可以是壓縮過的自訂格式。選哪種格式直接決定了你之後能不能做「選擇性還原」(只還原某幾張表、平行還原),所以格式選擇比想像中重要。
## 最常用的三種輸出格式
```bash
# 1. 純文字 SQL(-Fp,預設值,可省略)
pg_dump -h localhost -U postgres -d mydb -Fp -f mydb.sql
# 2. 自訂格式(-Fc):壓縮過,且支援 pg_restore 做選擇性/平行還原,最推薦
pg_dump -h localhost -U postgres -d mydb -Fc -f mydb.dump
# 3. 目錄格式(-Fd):每張表拆成獨立檔案,搭配 -j 可以平行匯出,適合超大資料庫
pg_dump -h localhost -U postgres -d mydb -Fd -j 4 -f mydb_dir
```
純文字格式的缺點是:它就是一份完整的 SQL 腳本,只能整份從頭跑到尾,想跳過某張表或平行處理都做不到。自訂格式(`-Fc`)則是把資料跟結構分開存,`pg_restore` 可以只挑你要的部分還原,還能開多個 worker 平行寫入,幾乎沒有理由不選這個,除非你就是想要一份人眼可讀、能直接用 `psql` 執行的 SQL 檔。
## 常搭配的參數
| 參數 | 作用 | 什麼時候用 |
|---|---|---|
| `-s` / `--schema-only` | 只匯出結構,不含資料 | 想先在新環境建好空的 schema 再灌資料時 |
| `-a` / `--data-only` | 只匯出資料,不含結構 | 結構已經建好,只想搬資料 |
| `-t table_name` | 只匯出指定表(可重複多次) | 只想備份某幾張表,不要整庫 |
| `-n schema_name` | 只匯出指定 schema | 多 schema 資料庫只想搬一部分 |
| `--no-owner` | 不記錄物件擁有者(角色) | 目標環境的角色名稱跟來源不同時,避免還原時因角色不存在而報錯 |
| `-j N` | 平行匯出 N 個 worker(只有 `-Fd` 支援) | 資料量大、想縮短匯出時間 |
## 全庫層級:pg_dumpall
`pg_dump` 只匯出「一個資料庫」,不包含角色(users/roles)和 tablespace 這些叢集層級的東西。如果是整個 PostgreSQL 叢集要搬家(例如版本升級),通常會搭配 `pg_dumpall`:
```bash
# 只匯出角色定義和權限,不含每個資料庫的實際資料
pg_dumpall -h localhost -U postgres --globals-only -f globals.sql
```
先用這個補上角色,再用 `pg_dump -Fc` 一個個把資料庫內容匯出,是版本升級時常見的組合。
---
# pg_restore 筆記
## 先搞懂它在幹嘛
`pg_restore` 只吃 `pg_dump` 用 `-Fc` 或 `-Fd` 匯出的檔案(純文字 `-Fp` 的檔案要直接用 `psql` 執行,不能用 `pg_restore`)。它做的事情是把那份「照片」讀出來,重新在目標資料庫建結構、灌資料。
## 基本流程
```bash
# 1. 先在目標端建一個空資料庫
createdb -h localhost -U postgres newdb
# 2. 把備份還原進去
pg_restore -h localhost -U postgres -d newdb mydb.dump
```
## 常搭配的參數
```bash
# 平行還原,4 個 worker 同時跑(只有 -Fc/-Fd 支援,大幅縮短還原時間)
pg_restore -h localhost -U postgres -d newdb -j 4 mydb.dump
# 還原前先清掉目標資料庫裡已存在的物件(避免「物件已存在」的報錯)
pg_restore -h localhost -U postgres -d newdb --clean --if-exists mydb.dump
# 忽略擁有者資訊,用目前連線的角色當擁有者(跨環境還原常用)
pg_restore -h localhost -U postgres -d newdb --no-owner mydb.dump
# 只還原結構,不還原資料
pg_restore -h localhost -U postgres -d newdb --schema-only mydb.dump
# 只還原某一張表
pg_restore -h localhost -U postgres -d newdb -t orders mydb.dump
```
## 選擇性還原的實用技巧
自訂格式(`-Fc`)備份可以先用 `-l` 印出裡面所有物件的清單,挑出你要的部分,再用 `-L` 指定只還原清單裡列的項目:
```bash
# 列出備份檔裡有哪些物件
pg_restore -l mydb.dump > contents.list
# 編輯 contents.list,刪掉不想還原的行,再指定回去
pg_restore -h localhost -U postgres -d newdb -L contents.list mydb.dump
```
這個做法比每次都用 `-t` 一張張指定表名方便很多,尤其是想跳過某些索引或觸發器,又不想動到主要表資料的時候。
## 常見報錯的排解方向
還原時最常撞到的是「物件已存在」跟「角色不存在」兩類錯誤:前者通常是目標資料庫不是全新的,加 `--clean --if-exists` 通常能解;後者是來源跟目標的角色設定不同,加 `--no-owner` 就能繞過(權限之後再手動補)。`pg_restore` 預設遇到錯誤會印出來但繼續往下跑,不會整個中斷,所以還原完務必看一下輸出訊息裡有沒有紅字。
---
## 版本升級
PostgreSQL 大版本升級不能直接掛載 volume 就跑,因為 on-disk 格式不相容。
### pg_dump / pg_restore(邏輯備份,推薦)
最穩、跨版本相容性最好,13→18 沒有官方支援 `pg_upgrade` 直接跳版(通常只保證 n → n+1,你這邊建議走邏輯匯出匯入)。
```bash
# 從舊的 postgres 13 匯出
pg_dumpall -h <new-host> -p <new_port> -U postgres > full_backup.sql
# 或針對單一 DB 用 custom format,體積小、可平行還原
pg_dump -h <new-host> -p <new_port> -U postgres -Fc your_db > your_db.dump
```
還原到新的 18.6:
```bash
pg_restore -h <new-host> -p <new_port> -U postgres -d your_db --no-owner --no-acl -j 4 your_db.dump
```
注意事項:
- `pg_dump` 用**新版**的 client(18.x 的 `pg_dump`/`pg_restore`)去讀舊的 13 資料庫比較保險,反向不保證相容。
- 檢查 extension 版本(尤其如果有用 PostGIS、pgcrypto 等),18.6 上要先 `CREATE EXTENSION` 對應版本。
- 檢查已棄用語法/型別(13→18 之間有些行為變更,例如 `WITH OIDS` 已完全移除、部分 collation 行為改變)。
---
# PostgreSQL:Schema 與 Owner 筆記
## 核心比喻:樓層編號 vs. 房間登記
資料庫裡最容易搞混的兩個字,就是「schema」跟「owner」。想像一棟大樓:
- **schema** 是樓層/房間編號,決定一個物件「放在哪裡」,純粹是命名空間、分類用途。
- **owner** 是房間登記在誰名下,決定「誰對這個物件有預設的完整權限」。
這兩件事在概念上完全獨立——同一個 schema 底下,可以有好幾張表分屬不同 owner;一張表也可以換 owner,而不需要搬到別的 schema。之所以會搞混,通常是因為 Oracle、MySQL 這類系統把兩者綁在一起,而 PostgreSQL 是刻意把它們拆開的少數幾套系統之一。
## 命名層級:schema 是一層,不是巢狀的
`xxx.table_name` 這種寫法裡,`xxx` 是 schema,不是 owner。schema 本身只有一層,不能再往下開子 schema。完整定位一張表理論上是三段式:`database.schema.table`,但實務上你不會真的寫出 database 那段——因為 PostgreSQL 一次連線只能連一個資料庫,SQL 語句裡也不允許跨資料庫查詢,想跨庫得靠 `postgres_fdw`/`dblink` 這類擴充功能,而不是靠命名路徑。
## 權限階層:上層是門檻,不是所有權的延伸
schema 和 owner 各自獨立,但**權限**確實有上下階層關係:
- 要在某個資料庫裡建 schema,得先對這個資料庫有 `CREATE` 權限(db owner 天生有,其他人要 `GRANT`)。
- 要在某個 schema 裡建表,得先對這個 schema 有 `CREATE` 權限(schema owner 天生有)。
也就是說,db owner 對底下所有 schema 都有建立東西的權限,但**不會自動變成**每個 schema 的 owner——這正是「所有權分層」的重點:讓大團隊裡不同小組能各自管好自己的 schema,不需要互相踩線,也不用事事驚動最上層的 DBA。
## Owner 實際能做什麼
物件(表、schema、sequence、function 都算)的 owner,不用額外 `GRANT` 就天生擁有:
- `ALTER`、`DROP` 這個物件
- 把權限 `GRANT` 給別人,或 `REVOKE` 收回
- 對表來說,預設就能 `SELECT`/`INSERT`/`UPDATE`/`DELETE`
沒有 owner 身分、也沒被授權的角色,連看內容都不行,即使能登入資料庫也一樣。
## 查詢語法
```
-- psql 互動指令(最快,背後也是查系統目錄,只是包裝成好讀的表格)
\dt+ table_name -- 列出表,含 Schema、Owner 欄位
\d+ table_name -- 看單一表詳細資訊,底部標 Owner
```
```sql
-- SQL 標準寫法:只查得到 schema,查不到 owner
-- (owner 是 PostgreSQL 專屬擴充,標準 information_schema 沒有這個概念)
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name = 'orders';
-- PostgreSQL 原生目錄,資訊最完整
SELECT
n.nspname AS schema,
c.relname AS table_name,
pg_get_userbyid(c.relowner) AS owner
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = 'orders'
AND c.relkind = 'r'; -- 'r' = 一般表;'i'=索引 'v'=view 'm'=materialized view 'S'=sequence
-- 懶人版:pg_tables 幫你把上面那串 join 包好了
SELECT schemaname, tablename, tableowner
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');
```
`relowner` 在 `pg_class` 裡存的是角色的 oid(數字),要靠 `pg_get_userbyid()` 轉成名字,這是唯一比較容易忘記的細節。
## 跟其他資料庫系統的對照
| 資料庫 | schema 與 owner 的關係 |
|---|---|
| Oracle(及 SQL Server 2005 前) | schema = user,建立時綁死,無法拆分 |
| PostgreSQL、SQL Server 2005+ | schema 與 owner 解耦,可任意指派、轉讓 |
| MySQL | schema 只是 database 的別名,沒有獨立的 owner 概念 |
Oracle 是把「owner」這個角色直接做成了「schema」本身——建一個 user 就自動生出同名 schema,這個 user 建的所有東西永遠只能待在自己的 schema 底下,兩者連拆開想都沒有意義。這也是為什麼從 Oracle/MySQL 背景轉過來的人,第一次看到 PostgreSQL 的 `xxx.table_name` 很容易直覺當成 owner——舊系統的訓練直覺剛好在這裡失效。