详述PostgreSQL的常见参数和技巧

原文：https://www.enmotech.com/web/detail/1/810/1.html （复制链接，打开浏览器即可查看）

原创：云和恩墨交付工程师燕鑫

导读：本文主要详述PostgreSQL的常见参数以及一些技巧。

1. psql命令

---

1.1 General options

1.1.1- ？

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

[postgres@host01 ~]$ psql --helppsql 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 exitInput 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 byteConnection 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 SQLcommands) from within psql, or consult the psql section in the PostgreSQLdocumentation.Report bugs to <pgsql-bugs@postgresql.org>.

1.1.2- E

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

[postgres@host01 ~]$ psql -Epsql (11.2)Type "help" for help.postgres=# \c yxYou 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.relnamespaceWHERE 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，直接执行命令：

[postgres@host01 ~]$ psql yx -c "select * from student;" id |              name              | number----+----------------------------------+-------- 1 | aaa                             | 1023(1 row)

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

[postgres@host01 ~]$ psql yx -c "select * from student;select count(1) from student;" count-------    1(1 row)

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

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

[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：

[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写到一个文件中：

[postgres@host01 ~]$ cat test.sqlselect * from student;select count(1) from student;

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

[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改一下：

[postgres@host01 ~]$ cat test.sqlselect * from student where id=:1;select * from student where id=:b;

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

[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如下：

[postgres@host01 ~]$ cat test.sqlselect * from :tab1 where id=2;select * from :tab2 where id=1;

然后我们执行来看效果：

[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中：

[postgres@host01 ~]$ psql -Vpsql (PostgreSQL) 11.2[postgres@host01 ~]$ psql -lPassword 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 yxPassword for user postgres:psql (11.2)Type "help" for help.yx=# select current_database(); current_database------------------ yx(1 row)1.2 Connection optionsConnection 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)

想要用到这些，需要修改如下文件：

[postgres@host01 ~]$ ls $PGDATA/pg_hba.conf/pgdata/pg_hba.conf

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

# TYPE DATABASE       USER           ADDRESS                METHOD# "local" is for Unix domain socket connections onlylocal  all            all                                    password#local  all            all                                    trust# IPv4 local connections:host   all            all            127.0.0.1/32           trusthost   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                                    trusthost   replication    all            127.0.0.1/32           trusthost   replication    all            ::1/128                trust

method可以控制访问方式：

# 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.

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

[postgres@host01 ~]$ pg_ctl reloadserver signaled

1.3 Output format options

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

想了解更多关于数据库、云技术的内容吗？

快来关注“数据和云”公众号、“云和恩墨”官方网站，我们期待与大家一同学习和进步！

（扫描上方二维码，关注“数据和云”公众号，即可查看更多科技文章）

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29715045/viewspace-2652683/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29715045/viewspace-2652683/