查看所有数据库占用磁盘空间

SELECT
  pg_database.datname AS "数据库名称",
  pg_size_pretty(pg_database_size(pg_database.datname)) AS "磁盘占用空间"
FROM
  pg_database;

发现有个数据库占用空间过大

查询库中所有表占用空间

SELECT
  table_name,
  pg_size_pretty(total_size) AS total_size
FROM (
  SELECT
    table_name,
    pg_total_relation_size(table_name::regclass) AS total_size
  FROM
    information_schema.tables
  WHERE
    table_schema = 'public' -- 你的数据库架构，通常是'public'
) AS table_sizes
ORDER BY
  total_size DESC;

发现有两张表超大，与实际数据量不符

安装pg_repack

yum install pg_repack12.x86_64

修改pg配置postgresql.conf

shared_preload_libraries = 'pg_repack'

重启pg
在数据库中创建扩展

CREATE EXTENSION pg_repack;

测试使用

/usr/pgsql-12/bin/pg_repack -h 10.10.33.14 -U postgres --dry-run -d test_db --table tset_table

如下则表示可以使用

尝试使用

/usr/pgsql-12/bin/pg_repack -h 10.10.33.14 -U postgres --no-kill-backend --wait-timeout=3600 -d test_db --table test_table

关于参数介绍，可以参考pg_repack 1.5.0 – Reorganize tables in PostgreSQL databases with minimal locks