关于postgresql分割的信息

postgresql 怎么自动创建分区

在数据库日渐庞大的今天，为了方便对数据库数据的管理，比如按时间，按地区去统计一些数据时，基数过于庞大，多有不便。很多商业数据库都提供分区的概念，按不同的维度去存放数据，便于后期的管理，PostgreSQL也不例外。

成都创新互联公司2013年开创至今，是专业互联网技术服务公司，拥有项目成都做网站、成都网站设计网站策划，项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命，1280元丹阳做网站,已为上家服务,为丹阳各地企业和个人服务,联系电话:18980820575

PostgresSQL分区的意思是把逻辑上的一个大表分割成物理上的几块儿。分区不仅能带来访问速度的提升，关键的是，它能带来管理和维护上的方便。

分区的具体好处是：

某些类型的查询性能可以得到极大提升。

更新的性能也可以得到提升，因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问。

批量删除可以用简单的删除某个分区来实现。

可以将很少用的数据移动到便宜的、转速慢的存储介质上。

在PG里表分区是通过表继承来实现的，一般都是建立一个主表，里面是空，然后每个分区都去继承它。无论何时，都应保证主表里面是空的。

小表分区不实际，表在多大情况下才考虑分区呢？PostgresSQL官方给出的建议是：当表本身大小超过了机器物理内存的实际大小时（the size of the table should exceed the physical memory of the database server），可以考虑分区。

PG目前（9.2.2）仅支持范围分区和列表分区，尚未支持散列分区。

二、环境

系统环境：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

三、实现分区

3.1 创建主表

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 创建分区表

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 分区键上建索引

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=#

对于开发人员来说，希望数据库是透明的，只管 insert into tbl_partition。对于数据插向哪个分区，则希望由DB决定。这点，ORACLE实现了，但是PG不行，需要前期人工处理下。

3.4 创建触发器函数

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=#

说明：如果不想丢失数据，上面的ELSE 条件可以改成 INSERT INTO tbl_partition_error_join_date VALUES (NEW.*); 同时需要创建一张结构和tbl_partition 一样的表tbl_partition_error_join_date，这样，错误的join_date 数据就可以插入到这张表中而不是报错了。

附上出处链接：

postgresql自定义函数语法

最近在写postgres的函数，整理下常用语法备忘

regexp_split_to_table 字符串分割为表格

regexp_split_to_array 字符串分割为数组

定义内部变量

执行动态sql，并传入参数

执行动态sql，取出结果

打印变量

字符串拼接

如何处理大数据文件+录入数据

客户在集群上提供了一份.sql文件，有2个多G，用vim等编辑器打不开，只能less一部分，而且内容有乱码（中文部分，也不清楚该份文件的编码格式）——改一下vim的字符集配置就可以解决。

下载文件到本地，尝试用notepad++打开，提示“File is to be opened by Notepate++”；用MySQL Workbench打开，出现卡死。

使用文件分割器，对其进行分割。把文件拆分成15等份，每份150MB。

通过less可以看到建表语句，为oracle，改成postgresql版，并建好表。打开1.zg，把insert into之外的语句删掉后，用Navicat for PostgreSQL工具运行sql，出现字符集错误。

提示：

关于处理大文件imsi_param2016.sql：

该文件有2个多G，存放的内容是客户oracle数据库中的某张表数据。用Linux的vim等编辑器不能打开，只能less一小部分。

①下载到本地；

②使用notepad++、MySQL Workbench等工具无法打开如此大的文件；

③使用postgresql运行.sql，出现字符集编码不一致导入失败的问题；

④使用“橘子分割”器，把文件拆分成15等份，每份150MB，再通过Java程序对文件进行处理，过滤得到完整的sql。把oracle表转换为postgresql版（因为to_date函数mysql没有）；

⑤再分别对15个sql文件，转码成UTF-8；

⑥用程序，把每个文件不完整的sql抽出，并整合成一份error.sql，再人工调整格式；

⑦15分sql文件，逐一运行，确实麻烦，写程序自动运行，一次搞定；

⑧经过如上处理，oracle可转为postgresql且错误率0%。

postgresql sql 怎么逗号分隔的

PostgreSQL的连接符与oracle一样，都是||比如'Post' || 'greSQL' 即为　'PostgreSQL'！

kettle提取postgresql数据

kettle提取postgresql数据步骤如下

1 遍历区域和像素

其中xingzheng是行政区域数据表，thematic是专题像素分类表

2 影像分割

通过查询某个区域和影像的交，达到影像分割的目的，

查询区域gid=24，波段为1，像素值为4的像素个数的sql语句为：

其中函数ST_Intersects查询两个几何的交集，示例如下

3 栅格统计与数据提取

通过提取函数ST_ValueCount，查询分割后的栅格、指定波段和像素的像素总个数，并乘以单个像元值大小，实现数据面积提取。

其中28.15132773 * 28.15132773为单个像元值面积大小，具体使用albers投影（双标准纬线投影）计算得出，0.000001单位换算成平方公里。

区域gid和像素类型作为输入，实现影像分割和栅格面积统计，完整的sql如下：

4 kettle数据提取转换

kettle数据提取转换过程如下：

kettle遍历区域和像素，如kettle数据转换图所示“表输入2”，sql见步骤1；

kettle影像分割和栅格统计，如kettle数据转换图所示“表输入”，sql见步骤3；

将提取出的数据保存到文件中。

什么是一个好办法，在PostgreSQL的水平片段

PostgreSQL允许划分两种不同的方式.a个是由范围，而另一个是由列表。表继承做分区。 通过分区范围，通常一个日期范围，是但通过分区列表可以的，如果是这样的分区变量是静态的，没有歪斜。 分区是通过表继承这样做的优先件事就是建立新的子表。

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);

那么无论是规则或触发器需要删除该数据在正确的表格。 规则是更快的批量更新，单触发更新 CodeGo.net，以及更易于维护。下面是一个示例触发。

CREATE TRIGGER insert_measurement_trigger

BEFORE INSERT ON measurement

FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

和触发函数来进行插入

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;

这些例子是简化了的PostgreSQL版本，以便于阅读。 我不熟悉pgpool2，但gridsql是专为EnterpriseDB公司，这是建立在PostgreSQL的顶部数据库的产品。他们的产品是非常好的，但我不认为它会工作在标准postgresl。

2. 那么，如果问题是关于分片，那么的pgpool和PostgreSQL的分区函数是无效的答案。 分区的分区是在服务器上。 分片是更通用，使用时数据库被分割在几个服务器上。分片分区的时候是不可能的更多，比如对于不适合在单个磁盘上的大型数据库。 对于真正的分片那么Skype的PL /代理可能是最好的。

3. PL /代理（通过Skype等）是一个很好的解决方案。它需要你的访问是通过一个函数的API，但一旦你有，它可以让它非常透明。