侧边栏壁纸
博主头像
极客日记 博主等级

行动起来，活在当下

  • 累计撰写 93 篇文章
  • 累计创建 17 个标签
  • 累计收到 1 条评论

目 录CONTENT

文章目录
PostgreSQL

PostgreSQL 学习笔记 - PG常用命令

Jack.Jia
Jack.Jia
2022-12-10 / 0 评论 / 0 点赞 / 5 阅读 / 0 字

常用的管理命令

查看当前数据库实例的版本信息

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

查看数据库的启动时间

postgres=# select pg_postmaster_start_time();
   pg_postmaster_start_time
-------------------------------
 2022-08-09 05:02:23.605406+08
(1 row)

查看最后 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)

显示当前数据库时区

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

查看当前实例中有哪些数据库

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

查询当前连接的数据库名称

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

查看当前用户

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

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

查询当前 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=>

查询当前数据库服务器的 IP 地址及端口

cndba=> select inet_server_addr(),inet_server_port();
 inet_server_addr | inet_server_port
------------------+------------------
 127.0.0.1        |             5432
(1 row)

查询当前 session 的后台服务进程的 PID

cndba=> select pg_backend_pid();
 pg_backend_pid
----------------
          13366
(1 row)

查看当前参数配置情况

postgres=# show shared_buffers;
 shared_buffers
----------------
 150MB
(1 row)
                               ^
postgres=# select current_setting('shared_buffers');
 current_setting
-----------------
 150MB
(1 row)

修改当前 session 的参数配置

postgres=# set maintenance_work_mem to '128MB';
SET
postgres=#  SELECT set_config('maintenance_work_mem', '150MB', false);
 set_config
------------
 150MB
(1 row)

查看数据库实例是否正在做基础备份

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

查看当前数据库实例处于 Hot Standby 状态还是正常数据库状态

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

查看数据库的大小

postgres=# select pg_database_size('cndba'), pg_size_pretty(pg_database_size('cndba'));
 pg_database_size | pg_size_pretty
------------------+----------------
         16556835 | 16 MB
(1 row)

查看表的大小

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()则会把表上索引的大小也计算进来。

查看表上所有索引的大小

cndba=# select pg_size_pretty(pg_indexes_size('film'));
 pg_size_pretty
----------------
 200 kB
(1 row)

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

查看表空间的大小

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)

查看表对应的数据文件

cndba=# select pg_relation_filepath('film');
 pg_relation_filepath
----------------------
 base/16384/16437
(1 row)

常用的运维命令

配置文件生效

对于动态参数可以适用如下两种方法进行：
方法一：在操作系统下使用如下命令：

-bash-4.2$ pg_ctl reload
server signaled

方法二：在 psql 中使用如下命令：

cndba=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

对于静态参数，只能通过重启数据库服务才能使修改生效。

WAL 日志维护

wal 日志即 write ahead log 预写式日志，简称 wal 日志。wal 日志可以说是 PostgreSQL 中十分重要的部分，相当于 oracle 中的 redo 日志。

当数据库中数据发生变更时：
（1）change 发生时：先要将变更后内容计入 wal buffer 中，再将变更后的数据写入 data buffer；
（2）commit 发生时：wal buffer 中数据刷新到磁盘；
（3）checkpoint 发生时：将所有 data buffer 刷新的磁盘。

查看 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)

wal 日志大小设置

在自动 WAL 检查点之间允许 WAL 增长到的最大尺寸。这是一个软限制，在特殊的情况 下 WAL 尺寸可能会超过 max_wal_size， 例如在重度负荷下、archive_command 失败或者高的 wal_keep_segments 设置。默认为 1 GB。增加这个参数可能导致崩溃恢复所需的时间。

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)

查看 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
……

查看 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)

查看表数据量

cndba=# select reltuples::bigint from pg_catalog.pg_class where relname ='film';
 reltuples
-----------
      1000
(1 row)

取消一个长时间执行的 SQL

取消正在长时间执行的 SQL 命令的方法有以下两种。

  1. pg_cancel_backend(pid int)；## 取消正在执行的 sql
  2. 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);

查看长时间运行的 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;

查看表的统计信息

select relowner::regrole,relname,relkind,relpages,reltuples from pg_class where relname='t1';

查看膨胀高的表 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;

查看数据库用户连接数

select datname,usename,state,count(*) from pg_stat_activity group by datname,usename,state order by 1,2,3,4;

查杀执行时间超过 10 分钟的 sql

select pg_terminate_backend(pid) from pg_stat_activity where   clock_timestamp()-query_start >  '10 min' and backend_type='client backend';

查杀超过 10 分钟的长事务会话

select pg_terminate_backend(pid) from pg_stat_activity where   clock_timestamp()-xact_start >  '10 min' and backend_type='client backend';
0
PostgreSQL
版权归属： Jack.Jia
本文链接： https://www.explorexd.com/archives/c6736a13-d861-4f27-aff5-1d3ab96f9a7f
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区