1，概念

2，安裝配置與常見命令

【資料圖】

1）安裝與配置

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

1>安裝目錄

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

2>數(shù)據(jù)目錄

/var/lib/pgsql//data。

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

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

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表示無條件地允許聯(lián)接。

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

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

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

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

iii>其它文件

文件/目錄作用備注

PG_VERSIONpg版本號

postmaster.opts記錄服務(wù)器上次啟動的命令行參數(shù)

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

global一些共享系統(tǒng)表的目錄

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

pg_commit_ts視圖提交的時間戳數(shù)據(jù)pg9.5之后

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

2）psql命令行操作

# 數(shù)據(jù)庫連接： psql命令在postgresql/bin目錄下。# 添加參數(shù)-E可以在執(zhí)行psql快捷命令時同時輸出對應(yīng)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"# 數(shù)據(jù)導(dǎo)出-pg_dump命令pg_dump "host=XX.XX.XX.XX port=5432 user=XXXX password=XXXX dbname=XXXXX" -t table_name -f table_name.sql# 數(shù)據(jù)導(dǎo)出-psql命令psql "host=XX.XX.XX.XX port=5432  user=XXX password=XXX dbname=XXX" -f table_name.sql

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

說明命令備注

退出命令行模式\q

查看數(shù)據(jù)庫\l小寫L

切換數(shù)據(jù)庫\c dbName

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

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

查看更多命令?

查看所有表\d

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

列出所有schema\dn

查看所有表空間\db

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

查看表權(quán)限分配情況\dp或\z

查看執(zhí)行時間\timing on sql語句

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

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

編輯器\e類似vi，退出vi后會執(zhí)行其中輸入的內(nèi)容

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

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

3）查看版本信息

說明命令

查看客戶端版本psql --version

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

查看服務(wù)器版本信息show server_version;

查看服務(wù)器數(shù)字版本信息包括小版號SHOW server_version_num;

4）服務(wù)啟停及原理

1>服務(wù)啟動

直接運行postgres進程

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

使用pg_ctl命令啟動

/lwh/postgresql/bin/pg_ctl start -D /lwh/data/postgresql  #-D指定數(shù)據(jù)目錄

2>服務(wù)停止

直接向運行的postgres主進程發(fā)送signal信號

signal信號關(guān)機模式描述

SIGTERMSmart Shutdown 智能關(guān)機服務(wù)器將不允許新連接，等所有連接斷開才關(guān)閉數(shù)據(jù)庫

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

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

使用pg_ctl命令停止數(shù)據(jù)庫

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

3>服務(wù)檢測是否啟動

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

5）備份與還原

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

1>pg_dump/pg_dumpall命令

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

2>冷備份

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

3>熱備份

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

6）時間相關(guān)命令

說明命令備注

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

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

顯示當前數(shù)據(jù)庫時區(qū)show timezone時區(qū)不一樣的情況下，數(shù)據(jù)庫時間和操作系統(tǒng)時間不一致。;PRC： People’s Republic of China

查看當前時間select now()

設(shè)置時區(qū)set time zone ‘GMT’PRC為北京時區(qū)

查看所有時區(qū)的名字SELECT * FROM pg_timezone_names

7）其它常用命令

說明命令備注

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

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

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

3，數(shù)據(jù)類型

類型轉(zhuǎn)換：

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

常見操作符：

# + - * / %3 ^ 3  # 冪|/36.0 #平方根||/8.0 #立方根5!   或 !!5 #階乘，結(jié)果都是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）數(shù)值

1>int

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

2>decimal（numeric，精確類型的小數(shù)）

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

3>float

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

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

update table set x="Infinity"   #不區(qū)分大小寫

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

4>money（8字節(jié)，貨幣類型）

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

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

5>serial

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

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是好的磁盤或內(nèi)存塊對齊，對齊塊更快。今天“塊”的大小更大，內(nèi)存和磁盤足夠快，可以忽略對齊。char(n)：定長不足補空白，最大存儲1GB，存儲空間為4+n。text：變長。 在大多數(shù)數(shù)據(jù)庫中，定長char(n)有一定的性能優(yōu)勢；但在pg中，定長變長無區(qū)別。建議使用varchar(n)、text。

