如何扩大postgresql存储空间

1. 概述

让客户满意是我们工作的目标，不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户，将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴，公司提供的服务项目有：主机域名、网络空间、营销软件、网站建设、岳阳网站维护、网站推广。

cstore_fdw实现了 PostgreSQL 数据库的列式存储。列存储非常适合用于数据分析的场景，数据分析的场景下数据是批量加载的。

这个扩展使用了Optimized Row Columnar (ORC)数据存储格式，ORC改进了Facebook的RCFile格式，带来如下好处:

压缩:将内存和磁盘中数据大小削减到2到4倍。可以扩展以支持不同压缩算法。

列投影:只提取和查询相关的列数据。提升IO敏感查询的性能。

跳过索引:为行组存储最大最小统计值，并利用它们跳过无关的行。

2. 使用

cstore_fdw的安装和使用都非常简单，可以参考官方资料。

thub.com/citusdata/cstore_fdw

注)注意cstore_fdw只支持PostgreSQL9.3和9.4 。

下面做几个简单的性能对比，看看cstore_fdw究竟能带来多大的性能提升。

2.1 数据加载

2.1.1 普通表

CREATE TABLE tb1

(

id int,

c1 TEXT,

c2 TEXT,

c3 TEXT,

c4 TEXT,

c5 TEXT,

c6 TEXT,

c7 TEXT,

c8 TEXT,

c9 TEXT,

c10 TEXT

);

注:要和普通表的全表扫描作对比，所以不建主键和索引。

[postgres@node2 chenhj]$ time psql -p 40382 -At -F, -c "select id,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text,id::text from generate_series(1,10000000) id"|time psql -p 40382 -c "copy tb1 from STDIN with CSV"

COPY 10000000

1.56user 1.00system 6:42.39elapsed 0%CPU (0avgtext+0avgdata 7632maxresident)k

776inputs+0outputs (17major+918minor)pagefaults 0swaps

real 6m42.402s

user 0m15.174s

sys 0m14.904s

postgres=# select pg_total_relation_size('tb1'::regclass);

pg_total_relation_size

------------------------

1161093120

(1 row)

postgres=# \timing

Timing is on.

postgres=# analyze tb1;

ANALYZE

Time: 11985.070 ms

插入1千万条记录，数据占用存储大小1.16G，插入耗时6分42秒，分析耗时12秒。

2.1.2 cstore表

$ mkdir -p /home/chenhj/data94/cstore

CREATE EXTENSION cstore_fdw;

CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;

CREATE FOREIGN TABLE cstb1

(

id int,

c1 TEXT,

c2 TEXT,

c3 TEXT,

c4 TEXT,

c5 TEXT,

c6 TEXT,

c7 TEXT,

c8 TEXT,

c9 TEXT,

c10 TEXT

)

SERVER cstore_server

OPTIONS(filename '/home/chenhj/data94/cstore/cstb1.cstore',

compression 'pglz');

[postgres@node2 chenhj]$ time psql -p 40382 -At -F, -c "select id,id::text,id::text,id::text,id::text, id::text,id::text,id::text,id::text,id::text,id::text from generate_series(1,10000000) id"|time psql -p 40382 -c "copy cstb1 from STDIN with CSV"

COPY 10000000

1.53user 0.78system 7:35.15elapsed 0%CPU (0avgtext+0avgdata 7632maxresident)k

968inputs+0outputs (20major+920minor)pagefaults 0swaps

real 7m35.520s

user 0m14.809s

sys 0m14.170s

[postgres@node2 chenhj]$ ls -l /home/chenhj/data94/cstore/cstb1.cstore

-rw------- 1 postgres postgres 389583021 Jun 23 17:32 /home/chenhj/data94/cstore/cstb1.cstore

postgres=# \timing

Timing is on.

postgres=# analyze cstb1;

ANALYZE

Time: 5946.476 ms

插入1千万条记录，数据占用存储大小390M，插入耗时7分35秒，分析耗时6秒。

使用cstore列存储后，数据占用存储大小降到普通表的3分之1。需要说明的是，由于所有TEXT列填充了随机数据，压缩率不算高，某些实际的应用场景下压缩效果会比这更好。

2.2 Text列的like查询性能对比

2.2.1 普通表

清除文件系统缓存，并重启PostgreSQL

