首页 > 其他分享 >安装使用pg_repack

安装使用pg_repack

时间:2022-12-02 11:35:40浏览次数:42  
标签:postgres 安装 repack db pg test tb

环境:

OS:Centos 7

DB:pg12

 

1.查看有那些可以使用的版本

[root@localhost bin]# yum list pg_repack*
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: mirrors.ustc.edu.cn
 * epel: mirror.01link.hk
 * extras: mirrors.ustc.edu.cn
 * updates: mirrors.ustc.edu.cn
Available Packages
pg_repack10.x86_64                                        1.4.6-1.rhel7                                        pgdg10
pg_repack11.x86_64                                        1.4.6-1.rhel7                                        pgdg11
pg_repack12.x86_64                                        1.4.6-1.rhel7                                        pgdg12
pg_repack13.x86_64                                        1.4.6-1.rhel7                                        pgdg13
pg_repack_10.x86_64                                       1.4.8-1.rhel7                                        pgdg10
pg_repack_11.x86_64                                       1.4.8-1.rhel7                                        pgdg11
pg_repack_12.x86_64                                       1.4.8-1.rhel7                                        pgdg12
pg_repack_13.x86_64                                       1.4.8-1.rhel7                                        pgdg13
pg_repack_14.x86_64                                       1.4.8-1.rhel7                                        pgdg14
pg_repack_15.x86_64                                       1.4.8-1.rhel7                                        pgdg15

 

2.安装与pg一致的版本
[root@localhost bin]#yum install pg_repack12.x86_64

 

3.加载扩展
修改配置文件后重启postgresql
vi /opt/pg12/data/postgresql.conf
shared_preload_libraries = 'pg_repack'

[root@localhost ~]# systemctl stop postgresql-12
[root@localhost ~]# systemctl start postgresql-12

 

4.数据库中创建扩展
su - postgres
[postgres@localhost data]$ psql
postgres=# CREATE EXTENSION pg_repack;
CREATE EXTENSION

 

5.查看如何使用
[postgres@localhost ~]$ /usr/pgsql-12/bin/pg_repack -h 192.168.1.102 -U postgres --dry-run -d db_test --table tb_test
INFO: Dry run enabled, not executing repack
Password:
ERROR: pg_repack failed with error: pg_repack 1.4.8 is not installed in the database
[postgres@localhost ~]$

 

解决办法:需要在具体的库下面安装扩展插件

[postgres@localhost ~]$ psql -h 192.168.1.102 -U postgres -d db_test
Password for user postgres: 
psql (12.13)
Type "help" for help.

db_test=# 
db_test=# CREATE EXTENSION pg_repack;
CREATE EXTENSION

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

再次执行(不真正执行dry-run)

[postgres@localhost ~]$ /usr/pgsql-12/bin/pg_repack -h 192.168.1.102 -U postgres --dry-run -d db_test --table tb_test
INFO: Dry run enabled, not executing repack
Password: 
INFO: repacking table "public.tb_test"

 

说明:表tb_test是支持pg_repack的,表必须有主键或者唯一约束

 

#################模拟删除表释放空间######################
1.查看当前表记录数和大小
db_test=# SELECT pg_size_pretty(pg_total_relation_size('tb_test'));
pg_size_pretty
----------------
2023 MB
(1 row)


db_test=# select count(1) from tb_test;
count
----------
13430004
(1 row)

2.尝试删除数据
db_test=# delete from tb_test;
DELETE 13430004

这个时候再次查看表占用存储大小

db_test=# SELECT pg_size_pretty(pg_total_relation_size('tb_test'));
pg_size_pretty
----------------
288 MB
(1 row)

发现表还是会占用存储空间


3.执行pg_repack
su - postgres
[postgres@localhost ~]$ /usr/pgsql-12/bin/pg_repack -h 192.168.1.102 -U postgres -d db_test --table tb_test
Password:
INFO: repacking table "public.tb_test"
WARNING: skipping invalid index: CREATE INDEX idx_name2 ON public.tb_test USING btree (name2)


db_test=# SELECT pg_size_pretty(pg_total_relation_size('tb_test'));
pg_size_pretty
----------------
8192 bytes
(1 row)