1>常見操作符和函數(shù)

描述示例結(jié)果備注

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

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

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

轉(zhuǎn)換為大寫upper("TOm")TOM

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

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

字符長度獲取length(str)

字符串反轉(zhuǎn)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根據(jù)是否包括時區(qū)又分為兩種類型。interval 表示時間間隔。

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

1>日期函數(shù)

--按照當前事務(wù)開始的時間返回數(shù)據(jù)。在整個事務(wù)中值不變。select current_time; --帶時區(qū)。current_date，current_time, current_timestamp, current_time,current_timestamp(precision) select localtime; -- 10:13:42.334327，不帶時區(qū)。localTimestamp, localTime(precision)...select now(); --2021-01-20 10:14:37.50845+08  包括時區(qū)，秒也保留到了6位小數(shù) select now()::timestamp(0)without time zone; --2021-01-20 10:22:41select transaction_timestamp(); --當前事務(wù)開始的時間戳--返回實時時間，不受事務(wù)影響：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")

函數(shù)函數(shù)說明舉例備注

to_char日期轉(zhuǎn)為格式化字符串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系統(tǒng)零時）

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

-infinitytimestamp時間戳min

nowdate, time, timestamp當前事務(wù)開始時間

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"); --報錯，字符串不在枚舉類型內(nèi)。區(qū)分大小寫。select * from lwh_duty where weekday = "Sun"; --報錯--查看枚舉類型的定義select * from pg_enum; --常見函數(shù)select enum_first("wed"::week);--sun，返回第一個枚舉類型。wed可以是任意枚舉值即可。同理還有enum_lastselect enum_range("wed"::week); --{sun,mon,wed},wed可以是任意枚舉值即可。以有序數(shù)組的形式返回所有枚舉類型--刪除枚舉 刪除之前要清除所有依賴drop type enum_lwh_test;--枚舉創(chuàng)建 表字段類型為枚舉名即可create type enum_lwh_test as enum ("confirmed","unconfirmed");--枚舉重命名 會一并修改相關(guān)依賴alter type enum_lwh_test rename to enum_lwh;--插入新值A(chǔ)LTER TYPE enum_lwh ADD VALUE "orange" AFTER "unconfirmed";

8）幾何（pg特有類型）

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

9）網(wǎng)絡(luò)地址（cidr、inet、macaddr，pg特有類型）

類型名稱存儲空間描述

cidr7或19字節(jié)ipv4或ipv6的網(wǎng)絡(luò)地址 （總是顯示掩碼）

inet7或19字節(jié)ipv4或ipv6的網(wǎng)絡(luò)地址或主機地址

macaddr6字節(jié)mac地址

--macaddr支持多種格式select "00e005664477"::macaddr;select "00E005:664477"::macaddr;--不區(qū)分大小寫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--其它函數(shù)……h(huán)ost(inet) --取主機地址hostmask(inet) --主機掩碼地址netmask(inet) --子網(wǎng)掩碼

10）數(shù)組（pg特有類型）

數(shù)組的類型可以是數(shù)據(jù)庫內(nèi)建的類型、用戶自定義的類型、枚舉或者組合類型。

1>定義

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

2>插入

--插入--數(shù)組通過單引號+大括號來表示，具體分隔符通過以下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關(guān)鍵字使用數(shù)組構(gòu)造器也可以輸入數(shù)組insert into lwh01 values(3, ARRAY[1,2,3], ARRAY["1","2","3"]);--二維數(shù)組 array輸入insert into lwh01 values(4, ARRAY[[1,2],[2,3]], NULL);--數(shù)組下標輸入。默認從1開始，可以手動指定以0開始輸入。insert into lwh01 values(5, "[0:2]={1,2,3}", NUll);

3>查詢

--pg數(shù)組下標從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>常用操作符

操作操作符說明備注

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

大于小于> <按BTree比較函數(shù)逐個元素比較

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

重疊&&是否有共同元素?？煽缇暥扔嬎恪RRAY[1,2,3] && ARRAY[3,4]結(jié)果為t

連接``

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

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

