Postgres Commands

PostgreSQL 常见命令/查询语句

数据库连接

在服务器或本机下载PostgreSQL客户端

# 先添加 PG 官方 YUM 源
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 再安装客户端
sudo yum install -y postgresql13

连接数据库

psql -h <host> -p <5432(默认端口)> -U <user_id> -d <database_name>

接着输入密码即可

库与表操作

查看所有表的大小

SELECT
  n.nspname        AS schema,
  c.relname        AS table_name,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
  pg_total_relation_size(c.oid)                 AS total_size_bytes
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'      -- 只统计普通表
  AND n.nspname NOT IN ('pg_catalog','information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC;

查看所有索引的情况（大小，使用次数）

SELECT
    s.schemaname,
    s.relname AS table_name,
    s.indexrelname AS index_name,
    s.idx_scan AS times_used,         -- 索引扫描次数
    s.idx_tup_read AS tuples_read,    -- 通过索引读取到的元组
    s.idx_tup_fetch AS tuples_fetched,-- 通过索引返回的元组
    pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
FROM pg_stat_all_indexes s
JOIN pg_indexes i
  ON s.schemaname = i.schemaname
 AND s.indexrelname = i.indexname
WHERE s.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY index_size DESC;

查看单个表的基本情况

\d <table_name>

查看单个表的表数据大小 + 索引大小 + TOAST 大小

SELECT
    c.relname AS table_name,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
    pg_size_pretty(pg_relation_size(c.oid))       AS data_size,
    pg_size_pretty(pg_indexes_size(c.oid))        AS indexes_size,
    pg_size_pretty(COALESCE(pg_relation_size(c.reltoastrelid), 0)) AS toast_size
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = '<schema>'          -- 如有需要改成你的 schema
  AND c.relname = '<table_name>'
  AND c.relkind = 'r';              -- 普通表

查看单个表的索引明细（名称，大小，是否唯一/定义）

-- 把 schema 和 table 名改成你的
WITH t AS (
  SELECT c.oid, n.nspname AS schema, c.relname AS table_name
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE n.nspname = '<schema>'          -- ← 改 schema
    AND c.relname = '<table_name>'      -- ← 改表名
    AND c.relkind IN ('r','m','f')    -- 普通表/物化视图/外部表
)
SELECT
  ic.relname                                  AS index_name,
  pg_relation_size(ic.oid)                    AS index_bytes,
  pg_size_pretty(pg_relation_size(ic.oid))    AS index_size,
  am.amname                                   AS access_method,
  idx.indisprimary                            AS is_primary,
  idx.indisunique                             AS is_unique,
  pg_get_indexdef(ic.oid)                     AS index_def
FROM t
JOIN pg_index idx   ON idx.indrelid = t.oid
JOIN pg_class ic    ON ic.oid = idx.indexrelid
JOIN pg_am am       ON am.oid = ic.relam
ORDER BY pg_relation_size(ic.oid) DESC;

回收空洞/ 解决空间膨胀问题

PostgreSQL 解决膨胀问题

离线回收

VACUUM FULL;

-- 推荐
VACUUM FULL <table_name>;

在线回收

需要使用到pg_repack或者pg_squeeze插件

pg_repack

pg_repack官方库

确认是否安装扩展

-- 每个数据库要使用时需要创建扩展
CREATE EXTENSION pg_repack;

执行调用

-- 外部调用
pg_repack  -h <host> -p <5432(默认端口)> -U <user_id> -d <database_name> --table <schema>.<table_name>

-- 内部运行（功能有限）
SELECT repack.repack_table('<schema>.<table_name>');

pg_squeeze

pg_repack官方库

配置 PostgreSQL

• 编辑 postgresql.conf：

shared_preload_libraries = 'pg_squeeze'
wal_level = logical
max_replication_slots = 4

• 重启PostgreSQL

确认是否安装扩展

CREATE EXTENSION pg_squeeze;

执行调用

-- 手动压缩
SELECT squeeze_table('<schema>', '<table_name>');

-- 自动压缩（配置任务）
INSERT INTO squeeze.tables (
  tabschema, tabname, schedule, free_space_extra, vacuum_max_age, max_retry
) VALUES (
  '<schema>', '<table_name>',
  ('{30}', '{22}', NULL, NULL, '{3,5}'),
  30, '2 hours', 2
);

free_space_extra：额外空洞比例阈值（不含 fillfactor 预留部分），默认 50%。
例如表 fillfactor=60，则正常就允许 40% 空。想在“空洞 ≥70%”触发，填 70-40=30。

vacuum_max_age：最近一次 VACUUM 之后多长时间内认为 FSM 仍新鲜（默认 1h）。

max_retry：遇到 DDL 干扰等失败时的最大重试次数（默认 0）

details…

运维管理

• 查看正在处理的任务

SELECT * FROM squeeze.get_active_workers();

• 查看历史与耗时

SELECT * FROM squeeze.log ORDER BY started DESC LIMIT 50;

• 查看失败原因

SELECT * FROM squeeze.errors LIMIT 50;

• 启停后台

SELECT squeeze.start_worker();
SELECT squeeze.stop_worker();

• 取消自动维护（取消注册）

DELETE FROM squeeze.tables WHERE tabschema='<schema>' AND tabname='<table_name>';