postgresql 怎么自動(dòng)創(chuàng)建分區(qū)

在數(shù)據(jù)庫(kù)日漸龐大的今天，為了方便對(duì)數(shù)據(jù)庫(kù)數(shù)據(jù)的管理，比如按時(shí)間，按地區(qū)去統(tǒng)計(jì)一些數(shù)據(jù)時(shí)，基數(shù)過(guò)于龐大，多有不便。很多商業(yè)數(shù)據(jù)庫(kù)都提供分區(qū)的概念，按不同的維度去存放數(shù)據(jù)，便于后期的管理，PostgreSQL也不例外。

成都創(chuàng)新互聯(lián)公司2013年開(kāi)創(chuàng)至今，是專(zhuān)業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項(xiàng)目成都做網(wǎng)站、成都網(wǎng)站設(shè)計(jì)網(wǎng)站策劃，項(xiàng)目實(shí)施與項(xiàng)目整合能力。我們以讓每一個(gè)夢(mèng)想脫穎而出為使命，1280元丹陽(yáng)做網(wǎng)站,已為上家服務(wù),為丹陽(yáng)各地企業(yè)和個(gè)人服務(wù),聯(lián)系電話(huà):18980820575

PostgresSQL分區(qū)的意思是把邏輯上的一個(gè)大表分割成物理上的幾塊兒。分區(qū)不僅能帶來(lái)訪(fǎng)問(wèn)速度的提升，關(guān)鍵的是，它能帶來(lái)管理和維護(hù)上的方便。

分區(qū)的具體好處是：

某些類(lèi)型的查詢(xún)性能可以得到極大提升。

更新的性能也可以得到提升，因?yàn)楸淼拿繅K的索引要比在整個(gè)數(shù)據(jù)集上的索引要小。如果索引不能全部放在內(nèi)存里，那么在索引上的讀和寫(xiě)都會(huì)產(chǎn)生更多的磁盤(pán)訪(fǎng)問(wèn)。

批量刪除可以用簡(jiǎn)單的刪除某個(gè)分區(qū)來(lái)實(shí)現(xiàn)。

可以將很少用的數(shù)據(jù)移動(dòng)到便宜的、轉(zhuǎn)速慢的存儲(chǔ)介質(zhì)上。

在PG里表分區(qū)是通過(guò)表繼承來(lái)實(shí)現(xiàn)的，一般都是建立一個(gè)主表，里面是空，然后每個(gè)分區(qū)都去繼承它。無(wú)論何時(shí)，都應(yīng)保證主表里面是空的。

小表分區(qū)不實(shí)際，表在多大情況下才考慮分區(qū)呢？PostgresSQL官方給出的建議是：當(dāng)表本身大小超過(guò)了機(jī)器物理內(nèi)存的實(shí)際大小時(shí)（the size of the table should exceed the physical memory of the database server），可以考慮分區(qū)。

PG目前（9.2.2）僅支持范圍分區(qū)和列表分區(qū)，尚未支持散列分區(qū)。

二、環(huán)境

系統(tǒng)環(huán)境：CentOS release 6.3 (Final)

PostgreSQL版本：PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit

三、實(shí)現(xiàn)分區(qū)

3.1 創(chuàng)建主表

david=# create table tbl_partition (

