概述

postgresql是一個(gè)開(kāi)源的關(guān)系型數(shù)據(jù)庫(kù)，可以作為mysql的替代品。
本篇意在讓讀者快速的了解postgresql的基本概念，使用的版本是postgresql9.6本，如有錯(cuò)誤歡迎指正。

參考書(shū)籍

《PostgreSQL 9 Administration Cookbook》第二版。

本篇省略的東西

有些內(nèi)容比較冗長(zhǎng)，或者使用場(chǎng)景較少，或者過(guò)于復(fù)雜，建議參考具體的手冊(cè)，本篇將會(huì)省略。

• 選擇postgresql的理由。
• 常用的用于操作的語(yǔ)法、語(yǔ)句。
• 數(shù)據(jù)庫(kù)函數(shù)的編寫(xiě)與執(zhí)行。
• 不同數(shù)據(jù)庫(kù)、模式、表空間之間的數(shù)據(jù)移動(dòng)。
• 訪問(wèn)其他postgres數(shù)據(jù)庫(kù)或者其他類型的數(shù)據(jù)庫(kù)。
• 自動(dòng)數(shù)據(jù)庫(kù)維護(hù)的定制化配置。
• 數(shù)據(jù)庫(kù)的復(fù)制配置。
• 數(shù)據(jù)庫(kù)的升級(jí)操作。

postgres服務(wù)使用

發(fā)音

post-gres-q-l。

下載、安裝、啟動(dòng)

基本上主流unix操作系統(tǒng)的庫(kù)里都有制作好的安裝包，直接安裝即可，然后用相應(yīng)的系統(tǒng)服務(wù)啟動(dòng)。
也可以使用命令行工具啟動(dòng)或者停止。
-m參數(shù)可以指定關(guān)閉方式，fast會(huì)等待所有操作完畢后再關(guān)閉，immediate則會(huì)強(qiáng)行關(guān)閉。

pg_ctl -D Datadir start
pg_ctl -D Datadir -m fast stop
pg_ctl -D Datadir -m immediate stop

每個(gè)服務(wù)在初始化的時(shí)候都會(huì)被分配一個(gè)系統(tǒng)標(biāo)識(shí)符，很多系統(tǒng)操作需要使用到，可以通過(guò)如下命令獲得。

pg_controldata Datadir | grep “system identifier”

版本號(hào)

postgreql的版本號(hào)組成為：主版本號(hào).次版本號(hào).維護(hù)版本號(hào)，它的特性和兼容性是由主版本號(hào)和次版本號(hào)共同決定的。

psql --version

連接

初始管理員用戶：postgres，密碼：postgres，數(shù)據(jù)庫(kù)：postgres。

psql postgres://user:password@host:port/db
psql -U user -W password -h host -p port -d db

每個(gè)連接在同一時(shí)刻只允許有一個(gè)活躍的事務(wù)，并且在任何時(shí)刻只允許有一個(gè)完全活躍的語(yǔ)句。

數(shù)據(jù)庫(kù)

psql -l

template0和template1是兩個(gè)數(shù)據(jù)庫(kù)模板，template1是可以修改的，用來(lái)自定義。

命令行執(zhí)行語(yǔ)句和腳本

psql -c "SELECT current_time"
psql -f test.sql

postgres客戶端

幫助

postgres=# help
postgres=# ?
postgres=# \h

注釋

-- 單行注釋
/*
* 多行注釋
*/

退出

postgres=# \q

開(kāi)關(guān)一行顯示一列數(shù)據(jù)

postgres=# \x

運(yùn)行操作系統(tǒng)的命令

postgres=# \! cat test.sql

命令從文件中定向輸入

postgres=# \i test.sql

臨時(shí)數(shù)據(jù)庫(kù)配置，LOCAL表示僅對(duì)當(dāng)前事務(wù)生效

postgres=# SET work_mem = '16MB';
postgres=# SET LOCAL work_mem = '16MB';
postgres=# RESET work_mem;
postgres=# SHOW work_mem;

管理數(shù)據(jù)庫(kù)插件

postgres=# CREATE EXTENSION myext;
postgres=# ALTER EXTENSION myext UPDATE;
postgres=# DROP EXTENSION myext;

更新所有表大小和數(shù)據(jù)分布的統(tǒng)計(jì)信息

postgres=# ANALYZE;

垃圾回收

postgres=# VACUUM;

postgres數(shù)據(jù)管理

服務(wù)（server）

