PostgreSQL 连接串URI配置(libpq兼容配置)

背景

连接数据库是最基本的操作之一，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 sessions 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 

--------------------

(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安装与配置 postgresql数据库安装可主要分为两种，一种是apt快速安装，一种是二进制安装。本次安装我们将一一记录这两种安装步骤。
PostgreSQL安装、配置及简单使用方法 一、PostgreSQL简介 1、什么是PostgreSQL PostgreSQL数据库是目前功能最强大的开源数据库，支持丰富的数据类型（如JSON何JSONB类型，数组类型）和自定义类型。而且它提供了丰富的接口，可以很容易地扩展它的功能，如可以在GiST框架下实现自己的索引类型等，它还支持使用C语言写自定义函数、触发器，也支持使用流行的语言写自定义函数，比如其中的PL/Perl提供了使用Perl语言写自定义函数的功能，当然还有PL/Python、PL/Tcl,等等。 2、PostgreSQL数据库的优势 PostgreSQL数据库是目前功能最强大的开源数据库，它是最接近工业标准SQL