生产环境修改PostgreSQL表索引对应的表空间

通过iostat命令发现某块磁盘的io使用率经常保持在100%，通过blkid命令获取linux raid存储盘符和挂载点的关系后，最后发现是挂载点上的一个数据库表空间在占用大io。

现象

postgres@dbmaster:~$ iostat -xm 3 |grep -v dm

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          11.68    0.00    3.82    8.63    0.00   75.87

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00     0.69    0.29    1.54     0.00     0.01    18.01     0.00    1.45    4.34    0.91   0.57   0.10
sdb               0.00     0.77    3.51    2.63     0.42     0.57   329.19     0.03    4.23    0.61    9.07   0.52   0.32
sdc               0.00    12.98   31.28  283.84     1.17     5.46    43.07     0.10    2.88   21.27    0.85   0.57  18.00
sdd               0.00     0.08    0.01    0.95     0.00     0.42   889.72     0.34  358.73   65.53  361.07   4.14   0.40
sde               0.42    13.04   58.26  766.30     1.60     6.63    20.45     0.71    0.86    4.56    0.58   0.89  73.57
sdf               0.11     8.62   56.90  217.50     3.02     2.50    41.15     0.63    2.28   10.76    0.07   0.89  24.46

解决办法

现在知道个别磁盘io使用率很高，接下来就是需要修改个别表索引的表空间到空闲磁盘中。

通过alter index直接移动索引会锁住其它更新操作，大索引的移动需要很长时间，在生产环境中不可取。可以通过以下方式解决：

1。通过create index concurrently在新的表空间重建和原表空间定义一样的索引（名字不同）。

2。删除原表空间的索引。

create index concurrently的介绍可以参考这篇文章：http://my.oschina.net/Kenyon/blog/93465

实际操作

下面是原来一个表的索引详情，需要把除了主键外在indextbs上的索引移动到默认表空间。

Indexes:
“article_111_pkey” PRIMARY KEY, btree (aid), tablespace “indextbs”
“article_111_url_hash” UNIQUE CONSTRAINT, btree (url_hash), tablespace “indextbs”
“article_111_bid_titlehash_idx” btree (bid, title_hash), tablespace “indextbs”
……

1、移动article_111_bid_titlehash_idx索引

CREATE INDEX CONCURRENTLY article_111_bid_title_hash_idx ON article_111 USING btree (bid, title_hash COLLATE pg_catalog."default") TABLESPACE pg_de
fault ;
drop index article_111_bid_titlehash_idx ;

2、移动article_111_url_hash索引

这个索引有一个唯一性约束，和前面方法有些区别。

CREATE UNIQUE INDEX CONCURRENTLY article_111_urlhash_idx ON article_111 USING btree (url_hash) ;
alter table article_111 drop constraint article_111_url_hash,add unique using index article_111_urlhash_idx ;

参考网址：http://www.postgresql.org/docs/9.1/static/sql-altertable.html