【PG_PSQL】PSQL使用测试

--psql功能及应用--创建用户,role默认没有登录权限create user mydbuser with encrypted password 'mydbuser';--orcreate role mydbuser with encrypted password 'mydbuser';alter role mydbuser with login;     --创建表空间mkdir -p /pgdata/10/data/pg_tbs/tbs_mydbcreate tablespace tbs_mydb owner mydbuser location '/pgdata/10/data/pg_tbs/tbs_mydb';--创建数据库create database mydb with owner=mydbuser template=template0 encoding='UTF8' tablespace=tbs_mydb;--赋权grant all on database mydb to mydbuser with grant option;grant all on tablespace tbs_mydb to mydbuser;--查看角色信息\duSELECT * FROM pg_roles;--删除角色revoke all on database mydb from test;drop role test;--连接psql mydb mydbuser--列出数据库信息 pg_database\l--表空间列表  pg_tablespace\db--查看表信息 pg_classcreate table test_1(id int4,name text,create_time timestamp without time zone default clock_timestamp());alter table test_1 add primary key (id);\d test_1\dt+ test_1insert into test_1(id,name) select n,n ||'_francs' from generate_series(1,300000) n;insert into test_1 values(100001,'100001_francs');--索引大小\di+ test_1_pkey--获取元命令psql -E mydb mydbuser--大数据量，copy(只能超级用户)比\copy性能高psql mydb postgrescopy table_copy from '/home/postgres/test_copy_in.txt';copy table_copy to '/home/postgres/table_copy_in.txt';--csv 格式copy table_copy to '/home/postgres/table_copy_in.csv' with csv header;--A 取消格式化输出，-t只显示数据 psql -At -c "select oid,relname,relfilenode from pg_class limit 2" mydb mydbuser--执行相关脚本psql mydb mydbuser -f xx.sql--查看活动会话select pid,usename,datname,query,client_addr from pg_stat_activity where pid<>pg_backend_pid() and state='active' order by query;--查看等待事件select pid,usename,datname,query,client_addr,wait_event_type,wait_event from pg_stat_activity where pid<>pg_backend_pid() and wait_event is not null order by wait_event_type;--查看数据库连接数select datname,usename,client_addr,count(*) from pg_stat_activity where pid<>pg_backend_pid group by 1,2,3 order by 1,2,4 desc;--编辑 .psqlrc\set active_session 'select pid,usename,datname,query,client_addr from pg_stat_activity where pid<>pg_backend_pid() and state=\'active\' order by query';--执行:active_session 即可--反复执行当前sql\watch [seconds]--查看当前时间select now();--psql 查看psql --help\?