[Geoserver-users] Set database conecctiones polling

Hi list

I have approximately 160 layers stored in postgis. The layers was loaded automatically using the geoserver api rest. Recurrently postgres runs out of available connections. (FATAL: remaining connection slots are reserved for non-replication superuser connections) .
Reviewing the data stores, I found that none of the parameters are set. According to the geoserver documentation [1], it is indicated that they must be configured for each data store. The question is: is there any way to configure the parameters in bulk, using the api rest of geoserver? since doing it one by one is a tedious task.

Regards
Pablo

[1] https://docs.geoserver.org/stable/en/user/data/database/connection-pooling.html

···

Pablo J. Zader
Lic. en Cs. de la Computación + MSc. en Aplicaciones Espaciales de Alerta y Respuesta Temprana a Emergencias
pablo.zader@anonymised.com

Universidad Nacional de Córdoba
Av. Valpáraíso s/n Ciudad Universitaria

skypelinkedIn

"Los Grandes Hombres hablan sobre ideas… Los Hombres Promedio hablan sobre cosas… Los Hombres Pequeños hablan… de otros Hombres.

del libro Matemática estas ahí? A. Paenza "

I have found it convenient to configure the datastores as JNDI datasources that are defined in the Tomcat configuration. This enables using connection pooling enabled at the application server level.

It is also much easier to move data between environments as you can configure the datasources in each environment using the same names so the datastores work with no extra configuration.

In Tomcat the datasources can be configured in <TOMCAT_HOME>/conf/context.xml like this (for Oracle):

<Resource name=“jdbc/brm”

auth=“Container”

type=“javax.sql.DataSource”

url=“jdbc:oracle:thin:@:<port_no>/”

driverClassName=“oracle.jdbc.OracleDriver”

username=“”

password=“”

initialSize=“10”

maxTotal=“100”

maxWaitMillis=“20000”

validationQuery=“SELECT SYSDATE FROM DUAL” />

Cheers,

Graham H.

image001.jpg

···

From: Pablo J. Zader [mailto:pzader@…84…]
Sent: Wednesday, 4 September 2019 1:13 PM
To: geoserver-users geoserver-users@lists.sourceforge.net
Subject: [Geoserver-users] Set database conecctiones polling

Hi list

I have approximately 160 layers stored in postgis. The layers was loaded automatically using the geoserver api rest. Recurrently postgres runs out of available connections. (FATAL: remaining connection slots are reserved for non-replication superuser connections) .

Reviewing the data stores, I found that none of the parameters are set. According to the geoserver documentation [1], it is indicated that they must be configured for each data store. The question is: is there any way to configure the parameters in bulk, using the api rest of geoserver? since doing it one by one is a tedious task.

Regards

Pablo

[1] https://docs.geoserver.org/stable/en/user/data/database/connection-pooling.html

Pablo J. Zader
Lic. en Cs. de la Computación + MSc. en Aplicaciones Espaciales de Alerta y Respuesta Temprana a Emergencias
pablo.zader@…84…

Universidad Nacional de Córdoba
Av. Valpáraíso s/n Ciudad Universitaria

Image removed by sender. skypeImage removed by sender. linkedIn

"Los Grandes Hombres hablan sobre ideas… Los Hombres Promedio hablan sobre cosas… Los Hombres Pequeños hablan… de otros Hombres.

del libro Matemática estas ahí? A. Paenza "


CONFIDENTIALITY NOTICE AND DISCLAIMER
The information in this transmission may be confidential and/or protected by legal professional privilege, and is intended only for the person or persons to whom it is addressed. If you are not such a person, you are warned that any disclosure, copying or dissemination of the information is unauthorised. If you have received the transmission in error, please immediately contact this office by telephone, fax or email, to inform us of the error and to enable arrangements to be made for the destruction of the transmission, or its return at our cost. No liability is accepted for any unauthorised use of the information contained in this transmission.

Are they all connecting to the same database? If so you may consider setting up the database connection pool once in tomcat, and then setting up each datastore to use this connection pool (by using a JNDI reference to the connection pool in tomcat).

···


Jody Garnett

Hi

Thank for you help
It worked to postgres database

Geoserver 2.15
Tomcat 9
Ubuntu 18.04 LST
Driver: https://jdbc.postgresql.org/download/postgresql-42.2.6.jar
cp postgresql-42.2.6.jar tomcat/lib

Regards
Pablo

image001.jpg

···

Pablo J. Zader
Lic. en Cs. de la Computación + MSc. en Aplicaciones Espaciales de Alerta y Respuesta Temprana a Emergencias
pablo.zader@anonymised.com

Universidad Nacional de Córdoba
Av. Valpáraíso s/n Ciudad Universitaria

skypelinkedIn

"Los Grandes Hombres hablan sobre ideas… Los Hombres Promedio hablan sobre cosas… Los Hombres Pequeños hablan… de otros Hombres.

del libro Matemática estas ahí? A. Paenza "