概述

postgresql是一個開源的關系型數(shù)據(jù)庫睬罗，可以作為mysql的替代品踱启。
本篇意在讓讀者快速的了解postgresql的基本概念休建，使用的版本是postgresql9.6本袍镀，如有錯誤歡迎指正。

參考書籍

《PostgreSQL 9 Administration Cookbook》第二版。

本篇省略的東西

有些內(nèi)容比較冗長漱办，或者使用場景較少这刷，或者過于復雜，建議參考具體的手冊娩井，本篇將會省略暇屋。

• 選擇postgresql的理由。
• 常用的用于操作的語法洞辣、語句咐刨。
• 數(shù)據(jù)庫函數(shù)的編寫與執(zhí)行。
• 不同數(shù)據(jù)庫扬霜、模式定鸟、表空間之間的數(shù)據(jù)移動。
• 訪問其他postgres數(shù)據(jù)庫或者其他類型的數(shù)據(jù)庫著瓶。
• 自動數(shù)據(jù)庫維護的定制化配置联予。
• 數(shù)據(jù)庫的復制配置。
• 數(shù)據(jù)庫的升級操作材原。

postgres服務使用

發(fā)音

post-gres-q-l沸久。

下載、安裝余蟹、啟動

基本上主流unix操作系統(tǒng)的庫里都有制作好的安裝包卷胯，直接安裝即可，然后用相應的系統(tǒng)服務啟動威酒。
也可以使用命令行工具啟動或者停止窑睁。
-m參數(shù)可以指定關閉方式，fast會等待所有操作完畢后再關閉葵孤，immediate則會強行關閉担钮。

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

每個服務在初始化的時候都會被分配一個系統(tǒng)標識符，很多系統(tǒng)操作需要使用到佛呻，可以通過如下命令獲得。

pg_controldata Datadir | grep “system identifier”

版本號

postgreql的版本號組成為：主版本號.次版本號.維護版本號病线，它的特性和兼容性是由主版本號和次版本號共同決定的吓著。

psql --version

連接

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

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

每個連接在同一時刻只允許有一個活躍的事務，并且在任何時刻只允許有一個完全活躍的語句惕耕。

數(shù)據(jù)庫

psql -l

template0和template1是兩個數(shù)據(jù)庫模板纺裁，template1是可以修改的，用來自定義。

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

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

postgres客戶端

幫助

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

注釋

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

退出

postgres=# \q

開關一行顯示一列數(shù)據(jù)

postgres=# \x

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

postgres=# \! cat test.sql

命令從文件中定向輸入

postgres=# \i test.sql

臨時數(shù)據(jù)庫配置欺缘，LOCAL表示僅對當前事務生效

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

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

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

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

postgres=# ANALYZE;

垃圾回收

postgres=# VACUUM;

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

服務（server）

• 一個操作系統(tǒng)中可以啟動多個postgres服務栋豫。
• 每個服務由多個進程組成，為首的進程名為postmaster谚殊。
• 每個服務要占用一個端口丧鸯，多個服務不能共享端口。
• 每個服務都有一個data目錄用于存放數(shù)據(jù)嫩絮，目錄不允許修改丛肢，否則會破壞數(shù)據(jù)庫，并且無法修復剿干。
• 服務使用4字節(jié)長的內(nèi)部事務標識符蜂怎，即時發(fā)生重疊后仍然繼續(xù)使用，這會導致問題置尔，所以需要定期進行VACUUM操作杠步。

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

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

模式（schema）

• 一個數(shù)據(jù)庫中可以有多個模式，模式相當于表的命名空間设褐，類似于mysql中的database颠蕴，可以使用帶模式的完整名稱來訪問或者創(chuàng)建對象。
• 不同模式之間的表是可以聯(lián)表查詢的助析。
• 可以通過對用戶設置search_path參數(shù)來指定默認搜索的模式犀被。

表（table）

• 一個模式中可以有多張表。
• 表是由多個關系元素組成的外冀，大字段數(shù)據(jù)放在另一個名為TOAST的表中寡键，每張表都有一個TOAST表和TOAST索引。
• 用雙引號括起來的表和沒用雙引號括起來的表是不一樣的雪隧，即使名字一樣西轩。
• 雙引號括起來的表區(qū)分大小寫，沒用雙引號括起來的表不區(qū)分大小寫脑沿。

列（column）

• 每張表都由許多列組成藕畔，每一列有一個列名、類型庄拇、默認值等屬性注服，用來存儲每一條記錄中的各種值韭邓。
• 文本類型統(tǒng)一由一種數(shù)據(jù)類型存儲，支持長度從1B到1G溶弟，經(jīng)過優(yōu)化女淑，存儲少的時候很高效，存儲多的時候會自動管理和壓縮可很。
• 自增類型serial本質(zhì)上就是整數(shù)诗力，通過創(chuàng)建并關聯(lián)到一個SEQUENCE類型的對象來記錄自增值。

表空間（tablespace）

• 默認情況下我抠，所有的數(shù)據(jù)都會放在postgres指定的data目錄下苇本，通過定義表空間，可以讓postgres將數(shù)據(jù)存放在不同的設備上菜拓。
• 表空間是通過軟鏈接來實現(xiàn)的瓣窄。
• 建議為每個數(shù)據(jù)庫設立一個單獨的表空間，尤其是不同數(shù)據(jù)庫中有同名的模式或者表的時候纳鼎。

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

視圖（view）

• 視圖本質(zhì)上是預定義好的一個sql查詢俺夕，以一張表的形式給出，在每次調(diào)用時都會執(zhí)行相應的sql查詢贱鄙。

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

• 當視圖足夠簡單的時候劝贸，postgres是支持視圖更新的，相應的更新會傳遞到相應的表中逗宁。
  還可以使用INSTEAD OF觸發(fā)器或者規(guī)則來實現(xiàn)視圖更新映九，請參考具體的操作手冊。
• 物化視圖可以預先將數(shù)據(jù)查詢出來瞎颗，這樣調(diào)用的時候就不必反復查詢了件甥，更新需要手動更新。

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

行（row）

• 行即表中的一條數(shù)據(jù)哼拔。
• postgres中每個行都有一個行版本引有，而且還有兩個系統(tǒng)列xmin和xmax，分別標示這個行被創(chuàng)建和刪除的事務倦逐。
  刪除時譬正，設置xmax為刪除事務號，不會實際執(zhí)行刪除檬姥。
• UPDATE操作被認為是緊跟INSERT操作后的DELETE操作曾我。

索引（index）

• 索引可以用來給表添加約束或者提高查詢速度。
• 在涉及高比例插入\刪除的表中穿铆，會造成索引膨脹您单，這時候可以重建索引斋荞。

reindexdb

• 創(chuàng)建CONCURRENTLY索引時不會持有全表鎖荞雏，這條指令分成兩個步驟，第一部分創(chuàng)建索引并標記為不可用，這時候INSERT凤优、UPDATE悦陋、DELETE操作已經(jīng)開始維護索引了，但是查詢不能使用索引筑辨。建立完畢后才會被標記為可用俺驶。

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

• 可以手工設置索引的可用性。

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

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

postgres權(quán)限控制

連接權(quán)限

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

listen_addresses = '*'

postgres連接的權(quán)限在數(shù)據(jù)目錄下的pg_hba.conf文件中按照指定格式進行定義抚太，每次連接會按照順序檢查每條規(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ù)庫匾乓，all表示所有、sameuser表示用戶名和數(shù)據(jù)庫名相同身冀、其他表示數(shù)據(jù)庫名稱钝尸。
• user：連接的用戶，all表示所有用戶搂根、其他表示連接的數(shù)據(jù)庫用戶珍促。
• address：允許連接的子網(wǎng)和子網(wǎng)掩碼。
• method：認證方式剩愧，trust表示允許猪叙、password表示密碼、md5表示加密密碼仁卷、cert表示證書穴翩、ldap表示ldap認證，對于證書認證之后還會有一個參數(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)限垛叨，不能被授予伦糯，也不會被繼承，分別是SUPERUSER嗽元、CREATEDB和CREATEUSER;
• 特殊權(quán)限單獨對用戶進行授予或者回收敛纲。

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

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

postgres=# SET ROLE TO role;

• 可以通過NOINHERIT參數(shù)來創(chuàng)建不會繼承角色的用戶剂癌，這樣用戶只有切換角色才能獲得相應的權(quán)限载慈。

postgres=# CREATE USER user NOINGERIT;

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

數(shù)

• 整數(shù)：smallint、integer珍手、bigint
• 實數(shù)：real办铡、double
• 自增數(shù)：smallserial、serial琳要、bigserial
• 高精度：decimal寡具、numeric

串

• 有限不定長字符串：varchar
• 有限定長字符串：char
• 無限不定長字符串：text
• 位串：bit
• UUID

幾何

• 點：point
• 線：line、lseg
• 矩形：box
• 圓：circle
• 路徑：path
• 多邊形：polygon

其他

• 布爾：boolean
• 枚舉：enum
• 二進制：bytea
• 時間：date稚补、time童叠、timestamp、interval
• 貨幣：money
• 網(wǎng)絡地址：cidr课幕、inet厦坛、macaddr
• 數(shù)據(jù)格式：json、xml
• 復合類型：table乍惊、type

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

postgres腳本執(zhí)行

postgres會按照順序執(zhí)行腳本中的每條指令，遇到錯誤會報錯并繼續(xù)執(zhí)行接下來的指令润绎。

事務

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

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

因為事務的特性，使得事務中不能包含以下命令涂佃。

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

遇錯退出

通過設置開關來實現(xiàn)遇到錯誤就停止腳本励翼。

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

數(shù)據(jù)導入與導出

postgres提供兩個工具來進行數(shù)據(jù)導入和導出粮揉，pg_dump和pg_dumpall。

• pg_dump工具產(chǎn)生一個默認輕量壓縮的自定義格式歸檔文件抚笔，通過pg_restore恢復；pg_dumpall工具產(chǎn)生一個腳本文件侨拦，通過psql執(zhí)行殊橙。
• pg_dump不會導出角色和表空間；pg_dumpall可以導出全局對象狱从，也可以只導出全局對象膨蛮。
• pg_dump和gp_restore可以并行，pg_dumpall和psql只能串行季研。
• pg_dump也可以通過選項-F p來生成腳本文件敞葛。
  postgres還可以進行熱物理備份，需要遵循步驟來處理備份中的增量數(shù)據(jù)与涡，詳細信息請參考手冊惹谐。

復制

postgres提供兩種復制方法，物理流復制和邏輯流復制驼卖。
之前還有一種基于日志文件傳送的復制氨肌，主節(jié)點將數(shù)據(jù)庫更改記錄到事務日志，然后將日志文件從主節(jié)點傳動到備節(jié)點酌畜，之后備節(jié)點再重演日志≡跚簦現(xiàn)在它已經(jīng)被流復制取代了，不推薦使用桥胞。

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