postgrsql 常用命令
数据库基本查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14//显示服务器监听端口号
select inet_server_port();
//显示当前数据库
select current_database();
//显示当前的userid
select current_user;
//显示接受服务器的连接地址
select inet_server_addr();
//通过用户{role}连接
\c - {role}查看表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41postgres=# \d+ pg_stat_activity;
View "pg_catalog.pg_stat_activity"
Column | Type | Modifiers | Storage | Description
------------------+--------------------------+-----------+----------+-------------
datid | oid | | plain |
datname | name | | plain |
pid | integer | | plain |
usesysid | oid | | plain |
usename | name | | plain |
application_name | text | | extended |
client_addr | inet | | main |
client_hostname | text | | extended |
client_port | integer | | plain |
backend_start | timestamp with time zone | | plain |
xact_start | timestamp with time zone | | plain |
query_start | timestamp with time zone | | plain |
state_change | timestamp with time zone | | plain |
waiting | boolean | | plain |
state | text | | extended |
query | text | | extended |
View definition:
SELECT s.datid,
d.datname,
s.pid,
s.usesysid,
u.rolname AS usename,
s.application_name,
s.client_addr,
s.client_hostname,
s.client_port,
s.backend_start,
s.xact_start,
s.query_start,
s.state_change,
s.waiting,
s.state,
s.query
FROM pg_database d,
pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port),
pg_authid u
WHERE s.datid = d.oid AND s.usesysid = u.oid;