david(# id integer,

david(# name varchar(20),

david(# gender boolean,

david(# join_date date,

david(# dept char(4));

CREATE TABLE

david=#

3.2 創(chuàng)建分區(qū)表

david=# create table tbl_partition_201211 (

check ( join_date = DATE '2012-11-01' AND join_date DATE '2012-12-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201212 (

check ( join_date = DATE '2012-12-01' AND join_date DATE '2013-01-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201301 (

check ( join_date = DATE '2013-01-01' AND join_date DATE '2013-02-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201302 (

check ( join_date = DATE '2013-02-01' AND join_date DATE '2013-03-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201303 (

check ( join_date = DATE '2013-03-01' AND join_date DATE '2013-04-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201304 (

check ( join_date = DATE '2013-04-01' AND join_date DATE '2013-05-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201305 (

check ( join_date = DATE '2013-05-01' AND join_date DATE '2013-06-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=#

3.3 分區(qū)鍵上建索引

david=# create index tbl_partition_201211_joindate on tbl_partition_201211 (join_date);

CREATE INDEX

david=# create index tbl_partition_201212_joindate on tbl_partition_201212 (join_date);

CREATE INDEX

david=# create index tbl_partition_201301_joindate on tbl_partition_201301 (join_date);

CREATE INDEX

david=# create index tbl_partition_201302_joindate on tbl_partition_201302 (join_date);

CREATE INDEX

david=# create index tbl_partition_201303_joindate on tbl_partition_201303 (join_date);

CREATE INDEX

david=# create index tbl_partition_201304_joindate on tbl_partition_201304 (join_date);

CREATE INDEX

david=# create index tbl_partition_201305_joindate on tbl_partition_201305 (join_date);

CREATE INDEX

david=#

對(duì)于開(kāi)發(fā)人員來(lái)說(shuō)，希望數(shù)據(jù)庫(kù)是透明的，只管 insert into tbl_partition。對(duì)于數(shù)據(jù)插向哪個(gè)分區(qū)，則希望由DB決定。這點(diǎn)，ORACLE實(shí)現(xiàn)了，但是PG不行，需要前期人工處理下。

3.4 創(chuàng)建觸發(fā)器函數(shù)

david=# CREATE OR REPLACE FUNCTION tbl_partition_insert_trigger()

RETURNS TRIGGER AS $$

BEGIN

IF ( NEW.join_date = DATE '2012-11-01' AND

NEW.join_date DATE '2012-12-01' ) THEN

INSERT INTO tbl_partition_201211 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2012-12-01' AND

NEW.join_date DATE '2013-01-01' ) THEN

INSERT INTO tbl_partition_201212 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-01-01' AND

NEW.join_date DATE '2013-02-01' ) THEN

INSERT INTO tbl_partition_201301 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-02-01' AND

NEW.join_date DATE '2013-03-01' ) THEN

INSERT INTO tbl_partition_201302 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-03-01' AND

NEW.join_date DATE '2013-04-01' ) THEN

INSERT INTO tbl_partition_201303 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-04-01' AND

NEW.join_date DATE '2013-05-01' ) THEN

INSERT INTO tbl_partition_201304 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-05-01' AND

NEW.join_date DATE '2013-06-01' ) THEN

INSERT INTO tbl_partition_201305 VALUES (NEW.*);

ELSE

RAISE EXCEPTION 'Date out of range. Fix the tbl_partition_insert_trigger() function!';

END IF;

RETURN NULL;

END;

$$

LANGUAGE plpgsql;

CREATE FUNCTION

david=#

說(shuō)明：如果不想丟失數(shù)據(jù)，上面的ELSE 條件可以改成 INSERT INTO tbl_partition_error_join_date VALUES (NEW.*); 同時(shí)需要?jiǎng)?chuàng)建一張結(jié)構(gòu)和tbl_partition 一樣的表tbl_partition_error_join_date，這樣，錯(cuò)誤的join_date 數(shù)據(jù)就可以插入到這張表中而不是報(bào)錯(cuò)了。

附上出處鏈接：

postgresql自定義函數(shù)語(yǔ)法

最近在寫(xiě)postgres的函數(shù)，整理下常用語(yǔ)法備忘

regexp_split_to_table 字符串分割為表格

regexp_split_to_array 字符串分割為數(shù)組

定義內(nèi)部變量

執(zhí)行動(dòng)態(tài)sql，并傳入?yún)?shù)

執(zhí)行動(dòng)態(tài)sql，取出結(jié)果

打印變量

字符串拼接

如何處理大數(shù)據(jù)文件+錄入數(shù)據(jù)

客戶(hù)在集群上提供了一份.sql文件，有2個(gè)多G，用vim等編輯器打不開(kāi)，只能less一部分，而且內(nèi)容有亂碼（中文部分，也不清楚該份文件的編碼格式）——改一下vim的字符集配置就可以解決。

下載文件到本地，嘗試用notepad++打開(kāi)，提示“File is to be opened by Notepate++”；用MySQL Workbench打開(kāi)，出現(xiàn)卡死。

使用文件分割器，對(duì)其進(jìn)行分割。把文件拆分成15等份，每份150MB。

通過(guò)less可以看到建表語(yǔ)句，為oracle，改成postgresql版，并建好表。打開(kāi)1.zg，把insert into之外的語(yǔ)句刪掉后，用Navicat for PostgreSQL工具運(yùn)行sql，出現(xiàn)字符集錯(cuò)誤。

提示：

關(guān)于處理大文件imsi_param2016.sql：

該文件有2個(gè)多G，存放的內(nèi)容是客戶(hù)oracle數(shù)據(jù)庫(kù)中的某張表數(shù)據(jù)。用Linux的vim等編輯器不能打開(kāi)，只能less一小部分。

①下載到本地；

②使用notepad++、MySQL Workbench等工具無(wú)法打開(kāi)如此大的文件；

③使用postgresql運(yùn)行.sql，出現(xiàn)字符集編碼不一致導(dǎo)入失敗的問(wèn)題；

④使用“橘子分割”器，把文件拆分成15等份，每份150MB，再通過(guò)Java程序?qū)ξ募M(jìn)行處理，過(guò)濾得到完整的sql。把oracle表轉(zhuǎn)換為postgresql版（因?yàn)閠o_date函數(shù)mysql沒(méi)有）；

