pgadmin客户端

下载地址
https://www.postgresql.org/ftp/pgadmin/pgadmin4/v5.0/windows/
右键以管理员身份运行，下一步安装即可。

psql常用命令介绍

• 查看数据库列表
  \l
• 查看表空间列表
  \db
• 查看表定义
  \d 接表名
• 查看表大小
  \dt+ 接表名
• 查看索引大小
  \di 接索引名
• 将SQL命令的语法列出
  \h 后接SQL命令关键字
• 查看用户
  \du
• 切换连接的数据库
  \c postgres
• 查看数据库最大连接数
  show max_connections;
• 查看数据库当前连接数
  select count(*), usename from pg_stat_activity group by usename;
• 数据库预留给超级用户的连接数
  show superuser_reserved_connections;
• 备份库中所有表
  pg_dump -U postgresql test >/tools/test.sql
• 备份库中指定的表
  pg_dump -U postgresql test -t tb_emp >/tools/tb_emp.sql
• 备份所有数据库
  pg_dumpall -U postgres >/tools/all.sql
• 恢复数据
  psql -U postgres -d test </tools/test.sql
• 慢查询
  select * from pg_stat_activity where state<>'idle' and now()-query_start > interval '5 s' order by query_start;
• 查看用户名密码
  select usename,passwd from pg_shadow;
• 修改用户名密码
  ALTER USER qguser PASSWORD 'qguser'
• 导出表中数据
  create table test_uc as select * from t_uc_organize;
  copy test_uc to '/opt/postgres/test_uc.txt' with delimiter as '|';
• 导出表结构
  /web/pgsql/bin/pg_dumpall -U postgres -s >all.sql
• 导入数据
  /web/pgsql/bin/psql -U postgres </web/all.sql
• 获取当前db所有表
  select * from pg_tables;
• 查看表结构
  \d tablename;
• 普通用户登录
  psql -U username -W databasename
• 创建只读用户
  \c gmcc;
  create user gmccuser1 with password 'GEz5qCW/AtxvVKpAGmcc';
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO gmccuser1;
• 查看某用户的表权限
  select * from information_schema.table_privileges where grantee='testuser';
• 整理索引：
  REINDEX TABLE t_els_system_setting;
• 回收空间：
  vacuum full t_els_system_setting;
• 查找锁表进程id
  select pid from pg_locks l join pg_class t on l.relation = t.oid where t.relkind = 'r' and t.relname = 't_tbcs_order_snap';
• 查找锁表语句
  select pid, state, usename, query, query_start from pg_stat_activity where pid in ( select pidfrom pg_locks l join pg_class t on l.relation = t.oid and t.relkind = 'r' where t.relname = 't_tbcs_order_snap');
• 查找所有活动的被锁的表
  select pid, state, usename, query, query_start
  from pg_stat_activity
  where pid in (
  select pid from pg_locks l
  join pg_class t on l.relation = t.oid
  and t.relkind = 'r'
  );
• 解锁
  SELECT pg_cancel_backend(pid);
• 批量解锁
  select pg_cancel_backend(pid)
  from pg_stat_activity
  where pid in (
  select pid from pg_locks l
  join pg_class t on l.relation = t.oid
  and t.relkind = 'r'
  );
• 查看当前正在使用的连接数
  select count(1) from pg_stat_activity;
• 显示系统保留的用户数
  show superuser_reserved_connections;

psql执行sql脚本

/web/pgsql/bin/psql -c "select current_user";
加上-At选项仅显示命令返回的结果。

psql定制日常维护脚本

如果psql没有带-X选项，psql尝试读取和执行用户~/.psqlrc启动文件中的命令，结合这个文件能够方便的预先定制维护脚本。

• 查询活动会话
  vim ~/.psqlrc
  \set active_session 'select pid,usename,datname,client_addr from pg_stat_activity where pid <> pg_backend_pid() and state='active' order by query;'
• 查询会话等待事件
  vim ~/.psqlrc
  \set wait_event '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;'
• 查看数据库连接数
  vim ~/.psqlrc
  \set connections 'select usename,datname,client_addr,count(*) from pg_stat_activity where pid <> pg_backend_pid() group by 1,2,3 order by 1,2,4 desc;'

psql亮点功能

• 显示SQL执行时间
  \timing命令用于设置打开或关闭显示SQL的执行时间，单位为毫秒。
• 反复执行当前SQL
  \watch seconds
  seconds表示两次执行间隔的时间，以秒为单位，默认为2秒。
• Tab键自动补全
• 支持箭头上下翻历史SQL命令
• psql客户端提示符
  %M：数据库服务器别名，不是指主机名，显示的是psql -h参数设置的值；
  %>：数据库服务器的端口号；
  %n：数据库会话的用户名；
  %/：当前数据库名称；
  %#：如果是超级用户则显示”#“，其它用户则显示”>“；
  %p：当前数据库连接的后台进程号；
  %R：在PROMPT1中通常显示”=“，如果进程被断开则显示”！“。
  配置
  vim ~/.psqlrc
  \set PROMPT1 '%/@%M:%>%R%#'