- 查询版本号
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
- 查看数据库启动时间
postgres=# select pg_postmaster_start_time();
pg_postmaster_start_time
-------------------------------
2022-02-13 15:08:46.013327+08
(1 row)
- 查看最后load配置文件的时间
postgres=# select pg_conf_load_time();
pg_conf_load_time
-------------------------------
2022-02-13 15:08:45.986653+08
(1 row)
- 查看当前时区（数据库时区可能和操作系统时区不一致）
postgres=# show timezone;
TimeZone
---------------
Asia/Shanghai
(1 row)
- 查看当前用户名
postgres=# select user;
user
----------
postgres
(1 row)
postgres=# select current_user;
current_user
--------------
postgres
(1 row)
- 查看当前session的后台服务进程的PID
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
22777
(1 row)
- 查看当前正在写入的wal文件
postgres=# select pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
000000010000000000000043
(1 row)
- 查看wal文件的buffer还有多少字节的数据没有写入磁盘
postgres=# select pg_wal_lsn_diff(pg_current_wal_insert_lsn(),pg_current_wal_lsn());
pg_wal_lsn_diff
-----------------
0
(1 row)
- 查看当前是否在进行基础备份
postgres=# select pg_is_in_backup(),pg_backup_start_time();
pg_is_in_backup | pg_backup_start_time
-----------------+----------------------
f |
(1 row)
- 查看数据库大小
postgres=# select pg_database_size('postgres'),pg_size_pretty(pg_database_size('postgres'));
pg_database_size | pg_size_pretty
------------------+----------------
8471407 | 8273 kB
(1 row)
- 查看表大小
postgres=# select pg_size_pretty(pg_total_relation_size('t1'));
pg_size_pretty
----------------
8912 kB
(1 row)
--pg_total_relation_size显示表及索引大小，pg_relation_size仅显示表大小
postgres=# select pg_size_pretty(pg_relation_size('t1'));
pg_size_pretty
----------------
6672 kB
(1 row)
- 查看表上所有索引大小
postgres=# select pg_size_pretty(pg_indexes_size('t1'));
pg_size_pretty
----------------
2208 kB
(1 row)
- 查看表空间大小
postgres=# select pg_size_pretty(pg_tablespace_size('pg_global'));
pg_size_pretty
----------------
623 kB
(1 row)
- 查看表对应物理文件
postgres=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/13593/16775
(1 row)
- 切换log日志
postgres=# select pg_rotate_logfile();
pg_rotate_logfile
-------------------
t
(1 row)
- 切换wal日志
postgres=# select pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
000000010000000000000046
(1 row)
postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/46000128
(1 row)
postgres=# checkpoint ;
CHECKPOINT
postgres=# select pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
000000010000000000000047
(1 row)
- 取消长时间执行的sql命令的方法
- 查询时间长的sql命令
postgres=# select pid,usename,query_start,query from pg_stat_activity;
pid | usename | query_start | query
-------+----------+-------------------------------+-------------------------------------------------------------
5886 | postgres | |
5883 | | |
22777 | postgres | 2022-02-27 12:50:05.877155+08 | select pid,usename,query_start,query from pg_stat_activity;
2938 | postgres | 2022-02-27 12:50:03.576143+08 | select pg_sleep(300);
- 使用pg_cancel_backend()取消该sql命令，取消失败可使用pg_terminate_backend();
postgres=# select pid,usename,query_start,query from pg_stat_activity;
pid | usename | query_start | query
-------+----------+-------------------------------+-------------------------------------------------------------
5886 | postgres | |
5883 | | |
22777 | postgres | 2022-02-27 12:50:05.877155+08 | select pid,usename,query_start,query from pg_stat_activity;
2938 | postgres | 2022-02-27 12:50:03.576143+08 | select pg_sleep(300);
5881 | | |
5880 | | |
5882 | | |
(7 rows)
postgres=# select pg_cancel_backend(2938);
pg_cancel_backend
-------------------
t
(1 row)
postgres=# select pid,usename,query_start,query from pg_stat_activity;
pid | usename | query_start | query
-------+----------+-------------------------------+-------------------------------------------------------------
5886 | postgres | |
5883 | | |
22777 | postgres | 2022-02-27 12:51:05.83071+08 | select pid,usename,query_start,query from pg_stat_activity;
2938 | postgres | 2022-02-27 12:50:03.576143+08 | select pg_sleep(300);
5881 | | |
5880 | | |
5882 | | |
(7 rows)
postgres=# select pg_terminate_backend(2938);
pg_terminate_backend
----------------------
t
(1 row)
postgres=# select pid,usename,query_start,query from pg_stat_activity;
pid | usename | query_start | query
-------+----------+-------------------------------+-------------------------------------------------------------
5886 | postgres | |
5883 | | |
22777 | postgres | 2022-02-27 12:51:24.907149+08 | select pid,usename,query_start,query from pg_stat_activity;
5881 | | |
5880 | | |
5882 | | |
(6 rows)
参考《PostgreSQL修炼之道 从小工到专家 第2版》
