Postgre数据库常⽤SQL汇总⼀、表存储、数据库连接、锁表等相关SQL
--查询单个表⼤⼩
lect pg_size_pretty(pg_relation_size('AIKES'));
--查询所有表物理存储⼤⼩
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
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')
DESC limit 5;
--查询表和索引总⼤⼩
SELECT
table_name,qq邮箱注册官网
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
pg_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
) AS all_tables
ORDER BY total_size DESC
) AS pretty_sizes;
--当前总共正在使⽤的连接数
lect count(1) from pg_stat_activity;
--显⽰系统允许的最⼤连接数
show max_connections;
--显⽰系统保留的⽤户数
show superur_rerved_connections ;
--按照⽤户分组查看
lect uname, count(*) from pg_stat_activity group by uname order by count(*) desc;
--修改最⼤连接数
alter system t max_connections= 150
--查看数据库剩余连接数
lect max_conn-now_conn as resi_conn from (lect tting::int8 as max_conn,(lect count(*) from pg_stat_activity) as now_conn from pg_ttings where nam
--清理空闲连接
lect pg_terminate_backend(pid) from pg_stat_activity where state = 'idle';
--查询锁表
lect a.locktype,a.databa,a.lname
from pg_locks a
join pg_class b lation = b.oid;
--解锁
SELECT pg_cancel_backend('58804');
⼆、表权限、⽤户权限相关SQL
如何安慰朋友
#撤掉超级权限
alter ur hmims nosuperur;
--给⽤户赋权对应模式
grant USAGE on SCHEMA aikes_hm to aikes_hm;
#访问表
grant lect,inrt,update,delete on all tables in schema public to hmims;
#如果访问⾃增序列，需要授权
grant lect,update on all quences in schema public to hmims;
注意：上⾯的授权只对历史的⼀些对象授权，后期增加的对象是没有权限的，需要给个默认权限
#默认表权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT lect,inrt,update,delete ON TABLES TO hmims;
#默认⾃增序列权限
间断点
掠词语ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT lect,update ON quences TO hmims;
--查看当前⽤户可访问的表
lect distinct grantor,grantee,table_schema,table_name from information_schema.table_privileges
where grantee ='aikes_hm' ;
--查看表权限
lect distinct grantor,grantee,table_schema,table_name from information_schema.table_privileges
where grantor ='aikes_c' and grantee <>'aikes_c' and grantee <>'aikes_hm';--521
SELECT n.nspname as "Schema",
房地产项目管理lkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'S' THEN 'quence' WHEN 'f' THEN 'foreign table' END pg_catalog.array_to_lacl, E'\n') AS "Access privileges",
pg_catalog.array_to_string(ARRAY(
SELECT attname || E':\n ' || pg_catalog.array_to_string(attacl, E'\n ')
FROM pg_catalog.pg_attribute a
WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
), E'\n') AS "Column privileges",
pg_catalog.array_to_string(ARRAY(
SELECT polname
|| CASE WHEN polcmd != '*' THEN
E' (' || polcmd || E'):'
ELSE E':'
END萝卜的卜组词
|| CASE WHEN polqual IS NOT NULL THEN
E'\n (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)
ELSE E''
END
|| CASE WHEN polwithcheck IS NOT NULL THEN
E'\n (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)
ELSE E''
END || CASE WHEN polroles <> '{0}' THEN
E'\n to: ' || pg_catalog.array_to_string(
ARRAY(
SELECT rolname
FROM pg_catalog.pg_roles
WHERE oid = ANY (polroles)
ORDER BY 1
), E', ')
三生三世枕上书结局ELSE E''
END
FROM pg_catalog.pg_policy pol
WHERE polrelid = c.oid), E'\n')
AS "Policies"
FROM pg_catalog.pg_class c
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
lkind IN ('r', 'v', 'm', 'S', 'f')
AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1, 2;
三、数据导出、导⼊
--导出dmp数据⽂件
pg_dump -U ⽤户名 -Ft -v 数据库名 > 指定dmp⽂件输出路径
--例⼦：pg_dump -U aikes -Ft -v testDB > /pg/data/backup/aikes_20200821.dmp
--具体参数可查看PG官⽅⽂档
--导⼊dmp数据⽂件
pg_restore -U ⽤户名 -O -d 数据库名要导⼊的dmp⽂件路径懈逅
--例⼦：pg_restore -U aikes -O -d testDB1 /usr/local/postgresql/backup/aikes_20200821.dmp --参数O是不使⽤dmp⽂件中的库名⽤户名，以导⼊的⽬标库为准
