189 8069 5689
--psql功能及应用 --创建用户,role默认没有登录权限 create user mydbuser with encrypted password 'mydbuser'; --or create role mydbuser with encrypted password 'mydbuser'; alter role mydbuser with login; --创建表空间 mkdir -p /pgdata/10/data/pg_tbs/tbs_mydb create 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; --查看角色信息 \du SELECT * FROM pg_roles; --删除角色 revoke all on database mydb from test; drop role test; --连接 psql mydb mydbuser --列出数据库信息 pg_database \l --表空间列表 pg_tablespace \db --查看表信息 pg_class create 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_1 insert 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 postgres copy 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 \?