• 一個(gè)操作系統(tǒng)中可以啟動(dòng)多個(gè)postgres服務(wù)。
• 每個(gè)服務(wù)由多個(gè)進(jìn)程組成，為首的進(jìn)程名為postmaster。
• 每個(gè)服務(wù)要占用一個(gè)端口，多個(gè)服務(wù)不能共享端口。
• 每個(gè)服務(wù)都有一個(gè)data目錄用于存放數(shù)據(jù)，目錄不允許修改，否則會(huì)破壞數(shù)據(jù)庫(kù)，并且無(wú)法修復(fù)。
• 服務(wù)使用4字節(jié)長(zhǎng)的內(nèi)部事務(wù)標(biāo)識(shí)符，即時(shí)發(fā)生重疊后仍然繼續(xù)使用，這會(huì)導(dǎo)致問(wèn)題，所以需要定期進(jìn)行VACUUM操作。

數(shù)據(jù)庫(kù)（database）

• 一個(gè)服務(wù)中可以擁有多個(gè)數(shù)據(jù)庫(kù)。
• 數(shù)據(jù)庫(kù)默認(rèn)是任何用戶可連接的，創(chuàng)建好后需要修改相應(yīng)的權(quán)限。
• 數(shù)據(jù)庫(kù)之間的數(shù)據(jù)是隔離的，不能進(jìn)行聯(lián)表。
• 數(shù)據(jù)庫(kù)默認(rèn)的數(shù)據(jù)塊大小為8192。

模式（schema）

• 一個(gè)數(shù)據(jù)庫(kù)中可以有多個(gè)模式，模式相當(dāng)于表的命名空間，類似于mysql中的database，可以使用帶模式的完整名稱來(lái)訪問(wèn)或者創(chuàng)建對(duì)象。
• 不同模式之間的表是可以聯(lián)表查詢的。
• 可以通過(guò)對(duì)用戶設(shè)置search_path參數(shù)來(lái)指定默認(rèn)搜索的模式。

表（table）

• 一個(gè)模式中可以有多張表。
• 表是由多個(gè)關(guān)系元素組成的，大字段數(shù)據(jù)放在另一個(gè)名為T(mén)OAST的表中，每張表都有一個(gè)TOAST表和TOAST索引。
• 用雙引號(hào)括起來(lái)的表和沒(méi)用雙引號(hào)括起來(lái)的表是不一樣的，即使名字一樣。
• 雙引號(hào)括起來(lái)的表區(qū)分大小寫(xiě)，沒(méi)用雙引號(hào)括起來(lái)的表不區(qū)分大小寫(xiě)。

列（column）

• 每張表都由許多列組成，每一列有一個(gè)列名、類型、默認(rèn)值等屬性，用來(lái)存儲(chǔ)每一條記錄中的各種值。
• 文本類型統(tǒng)一由一種數(shù)據(jù)類型存儲(chǔ)，支持長(zhǎng)度從1B到1G，經(jīng)過(guò)優(yōu)化，存儲(chǔ)少的時(shí)候很高效，存儲(chǔ)多的時(shí)候會(huì)自動(dòng)管理和壓縮。
• 自增類型serial本質(zhì)上就是整數(shù)，通過(guò)創(chuàng)建并關(guān)聯(lián)到一個(gè)SEQUENCE類型的對(duì)象來(lái)記錄自增值。

表空間（tablespace）

• 默認(rèn)情況下，所有的數(shù)據(jù)都會(huì)放在postgres指定的data目錄下，通過(guò)定義表空間，可以讓postgres將數(shù)據(jù)存放在不同的設(shè)備上。
• 表空間是通過(guò)軟鏈接來(lái)實(shí)現(xiàn)的。
• 建議為每個(gè)數(shù)據(jù)庫(kù)設(shè)立一個(gè)單獨(dú)的表空間，尤其是不同數(shù)據(jù)庫(kù)中有同名的模式或者表的時(shí)候。

postgres=# CREATE TABLESPACE tbs LOCATION '/usr/local/tbs';

視圖（view）

• 視圖本質(zhì)上是預(yù)定義好的一個(gè)sql查詢，以一張表的形式給出，在每次調(diào)用時(shí)都會(huì)執(zhí)行相應(yīng)的sql查詢。

postgres=# CREATE VIEW view AS SELECT * FROM tb;

• 當(dāng)視圖足夠簡(jiǎn)單的時(shí)候，postgres是支持視圖更新的，相應(yīng)的更新會(huì)傳遞到相應(yīng)的表中。
  還可以使用INSTEAD OF觸發(fā)器或者規(guī)則來(lái)實(shí)現(xiàn)視圖更新，請(qǐng)參考具體的操作手冊(cè)。
• 物化視圖可以預(yù)先將數(shù)據(jù)查詢出來(lái)，這樣調(diào)用的時(shí)候就不必反復(fù)查詢了，更新需要手動(dòng)更新。

postgres=# CREATE MATERIALIZED VIEW view AS SELECT * FROM tb;
postgres=# REFRESH MATERIALIZED VIEW view;

