PG-客户端工具

客户端工具

pgAdmin 是一款功能丰富、开源免费的 PostgreSQL 图形化客户端工具
psql 是 PostgreSQL 自带的命令行客户端工具，功能全面

pgAdmin

pgAdmin 4 是一款专门针对PostgreSQL数据库基于浏览器的BS架构的客户端管理软件。

psql

用法

Usage:
  psql [OPTION]... [DBNAME [USERNAME]]

General options:
  -c, --command=COMMAND    run only single command (SQL or internal) and exit
  -d, --dbname=DBNAME      database name to connect to (default: "postgres")
  -f, --file=FILENAME      execute commands from file, then exit
  -l, --list               list available databases, then exit
  -v, --set=, --variable=NAME=VALUE
                           set psql variable NAME to VALUE
                           (e.g., -v ON_ERROR_STOP=1)
  -V, --version            output version information, then exit
  -X, --no-psqlrc          do not read startup file (~/.psqlrc)
  -1 ("one"), --single-transaction
                           execute as a single transaction (if non-interactive)
  -?, --help[=options]     show this help, then exit
      --help=commands      list backslash commands, then exit
      --help=variables     list special variables, then exit

Input and output options:
  -a, --echo-all           echo all input from script
  -b, --echo-errors        echo failed commands
  -e, --echo-queries       echo commands sent to server
  -E, --echo-hidden        display queries that internal commands generate
  -L, --log-file=FILENAME  send session log to file
  -n, --no-readline        disable enhanced command line editing (readline)
  -o, --output=FILENAME    send query results to file (or |pipe)
  -q, --quiet              run quietly (no messages, only query output)
  -s, --single-step        single-step mode (confirm each query)
  -S, --single-line        single-line mode (end of line terminates SQL command)

Output format options:
  -A, --no-align           unaligned table output mode
      --csv                CSV (Comma-Separated Values) table output mode
  -F, --field-separator=STRING
                           field separator for unaligned output (default: "|")
  -H, --html               HTML table output mode
  -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see \pset command)
  -R, --record-separator=STRING
                           record separator for unaligned output (default: newline)
  -t, --tuples-only        print rows only
  -T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)
  -x, --expanded           turn on expanded table output
  -z, --field-separator-zero
                           set field separator for unaligned output to zero byte
  -0, --record-separator-zero
                           set record separator for unaligned output to zero byte

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "local socket")
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "postgres")
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)

For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.

元命令

psql 中的元命令是指以反斜线开头的命令， psql 提供丰富的元命令，能够便捷地管理数据库，比如查看数据库对象定义、查看数据库对象占用空间大小、列出数据库各种对象名称、数据导人导出等。

查看数据库列表

10:37:12 [local]:5432 dev@devdb=> \l
********* QUERY **********
SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************

                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
------------+----------+----------+-------------+-------------+-----------------------
 devdb      | dev      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 regression | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
            |          |          |             |             | postgres=CTc/postgres
 template0  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
 template1  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
(5 rows)

10:47:38 [local]:5432 dev@devdb=>

\db 查看表空间列表

10:47:38 [local]:5432 dev@devdb=> \db
********* QUERY **********
SELECT spcname AS "Name",
  pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
  pg_catalog.pg_tablespace_location(oid) AS "Location"
FROM pg_catalog.pg_tablespace
ORDER BY 1;
**************************

                List of tablespaces
    Name    |  Owner   |          Location          
------------+----------+----------------------------
 devtbs     | postgres | /ups/data/pgdata/12/pg_usr
 pg_default | postgres | 
 pg_global  | postgres | 
(3 rows)

10:48:37 [local]:5432 dev@devdb=>

\x 设置查询结果输出格式

类似mysql中的\G选项，按列输出

获取元命令对应的 SQL 代码

psql 连接数据库时，加上-E参数

psql -E

\watch 反复执行当前 SQL

-- 每秒执行一次 now() 命令 
SELECT now();
\watch 1

执行SQL脚本

-- 1. psql 的 -c 选项支持在操作系统层面通过 psql 向数据库发起 SQL 命令
psql -c "SELECT current_user ;"
psql -At -c "SELECT current_user ;"

-- 输出
[postgres@progs ~]$ psql -c "SELECT current_user ;"
 current_user 
--------------
 postgres
(1 row)

[postgres@progs ~]$ psql -At -c "SELECT current_user ;"
postgres
[postgres@progs ~]$

