心動不如行動，基於Docker安裝關係型數據庫PostgrelSQL替代Mysql
原文轉載自「劉悅的技術部落格」https://v3u.cn/a_id_171
最近「全棧數據庫」的概念甚囂塵上，主角就是PostgrelSQL，它最近這幾年的技術發展不可謂不猛，覆蓋OLTP、OLAP、NoSQL、搜尋、影象等應用場景，實實在在的全棧性發展。幫助公司解決了數據孤島、數據平臺多、同步一致性、延遲，軟硬體成本增加等業務痛點，在網際網路、金融、物聯網、傳統企業等領域得到了廣泛的應用。PostgreSQL的應用場景豐富，不亞於商用數據庫Oracle，常被業界稱爲「開源界的Oracle」。
至於Mysql大家都很熟悉，很多公司因爲人才儲備和數據量大的原因，一般是Hadoop+Mysql的模式，Hadoop計算大量原始數據，然後按維度彙總後的展示數據儲存在Mysql上，但是Mysql也有很多的「坑」：比如著名的Emoji表情坑，由此引申出來的utf8mb4的坑（隱式型別轉換陷阱），效能低到髮指的悲觀鎖機制 機製，不支援多表單序列中取 id，不支援over子句，幾乎沒有效能可言的子查詢…有點罄竹難書的意思，更多的「罪行」詳見：見鬼的選擇：Mysql。而這些問題，在PostgrelSQL中得到了改善，本次我們在Win10平臺利用Docker安裝PostgrelSQL，並且初步感受一下它的魅力。
第一步當然是安裝Docker，不熟悉的同學請參照：win10系統下把玩折騰DockerToolBox以及更換國內映象源(各種神坑)。
隨後拉取映象，這裏我們選擇相對穩定的PostgrelSQL11.1。
docker pull postgres:11.1
拉取成功後，輸入命令檢視映象
docker images
可以看到，它的映象非常小，大概300m左右，比Mysql小很多。
然後我們就可以將容器啓動了，輸入命令
docker run -d --name dev-postgres -e POSTGRES_PASSWORD=root -p 6432:5432 postgres:11.1
這裏POSTGRES_PASSWORD是PostgrelSQL的使用者密碼，自己制定一個就可以了，預設埠號是5432，由於筆者的宿主機上已經安裝好一個PostgrelSQL伺服器端，所以這裏通過埠對映改成了6432。
輸入命令
docker ps
來檢視容器執行狀態
沒有問題，現在我們進入命令列操作一下。
docker exec -it dev-postgres bash
psql -h localhost -U postgres
這樣就可以進入容器內部的命令列，在命令列輸入PostgrelSQL的命令\l 就可以檢視數據庫列表。
建立數據庫
CREATE DATABASE mytest;
使用數據庫
\c mytest
建立一張表
CREATE TABLE "public"."article" (
"id" int4 NOT NULL,
"content" text,
PRIMARY KEY ("id")
)
WITH (OIDS=FALSE);
列出所有表
\d
如果不習慣使用命令列，也可以用視覺化工具來進行鏈接，比如Navicat
注意預設使用者是postgres，值得一提的是，使用navicat無法像Mysql一樣手動設定屬性自增長(auto-increment)，PostgrelSQL使用的是序列的形式來實現自增長：
CREATE SEQUENCE serial START 1;
這裏建立好的序列是從1開始計數。
隨後，將需要設定的欄位的預設值設爲序列增長即可
ALTER TABLE "public"."article" ALTER COLUMN "id" SET DEFAULT nextval('serial');
可以使用utf-8編碼輕鬆儲存Emoji
over子句的應用，假設我們有一個員工薪資的表（部門、員工id，工資）：
postgres=# d empsal
Table "public.empsal"
Column | Type | Modifiers
---------+-------------------+-----------
depname | character varying |
empno | integer |
salary | integer |
有一些數據：
postgres=# select * from empsal ;
depname | empno | salary
-----------+-------+--------
develop | 11 | 5200
develop | 7 | 4200
develop | 9 | 4500
develop | 8 | 6000
develop | 10 | 5200
personnel | 5 | 3500
personnel | 2 | 3900
sales | 3 | 4800
sales | 1 | 5000
sales | 4 | 4800
(10 rows)
現在我想將每一個員工的工資與他所在部門的平均工資做個比較，怎麼做？其實這也是leetcode原題，用mysql只能用子查詢，而用PostgrelSQL該查詢可以很容易的實現
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsal;
查詢結果：
depname | empno | salary | avg
-----------+-------+--------+-----------------------
develop | 11 | 5200 | 5020.0000000000000000
develop | 7 | 4200 | 5020.0000000000000000
develop | 9 | 4500 | 5020.0000000000000000
develop | 8 | 6000 | 5020.0000000000000000
develop | 10 | 5200 | 5020.0000000000000000
personnel | 5 | 3500 | 3700.0000000000000000
personnel | 2 | 3900 | 3700.0000000000000000
sales | 3 | 4800 | 4866.6666666666666667
sales | 1 | 5000 | 4866.6666666666666667
sales | 4 | 4800 | 4866.6666666666666667
(10 rows)
可以看到，這個查詢中，聚合函數avg的含義沒有變，仍然是求平均值。但和普通的聚合函數不同的是，它不再對錶中所有的salary求平均值，而是針對同一個部門（PARTITION BY指定的depname）內的salary求平均值，而且得到的結果由同一個部門內的所有行共用，並沒有將這些行合併，這就大大簡化了sql的複雜度，同時也能很方便的解決 「每組取 top k」 的這類問題。
使用容器啓動數據庫會有個問題，就是每次容器停止，數據就會丟失，所有我們可以用docker的掛載命令將數據存在宿主機中，這樣就可以持久化儲存數據：
docker run -d --name dev-postgres -e POSTGRES_PASSWORD=root -e PGDATA=/var/lib/postgresql/data/pgdata
-v /custom/mount:/var/lib/postgresql/data -p 6432:5432 postgres:1.11
如果你不習慣navicat這樣的桌面視覺化工具，也可以使用類似pgadmin4這樣的網頁端工具
$ docker pull dpage/pgadmin4
$ docker run
-p 80:80
-e '[email protected]'
-e 'PGADMIN_DEFAULT_PASSWORD=SuperSecret'
--name dev-pgadmin
-d dpage/pgadmin4
也可以使用Python和PostgrelSQL進行互動，安裝三方庫：
pip3 isntall psycopg2
import psycopg2
import psycopg2.extras
conn = psycopg2.connect(host='localhost', port=6432, user='postgres', password='root', database='mytest')
cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cursor.execute('SELECT * FROM article WHERE id = 1;')
result = cursor.fetchone()
print(result)
就可以查詢出數據了
結語：如果對Mysql足夠熟悉，那麼上手PostgrelSQL並不是一件難事，自從MySQL被Oracle收購的那一刻起，它就已經不是開源軟體的最佳選擇了。所以，不要固執的拒絕時代浪潮，擁抱未來，擁抱PostgrelSQL吧。
原文轉載自「劉悅的技術部落格」 https://v3u.cn/a_id_171