JDBC PostgreSQL Connection String

Giriş

Açıklaması şöyle. Yani host, port, database parametreleri tanımlanabilir.. Eğer tanımlanmazsa her birinin varsayılan değeri var. Örneğin database tanımlanmazsa bağlanırken kullanılan username kabul edilir.

... this takes one of the following forms:

- jdbc:postgresql:database

- jdbc:postgresql:/

- jdbc:postgresql://host/database

- jdbc:postgresql://host/

- jdbc:postgresql://host:port/database

- jdbc:postgresql://host:port/

The parameters have the following meanings:

host = The host name of the server. Defaults to localhost . To specify an IPv6 address your must enclose the host parameter with square brackets, for example: jdbc:postgresql://[::1]:5740/accounting

port = The port number the server is listening on. Defaults to the PostgreSQL® standard port number (5432).

database = The database name. The default is to connect to a database with the same name as the user name used to connect to the server.

JDBC ile bağlanmak için gereken parametreler Postgres jdbc documentation sayfasında.

Veri Tabanı İsmi Belirtilmezse

Yani jdbc:postgresql://host:port/ şeklide belirtirsek varsayılan veri tabanı ismi kullanıcı ismi ile aynı. Açıklaması şöyle

... the dbname parameter in the connection string defaults to the value of the user parameter.

currentSchema

Schema yazısına taşıdım. Kısaca birden fazla schema'yı görebilsin diye şöyle yaparız

jdbc:postgresql://localhost:5432/tst?currentSchema=app1,public

reWriteBatchedInserts

Açıklaması şöyle

Asking PostgreSQL to Rewrite batched inserts

Hibernate will send a multiple insert statements to RDBMS at once, in order to insert data, and this will be done in the same transaction which is great; however if we are using PostgreSQL we could go a little further in our optimizations by asking him to rewrite those inserts to a single multi-value insert statement; this way if we have a 100 insert statements, it will rewrites them to a single multi-value statement.

Ayrı ayrı olan bir çok INSERT cümlesini tek bir INSERT + çoklu VALUES haline getirir. Yani şöyle olur

// before Rewrite batched inserts
INSERT INTO container( ...) VALUES (...);
INSERT INTO container( ...) VALUES (...);
....
INSERT INTO container( ...) VALUES (...);
 
 
// After PostgreSQL to Rewrite batched inserts
INSERT INTO container( ...) VALUES (...),(...) ..., (...);

Örnek

Şöyle yaparız

jdbc:postgresql://localhost:5432/mastership?reWriteBatchedInserts=true

Prepared Statement Ayarları

3 tane ayar yapılabilir. Açıklaması şöyle

prepareThreshold, default: 5

preparedStatementCacheQueries, default: 256

preparedStatementCacheSizeMiB, default: 5

prepareThreshold

Açıklaması şöyle 5 prepared statement çalıştırıldıktan sonra server-side hale geliyor.

When a prepared statement is defined and executed in Java, it doesn't create a server-side prepared statement. Instead, it waits for 4 client-side executions before a server-side prepared statement is created. 

preparedStatementCacheQueries

Açıklaması şöyle. Prepared statement kapatılsa bile sunucuda yaşamaya devam ediyor.

A prepared statement is automatically cached. It's logical a prepared statement is kept available as long as the prepared statement is not closed. It's less obvious that the prepared statement is cached even if it's closed. The reason for the cache is to keep a potential server-side prepared statement in existence.

preparedStatementCacheSizeMiB

Açıklaması şöyle

The prepared statement cache described with preparedStatementCacheQueries also has a limit in size, which is 5 MB. Please mind this is the client-side prepared statement itself, not the data.

What I don't know for the cache's number of queries and size is how that relates to open/used prepared statements, in other words: is the number the total (open+closed) number or the number of closed prepared statements, and does this limit open cursors.

user + password Ayarları

Örnek

Şöyle yaparız

jdbc:postgresql://myserver:5432/test?user=postgres&password=<postgresql-password>

ssl Ayarları

Açıklaması şöyle. Yani SSL kullanılmasını etkinleştirir. 

Connect using SSL. The server must have been compiled with SSL support. This property does not need a value associated with it. The mere presence of it specifies an SSL connection. However, for compatibility with future versions, the value “true” is preferred.

Şöyle yaparız

jdbc:postgresql://postgres.example.com/test_db?user=bob&password=secret&ssl=true

SSL çeşitli modlarda çalışabilir. Modlar sslmode=XXX şeklinde belirtilir. Modlar şöyle

disable

allow : Non-validating

prefer : Non-validating

require : Non-validating

verify-ca : Validating

verify-full : Validating

verify-full Modu

Açıklaması şöyle. Yani varsayılan davranış  verify-full 

As of 42.2.5 ssl=true implies verify-full as per the release notes. If you wish to get the old behaviour use sslmode=require

Bu modda şöyle bir hata alabiliriz

org.postgresql.util.PSQLException: Could not open SSL root certificate file
  blahblah/.postgresql/root.crt.

veya

org.postgresql.util.PSQLException: Could not open SSL root certificate file 
  C:\Users\user\AppData\Roaming\postgresql\root.crt.

Örnek

Şöyle yaparız

jdbc:postgresql://myamazondomain.rds.amazonaws.com:5432/db_name?
sslmode=verify-full&
sslfactory=org.postgresql.ssl.SingleCertValidatingFactory&
sslfactoryarg=classpath:cert/rds-ca-cert_name.p12

Açıklaması şöyle

We need to use SingleCertValidatingFactory class to specify certificate file on classpath (or from file system, environment variables etc). This class has argument sslfactoryarg where we can add path to certificate file.

require modu

Açıklaması şöyle

require, allow and prefer all default to a non-validating SSL factory and do not check the validity of the certificate or the host name.

Failover and Load Balance

load Balancing Seçeneği

Açıklaması şöyle. Yani loadBalanceHosts=true yapmak lazım

In default mode (disabled) hosts are connected in the given order. If enabled, hosts are chosen randomly from the set of suitable candidates.

Örnek

Şöyle yaparız

jdbc:postgresql://host1:5432,host2:5432,host3:5432/mydb?loadBalanceHosts=true

Örnek - master

Şöyle yaparız

jdbc:postgresql://node1,node2,node3/accounting?targetServerType=master

Örnek - slave

Şöyle yaparız

jdbc:postgresql://node1,node2,node3/accounting?targetServerType=preferSlave&loadBalanceHosts=true