5>常用函數(shù)

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

聚合函數(shù)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）復(fù)合類型（用戶自定義類型）

類似于C的結(jié)構(gòu)體，

12）xml類型

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

13）json/jsonb

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

1>概念

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

json類型和pg類型映射：

json類型pg類型備注

stringtext

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

booleanbooleanjson僅支持小寫的true false

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

2>常用函數(shù)和操作符

json和jsonb的操作與函數(shù)基本通用。

①數(shù)組操作

json和jsonb通用：

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

②map操作

json和jsonb通用：

--如果用操作符->>，取出數(shù)據(jù)會自動轉(zhuǎn)換為textselect "{"a": {"b":"foo"}}"::json->"a";  --{"b":"foo"}   從map中取出key為a的值--如果用操作符#>>，取出數(shù)據(jù)會自動轉(zhuǎn)換為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中是否包含某些鍵（這些鍵是一個數(shù)組）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轉(zhuǎn)換為鍵值對  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"

③常用函數(shù)

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類型字段（對象轉(zhuǎn)成的json）-- 第一個參數(shù)表示jsonb對象，第二個參數(shù)表示路徑text[],第三個參數(shù)表示value（jsonb對象），第四個值表示無此值是否新建（默認為true）原值：{"a":"1"}update user_test set attributes = jsonb_set(attributes,"{a}",""0""::jsonb, false) where id = "8888";執(zhí)行后：{"a":"0"}

3>索引

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

GIN索引創(chuàng)建方式：

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

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

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

15）對象標識符

16）偽類型

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

17）其他類型

類型描述備注

UUID128字節(jié)的數(shù)字。pg核心庫沒有提供生成UUID的函數(shù)

pg_lsnpg9.4以上版本支持。用于表示LSN的數(shù)據(jù)類型，64位大整數(shù)。LSN表示W(wǎng)AL日志的位置

4，SQL語法

1）庫操作

創(chuàng)建、修改、刪除、使用與常規(guī)sql語法一致。

查詢當前數(shù)據(jù)庫：

select current_database();

查詢當前用戶：

select user;  或者：select current_user;

2）模式

創(chuàng)建、修改、刪除、使用與常規(guī)sql語法一致。 默認模式位public。

3）表操作

創(chuàng)建、修改、刪除、使用與常規(guī)sql語法一致。。

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

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

1>更新插入一體

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

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;--查詢表結(jié)構(gòu)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;--從數(shù)據(jù)庫中導(dǎo)出數(shù)據(jù)到文件\copy (select * from "user") to "/tmp/1.txt";

a）similar to（pg特色）

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

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

元字符功能

()表示獨立邏輯項目

``

*重復(fù)前面的項0次或更多次

+重復(fù)前面的項一次或更多次

?重復(fù)前面的項0次或1次

{m}重復(fù)前面的項m次

{m, }重復(fù)前面的項m次或更多次

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

[…]

b)POSIX 正則表達式

pg中有兩種正則表達式：

區(qū)別SQL正則表達式POSIX 正則表達式

概念遵循sql語句中l(wèi)ike、similar to語法腳本語言中的標準正則表達式

任意一個字符_.

任意個字符%.*

POSIX 正則表達式：

模式匹配操作符功能

~匹配正則表達式，區(qū)分大小寫

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

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

!~*-

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

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

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); --向子表插入數(shù)據(jù)，父子表都有數(shù)據(jù)insert into persons_lwh values("Bob",15,true);--向父表插入數(shù)據(jù)，只有父表有數(shù)據(jù)select * from persons_lwh;select * from students_lwh;select * from only persons_lwh; --添加only，只獲取直接插入父表的數(shù)據(jù)

4>表空間

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

4）表分區(qū)

1>概念

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

優(yōu)點

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

2>表繼承分區(qū)

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

--表繼承create table sales_detail(...sale_date...);--父表，不存數(shù)據(jù)create table sales_detail_y2014m01 (  check (sale_date >= DATE"2014-01-01" and sale < DATE"2014-02-01")  --check約束)INHERITS(sales_detail); --子表繼承父表--在各個分區(qū)表的分區(qū)鍵上建立索引create index sales_detail_y2014m01_sale_date on sales_detail_y2014m01 (sale_date);--建立規(guī)則或觸發(fā)器，把對主表的數(shù)據(jù)插入重定向到具體分表。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.*);