这个时候发现空间以及完成释放

 

上面提示索引失效

db_test=# \d+ tb_test;
                                                            Table "public.tb_test"
   Column   |            Type             | Collation | Nullable |               Default               | Storage  | S
tats target | Description 
------------+-----------------------------+-----------+----------+-------------------------------------+----------+--
------------+-------------
 id         | bigint                      |           | not null | nextval('tb_test_id_seq'::regclass) | plain    |  
            | 
 name1      | character varying(32)       |           |          |                                     | extended |  
            | 
 name2      | character varying(32)       |           |          |                                     | extended |  
            | 
 name3      | character varying(32)       |           |          |                                     | extended |  
            | 
 name4      | character varying(32)       |           |          |                                     | extended |  
            | 
 name5      | character varying(32)       |           |          |                                     | extended |  
            | 
 name6      | character varying(32)       |           |          |                                     | extended |  
            | 
 createtime | timestamp without time zone |           |          | CURRENT_TIMESTAMP                   | plain    |  
            | 
 modifytime | timestamp without time zone |           |          | CURRENT_TIMESTAMP                   | plain    |  
            | 
 name7      | character varying(32)       |           |          |                                     | extended |  
            | 
 name8      | character varying(32)       |           | not null | 'aaa'::character varying            | extended |  
            | 
Indexes:
    "tb_test_pkey" PRIMARY KEY, btree (id)
    "idx_name2" btree (name2) INVALID
Access method: heap

 

重建索引
db_test=# reindex index idx_name2;
REINDEX

 

标签:postgres,安装,repack,db,pg,test,tb
From: https://www.cnblogs.com/hxlasky/p/16943889.html

相关文章

  • linux - 最小化安装-网络配置【兼容服务器与虚拟机安装】
    1.背景根据提示 最小化安装完成后我安装的版本为CentOSLinuxrelease7.8.2003(Core)2.设置linux网卡cd/etc/sysconfig/network-scripts找到文件ifcfg-ens+......
  • window安装nvm
    下载nvm安装包地址:https://github.com/coreybutler/nvm-windows/releases下载nvm-setup.exe文件安装nvm安装的时候会选择两个路径第一个路径为nvm的安装路径第二个路......
  • 火狐firebug和firepath插件安装方法
    火狐浏览器下掉了firebug和firepath插件,用户即使下载了火狐55以下的版本,也无法查找到这两个插件。可以用以下方法来获取这两个插件。第1步:下载火狐55以内版本安装包,安装时......
  • vue npm 安装失败解决方案
    npminstall-gcnpm--registry=http://registry.npm.taobao.orgnpmgetregistrycnpminstall在系统中搜索框输入WindosPowerShell点击“管理员身份运行”......
  • pl-sql developer安装
    一直都是机器本机上有oracle,所以装plsqldeveloper这个oracle开发工具的时候,也没怎么在意,但如果是不在本机上装oracle,而要用这个工具的话,则需要下载......
  • Ubuntu安装桌面系统(ubuntu无桌面服务器,远程连接时显示桌面)
    Ubuntu安装桌面系统(ubuntu无桌面服务器,远程连接时显示桌面)一.安装vncserversudoapt-getinstalltightvncserver安装好后在ubuntu终端输入vncserver然后就会让你设......
  • docker的安装使用
    Ubuntu18.04中docker的安装######################安装dockersudoapt-getupdate#安装依赖包sudoapt-getinstallapt-transport-httpssudoapt-getinstallca-c......
  • IDEA安装
    IDEA安装什么是IDE集成开发环境(IDE,IntegratedDevelopmentEnvironment)是用于提供程序开发环境的应用程序,一般包括代码编辑器、编译器、调试器和图形用户界面等工具。......
  • Debian11 安装
    1.开启虚拟机2.断开网络连接【断网安装】断网安装的原因:Debian的服务器在国外,联网安装系统会从官网库中下载某些包,会导致拖慢安装进度,断网安装不会影响使用。【点击......
  • Debian安装
    下载Debian镜像包Debian官网:DebianISO:Debian_ISO创建虚拟机实例1.选择“自定义(高级)”2.选择最新硬件兼容性【默认】3.选择“稍后安装操作系统”4.选择Linux......