psql是PostgreSQL自带的一个交互式命令行客户端,使用起来非常的灵活。一般如果是在Linux环境下,我们都会选择它。而且,当我们要在Shel脚本中操作数据库的话,那psql就非常的方便了。

一,psql的常用选项

第一类:连接相关

  • -d:指定要连接的数据库。
  • -U:连接时使用的用户。
  • -h:要连接的主机地址(IP)。
  • -p:连接的端口号。

比如,使用数据库用户allan登陆192.168.1.148主机上面监听端口为5432的PostgreSQL数据库,命令如下:

allan@ubuntu:~$ psql -d postgres -U allan -h 192.168.1.148 -p 5432
psql (9.3.5)
Type "help" for help.

postgres=#

注意点:

  1. 这几个参数都有默认值,可以使用psql --help查看默认值。如果不明确使用某个选项,就使用默认值。比如如果默认登陆的数据库是postgres,默认端口号是5432,那么上面的命令可以简写为:psql -U allan -h 192.168.1.148
  2. 要注意使用-h与不使用-h的区别:一般不加-h是默认使用的是local socket,亦即UNIX socket(该socket此处不详细介绍),该socket只能用于本机登陆,所以一般认证机制会稍微比较宽松。而加-h参数都使用的都是TCP socket,可以在不同主机间通信。这两者的安全级别在pg_hba.conf文件里面是分开规定的。所以,psql -d postgres -h 127.0.0.1psql -d postgres都登陆的是本机的postgres数据库,但是走的通道却是不一样的,前者走的是TCP socket通道,后者走的是Unix socket通道。
  3. PostgreSQL数据库默认只监听本机,所以如果需要接受其他主机的连接,需要设置两个文件:postgresql.conf和pg_hba.conf文件。

第二类:执行命令

  • -c:执行该参数后面跟的字符串命令。如果字符串中包含多个SQL命令,那他们将在一个事务里面处理,除非明确使用BEGIN/COMMIT命令将他们分隔开。
  • -f:执行该参数后面跟的文件。与psql内部的命令i作用相同。

第三类:其他选项

  • -v:设置psql的参数。psql有许多参数可以设置,如果我们只想让某些选项只在某一次会话中有效,那么我们就不能将设置写在配置文件里面,而需要登陆的时候加上-v参数进行设置。
  • -V:打印psql的版本号。
  • -l:列出可用的数据库。
  • -o:后跟文件名,功能是将查询结果重定向到该文件中。
    其他还有许多参数,但是都不太常用,这里不再一一列出,可以使用psql -?或者psql --help查看。

二,psql返回值

psql共有四个返回值:0,1,2,3.

  • 0:正常结束,向shell返回0.
  • 1:自身发生致命错误,比如内存用尽,文件不存在等。
  • 2:如果和数据库的连接失效而且会话不再活跃,返回2.
  • 3:如果脚本中发生了错误,并且设置了ON_ERROR_STOP,则返回3.

这里需要注意的是0和3这两个返回值。我们经常会利用psql去执行一个文件,但是却发现即使文件里面执行时出了错,却返回值依旧是0,而不是3.这是因为要返回3,有两个条件:脚本中发生了错误,并且设置了ON_ERROR_STOP选项。如果不设置后者,那么即使出错,也返回0.无法判断文件是否真正成功执行。

那ON_ERROR_STOP这个选项是什么意思呢?其实很简单,就是如果遇到执行失败,就停止继续执行。比如说test.sql文件里面写了10句sql命令,而且第6条执行会出错。那么不设置该选项时,第6条失败后,后面的语句会继续执行,且返回值为0。但是如果设置了ON_ERROR_STOP选项,那么当第6条执行出错后,就停止执行,后面的语句将不再执行,且返回值为3.这在shell脚本中判断psql是否真正执行成功非常的有用。看下面的例子:

# 创建测试用的表test
allan@ubuntu:~$ psql postgres
psql (9.3.5)
Type "help" for help.

postgres=# CREATE TABLE test(
postgres(#    word   varchar
postgres(# );
CREATE TABLE
postgres=# d test
          Table "public.test"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 word   | character varying |

postgres=# q

# 创建测试用的sql文件
allan@ubuntu:~$ cat test.sql
INSERT INTO test VALUES('a');
INSERT INTO test VALUES(b);       # 该句存在语法错误
INSERT INTO test VALUES('c');

# 利用psql刷入test.sql文件,执行第二条语句时出错
allan@ubuntu:~$ psql -d postgres -f test.sql
INSERT 0 1
ERROR:  column "b" does not exist at character 25
STATEMENT:  INSERT INTO test VALUES(b);
psql:test.sql:2: ERROR:  column "b" does not exist
LINE 1: INSERT INTO test VALUES(b);
                                ^
INSERT 0 1                   # 虽然第二句执行出错,但第三条依旧执行
allan@ubuntu:~$ echo $?
0                            # 虽然执行脚本时出错,但是psql向shell的返回值为0

# 查看结果,可见a、b入库。
allan@ubuntu:~$ psql postgres
psql (9.3.5)
Type "help" for help.

postgres=# select * from test;
 word
------
 a
 c
(2 rows)

postgres=# truncate table test;    # 清空表,继续后面的测试
TRUNCATE TABLE
postgres=# q

# 加了ON_ERROR_STOP选项后再测试
allan@ubuntu:~$ psql postgres -v ON_ERROR_STOP=true -f test.sql
INSERT 0 1
ERROR:  column "b" does not exist at character 25
STATEMENT:  INSERT INTO test VALUES(b);
psql:test.sql:2: ERROR:  column "b" does not exist
LINE 1: INSERT INTO test VALUES(b);
                                ^
allan@ubuntu:~$ echo $?
3                      #第二句执行出错后,后面的没有继续执行,且返回值为3

查看数据库,只有a入库了
allan@ubuntu:~$ psql postgres
psql (9.3.5)
Type "help" for help.

postgres=# select * from test;
 word
------
 a
(1 row)

postgres=# q