pg_repack安装及使用

1.pg_repack是一个可以在线重建表和索引的扩展。它会在数据库中建立一个和需要清理的目标表一样的临时表，将目标表中的数据COPY到临时表，并在临时表上建立与目标表一样的索引，然后通过重命名的方式用临时表替换目标表

os：centos 7.4

数据库：postgresql 10.5

pg_repack-1.4.6

2.安装

[postgres@node1 ~]$ unzip pg_repack-1.4.6.zip
[postgres@node1 ~]$ ll
total 248
drwxrwxr-x 8 postgres postgres    184 Oct  4  2019 pg_repack-1.4.6
-rwxr--r-- 1 postgres postgres 125523 May 28 15:23 pg_repack-1.4.6.zip
[postgres@node1 ~]$ cd pg_repack-1.4.6/
[postgres@node1 pg_repack-1.4.6]$ ll
total 16
drwxrwxr-x 3 postgres postgres   71 Oct  4  2019 bin
-rw-rw-r-- 1 postgres postgres 1662 Oct  4  2019 COPYRIGHT
drwxrwxr-x 2 postgres postgres  121 Oct  4  2019 doc
drwxrwxr-x 3 postgres postgres  139 Oct  4  2019 lib
-rw-rw-r-- 1 postgres postgres 1616 Oct  4  2019 Makefile
-rw-rw-r-- 1 postgres postgres 1285 Oct  4  2019 META.json
drwxrwxr-x 2 postgres postgres  219 Oct  4  2019 msvc
-rw-rw-r-- 1 postgres postgres 2201 Oct  4  2019 README.rst
drwxrwxr-x 4 postgres postgres   96 Oct  4  2019 regress
drwxrwxr-x 2 postgres postgres   54 Oct  4  2019 SPECS
[postgres@node1 pg_repack-1.4.6]$ make

postgres@node1 pg_repack-1.4.6]$ psql
psql (12.2)
Type "help" for help.

postgres=# create extension pg_repack;
CREATE EXTENSION
postgres=# \dn
    List of schemas
    Name    |  Owner   
------------+----------
 public     | postgres
 repack     | postgres
 tbase      | postgres
 tbase_pgxz | tbase
(4 rows)

postgres=# \dx
                                  List of installed extensions
   Name    | Version |   Schema   |                         Description                          
-----------+---------+------------+--------------------------------------------------------------
 pg_repack | 1.4.6   | public     | Reorganize tables in PostgreSQL databases with minimal locks
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

测试：

--安装pgstattuple模块（提供多种函数来获得元组层的统计信息） 

postgres=# create extension pgstattuple;
CREATE EXTENSION

--建测试表（表必须有主键或者唯一约束）

postgres=# create table repack_test(id int primary key ,name varchar(10));
CREATE TABLE

postgres=# \d repack_test
                   Table "public.repack_test"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 id     | integer               |           | not null | 
 name   | character varying(10) |           |          | 
Indexes:
    "repack_test_pkey" PRIMARY KEY, btree (id)

--插入测试数据
postgres=# insert into repack_test select generate_series(1,50000000),'a';
INSERT 0 50000000

--用pg_stattuple查看表情况
postgres=# select * from pgstattuple('repack_test');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
 181239808 |     5000000 | 150000000 |         82.76 |                0 |              0 |                  0 |     620336 |         0.34
(1 row)

--查看表大小postgres=# \dt+ repack_test
                       List of relations
 Schema |    Name     | Type  |  Owner   |  Size  | Description 
--------+-------------+-------+----------+--------+-------------
 public | repack_test | table | postgres | 173 MB | 
(1 row)


--更新数据

postgres=# update repack_test set name ='b' where id<2500000;
UPDATE 2499999

--再用pg_stattuple查看表情况
postgres=# select * from pgstattuple('repack_test');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
 271859712 |     5000000 | 150000000 |         55.18 |          2499999 |       74999970 |              27.59 |     930540 |         0.34
(1 row)

--在此执行pg_repack
[postgres@node1 contrib]$ pg_repack -p 5432 -d postgres --no-order --table repack_test
INFO: repacking table "public.repack_test"
--再用pg_stattuple查看表情况
postgres=# select * from pgstattuple('repack_test');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
 271859715 |     5000000 | 150000000 |         82.76 |                0 |              0 |                  0 |     620336 |         0.34
(1 row)

在线pg_repack

repack数据库

postgres@node1 contrib]$ pg_repack -p 5432 -d postgres --no-order --jobs 4  --elevel=info

repack模式

 pg_repack -p 5432 -d postgres --schema=public --no-order --jobs 4  --elevel=info

repack表和索引

pg_repack -p 5432 -d postgres --no-order --table public.repack_test --elevel=info

repack所有索引

pg_repack -p 5432 -d postgres --no-order --only-indexes --table public.repack_test --elevel=info

repack指定索引

 pg_repack -p 5432 -d postgres  --index public.repack_test_pkey --elevel=info

参考文章

https://www.cnblogs.com/zhangfx01/archive/2004/01/13/14267667.html