PostgreSQL 常用管理命令

1.1 查看系统信息的常用命令

1.1.1查看当前数据库实例的版本信息，命令如下：

[maxwell@MaxwellDBA ~]$ psql -d maxwelldb
psql (12.9)
Type "help" for help.

maxwelldb=# select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.9 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit
(1 row)

maxwelldb=# 

1.1.2查看数据库的启动时间，命令如下：

maxwelldb=# select pg_postmaster_start_time();
   pg_postmaster_start_time    
-------------------------------
 2022-08-30 13:09:57.389012+08
(1 row)

maxwelldb=# 

1.1.3 查看最后load配置文件的时间，命令如下：

maxwelldb=# select pg_conf_load_time();
       pg_conf_load_time       
-------------------------------
 2022-08-30 13:09:57.371297+08
(1 row)

maxwelldb=# 

1.1.4 使用pg_ctl reload后会改变配置的装载时间，命令如下：

osdba@db01:~$ pg_ctl reload  
server signaled osdba@db01:~$ psql 
psql (9.3.2) Type "help" for help.  
osdba=# select pg_conf_load_time();        
pg_conf_load_time        
-------------------------------   
2014-07-19 09:36:06.292696+00 
(1 row) 

1.1.5 显示当前数据库时区，命令如下：

maxwelldb=# show timezone;
   TimeZone    
---------------
 Asia/Shanghai
(1 row)

maxwelldb=# 

注意，数据库的时区有时并不是当前操作系统的时区，此时在数据库中看到的时间就与在操作系统中看到的不一致，示例如下：

maxwelldb=# select now();
              now              
-------------------------------
 2022-08-30 14:58:29.949507+08
(1 row)

maxwelldb=#

1.1.6 查看当前实例中有哪些数据库，命令如下：

[maxwell@MaxwellDBA ~]$ psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 maxwell   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 maxwelldb | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)

[maxwell@MaxwellDBA ~]$ psql -d maxwelldb
psql (12.9)
Type "help" for help.

maxwelldb=# \
invalid command \
Try \? for help.
maxwelldb=# 
maxwelldb=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 maxwell   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 maxwelldb | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)

maxwelldb=# 

1.1.7 查看当前用户名，命令如下：

maxwelldb=# select user;
  user   
---------
 maxwell
(1 row)

maxwelldb=# select current_user;
 current_user 
--------------
 maxwell
(1 row)

maxwelldb=#

上例中使用current_user与使用user的结果是完全相同的。

1.1.8 查看session用户，命令如下：

maxwelldb=# select session_user;
 session_user 
--------------
 maxwell
(1 row)

maxwelldb=#

注意，通常情况下“session_user”与“user”是相同的。但当用命令“SET ROLE”改变用户的角色时，这两者就不同了，示例如下：

maxwelldb=# set role postgres;
SET
maxwelldb=# select session_user;
 session_user 
--------------
 maxwell
(1 row)

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

maxwelldb=#

1.1.9 查询当前连接的数据库名称，命令如下：

maxwelldb=# select current_catalog,current_database();
 current_catalog | current_database 
-----------------+------------------
 maxwelldb       | maxwelldb
(1 row)

maxwelldb=# 

注意，使用current_catalog与current_database()都显示当前连接的数据库名称，两者的功能完全相同，只不过catalog是SQL标准中的用语。

1.1.10 查询当前session所在客户端的IP地址及端口，命令如下：

maxwelldb=# select inet_client_addr(),inet_client_port();
 inet_client_addr | inet_client_port 
------------------+------------------
                  |                 
(1 row)

maxwelldb=# 

1.1.11 查询当前数据库服务器的IP地址及端口，命令如下：

maxwelldb=# select inet_server_addr(),inet_server_port();
 inet_server_addr | inet_server_port 
------------------+------------------
                  |                 
(1 row)

maxwelldb=#

1.1.12 查询当前session的后台服务进程的PID，命令如下：

maxwelldb=# select pg_backend_pid();
 pg_backend_pid 
----------------
         341331
(1 row)

maxwelldb=# 

1.1.13 通过操作系统命令查看此后台服务进程，命令如下：

