世界快報:【數據庫學習】數據庫平臺：postgresql安裝配置與常見命令

1，概念

2，安裝配置與常見命令

【資料圖】

1）安裝與配置

#安裝yum install https:....rpm

1>安裝目錄

bin目錄：二進制可執行文件目錄，此目錄下有postgres、psql等可執行程序；pg_ctl工具在此目錄，可以通過pg_ctl --help查看具體使用。conf目錄：emptyinclude：頭文件目錄lib：動態庫目錄，如libpq.soshare：存放文檔和配置模板文件，一些擴展包的sql文件在子目錄extension下。

2>數據目錄

/var/lib/pgsql//data。

i>pg_hba.conf（認證配置文件）

用于配置數據庫的遠程連接，通過加入以下命令行，運行任何用戶遠程連接本數據庫，連接時需要提供密碼。

host    replication     all             127.0.0.1/32            md5host    replication     all             10.99.99.99            md5  # ip為10.99.99.99機器可訪問host    all             all          0.0.0.0/0       md5     # navicat可訪問。md5表示要求客戶端提供一個 MD5 加密的口令進行認證。如果改為trust表示無條件地允許聯接。

ii>postgresql.conf（主配置文件）

所有配置信息在系統視圖pg_settings中可查看，通過context可知修改相關配置后是否需要重啟。

internal 只讀參數，初始化實例時寫死的。postmaster 改變后需要重啟。sighup/backend 不需要重啟，需要向postmaster主進程發送SIGHUP信號，讓其重啟裝配配置新的參數。運行pg_ctl reload命令重新裝配。superuser 超級用戶使用set命令改變。user 普通用戶使用set命令改變。

## 連接配置項listen_addresses = "*"          # 默認為localhost，這會導致遠程主機無法登錄數據庫。寫具體網絡ip表示讓特定機器登錄，*表示所有地址都可監聽。port = 5432                             # pg默認端口為5432。多個pg實例可以設置不同端口。max_connections         #允許數據庫連接的最大并發連接數，默認100，修改后需要重啟。通過sql：show max_connections;也可以查看superuser_reserved_connections  #超級用戶連接數。默認為3，為防止普通用戶消費連接數過多導致超級用戶無法連接pg。## 日志配置項logging_collector = on    #開啟日志收集。pgSQL10已經默認開啟log_directory = "log"     #日志目錄。日志切換與覆蓋有多種方案，可配。一是每天生成一個新的日志文件；二是日志寫滿到一定大小開啟新文件；三是只保留最近7天日志，循環覆蓋（pgSQL10默認模式)。## 內存配置項shared_buffers = 4096MB                 # min 128kB  共享內存大小，主要用于共享數據塊。默認值為32MB，盡量設置大一些。具體說明見后文講解。work_mem = 4MB                         # min 64kB 單個SQL執行、排序、Hash Join時使用的內存，執行完畢后釋放。設置大一些會提高排序操作效率。具體說明見后文講解。max_stack_depth    #服務器執行堆棧的最大安全深度，默認為2M。如果發現不能運行復雜的函數時，可以調高此配置，但一個正在運行的遞歸函數可能會導致pg后臺服務進程崩潰，慎重設置。

iii>其它文件

文件/目錄作用備注

PG_VERSIONpg版本號

postmaster.opts記錄服務器上次啟動的命令行參數

base默認表空間的目錄下面子目錄以對應數據庫的OID命名，對應OID子目錄下存放著這個數據庫的表、索引等數據文件。OID通過select oid,datname from pg_database;查詢。

global一些共享系統表的目錄

log程序日志目錄pg10版本之前未pg_log目錄

pg_commit_ts視圖提交的時間戳數據pg9.5之后

pg_walWAL日志的目錄，在pg10之前此目錄是pg_clog

2）psql命令行操作

# 數據庫連接： psql命令在postgresql/bin目錄下。# 添加參數-E可以在執行psql快捷命令時同時輸出對應sql。也可以通過命令\set ECHO_HIDDEN on|off控制psql "host=127.0.0.1 port=5432 user=postgres password=123456 dbname=postgres"psql -U postgres -d DB_NAME -h localhost -c "select * from user_info"# 數據導出-pg_dump命令pg_dump "host=XX.XX.XX.XX port=5432 user=XXXX password=XXXX dbname=XXXXX" -t table_name -f table_name.sql# 數據導出-psql命令psql "host=XX.XX.XX.XX port=5432  user=XXX password=XXX dbname=XXX" -f table_name.sql

常見psql快捷命令（通過psql連接數據庫后，通過“\”開頭的快捷命令進行數據庫相關操作，tab鍵可補全命令）：

說明命令備注

退出命令行模式\q

查看數據庫\l小寫L

切換數據庫\c dbName

查詢當前登錄的數據庫和用戶\cYou are now connected to database “postgres” as user “postgres”

查看sql語法（help）\h create user

查看更多命令?

查看所有表\d

查看結構\d namename可以包含通配符*或?，可以是表名、索引、視圖、序列、函數。如果使用\d+會顯示的更詳細

列出所有schema\dn

查看所有表空間\db

查看所有角色和用戶\du或\dgpg中用戶和角色是不區分的

查看表權限分配情況\dp或\z

查看執行時間\timing on sql語句

指定客戶端字符編碼\encoding gbkutf8

執行外部文件的sql命令\i fileName或 psql -x -f fileName

編輯器\e類似vi，退出vi后會執行其中輸入的內容

查看或編輯函數\ef 函數名不加函數名顯示函數模板。退出vi后可\reset來清除命令緩沖區數據，防止誤操作。

查看或編輯視圖\ev 視圖名不加函數名顯示視圖模板。退出vi后可\reset來清除命令緩沖區數據，防止誤操作。

3）查看版本信息

說明命令

查看客戶端版本psql --version

查看服務器版本詳細信息select version();

查看服務器版本信息show server_version;

查看服務器數字版本信息包括小版號SHOW server_version_num;

4）服務啟停及原理

1>服務啟動

直接運行postgres進程

/lwh/postgresql/bin/postgres -D /lwh/data/postgresql &   #-D指定數據目錄,&表示后臺執行。postgres也可以換成postmaster，一回事。如果權限不夠在命令前面添加：su postgres -c

使用pg_ctl命令啟動

/lwh/postgresql/bin/pg_ctl start -D /lwh/data/postgresql  #-D指定數據目錄

2>服務停止

直接向運行的postgres主進程發送signal信號

signal信號關機模式描述

SIGTERMSmart Shutdown 智能關機服務器將不允許新連接，等所有連接斷開才關閉數據庫

SIGINTFast Shutdown 快速關機不再允許新連接，并向所有子進程發送 SIGINT 信號，讓它們立刻退出，然后等待子進程退出后關閉數據庫

SIGQUITImmediate Shutdown 立即關閉立即關閉并退出，下次啟動時數據庫重放WAL日志進行恢復。僅用于緊急情況的關閉。

使用pg_ctl命令停止數據庫

#沒有權限需要在最前面添加su postgres -cpg_ctl stop -D dataDir -m smart  #對應Smart Shutdown 模式；fast對應 Fast Shutdown；immediate 對應 Immediate Shutdown.具體說明可通過--help查看。默認模式是哪個？誰知道?。?？？

3>服務檢測是否啟動

ps aux | grep /lwh/data/postgresql | grep -v grep | wc -l    #返回值不為0表示服務存在ps aux | grep /lwh/data/postgresql | grep -v grep | awk "{print $2}" #返回具體的pid表示服務存在或者：netstat -ntlp | grep 5432  #5432為pg默認端口

5）備份與還原

分為邏輯備份和物理備份。

1>pg_dump/pg_dumpall命令

pg_dumpall是將一個pg集群全部轉存到另一個腳本文件（sql腳本、歸檔文件）中，而pg_dump命令可以選擇一個數據庫或部份表進行備份。pg_dump結合pg_restore使用，能靈活備份和恢復。

2>冷備份

最簡單的物理備份就是冷備份，即：停止pg，然后拷貝pg的data目錄。

3>熱備份

即不停止數據庫進行備份，常見的方法有：PITR方法、使用文件系統或塊設備級別的快照功能完成備份。Linux下最簡單的備份方式是使用LVM的快照功能。

6）時間相關命令

說明命令備注

查看數據庫啟動時間select pg_postmaster_start_time()

查看最后load配置文件的時間select pg_conf_load_time()pg_ctl reload后改變這個時間

顯示當前數據庫時區show timezone時區不一樣的情況下，數據庫時間和操作系統時間不一致。;PRC： People’s Republic of China

查看當前時間select now()

設置時區set time zone ‘GMT’PRC為北京時區

查看所有時區的名字SELECT * FROM pg_timezone_names

7）其它常用命令

說明命令備注

當前連接數據庫select current_catalog, current_database();兩者結果一樣

查看pg是否正在做基礎備份select pg_is_in_backup(),pg_backup_start_time();

查看數據庫大小select pg_database_size("ngsoc"), pg_size_pretty(pg_database_size("ngsoc"));pg_size_pretty會轉換成MB\GB等格式展示

3，數據類型

類型轉換：

select "5"::int, "2014-09-09"::date;select int "5", date "2014-09-09";

常見操作符：

# + - * / %3 ^ 3  # 冪|/36.0 #平方根||/8.0 #立方根5!   或 !!5 #階乘，結果都是120@-5.0  #絕對值# & (and)  |(or)  #(XOR) ~(not) << >>

1）布爾（boolean）

值：true\false\NULL 表示方法很多，如下：

create table lwh(id int, a boolean, b boolean);insert into lwh values (1,true,faLSE);insert into lwh values (2,"true","false");insert into lwh values (3,"t","f");insert into lwh values (4,"y","no");insert into lwh values (5,"1","0");select * from lwh;

常用操作符：is、and、or、not

2）數值

1>int

smallint（int2）、int（int4）、bigint（int8）；

2>decimal（numeric，精確類型的小數）

decimal精度可達1000，適用于貨幣金額等要求精度的場合。 會影響運算速度。

3>float

real（非精確類型的浮點小數，float4）、double（非精確類型的浮點小數，float8）；

浮點數（float）特殊值：Infinity（正無窮大）、-Infinity、NaN（不是一個數字），sql操作：

update table set x="Infinity"   #不區分大小寫

另外兩個浮點數值做相等性比較時可能不符合預期。

4>money（8字節，貨幣類型）

完全保證精度，不同國家其輸出格式不同。

show lc_monetary;  #en_US.UTF-8輸出就是$1.22select "1.22" :: money;set lc_monetary = "zh_CN.UTF-8"; #通過這個修改，可輸出￥1.22

5>serial

serial（自動遞增整數，serial4）、bigserial（大的自動遞增整數，serial8）。 pg中的自增字段，通過序列（sequence）來實現的。

create table t (id serial);

等價于：

create sequence t_id_seq;create table t(  id integer not null default nextval("t_id_seq"));alter sequence t_id_seq owned by t.id;

3）字符（ varchar(n)、char(n)、text ）

varchar(n)最大存儲1GB，在mysql中最大是64kb; 長度為（2^n）-1。 （2^n）-1是好的磁盤或內存塊對齊，對齊塊更快。今天“塊”的大小更大，內存和磁盤足夠快，可以忽略對齊。char(n)：定長不足補空白，最大存儲1GB，存儲空間為4+n。text：變長。 在大多數數據庫中，定長char(n)有一定的性能優勢；但在pg中，定長變長無區別。建議使用varchar(n)、text。

1>常見操作符和函數

描述示例結果備注

字符串連接`‘Post’‘greSQL’`

字符串拼接select CONCAT(‘Post’,‘gre’,‘SQL’)PostgreSQL

換為小寫lower("TOm")tom做大小寫無關的比較時使用

轉換為大寫upper("TOm")TOM

字符串截取substring(str, 1,2)第二個參數為起始位置，第三個參數為截取長度（可不填）

字符index獲取position(",",str)

字符長度獲取length(str)

字符串反轉reverse(str)

4）二進制（bytea）

適用于存儲圖片等信息。

5）位串( bit(n)、bit varying(n) )

是一串由1和0組成的字符串。相比二進制類型，在位操作方面更方便。

bit(n），必須存儲n位。bit varying(n)，存儲最長為n位。

6）日期和時間（date、time、timestamp）

time、timestamp根據是否包括時區又分為兩種類型。interval 表示時間間隔。

pg中世紀可以精確到毫秒。

1>日期函數

--按照當前事務開始的時間返回數據。在整個事務中值不變。select current_time; --帶時區。current_date，current_time, current_timestamp, current_time,current_timestamp(precision) select localtime; -- 10:13:42.334327，不帶時區。localTimestamp, localTime(precision)...select now(); --2021-01-20 10:14:37.50845+08  包括時區，秒也保留到了6位小數 select now()::timestamp(0)without time zone; --2021-01-20 10:22:41select transaction_timestamp(); --當前事務開始的時間戳--返回實時時間，不受事務影響：statement_timestamp()  --當前語句開始時的時間戳clock_timestamp()  --實時時間戳，同一條sql中也可能不同timeofday()      --類似clock_timestamp，但返回值為text字符串select age(timestamp "2011-05-02", timestamp "1980-05-01") ;--31 years 1 dayselect age(timestamp "2011-05-02") ;--9 years 8 mons 18 days, 相當于age(current_date, timestamp "2011-05-02")

函數函數說明舉例備注

to_char日期轉為格式化字符串to_char(time,"YYYY-MM-DD hh24:mi:ss") as time1

to_date

to_timestamp

2>日期運算

比較：date、timestamp、long可以通過>=、between and來比較計算

WHEREcreate_time BETWEEN (CURRENT_TIMESTAMP - INTERVAL "3 hour"  --日期計算，可以是+或者-)AND CURRENT_TIMESTAMPselect date"2020-04-02" + integer"7"; --2020-04-09  dateselect date"2020-04-02" + interval"1 hour"; --2020-04-02 01:00:00  timestampselect date"2020-04-02" + time"3:00"; --2020-04-02 03:00:00 timestampselect time"3:00" + interval"1 hour"; --04:00:00   timeselect -interval"1 hour";  ---01:00:00  intervalselect date"2020-04-02" - date"2020-04-01";  --1--overlaps 兩個時間戳重疊為trueselect (date "2020-04-02", date "2021-04-02") overlaps (date "2020-06-02", date "2022-04-02");   --true

3>日期特殊值

字符串使用類型描述

epochdate, timestamp1970-01-01 00:00:00+00（UNIX系統零時）

infinitytimestamp時間戳最大值，比任何時間都晚

-infinitytimestamp時間戳min

nowdate, time, timestamp當前事務開始時間

todaydate, timestamp今日午夜

tomorrowdate, timestamp明日午夜

yesterdaydate, timestamp昨日午夜

7）枚舉

mysql也有枚舉類型，用法稍微不同。

create type week as enum ("sun","mon","wed");create table lwh_duty(person text, weekday week);insert into lwh_duty values ("mary","sun");insert into lwh_duty values ("mary","Sun"); --報錯，字符串不在枚舉類型內。區分大小寫。select * from lwh_duty where weekday = "Sun"; --報錯--查看枚舉類型的定義select * from pg_enum; --常見函數select enum_first("wed"::week);--sun，返回第一個枚舉類型。wed可以是任意枚舉值即可。同理還有enum_lastselect enum_range("wed"::week); --{sun,mon,wed},wed可以是任意枚舉值即可。以有序數組的形式返回所有枚舉類型--刪除枚舉 刪除之前要清除所有依賴drop type enum_lwh_test;--枚舉創建 表字段類型為枚舉名即可create type enum_lwh_test as enum ("confirmed","unconfirmed");--枚舉重命名 會一并修改相關依賴alter type enum_lwh_test rename to enum_lwh;--插入新值ALTER TYPE enum_lwh ADD VALUE "orange" AFTER "unconfirmed";

8）幾何（pg特有類型）

包括類型：點（point）、直線（line）、線段（lseg）、路徑（path）、多邊形（polygon）、圓（cycle）。

9）網絡地址（cidr、inet、macaddr，pg特有類型）

類型名稱存儲空間描述

cidr7或19字節ipv4或ipv6的網絡地址 （總是顯示掩碼）

inet7或19字節ipv4或ipv6的網絡地址或主機地址

macaddr6字節mac地址

--macaddr支持多種格式select "00e005664477"::macaddr;select "00E005:664477"::macaddr;--不區分大小寫select "00-E0-05-66-44-77"::macaddr;select "00E005-664477"::macaddr;select "00E0.0566.4477"::macaddr;--運算select inet"192.0.0.1" < inet "192.0.0.2";-- true。大于等于、不等于<>select inet"192.0.0.3" << inet "192.0.0.0/24";-- true。包含于<<、包含>>select ~inet"0.0.0.255"; --255.255.255.0 位非select inet"0.0.0.255" & inet"0.0.0.255"; --0.0.0.255  位與&，位或|select inet"192.0.0.50" + 1; --192.0.0.51  + -select inet"192.0.0.50" - inet"192.0.0.0"; --50--其它函數……host(inet) --取主機地址hostmask(inet) --主機掩碼地址netmask(inet) --子網掩碼

10）數組（pg特有類型）

數組的類型可以是數據庫內建的類型、用戶自定義的類型、枚舉或者組合類型。

1>定義

--數組可以定義長度、也可以不給。也可以定義多維數組。但定義長度和多維數組在實際使用中是無效的。如下面兩種定義是等價的。create table lwh01(id int, col1 int[], col2 text[][]);create table lwh01(id int, col1 int[10], col2 text[]);

2>插入

--插入--數組通過單引號+大括號來表示，具體分隔符通過以下sql來查詢，大部分情況下使用的是逗號分隔（box通過分號分隔，其它用逗號）。select typname,  typdelim from pg_type where typname in ("int4","box");insert into lwh01 values(1, "{1,2,3}", "{1,2,3}");insert into lwh01 values(2, "{{1,2,3},{1,2,3}}", NULL);--通過ARRAY關鍵字使用數組構造器也可以輸入數組insert into lwh01 values(3, ARRAY[1,2,3], ARRAY["1","2","3"]);--二維數組 array輸入insert into lwh01 values(4, ARRAY[[1,2],[2,3]], NULL);--數組下標輸入。默認從1開始，可以手動指定以0開始輸入。insert into lwh01 values(5, "[0:2]={1,2,3}", NUll);

3>查詢

--pg數組下標從1開始（也可以指定下標開始值）select id, col1, col1[0],col1[1],col1[2],col1[3],col1[4], col1[1][2], col1[1:2], col1[1][1:2] from lwh01;

4>常用操作符

操作操作符說明備注

等于= <>兩個數組維度、元素個數及值和順序完全一致為真

大于小于> <按BTree比較函數逐個元素比較

包含@>同理有<@，表示> ARRAY[1,2]結果t

重疊&&是否有共同元素?？煽缇暥扔嬎?。ARRAY[1,2,3] && ARRAY[3,4]結果為t

連接``

在jpa中，兩個數組取交集，即&&運算這樣使用：

select ids && "{1}" :: int[] from book_info;  --jpa中，sql的::

5>常用函數

功能| 操作 |備注 –|–|–|– 數組末尾追加 |array_append(ARRAY[2,3], 3); --{1,2,3}數組連接 | array_cat(ARRAY[2,3],ARRAY[2,3]); --{2,3,2,3}數組中移除特定值 | array_remove(ARRAY[2,3], 2); --{3}| 只支持一維數組 數組中替換某個值| array_replace(ARRAY[2,3],3,5);--{2,5}| 針對所有維度的數據 array2string | array_to_string(ARRAY[2,3],","); --2,3string2array| string_to_array("1,2,3", ","); --{1,2,3}

聚合函數array_agg：

select id, col3 from lwh01;--int, intidcol31122231425select  id, array_agg(col3) from lwh01 group by id; --int arrayidarray_agg2{2,3,5}1{1,4}

11）復合類型（用戶自定義類型）

類似于C的結構體，

12）xml類型

可以存儲xml類型，自動校驗是否合法。

13）json/jsonb

postgresql支持兩種json數據類型：json和jsonb。與text不同，會自動校驗格式是否合法。

1>概念

主要區別在于效率：json類型存儲快，使用慢；jsonb類型存儲稍慢，使用較快。 json是對輸入的完整拷貝，使用時再去解析，所以它會保留輸入的空格，重復鍵以及順序等。 jsonb是解析輸入后保存的二進制，它在解析時會刪除不必要的空格和重復的鍵，順序和輸入可能也不相同。使用時不用再次解析。

json類型和pg類型映射：

json類型pg類型備注

stringtext

numbernumericjson中沒有pg的“NAN”和“infinity”值

booleanbooleanjson僅支持小寫的true false

null（none）sql中NULL表示的意思不同

2>常用函數和操作符

json和jsonb的操作與函數基本通用。

①數組操作

json和jsonb通用：

--如果用操作符->>，取出數據會自動轉換為textselect "[{"a":"foo"},{"b":"bar"},{"c":"baz"}]"::json->2; --{"c":"baz"}    索引從0開始，從數組中取出index=2的對象select "["a", "b"]"::jsonb ?& array["a", "b"];  --true   array中是否包含某些鍵（top-level keys，這些鍵是一個數組）select "["a", "b"]"::jsonb || "["c", "d"]"::jsonb; --["a", "b", "c", "d"]    兩個數組求并集select "["a", "b"]"::jsonb || "["c", "d"]"::jsonb; --兩個數組求并集select (ARRAY[11] || (select type from field where label = "名稱") )select "["a", "b"]"::jsonb - 1;  --["a"]  數組去除某個索引select "["a", {"b":1}]"::jsonb #- "{1,b}";  --["a", {}]  數組去除某個路徑的鍵  --各種格式生成arrayselect json_build_array(1,2,"3",4,5);  --[1, 2, "3", 4, 5]    select array_to_json("{{1,5},{99,100}}"::int[]); --[[1,5],[99,100]] select json_array_length("[1,2,3,{"f1":1,"f2":[5,6]},4]"); --5   數組長度select * from json_array_elements("[1,true, [2,false]]");--取出數組中所有元素 json_array_elements_text返回text

②map操作

json和jsonb通用：

--如果用操作符->>，取出數據會自動轉換為textselect "{"a": {"b":"foo"}}"::json->"a";  --{"b":"foo"}   從map中取出key為a的值--如果用操作符#>>，取出數據會自動轉換為textselect "{"a": {"b":"foo"}}"::json #>"{a,b}";   --"foo"   map輸出特定路徑對象select "{"a":1, "b":2}"::jsonb @> "{"b":2}"::jsonb;  --true    map中是否包含某個鍵值對 同理還有<@select "{"a":1, "b":2}"::jsonb ? "b"; --true  map是否包含某個鍵（top-level keys）select "{"a":1, "b":2, "c":3}"::jsonb ?| array["b", "c"];  --true   map中是否包含某些鍵（這些鍵是一個數組）select "{"a": "b"}"::jsonb - "a";  --{}   map剔除某個鍵select "{"a": "b", "c": "d"}"::jsonb - "{a,c}"::text[];  --{}  map剔除某些鍵--各種格式生成mapselect row_to_json(row(1,"foo","1"));  --{"f1":1,"f2":"foo","f3":"1"}select json_build_object("foo",1,"bar",2);   --{"foo" : 1, "bar" : 2}   select json_object("{a, 1, b, "def", c, 3.5}");  --{"a" : "1", "b" : "def", "c" : "3.5"} select json_object("{{a, 1},{b, "def"},{c, 3.5}}");select json_object("{a, b}", "{1,2}"); --{"a" : "1", "b" : "2"}select * from json_each("{"a":"foo", "b":"bar"}"); --把jsonMap轉換為鍵值對  json_each_text返回text--通過鍵路徑獲取值 json_extract_path_text返回textselect json_extract_path("{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}","f4"); --{"f5":99,"f6":"foo"} select json_extract_path("{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}","f4","f6");--"foo"

③常用函數

select to_json("Fred said "Hi.""::text);  --"Fred said \"Hi.\""    string2json select json_typeof("-123.4"); --number --修改jsonb   jsonb_setjsonb_set("[{"f1":1,"f2":null},2,null,3]", "{0,f1}","[2,3,4]", false)-- attributes為jsonb類型字段（對象轉成的json）-- 第一個參數表示jsonb對象，第二個參數表示路徑text[],第三個參數表示value（jsonb對象），第四個值表示無此值是否新建（默認為true）原值：{"a":"1"}update user_test set attributes = jsonb_set(attributes,"{a}",""0""::jsonb, false) where id = "8888";執行后：{"a":"0"}

3>索引

json類型沒辦法直接建索引，但是可以建函數索引。 jsonb類型的列上可以建索引，GIN索引可以高效的從jsonb內部的key/value對中搜索數據。（BTree索引效率較低）

GIN索引創建方式：

--使用默認的操作符jsonb_ops創建GIN索引：--每個key和value都作為一個單獨的索引項--如：{“foo”：{“bar”：“baz”}}，會創建3個索引項：“foo” “bar” “baz”create index idx_name on table_name using gin(index_col);--使用jsonb_path_ops操作符建GIN索引：(推薦）--為每個value創建一個索引項--如：{“foo”：{“bar”：“baz”}}，會創建1個索引項：“foo” “bar” “baz”組合成一個Hash值作為索引項。因為索引相對較小，帶來性能的提升。create index idx_name on table_name using gin(index_col jsonb_path_ops);

14）range（范圍類型，pg特有類型）

主要用于范圍快速搜索。 直接通過庫里的開始值和結束值進行范圍搜索效率較低，而使用range類型，通過創建空間索引的方式來執行范圍搜索會大大提高效率。

15）對象標識符

16）偽類型

不能作為pg的字段，主要用于聲明函數的參數和結果類型。

17）其他類型

類型描述備注

UUID128字節的數字。pg核心庫沒有提供生成UUID的函數

pg_lsnpg9.4以上版本支持。用于表示LSN的數據類型，64位大整數。LSN表示WAL日志的位置

4，SQL語法

1）庫操作

創建、修改、刪除、使用與常規sql語法一致。

查詢當前數據庫：

select current_database();

查詢當前用戶：

select user;  或者：select current_user;

2）模式

創建、修改、刪除、使用與常規sql語法一致。 默認模式位public。

3）表操作

創建、修改、刪除、使用與常規sql語法一致。。

pg表也支持TOAST（跨頁存儲的大字段，需要將行外數據存儲到TOAST表）。

--獲取數據庫中所有表select * from pg_tables where schemaname="public";

1>更新插入一體

當主鍵或者unique key發生沖突時，什么都不做（同時也適用于多個字段的唯一性約束）：

INSERT INTO test.upsert_test(id, "name")VALUES(1, "m"),(2, "n"),(4, "c")ON conflict(id)  DO NOTHING;

upsert方法，沖突則更新，否則插入：

insert into "public"."user"(id, name)values(1,"a")ON conflict(id)DO UPDATE SET name = "b";

2>查詢

表查詢沒有指定查詢順序時，按插入順序進行排序。

--查詢一個表是否存在select count(*) from pg_class where relname = "tablename";--查詢所有的表SELECT * FROM pg_tables;--查詢所有的視圖SELECT * FROM pg_views;--查詢表結構SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a.atttypmod AS lengthvar,a.attnotnull AS notnull,b.description AS comment FROM pg_class c,pg_attribute a LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,pg_type t WHERE c.relname = "person_wechat_label" and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid ORDER BY a.attnum;--從數據庫中導出數據到文件\copy (select * from "user") to "/tmp/1.txt";

a）similar to（pg特色）

類似like的用法，在like的基礎上又增加了與POSIX正則表達式相同的模式匹配元字符。

select label FROM classify where label similar to "(36)+%" ;--檢索以36開頭或3636……開頭的數據select label FROM classify where label like "(36)+%" ; --檢索(36)+開頭的數據

元字符功能

()表示獨立邏輯項目

``

*重復前面的項0次或更多次

+重復前面的項一次或更多次

?重復前面的項0次或1次

{m}重復前面的項m次

{m, }重復前面的項m次或更多次

{m,n}重復前面的項至少m次，不超過n次

[…]

b)POSIX 正則表達式

pg中有兩種正則表達式：

區別SQL正則表達式POSIX 正則表達式

概念遵循sql語句中like、similar to語法腳本語言中的標準正則表達式

任意一個字符_.

任意個字符%.*

POSIX 正則表達式：

模式匹配操作符功能

~匹配正則表達式，區分大小寫

~*匹配正則表達式，不區分大小寫

!~不匹配正則表達式，區分大小寫

!~*-

--下面兩種語句功能一致：select "osdba" ~"a"; --t  select "osdba" similar to "%a%"; --tselect "osdba" ~"b|a";--t

c) substring函數（pg特色） 這個函數功能強大，可以使用正則表達式。

3>表繼承

表繼承是pg特有的。

create table persons_lwh(  name text,  age int,  sex boolean);create table students_lwh(  class_no int)INHERITS(persons_lwh);  --繼承。--父表的檢查約束、非空約束會被子表繼承，但唯一、主鍵、外鍵約束子表不會繼承。--一個子表可以繼承多個父表，多個相同字段必須類型一致，融合后在子表中只有一個字段。insert into students_lwh values("Mary",15,true,1); --向子表插入數據，父子表都有數據insert into persons_lwh values("Bob",15,true);--向父表插入數據，只有父表有數據select * from persons_lwh;select * from students_lwh;select * from only persons_lwh; --添加only，只獲取直接插入父表的數據

4>表空間

pg中的表空間就是為表一指定個存儲目錄，主要用于把表存放到不同的存儲目錄。

4）表分區

1>概念

表分區是邏輯上把大表分割成物理上幾塊，如按時區分、按類型分等。

優點

刪除更快。如按時間分區，刪除歷史數據直接刪除對應分區表。查詢更快。如按時間分區，較早時間的表幾乎很少查詢，各個分區表有各自的索引，使用頻率高的分區表的索引就可以完全緩存到內存中。修改更快。訪問具體分區表，而不是全表掃描。節省資源。根據訪問頻率把不常訪問的數據存儲到不同的物理介質上。

2>表繼承分區

注意：通過觸發器創建表，主表的唯一索引可以創建但是不起作用?。?！

--表繼承create table sales_detail(...sale_date...);--父表，不存數據create table sales_detail_y2014m01 (  check (sale_date >= DATE"2014-01-01" and sale < DATE"2014-02-01")  --check約束)INHERITS(sales_detail); --子表繼承父表--在各個分區表的分區鍵上建立索引create index sales_detail_y2014m01_sale_date on sales_detail_y2014m01 (sale_date);--建立規則或觸發器，把對主表的數據插入重定向到具體分表。create RULE sales_detail_insert_y2014m01 AS ON insert to sales_detail where   (sale_date >= DATE"2014-01-01" and sale < DATE"2014-02-01")DO INSTEAD   insett into sales_detail_y2014m01 values(NEW.*);

分表通過規則或觸發器優缺點對比：

相比觸發器，規則開銷很大。批量插入的情況下，規則效率更高。copy插入數據不會觸發規則，但會觸發觸發器。分表之外的數據，再次插入父表時，觸發器會報錯，但規則會把這些規則之外的數據插入主表。

3>聲明式分區

pg內部通過表繼承來實現分區表。pg10.x通過聲明式分區直接創建分區表，但其內部原理依然是表繼承。

相比表繼承分區，不需要在父表創建各種觸發器（降低維護成本），對父表的DML操作會自動路由到相應分區。

目前僅支持范圍分區和列表分區。

--范圍分區 --創建主表1. 創建全局主鍵必須攜帶分區字段2. 不允許全局創建索引，可以在分表上創建索引。3. 刪除主表不會刪除子表，通過級聯刪除可以一起刪除。4. 查詢主表時若在where條件中攜帶分區字段（如日期）,可直接去分區表檢索，提高檢索速度。注意：這個字段不包含運算，否則失效。create table sales_detail(  ...  sale_date date not null,  ...) PARTITION BY RANGE(sale_date); --通過PARTITION BY來支持分區。不能給沒有分區表的分區插入數據。--創建分區表（分區表需要手動進行創建，可以用定時任務一次創建多個）create table sales_detail_y2014m01 PARTITION OF sales_detail  --通過PARTITION OF指定分區表分區。FOR VALUES FROM ("2014-01-01") TO ("2014-02-01"); --查看所有的分區表select relname from pg_catalog.pg_class where relispartition = "t";

--列表分區create table test_list_part(id int, state boolean) PARTITION BY list(state);create table test_list_part_t partition of test_list_part for values in ("t");create table test_list_part_f partition of test_list_part for values in ("f"); insert into test_list_part values ("1","t");insert into test_list_part values ("1","f"); create index on test_list_part_f (state);create index on test_list_part_t (state);

④優化

1.打開約束排除（postgresql.conf中的constraint_exclusion設置為on）。 sql查詢中，where語句的過濾條件與分區表的check條件進行對比，直接跳過不需要掃描的分區表。 注意：分區字段的where語句如果包含計算，可能會掃描全表，需要解釋執行確認。

select count(*) from sales_detail where sale_date >= DATE"2014-12-01";--同各個分區表上的check條件進行對比，可知只需要掃描主表和分表sales_detail_y2014m12。

⑤主鍵

可能我的navicat版本太低了看不見主鍵，通過以下sql可以查詢到主鍵。

SELECTpg_attribute.attname AS colname,pg_type.typname AS typename,pg_constraint.conname AS pk_name FROMpg_constraintINNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oidINNER JOIN pg_attribute ON pg_attribute.attrelid = pg_class.oid AND pg_attribute.attnum = pg_constraint.conkey [ 2 ]INNER JOIN pg_type ON pg_type.oid = pg_attribute.atttypid WHEREpg_class.relname = "tbl_alarm_judge_conclusion" AND pg_constraint.contype = "p";--pg_constraint.contype : c = 檢查約束， f = 外鍵約束， p = 主鍵約束， u = 唯一約束 t = 約束觸發器 x = 排斥約束

5）序列

序列對象（也叫序列生成器）就是用CREATE SEQUENCE 創建的特殊的單行表。一個序列對象通常用于為行或者表生成唯一的標識符。通常用于表的主鍵自增。

mysql中的序列有以下兩個限制，但pg中的序列沒有。

自增長只能用于表中的一個字段；自增長只能被分配給固定表的固定的某一個字段，不能被多個表使用。

①創建

1> serial

將id類型設置為：serial，pg會自動創建一個序列，同時將列設置為INT，默認值設置為nextval(‘序列’)。serial8會將列設置為int8(long)

2> sequence（序列）

--[TEMPORARY | TEMP] 臨時序列，在會話結束時自動刪除；除非用模式修飾，否則同一個會話中同名的非臨時序列是不可見的。create [TEMPORARY | TEMP] sequence seq_name;  --nextval("seq_name") 表示遞增序列seq_name并返回新值。字符串會自動轉換為regclass類型--setval("seq_name", bigint) 設置序列當前數值create table test (id int default nextval("seq_name"), info text);

CREATE SEQUENCEIF NOT EXISTS PUBLIC .role_id_seq   --序列名    START WITH 1          --指定序列的起點，缺省初始值對于遞增序列為minvalue， 對于遞減序列為maxvalue。    INCREMENT BY 1    --遞增量，缺省為1；遞減為負數    NO MINVALUE         --指定序列的最小值， 如：minvalue 1    NO MAXVALUE        --指定序列的最大值，遞減序列最大值為-1    CACHE 1                 --為快速訪問而在內存里預先存儲多少個序列號，缺省值為1，表示一次只能生成一個值，也就是說沒有緩存。    OWNED BY {table.column | NONE}   --將序列關聯到一個特定的表字段上，在刪除該字段或其所在的表時將自動刪除綁定的序列；NONE表示無關聯。    CYCLE --默認不添加cycle選項，創建出來的序列滿時會報錯；添加cycle時，序列會從開始值重新開始。    ;

② 給已有的字段創建添加自增，并且自增值從最大的id+1開始

--將下一次的自增值設置成最大id+1select setval("user_info_id_seq",(select max(id)+1 from user_info));

③修改序列

alter sequence serialincrement by 3restart with 10;   --是一個可選選項，它改變序列的當前值 select nextval("serial");

④刪除序列

DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]--IF EXISTS     如果指定的序列不存在，那么發出一個 notice 而不是拋出一個錯誤。--CASCADE     級聯刪除依賴序列的對象。--RESTRICT    如果存在任何依賴的對象，則拒絕刪除序列。這個是缺省。drop sequence serial;

⑤序列和事務

事務回滾不會影響序列。

begin;select netval("seqtest01"); --3rollback;select currval("seqtest01"); --3

⑥cache

如果cache大于1，當序列被用于多會話時，每個會話在每次訪問序列對象的過程中都會分配并緩存隨后的序列值，會話結束時丟失沒有使用的數字，從而導致序列出現空洞。 多個會話的情況下，如果cache大于1，那么只能保證nextval值是唯一的，缺不按順序生成。比如cache=10，會話A保留了1……10并且返回nextval=1，會話B保留了11……20。會話A返回nextval=1之前會話B可能先返回nextval=11。

6）觸發器（Trigger）

觸發器是一種由事件自動觸發執行的特殊的存儲過程。主要用于加強數據的完整性約束和業務規則上的約束。 PostgreSQL的觸發器是數據庫自動執行\指定的數據庫事件發生時調用的回調函數。 注意：

如果有多個相同類型的觸發器定義了相同的事件，他們將被觸發名稱是按字母順序排列。

1>創建觸發器

語句觸發：（修改0行的操作依然會觸發。按語句觸發，而不管這條語句操作了多少行）

CREATE TRIGGER stu_trigger AFTER INSERT OR DELETE OR UPDATE ON stu_info   --AFTER 可以換成BEFORE，在語句執行前觸發。行級別的AFTER觸發器在任何語句級別的AFTER觸發器之前觸發。FOR STATEMENT EXECUTE PROCEDURE stu_trigger_function ();

針對字段觸發：

CREATE TRIGGER stu_trigger AFTER INSERT OR UPDATE OF birthday OR DELETE ON stu_info   FOR EACH ROW EXECUTE PROCEDURE stu_trigger_function (); --stu_trigger_function 為觸發函數

行觸發：(如果執行的語句沒有更新實際的行，那么不會+觸發)

CREATE TRIGGER example_trigger AFTER INSERT OR DELETE OR UPDATE ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

2>刪除

觸發器刪除不會刪除對應的觸發函數；但刪除表時，表上的觸發器會一并刪除。

DROP TRIGGER if exists stu_trigger ON stu_info [CASCADE | RESTRRICT]; --中括號中的可省略。CASCADE 級聯刪除依賴此觸發器的對象；RESTRRICT是默認值，有依賴對象存在就拒絕刪除。

3>觸發函數

觸發函數有返回值。 對于BEFORE和INSTEAD OF這類行級觸發器來說：

返回NULL，表示忽略當前行的操作。也不會觸發其它觸發器。返回非NULL：對于INSERT和UPDATE操作來說，返回的行將成為被插入的行或者將要更新的行。每個觸發器返回的行將成為下一個觸發器的輸入。 對于AFTER這類行級觸發器：返回值會被忽略。

觸發函數常用特殊變量：

NEW: 1. INSERT/UPDATE 行級觸發器中新的數據行，數據類型是RECORD；2. 語句級別觸發器、DELETE操作觸發器 中此變量未分配。OLD:3. UPDATE/DELETE 行級觸發器中原有的數據行，數據類型是RECORD；4. 語句級別觸發器、INSERT操作觸發器 中此變量未分配。TG_NAME： 觸發器名。數據類型：name。TG_WHEN： 是 BEFORE/AFTER 觸發器。TG_LEVEL：是 ROW/STATEMENT 觸發器。TG_OP：是 INSERT/UPDATE/DELETE/TRUNCATE 之一的字符串，表示DML語句類型。TG_TABLE_NAME：觸發器所在表的名稱。同理有TG_TABLE_SCHEMA（模式）。

CREATEOR REPLACE FUNCTION stu_trigger_function () RETURNS TRIGGER AS$BODY$DECLARE_birthday TIMESTAMP;_id int;BEGIN    IF TG_OP = "INSERT" THEN                SELECT birthday INTO _birthday FROM stu_info WHERE name= NEW .name AND class_id= NEW .class_id;        IF _birthday is NULL THEN            INSERT INTO stu_info ( ID, name, birthday , class_id) VALUES ( NEW . ID, NEW .name, NEW .first_occur_time, NEW .class_id) ;            ELSE IF NEW .birthday < _birthday THEN                UPDATE stu_info SET ID = NEW . ID, birthday = NEW .birthday ;            END IF ;        END IF ;        ELSE IF TG_OP = "DELETE" THEN            DELETE FROM stu_info A WHERE A . ID = OLD . ID ; RETURN OLD ;               ELSE IF TG_OP = "UPDATE" THEN                if UPDATE(birthday ) THEN                    SELECT ID,birthday INTO _id, _birthday FROM stu_info WHERE class_id= OLD .class_id AND name= OLD .name;                            IF ( _id != 0 AND _birthday > OLD .birthday ) THEN                        UPDATE stu_info SET ID = _id, birthday = _birthday ;                    END IF ;                END IF;            END IF ;        END IF ;    END IF ;    RETURN NEW;  --語句級觸發函數可以顯示的寫：RETURN NULLEND ;$BODY$LANGUAGE plpgsql;--刪除觸發函數drop FUNCTION add_stu_trigger()；

4>事件觸發器

pg9.3開始支持Event Trigger，主要用于彌補pg以前版本不支持DDL觸發器的不足。

7）函數

1>創建

CREATE [OR REPLACE] FUNCTION function_name (arguments)   RETURNS return_datatype AS $variable_name$    DECLARE      declaration;      [...]    BEGIN      < function_body >      [...]      RETURN { variable_name | value }    END; LANGUAGE plpgsql;

function_name:指定函數的名稱。 arguments: 函數參數 [OR REPLACE]:是可選的，它允許修改/替換現有函數。 DECLARE:定義參數(參數名寫在前面 類型寫在后面)。 BEGIN~END: 在中間寫方法主體。 RETURN:指定要從函數返回的數據類型(它可以是基礎，復合或域類型，或者也可以引用表列的類型)。 LANGUAGE:它指定實現該函數的語言的名稱。 可以是SQL，PL/pgSQL，C， Python等。

2>函數的參數說明

參數說明

IN可以將 IN 參數傳遞給函數，但無法從返回結果里再獲取到。

