常用查询
常用查询
查询所有数据库和对应的OID，表空间
注2：OID是伪列
select oid,datname,dattablespace from pg_database;
database cluster物理上是一个 base directory（PGDATA），包括一些子目录和文件
[pg@pg data]$ tree -L 2
.
├── backup_label.old
├── base
│ ├── 1
│ ├── 13211
│ └── 13212
├── current_logfiles
├── global
│ ├── 1136
│ ├── 1136_fsm
│ ├── 1136_vm
│ ├── pg_control
│ ├── pg_filenode.map
│ └── pg_internal.init
├── pg_commit_ts
├── pg_dynshmem
├── pg_hba.conf
├── pg_ident.conf
├── pg_log
├── pg_logical
│ ├── mappings
│ ├── replorigin_checkpoint
│ └── snapshots
├── pg_multixact
│ ├── members
│ └── offsets
├── pg_notify
│ └── 0000
├── pg_replslot
├── pg_serial
├── pg_snapshots
├── pg_stat
├── pg_stat_tmp
│ ├── db_0.stat
│ ├── db_13212.stat
│ └── global.stat
├── pg_subtrans
│ └── 0000
├── pg_tblspc
│ ├── 16388 -> /opt/postgres/data/tb1
│ └── 49155 -> /tbs
├── pg_twophase
├── PG_VERSION
├── pg_wal
│ ├── 000000090000000000000077
│ └── archive_status
├── pg_xact
│ └── 0000
├── postgresql.auto.conf
├── postgresql.auto.conf.bak20181230
├── postgresql.conf
├── postmaster.opts
├── postmaster.pid
├── recovery.done
├── serverlog
├── tablespace_map.old
└── tb1
数据库聚簇布局结构（Layout of a Database Cluster）
base--存放数据库的子目录
global--包括聚簇范围的表，如pg_database
pg_control--控制文件，用于存储全局控制信息
pg_filenode.map--系统表的OID与具体文件名进行硬编映射
pg_internal.init--缓存系统文件，加快系统表读取速度
1136--对象数据文件，每个表和索引都存储为单独的文件，以表或索引的filenode number命令（pg_class.relfilenode）
1136_fsm--数据文件对应的FSM(free space map)件，用map方式来标识哪些block是空闲的
1136_vm--数据文件对应的VM(visibility map)PostgreSQL中在做多版本并发控制时是通过在元组头上标“已无效”来实现删除或更新的，最后通过VACUUM功能来清理效数据回收空闲空间。在做VACUUM时就使用VM快速查找包含无效元组的block。VM仅是个简单的bitmap,一个bit对应一block。
pg_hba.conf--客户端网络访问控制配置文件
pg_log--默认错误日志输出位置
pg_logical--配置逻辑复制时，存储逻辑解码的数据状态
pg_tblspc--存放非默认表空间路径，软连接的形式
pg_wal--wal日志路径
postgresql.auto.conf--参数文件，使用alter system命令修改的参数文件，优先级高，会覆盖postgresql.conf参数值
postgresql.conf--参数配置文件
postmaster.opts--记录上次启动服务器时使用的命令行选项的文件
postmaster.pid--启动pg后产生的文件，记录启动pg的信息
tb1--非默认表空间
详细描述参考官方文档
https://www.postgresql.org/docs/current/storage-file-layout.html
查看系统中所有的表空间
表空间的类型有，默认表空间pg_default，系统共享表空间pg_global，自定义表空间
select oid,spcname,pg_tablespace_location(oid) from pg_tablespace;
数据库的启停
启动（有两种）
第一种：直接石头postgres进程后台启动
postgres -D /data/pgdata &
第二种：使用pg_ctl命令启动
pg_ctl -D /data/pgdata start
停止数据库（有两种）
1.直接运行postgres 进程发出signal信号，停止数据库
smart shutdown
fast shutdown
immediate shutdown
2.使用pg_ctl命令停止数据库
pg_ctl stop -D data/pgdata -m smart
pg_ctl stop -D data/pgdata -m fast
pg_ctl stop -D data/pgdata -m immediate
查看版本
select version()
查看数据库启动时间
select pg_postmaster_start_time()
查看最后load配置文件的时间
select pg_conf_load_time()
使用pg_ctl_reload 改变配置的装载时间
pg_ctl reload
显示当前数据库时区
show timezone
查看数据库
psql -l
查看当前用户名
select user;
select current_user;
select session_user;
查看当前连接的数据库的名称
select CURRENT_CATALOG,current_database();
使用CURRENT_CATALOG 和 current_database 都是显示当前连接的数据库名称，这两者功能完全相同，只不过catalog是sql标准中的语句
查询当前session所在客户端的IP地址以及端口
select inet_client_addr(),inet_server_port();
查询到当前数据库服务器的IP和端口
select inet_server_addr(),inet_server_port();
查询当前会话的后台服务进程ID
select pg_backend_pid();
ps -ef|grep xxx |grep -v grep
查看参数配置
show shared_buffers
select current_setting('shared_buffers');
查看数据库实例是否正在做基础备份
select pg_is_in_backup(),pg_backup_start_time();
查看当前数据库实例是hot standby状态还是正常数据库状态
select pg_is_in_recovery()
如果上面返回为真，表示数据库处于hot standby状态（备库）
查看数据库的大小
select pg_database_size('test'),pg_size_pretty(pg_database_size('test'))
注意：如果数据库中有很多表，使用上面的命令将比较慢，也可能对当前系统产生不利的影像，上面命令中，pg_size_pretty()函数会把数字以MB、GB等格式显示出来，这样会更直观。
查看表的大小
select pg_size_pretty(pg_relation_size('test'))
select pg_size_pretty(pg_total_relation_size('test'))
上列中pg_relation_size()仅计算表的大小，不包括索引的大小，而pg_total_relation_size()则把表上索引的大小也计算进来。
查看表上所有索引的大小
select pg_size_pretty(pg_indexes_size('test'))
注意：pg_indexes_size()hanshu 的参数名是一个表对应的OID（输入表名会自动转换成表的OID）而不是索引的名称。
查看表空间的大小
select pg_size_pretty(pg_tablespace_size('pg_global'))
select pg_size_pretty(pg_tablespace_size('pg_default'))
上面查看了全局表空间'pg_global'和默认表空间pg_default的大小，
查看表对应的数据文件
select pg_relation_filepath('test')
系统常用维护命令
修改配置文件postgresql.conf后，让修改生效的方法有两种
方法一：在操作系统下使用如下命令
pg_ctl pg_reload
方法二：在psql中使用如下命令
select pg_reload_config()
切换log日志文件到下一个
select pg_rotate_logfile();
切换WAL日志文件
select pg_switch_wal()
手工产生一次checkpoint
checkpoint
取消一个正在长时间执行的sql
有两个函数可以完成这个功能
- 1.pg_cancel_backend(pid) 取消一个正在执行的sql
- 2.pg_terminate_backend(pid) 终止一个后台服务进程，同时释放此后台服务进程的资源。
这两个函数的区别是：pg_cancel_backend()函数实际上时给正在执行的sql任务配置一个取消标志，正在执行的任务整合适的时候检查到此标志之后会主动退出，但是如果这个任务没有主动检查到这个标志，则任务就无法正常退出，pg_terminate_backend()命令来终止sql的执行。
先查询pg_stat_activity ，视图找出长时间运行的sql
select pid,usename,datname,query_start,application_name,client_addr,query,state from pg_stat_activity;
select * from pg_stat_activity;
然后使用pg_cancel_backend()取消
删除表中重复记录
方案一：适用于小表
DELETE
FROM
test A
WHERE
A.ctid <> ( SELECT MIN ( b.ctid) FROM test b WHERE A.ID = b.ID );
以上方案在表数据量较小时适用，表数据量过大时性能会很差，那么可以使用以下的查询删除重复数据
方案二：
DELETE
FROM
test
WHERE
ctid = ANY (
ARRAY ( SELECT ctid FROM ( SELECT ROW_NUMBER ( ) OVER ( PARTITION BY ID ), CTID FROM test ) x WHERE x.ROW_NUMBER > 1 )
);
查询所有函数：
方法一：使用 information_schema.routines 视图
该视图包含当前数据库中所有函数和过程的信息。以下 SQL 查询可列出所有存储过程的名称和所属模式：
SELECT routine_schema, routine_name
FROM information_schema.routines
WHERE routine_type = 'PROCEDURE';
此查询将返回所有存储过程的列表，包括其所在的模式（schema）和名称。
方法二：查询系统目录 pg_proc 和 pg_namespace
PostgreSQL 的系统目录 pg_proc 存储了关于函数和过程的详细信息。通过与 pg_namespace 联合查询，可以获取存储过程的名称和所属模式：
SELECT n.nspname AS schema_name, p.proname AS procedure_name
FROM pg_catalog.pg_proc p
JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid
WHERE p.prokind = 'p'; -- 'p' 表示过程（procedure）
查询所有视图：
select concat("create view ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";") from information_schema.VIEWS where table_schema in ('amc_aim',
'amc_ambd',
'amc_aom',
'amc_aum',
'amc_mro',
'apct',
'auth_0',
'znbz',
'znbz_fileservice')
查询所有的表：
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
and table_schema in('amc_aim',
'amc_ambd',
'amc_aom',
'amc_aum',
'ypdtrain_db',
'znbz',
'znbz_fileservice');
查询缓存命中率
缓冲区命中率，基于clock sweep扫描算法，要注意双缓存的影响
SELECT
round( SUM ( blks_hit ) * 100 / SUM ( blks_hit + blks_read ), 2 ) :: NUMERIC
FROM
pg_stat_database
WHERE
datname = current_database ( );
查询⼤于5分钟的⻓事务
⻓事务检查，会引起表膨胀、年龄回收失败、索引失效等
SELECT
query,
STATE
FROM
pg_stat_activity
WHERE
STATE <> 'idle'
AND ( backend_xid IS NOT NULL OR backend_xmin IS NOT NULL )
AND now( ) - xact_start > INTERVAL '5 min'
ORDER BY
xact_start;
TOP SQL查询
1 最耗IO SQL、单次调⽤最耗IO ：
select userid::regrole, dbid, query from
pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 5;
2 总最耗IO ：
select userid::regrole, dbid, query from pg_stat_statements
order by (blk_read_time+blk_write_time) desc limit 5;
3 最耗时 SQL，单次调⽤最耗时 ：
select userid::regrole, dbid, query from
pg_stat_statements order by mean_exec_time desc limit 5;
4 总最耗时 ：
select userid::regrole, dbid, query from pg_stat_statements
order by total_time desc limit 5;
5 响应时间抖动最严重 SQL：
select userid::regrole, dbid, query from
pg_stat_statements order by stddev_exec_time desc limit 5;
6 最耗共享内存 SQL：
select userid::regrole, dbid, query from pg_stat_statements order
by (shared_blks_hit+shared_blks_dirtied) desc limit 5;
7 最耗临时空间 SQL：
select userid::regrole, dbid, query from pg_stat_statements order
by temp_blks_written desc limit 5;
查看表有哪些字段：
要查询系统表，看表“pitr_test”有哪些字段，一般的SQL命令是需要先查询pg_attribute表再关联pg_class表的，示例如下
SELECT
attrelid,
attname,
atttypid,
attlen,
attnum,
attnotnull
FROM
pg_attribute
WHERE
attrelid = ( SELECT OID FROM pg_class WHERE relname = 'pitr_test' );
使用regclass类型的自动转换运算符就可以不关联查询pg_class
了，示例如下：
SELECT
attrelid,
attname,
atttypid,
attlen,
attnum,
attnotnull
FROM
pg_attribute
WHERE
attrelid = 'pitr_test' :: REGCLASS;
删除重复记录
ctid表示数据行在它所处的表内的物理位置。ctid字段的类型是tid。尽管ctid可以非常快速地定位数据行，但每次VACUUM FULL之后，数据行在块内的物理位置会移动，即ctid会发生变化，所以ctid是不能作为长期的行标识符的，应该使用主键来标识逻辑行。
利用ctid可以删除表中的重复记录，如表“t”中有如下数据：
删除此表中重复数据的SQL命令如下：
DELETE FROM t a
WHERE a.ctid <> (SELECT min(b.ctid)
FROM t b
WHERE a.id = b.id);
上例的SQL语句在表“t”中的记录比较多时，效率比较低，这时可以使用一个更高效的删除此表重复数据的SQL命令：
DELETE FROM t
WHERE ctid = ANY(ARRAY(SELECT ctid
FROM (SELECT row_number() OVER
(PARTITION BY id), ctid
FROM t) x
WHERE x.row_number > 1));
浙公网安备 33010602011771号