[maxwell@MaxwellDBA ~]$ ps -ef|grep 340540 | grep -v grep
postgres  340540       1  0 13:09 ?        00:00:00 /usr/bin/postmaster -D /var/lib/pgsql/data
postgres  340541  340540  0 13:09 ?        00:00:00 postgres: logger   
postgres  340543  340540  0 13:09 ?        00:00:00 postgres: checkpointer   
postgres  340544  340540  0 13:09 ?        00:00:00 postgres: background writer   
postgres  340545  340540  0 13:09 ?        00:00:00 postgres: walwriter   
postgres  340546  340540  0 13:09 ?        00:00:00 postgres: autovacuum launcher   
postgres  340547  340540  0 13:09 ?        00:00:00 postgres: stats collector   
postgres  340548  340540  0 13:09 ?        00:00:00 postgres: logical replication launcher   
[maxwell@MaxwellDBA ~]$ 

1.1.14 查看当前参数配置情况，命令如下：

[maxwell@MaxwellDBA ~]$ psql -d maxwelldb
psql (12.9)
Type "help" for help.

maxwelldb=# show shared_buffers;
 shared_buffers 
----------------
 128MB
(1 row)

maxwelldb=# select current_setting('shared_buffers');
 current_setting 
-----------------
 128MB
(1 row)

maxwelldb=#

1.1.15 修改当前session的参数配置，命令如下：

maxwelldb=# select set_config('maintenance_work_mem','128MB',false);
 set_config 
------------
 128MB
(1 row)

maxwelldb=# set maintenance_work_mem to '128MB';
SET
maxwelldb=# select set_config('maintenance_work_mem','128MB',false);
 set_config 
------------
 128MB
(1 row)

maxwelldb=# 

1.1.16 查看当前正在写的WAL文件，命令如下：

1.1.17  查看当前WAL文件的buffer中还有多少字节的数据没有写入磁盘中，命令如下：

 1.1.18 查看数据库实例是否正在做基础备份，命令如下：

maxwelldb=# select pg_is_in_backup(),pg_backup_start_time();
 pg_is_in_backup | pg_backup_start_time 
-----------------+----------------------
 f               | 
(1 row)

maxwelldb=# 

1.1.19 查看当前数据库实例处于Hot Standby状态还是正常数据库状态，命令如下：

maxwelldb=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)

maxwelldb=#

1.1.20 查看数据库的大小，命令如下：

maxwelldb=# select pg_database_size('maxwelldb'),pg_size_pretty(pg_database_size('maxwelldb'));
 pg_database_size | pg_size_pretty 
------------------+----------------
          8242031 | 8049 kB
(1 row)

maxwelldb=# 

上面的命令用于查看数据库“maxwelldb”的大小。注意，如果数据库中有很多表，使用上述命令查询将比较慢，也可能对当前系统产生不利的影响。在上面的命令中，pg_size_pretty()函数会把数字以MB、GB等格式显示出来，这样的结果更加直观。

1.1.21 查看表的大小，命令如下：

maxwelldb=# select pg_size_pretty(pg_relation_size('playground'));
 pg_size_pretty 
----------------
 8192 bytes
(1 row)

maxwelldb=# select pg_size_pretty(pg_total_relation_size('playground'));
 pg_size_pretty 
----------------
 24 kB
(1 row)

maxwelldb=# 

上例中，pg_relation_size()仅计算表的大小，不包括索引的大小，而pg_total_relation_size()则会把表上索引的大小也计算进来。

1.1.22 查看表上所有索引的大小，命令如下：

maxwelldb=# select pg_size_pretty(pg_indexes_size('playground'));
 pg_size_pretty 
----------------
 16 kB
(1 row)

maxwelldb=# 

注意，pg_indexes_size()函数的参数名是一个表对应的OID（输入表名会自动转换成表的OID），而不是索引的名称。

1.1.23 查看表空间的大小，命令如下：

maxwelldb=# select pg_size_pretty(pg_tablespace_size('pg_global'));
 pg_size_pretty 
----------------
 623 kB
(1 row)

maxwelldb=# 

maxwelldb=# select pg_size_pretty(pg_tablespace_size('pg_default'));
 pg_size_pretty 
----------------
 39 MB
(1 row)

maxwelldb=# 

  上面的示例中查看了全局表空间“pg_global”和默认表空间“pg_default”的大小。

1.1.24 查看表对应的数据文件，命令如下：

maxwelldb=# select pg_relation_filepath('playground');
 pg_relation_filepath 
----------------------
 base/16385/16389
(1 row)

maxwelldb=#