OUTOUT 參數經常用于一個函數需要返回多個值，所以不需要 RETURN 語句。

INOUTINOUT 參數是 IN 和 OUT 參數的組合。這意味著調用者可以將值傳遞給函數，函數然后改變參數并且將該值作為結果的一部分傳遞回去。

VARIADICPostgreSQL 函數可以接受可變數量的參數，其中一個條件是所有參數具有相同的數據類型。參數作為數組傳遞給函數。

參數使用例子：

CREATE OR REPLACE FUNCTION hi_lo( IN a NUMERIC,  IN b NUMERIC,     OUT c NUMERIC,      OUT hi NUMERIC,         INOUT lo NUMERIC)AS $$BEGIN c:= GREATEST(a,b); hi:= LEAST(a,b); lo:=GREATEST(a,b);END; $$ LANGUAGE plpgsql;

3>函數重載

與 Java 等編程語言相同，PostgreSQL 允許多個函數具有相同的名稱，只要參數不同即可。如果多個函數具有相同的名稱，那么我們說這些函數是重載的。當一個函數被調用時，PostgreSQL 根據輸入參數調用確切的函數。

4>塊結構

一個 PostgreSQL 函數由塊（block）進行組織。

[ DECLARE    聲明 ]  --可以沒有BEGIN    主體;   --必需 ...END;--聲明部分中的每個語句都以分號（;）結尾。 主體部分中的每個語句也以分號（;）結尾。

8）視圖

--獲取數據庫中所有viewselect * from pg_views where schemaname="public";

9）索引

10）用戶及權限管理

pg中角色和用戶沒區別。超級用戶為postgres。

11）事務、并發和鎖

保存點（savePoint）：在一個大的事務中，可以把操作過程分成幾個部分，第一個部分執行成功后可以建一個保存點，若后面的部分執行失敗，則回滾到此保存點，而不必回滾整個事務。咨詢鎖（Advisory Lock）：session級別和事務級別。pg數據庫提供一種與具體表數據無關的鎖，pg將變成一個鎖服務提供中心。主要場景：多個進程訪問同一個數據庫、分布式系統中類似Zookeeper的鎖服務。

12）規則（Rule）

規則系統更準確地說是查詢重寫規則系統，使用時也可以被函數和觸發器替代，但原理和使用場景不同（這在上文講分表的時候就提到過）。對于批量操作，規則比觸發器效率更高。

1>SELECT規則

create RULE "_RETURN" as on select to myview Do instead select * from mytable;#相當于create table myview (same column list as mytab);

2>更新規則

create [or replace] RULE name as on event  #event 值為SELECT/INSERT/UPDATE/DELETEto tableName [where condition] do [also | instead] {nothing | command}  # also表示執行原操作后還執行一些附加操作 如：also insert into mytab_log...; instead表示把原操作替換為后面的command操作

3>權限

規則從屬于表和視圖

5，執行計劃

explain sql語句；  #查看執行計劃。也可以使用navicat的解釋功能查看。

1）多表連接查詢

①Nest Loop Join（嵌套循環連接）

1>場景

適合兩個表的數據量都比較少的情況(最簡單的 table join 方式)。

外表為小表，且過濾后的數據量較少。內表的關聯列上有高效索引(主鍵或者唯一性索引)。

2>舉例

# 內表（t4)被外表(t5)驅動。外表返回的每一行都要在內表中檢索找到與它匹配的行，因此整個查詢返回的結果集不能太大（大于1 萬不適合）select t4.*,t5.*  from tmp_t4 t4,       tmp_t5 t5 where 1=1   and t4.id = t5.id   and t4.id = 1;

相當于for循環：

for(t4.data in tmp_t4)  { t5.data in tmp_t5 on t5.data = t4.data }

②Hash JOIN（哈希、散列連接）

1>場景

針對那些沒有索引或者其中任一個有索引的大表。 哈希連接只能應用于等值連接(如WHERE A.COL3 = B.COL4)、非等值連接(WHERE A.COL3 > B.COL4)、外連接(WHERE A.COL3 = B.COL4(+))。

2>操作步驟

優化器使用兩個表中較小的表（或數據源）利用連接鍵在內存中建立散列表，然后掃描較大的表并探測散列表，找出與散列表匹配的行。 這種方式適用于較小的表完全可以放于內存中的情況，這樣總成本就是訪問兩個表的成本之和。但是在表很大的情況下并不能完全放入內存，這時優化器會將它分割成若干不同的分區，不能放入內存的部分就把該分區寫入磁盤的臨時段，此時要有較大的臨時段從而盡量提高I/O 的性能。

③Sort Merge JOIN

1>場景

通常Hash JOIN的性能都優于Merge JOIN，對于那些連接列上有索引的表（已排好序）Merge JOIN性能會優于Hash JOIN。

2>操作步驟

mrege join的性能開銷幾乎都在前兩步。

對連接的每個表做全表掃描（table access full）;對table access full的結果進行排序。進行merge join對排序結果進行合并。

在全表掃描比索引范圍掃描再通過rowid進行表訪問更可取的情況下，merge join會比nested loops性能更佳。當表特別小或特別巨大的時候，實行全表訪問可能會比索引范圍掃描更有效。

6，數據庫優化

1）SQL優化

對于重復的代碼邏輯，sql執行速度遠遠大于代碼邏輯。 但是，由于sql難以測試、難以復用、難以加工變量，對于復雜的邏輯不建議用在sql中。代碼可以分成模塊、邏輯獨立、方便測試。

sql優化的思路有兩種：一是：

即去掉無用的步驟；二是優化算法，如讓sql走更優的執行計劃上。

①基于規則優化(RBO)和基于代價優化(CBO)

RBO和CBO是兩種數據庫引擎在執行sql語句時的優化策略。

總結： 基于規則的優化器更像是一個經驗豐富熟知各條路段的老司機，大部分情況可以根據自己的經驗來判斷走哪條路可以更快的到達目的地，而基于代價的優化更像手機里面的地圖，它可以選擇出許多不同的路徑根據實時的路況信息綜合考慮路程長度，交通狀況來挑出最優的路徑。

2）配置優化

pg中與內存有關的配置參數：

1>shared_buffers（共享緩存區）

i>工作原理

shared_buffers是一個8KB的數組，postgres在從磁盤中查詢數據前，會先查找shared_buffers的頁，如果命中，就直接返回，避免從磁盤查詢。 多個進程通過共享內存技術來共享緩存中的數據。

shared_buffers存儲什么？ 表數據； 索引，索引也存儲在8K塊中； 執行計劃，存儲基于會話的執行計劃，會話結束，緩存的計劃也就被丟棄。

什么時候加載shared_buffers？ 1）在訪問數據時，數據會先加載到os緩存，然后再加載到shared_buffers，這個加載過程可能是一些查詢，也可以使用pg_prewarm預熱緩存。 2）當然也可能同時存在os和shared_buffers兩份一樣的緩存（雙緩存）。 3）查找到的時候會先在shared_buffers查找是否有緩存，如果沒有再到os緩存查找，最后再從磁盤獲取。 4）os緩存使用簡單的LRU（移除最近最久未使用的緩存），而數據庫采用的優化的時鐘掃描，即緩存使用頻率高的會被保存，低的被移除。

ii>優化策略

提高shared_buffers，增加緩存命中率，提高查詢效率。 同時為了避免Double Buffering問題，將shared_buffers設置較小，更多的內存留給文件系統使用。

【Double Buffering（雙緩存）】問題： pg的數據文件都存儲在文件系統中，os的文件系統也有緩存，這導致pg的數據庫副本可能同時存在于共享內存和文件系統中，造成內存利用率低的問題。 Oracle中通過設置Birect I/O避免雙緩存問題，但pg不支持。 shared_buffers的大小不應該超過內存的1/4。

shared_buffers設置的合理范圍 1）windows服務器有用范圍是64MB到512MB，默認128MB 2）linux服務器建議設置為25%，亞馬遜服務器設置為75%（避免雙緩存，數據會存儲在os和shared_buffers兩份） os緩存的重要性：數據寫入時，從內存到磁盤，這個頁面就會被標記為臟頁，一旦被標記為臟頁，它就會被刷新到os緩存，然后寫入磁盤。所以如果os高速緩存大小較小，則它不能重新排序寫入并優化io，這對于繁重的寫入來說非常致命，因此os的緩存大小也非常重要。給予shared_buffers太大或太小都會損害性能。

shared_buffers調整策略

2> work_mem

