PG数据库实用SQL-随时更新

#设置禁止连接

UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'test';

 

#中断会话连接

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'test';

#创建数据库及用户 ，用户赋权

create user test01 with password '123456'

create database test01 owner test01;

grant all PRIVILEGES on database test01 to test01

 

#修改密码

alter user postgres with password 'new password';

 

#查询当前shceme 的所有表

select tablename from pg_tables where schemename = 'XXX';

 

#设置密码永不过期

alter user user_name with valid until 'infinity';

 

#查询会话

select pid,array_to_string(pg_blocking_pids(pid),',') blocked,state,wait_event,wait_event_type,current_timestamp-query_start AS runtime,datname,usename,application_name,client_addr,client_port,query_start,query
from pg_stat_activity;