跳转至

常用系统管理命令

查看系统信息

查看当前数据库实例版本

1
2
3
4
5
postgres=# select version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 12.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

查看数据库启动时间

1
2
3
4
5
postgres=# select pg_postmaster_start_time();
    pg_postmaster_start_time    
--------------------------------
 16/11/2023 05:27:36.234696 CET
(1 row)

显示当前数据库时区

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
postgres=# show timezone;
 TimeZone 
----------
 CET
(1 row)
postgres=# select now();
              now               
--------------------------------
 17/11/2023 01:11:54.254751 CET
(1 row)

数据库时区是操作系统时区有时并不一致

查看当前用户

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
postgres=# select session_user;
 session_user 
--------------
 postgres
(1 row)

postgres=# select user;
   user   
----------
 postgres
(1 row)

显示当前数据库

1
2
3
4
5
tpcc=# select current_catalog, current_database();
 current_catalog | current_database 
-----------------+------------------
 tpcc            | tpcc
(1 row)

查看当前数据库的IP地址和端口

1
2
3
4
5
postgres=# select inet_server_addr(), inet_server_port();
 inet_server_addr | inet_server_port 
------------------+------------------
 127.0.0.1        |             5432
(1 row)

查看当前session的后台服务进程ID

1
2
3
4
5
postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
          40997
(1 row)

查看数据库大小

1
2
3
4
5
postgres=# select pg_size_pretty(pg_database_size('tpcc'));
 pg_size_pretty 
----------------
 7953 kB
(1 row)

查看表的大小

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
-- 查看表的大小，不包含索引
benchmark=# select pg_size_pretty(pg_relation_size('bmsql_order_line'));
 pg_size_pretty 
----------------
 569 MB
(1 row)
-- 查看表和索引的大小
benchmark=# select pg_size_pretty(pg_total_relation_size('bmsql_order_line'));
 pg_size_pretty 
----------------
 779 MB
(1 row)
-- 查看索引大小
benchmark=# select pg_size_pretty(pg_indexes_size('bmsql_order_line'));
 pg_size_pretty 
----------------
 210 MB
(1 row)