PostgreSQL里面的一些命令小结

***里面的一些命令小结

近两天总结了下***的基本操作命令，对***也有了一个基本的认识。

***的功能还是很丰富的，有序列，支持db link，基本***里有的概念它这里也有，目前来看不支持package。

风格和***也类似，没有MySQL中快捷方便的show create table 这样的语句。

从我的使用习惯来说，我基本关注以下的一些方面。

查看数据库的配置查看用户信息查看会话连接信息show tables的类似方法用户的权限查看建表语句表空间信息对象存储信息查看锁的信息查看数据库参数显示数据库的运行状态查看数据字典的信息查看索引的信息查看执行计划查看存储过程存储过程的调度执行事务隔离级别

1.查看数据库的配置

可以直接使用\l 选项列出所有的数据库来，字符集，基本的配置都一目了然，有点*** 12c中的show pdbs的感觉。

从进程情况来看，PG是多进程多线程的架构设计。

如果查看当前数据库，可以使用current_database()。

postgres=# select current_database();   current_database   ------------------   postgres

2.查看用户信息

可以使用\dn来得到schema的相关信息，在PG里面的schema和user还是有一些差别，在其他数据库schema基本就是user了。

postgres-# \dn    List of schemas    Name  |  Owner    --------+----------   public | postgres

我们创建一个schema,然后使用\dn来查看。

postgres=# create schema jeanron100;  CREATE SCHEMA  postgres=# \dn      List of schemas      Name    |  Owner   ------------+----------   jeanron100 | postgres   public     | postgres

或者使用数据字典pg_authid来查看。

postgres=# select *from pg_authid;

关于schema的概念，我们可以创建一个表test,

postgres=# create table test(id int);  CREATE TABLE

可以看到这个是一个public的schema

postgres=# \d          List of relations   Schema | Name | Type  |  Owner     --------+------+-------+----------   public | test | table | postgres

如果使用\d来查看字段信息，结果如下：

postgres=# \d test       Table "public.test"   Column |  Type   | Modifiers   --------+---------+-----------   id     | integer |

还可以使用pg_users来查看，比如我创建了一个用户replica，就会有相应的配置。

select *from pg_user;   usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig   ----------+----------+-------------+----------+---------+--------------+----------+----------   postgres |       10 | t           | t        | t       | t            | ******** |          |    replica  |    16384 | f           | f        | t       | f            | ******** |          |

查看当前的schema信息，可以使用current_schema()

3.查看会话连接信息

如果查看PG中的会话信息，可以使用select * from pg_stat_activity;

如果新增了一个连接，开启了一个会话，在服务端是会有一个影子进程存在的。可以根据pid找到对应的会话。包括执行的SQL都可以看到，如果有多个会话，就是多条记录。

postgres=# select * from pg_stat_activity;  -[ RECORD 1 ]----+--------------------------------  datid            | 13241  datname          | postgres  pid              | 20644  usesysid         | 10 usename          | postgres  application_name | psql  client_addr      |   client_hostname  |   client_port      | -1  backend_start    | 2018-03-25 05:38:16.988057+08  xact_start       | 2018-03-25 05:48:08.113649+08  query_start      | 2018-03-25 05:48:08.113649+08  state_change     | 2018-03-25 05:48:08.113653+08  waiting          | f  state            | active  backend_xid      |   backend_xmin     | 1753  query            | select * from pg_stat_activity;

4.show tables的类似方法

PG里面暂时没有找到show tables这种的快捷方式，目前发现有两类方式。

一种是通过数据字典pg_tables来查看，相当于***里面的all_tables

或者是使用information_schema里面的tables来查看。

postgres=# select *from information_schema.tables;  postgres=# select *from pg_tables;

PG里面的information_schema比较特别，在数据库中直接\l无法看到，但是确确实实存在，着数据字典风格和MySQL很相似。

5.用户的权限查看

查看权限可以使用\dp来完成，或者等价的命令\z来实现。