⑤再分別對(duì)15個(gè)sql文件，轉(zhuǎn)碼成UTF-8；

⑥用程序，把每個(gè)文件不完整的sql抽出，并整合成一份error.sql，再人工調(diào)整格式；

⑦15分sql文件，逐一運(yùn)行，確實(shí)麻煩，寫(xiě)程序自動(dòng)運(yùn)行，一次搞定；

⑧經(jīng)過(guò)如上處理，oracle可轉(zhuǎn)為postgresql且錯(cuò)誤率0%。

postgresql sql 怎么逗號(hào)分隔的

PostgreSQL的連接符與oracle一樣，都是||比如'Post' || 'greSQL' 即為　'PostgreSQL'！

kettle提取postgresql數(shù)據(jù)

kettle提取postgresql數(shù)據(jù)步驟如下

1 遍歷區(qū)域和像素

其中xingzheng是行政區(qū)域數(shù)據(jù)表，thematic是專(zhuān)題像素分類(lèi)表

2 影像分割

通過(guò)查詢(xún)某個(gè)區(qū)域和影像的交，達(dá)到影像分割的目的，

查詢(xún)區(qū)域gid=24，波段為1，像素值為4的像素個(gè)數(shù)的sql語(yǔ)句為：

其中函數(shù)ST_Intersects查詢(xún)兩個(gè)幾何的交集，示例如下

3 柵格統(tǒng)計(jì)與數(shù)據(jù)提取

通過(guò)提取函數(shù)ST_ValueCount，查詢(xún)分割后的柵格、指定波段和像素的像素總個(gè)數(shù)，并乘以單個(gè)像元值大小，實(shí)現(xiàn)數(shù)據(jù)面積提取。

其中28.15132773 * 28.15132773為單個(gè)像元值面積大小，具體使用albers投影（雙標(biāo)準(zhǔn)緯線(xiàn)投影）計(jì)算得出，0.000001單位換算成平方公里。

區(qū)域gid和像素類(lèi)型作為輸入，實(shí)現(xiàn)影像分割和柵格面積統(tǒng)計(jì)，完整的sql如下：

4 kettle數(shù)據(jù)提取轉(zhuǎn)換

