PG常用的几个查询

select version(); —查看pg版本
SELECT current_database(); –查看当前数据库
select current_user; –查看当前用户
psql -c “select version()” —-执行单条sql命令
psql -f xxxx.sql —–可以把多条命令写入文件,用-f执行
SELECT pg_database_size(current_database()); 查看当前库大小
SELECT sum(pg_database_size(datname)) from pg_database; 查询所有库大小之和
select pg_relation_size(‘accounts’); —查询表大小
select pg_total_relation_size(‘accounts’); —查询包含表和表索引其他总大小
select pg_tablespace_size(‘tbs_index’)/1024/1024 as “SIZE M”; 查看表空间大小

查看TOP10大表:
SELECT table_name
,pg_relation_size(table_name) as size
FROM information_schema.tables
WHERE table_schema NOT IN (‘information_schema’,
‘pg_catalog’)
ORDER BY size DESC
LIMIT 10;

快速评估表的行数:(一般都是大表,如果用count比较耗时间)
SELECT (CASE WHEN reltuples > 0 THEN
pg_relation_size(‘mytable’)/(8192*relpages/reltuples)
ELSE 0
END)::bigint AS estimated_row_count
FROM pg_class
WHERE oid = ‘mytable’::regclass;

查询等待会话
SELECT
w.current_query as waiting_query,
w.procpid as w_pid,
w.usename as w_user,
l.current_query as locking_query,
l.procpid as l_pid,
l.usename as l_user,
t.schemaname || ‘.’ || t.relname as tablename
from pg_stat_activity w
join pg_locks l1 on w.procpid = l1.pid and not l1.granted
join pg_locks l2 on l1.relation = l2.relation and l2.granted
join pg_stat_activity l on l2.pid = l.procpid
join pg_stat_user_tables t on l1.relation = t.relid
where w.waiting;

SELECT date_trunc(‘second’,
current_timestamp - pg_postmaster_start_time()) as uptime; —-数据库开启多久

SELECT pg_postmaster_start_time(); ——–什么时候开启的

你活着,证明了什么?