postgres=# \dp                              Access privileges   Schema | Name | Type  | Access privileges | Column privileges | Policies   --------+------+-------+-------------------+-------------------+----------   public | test | table |                   |                   |

6.建表语句

建表语句，目前还没有发现show create table这种快捷的方式，不过可以通过pg_dump或者根据数据字典的信息来拼接了。

7.表空间信息

表空间的部分相对比较清晰，可以直接使用\db来完成。

postgres=# \db         List of tablespaces      Name    |  Owner   | Location   ------------+----------+----------   pg_default | postgres |    pg_global  | postgres |

或者使用pg_tablespace

postgres=# select *from pg_tablespace;      spcname   | spcowner | spcacl | spcoptions   ------------+----------+--------+------------   pg_default |       10 |        |    pg_global  |       10 |        |

8.对象存储信息

这部分信息可以参考pg_tables，还有一些更细节的

更多的细节还有待求证和发现。

9.查看锁的信息

查看锁的信息可以使用pg_locks来得到。

postgres=# select *from pg_locks;  -[ RECORD 1 ]------+----------------  locktype           | relation  database           | 13241  relation           | 11673  page               |  tuple              |   virtualxid         |   transactionid      |   classid            |   objid              |   objsubid           |   virtualtransaction | 4/81  pid                | 20644  mode               | AccessShareLock  granted            | t  fastpath           | t

10.查看数据库参数

这部分的功能不是很理解，因为没有找到很便捷的方式。

比如查看缓存的设置

postgres=#  show shared_buffers;  -[ RECORD 1 ]--+------  shared_buffers | 128MB

或者根据参数文件postgresql.conf来查看。

11.显示数据库的运行状态

这个信息毫无疑问，建议还是从pg_stats_activity来查看。

12.查看数据字典的信息

这应该是本小节的重点，通过查看视图可以看到，有100多个视图。

postgres=# select count(*)from pg_views;  -[ RECORD 1 ]  count | 112

还可以使用information_schema中的信息来补充。

13.查看索引的信息

查看索引的信息，可以使用\di来完成，非常快捷。

14.查看执行计划

查看执行计划一般可以根据explain来得到，但是还有几类方法，对结果做格式化处理，比如转化为json或者xml的格式等。

postgres=# explain select *from test;  -[ RECORD 1 ]------------------------------------------------------  QUERY PLAN | Seq Scan on test  (cost=0.00..35.50 rows=2550 width=4)  得到json格式的执行计划。  postgres=# explain(format json) select *from test;  -[ RECORD 1 ]------------------------------  QUERY PLAN | [                             +             |   {                           +             |     "Plan": {                 +             |       "Node Type": "Seq Scan",+             |       "Relation Name": "test",+             |       "Alias": "test",        +             |       "Startup Cost": 0.00,   +             |       "Total Cost": 35.50,    +             |       "Plan Rows": 2550,      +             |       "Plan Width": 4         +             |     }                         +             |   }                           +             | ]

或者做一些分析，能够得到更细节的执行信息。

postgres=# explain analyze select *from test;                                              QUERY PLAN                                         --------------------------------------------------------------------------------------------------   Seq Scan on test  (cost=0.00..35.50 rows=2550 width=4) (actual time=0.001..0.001 rows=0 loops=1)   Planning time: 0.018 ms   Execution time: 0.009 ms

15.查看存储过程

查看存储过程就是比较单薄的。可以直接使用pg_proc来得到详细的信息。

pg_proc

16.存储过程的调度执行

目前没有看到很直接的方式，这部分感觉还不够强大。

17.事务隔离级别

根据公司现状和业务规模的不断扩大，其实技术上也是不断地改进和积累，事务方面的处理也是如此，等规模达到了一定的量级，这部分的要求就会很明确。所以很多开发同学对于锁机制都很感兴趣。

查看事务隔离级别的两种SQL语句。

查看当前的事务隔离级别设置。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。