PG常用的几个查询（1）

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(); --------什么时候开启的