分表通過規(guī)則或觸發(fā)器優(yōu)缺點對比：

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

3>聲明式分區(qū)

pg內(nèi)部通過表繼承來實現(xiàn)分區(qū)表。pg10.x通過聲明式分區(qū)直接創(chuàng)建分區(qū)表，但其內(nèi)部原理依然是表繼承。

相比表繼承分區(qū)，不需要在父表創(chuàng)建各種觸發(fā)器（降低維護成本），對父表的DML操作會自動路由到相應(yīng)分區(qū)。

目前僅支持范圍分區(qū)和列表分區(qū)。

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

--列表分區(qū)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);

④優(yōu)化

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

select count(*) from sales_detail where sale_date >= DATE"2014-12-01";--同各個分區(qū)表上的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 = 約束觸發(fā)器 x = 排斥約束

5）序列

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

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

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

①創(chuàng)建

1> serial

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

2> sequence（序列）

--[TEMPORARY | TEMP] 臨時序列，在會話結(jié)束時自動刪除；除非用模式修飾，否則同一個會話中同名的非臨時序列是不可見的。create [TEMPORARY | TEMP] sequence seq_name;  --nextval("seq_name") 表示遞增序列seq_name并返回新值。字符串會自動轉(zhuǎn)換為regclass類型--setval("seq_name", bigint) 設(shè)置序列當前數(shù)值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；遞減為負數(shù)    NO MINVALUE         --指定序列的最小值， 如：minvalue 1    NO MAXVALUE        --指定序列的最大值，遞減序列最大值為-1    CACHE 1                 --為快速訪問而在內(nèi)存里預(yù)先存儲多少個序列號，缺省值為1，表示一次只能生成一個值，也就是說沒有緩存。    OWNED BY {table.column | NONE}   --將序列關(guān)聯(lián)到一個特定的表字段上，在刪除該字段或其所在的表時將自動刪除綁定的序列；NONE表示無關(guān)聯(lián)。    CYCLE --默認不添加cycle選項，創(chuàng)建出來的序列滿時會報錯；添加cycle時，序列會從開始值重新開始。    ;

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

--將下一次的自增值設(shè)置成最大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     如果指定的序列不存在，那么發(fā)出一個 notice 而不是拋出一個錯誤。--CASCADE     級聯(lián)刪除依賴序列的對象。--RESTRICT    如果存在任何依賴的對象，則拒絕刪除序列。這個是缺省。drop sequence serial;

⑤序列和事務(wù)

事務(wù)回滾不會影響序列。

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

⑥cache

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

6）觸發(fā)器（Trigger）

觸發(fā)器是一種由事件自動觸發(fā)執(zhí)行的特殊的存儲過程。主要用于加強數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則上的約束。 PostgreSQL的觸發(fā)器是數(shù)據(jù)庫自動執(zhí)行\(zhòng)指定的數(shù)據(jù)庫事件發(fā)生時調(diào)用的回調(diào)函數(shù)。 注意：

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

1>創(chuàng)建觸發(fā)器

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

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

針對字段觸發(fā)：

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 為觸發(fā)函數(shù)

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

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

2>刪除

觸發(fā)器刪除不會刪除對應(yīng)的觸發(fā)函數(shù)；但刪除表時，表上的觸發(fā)器會一并刪除。

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

3>觸發(fā)函數(shù)

觸發(fā)函數(shù)有返回值。 對于BEFORE和INSTEAD OF這類行級觸發(fā)器來說：

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

觸發(fā)函數(shù)常用特殊變量：

