PostgreSQL 常用的命令

2022-04-01 09:25:33 浏览数 (1)

最近在学习PostgreSQL 技术体系，前面几篇文章介绍了 认识 PostgreSQL 基础权限体系，PostgreSQL 相关系统日志参数，今天学习一下常用的命令。

常用管理命令

1 查看用户信息

查看当前数据库所有的用户以及对应的权限

du

查看schema的相关信息

dn

在PG里面的schema和user有差异，用户是独立于 数据库，schema，表之外的，MySQL 中 user 基本等于 schema 。PG 默认登陆的是 指定数据库的 public schema 。可以使用 c 切换到对应的 schema下面

c schema_name user_name

验证当前的schema 是哪个？

show search_path ;

2 查看数据库信息

可以直接使用 l (小写字母L) 显示出所有的数据库来，字符集，

l

如果我们要切换到其他数据库， mysql 使用 use dbname; PG 使用

c dbname [username]

3 查看表相关信息

查看当前schema 里面所有的表

d

查看表结构

d tablename

查看哪些用户具有权限访问表

z tablename

代码语言：javascript复制
test=# z proctest
                              Access privileges
 Schema |   Name   | Type  | Access privileges | Column privileges | Policies
-------- ---------- ------- ------------------- ------------------- ----------
 public | proctest | table |                   |                   |
(1 row)
### 赋给 xx用户对表 proctest的select权限。

test=# grant select on proctest to xx; 
GRANT
test=# z proctest
                                   Access privileges
 Schema |   Name   | Type  |      Access privileges      | Column privileges | Policies
-------- ---------- ------- ----------------------------- ------------------- ----------
 public | proctest | table | test_user=arwdDxt/test_user |                   |
        |          |       | xx=r/test_user              |                   |
(1 row)

查看索引

di index_name

查看 参数

直接执行 show 参数名即可。

代码语言：javascript复制
postgresql=# show  log_statement;
 log_statement
---------------
 all
(1 row)
其他命令行

x命令的含义是:psql客户端每行仅仅显示一个字段以及该字段值. 类似 mysql 中的 G

代码语言：javascript复制
h：查看SQL命令的解释，比如h select。
?：查看psql命令列表。
e：打开文本编辑器。
conninfo：列出当前数据库和连接的信息。

常用 sql

查看执行计划
代码语言：javascript复制
postgresql=# explain select * from t1 where id=2;
                     QUERY PLAN
----------------------------------------------------
 Seq Scan on t1  (cost=0.00..41.88 rows=13 width=4)
   Filter: (id = 2)
(2 rows)
查看会话连接信息

select * from pg_stat_activity;

查看长时间运行的SQL
代码语言：javascript复制
SELECT datname,
       pid,
       usename,
       query_start,
       STATE,
       left(query,40) query,
       now()-query_start
FROM pg_stat_activity
WHERE STATE<>'idle'
  AND (backend_xid IS NOT NULL
       OR backend_xmin IS NOT NULL)
ORDER BY now()-query_start;
查看表的统计信息
代码语言：javascript复制
select relowner::regrole,relname,relkind,relpages,reltuples from pg_class where relname='t1';
查看膨胀高的表TOP 10，如果膨胀太高需要执行，例如 vacuum t1,vacuum不会锁表，vacuum full会锁表
代码语言：javascript复制
SELECT relname AS TABLE_NAME,
       pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) AS table_size,
       n_dead_tup,
       n_live_tup,
       (n_dead_tup * 100 / (n_live_tup   n_dead_tup))AS dead_tup_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup<>0  order by 5  desc LIMIT 10;
查看数据库用户连接数
代码语言：javascript复制
select datname,usename,state,count(*) from pg_stat_activity group by datname,usename,state order by 1,2,3,4;
查杀执行时间超过10分钟的sql
代码语言：javascript复制
select pg_terminate_backend(pid) from pg_stat_activity where   clock_timestamp()-query_start >  '10 min' and backend_type='client backend';
查杀超过10分钟的长事务会话
代码语言：javascript复制
select pg_terminate_backend(pid) from pg_stat_activity where   clock_timestamp()-xact_start >  '10 min' and backend_type='client backend';
数据库 postgresql sql 云数据库SQLServer

0 人点赞