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=#