CNDBA社区
在之前的博客，我们了解的PG的相关信息，如下：
PostgreSQL 学习笔记（1） — PG 概述
https://www.cndba.cn/dave/article/116370
PostgreSQL 学习笔记（2） — PG 版本发布策略 和 生命周期说明
https://www.cndba.cn/dave/article/116372
PostgreSQL 学习笔记（3） — PG 单实例安装手册
https://www.cndba.cn/dave/article/116374
PostgreSQL 学习笔记（4） — PG 启动 与 关闭
https://www.cndba.cn/dave/article/116375
PostgreSQL 学习笔记（5） — psql 工具使用说明
https://www.cndba.cn/dave/article/116376
PostgreSQL 学习笔记（6） — PG 参数管理
https://www.cndba.cn/dave/article/116377
PostgreSQL 学习笔记（7） — PG 数据库 基本操作
https://www.cndba.cn/dave/article/116379
PostgreSQL 学习笔记（8） — PG 模式 与 用户 管理
https://www.cndba.cn/dave/article/116380
PostgreSQL 学习笔记（9） — PG 进程和内存结构说明
https://www.cndba.cn/dave/article/116381
PostgreSQL 学习笔记（10） — PG 目录结构 说明
https://www.cndba.cn/dave/article/116382
PostgreSQL 学习笔记（11） — PG 常用的 系统表 和 系统视图 说明
https://www.cndba.cn/dave/article/116383
本篇我们看下PG的常用命令。
1 常用的管理命令
1.1 查看当前数据库实例的版本信息
[root@db1 ~]# su - postgres
Last login: Wed Aug 10 05:53:07 CST 2022 on pts/3
-bash-4.2$ psql
psql (14.6)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 14.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
postgres=#
1.2 查看数据库的启动时间
postgres=# select pg_postmaster_start_time();
pg_postmaster_start_time
-------------------------------
2022-08-09 05:02:23.605406+08
(1 row)
1.3 查看最后load配置文件的时间
postgres=# select pg_conf_load_time();
pg_conf_load_time
-------------------------------
2022-08-09 05:02:23.532756+08
(1 row)
-bash-4.2$ ps -ef|grep pg
postgres 3559 1 0 Aug09 ? 00:00:21 /usr/pgsql-14/bin/postgres -D /var/lib/pgsql/14/data
postgres 11628 11533 0 22:04 pts/3 00:00:00 grep --color=auto pg
-bash-4.2$ pg_ctl reload
server signaled
-bash-4.2$
postgres=# select pg_conf_load_time();
pg_conf_load_time
-------------------------------
2022-08-12 22:05:00.533836+08
(1 row)
1.4 显示当前数据库时区
postgres=# show timezone;
TimeZone
---------------
Asia/Shanghai
(1 row)
1.5 查看当前实例中有哪些数据库
-bash-4.2$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
cndba | 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
ustc | cndba | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/cndba +
| | | | | cndba=CTc/cndba
(5 rows)
-bash-4.2$ psql
psql (14.6)
Type "help" for help.
postgres=# /l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
cndba | 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
ustc | cndba | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/cndba +
| | | | | cndba=CTc/cndba
(5 rows)
postgres=#
1.6 查询当前连接的数据库名称
postgres=# select current_catalog, current_database();
current_catalog | current_database
-----------------+------------------
postgres | postgres
(1 row)
1.7 查看当前用户
postgres=# select user;
user
----------
postgres
(1 row)
postgres=# select current_user;
current_user
--------------
postgres
(1 row)
1.8 查询当前session所在客户端的IP地址及端口
-bash-4.2$ psql -h localhost -U cndba
Password for user cndba:
psql (14.6)
Type "help" for help.
cndba=> select inet_client_addr(),inet_client_port();
inet_client_addr | inet_client_port
------------------+------------------
127.0.0.1 | 44708
(1 row)
cndba=>
1.9 查询当前数据库服务器的IP地址及端口
cndba=> select inet_server_addr(),inet_server_port();
inet_server_addr | inet_server_port
------------------+------------------
127.0.0.1 | 5432
(1 row)
1.10 查询当前session的后台服务进程的PID
cndba=> select pg_backend_pid();
pg_backend_pid
----------------
13366
(1 row)
1.11 查看当前参数配置情况
postgres=# show shared_buffers;
shared_buffers
----------------
150MB
(1 row)
^
postgres=# select current_setting('shared_buffers');
current_setting
-----------------
150MB
(1 row)
1.12 修改当前session的参数配置
postgres=# set maintenance_work_mem to '128MB';
SET
postgres=# SELECT set_config('maintenance_work_mem', '150MB', false);
set_config
------------
150MB
(1 row)
1.13 查看数据库实例是否正在做基础备份
postgres=# select pg_is_in_backup(), pg_backup_start_time() ;
pg_is_in_backup | pg_backup_start_time
-----------------+----------------------
f |
(1 row)
1.14 查看当前数据库实例处于Hot Standby状态还是正常数据库状态
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
1.15 查看数据库的大小
postgres=# select pg_database_size('cndba'), pg_size_pretty(pg_database_size('cndba'));
pg_database_size | pg_size_pretty
------------------+----------------
16556835 | 16 MB
(1 row)
1.16 查看表的大小
cndba=# /dt+ film
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------+-------+----------+-------------+---------------+--------+-------------
public | film | table | postgres | permanent | heap | 464 kB |
(1 row)
cndba=# select pg_size_pretty(pg_relation_size('film')) ;
pg_size_pretty
----------------
432 kB
(1 row)
cndba=# select pg_size_pretty(pg_total_relation_size('film')) ;
pg_size_pretty
----------------
664 kB
(1 row)
pg_relation_size()仅计算表的大小，不包括索引的大小。
pg_total_relation_size()则会把表上索引的大小也计算进来。
1.17 查看表上所有索引的大小
cndba=# select pg_size_pretty(pg_indexes_size('film'));
pg_size_pretty
----------------
200 kB
(1 row)
pg_indexes_size()函数的参数名是一个表对应的OID（输入表名会自动转换成表的OID），而不是索引的名称。
1.18 查看表空间的大小
cndba=# select pg_size_pretty(pg_tablespace_size('pg_global'));
pg_size_pretty
----------------
576 kB
(1 row)
cndba=# select pg_size_pretty(pg_tablespace_size('pg_default'));
pg_size_pretty
----------------
50 MB
(1 row)
1.19 查看表对应的数据文件
cndba=# select pg_relation_filepath('film');
pg_relation_filepath
----------------------
base/16384/16437
(1 row)
2 常用的运维命令
2.1 配置文件生效
对于动态参数可以适用如下两种方法进行：
方法一：在操作系统下使用如下命令：
-bash-4.2$ pg_ctl reload
server signaled
方法二：在psql中使用如下命令：
cndba=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
对于静态参数，只能通过重启数据库服务才能使修改生效。
2.2 WAL日志维护
wal日志即write ahead log预写式日志，简称wal日志。wal日志可以说是PostgreSQL中十分重要的部分，相当于oracle中的redo日志。
当数据库中数据发生变更时：
（1）change发生时：先要将变更后内容计入wal buffer中，再将变更后的数据写入data buffer；
（2）commit发生时：wal buffer中数据刷新到磁盘；
（3）checkpoint发生时：将所有data buffer刷新的磁盘。
2.2.1 查看 online wal日志
cndba=# select * from pg_ls_waldir() order by modification asc;
name | size | modification
--------------------------+----------+------------------------
000000010000000000000002 | 16777216 | 2022-08-08 23:25:52+08
000000010000000000000001 | 16777216 | 2022-08-10 06:03:44+08
(2 rows)
cndba=# select pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
000000010000000000000001
(1 row)
cndba=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/1DD13E8
(1 row)
2.2.2 wal日志大小设置
在自动 WAL检查点之间允许WAL 增长到的最大尺寸。这是一个软限制，在特殊的情况 下 WAL 尺寸可能会超过max_wal_size， 例如在重度负荷下、archive_command失败或者高的 wal_keep_segments设置。默认为 1 GB。增加这个参数可能导致崩溃恢复所需的时间。
2.2.3 wal日志切换
cndba=# select pg_switch_wal();
pg_switch_wal
---------------
0/1DD1400
(1 row)
cndba=# select * from pg_ls_waldir() order by modification asc;
name | size | modification
--------------------------+----------+------------------------
000000010000000000000001 | 16777216 | 2022-08-12 22:29:34+08
000000010000000000000002 | 16777216 | 2022-08-12 22:29:40+08
(2 rows)
2.2.4 查看wal日志内容
-bash-4.2$ pg_waldump 000000010000000000000001|more
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/01000028, prev 0/00000000, desc: CHECKPOINT_SHUTDOWN redo 0/1000028;
tli 1; prev tli 1; fpw true; xid 0:3; oid 12000; multi 1; offset 0; oldest xid 3 in DB 1; oldest multi 1 in DB 1; oldest/newest commit tim
estamp xid: 0/0; oldest running xid 0; shutdown
rmgr: XLOG len (rec/tot): 30/ 30, tx: 1, lsn: 0/010000A0, prev 0/01000028, desc: NEXTOID 20192
rmgr: XLOG len (rec/tot): 49/ 137, tx: 1, lsn: 0/010000C0, prev 0/010000A0, desc: FPI , blkref #0: rel 1663/1/6117 bl
k 0 FPW
rmgr: XLOG len (rec/tot): 49/ 137, tx: 1, lsn: 0/01000150, prev 0/010000C0, desc: FPI , blkref #0: rel 1664/0/6115 bl
k 0 FPW
……
2.3 查看schema下各表数据量
cndba=# select relname,pg_size_pretty(pg_total_relation_size(relid)) from pg_stat_user_tables where schemaname ='public' order by pg_total_relation_size(relid) desc;
relname | pg_size_pretty
---------------+----------------
rental | 2416 kB
payment | 1872 kB
film | 664 kB
film_actor | 528 kB
inventory | 440 kB
customer | 216 kB
address | 160 kB
film_category | 120 kB
city | 120 kB
actor | 80 kB
country | 56 kB
store | 40 kB
staff | 32 kB
dave | 24 kB
category | 24 kB
language | 24 kB
cndba | 0 bytes
(17 rows)
2.4 查看表数据量
cndba=# select reltuples::bigint from pg_catalog.pg_class where relname ='film';
reltuples
-----------
1000
(1 row)
2.5 取消一个长时间执行的SQL
取消正在长时间执行的SQL命令的方法有以下两种。
- pg_cancel_backend(pid int)；##取消正在执行的sql
- pg_terminate_backend(pid int); ##终止一个后台服务进程，同时释放后台服务进程资源
区别：前者是给正在执行的SQL配置一个取消标志，正在执行的任务在合适的时候检查到就主动退出，如果没有检测到该任务无法正常退出，这时需要用后者来执行。
通常先查询pg_stat_activity以找出长时间运行的SQL命令
cndba=# select pid,usename,query_start, query from pg_stat_activity;
pid | usename | query_start | query
-------+----------+-------------------------------+--------------------------------------------------------------
3571 | postgres | |
3569 | | |
17947 | postgres | 2022-08-12 23:22:43.575504+08 | select pid,usename,query_start, query from pg_stat_activity;
3567 | | |
3566 | | |
3568 | | |
(6 rows)
然后再使用pg_cancel_backend()取消该SQL命令，如果pg_cancel_backend()取消失败，再使用pg_terminate_backend()
select pg_cancel_backend(17947);
select pid,usename,query_start, query from pg_stat_activity;
select pg_terminate_backend(17947);
2.6 查看长时间运行的SQL
SELECT datname,
pid,
usename,
query_start,
STATE,
left(query,40) query,
now()-query_start
FROM pg_stat_activity
WHERE STATE<>'idle'
AND (backend_xid IS NOT NULL
OR backend_xmin IS NOT NULL)
ORDER BY now()-query_start;
2.7 查看表的统计信息
select relowner::regrole,relname,relkind,relpages,reltuples from pg_class where relname='t1';
2.8 查看膨胀高的表TOP 10(碎片)
如果膨胀太高需要执行，例如 vacuum t1,vacuum不会锁表，vacuum full会锁表。
SELECT relname AS TABLE_NAME,
pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) AS table_size,
n_dead_tup,
n_live_tup,
(n_dead_tup * 100 / (n_live_tup + n_dead_tup))AS dead_tup_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup<>0 order by 5 desc LIMIT 10;
2.9 查看数据库用户连接数
select datname,usename,state,count(*) from pg_stat_activity group by datname,usename,state order by 1,2,3,4;
2.10 查杀执行时间超过10分钟的sql
select pg_terminate_backend(pid) from pg_stat_activity where clock_timestamp()-query_start > '10 min' and backend_type='client backend';
2.11 查杀超过10分钟的长事务会话
select pg_terminate_backend(pid) from pg_stat_activity where clock_timestamp()-xact_start > '10 min' and backend_type='client backend';
版权声明：本文为博主原创文章，未经博主允许不得转载。
