1. PostgreSQL介紹
PostgreSQL是一個(gè)功能強(qiáng)大的開源對(duì)象關(guān)系型數(shù)據(jù)庫系統(tǒng)悔橄,他使用和擴(kuò)展了SQL語言绒疗,并結(jié)合了許多安全存儲(chǔ)和擴(kuò)展最復(fù)雜數(shù)據(jù)工作負(fù)載的功能侵歇。
2. 使用PostgreSql優(yōu)勢
PostgreSql提供了許多功能,旨在幫助開發(fā)人員構(gòu)建應(yīng)用程序吓蘑,管理員保護(hù)數(shù)據(jù)完整性并且構(gòu)建容錯(cuò)環(huán)境惕虑,并幫助你管理數(shù)據(jù)坟冲,無論數(shù)據(jù)集的大小。除了免費(fèi)和開源之外溃蔫,Postgre SQL還具有高度的可擴(kuò)展性健提。例如,你可以定義自己的數(shù)據(jù)類型伟叛,構(gòu)建自定義SQL函數(shù)私痹,甚至可以編寫來自不同編程語言的代碼,而不需要重新編譯數(shù)據(jù)庫统刮。
3. PostgreSql安裝
使用如下命令紊遵,會(huì)自動(dòng)安裝最新版,這里為9.5
sudo apt-get install postgresql
安裝完成后网沾,默認(rèn)會(huì):
(1)創(chuàng)建名為"postgres"的Linux用戶
(2)創(chuàng)建名為"postgres"癞蚕、不帶密碼的默認(rèn)數(shù)據(jù)庫賬號(hào)作為數(shù)據(jù)庫管理員
(3)創(chuàng)建名為"postgres"的表
4. psql命令
安裝完后會(huì)有PostgreSQL的客戶端psql,通過 sudo -u postgres psql 進(jìn)入辉哥,提示符變成: postgres=#
在這里可用執(zhí)行SQL語句和psql的基本命令桦山。可用的基本命令如下:
- \password:設(shè)置密碼
- \q:退出
- \h: 查看SQL命令的解釋醋旦,比如\h select恒水。
- \?:查看psql命令列表。饲齐。
- \l:列出所有數(shù)據(jù)庫钉凌。
- \c [database_name]:連接其他數(shù)據(jù)庫。
- \d:列出當(dāng)前數(shù)據(jù)庫的所有表格捂人。
- \d [table_name]:列出某一張表格的結(jié)構(gòu)御雕。
- \du:列出所有用戶。
- \e:打開文本編輯器滥搭。
- \conninfo:列出當(dāng)前數(shù)據(jù)庫和連接的信息酸纲。
5. 修改數(shù)據(jù)庫默認(rèn)賬號(hào)的密碼
a. 登錄
使用psql命令登錄數(shù)據(jù)庫的命令為:
psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432
輸入上面命令以后,系統(tǒng)會(huì)提示輸入dbuser用戶的密碼瑟匆。
上面命令的參數(shù)含義如下:-U指定用戶闽坡,-d指定數(shù)據(jù)庫,-h指定服務(wù)器愁溜,-p指定端口
b. 修改默認(rèn)管理員賬號(hào)的密碼
以Linux用戶"postgres"的身份(此時(shí)只有該用戶有psql命令)執(zhí)行psql客戶端疾嗅,進(jìn)入該客戶端的提示符界面(這里系統(tǒng)用戶名、數(shù)據(jù)庫用戶名冕象、數(shù)據(jù)庫名都為postgres代承,故可采用簡寫形式)
sudo -u postgres psql
postgres=# alter user postgres with password '123456';
這樣,管理員"postgres"的密碼就為"123456"渐扮。
退出psql客戶端命令:\q
若要?jiǎng)h除該管理員的密碼次泽,則可用命令:sudo -u postgres psql -d postgres
6. 配置數(shù)據(jù)庫以允許遠(yuǎn)程連接訪問
安裝完成后穿仪,默認(rèn)只能本地才能連接數(shù)據(jù)庫,其他機(jī)子訪問不了意荤,需要進(jìn)行配置啊片。
a. 修改監(jiān)聽地址
vim /etc/postgresql/9.5/main/postgresql.conf
將 #listen_addresses = 'localhost' 的注釋去掉并改為 listen_addresses = '*'
b. 修改可訪問用戶的IP段
vim /etc/postgresql/9.5/main/pg_hba.conf
在文件末尾添加: host all all 0.0.0.0 0.0.0.0 md5 ,表示運(yùn)行任何IP連接
c. 重啟數(shù)據(jù)庫
/etc/init.d/postgresql restart
7. 添加新用戶和新數(shù)據(jù)庫
a. 使用PostgreSQL客戶端psql
運(yùn)行系統(tǒng)用戶"postgres"的psql命令玖像,進(jìn)入客戶端:
sudo -u postgres psql
創(chuàng)建用戶"xiaoai"并設(shè)置密碼:
postgres=# create user xiaoai with password '123456';
創(chuàng)建數(shù)據(jù)庫exampledb_12_21紫谷,所有者為xiaoai:
postgres=# create database exampledb_12_21 owner xiaoai;
將exampledb_12_21數(shù)據(jù)庫的所有權(quán)限賦予xiaoai,否則xiaoai只能登錄psql捐寥,沒有任何數(shù)據(jù)庫操作權(quán)限:
postgres=# grant all privileges on database exampledb_12_21 to xiaoai;
b. 使用shell命令行
安裝PostgreSQL后提供了createuser和createdb命令行程序笤昨。
首先創(chuàng)建數(shù)據(jù)庫用戶"test",并指定為超級(jí)用戶:
sudo -u postgres createuser --superuser test;
接著登錄psql控制臺(tái)設(shè)置其密碼后退出:
然后在shell命令行下創(chuàng)建數(shù)據(jù)庫并指定所有者:
sudo -u postgres createdb -O test testdb;
8. PostgreSql功能介紹:
a. 數(shù)據(jù)類型
基本類型:Integer, Numeric, String, Boolean
結(jié)構(gòu)類型:Date/Time, Array, Range, UUID
文檔類型:JSON/JSONB, XML, Key-value(Hstore)
幾何類型:Point, Line, Circle, Polygon
自定義類型:Composite, Custom Types
b. 數(shù)據(jù)完整性
- 唯一性握恳, 不為空
- 主鍵瞒窒, 外鍵
- 排除約束
- 顯示鎖定, 咨詢鎖定
c. 并發(fā)性乡洼,性能
- 索引崇裁,高級(jí)索引
- 復(fù)雜的查詢計(jì)劃期/優(yōu)化器
- 交互
- 多版本并發(fā)控制(MVCC)
- 讀取查詢的并行化和構(gòu)建B樹索引
- 表分區(qū)
- Sql標(biāo)準(zhǔn)中定義的所有事物隔離級(jí)別,包括Serializable
- 即時(shí)表達(dá)式匯編(JIT)
d. 可靠性束昵, 災(zāi)難恢復(fù)
- 預(yù)寫日志(WAL)
- 復(fù)制:異步拔稳,同步,邏輯
- 時(shí)間點(diǎn)恢復(fù)锹雏,主動(dòng)備用
- 表空間
e. 安全性
- 身份驗(yàn)證: GSSAPI, SSPI, LDAP, SCRAM-SHA-256, 證書等
- 強(qiáng)大的訪問控制系統(tǒng)
- 列和行級(jí)安全性
f. 可擴(kuò)展性
- 存儲(chǔ)的功能和程序
- 程序語言:PL/PGSQL, Perl, Python等
- 外部數(shù)據(jù)包裝器:使用標(biāo)準(zhǔn)SQL接口連接到其他數(shù)據(jù)庫或流
- 許多提供附加功能的擴(kuò)展巴比,包括PostGIS
g. 國際化, 文本搜索
- 支付國際字符集礁遵,例如通過ICU校對(duì)
- 全文檢索
基本數(shù)據(jù)庫操作命令
# 創(chuàng)建新表
CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);
# 插入數(shù)據(jù)
INSERT INTO user_tbl(name, signup_date) VALUES('xaioai', '2019-12-21');
# 選擇記錄
SELECT * FROM user_tbl;
# 更新數(shù)據(jù)
UPDATE user_tbl set name = '小艾' WHERE name = 'xaioai';
# 刪除記錄
DELETE FROM user_tbl WHERE name = '小艾' ;
# 添加欄位
ALTER TABLE user_tbl ADD email VARCHAR(40);
# 更新結(jié)構(gòu)
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;
# 更名欄位
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;
# 刪除欄位
ALTER TABLE user_tbl DROP COLUMN email;
# 表格更名
ALTER TABLE user_tbl RENAME TO backup_tbl;
# 刪除表格
DROP TABLE IF EXISTS backup_tbl;
重啟服務(wù)
/etc/init.d/postgresql restart
或者
service postgresql restart
卸載
sudo apt-get purge 'postgresql-*'
sudo apt-get autoremove 'postgresql-*'
對(duì)比Mysql:
- PostgreSQL的穩(wěn)定性極強(qiáng)轻绞,Innodb等引擎在崩潰、斷電之類的災(zāi)難場景下抗打擊能力有了長足的進(jìn)步佣耐,然而很多Mysql用戶都遇到過Server級(jí)的數(shù)據(jù)庫丟失的場景---Mysql系統(tǒng)庫是MyISAM的铲球,相較而言,PG數(shù)據(jù)庫在這方面要好一些晰赞。
- 任何系統(tǒng)都有他的性能極限,在高并發(fā)讀寫选侨,負(fù)載逼近極限下掖鱼,PG的性能指標(biāo)仍然可以維持雙曲線甚至對(duì)數(shù)曲線,到頂峰之后不再下降援制,而MySQL明細(xì)出現(xiàn)一個(gè)波峰后下滑戏挡。
- PG多年在GIS領(lǐng)域處于優(yōu)勢地位,因?yàn)樗胸S富的幾何類型晨仑,實(shí)際上不止幾何類型褐墅,PG中有大量的字典拆檬、數(shù)組、bitmap等數(shù)據(jù)類型妥凳,相比之下MaySQL就差很多竟贯,insagram就是因?yàn)镻G的空間數(shù)據(jù)庫擴(kuò)展POSTGIS遠(yuǎn)遠(yuǎn)強(qiáng)于MySQL的my spatial而采用PGSQL的。
- PG的“無鎖定”特性非常突出逝钥,甚至包括vacuum這樣的整理數(shù)據(jù)空間的操作屑那,這個(gè)和PGSSQL的MVCC實(shí)現(xiàn)有關(guān)系。
- PG的可以使用函數(shù)和條件索引艘款,這使得PG數(shù)據(jù)庫的調(diào)優(yōu)非常靈活持际,mysql就沒有這個(gè)功能,條件索引在web應(yīng)用中很重要哗咆。
- PG有極其強(qiáng)悍的SQL編程能力蜘欲,有豐富的統(tǒng)計(jì)函數(shù)和統(tǒng)計(jì)語法支持,比如分析函數(shù)(Oracle的叫法晌柬,PG里面叫Window函數(shù))姥份,還可以用多種語言來寫存儲(chǔ)過程,對(duì)于R的支持也很好空繁。這一點(diǎn)上MySQL就差的很遠(yuǎn)殿衰,很多分析功能那個(gè)都沒有,騰訊內(nèi)部數(shù)據(jù)存儲(chǔ)主要是Mysql盛泡,但是主要的數(shù)據(jù)分析就是Hadoop+PGsql闷祥。
- PG的有很多中集群架構(gòu)可以選擇,plproxy可以支持語句級(jí)的鏡像或者分片傲诵,slony可以進(jìn)行字段級(jí)的同步設(shè)置凯砍,standby可以構(gòu)建WAL文件級(jí)或者流式的讀寫分離集群,同步頻率和集群策略調(diào)整方便拴竹,操作非常簡單悟衩。
- 一般關(guān)系型數(shù)據(jù)庫的字符串有限定長度8k左右,無限長Text類型的功能受限栓拜,只能作為外部大數(shù)據(jù)訪問座泳。而PG的TEXT類型可以直接方法,SQL語法內(nèi)置正則表達(dá)式幕与,可以索引挑势,還可以全文檢索,或使用xml xpath啦鸣。用PG的話潮饱,文檔數(shù)據(jù)庫就可以省略了
- 對(duì)于web應(yīng)用來說,復(fù)制的特性很重要诫给,Mysql到現(xiàn)在也是異步復(fù)制香拉,pgsql可以做到同步啦扬,異步,半同步復(fù)制凫碌。還有mysql的同步是基于binlog復(fù)制扑毡,類似oracle golden gate, 是基于stream的復(fù)制,做到同步很困難证鸥,這種方式更加適合異地復(fù)制僚楞,pgsql的復(fù)制基于wal,可以做到同步復(fù)制枉层。同時(shí)pgsql還提供stream復(fù)制泉褐。
- Pgsql對(duì)于numa架構(gòu)的支持要比mysql強(qiáng)一些,比mysql對(duì)于讀的性能要好些鸟蜡,pgsql提交可以完全異步膜赃,而mysql的內(nèi)存表不夠?qū)嵱茫ū礞i原因)
[引用鏈接]:https://blog.csdn.net/qq_40223688/article/details/89451616