--psql功能及应用--创建用户,role默认没有登录权限createusermydbuserwithencryptedpassword'mydbuser';--orcreaterolemydbuserwithencryptedpassword'mydbuser';alterrolemydbuserwithlogin;--创建表空间mkdir-p/pgdata/10/data/pg_tbs/tbs_mydbcreatetablespacetbs_mydbownermydbuserlocation'/pgdata/10/data/pg_tbs/tbs_mydb';--创建数据库createdatabasemydbwithowner=mydbusertemplate=template0encoding='UTF8'tablespace=tbs_mydb;--赋权grantallondatabasemydbtomydbuserwithgrantoption;grantallontablespacetbs_mydbtomydbuser;--查看角色信息\duSELECT*FROMpg_roles;--删除角色revokeallondatabasemydbfromtest;droproletest;--连接psqlmydbmydbuser--列出数据库信息pg_database\l--表空间列表pg_tablespace\db--查看表信息pg_classcreatetabletest_1(idint4,nametext,create_timetimestampwithouttimezonedefaultclock_timestamp());altertabletest_1addprimarykey(id);\dtest_1\dt+test_1insertintotest_1(id,name)selectn,n||'_francs'fromgenerate_series(1,300000)n;insertintotest_1values(100001,'100001_francs');--索引大小\di+test_1_pkey--获取元命令psql-Emydbmydbuser--大数据量，copy(只能超级用户)比\copy性能高psqlmydbpostgrescopytable_copyfrom'/home/postgres/test_copy_in.txt';copytable_copyto'/home/postgres/table_copy_in.txt';--csv格式copytable_copyto'/home/postgres/table_copy_in.csv'withcsvheader;--A取消格式化输出，-t只显示数据psql-At-c"selectoid,relname,relfilenodefrompg_classlimit2"mydbmydbuser--执行相关脚本psqlmydbmydbuser-fxx.sql--查看活动会话selectpid,usename,datname,query,client_addrfrompg_stat_activitywherepid<>pg_backend_pid()andstate='active'orderbyquery;--查看等待事件selectpid,usename,datname,query,client_addr,wait_event_type,wait_eventfrompg_stat_activitywherepid<>pg_backend_pid()andwait_eventisnotnullorderbywait_event_type;--查看数据库连接数selectdatname,usename,client_addr,count(*)frompg_stat_activitywherepid<>pg_backend_pidgroupby1,2,3orderby1,2,4desc;--编辑.psqlrc\setactive_session'selectpid,usename,datname,query,client_addrfrompg_stat_activitywherepid<>pg_backend_pid()andstate=\'active\'orderbyquery';--执行:active_session即可--反复执行当前sql\watch[seconds]--查看当前时间selectnow();--psql查看psql--help\?