Advertisement
Guest User

Untitled

a guest
Jun 27th, 2019
78
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.65 KB | None | 0 0
  1. CREATE TABLESPACE ts1 LOCATION '/data/disk2';
  2.  
  3. CREATE TABLE new_tbl
  4. TABLESPACE ts1 AS
  5. SELECT * FROM tbl
  6. ORDER BY .... -- ORDER BY is optional
  7.  
  8. ALTER TABLE tbl SET TABLESPACE pg_default
  9.  
  10. COPY tbl TO '/path/to/other/disk/tbl.pgsql';
  11. TRUNCATE tbl;
  12. COPY tbl FROM '/path/to/other/disk/tbl.pgsql';
  13.  
  14. pg93@db-172-16-3-150-> psql
  15. psql (9.3.3)
  16. Type "help" for help.
  17.  
  18. digoal=# create table t_bloat(id int primary key, info text, crt_time timestamp);
  19. CREATE TABLE
  20. digoal=# insert into t_bloat select generate_series(1,100000),md5(random()::text),clock_timestamp();
  21. INSERT 0 100000
  22. digoal=# delete from t_bloat where id<>100000;
  23. DELETE 99999
  24. digoal=# vacuum t_bloat;
  25. VACUUM
  26. digoal=# vacuum t_bloat;
  27. VACUUM
  28. digoal=# select relpages from pg_class where relname='t_bloat';
  29. relpages
  30. ----------
  31. 233
  32. (1 row)
  33.  
  34. digoal=# insert into t_bloat select generate_series(1,100),md5(random()::text),clock_timestamp();
  35. INSERT 0 100
  36. digoal=# select split_part(ctid::text,',',1) from t_bloat group by 1;
  37. split_part
  38. ------------
  39. (0
  40. (232
  41. (2 rows)
  42.  
  43. digoal=# begin;
  44. BEGIN
  45. digoal=# create temp table t_bloat_tmp(like t_bloat);
  46. CREATE TABLE
  47. digoal=# insert into t_bloat_tmp select * from t_bloat where split_part(ctid::text,',',1)='(232';
  48. INSERT 0 1
  49. digoal=# delete from t_bloat where split_part(ctid::text,',',1)='(232';
  50. DELETE 1
  51. digoal=# insert into t_bloat select * from t_bloat_tmp ;
  52. INSERT 0 1
  53. digoal=# end;
  54. COMMIT
  55. digoal=# vacuum t_bloat;
  56. VACUUM
  57. digoal=# select split_part(ctid::text,',',1) from t_bloat group by 1;
  58. split_part
  59. ------------
  60. (0
  61. (1
  62. (2 rows)
  63.  
  64. digoal=# select relpages from pg_class where relname='t_bloat';
  65. relpages
  66. ----------
  67. 2
  68. (1 row)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement