跳到主要内容

postgres常用命令

高频使用命令

psql -U postgres       -- 连接
\l                     -- 看库
\c dbname              -- 切库
\dt                    -- 看表
\d table               -- 看表结构
SELECT * FROM table;   -- 看数据
\q                     -- 退出

连接与基础信息​

连接 PostgreSQL​

psql -h <host> -p <port> -U <user> -d <db>

常见示例：

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

环境变量方式（推荐用于脚本）：

export PGHOST=127.0.0.1
export PGPORT=5432
export PGUSER=postgres
export PGPASSWORD=xxxx
psql -d mydb

查看当前连接信息​

SELECT current_database();
SELECT current_user;
SELECT inet_server_addr(), inet_server_port();

psql 交互命令（元命令）​

说明

以下命令 只在 psql 里有效，不是 SQL

帮助与退出​

\?      -- 查看所有 psql 命令
\h      -- 查看 SQL 语法帮助
\q      -- 退出 psql

数据库级别​

\l            -- 列出所有数据库
\l+           -- 带大小、权限
\c dbname     -- 切换数据库

Schema（模式）​

\dn           -- 查看 schema
\dn+          -- 查看 schema 详细信息

表 / 视图 / 序列​

\dt           -- 当前 schema 下的表
\dt *.*       -- 所有 schema 下的表
\dt+          -- 表大小等信息

\dv           -- 视图
\ds           -- 序列

表结构​

\d table_name
\d+ table_name   -- 包含表大小、存储方式

索引、函数、触发器​

\di           -- 索引
\df           -- 函数
\dtg          -- 触发器

用户与权限管理​

查看用户 / 角色​

\du
\du+

等价 SQL：

SELECT * FROM pg_roles;

创建 / 删除用户​

CREATE USER app_user WITH PASSWORD 'xxx';
DROP USER app_user;

创建可登录用户（推荐）：

CREATE ROLE app_user LOGIN PASSWORD 'xxx';

授权​

GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO app_user;

对未来表生效：

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO app_user;

数据库与表操作​

数据库​

CREATE DATABASE mydb;
DROP DATABASE mydb;

表​

CREATE TABLE demo (
  id SERIAL PRIMARY KEY,
  name TEXT,
  created_at TIMESTAMP DEFAULT now()
);

DROP TABLE demo;
TRUNCATE TABLE demo;

表大小​

\dt+ demo

SELECT
  pg_size_pretty(pg_total_relation_size('demo'));

数据查询与调试​

基础查询​

SELECT * FROM table;
SELECT count(*) FROM table;

查看执行计划​

EXPLAIN SELECT * FROM table;
EXPLAIN ANALYZE SELECT * FROM table;

慢 SQL 定位​

SELECT pid, query, state, now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

连接与会话管理（非常重要）​

查看当前连接​

SELECT * FROM pg_stat_activity;

常用字段：

  • pid
  • usename
  • datname
  • state
  • query

杀连接​

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid();

最大连接数​

SHOW max_connections;

事务与锁​

事务​

BEGIN;
COMMIT;
ROLLBACK;

查看锁​

SELECT * FROM pg_locks;

常用锁分析：

SELECT
  a.pid,
  a.query,
  l.mode,
  l.granted
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid;

备份与恢复（生产必会）​

逻辑备份​

pg_dump -h host -U user dbname > db.sql

只备份结构：

pg_dump -s dbname > schema.sql

恢复​

psql -d dbname < db.sql

整库备份​

pg_dumpall > all.sql

系统视图（排障利器）​

表统计​

SELECT * FROM pg_stat_user_tables;

索引使用率​

SELECT
  relname,
  idx_scan,
  seq_scan
FROM pg_stat_user_tables
ORDER BY idx_scan ASC;

表膨胀判断​

SELECT
  relname,
  n_dead_tup,
  n_live_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

维护命令​

VACUUM;                -- 清理死元组，回收表内部空间，防止表膨胀（不更新统计）
VACUUM ANALYZE;        -- 清理死元组 + 更新统计信息，日常维护首选
ANALYZE;               -- 仅更新统计信息，帮助优化器生成更优执行计划
REINDEX TABLE table_name; -- 重建表的所有索引，解决索引膨胀或损坏（会锁表，慎用）
命令清垃圾更新统计重建索引是否锁表常用程度
VACUUM✅⚠️ 少量❌❌⭐⭐⭐
ANALYZE❌✅❌❌⭐⭐⭐
VACUUM ANALYZE✅✅❌❌⭐⭐⭐⭐⭐
REINDEX❌❌✅⚠️ 是⭐⭐
Bottom GIF
Top GIF