PostgreSQL常見問題
收集客戶端常見問題
FAQ
如何查看Table or User的存取權限?
// by user SELECT (table_schema || '.' || table_name) AS table, string_agg(privilege_type, ', ') AS privileges
FROM information_schema.role_table_grants
WHERE grantee='user_name'
GROUP BY (table_schema || '.' || table_name);
// by table SELECT grantee, string_agg(privilege_type, ', ') AS privileges FROM information_schema.role_table_grants WHERE table_name='table_name'
GROUP BY grantee;
如何查看Database大小?
SELECT pg_database.datname, pg_database_size(pg_database.datname), pg_size_pretty(pg_database_size(pg_database.datname))
FROM pg_database
ORDER BY pg_database_size DESC;
如何查看各Table的大小?
SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC;
or
SELECT nspname || '.' || relname AS "relation", T.spcname, pg_size_pretty(pg_relation_size(C.oid)) AS "size", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(C.oid) DESC
如何create 一個readonly的user?
1.CREATE ROLE xxx LOGIN PASSWORD 'yyy';
2.GRANT CONNECT ON DATABASE mydb TO xxx;
3.GRANT USAGE ON SCHEMA my_schema | public TO xxx;
4.GRANT SELECT ON ALL TABLES IN SCHEMA my_schema | public TO xxx;
如何查到Lock的Statments?
SELECT bl.pid AS src_pid, a.usename AS src_user, ka.query AS src_stmt, now() - ka.query_start AS src_duration, kl.pid AS blk_pid,
ka.usename AS blk_user, a.query AS blk_stmt, now() - a.query_start AS blk_duration
FROM pg_locks bl
JOIN pg_stat_activity a ON a.pid = bl.pid
JOIN pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
JOIN pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted;