极客油画

pg常用查询

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

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

  • VACUUM ANALYZE主要关注优化查询性能，而 VACUUM FREEZE的核心任务是防止数据库因事务ID耗尽而宕机。

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

  • 切到对应数据库后，通过base/xxx/目录下的文件名查看对应的数据表： SELECT oid, relname, relkind FROM pg_class WHERE oid = 22899 or relfilenode = 22899 ;

  • 表名可以反向查找文件路径： SELECT pg_relation_filepath('schema_name.table_name');

pg_filedump这个工具可以查看“已损坏”数据文件的内容。

  • 查看所有的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的记录
  • 生产环境最佳实践，先备份权限和全局对象（pg_dumpall –globals-only），再高效备份每个数据库（为每个库用 pg_dump –format c）。
# dump 过滤掉指定的数据表
PGPASSWORD=pg密码  ./pg_dump -h 127.0.0.1 -p 5432 -U postgres --exclude-table=table_name -d db_name -w --format c --file db_name_20350620.dump

# 一次性dump所有库
# linux
export PGPASSWORD=
psql -U username -d postgres -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname NOT IN ('postgres')" | xargs -I {} pg_dump -h 127.0.0.1 -p 5432 -U postgres -d {} -w --format c --create --file {}_backup.dump
# windows powershell
$env:PGPASSWORD = 
psql -U username -d postgres -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname NOT IN ('postgres')" | ForEach-Object { $db = $_.Trim(); if ($db) { Write-Host "备份: $db"; pg_dump -h 127.0.0.1 -p 5432 -U postgres -d $db -w --format c --create --file "${db}_backup.dump" } }

  • 跨版本升级或需要人工审阅SQL还是需要plain sql的。

    • dumpall 过滤掉指定的数据库：PGPASSWORD=pg密码 ./pg_dumpall -h 127.0.0.1 -p 5432 -U postgres –exclude-database=‘template*’ -w -f all_db_dump_20250620.sql
    • pg_dumpall的工作流程：备份全局对象-> 获取数据库列表-> 为每个数据库调用 (pg_dump -Fp --create) -> 将生成的全局对象 SQL 和为每个数据库生成的 SQL 按正确的逻辑顺序组合在一起。
    • pg_dump -Fp 当索引和数据矛盾时，会丢弃有问题的索引定义，只保留数据，以保证./psql命令执行这些sql的时候不会报错。
    • pg_dump -Fc 当索引和数据矛盾时，会同时保留索引定义和数据，pg_restore的时候会先插入数据再创建索引，创建有问题的索引时会报错，但不会中断整个恢复过程。

  • 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();
  • 表重建
-- 1. 创建一张结构相同的新表
CREATE TABLE 新表名 (LIKE 原表名 INCLUDING ALL);

-- 2. 将数据从原表插入新表
INSERT INTO 新表名 SELECT * FROM 原表名;

-- 3. 删除有问题的原表
DROP TABLE 原表名;

-- 4. 将新表重命名为原表名
ALTER TABLE 新表名 RENAME TO 原表名;

  • \t \a \x 的区别

    • \t - 切换是否显示列标题/表头
    • \a - 切换对齐模式（切换是否使用“整齐的表格格式”）
    • \x - 切换扩展显示模式（每列单独一行显示）
    • \pset - 显示所有格式设置状态

  • pg_resetwal是最后的手段

    • 如果有最新的物理备份（基础备份）和连续的 WAL 归档，优先使用 Point-in-Time Recovery (PITR)​ 进行恢复
    • 完整备份数据目录
    • 使用 -n或 –dry-run选项，仔细分析输出，特别是它建议设置的新事务 ID（-x）和新 OID（-O） 
      pg_control version: 1300
Latest checkpoint's NextXID: 0:748
Latest checkpoint's NextOID: 12345
建议的重置值：
NextXID: 0:749
NextOID: 12346
    • -x和-O参数，强烈建议使用pg_resetwal -n -D /var/lib/pgsql/data提供的值，或者设置得比它提供的值更高一点，但绝不能更低 
      # 不要使用 -f（强制）选项
$ sudo -u postgres pg_resetwal -D /var/lib/pgsql/data -x <calculated_next_xid> -O <calculated_next_oid>
    • pg_resetwal之后，先对数据库执行vacuum full，再对数据库执行reindex 
      $ ./vacuumdb --all --full -h 127.0.0.1 -U postgres -p 5432
$ ./reindexdb --all -h 127.0.0.1 -U postgres -p 5432

  • 单引号和双引号的区别

    • 双引号("")：用于引用标识符（如表名、列名等）。默认情况下，PostgreSQL会自动将未用双引号括起来的标识符转换为小写处理。
    • 单引号(’’)：主要用于表示字符串常量。

  • pg的schema概念有点像命名空间或者把它想象成一个文件系统中的目录，差别就是这个schema下不能再有schema嵌套。

    • 各个对象比如表、函数等存放在各个schema下，同一个schema下不能有重复的对象名字，但在不同schema下可以重复。
    • 所有的数据库，都有一个public的schema，相当于默认命名空间。
    • schema是database之下的一个概念，起到namespace的概念

  • user是带有登录权限的role

    • 官网上的描述是：CREATE USER is now an alias for CREATE ROLE. The only difference is that when the command is spelled CREATE USER, LOGIN is assumed by default, whereas NOLOGIN is assumed when the command is spelled CREATE ROLE.
    • 也就是说role和user区别不大，create user默认有login权限，而create role没有

  • 给表的某个字符串类型的字段创建btree索引时，如果某一行中该字段的字符串长度超过2704，就会报错：index row size 2768 exceeds btree version 4 maximum 2704

    • pg的这个限制，是硬限制，因为btree分叉，少于3条就会不够分（BLCKSZ / 3 = 每个索引条目的最大允许大小，8k / 3 ≈ 2704）。除非在编译pg时，–with-blocksize参数设置为16k或者32k，默认8k。
    • 如果插入的数据超过了索引的长度限制2704字节，会发生什么？这条数据会插入失败

  • sql in语句的参数占位符的数量是有限的，超过了，就会报错：pg: got 94353 parameters but postgresql only supports 65535 parameters

    • 这里的 parameters​ 指的是 SQL 语句中传入的参数占位符的数量，而不是 SQL 语句的总字符数。

  • data/global目录下存放的文件用于存储全局的系统表信息和全局控制信息。global下有四种文件：

    • pg_control 用于存储全局控制信息
    • pg_filenode.map 用于将当前目录下系统表的OID与具体文件名进行硬编码映射（每个用户创建的数据库目录下也有同名文件）。
    • pg_internal.init 用于缓存系统表，加快系统表读取速度（每个用户创建的数据库目录下也有同名文件）。
    • 全局系统表文件 数字命名的文件，用于存储系统表的内容。它们在pg_class里的relfilenode都为0，是靠pg_filenode.map将OID与文件硬编码映射。（注：不是所有的系统表的relfilenode都为0） 
      data
├── global                # under global, all the filenode is hard-code(select oid,relname,relfilenode from pg_class where relfilenode=0 order by oid;)
│   ├── 1136              # pg_pltemplate
│   ├── 1137              # pg_pltemplate_name_index
│   ├── 1213              # pg_tablespace
│   ├── 1214              # pg_shdepend
│   ├── 1232              # pg_shdepend_depender_index
│   ├── 1233              # pg_shdepend_reference_index
│   ├── 1260              # pg_authid
│   ├── 1261              # pg_auth_members
│   ├── 1262              # pg_database
│   ├── 2396              # pg_shdescription
│   ├── 2397              # pg_shdescription_o_c_index
│   ├── 2671              # pg_database_datname_index
│   ├── 2672              # pg_database_oid_index
│   ├── 2676              # pg_authid_rolname_index
│   ├── 2677              # pg_authid_oid_index
│   ├── 2694              # pg_auth_members_role_member_index
│   ├── 2695              # pg_auth_members_member_role_index
│   ├── 2697              # pg_tablespace_oid_index
│   ├── 2698              # pg_tablespace_spcname_index
│   ├── 2846              # pg_toast_2396
│   ├── 2847              # pg_toast_2396_index
│   ├── 2964              # pg_db_role_setting
│   ├── 2965              # pg_db_role_setting_databaseid_rol_index
│   ├── 2966              # pg_toast_2964
│   ├── 2967              # pg_toast_2964_index
│   ├── 3592              # pg_shseclabel
│   ├── 3593              # pg_shseclabel_object_index
│   ├── 4060              # pg_toast_3592x
│   ├── 4061              # pg_toast_3592_index
│   ├── 6000              # pg_replication_origin
│   ├── 6001              # pg_replication_origin_roiident_index
│   ├── 6002              # pg_replication_origin_roname_index
│   ├── pg_control        # global control file, use pgcheck -pc to see it.
│   ├── pg_filenode.map   # system table (oid -> filenode) mapping file, use pgcheck -pm to see it.
│   └── pg_internal.init  # system table cache file, use pgcheck -pr to see it.

  • postgres数据库中，base/*/2601 文件为何都是一样的

    • 2601名为pg_am的系统表的物理文件。这个表存储了访问方法（Access Method）的元数据信息。
    • 访问方法是数据存储和检索的抽象接口，内置的访问方法有：
      • 表级访问方法（amtype = ’t’）：
        • heap：默认的表存储方法（堆表）
        • 在 PostgreSQL 12+ 中，可以通过 CREATE TABLE … USING指定其他表访问方法（如 zheap、zedstore等，如果已安装插件）
      • 索引级访问方法（amtype = ‘i’）：
        • btree：B-树索引（默认索引类型，支持排序、范围查询）
        • hash：哈希索引（等值查询）
        • gist：广义搜索树（支持地理数据、全文搜索等）
        • gin：广义倒排索引（用于数组、JSON、全文搜索）
        • spgist：空间分区 GiST（用于空间数据、文本前缀等）
        • brin：块范围索引（用于有序大表）
        • bloom：布隆过滤器索引（多列等值查询）
    • 类似的还是有 pg_class（1259 文件）和其他与单个数据库相关的系统表。 
      -- 查看 2601 文件对应哪个系统表
SELECT relname FROM pg_class WHERE relfilenode = 2601;
-- 应该返回：pg_am

select * from pg_am;
 oid  | amname |      amhandler       | amtype
------+--------+----------------------+--------
    2 | heap   | heap_tableam_handler | t
  403 | btree  | bthandler            | i
  405 | hash   | hashhandler          | i
  783 | gist   | gisthandler          | i
 2742 | gin    | ginhandler           | i
 4000 | spgist | spghandler           | i
 3580 | brin   | brinhandler          | i

专题: pgsql

本文发表于 2022-01-01，最后修改于 2022-01-01。

本站永久域名「 jiavvc.top 」，也可搜索「 极客油画 」找到我。

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

赞赏支持

请我吃鸡腿 =^_^=

i ysf

云闪付

i wechat

微信

推荐阅读

位图

发表于2021-07-17， 阅读约1分钟

Untitled

发表于0001-01-01， 阅读约17分钟

Untitled

发表于0001-01-01， 阅读约2分钟

Big Image