前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >PostgreSQL的常见参数和技巧

PostgreSQL的常见参数和技巧

作者头像
数据和云
发布2019-08-06 16:39:25
1.2K0
发布2019-08-06 16:39:25
举报
文章被收录于专栏:数据和云

1. psql命令


1.1 General options

1.1.1- ?

我们可以psql -?或者psql --help看下psql有哪些可用参数:

代码语言:javascript
复制
[postgres@host01 ~]$ psql --help
psql is the PostgreSQL interactive terminal.
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
  -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.

Report bugs to <pgsql-bugs@postgresql.org>.

1.1.2- E

我们可以实验一下-E的效果:

代码语言:javascript
复制
[postgres@host01 ~]$ psql -E
psql (11.2)
Type "help" for help.

postgres=# \c yx
You are now connected to database "yx" as user "postgres".
yx=# \d
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' WHEN 'I' THEN 'index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | student | table | postgres
 public | yx      | table | postgres
(2 rows)

我们看到多出来一条sql,那么这条sql就是pg为我们展示\d的结果所使用的sql。

1.1.3- c

-c就是不用进psql,直接执行命令:

代码语言:javascript
复制
[postgres@host01 ~]$ psql yx -c "select * from student;"
 id |               name               | number 
----+----------------------------------+--------
  1 | aaa                              | 1023 
(1 row)

但是我们看哈,如果连着俩条sql什么效果:

代码语言:javascript
复制
[postgres@host01 ~]$ psql yx -c "select * from student;select count(1) from student;"
 count 
-------
     1
(1 row)

我们看到,只显示了最后一条sql的执行结果。

但是前面的sql到底执行了么?肯定执行了,前面的sql语法错了要报错。我们看下面一个例子:

代码语言:javascript
复制
[postgres@host01 ~]$ psql yx -c "insert into student values(2,'bbbb',1024);insert into student values(3,'cccc',1025);select * from student;"
 id |               name               | number 
----+----------------------------------+--------
  1 | aaa                              | 1023 
  2 | bbbb                             | 1024 
  3 | cccc                             | 1025 
(3 rows)

那么我们要想显示一批sql的执行结果咋办呢?一条命令指定一个-c:

代码语言:javascript
复制
[postgres@host01 ~]$ psql yx -c "select * from student;" -c "select count(1) from student;"
 id |               name               | number 
----+----------------------------------+--------
  1 | aaa                              | 1023 
  2 | bbbb                             | 1024 
  3 | cccc                             | 1025 
(3 rows)

 count 
-------
     3
(1 row)

那么为了方便,还可以使用下面的-f。

1.1.4- f

我们把刚才要执行的sql写到一个文件中:

代码语言:javascript
复制
[postgres@host01 ~]$ cat test.sql
select * from student;
select count(1) from student;

然后用-f来执行这个文件:

代码语言:javascript
复制
[postgres@host01 ~]$ psql yx -f test.sql
 id |               name               | number 
----+----------------------------------+--------
  1 | aaa                              | 1023 
  2 | bbbb                             | 1024 
  3 | cccc                             | 1025 
(3 rows)

 count 
-------
     3
(1 row)

那我想实现动态sql,咋办,就是-v。

1.1.5- v

我们把test.sql改一下:

代码语言:javascript
复制
[postgres@host01 ~]$ cat test.sql 
select * from student where id=:1;
select * from student where id=:b;

然后我们使用-v来给绑定变量赋值:

代码语言:javascript
复制
[postgres@host01 ~]$ psql yx -f test.sql -v 1=2 -v b=1
 id |               name               | number 
----+----------------------------------+--------
  2 | bbbb                             | 1024 
(1 row)

 id |               name               | number 
----+----------------------------------+--------
  1 | aaa                              | 1023 
(1 row)

我们知道,PLSQL里的动态sql,表名和列名是不能作为绑定变量。需要在动态sql中将表名、列名做为字符串变量,来拼接sql。

psql结合-f -v来实现动态sql就不一样了,我们改写test.sql如下:

代码语言:javascript
复制
[postgres@host01 ~]$ cat test.sql 
select * from :tab1 where id=2;
select * from :tab2 where id=1;

然后我们执行来看效果:

代码语言:javascript
复制
[postgres@host01 ~]$ psql yx -f test.sql -v tab1=student -v tab2=t1
 id |               name               | number 
----+----------------------------------+--------
  2 | bbbb                             | 1024 
(1 row)

 id | name 
----+------
  1 | yx
(1 row)

所以说,psql这个并不是真正的绑定变量传值,而是跟拼接字符串一个道理。

1.1.6- d -l -V

-V可以看psql的版本号,-l就是列出可用的database name,-d 就是直接连到某个database中:

代码语言:javascript
复制
[postgres@host01 ~]$ psql -V
psql (PostgreSQL) 11.2

[postgres@host01 ~]$ psql -l
Password for user postgres: 
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 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
 yx        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

[postgres@host01 ~]$ psql -d yx
Password for user postgres: 
psql (11.2)
Type "help" for help.

yx=# select current_database();
 current_database 
------------------
 yx
(1 row)

1.2 Connection options

代码语言:javascript
复制
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)

想要用到这些,需要修改如下文件:

代码语言:javascript
复制
[postgres@host01 ~]$ ls $PGDATA/pg_hba.conf
/pgdata/pg_hba.conf

约等于配置黑白名单,以及访问方式,文件主要内容如下:

代码语言:javascript
复制
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     password
#local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             192.168.12.10/24        trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

method可以控制访问方式:

代码语言:javascript
复制
# METHOD can be "trust", "reject", "md5", "password", "scram-sha-256",
# "gss", "sspi", "ident", "peer", "pam", "ldap", "radius" or "cert".
# Note that "password" sends passwords in clear text; "md5" or
# "scram-sha-256" are preferred since they send encrypted passwords.

修改完该文件记得重载才能生效:

代码语言:javascript
复制
[postgres@host01 ~]$ pg_ctl reload
server signaled

1.3 Output format options

这个还是有点儿意思,比如-H就是用html的格式输出结果:

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2019-08-04,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 数据和云 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建,轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量,更专注于业务发展,让企业一站式享受数据上云及分布式架构的技术红利!
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档