登录客户端

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

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

sql

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

一、实例、全局数据库状态（对标 Oracle v$instance、GBase8s onstat -g glo）

1. 版本、启动时间、基础信息

sql

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

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

sql

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

3. 查看全局参数配置

sql

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

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

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

sql

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

二、内存、缓存、性能指标（对标 Oracle SGA/PGA、GBase8s onstat -p）

1. 核心内存参数

sql

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

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

sql

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 统计

sql

SELECT * FROM pg_stat_bgwriter;

三、表空间、存储、大对象 LOB 管理（对标 Oracle 表空间、GBase8s dbspace/sbspace/blobspace）

PostgreSQL 核心特性：普通表、索引、大对象 lo 通用一套表空间，无强制拆分独立 LOB 空间

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

sql

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. 查看数据库整体占用

sql

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

3. 表、索引占用空间

sql

-- 当前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）

sql

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

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

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. 查询所有用户角色

sql

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

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

sql

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. 修改密码、锁定、删除用户

sql

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

五、会话、锁、阻塞、活跃 SQL 排查（对标 GBase8s onstat -g ses /onstat -k）

1. 查询全部在线会话

sql

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. 查找阻塞会话、锁等待链

sql

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

3. 终止卡死 / 阻塞会话

sql

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

4. 对象锁明细

sql

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

六、WAL 事务日志、归档管理（对标 Oracle redo 日志、GBase8s onstat -l）

1. WAL 基础配置查看

sql

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

2. 当前 WAL 文件位置

sql

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

3. 手动切换 WAL 日志

sql

SELECT pg_switch_wal();

七、数据库对象：表、索引、约束查询

1. 查询当前业务 schema 所有表

sql

SELECT tablename FROM pg_tables WHERE schemaname = 'public';

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

sql

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

3. 索引查询

sql

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

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

1. 系统全局等待事件统计

sql

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

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

sql

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

3. 日志文件路径

sql

SHOW log_directory;
SHOW log_filename;

PostgreSQL SQL 运维体系核心优势

1. 统一 SQL 入口：表空间、权限、会话、存储扩容、参数重载全部库内 SQL 完成，无大量外置独立命令；
2. LOB 无强制隔离：不需要提前创建专属大对象存储空间，开箱即用，无前置运维成本；
3. 全结构化视图：pg_stat_*、pg_tablespace、pg_locks 等输出标准二维表格，脚本、监控平台可直接采集；
4. 轻量化极简设计：语法简洁，无 Oracle 复杂 v$ 数据字典体系，学习成本更低；
5. 云原生友好：容器、K8s、Prometheus 监控原生适配，指标标准化；
6. 内置性能观测体系：缓存命中率、锁、慢 SQL、WAL 状态全部内置视图，无需文本解析。