postgresql 基础

二、授权

1）sequence授权，跟table授权有区别
grant usage,select on sequence <sequence_name> to <role>;


三、查询size

1、sql查询
postgres=# select pg_size_pretty(pg_database_size('postgres'));

2 、linux du查询
postgres=# SELECT oid from pg_database where datname='postgres';
oid
-------
21360


查看名称为21360的文件的大小即是数据库postgres的大小;
linux可以以下命令替代
[hostname 21360]# cd /postgres/data/base/21360
[hostname 21360]# du -sh


四、解锁，注意要先连接到表所在的database
\c &dbname
select distinct a.relname,b.pid from pg_class a,pg_locks b where a.oid=b.relation and b.granted=true and a.relname like '%&table_name%';
select pg_terminate_backend(&pid);


五查询哪些表占用的空间大。

SELECT table_schema || '.' || table_name AS table_full_name,
pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
where table_name like '%awr%'
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC ;


原文链接：https://blog.csdn.net/wish503/article/details/52371550