收集postgresql数据库的SQL运维命令

mahan

登录客户端

psql -U postgres -h 127.0.0.1 -p 5432 -d postgres

进入 psql 后先执行格式化优化（固定模板）

\x auto;        -- 宽表自动分行展示
\timing on;     -- 显示SQL执行耗时
\pset border 2; -- 表格边框清晰

一、实例、全局数据库状态（对标 Oracle v$instance、GBase8s onstat -g glo）
1. 版本、启动时间、基础信息

-- 完整版本
SELECT version();
-- 实例启动时间
SELECT pg_postmaster_start_time() AS start_time, now() AS current_time;
-- 数据库集群数据目录
SHOW data_directory;

2. 查看所有库、模板库状态

SELECT datname, usename AS owner, encoding, datistemplate, datallowconn
FROM pg_database
ORDER BY datname;


3. 查看全局参数配置

SELECT name, setting, unit, short_desc
FROM pg_settings
ORDER BY name;

-- 单独查询关键参数
SHOW shared_buffers;
SHOW max_connections;
SHOW wal_level;


4. 启停 / 重载配置（操作系统 + 库内）

-- 重载配置文件，不重启库
SELECT pg_reload_conf();
-- 关闭数据库（操作系统执行）
pg_ctl stop -D /pgdata/data
-- 启动数据库
pg_ctl start -D /pgdata/data


二、内存、缓存、性能指标（对标 Oracle SGA/PGA、GBase8s onstat -p）
1. 核心内存参数

SELECT name, setting FROM pg_settings
WHERE name IN ('shared_buffers','work_mem','maintenance_work_mem','effective_cache_size');


2. 数据库缓存命中率（核心性能指标）

SELECT
  datname,
  sum(blks_hit) AS cache_hit,
  sum(blks_read) AS disk_read,
  ROUND(100.0 * sum(blks_hit) / (sum(blks_hit)+sum(blks_read)+1),2) AS hit_ratio
FROM pg_stat_database
GROUP BY datname;


3. 全局 IO 统计

SELECT * FROM pg_stat_bgwriter;


三、表空间、存储、大对象 LOB 管理（对标 Oracle 表空间、GBase8s dbspace/sbspace/blobspace）
PostgreSQL 核心特性：普通表、索引、大对象 lo 通用一套表空间，无强制拆分独立 LOB 空间

1. 查询全部表空间、物理路径、占用大小

SELECT
  spcname AS tablespace_name,
  pg_get_tablespace_location(oid) AS disk_path,
  pg_size_pretty(pg_tablespace_size(oid)) AS total_size
FROM pg_tablespace;


2. 查看数据库整体占用

SELECT
  datname,
  pg_size_pretty(pg_database_size(datname)) AS db_size
FROM pg_database;


3. 表、索引占用空间

-- 当前schema所有表大小
SELECT
  relname AS table_name,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_with_index
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;


4. 大对象 LOB 查看（pg_largeobject）

SELECT
  oid AS lob_id,
  pg_size_pretty(pg_large_object_size(oid)) AS lob_size
FROM pg_largeobject_metadata;


5. 创建 / 修改 / 删除表空间（纯 SQL）

-- 创建业务表空间
CREATE TABLESPACE ts_business LOCATION '/pgdata/tablespace_bus';

-- 指定表建到独立表空间
CREATE TABLE customer (id int) TABLESPACE ts_business;

-- 修改表所属表空间
ALTER TABLE customer SET TABLESPACE ts_business;

-- 删除空表空间
DROP TABLESPACE IF EXISTS ts_business;


四、用户、角色、权限管理（全 SQL 闭环）
1. 查询所有用户角色

SELECT usename, usesysid, usecreatedb, usesuper, passwd, valuntil
FROM pg_user;


2. 创建用户、设置密码、分配默认表空间

CREATE USER app_user WITH PASSWORD 'App@2026';
-- 设置默认表空间
ALTER USER app_user SET default_tablespace = ts_business;
-- 授予schema读写权限
GRANT USAGE, CREATE ON SCHEMA public TO app_user;
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO app_user;


3. 修改密码、锁定、删除用户

ALTER USER app_user WITH PASSWORD 'New@Pass123';
DROP USER IF EXISTS app_user CASCADE;


五、会话、锁、阻塞、活跃 SQL 排查（对标 GBase8s onstat -g ses /onstat -k）
1. 查询全部在线会话

SELECT
  pid, usename, datname, state, wait_event_type,
  query_start, now() - query_start AS run_time, query
FROM pg_stat_activity
ORDER BY run_time DESC;


2. 查找阻塞会话、锁等待链

SELECT
  pid,
  blocking_pid,
  usename,
  state,
  wait_event,
  now() - query_start AS wait_time,
  query
FROM pg_stat_activity
WHERE state = 'waiting';


3. 终止卡死 / 阻塞会话

-- 单个会话
SELECT pg_terminate_backend(1234);
-- 批量杀掉指定库空闲会话
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'testdb' AND state = 'idle';


4. 对象锁明细

SELECT relation::regclass, pid, mode, granted FROM pg_locks;


六、WAL 事务日志、归档管理（对标 Oracle redo 日志、GBase8s onstat -l）
1. WAL 基础配置查看

SELECT name, setting FROM pg_settings
WHERE name IN ('wal_level','max_wal_size','archive_mode','archive_command');


2. 当前 WAL 文件位置

SELECT pg_current_wal_lsn(), pg_current_wal_file();
-- 查看归档状态
SELECT pg_walfile_name(pg_current_wal_lsn());


3. 手动切换 WAL 日志

SELECT pg_switch_wal();


七、数据库对象：表、索引、约束查询
1. 查询当前业务 schema 所有表

SELECT tablename FROM pg_tables WHERE schemaname = 'public';


2. 查看表结构（psql 元命令）

\d customer;
-- SQL查询字段详情
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'customer';


3. 索引查询

SELECT
  tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public';


八、告警、等待事件、慢 SQL 统计（对标 Oracle v$system_event、GBase8s onstat -m）
1. 系统全局等待事件统计

SELECT event, total_wait, total_time
FROM pg_stat_wait_event
ORDER BY total_time DESC;


2. 慢 SQL 统计（需开启 pg_stat_statements 插件）

SELECT queryid, query, calls, total_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC;


3. 日志文件路径

SHOW log_directory;
SHOW log_filename;