SELECT pg_backend_pid()

SELECT txid_current()

select version()
PostgreSQL 13.6 (Debian 13.6-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit


查看当前数据库
select current_database()

select current_user;


查看所有表
SELECT * FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER BY tablename

所有数据库
select * from pg_database

select version();
 
select now()

查看数据库文件目录
show data_directory;

查看数据库大小
SELECT pg_size_pretty(pg_database_size('XX')) As fulldbsize;

查看所有数据库大小
select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database;

 
查看表的大小
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;



SELECT nspname FROM pg_namespace;


查看表结构
select * from information_schema.columns where table_name='xxx';

select * from pg_views where schemaname = 'public';
select * from information_schema.views where table_schema = 'public';

select * from information_schema.triggers;

select * from information_schema.sequences where sequence_schema = 'public';
 
 
查看表空间
select * from pg_tablespace;


查看语言
select * from pg_language;

查看索引
select * from pg_index;

查看各数据库数据创建时间
select datname,(pg_stat_file(format('%s/%s/PG_VERSION',case when spcname='pg_default' then 'base' else 'pg_tblspc/'||t2.oid||'/PG_11_201804061/' end, t1.oid))).* from pg_database t1,pg_tablespace t2 where t1.dattablespace=t2.oid;

查看索引大小
select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;

查看参数文件

show config_file;
show hba_file;
show ident_file;
 

查看参数值
select * from pg_file_settings


show all;

查看某个参数值,比如 work_mem
show work_mem


修改某个参数值,比如work_mem
alter sy stem set work_mem='8MB'


查看是否归档
show archive_mode;

show logging_collector 
show log_directory 
show log_filename
show log_truncate_on_rotation
show log_statement
show log_min_duration_statement
慢查询日志记录 
-1 禁用  ，0 记录所有语句


select * from pg_user;
select * from pg_shadow;
select * from pg_roles;

select * from information_schema.table_privileges where grantee='XX';

创建用户XX，并授予超级管理员权限
create user XXX SUPERUSER PASSWORD '123456'


GRANT ALL PRIVILEGES ON schema schemaname TO dbuser;

grant ALL PRIVILEGES on all tables in schema fds to dbuser;

GRANT ALL ON tablename TO user;

GRANT ALL PRIVILEGES ON DATABASE dbname TO dbuser;

grant select on all tables in schema public to dbuser;

GRANT create ON schema schemaname TO dbuser;

GRANT USAGE ON schema schemaname TO dbuser;


查看表的索引及大小

select relname,n.amname as index_type from pg_class m,pg_am n where m.relam = n.oid and m.oid in
(select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'cc');

SELECT c.relname,c2.relname, c2.relpages*8 as size_kb FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname ='cc' AND c.oid =i.indrelid AND c2.oid =i.indexrelid ORDER BY c2.relname;


查看索引定义

select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'cc';

select pg_get_indexdef(b.indexrelid);



PostgreSQL LSN  Log sequence number,日志序列号, WAL日志唯一的、全局的标识

select proname from pg_proc where proname like 'pg_%_lsn';

pg_current_wal_flush_lsn
pg_current_wal_insert_lsn
pg_current_wal_lsn
pg_last_wal_receive_lsn
pg_last_wal_replay_lsn

select pg_current_wal_lsn();
10/41B7F8A0

当前lsn对应的日志文件
select pg_walfile_name('10/41B7F8A0');

当前lsn在日志文件中的偏移量
SELECT * FROM pg_walfile_name_offset(pg_current_wal_lsn());

切换pg_wal日志
select pg_switch_wal();

select * from pg_replication_slots;