GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN schema public to "XXX_DMM_UPDATE";
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public to "XXX_DMM_UPDATE";
GRANT SELECT ON ALL TABLES IN schema public to "XXX_DMM_READONLY";

SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE,
 (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2,
 (array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4
FROM (
  SELECT indexrelid::regclass AS idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||
 	 COALESCE(indexprs::text,'')||E'\n' || COALESCE(indpred::text,'')) AS KEY
  FROM pg_index) sub
GROUP BY KEY HAVING COUNT(*)>1
ORDER BY SUM(pg_relation_size(idx)) DESC;

SELECT
  t.tablename,
  indexname,
  c.reltuples AS num_rows,
  pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
  pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
  CASE WHEN indisunique THEN 'Y'
   ELSE 'N'
  END AS UNIQUE,
  idx_scan AS number_of_scans,
  idx_tup_read AS tuples_read,
  idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
  ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
     JOIN pg_class c ON c.oid = x.indrelid
     JOIN pg_class ipg ON ipg.oid = x.indexrelid
     JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
  AS foo
  ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

select pg_size_pretty(pg_relation_size('表名'));

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 20

SELECT
table_name,
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
SELECT query, calls, total_time, total_time/calls, rows,
100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

select userid, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 5;  

select userid, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 5;  

select userid, dbid, query from pg_stat_statements order by mean_time desc limit 5;  

select userid, dbid, query from pg_stat_statements order by total_time desc limit 5;  

select userid, dbid, query from pg_stat_statements order by stddev_time desc limit 5;  

select userid, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 5;  

select userid, dbid, query from pg_stat_statements order by temp_blks_written desc limit 5;  

select pg_stat_statements_reset();

db_quhaizhou=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

db_quhaizhou=# \db
       List of tablespaces
    Name    |  Owner   | Location 
------------+----------+----------
 pg_default | postgres | 
 pg_global  | postgres | 
(2 rows)

db_quhaizhou=# \dg
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 quhaizhou |                                                            | {}
 
db_quhaizhou=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 quhaizhou |                                                            | {}

db_quhaizhou=# \z student
                              Access privileges
 Schema |  Name   | Type  | Access privileges | Column privileges | Policies 
--------+---------+-------+-------------------+-------------------+----------
 public | student | table |                   |                   | 
(1 row)
 
db_quhaizhou=# \dp student
                              Access privileges
 Schema |  Name   | Type  | Access privileges | Column privileges | Policies 
--------+---------+-------+-------------------+-------------------+----------
 public | student | table |                   |                   | 
(1 row)

db_quhaizhou=# \encoding gbk;        
db_quhaizhou=# 
db_quhaizhou=# select * from student;
 id |                name                | number 
----+------------------------------------+--------
  2 |                                | 1023 
(1 row)
 
db_quhaizhou=# \encoding utf8;
db_quhaizhou=# 
db_quhaizhou=# select * from student;
 id |                name                | number 
----+------------------------------------+--------
  2 | 张三                               | 1023 
(1 row)