為每個進程單獨分配的內存，主要用于group by, sort, hash agg, hash join 等操作。 注意：work_mem是每次分配的內存，加入有M個并發進程，每個進程有N個HASH操作，那么需要分配的內存為 MNwork_mem。因此work_mem不宜設置太大，通常保持默認的4MB即可，如果設置的太大超過256MB，很容易因為瞬間的大并發操作導致oom。

3>maintenance_work_mem

為每個進程單獨分配的內存，主要進行維護操作時需要的內存，如VACUUM、create index、ALTER TABLE ADD FOREIGN KEY等操作需要的內存。

4>autovacuum_work_mem

pg9.4版本新增參數。 9.4之后，AutoVacuum的worker進程分配的內存由參數autovacuum_work_mem控制，手動Vacuum時分配的內存由maintenance_work_mem 控制。9.4之前都用maintenance_work_mem 參數。 默認值為-1，表示與maintenance_work_mem 一樣。 vacuum 大小 = autovacuum_max_workers * autovacuum_work_mem

5>temp_buffers（臨時表緩存）

為每個不同的進程單獨分配的內存，不在共享內存中，默認為8MB。

6>wal_buffers（WAL日志緩存大?。?/p>

默認為-1，表示根據shared_buffer的大小自動設置。

7>huge_pages（是否使用大頁）

默認值為try，表示盡量使用大頁。若os未開啟大頁，不使用大頁內存，不影響數據庫正常使用。

8>effective_cache_size（sql執行中的實際磁盤緩存）

與具體內存分配無關

3）sql審計

相關配置：

參數調整說明

log_min_duration_statementsql審計記錄的標準，超過該時長的sql將被記錄到日志文件。默認為-1，不記錄超時sql。

log_statementnone默認，不記錄；all-記錄所有語句；ddl-記錄所有數據定義語句；mod記錄所有ddl和數據修改語句；

log_min_error_statement控制日志中是否記錄導致數據庫出現錯誤的SQL語句。默認為error

4）排查sql

-- 查看表結構 SELECT column_name,data_type FROM information_schema.columns WHERE table_name = "表名"; -- 展示在數據庫中當前正在執行多少查詢 SELECT datname, count(*) AS open, count(*) FILTER (WHERE state = "active") AS active, count(*) FILTER (WHERE state = "idle") AS idle, count(*) FILTER (WHERE state = "idle in transaction") AS idle_in_trans FROM pg_stat_activity GROUP BY ROLLUP(1) -- 查看事務已經打開了多久SELECT pid, xact_start, now() - xact_start AS duration FROM pg_stat_activity WHERE state LIKE "%transaction%" ORDER BY 3 DESC; -- 檢查是否有長查詢運行 SELECT now() - query_start AS duration, datname, query FROM pg_stat_activity WHERE state = "active" ORDER BY 1 DESC; -- 查看慢查詢日志是否開啟 SHOW log_min_duration_statement; -- 設置慢查詢日志 ALTER DATABASE test SET log_min_duration_statement TO 10000; -- 查找經常被掃描的大型表 SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan, seq_tup_read / seq_scan AS avg FROM pg_stat_user_tables WHERE seq_scan > 0 ORDER BY seq_tup_read DESC LIMIT 20; -- 跟蹤 vacuum 進度 SELECT * FROM pg_stat_progress_vacuum ;

7，內部機制與原理

1）進程架構模型

啟動pg，主進程為Postmaster（pg的bin目錄下，是一個指向Postgres的鏈接）。Postmaster是整個數據庫實例的總控進程，負責啟動和關閉數據庫實例，同時fork出一些與數據庫實例相關的輔助進程，并對其進行管理。

輔助進程作用配置

Logger系統日志參數logging_collect設置為on時啟動該輔助進程

每次客戶端與數據庫建立連接時，pg數據庫都會啟動一個服務進程來為該連接服務，故而是進程架構模型，而MySQL是線程架構模型。當某個服務進程報錯時，Postmaster主進程會自動完成系統恢復，恢復過程中停掉所有的服務進程，然后進行數據的一致性恢復，恢復完成后數據庫才能接受新的連接。

--查詢服務進程PID: 通過count(*)獲取當前連接數select pid,usename,client_addr,client_port from pg_stat_activity;

2）autovacuum進程

autovacuum 是 postgresql 里非常重要的一個服務端進程，能夠自動運行，在一定條件下自動觸發對 dead tuples 進行清理并對表進行分析。 在pg中更新、刪除行后，數據行并不會馬上從數據塊中清理掉，而是需要等VACUUM時時清理。為了加快VACUUM速度并降低對系統I/O性能的影響，pg8.4.1之后為每個數據塊文件加了一個后綴為“_vm”的文件（可見性映射表文件，VM文件）。這個文件為每個數據塊存儲了一個標志位，標記數據塊中是否存在要清理的tuple。

VACUUM有兩種方式：

Lazy VACUUM ：使用VM文件，掃描部分數據塊。Full VACUUM ：全量掃描數據塊。

vacuum相關的配置：

參數名說明優化思路

autovacuum默認為on，表示是否開起autovacuum。當需要凍結xid時，盡管此值為off，PG也會進行vacuum。

autovacuum_naptime下一次vacuum的時間，默認1min通過縮短實際，調整回收頻率，減少每次回收量，可以減小wal壓力

log_autovacuum_min_duration向日志打印autovacuum的統計信息（以及資源消耗），大于閾值，輸出這次autovacuum觸發的事件的統計信息 。 “-1”表示不記錄。“0”表示每次都記錄。

autovacuum_max_workers最大同時運行的worker數量，不包含launcher本身。CPU核多、IO優秀時，當DELETE\UPDATE非常頻繁時適量調多點。注意最多可能消耗這么多內存： # autovacuum_max_workers * autovacuum mem(autovacuum_work_mem)

autovacuum_vacuum_threshold默認50。與autovacuum_vacuum_scale_factor（默認值為20%）配合使用。當update,delete的tuples數量超過autovacuum_vacuum_scale_factor*table_size+autovacuum_vacuum_threshold時，進行vacuum。改小可以降低vacuum觸發條件，提高vacuum頻率

autovacuum_analyze_threshold默認50。與autovacuum_analyze_scale_factor（默認10%）配合使用。當update,insert,delete的tuples數量超過autovacuum_analyze_scale_factor*table_size+autovacuum_analyze_threshold時，進行analyze。改小可以降低vacuum觸發條件，提高vacuum頻率

autovacuum_freeze_max_age和autovacuum_multixact_freeze_max_age前者200 million，后者400 million。離下一次進行xid凍結的最大事務數。 如果表的事務ID年齡大于該值, 即使未開啟autovacuum也會強制觸發FREEZE，并告警Preventing Transaction ID Wraparound Failures。設置較大值，減少因事務id消耗造成全表掃描的頻率。（1000million、1200million）

autovacuum_vacuum_cost_delay如果為-1，取vacuum_cost_delay值。autovacuum觸發的vacuum、freeze、analyze的平滑化調度。設置過大，會導致AUTOVACUUM launcher觸發的vacuum耗時過長。特別是大表，耗時會非常長，可能導致膨脹等問題?？梢哉{小一點，0.

autovacuum_vacuum_cost_limit如果為-1，到vacuum_cost_limit的值，這個值是所有worker的累加值。

vacuum_freeze_table_age當表的年齡大于vacuum_freeze_table_age，則自動轉換成vacuum freeze調高可以降低vacuum freeze的頻率

vacuum_multixact_freeze_table_age當表的年齡大于autovacuum_freeze_max_age，也會強制觸發vacuum freeze調高可以降低vacuum freeze的頻率

如果開啟了autovacuum，當垃圾記錄數大于 autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor*reltuples ，autovacuum launcher觸發普通的vacuum。 當表的年齡大于vacuum_freeze_table_age，則自動轉換成vacuum freeze。如果開啟了autovacuum，當新增記錄數大于autovacuum_analyze_threshold + autovacuum_analyze_scale_factor*reltuples，autovacuum launcher觸發analyze。即使沒有開啟autovacuum，當表的年齡大于autovacuum_freeze_max_age，也會強制觸發vacuum freeze。

3）pg物理存儲結構

①數據塊結構

數據塊大小默認8KB，最大為32KB，一個數據塊中存儲了多行數據。

塊頭 包括： a. 塊的checksum值； b. 空閑空間的起始位置和結束位置； c. 特殊數據的起始位置； d. 其它信息。行指針：向后順序排列。是一個32bit的數字，具體結構： a. 行內容偏移量，15bit；能表示的最大偏移量是215，因此pg中塊最大為32kb b. 指針的標記，2bit； c. 行內容的長度，15bit。行內容：從塊尾向前反向排列?？臻e空間：行數據指針和行數據內容之間的空間。

②Tuple結構（數據行）

8，多版本并發控制（Multi-Version Concurrency Control，MVCC）

MVCC是數據庫中并發訪問數據時保證數據一致性的一種方法。實現MVCC的方法有以下兩種：

寫新數據時，把原數據移到一個單獨的位置，如回滾段中，其它用戶讀數據時，從回滾段中把原數據讀出來。（Oracle和Mysql數據庫中的InnoDB引擎使用這種方法）寫新數據時，原數據不刪除，而是把新數據插入進來。（pg使用這種方法）

1）pg MVCC的優缺點

pg在事務提交前，只需要訪問原來的數據；提交后，系統更新元組的存儲標識，直到Vaccum進程回收為止。

相比InnoDB和Oracle，pg多版本優勢在于： ① 事務回滾可以立即完成； ② 數據可以進行很多更新，不必像Oracle和InnoDB那樣需要經常保證回滾段不會被用完，也不會像Oracle數據庫那樣，經常遇到ORA-1555錯誤的困擾。

劣勢在于： ① 舊數據需要Vaccum清理。 ② 舊版本數據的存在降低查詢速率，需要掃描更多的數據塊。

2）事務ID及回卷

1>xmin 表系統字段

插入該行版本的事務ID。

2> xman 表系統字段

刪除此行時的事務ID。 第一次插入時，xman=0。如果xman>0，說明刪除事務未提交或者被回滾了。

當兩個事務同時訪問記錄時，通過xmin和xman的標記判斷記錄的版本，根據版本號與自己當前事務標識比較，確定數據權限。

3> cmin 表系統字段

事務內部的插入類操作的命令ID，此標識是從0開始。

4> xman 表系統字段

事務內部的刪除類操作的命令ID。如果不是刪除命令， xman = 0。

5>事務ID：xid

32bit的數字，從3開始，連續遞增。0 1 2是系統預留ID，這三個ID比任何普通xid都要舊： InvalidTransactionId=0：表示是無效的事務ID；表示還未分配事務ID。 BootstrapTransactionId=1：表示系統表初始化時的事務ID；表示Initdb服務正在初始化系統表。 FrozenTransactionId=2：凍結的事務ID。

6>回卷

xid一直遞增達到2^32為最大值，然后繼續從頭開始，以前的xid比新的xid大，導致比較新舊事務空難，即事務回卷問題。 為了確保事務的可見性需要頻繁判斷事務之間的新舊關系，如：數據行中已提交的事務比當前事務更早，則這條數據行對當前事務可見。

事務ID的比較新舊的公式如下：diff = (int32) (id1 - id2); 是一個有符號的int32。

事務ID的比較代碼：

/*     * TransactionIdPrecedes --- is id1 logically < id2?     */    bool TransactionIdPrecedes(TransactionId id1, TransactionId id2) // 結果返回一個bool值    {/*     * If either ID is a permanent XID then we can just do unsigned     * comparison.  If both are normal, do a modulo-2^32 comparison.     */    int32diff;         if (!TransactionIdIsNormal(id1) || !TransactionIdIsNormal(id2)) //若其中一個是特殊id即id為0 1 2，則另一個id一定較新（較大）        /*驗證：        *1）id1=10, id2=2   那么return false，說明id1更新。即普通事務更新        *2）id1=2, id2=10   那么return true，說明id2更新。即普通事務更新        */    return (id1 < id2);  //兩個普通id的比較，轉換為int32帶符號，第一位為符號位。/*驗證：     * id1=2^31+101, id2=100,   id1 - id2= 2^31+1  diff=-1 (大于2^31為負數），return true，id2更新。（事務回卷）* 那么如果id2確實是很早很早之前的舊事務怎么辦呢？根據pg規定的freeze原則，如果id2是很早很早的事務，兩事務年齡差又大于2^31，那么id2進行事務凍結。按之前的if邏輯，id1更新。*/    diff = (int32) (id1 - id2);    return (diff < 0);    }

7>事務凍結（freeze）

pg規定，最早和最新兩個事務之間年齡差對多為231。 當超過231時，就把舊的事務換成一個FrozenTransactionId=2的特殊事務，當正常事務ID與凍結事務ID比較時，會認為正常xid比FrozenTransactionId更新。即：xid空間雖然有232，但被一分為二，對某個特定的xid，其后231個xid屬于未來，均不可見；其前231個xid屬于過去，可見。

i>freeze實現：

pg9.4之前freeze方法： 直接將符合條件的元組的t_xmin設置為2，回收原來的xid。但這樣實現的問題是：一）當前可見的數據頁需要全部掃描，帶來大量的IO掃描；二）符合條件的元組需要更新xmin，造成大量臟頁，帶來大量IO pg9.4后對freeze優化： 不直接修改t_xmin，而是：一）只更新元組頭結點的t_infomask為HEAP_XMIN_FROZEN，表示該元組已經被凍結過（frozen）；二）有些插入操作，也可以直接將記錄置為frozen，例如大批量的COPY數據，insert into等；三）如果整個page所有記錄已經frozen，則在vm文件中標記為FROZEN，凍結清理會跳過該頁，減少了IO掃描。

ii>freeze優化：

freeze是被動觸發的，可以調節pg的一些參數優化freeze，更多時候提倡用戶進行主動預測需要freeze的時機，選擇合適的時間（比如pg負載較低的時間）主動執行vacuum freeze命令。目前已經有很多實現好的開源PostgreSQL vacuum freeze監控管理工具，比如flexible-freeze，能夠：確定數據庫的高峰和低峰期；在數據庫低峰期創建一個cron job執行flexible_freeze.py；flexible_freeze.py會自動對具有最老XID的表進行vacuum freeze。

9，Standby數據庫

1）概念

1>主備數據庫

高可用：主數據庫（Primary db，Master db）失敗后，備數據庫（Standby db）快速提升為Master db并提供服務。 高可靠性：Master bd和Standby db存在數據同步，從而提高數據的可靠性。通常是一臺master db提供讀寫，然后把數據同步到量一臺standby db（只讀）。 Hot standby db：同步數據時可以提供只讀服務的standby db（pg9.0提供）。反之為Warm standby db。

2>WAL日志文件

pg在數據目錄的pg_wal子目錄（10版本之前是pg_xlog目錄）中始終維護一個WAL文件。 wal文件記錄了db數據文件的每次改變，最初設計是為了db異常崩潰后，能夠重放最后一次checkpoint之后的日志文件，把數據塊推到最終的一致狀態，避免數據丟失和不一致。 WAL文件的存在可以讓備份數據塊存在不一致的數據，通過重放WAL日志文件加以糾正，推到任意一個時間點，即基于時間點備份（PITR，Point-in-Time Recovery）。 對于standby db，只要應用wal日志足夠快，就能保持與Master db一致的數據。把WAL日志傳送到另一臺機器的方法有兩種：

通過WAL歸檔日志方法； standby db會落后master db一個wal文件。具體落后多長時間取決于master db生成一個wal文件的時間。流復制。（pg9.x版本之后） 有兩種方式，同步方式，幾乎沒有延時。異步方式，落后時間取決于網絡延遲和standby的i/o能力。

主備同步wal相關配置：

參數名說明優化方案

synchronous_commit如果雙節點，設置為ON，如果是多副本，同步模式，建議設置為remote_write。如果磁盤性能很差，并且是OLTP業務。可以考慮設置為off降低COMMIT的RT，提高吞吐(設置為OFF時，可能丟失部分XLOG RECORD)

full_page_writes如果文件系統支持COW例如ZFS，則建議設置為OFF。 如果文件系統可以保證datafile block size的原子寫，在對齊后也可以設置為OFF。

wal_writer_delaywal寫的延遲縮短延遲，加快wal寫的速度

wal_writer_flush_after

checkpoint_timeout不建議頻繁做檢查點，否則XLOG會產生很多的FULL PAGE WRITE(when full_page_writes=on)提高該值，降低FULL PAGE WRITE，較少XLOG，降低wal壓力

max_wal_size建議等于SHARED BUFFER，或2倍。

min_wal_size建議是SHARED BUFFER的2分之一

wal_receiver_status_interval反饋給主節點自己已經接受（ replies ）到數據信息。減小，加快反饋速度

wal_buffers基于shared_buffers，shared_buffers/32增大，加快wal落盤速度

10，好用工具

1）PgBouncer

為pg提供的一個輕量級連接池工具。

2）Slony-I

基于觸發器的兩個pg的邏輯同步。

3）Bucardo

雙向同步工具，可以實現pg的雙（多）master方案。

4）pgpool-II

pg和客戶端之間的中間件。

11，Postgres-XC

Postgres-XC是基于pg實現的真正的數據水平拆分的分布式數據庫。