NEW: 1. INSERT/UPDATE 行級觸發(fā)器中新的數(shù)據(jù)行，數(shù)據(jù)類型是RECORD；2. 語句級別觸發(fā)器、DELETE操作觸發(fā)器 中此變量未分配。OLD:3. UPDATE/DELETE 行級觸發(fā)器中原有的數(shù)據(jù)行，數(shù)據(jù)類型是RECORD；4. 語句級別觸發(fā)器、INSERT操作觸發(fā)器 中此變量未分配。TG_NAME： 觸發(fā)器名。數(shù)據(jù)類型：name。TG_WHEN： 是 BEFORE/AFTER 觸發(fā)器。TG_LEVEL：是 ROW/STATEMENT 觸發(fā)器。TG_OP：是 INSERT/UPDATE/DELETE/TRUNCATE 之一的字符串，表示DML語句類型。TG_TABLE_NAME：觸發(fā)器所在表的名稱。同理有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;  --語句級觸發(fā)函數(shù)可以顯示的寫：RETURN NULLEND ;$BODY$LANGUAGE plpgsql;--刪除觸發(fā)函數(shù)drop FUNCTION add_stu_trigger()；

4>事件觸發(fā)器

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

7）函數(shù)

1>創(chuàng)建

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

2>函數(shù)的參數(shù)說明

參數(shù)說明

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

OUTOUT 參數(shù)經(jīng)常用于一個函數(shù)需要返回多個值，所以不需要 RETURN 語句。

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

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

參數(shù)使用例子：

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>函數(shù)重載

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

4>塊結(jié)構(gòu)

一個 PostgreSQL 函數(shù)由塊（block）進行組織。

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

8）視圖

--獲取數(shù)據(jù)庫中所有viewselect * from pg_views where schemaname="public";

9）索引

10）用戶及權(quán)限管理

pg中角色和用戶沒區(qū)別。超級用戶為postgres。

11）事務(wù)、并發(fā)和鎖

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

12）規(guī)則（Rule）

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

1>SELECT規(guī)則

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

2>更新規(guī)則

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

3>權(quán)限

規(guī)則從屬于表和視圖

5，執(zhí)行計劃

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

1）多表連接查詢

①Nest Loop Join（嵌套循環(huán)連接）

1>場景

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

外表為小表，且過濾后的數(shù)據(jù)量較少。內(nèi)表的關(guān)聯(lián)列上有高效索引(主鍵或者唯一性索引)。

2>舉例

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

相當于for循環(huán)：

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

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

1>場景

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

2>操作步驟

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

③Sort Merge JOIN

1>場景

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

2>操作步驟

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

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

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

6，數(shù)據(jù)庫優(yōu)化

1）SQL優(yōu)化

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

sql優(yōu)化的思路有兩種：一是：

即去掉無用的步驟；二是優(yōu)化算法，如讓sql走更優(yōu)的執(zhí)行計劃上。

①基于規(guī)則優(yōu)化(RBO)和基于代價優(yōu)化(CBO)

RBO和CBO是兩種數(shù)據(jù)庫引擎在執(zhí)行sql語句時的優(yōu)化策略。

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

2）配置優(yōu)化

pg中與內(nèi)存有關(guān)的配置參數(shù)：

1>shared_buffers（共享緩存區(qū)）

i>工作原理

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

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

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

ii>優(yōu)化策略

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

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

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

shared_buffers調(diào)整策略

2> work_mem

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

3>maintenance_work_mem

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

4>autovacuum_work_mem

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

5>temp_buffers（臨時表緩存）

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

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

默認為-1，表示根據(jù)shared_buffer的大小自動設(shè)置。

7>huge_pages（是否使用大頁）

默認值為try，表示盡量使用大頁。若os未開啟大頁，不使用大頁內(nèi)存，不影響數(shù)據(jù)庫正常使用。

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

與具體內(nèi)存分配無關(guān)

3）sql審計

相關(guān)配置：

參數(shù)調(diào)整說明

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

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

log_min_error_statement控制日志中是否記錄導(dǎo)致數(shù)據(jù)庫出現(xiàn)錯誤的SQL語句。默認為error

4）排查sql

-- 查看表結(jié)構(gòu) SELECT column_name,data_type FROM information_schema.columns WHERE table_name = "表名"; -- 展示在數(shù)據(jù)庫中當前正在執(zhí)行多少查詢 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) -- 查看事務(wù)已經(jīng)打開了多久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; -- 查看慢查詢?nèi)罩臼欠耖_啟 SHOW log_min_duration_statement; -- 設(shè)置慢查詢?nèi)罩?ALTER DATABASE test SET log_min_duration_statement TO 10000; -- 查找經(jīng)常被掃描的大型表 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，內(nèi)部機制與原理