kettle數(shù)據(jù)提取轉(zhuǎn)換過(guò)程如下：

kettle遍歷區(qū)域和像素，如kettle數(shù)據(jù)轉(zhuǎn)換圖所示“表輸入2”，sql見(jiàn)步驟1；

kettle影像分割和柵格統(tǒng)計(jì)，如kettle數(shù)據(jù)轉(zhuǎn)換圖所示“表輸入”，sql見(jiàn)步驟3；

將提取出的數(shù)據(jù)保存到文件中。

什么是一個(gè)好辦法，在PostgreSQL的水平片段

PostgreSQL允許劃分兩種不同的方式.a個(gè)是由范圍，而另一個(gè)是由列表。表繼承做分區(qū)。 通過(guò)分區(qū)范圍，通常一個(gè)日期范圍，是但通過(guò)分區(qū)列表可以的，如果是這樣的分區(qū)變量是靜態(tài)的，沒(méi)有歪斜。 分區(qū)是通過(guò)表繼承這樣做的優(yōu)先件事就是建立新的子表。

CREATE TABLE measurement (

x int not null,

y date not null,

z int

);

CREATE TABLE measurement_y2006 (

CHECK ( logdate = DATE '2006-01-01' AND logdate DATE '2007-01-01' )

) INHERITS (measurement);

CREATE TABLE measurement_y2007 (

CHECK ( logdate = DATE '2007-01-01' AND logdate DATE '2008-01-01' )

) INHERITS (measurement);

那么無(wú)論是規(guī)則或觸發(fā)器需要?jiǎng)h除該數(shù)據(jù)在正確的表格。 規(guī)則是更快的批量更新，單觸發(fā)更新 CodeGo.net，以及更易于維護(hù)。下面是一個(gè)示例觸發(fā)。

CREATE TRIGGER insert_measurement_trigger

BEFORE INSERT ON measurement

FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

和觸發(fā)函數(shù)來(lái)進(jìn)行插入

CREATE OR REPLACE FUNCTION measurement_insert_trigger()

RETURNS TRIGGER AS $$

BEGIN

IF ( NEW.logdate = DATE '2006-01-01'

AND NEW.logdate DATE '2007-01-01' ) THEN

INSERT INTO measurement_y2006 VALUES (NEW.*);

ELSIF ( NEW.logdate = DATE '2007-01-01'

AND NEW.logdate DATE '2008-01-01' ) THEN

INSERT INTO measurement_y2006m03 VALUES (NEW.*);

ELSE

RAISE EXCEPTION 'Date out of range.';

END IF;

RETURN NULL;

END;

$$

LANGUAGE plpgsql;

這些例子是簡(jiǎn)化了的PostgreSQL版本，以便于閱讀。 我不熟悉pgpool2，但gridsql是專(zhuān)為EnterpriseDB公司，這是建立在PostgreSQL的頂部數(shù)據(jù)庫(kù)的產(chǎn)品。他們的產(chǎn)品是非常好的，但我不認(rèn)為它會(huì)工作在標(biāo)準(zhǔn)postgresl。

2. 那么，如果問(wèn)題是關(guān)于分片，那么的pgpool和PostgreSQL的分區(qū)函數(shù)是無(wú)效的答案。 分區(qū)的分區(qū)是在服務(wù)器上。 分片是更通用，使用時(shí)數(shù)據(jù)庫(kù)被分割在幾個(gè)服務(wù)器上。分片分區(qū)的時(shí)候是不可能的更多，比如對(duì)于不適合在單個(gè)磁盤(pán)上的大型數(shù)據(jù)庫(kù)。 對(duì)于真正的分片那么Skype的PL /代理可能是最好的。

3. PL /代理（通過(guò)Skype等）是一個(gè)很好的解決方案。它需要你的訪(fǎng)問(wèn)是通過(guò)一個(gè)函數(shù)的API，但一旦你有，它可以讓它非常透明。