作者
digoal
日期
2017-09-12
标签
PostgreSQL , libpq , 连接串 , URI , options , jdbc
背景
连接数据库是最基本的操作之一，PostgreSQL libpq支持URI的连接模式，格式如下：
postgresql://[user[:password]@][netloc][:port][,...][/dbname][?param1=value1&...]
例子
postgresql://
postgresql://localhost
postgresql://localhost:5433
postgresql://localhost/mydb
postgresql://user@localhost
postgresql://user:secret@localhost
postgresql://other@localhost/otherdb?connect_timeout=10&application_name=myapp
postgresql://host1:123,host2:456/somedb?target_session_attrs=any&application_name=myapp
postgresql:///mydb?host=localhost&port=5433
postgresql://[2001:db8::1234]/database
postgresql:///dbname?host=/var/lib/postgresql
postgresql://%2Fvar%2Flib%2Fpostgresql/dbname
从10开始，支持连接多个主机
postgresql://host1:port1,host2:port2,host3:port3/
出了使用URI的写法，还支持这种格式
host=localhost port=5432 dbname=mydb connect_timeout=10
连接时，支持设定一些连接参数，例如application_name，target_session_attrs等等。还有一些数据库client参数也可以通过options这个参数传入（例如timezone），在建立连接后自动设置。
URI中支持的parameter详见：
https://www.postgresql.org/docs/10/static/libpq-connect.html
接下来使用psql来验证这个方法
连接时如何设置客户端参数
使用psql客户端进行验证
man psql
```
-d dbname
--dbname=dbname
Specifies the name of the database to connect to.
This is equivalent to specifying dbname as the first non-option argument on the command line.
If this parameter contains an = sign or starts with a valid URI prefix (postgresql:// or postgres://), it is treated as a conninfo string. See Section 33.1.1 for more information.复制
```
对于其他URI中非直接支持的客户端参数，需要通过options这个参数来进行设置
```
options
Specifies command-line options to send to the server at connection start.
For example, setting this to -c geqo=off sets the session's value of the geqo parameter to off.
Spaces within this string are considered to separate command-line arguments, unless escaped with a backslash ();
write \ to represent a literal backslash.
For a detailed discussion of the available options, consult Chapter 19.
```
与psql类似，postgres命令也支持类似方法设置启动参数
man postgres
```
-o extra-options
The command-line-style arguments specified in extra-options are
passed to all server processes started by this postgres process.
Spaces within extra-options are considered to separate arguments, unless escaped with a backslash (\); write \\ to represent a literal backslash. Multiple arguments can also be specified via multiple uses of -o. The use of this option is obsolete; all command-line options for server processes can be specified directly on the postgres command line.复制
```
使用psql验证非标准参数的连接参数的设置
1、比如我们需要设置客户端时区，连接时设置。
```
psql -d "host=127.0.0.1 port=1921 options='-c timezone=+10'"
psql (10beta4)
Type "help" for help.
postgres=# show timezone;
TimeZone
<+10>-10
(1 row)
postgres=# select now();
now
2017-09-12 23:57:58.174722+10
(1 row)
```
2、又比如，我们设置标准参数（即URI直接支持的参数）
```
psql postgres://postgres@127.0.0.1:1921/postgres?application_name=abc
psql (10beta4)
Type "help" for help.
postgres=# show application_name ;
application_name
abc
(1 row)
postgres=# \q
```
3、直接设置非标准参数，会导致合法性建议报错
psql postgres://postgres@127.0.0.1:1921/postgres?timezone=+10
psql: invalid URI query parameter: "timezone"
所以options参数，就是提供设置这种非标准参数的。
4、注意，psql在解析URI的options参数内容时，等号需要用%3D代替，这种写法，导致无法设置成功非标准参数
psql postgres://postgres@127.0.0.1:1921/postgres?options='-c TimeZone=+10'
psql: extra key/value separator "=" in URI query parameter: "options"
正确写法,使用percent encoding表示，如下。
https://en.wikipedia.org/wiki/Percent-encoding
``` psql postgres://postgres@127.0.0.1:1921/postgres?options='-c TimeZone%3D+10 -c extra_float_digits%3D2'
postgres=# show timezone; TimeZone
<+10>-10 (1 row)
postgres=# show extra_float_digits ; extra_float_digits
2 (1 row) ```
通过SET命令设置会话、事务级参数
如果以上方法无法满足非标准参数的设置，那么你还有两种方法可以实现非标准参数的设置，以timezone为例。
```
连接成功后，或者首次连接后，自动执行如下：
set timezone=+10;
```
通过配置database, role默认参数，设置会话参数
第二种方法，设置database, role的默认参数。例子
```
alter role all|username set timezone=+10;
alter database dbname set timezone=+10;
```
参考
https://www.postgresql.org/docs/10/static/libpq-connect.html
https://jdbc.postgresql.org/documentation/head/connect.html
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
