pg常用查询

• 当\dp命令报invalid page in block 1 of relation base/xxx/xxx的错误，可以执行如下命令解决

sql> REINDEX SYSTEM database_name;

该命令会重建指定数据库的系统目录表索引

• 通过base/目录下的文件名查看对应的数据库： select oid,datname from pg_database where oid=16384;

• 查看所有的namespace，namespace是schema的超集: select * from pg_namespace;

• 查看所有数据库的大小，并按大小排序：

  SELECT datname AS database_name, pg_size_pretty(pg_database_size(datname)) AS size
  FROM pg_database
  ORDER BY pg_database_size(datname) DESC;
  

• 查看当前数据库中所有表的大小：

  SELECT table_schema || '.' || table_name AS table_full_name,
      pg_size_pretty(pg_total_relation_size(table_schema || '.' || table_name)) AS total_size
  FROM information_schema.tables
  WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_total_relation_size(table_schema || '.' || table_name) DESC;
  

• 查看当前数据库中所有表的膨胀率：

  SELECT
      schemaname||'.'||relname as table_name,
      pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
      n_dead_tup,
      n_live_tup,
      round(n_dead_tup * 100 / (n_live_tup + n_dead_tup),2) AS dead_tup_ratio
  FROM
      pg_stat_all_tables
  WHERE
      n_dead_tup >= 1000
  ORDER BY dead_tup_ratio DESC
  LIMIT 20;
  

• 在postgres数据库上查看慢sql：

  select
      calls, max_time, mean_time, rows, left(regexp_replace(query, '[\n\t\s]+', ' ', 'g'), 100) as sql
  from
      pg_stat_statements
  order by mean_time desc
  limit 10;
  

• 实时监控所有数据库的慢查询：

  SELECT
    pid,
    now() - query_start AS duration,
    left(regexp_replace(query, '[\n\t\s]+', ' ', 'g'), 100) as sql,
    state,
    client_addr,
    application_name
  FROM pg_stat_activity
  WHERE state = 'active'
    AND now() - query_start > interval '1 second'
  ORDER BY duration DESC;
  

• 根据表名查数据库名的脚本：

  1 2 3 4 5 6 7 8 9 10 11

  #!/bin/bash export PGPASSWORD=NgYusAWLegezy5UP DB_LIST=$(./psql -U postgres -w -h 127.0.0.1 -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;") TABLE_NAME="$1" for DB in $DB_LIST; do EXISTS=$(./psql -U postgres -w -h 127.0.0.1 -d "$DB" -t -c "SELECT 1 FROM pg_tables WHERE tablename = '$TABLE_NAME';") if [ "${EXISTS:-0}" -eq "1" ]; then echo "表 $TABLE_NAME 存在于数据库: $DB" fi done

• PostgreSQL中删除重复数据但保留每组中时间最小的行:

DELETE FROM your_table
WHERE ctid IN (
    SELECT ctid FROM (
        SELECT 
            ctid,
            ROW_NUMBER() OVER (PARTITION BY 分组字段 ORDER BY 时间字段) AS rn
        FROM your_table
    ) t 
    WHERE t.rn > 1
);

-- 通过窗口函数为每组重复数据分配行号，然后删除行号大于1的记录

• dumpall 过滤掉指定的数据库：PGPASSWORD=pg密码 ./pg_dumpall -h 127.0.0.1 -p 5432 -U postgres --exclude-database='template*' -w -f all_db_dump_20250620.sql

• dump 过滤掉指定的数据表： PGPASSWORD=pg密码 ./pg_dump -h 127.0.0.1 -p 5432 -U postgres --exclude-table=table_name -d db_name -w -f db_name_20350620.sql

• ddl之新增索引：

ALTER TABLE table_name
ADD CONSTRAINT index_name
UNIQUE (client_id, asset_id, asset_oid, module, name);

ALTER TABLE table_name
ADD CONSTRAINT index_name
PRIMARY KEY (_id);

• idle进程被强制杀死时，如果其下有正在insert的语句，就会导致pg数据损坏：

> \t
Tuples only is on.
> SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE state = 'idle' AND pid <> pg_backend_pid();

上一篇 « pg为何需要HOT 下一篇 » LSM-Tree