769
PostgreSQL 連接串URI配置(libpq兼容配置)
標簽
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"
正確寫法
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
最後更新：2017-09-18 00:04:18