Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLESPACE ts1 LOCATION '/data/disk2';
- CREATE TABLE new_tbl
- TABLESPACE ts1 AS
- SELECT * FROM tbl
- ORDER BY .... -- ORDER BY is optional
- ALTER TABLE tbl SET TABLESPACE pg_default
- COPY tbl TO '/path/to/other/disk/tbl.pgsql';
- TRUNCATE tbl;
- COPY tbl FROM '/path/to/other/disk/tbl.pgsql';
- pg93@db-172-16-3-150-> psql
- psql (9.3.3)
- Type "help" for help.
- digoal=# create table t_bloat(id int primary key, info text, crt_time timestamp);
- CREATE TABLE
- digoal=# insert into t_bloat select generate_series(1,100000),md5(random()::text),clock_timestamp();
- INSERT 0 100000
- digoal=# delete from t_bloat where id<>100000;
- DELETE 99999
- digoal=# vacuum t_bloat;
- VACUUM
- digoal=# vacuum t_bloat;
- VACUUM
- digoal=# select relpages from pg_class where relname='t_bloat';
- relpages
- ----------
- 233
- (1 row)
- digoal=# insert into t_bloat select generate_series(1,100),md5(random()::text),clock_timestamp();
- INSERT 0 100
- digoal=# select split_part(ctid::text,',',1) from t_bloat group by 1;
- split_part
- ------------
- (0
- (232
- (2 rows)
- digoal=# begin;
- BEGIN
- digoal=# create temp table t_bloat_tmp(like t_bloat);
- CREATE TABLE
- digoal=# insert into t_bloat_tmp select * from t_bloat where split_part(ctid::text,',',1)='(232';
- INSERT 0 1
- digoal=# delete from t_bloat where split_part(ctid::text,',',1)='(232';
- DELETE 1
- digoal=# insert into t_bloat select * from t_bloat_tmp ;
- INSERT 0 1
- digoal=# end;
- COMMIT
- digoal=# vacuum t_bloat;
- VACUUM
- digoal=# select split_part(ctid::text,',',1) from t_bloat group by 1;
- split_part
- ------------
- (0
- (1
- (2 rows)
- digoal=# select relpages from pg_class where relname='t_bloat';
- relpages
- ----------
- 2
- (1 row)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement