PostgreSQL12客户端及常用命令介绍

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%#'
上一篇:035.PGSQL-psql元命令 \timing 和 psql客户端提示符


下一篇:报错注入