行（row）

• 行即表中的一條數(shù)據(jù)。
• postgres中每個(gè)行都有一個(gè)行版本，而且還有兩個(gè)系統(tǒng)列xmin和xmax，分別標(biāo)示這個(gè)行被創(chuàng)建和刪除的事務(wù)。
  刪除時(shí)，設(shè)置xmax為刪除事務(wù)號(hào)，不會(huì)實(shí)際執(zhí)行刪除。
• UPDATE操作被認(rèn)為是緊跟INSERT操作后的DELETE操作。

索引（index）

• 索引可以用來(lái)給表添加約束或者提高查詢速度。
• 在涉及高比例插入\刪除的表中，會(huì)造成索引膨脹，這時(shí)候可以重建索引。

reindexdb

• 創(chuàng)建CONCURRENTLY索引時(shí)不會(huì)持有全表鎖，這條指令分成兩個(gè)步驟，第一部分創(chuàng)建索引并標(biāo)記為不可用，這時(shí)候INSERT、UPDATE、DELETE操作已經(jīng)開(kāi)始維護(hù)索引了，但是查詢不能使用索引。建立完畢后才會(huì)被標(biāo)記為可用。

postgres=# CREATE CONCURRENTLY INDEX index ON tb(id);

• 可以手工設(shè)置索引的可用性。

UPDATE pg_index SET indisvalid = false WHERE indexrelid = index::regclass;

postgres目錄結(jié)構(gòu)

postgres權(quán)限控制

連接權(quán)限

首先要修改數(shù)據(jù)目錄下的postgresql.conf文件來(lái)允許它監(jiān)聽(tīng)所有地址。修改完后需要重啟服務(wù)。

listen_addresses = '*'

postgres連接的權(quán)限在數(shù)據(jù)目錄下的pg_hba.conf文件中按照指定格式進(jìn)行定義，每次連接會(huì)按照順序檢查每條規(guī)則，直到匹配到通過(guò)策略或者拒絕策略。

#TYPE DATABASE USER ADDRESS METHOD
host all all 127.0.0.1/32 password

每條策略的參數(shù)如下：

• type：連接類型，local表示允許本地socket連接、host表示允許ip連接、hostssl表示允許ssl連接。
• database：連接的數(shù)據(jù)庫(kù)，all表示所有、sameuser表示用戶名和數(shù)據(jù)庫(kù)名相同、其他表示數(shù)據(jù)庫(kù)名稱。
• user：連接的用戶，all表示所有用戶、其他表示連接的數(shù)據(jù)庫(kù)用戶。
• address：允許連接的子網(wǎng)和子網(wǎng)掩碼。
• method：認(rèn)證方式，trust表示允許、password表示密碼、md5表示加密密碼、cert表示證書(shū)、ldap表示ldap認(rèn)證，對(duì)于證書(shū)認(rèn)證之后還會(huì)有一個(gè)參數(shù)來(lái)配置證書(shū)。

權(quán)限模型

• postgres采用角色（ROLE）-權(quán)限（PRIVILEGE）模型。
• 權(quán)限主要有USAGE、SELECT、INSERT、UPDATE、DELETE五種。
• 角色分兩種，可以登錄的用戶（USER）與不可以登錄的組（GROUP）。
• 角色可以繼承。

postgres=# CREATE USER user;
postgres=# CREATE ROLE role NOLOGIN;
postgres=# GRANT role1 TO role2;

特殊權(quán)限

• postgres中有一些特殊權(quán)限，不能被授予，也不會(huì)被繼承，分別是SUPERUSER、CREATEDB和CREATEUSER;
• 特殊權(quán)限單獨(dú)對(duì)用戶進(jìn)行授予或者回收。

角色切換與權(quán)限繼承

• 用戶可以切換到自己擁有的角色。

postgres=# SET ROLE TO role;

• 可以通過(guò)NOINHERIT參數(shù)來(lái)創(chuàng)建不會(huì)繼承角色的用戶，這樣用戶只有切換角色才能獲得相應(yīng)的權(quán)限。

postgres=# CREATE USER user NOINGERIT;

postgres支持的數(shù)據(jù)類型

數(shù)

• 整數(shù)：smallint、integer、bigint
• 實(shí)數(shù)：real、double
• 自增數(shù)：smallserial、serial、bigserial
• 高精度：decimal、numeric

串

• 有限不定長(zhǎng)字符串：varchar
• 有限定長(zhǎng)字符串：char
• 無(wú)限不定長(zhǎng)字符串：text
• 位串：bit
• UUID

幾何

• 點(diǎn)：point
• 線：line、lseg
• 矩形：box
• 圓：circle
• 路徑：path
• 多邊形：polygon