1）進程架構(gòu)模型

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

輔助進程作用配置

Logger系統(tǒng)日志參數(shù)logging_collect設(shè)置為on時啟動該輔助進程

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

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

2）autovacuum進程

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

VACUUM有兩種方式：

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

vacuum相關(guān)的配置：

參數(shù)名說明優(yōu)化思路

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

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

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

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

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

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

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

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

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

vacuum_freeze_table_age當表的年齡大于vacuum_freeze_table_age，則自動轉(zhuǎn)換成vacuum freeze調(diào)高可以降低vacuum freeze的頻率

vacuum_multixact_freeze_table_age當表的年齡大于autovacuum_freeze_max_age，也會強制觸發(fā)vacuum freeze調(diào)高可以降低vacuum freeze的頻率

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

3）pg物理存儲結(jié)構(gòu)

①數(shù)據(jù)塊結(jié)構(gòu)

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

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

②Tuple結(jié)構(gòu)（數(shù)據(jù)行）

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

MVCC是數(shù)據(jù)庫中并發(fā)訪問數(shù)據(jù)時保證數(shù)據(jù)一致性的一種方法。實現(xiàn)MVCC的方法有以下兩種：

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

1）pg MVCC的優(yōu)缺點

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

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

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

2）事務(wù)ID及回卷

1>xmin 表系統(tǒng)字段

插入該行版本的事務(wù)ID。

2> xman 表系統(tǒng)字段

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

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

3> cmin 表系統(tǒng)字段

事務(wù)內(nèi)部的插入類操作的命令I(lǐng)D，此標識是從0開始。

4> xman 表系統(tǒng)字段

事務(wù)內(nèi)部的刪除類操作的命令I(lǐng)D。如果不是刪除命令， xman = 0。

5>事務(wù)ID：xid

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

6>回卷

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

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

事務(wù)ID的比較代碼：

/*     * TransactionIdPrecedes --- is id1 logically < id2?     */    bool TransactionIdPrecedes(TransactionId id1, TransactionId id2) // 結(jié)果返回一個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更新。即普通事務(wù)更新        *2）id1=2, id2=10   那么return true，說明id2更新。即普通事務(wù)更新        */    return (id1 < id2);  //兩個普通id的比較，轉(zhuǎn)換為int32帶符號，第一位為符號位。/*驗證：     * id1=2^31+101, id2=100,   id1 - id2= 2^31+1  diff=-1 (大于2^31為負數(shù)），return true，id2更新。（事務(wù)回卷）* 那么如果id2確實是很早很早之前的舊事務(wù)怎么辦呢？根據(jù)pg規(guī)定的freeze原則，如果id2是很早很早的事務(wù)，兩事務(wù)年齡差又大于2^31，那么id2進行事務(wù)凍結(jié)。按之前的if邏輯，id1更新。*/    diff = (int32) (id1 - id2);    return (diff < 0);    }

7>事務(wù)凍結(jié)（freeze）

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

i>freeze實現(xiàn)：

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

ii>freeze優(yōu)化：

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

9，Standby數(shù)據(jù)庫

1）概念

1>主備數(shù)據(jù)庫

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

2>WAL日志文件

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

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

主備同步wal相關(guān)配置：

參數(shù)名說明優(yōu)化方案

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

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

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

wal_writer_flush_after

checkpoint_timeout不建議頻繁做檢查點，否則XLOG會產(chǎn)生很多的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反饋給主節(jié)點自己已經(jīng)接受（ replies ）到數(shù)據(jù)信息。減小，加快反饋速度

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

10，好用工具

1）PgBouncer

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

2）Slony-I

基于觸發(fā)器的兩個pg的邏輯同步。

3）Bucardo

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

4）pgpool-II

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

11，Postgres-XC

Postgres-XC是基于pg實現(xiàn)的真正的數(shù)據(jù)水平拆分的分布式數(shù)據(jù)庫。