-- 2. -f 选项 通过 psql 向数据库发起 SQL文件命令
echo "SELECT current_user;" > t.sql
psql -f t.sql

传递变量

\set 元命令方式传递变量

10:57:48 [local]:5432 dev@devdb=> \set v_id 1
10:57:55 [local]:5432 dev@devdb=> select * from tb1 where id=:v_id;
 id | ival | description |             ctime             
----+------+-------------+-------------------------------
  1 |    1 |             | 2020-06-12 11:10:00.769246+08
(1 row)

10:58:10 [local]:5432 dev@devdb=>

psql 的 -v 参数传递变量

echo "SELECT * FROM tb1 WHERE id=:v_id" > t.sql

[postgres@progs ~]$ psql -v v_id=1 devdb dev -f t.sql
 id | ival | description |             ctime             
----+------+-------------+-------------------------------
  1 |    1 |             | 2020-06-12 11:10:00.769246+08
(1 row)

[postgres@progs ~]$

维护脚本

查询活动会话

SELECT pid, usename , datname , query , client_addr
FROM pg_stat_activity
WHERE pid <> pg_backend_pid () AND state='active' 
ORDER BY query;

state进程的状态值：

active ：后台进程正在执行 SQL 。
idle ：后台进程为空闲状态，等待后续客户端发出命令。
idle in transaction ：后台进程正在事务中，并不是指正在执行 SQL 。
idle in transaction (aborted）：和 idle in transaction 状态类似，只是事务中的部分SQL 异常。

定制~/.psqlrc脚本文件，编辑如下内容

cat >> ~/.psqlrc <<-EOF
\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;'
EOF

[postgres@progs ~]$ cat ~/.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 命令
[postgres@progs ~]$ psql devdb dev
psql (12.0)
Type "help" for help.

devdb=> :active_session
 pid | usename | datname | query | client_addr 
-----+---------+---------+-------+-------------
(0 rows)

devdb=> exit

查询等待事件

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;

定制~/.psqlrc脚本文件，编辑如下内容

cat >> ~/.psqlrc <<-EOF
\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;'
EOF

客户端提示符

psql 默认有三个提示符： PROMPT1 、 PROMPT2 、 PROMPT3

PROMPT1 是指当 psql 等待新命令发出时的常规提示符，这个提示符使用得最多
PROMPT2 是指在命令输入过程中等待更多输入时发出的提示符，例如当命令没有使用分号终止或者
引用没有被关闭时就会发出这个提示符， PROMPT2 的默认设直值与 PROMPT1 一样；
PROMPT3 指在运行一个 SQL COPY FROM STDIN 命令并且需要在终端上输入一个行值时发出的提示符。

选项

%M ：数据库服务器别名，不是指主机名，显示的是 psql 的 -h 参数设置的值；当连
接建立在 Unix 域套接字上时则是 [local]
%> ：数据库服务器的端口号。
%n ：数据库会话的用户名，在数据库会话期间，这个值可能会因为命令 SET
SESSION AUTHORIZATION 的结果而改变。
%/ ：当前数据库名称。
%# ：如果是超级用户则显示“＃”，其他用户显示“＞”，在数据库会话期间，这个
值可能会因为命令 SET SESSION AUTHORIZATION 的结果而改变。
%p ：当前数据库连接的后台进程号。
%R ：在 PROMPT1 中通常显示“＝”，如果进程被断开则显示“！” 。
%x: 指事务状态–通常为空白，除非在事务语句块中（*）

配置方式

临时配置

-- 查看当前配置 \echo :PROMPT1
11:24:50 [local]:5432 dev@devdb=> \echo :PROMPT1
%`date +%H:%M:%S` %M:%[%033[1;35m%]%>%[%033[0m%] %n@%/%R%#%x 
11:24:55 [local]:5432 dev@devdb=> 
                                       
-- 设置
\set PROMPT1 '%M%R%#'

文件配置方式

vi ~/.psqlrc 添加以下内容

-- 登录提示符
--\set PROMPT1 '%n->%/@%M:%>%R%# '
\set PROMPT1 '%`date +%H:%M:%S` %M:%[%033[1;35m%]%>%[%033[0m%] %n@%/%R%#%x '
\set PROMPT2 '%M %n@%/%R%# '
-- 关键字大写形式
\set COMP_KEYWORD_CASE upper

posted @ 2020-09-15 14:47 KuBee 阅读(2065) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

KuBee

～～～～成长轨迹～～～