其他

• 布爾：boolean
• 枚舉：enum
• 二進(jìn)制：bytea
• 時(shí)間：date、time、timestamp、interval
• 貨幣：money
• 網(wǎng)絡(luò)地址：cidr、inet、macaddr
• 數(shù)據(jù)格式：json、xml
• 復(fù)合類型：table、type

postgres中一些系統(tǒng)變量、系統(tǒng)函數(shù)和系統(tǒng)視圖

postgres腳本執(zhí)行

postgres會(huì)按照順序執(zhí)行腳本中的每條指令，遇到錯(cuò)誤會(huì)報(bào)錯(cuò)并繼續(xù)執(zhí)行接下來(lái)的指令。

事務(wù)

事務(wù)的功能是讓一批指令要么同時(shí)執(zhí)行成功，要么同時(shí)執(zhí)行失敗。本質(zhì)上postgres會(huì)按照順序執(zhí)行指令，一旦遇到錯(cuò)誤，就回滾之前的指令。
事務(wù)是不能夠嵌套的，否則后續(xù)的BEGIN會(huì)被忽略。

postgres=# BEGIN;
postgres=# command1;
postgres=# command2;
postgres=# COMMIT;

因?yàn)槭聞?wù)的特性，使得事務(wù)中不能包含以下命令。

• CREATE/DROP DATABASE/TABLESPACE
• CREATE INDEX CONCURRENTCY
• VACUUM
• REINDEX DATABASE / REINDEX SYSTEM
• CLUSTER

遇錯(cuò)退出

通過(guò)設(shè)置開(kāi)關(guān)來(lái)實(shí)現(xiàn)遇到錯(cuò)誤就停止腳本。

psql -f test.sql -v ON_ERROR_STOP=on
postgres=# \set ON_ERROR_STOP

數(shù)據(jù)導(dǎo)入與導(dǎo)出

postgres提供兩個(gè)工具來(lái)進(jìn)行數(shù)據(jù)導(dǎo)入和導(dǎo)出，pg_dump和pg_dumpall。

• pg_dump工具產(chǎn)生一個(gè)默認(rèn)輕量壓縮的自定義格式歸檔文件，通過(guò)pg_restore恢復(fù)；pg_dumpall工具產(chǎn)生一個(gè)腳本文件，通過(guò)psql執(zhí)行。
• pg_dump不會(huì)導(dǎo)出角色和表空間；pg_dumpall可以導(dǎo)出全局對(duì)象，也可以只導(dǎo)出全局對(duì)象。
• pg_dump和gp_restore可以并行，pg_dumpall和psql只能串行。
• pg_dump也可以通過(guò)選項(xiàng)-F p來(lái)生成腳本文件。
  postgres還可以進(jìn)行熱物理備份，需要遵循步驟來(lái)處理備份中的增量數(shù)據(jù)，詳細(xì)信息請(qǐng)參考手冊(cè)。

復(fù)制

postgres提供兩種復(fù)制方法，物理流復(fù)制和邏輯流復(fù)制。
之前還有一種基于日志文件傳送的復(fù)制，主節(jié)點(diǎn)將數(shù)據(jù)庫(kù)更改記錄到事務(wù)日志，然后將日志文件從主節(jié)點(diǎn)傳動(dòng)到備節(jié)點(diǎn)，之后備節(jié)點(diǎn)再重演日志?，F(xiàn)在它已經(jīng)被流復(fù)制取代了，不推薦使用。

• 物理流復(fù)制獲取事務(wù)日志W(wǎng)AL并將數(shù)據(jù)傳送到遠(yuǎn)程節(jié)點(diǎn)，WAL包含數(shù)據(jù)塊中發(fā)生的改變的精確物理備份，因此遠(yuǎn)程節(jié)點(diǎn)是主節(jié)點(diǎn)的精確副本，也無(wú)法執(zhí)行寫(xiě)入到數(shù)據(jù)庫(kù)的事務(wù)。
• 邏輯流復(fù)制允許遠(yuǎn)程節(jié)點(diǎn)復(fù)制數(shù)據(jù)而不需要保持?jǐn)?shù)據(jù)庫(kù)的完整副本，復(fù)制中所有形式的身份驗(yàn)證和安全工作都和普通的連接相同。邏輯流復(fù)制可以實(shí)現(xiàn)多主復(fù)制。
• 復(fù)制槽位功能允許明確定義復(fù)制架構(gòu)，還允許即使在節(jié)點(diǎn)連接斷開(kāi)的情況下跟蹤節(jié)點(diǎn)的細(xì)節(jié)。邏輯流復(fù)制以及任何其他使用邏輯解碼功能的工具都需要復(fù)制槽位，但對(duì)于物理流是可選的。