[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart

[root@node2 ~]# free

total used free shared buffers cached

Mem: 2055508 771356 1284152 0 9900 452256

-/+ buffers/cache: 309200 1746308

Swap: 4128760 387624 3741136

[root@node2 ~]# echo 1 /proc/sys/vm/drop_caches

[root@node2 ~]# free

total used free shared buffers cached

Mem: 2055508 326788 1728720 0 228 17636

-/+ buffers/cache: 308924 1746584

Swap: 4128760 381912 3746848

对Text列执行like查询

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.42 0.00 95.40

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 58.55 330.68 212.08 7351441 4714848

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where c1 like '%66'"

count

--------

100000

(1 row)

real 0m7.051s

user 0m0.001s

sys 0m0.004s

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.43 0.00 95.39

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 58.90 381.53 211.90 8489597 4714956

耗时7.1秒，产生IO读1.14G,IO写108K。

不清文件系统缓存，不重启PostgreSQL，再执行一次。消耗时间降到1.6秒，几乎不产生IO。

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.43 0.00 95.39

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 58.81 332.20 213.06 7350301 4714364

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where c1 like '%66'"

count

--------

100000

(1 row)

real 0m1.601s

user 0m0.002s

sys 0m0.001s

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.43 0.00 95.38

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 58.80 332.12 213.01 7350337 4714364

2.2.2 cstore表

清除文件系统缓存，并重启PostgreSQL

[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart

[root@node2 ~]# echo 1 /proc/sys/vm/drop_caches

对Text列执行like查询

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.38 0.00 95.45

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 58.12 376.42 209.04 8492017 4716048

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from cstb1 where c1 like '%66'"

count

--------

100000

(1 row)

real 0m2.786s

user 0m0.002s

sys 0m0.003s

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.38 0.00 95.44

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 58.12 378.75 208.89 8550761 4716048

耗时2.8秒，产生IO读59M,IO写0K。执行时间优化的虽然不是太多，但IO大大减少，可见列投影起到了作用。

不清文件系统缓存，不重启PostgreSQL，再执行一次。消耗时间降到1.4秒，几乎不产生IO。

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.36 0.00 95.47

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 57.75 376.33 207.58 8550809 4716524

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from cstb1 where c1 like '%66'"

count

--------

100000

(1 row)

real 0m1.424s

user 0m0.002s

sys 0m0.001s

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.80 0.00 0.38 3.36 0.00 95.47

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 57.70 375.96 207.38 8550809 4716588

2.3 对Int列执行=查询

2.3.1 普通表

清除文件系统缓存，并重启PostgreSQL后

[postgres@node2 chenhj]$ pg_ctl -D /home/chenhj/data94 -l logfile94 restart

[root@node2 ~]# echo 1 /proc/sys/vm/drop_caches

对Int列执行=查询

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.79 0.00 0.37 3.33 0.00 95.50

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 57.25 373.21 205.67 8560897 4717624

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where id =666666"

count

-------

1

(1 row)

real 0m6.844s

user 0m0.002s

sys 0m0.006s

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.79 0.00 0.37 3.34 0.00 95.49

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 57.60 422.57 205.54 9699161 4717708

耗时6.8秒，产生IO读1.14G,IO写84K

不清缓存，再执行一次。消耗时间降到1.1秒，几乎不产生IO。

[postgres@node2 chenhj]$ iostat -k dm-2

Linux 2.6.32-71.el6.x86_64 (node2) 06/23/14 _x86_64_ (2 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.79 0.00 0.37 3.33 0.00 95.50

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

dm-2 57.44 421.37 204.97 9699177 4718032

[postgres@node2 chenhj]$ time psql -p 40382 -c "select count(*) from tb1 where id =666666"

count

-------

postgresql 空间要素数据怎样导出导入

安装postgresql

yum install postgresql postgresql-server

mysql占用端口3306 pgsql是5432

导入整个数据库

psql -U postgres(用户名) 数据库名(缺省时同用户名) /data/dum.sql

导出整个数据库

pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名) /data/dum.sql

导出某个表

pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名) -t table(表名) /data/dum.sql

压缩方法

一般用dump导出数据会比较大，推荐使用xz压缩

压缩方法 xz dum.sql 会生成 dum.sql.xz 的文件

6

xz压缩数据倒数数据库方法

xzcat /data/dum.sql.xz | psql -h localhost -U postgres(用户名) 数据库名(缺省时同用户名)

如何在postgresql中创建空间数据库

PersistenceConfig（持久层配置）

我们想要一个配置了所有可用仓库的MONGODB配置。在这个简单的应用中我们只用了一个仓库，所以配置也非常的简单：

@Configuration

class PersistenceConfig {

@Bean

public AccountRepository accountRepository() throws UnknownHostException {

return new MongoAccountRepository(mongoTemplate());

如何在postgresql9.3中创建空间数据库

f(isset($_POST['submit'])$_POST['submit']=='提交'){

3 //判断是否是提交过来的

4 $intext = $_POST['intext'];

5 if($intext!=null||$intext!=''){

6 $link = mysql_connect("localhost", "root", "123456");

7 //数据库配置信息 第一个参数数据库位置第二个是用户名第三个是密码

8 mysql_select_db("szn_test");

9 //设置要使用的数据库

10 $sql = "select * from demo where res = '".$intext."'";

如何解决postgresql磁盘不足的问题

1）增加磁盘，创建分区，挂载目录

2）目录授权

Chown postgres /data2'

Chmod 777 /data2'

3）创建表空间指向该目录

create tablespace ts_demo location '/data2';

4）设置默认表空间参数值

SET default_tablespace = ts_demo;

select * from pg_settings where name ='default_tablespace';

5）后续创建表会默认存储在该表空间对应的目录

创建表查看表存储路径是否改过来了

select pg_relation_filepath('out_tiqu0')

Postgres数据库有默认的2个表空间

select * from pg_tablespace

Name | Owner | Location

-----------+----------+-----------------------------------

pg_default | postgres |

pg_global | postgres |

创建表如果不指定表空间 那么就会默认使用pg_default，对应的路径为pg安装路径中data文件夹下面，

6）对已存在的表更改表空间

alter table out_831108_1 set tablespace ts_demo;

更改之后再查看是否改过来了

分享文章：postgresql空间的简单介绍
地址分享：http://kswjz.com/article/dsciiig.html