网站建设评比办法,免费制作微信小程序平台,把自己做的动画传到哪个网站上,苏州建设网站哪家好pg_repack是一个可以在线重建表和索引的扩展。它会在数据库中建立一个和需要清理的目标表一样的临时表#xff0c;将目标表中的数据COPY到临时表#xff0c;并在临时表上建立与目标表一样的索引#xff0c;然后通过重命名的方式用临时表替换目标表。
环境#xff1a;
1将目标表中的数据COPY到临时表并在临时表上建立与目标表一样的索引然后通过重命名的方式用临时表替换目标表。
环境
1redhat-releaseCentOS Linux release 7.6.1810 (Core)
2database versionpostgresql 14.6
一、部署pg_repack
--解压编译
[postgrestencent soft]$ unzip pg_repack-master.zip
[postgrestencent soft]$ cd pg_repack-master/
[postgrestencent pg_repack-master]$ ll
total 36
drwxrwxr-x 3 postgres postgres 4096 Jun 15 04:53 bin
-rw-rw-r-- 1 postgres postgres 1662 Jun 15 04:53 COPYRIGHT
drwxrwxr-x 2 postgres postgres 4096 Jun 15 04:53 doc
drwxrwxr-x 3 postgres postgres 4096 Jun 15 04:53 lib
-rw-rw-r-- 1 postgres postgres 1616 Jun 15 04:53 Makefile
-rw-rw-r-- 1 postgres postgres 1381 Jun 15 04:53 META.json
drwxrwxr-x 2 postgres postgres 4096 Jun 15 04:53 msvc
-rw-rw-r-- 1 postgres postgres 2191 Jun 15 04:53 README.rst
drwxrwxr-x 4 postgres postgres 4096 Jun 15 04:53 regress
[postgrestencent pg_repack-master]$ make make install
--所在数据库安装pg_repack扩展
[postgrestencent pg_repack-master]$ psql -d testdb test
psql (14.6)
Type help for help.
testdb# select * from pg_available_extensions where name like pg_repack*; name | default_version | installed_version | comment
---------------------------------------------------
(0 rows)
testdb# create extension pg_repack;
CREATE EXTENSION
testdb# \dx List of installed extensions
Name | Version | Schema | Description
--------------------------------------------------------------- btree_gist | 1.6 | test | support for indexing common datatypes in GiST pageinspect | 1.9 | public | inspect the contents of database pages at a low level pg_freespacemap | 1.2 | test | examine the free space map (FSM) pg_repack | 1.4.8 | test | Reorganize tables in PostgreSQL databases with minimal locks pg_visibility | 1.2 | test | examine the visibility map (VM) and page-level visibility info pgstattuple | 1.5 | test | show tuple-level statistics plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(7 rows)
--查看pg_repack包含的函数
testdb# \dxS pg_repack Objects in extension pg_repack Object description
------------------------------------------------------------------ function repack.conflicted_triggers(oid) function repack.disable_autovacuum(regclass) function repack.get_alter_col_storage(oid) function repack.get_assign(oid,text) function repack.get_columns_for_create_as(oid) function repack.get_compare_pkey(oid,text) function repack.get_create_index_type(oid,name) function repack.get_create_trigger(oid,oid) function repack.get_drop_columns(oid,text) function repack.get_enable_trigger(oid) function repack.get_index_columns(oid,text) function repack.get_order_by(oid,oid) function repack.get_storage_param(oid) function repack.get_table_and_inheritors(regclass) function repack.oid2text(oid) function repack.repack_apply(cstring,cstring,cstring,cstring,cstring,integer) function repack.repack_drop(oid,integer) function repack.repack_indexdef(oid,oid,name,boolean) function repack.repack_index_swap(oid) function repack.repack_swap(oid) function repack.repack_trigger() function repack.version() function repack.version_sql() schema repack view repack.primary_keys view repack.tables
(26 rows)
二、安装pgstattuple模块
提供多种函数来获得元组层的统计信息
testdb# create extension pgstattuple;
CREATE EXTENSION
三、创建测试环境
3.1 创建表表必须有主键或者唯一约束
testdb# create table repack_test(id int primary key ,name varchar(10));
CREATE TABLE
testdb# \d repack_test Table test.repack_test Column | Type | Collation | Nullable | Default
------------------------------------------------------------- id | integer | | not null | name | character varying(10) | | |
Indexes:
repack_test_pkey PRIMARY KEY, btree (id)
3.2 插入数据
testdb# insert into repack_test select generate_series(1,50000000),a;
INSERT 0 50000000
--用pg_stattuple查看表情况
testdb# 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
------------------------------------------------------------------------------------------------------------------------------------ 1812389888 | 50000000 | 1500000000 | 82.76 | 0 | 0 | 0 | 6195196 | 0.34
(1 row)
--查看表大小
testdb# select pg_size_pretty(pg_relation_size(repack_test)); pg_size_pretty
---------------- 1728 MB
(1 row)
3.3 修改表数据
--update 表repack_test
testdb# update repack_test set name b where id2500000;
UPDATE 2499999
--用pg_stattuple查看表情况
testdb# 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
------------------------------------------------------------------------------------------------------------------------------------ 1903009792 | 50000000 | 1500000000 | 78.82 | 2499999 | 74999970 | 3.94 | 6505400 | 0.34
(1 row)
可以发现产生了2499999条死亡元组。
--查看表大小
testdb# select pg_size_pretty(pg_relation_size(repack_test)); pg_size_pretty
---------------- 1815 MB
(1 row)
四、执行pg_repack
--执行pg_repack
[postgrestencent bin]$ ./pg_repack -p 4519 -d testdb -Utest --no-order --table test.repack_test
INFO: repacking table test.repack_test
# test.repack_test 表示test模式下repack_test表默认自动查找postgres数据库下表repack_test执行过程中会报没有发现表所以需要指定schema。
另此处执行没有输入密码因为配置了.pgpass文件
--用pg_stattuple查看表情况
testdb# 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
------------------------------------------------------------------------------------------------------------------------------------ 1812389888 | 50000000 | 1500000000 | 82.76 | 0 | 0 | 0 | 6195196 | 0.34
(1 row)
当前死亡元组数归零。
--查看表大小
testdb# select pg_size_pretty(pg_relation_size(repack_test)); pg_size_pretty
---------------- 1728 MB
(1 row)
表数据恢复原始大小。
五、pg_repack主要模式
repack数据库
postgresnode1 contrib]$ pg_repack -p 5432 -d postgres --no-order --jobs 4 --elevelinfo
repack模式 pg_repack -p 5432 -d postgres --schemapublic --no-order --jobs 4 --elevelinfo
repack表和索引
pg_repack -p 5432 -d postgres --no-order --table public.repack_test --elevelinfo
repack所有索引
pg_repack -p 5432 -d postgres --no-order --only-indexes --table public.repack_test --elevelinfo
repack指定索引
pg_repack -p 5432 -d postgres --index public.repack_test_pkey --elevelinfo
