PostgreSQL連線串URI配置(libpq相容配置)

德哥發表於2017-09-17

背景

連線資料庫是最基本的操作之一，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;

參考