Problems configuring a JNDI JDBC pool to Postgres

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Problems configuring a JNDI JDBC pool to Postgres

Hrafn Malmquist
Good day fellow Apache Tomcat users

I have recently had reason to set a Postgres DataSource via the Tomcat JNDI
api. My experiments have been largely unsuccessful, unfortunately I hope
you are able to help me. I thank you in advance for taking the time to read
this.

I am coming to this problem in a rather backward way. I am not familiar
with deploying web applications through Tomcat, this is something that was
done in a semi-automated way and replicated so that a close look has not
really been necessary for me until now.

To provide a bit of context, the web server where I am trying to set up a
Postgres JNDI globally shared connection is running running several DSpace
webapps.

I have been largely assisted by the official documentation here [0] and
here [1] as well as DSpace documentation here [2] and discussion on a user
mailing list for DSpace here [3].

I am running:

CentOS Linux release 7.8.2003
Tomcat 9.0.12
PostgreSQL 9.5.23
openjdk version "1.8.0_262"
OpenJDK Runtime Environment (build 1.8.0_262-b10)


   1.  I downloaded postgresql-42.2.18.jar from
   https://jdbc.postgresql.org/download/postgresql-42.2.18.jar and placed
   it in /home/lib/dspace/apache-tomcat-9.0.12/lib
   2. In /home/lib/dspace/apache-tomcat-9.0.12/conf/server.xml I added the
   following Resource

   <Resource name="jdbc/dspaceWeb"
                 factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
                 type="javax.sql.DataSource"
                 auth="Container"
                 username="*****"
                 password="******"
                 driverClassName="org.postgresql.Driver"
                 url="jdbc:postgresql://localhost:5432/dspace"
                 initialSize="5"
                 maxTotal="50"
                 maxIdle="15"
                 minIdle="5" />

   There are a couple of issues that confuse me:
   a) the official documentation about JNDI datasource examples [0] says "*This
   author has not had success here, although others have reported so.
   Clarification would be appreciated here."  *in the section about
   Postgres. In other words, we can't be sure this works?
   b) in the official documtation about JDBC connection pool it says that
   if you want to define a JDBC connection pool in the Apache Container (which
   I understand to be trying to do) you have to specify the factory attribute
   and set the value to org.apache.tomcat.jdbc.pool.DataSourceFactory [1]

   This is not done in the example (that the author hasn't had success
   with?) in a)
   3. Created a context.xml in /dspace/webapps/xmlui/META-INF/

   <Context docBase="/dspace/webapps/xmlui">
       <ResourceLink name="jdbc/dspace"
                     global="jdbc/dspaceWeb"
                     type="javax.sql.DataSource"
       />
   </Context>


When I start up Tomcat howere. I get this in the log:

INFO [org.springframework.jndi.JndiObjectFactoryBean] JNDI lookup failed -
returning specified default object instead:
javax.naming.NameNotFoundException: Name [jdbc/dspace] is not bound in this
Context. Unable to find [jdbc].

My understanding is that the webapp, DSpace, tried to look for a JNDI
object in the namespace "jdbc/dspace" and barring that, falls back to
instantiating it's own DataSource as a bean. See [4]

But I don't understand why the above doesn't work.

Thanks for your time

Sources:
[0] :
https://tomcat.apache.org/tomcat-9.0-doc/jndi-datasource-examples-howto.html#PostgreSQL
[1] :
https://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html#Inside_the_Apache_Tomcat_Container
[2] :
https://wiki.lyrasis.org/display/DSDOC6x/Installing+DSpace#InstallingDSpace-Externaldatabaseconnectionpool
[3] :
http://dspace.2283337.n4.nabble.com/Notes-on-PostgreSQL-connection-pooling-with-a-Tomcat-JNDI-resource-td4687149.html
[4]:
https://github.com/DSpace/DSpace/blob/dspace-6_x/dspace/config/spring/api/core-hibernate.xml
Reply | Threaded
Open this post in threaded view
|

Re: Problems configuring a JNDI JDBC pool to Postgres

Rob Sargent
Looks like you named your resource jdbc/dspaceWeb and dropped the Web in your lookup

> On Nov 26, 2020, at 12:58 PM, Hrafn Malmquist <[hidden email]> wrote:
>
> ´╗┐Good day fellow Apache Tomcat users
>
> I have recently had reason to set a Postgres DataSource via the Tomcat JNDI
> api. My experiments have been largely unsuccessful, unfortunately I hope
> you are able to help me. I thank you in advance for taking the time to read
> this.
>
> I am coming to this problem in a rather backward way. I am not familiar
> with deploying web applications through Tomcat, this is something that was
> done in a semi-automated way and replicated so that a close look has not
> really been necessary for me until now.
>
> To provide a bit of context, the web server where I am trying to set up a
> Postgres JNDI globally shared connection is running running several DSpace
> webapps.
>
> I have been largely assisted by the official documentation here [0] and
> here [1] as well as DSpace documentation here [2] and discussion on a user
> mailing list for DSpace here [3].
>
> I am running:
>
> CentOS Linux release 7.8.2003
> Tomcat 9.0.12
> PostgreSQL 9.5.23
> openjdk version "1.8.0_262"
> OpenJDK Runtime Environment (build 1.8.0_262-b10)
>
>
>   1.  I downloaded postgresql-42.2.18.jar from
>   https://jdbc.postgresql.org/download/postgresql-42.2.18.jar and placed
>   it in /home/lib/dspace/apache-tomcat-9.0.12/lib
>   2. In /home/lib/dspace/apache-tomcat-9.0.12/conf/server.xml I added the
>   following Resource
>
>   <Resource name="jdbc/dspaceWeb"
>                 factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
>                 type="javax.sql.DataSource"
>                 auth="Container"
>                 username="*****"
>                 password="******"
>                 driverClassName="org.postgresql.Driver"
>                 url="jdbc:postgresql://localhost:5432/dspace"
>                 initialSize="5"
>                 maxTotal="50"
>                 maxIdle="15"
>                 minIdle="5" />
>
>   There are a couple of issues that confuse me:
>   a) the official documentation about JNDI datasource examples [0] says "*This
>   author has not had success here, although others have reported so.
>   Clarification would be appreciated here."  *in the section about
>   Postgres. In other words, we can't be sure this works?
>   b) in the official documtation about JDBC connection pool it says that
>   if you want to define a JDBC connection pool in the Apache Container (which
>   I understand to be trying to do) you have to specify the factory attribute
>   and set the value to org.apache.tomcat.jdbc.pool.DataSourceFactory [1]
>
>   This is not done in the example (that the author hasn't had success
>   with?) in a)
>   3. Created a context.xml in /dspace/webapps/xmlui/META-INF/
>
>   <Context docBase="/dspace/webapps/xmlui">
>       <ResourceLink name="jdbc/dspace"
>                     global="jdbc/dspaceWeb"
>                     type="javax.sql.DataSource"
>       />
>   </Context>
>
>
> When I start up Tomcat howere. I get this in the log:
>
> INFO [org.springframework.jndi.JndiObjectFactoryBean] JNDI lookup failed -
> returning specified default object instead:
> javax.naming.NameNotFoundException: Name [jdbc/dspace] is not bound in this
> Context. Unable to find [jdbc].
>
> My understanding is that the webapp, DSpace, tried to look for a JNDI
> object in the namespace "jdbc/dspace" and barring that, falls back to
> instantiating it's own DataSource as a bean. See [4]
>
> But I don't understand why the above doesn't work.
>
> Thanks for your time
>
> Sources:
> [0] :
> https://tomcat.apache.org/tomcat-9.0-doc/jndi-datasource-examples-howto.html#PostgreSQL
> [1] :
> https://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html#Inside_the_Apache_Tomcat_Container
> [2] :
> https://wiki.lyrasis.org/display/DSDOC6x/Installing+DSpace#InstallingDSpace-Externaldatabaseconnectionpool
> [3] :
> http://dspace.2283337.n4.nabble.com/Notes-on-PostgreSQL-connection-pooling-with-a-Tomcat-JNDI-resource-td4687149.html
> [4]:
> https://github.com/DSpace/DSpace/blob/dspace-6_x/dspace/config/spring/api/core-hibernate.xml

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problems configuring a JNDI JDBC pool to Postgres

markt
On 26/11/2020 21:01, Rob Sargent wrote:
> Looks like you named your resource jdbc/dspaceWeb and dropped the Web in your lookup

Nope. You are confusing the global and local name. The config looks
right on first glance.

Mark

>
>> On Nov 26, 2020, at 12:58 PM, Hrafn Malmquist <[hidden email]> wrote:
>>
>> ´╗┐Good day fellow Apache Tomcat users
>>
>> I have recently had reason to set a Postgres DataSource via the Tomcat JNDI
>> api. My experiments have been largely unsuccessful, unfortunately I hope
>> you are able to help me. I thank you in advance for taking the time to read
>> this.
>>
>> I am coming to this problem in a rather backward way. I am not familiar
>> with deploying web applications through Tomcat, this is something that was
>> done in a semi-automated way and replicated so that a close look has not
>> really been necessary for me until now.
>>
>> To provide a bit of context, the web server where I am trying to set up a
>> Postgres JNDI globally shared connection is running running several DSpace
>> webapps.
>>
>> I have been largely assisted by the official documentation here [0] and
>> here [1] as well as DSpace documentation here [2] and discussion on a user
>> mailing list for DSpace here [3].
>>
>> I am running:
>>
>> CentOS Linux release 7.8.2003
>> Tomcat 9.0.12
>> PostgreSQL 9.5.23
>> openjdk version "1.8.0_262"
>> OpenJDK Runtime Environment (build 1.8.0_262-b10)
>>
>>
>>   1.  I downloaded postgresql-42.2.18.jar from
>>   https://jdbc.postgresql.org/download/postgresql-42.2.18.jar and placed
>>   it in /home/lib/dspace/apache-tomcat-9.0.12/lib
>>   2. In /home/lib/dspace/apache-tomcat-9.0.12/conf/server.xml I added the
>>   following Resource
>>
>>   <Resource name="jdbc/dspaceWeb"
>>                 factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
>>                 type="javax.sql.DataSource"
>>                 auth="Container"
>>                 username="*****"
>>                 password="******"
>>                 driverClassName="org.postgresql.Driver"
>>                 url="jdbc:postgresql://localhost:5432/dspace"
>>                 initialSize="5"
>>                 maxTotal="50"
>>                 maxIdle="15"
>>                 minIdle="5" />
>>
>>   There are a couple of issues that confuse me:
>>   a) the official documentation about JNDI datasource examples [0] says "*This
>>   author has not had success here, although others have reported so.
>>   Clarification would be appreciated here."  *in the section about
>>   Postgres. In other words, we can't be sure this works?
>>   b) in the official documtation about JDBC connection pool it says that
>>   if you want to define a JDBC connection pool in the Apache Container (which
>>   I understand to be trying to do) you have to specify the factory attribute
>>   and set the value to org.apache.tomcat.jdbc.pool.DataSourceFactory [1]
>>
>>   This is not done in the example (that the author hasn't had success
>>   with?) in a)
>>   3. Created a context.xml in /dspace/webapps/xmlui/META-INF/
>>
>>   <Context docBase="/dspace/webapps/xmlui">
>>       <ResourceLink name="jdbc/dspace"
>>                     global="jdbc/dspaceWeb"
>>                     type="javax.sql.DataSource"
>>       />
>>   </Context>
>>
>>
>> When I start up Tomcat howere. I get this in the log:
>>
>> INFO [org.springframework.jndi.JndiObjectFactoryBean] JNDI lookup failed -
>> returning specified default object instead:
>> javax.naming.NameNotFoundException: Name [jdbc/dspace] is not bound in this
>> Context. Unable to find [jdbc].
>>
>> My understanding is that the webapp, DSpace, tried to look for a JNDI
>> object in the namespace "jdbc/dspace" and barring that, falls back to
>> instantiating it's own DataSource as a bean. See [4]
>>
>> But I don't understand why the above doesn't work.
>>
>> Thanks for your time
>>
>> Sources:
>> [0] :
>> https://tomcat.apache.org/tomcat-9.0-doc/jndi-datasource-examples-howto.html#PostgreSQL
>> [1] :
>> https://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html#Inside_the_Apache_Tomcat_Container
>> [2] :
>> https://wiki.lyrasis.org/display/DSDOC6x/Installing+DSpace#InstallingDSpace-Externaldatabaseconnectionpool
>> [3] :
>> http://dspace.2283337.n4.nabble.com/Notes-on-PostgreSQL-connection-pooling-with-a-Tomcat-JNDI-resource-td4687149.html
>> [4]:
>> https://github.com/DSpace/DSpace/blob/dspace-6_x/dspace/config/spring/api/core-hibernate.xml
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problems configuring a JNDI JDBC pool to Postgres

markt
In reply to this post by Hrafn Malmquist
On 26/11/2020 19:57, Hrafn Malmquist wrote:

<snip/>

> I am running:
>
> CentOS Linux release 7.8.2003
> Tomcat 9.0.12
> PostgreSQL 9.5.23
> openjdk version "1.8.0_262"
> OpenJDK Runtime Environment (build 1.8.0_262-b10)

There are good reasons to update to a more recent Tomcat 9 release but
I'm not aware of any fixes that would impact the specific issue you are
currently seeing.

>    1.  I downloaded postgresql-42.2.18.jar from
>    https://jdbc.postgresql.org/download/postgresql-42.2.18.jar and placed
>    it in /home/lib/dspace/apache-tomcat-9.0.12/lib

OK. That looks good.

>    2. In /home/lib/dspace/apache-tomcat-9.0.12/conf/server.xml I added the
>    following Resource
>
>    <Resource name="jdbc/dspaceWeb"
>                  factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
>                  type="javax.sql.DataSource"
>                  auth="Container"
>                  username="*****"
>                  password="******"
>                  driverClassName="org.postgresql.Driver"
>                  url="jdbc:postgresql://localhost:5432/dspace"
>                  initialSize="5"
>                  maxTotal="50"
>                  maxIdle="15"
>                  minIdle="5" />

Exactly where in server.xml did you put this? What element was it nested
under?

>    There are a couple of issues that confuse me:
>    a) the official documentation about JNDI datasource examples [0] says "*This
>    author has not had success here, although others have reported so.
>    Clarification would be appreciated here."  *in the section about
>    Postgres. In other words, we can't be sure this works?

That text needs to be removed. The author clearly made an error in their
config. This definitely works.

>    b) in the official documtation about JDBC connection pool it says that
>    if you want to define a JDBC connection pool in the Apache Container (which
>    I understand to be trying to do) you have to specify the factory attribute
>    and set the value to org.apache.tomcat.jdbc.pool.DataSourceFactory [1]

Do you want to use Tomcat's jdbc-pool? If so leave the factory attribute
as is. If you want to use Tomcat's built in copy for Apache Commons
DBCP2, remove the factory attribute.

Personally, I'd go with DBCP2 but either should work.

>    3. Created a context.xml in /dspace/webapps/xmlui/META-INF/

I think this is the problem. That location looks to be outside of the
default appBase. If I'm right, there is already a Context element
defined for this web application somewhere else. You need to find that
Context definition and add the ResourceLink to it.

>
>    <Context docBase="/dspace/webapps/xmlui">>        <ResourceLink name="jdbc/dspace"
>                      global="jdbc/dspaceWeb"
>                      type="javax.sql.DataSource"
>        />
>    </Context>

Mark

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problems configuring a JNDI JDBC pool to Postgres

Hrafn Malmquist
Hello Rob and Mark and others

Thanks for your input.

 > >    2. In /home/lib/dspace/apache-tomcat-9.0.12/conf/server.xml I added
the
 > >    following Resource
 > >
 > >    <Resource name="jdbc/dspaceWeb"
 > >
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
 > >                  type="javax.sql.DataSource"
 > >                  auth="Container"
 > >                  username="*****"
 > >                  password="******"
 > >                  driverClassName="org.postgresql.Driver"
 > >                  url="jdbc:postgresql://localhost:5432/dspace"
 > >                  initialSize="5"
 > >                  maxTotal="50"
 > >                  maxIdle="15"
 > >                  minIdle="5" />
 >
 > Exactly where in server.xml did you put this? What element was it nested
 > under?

I should have made clear, it's nested under <GlobalNamingResources> which
is in turn nested under <Server>.

I have removed the factory attribute and it now reads:

<GlobalNamingResources>
    <!-- Editable user database that can also be used by
         UserDatabaseRealm to authenticate users
    -->
    <Resource name="UserDatabase" auth="Container"
              type="org.apache.catalina.UserDatabase"
              description="User database that can be updated and saved"
              factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
              pathname="conf/tomcat-users.xml" />

    <Resource name="jdbc/dspaceWeb"
              type="javax.sql.DataSource"
              auth="Container"
              username="*******"
              password="*******"
              driverClassName="org.postgresql.Driver"
              url="jdbc:postgresql://localhost:5432/dspace"
              initialSize="5"
              maxTotal="50"
              maxIdle="15"
              minIdle="5" />
  </GlobalNamingResources>

> >    3. Created a context.xml in /dspace/webapps/xmlui/META-INF/
>
> I think this is the problem. That location looks to be outside of the
> default appBase. If I'm right, there is already a Context element
> defined for this web application somewhere else. You need to find that
> Context definition and add the ResourceLink to it.

Right. It is indeed possible that the underlying configuration is not set
up correctly.

This is the relevant extract from server.xml:

<Host name="localhost"  appBase="webapps"
            unpackWARs="true" autoDeploy="true">

        <Valve className="org.apache.catalina.valves.AccessLogValve"
directory="logs"
               prefix="localhost_access_log" suffix=".txt"
               pattern="%h %l %u %t &quot;%r&quot; %s %b" />

        <Context path="/solr" docBase="/dspace/webapps/solr"/>
      </Host>

<Host name="********" appBase="/dspace/webapps"
            unpackWARs="true" autoDeploy="true">


        <Valve className="org.apache.catalina.valves.AccessLogValve"
directory="logs"
               prefix="localhost_access_log" suffix=".txt"
               pattern="%h %l %u %t &quot;%r&quot; %s %b" />

        <Context path="" docBase="/dspace/webapps/xmlui"/>
        <Context path="/rest"  docBase="/dspace/webapps/rest" />
        <Context path="/oai"  docBase="/dspace/webapps/oai" />
</Host>

Host appBase for the domain earlier said "webapps", like it still does for
localhost, but I changed to "dspace/webapps".

No change when I restart Tomcat.

Under /home/lib/dspace/apache-tomcat-9.0.12/conf/Catalina there are folders
for the hosts that have been set up on the server but none of them contain
files.

I am not sure where to look elsewhere for this Context element.

Any ideas?
Hrafn



On Thu, Nov 26, 2020 at 9:30 PM Mark Thomas <[hidden email]> wrote:

> On 26/11/2020 19:57, Hrafn Malmquist wrote:
>
> <snip/>
>
> > I am running:
> >
> > CentOS Linux release 7.8.2003
> > Tomcat 9.0.12
> > PostgreSQL 9.5.23
> > openjdk version "1.8.0_262"
> > OpenJDK Runtime Environment (build 1.8.0_262-b10)
>
> There are good reasons to update to a more recent Tomcat 9 release but
> I'm not aware of any fixes that would impact the specific issue you are
> currently seeing.
>
> >    1.  I downloaded postgresql-42.2.18.jar from
> >    https://jdbc.postgresql.org/download/postgresql-42.2.18.jar and
> placed
> >    it in /home/lib/dspace/apache-tomcat-9.0.12/lib
>
> OK. That looks good.
>
> >    2. In /home/lib/dspace/apache-tomcat-9.0.12/conf/server.xml I added
> the
> >    following Resource
> >
> >    <Resource name="jdbc/dspaceWeb"
> >                  factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
> >                  type="javax.sql.DataSource"
> >                  auth="Container"
> >                  username="*****"
> >                  password="******"
> >                  driverClassName="org.postgresql.Driver"
> >                  url="jdbc:postgresql://localhost:5432/dspace"
> >                  initialSize="5"
> >                  maxTotal="50"
> >                  maxIdle="15"
> >                  minIdle="5" />
>
> Exactly where in server.xml did you put this? What element was it nested
> under?
>
> >    There are a couple of issues that confuse me:
> >    a) the official documentation about JNDI datasource examples [0] says
> "*This
> >    author has not had success here, although others have reported so.
> >    Clarification would be appreciated here."  *in the section about
> >    Postgres. In other words, we can't be sure this works?
>
> That text needs to be removed. The author clearly made an error in their
> config. This definitely works.
>
> >    b) in the official documtation about JDBC connection pool it says that
> >    if you want to define a JDBC connection pool in the Apache Container
> (which
> >    I understand to be trying to do) you have to specify the factory
> attribute
> >    and set the value to org.apache.tomcat.jdbc.pool.DataSourceFactory [1]
>
> Do you want to use Tomcat's jdbc-pool? If so leave the factory attribute
> as is. If you want to use Tomcat's built in copy for Apache Commons
> DBCP2, remove the factory attribute.
>
> Personally, I'd go with DBCP2 but either should work.
>
> >    3. Created a context.xml in /dspace/webapps/xmlui/META-INF/
>
> I think this is the problem. That location looks to be outside of the
> default appBase. If I'm right, there is already a Context element
> defined for this web application somewhere else. You need to find that
> Context definition and add the ResourceLink to it.
>
> >
> >    <Context docBase="/dspace/webapps/xmlui">>        <ResourceLink
> name="jdbc/dspace"
> >                      global="jdbc/dspaceWeb"
> >                      type="javax.sql.DataSource"
> >        />
> >    </Context>
>
> Mark
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Problems configuring a JNDI JDBC pool to Postgres

Hrafn Malmquist
Ahhhh

How silly of me. It's right there staring me in the face nested inside the
host in server.xml, right?

So, best practice is to remove contexts from host and place either under
[engine]/[host] or directly in META-INF in app?

On Thu, Nov 26, 2020 at 10:43 PM Hrafn Malmquist <[hidden email]>
wrote:

> Hello Rob and Mark and others
>
> Thanks for your input.
>
>  > >    2. In /home/lib/dspace/apache-tomcat-9.0.12/conf/server.xml I
> added the
>  > >    following Resource
>  > >
>  > >    <Resource name="jdbc/dspaceWeb"
>  > >
> factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
>  > >                  type="javax.sql.DataSource"
>  > >                  auth="Container"
>  > >                  username="*****"
>  > >                  password="******"
>  > >                  driverClassName="org.postgresql.Driver"
>  > >                  url="jdbc:postgresql://localhost:5432/dspace"
>  > >                  initialSize="5"
>  > >                  maxTotal="50"
>  > >                  maxIdle="15"
>  > >                  minIdle="5" />
>  >
>  > Exactly where in server.xml did you put this? What element was it nested
>  > under?
>
> I should have made clear, it's nested under <GlobalNamingResources> which
> is in turn nested under <Server>.
>
> I have removed the factory attribute and it now reads:
>
> <GlobalNamingResources>
>     <!-- Editable user database that can also be used by
>          UserDatabaseRealm to authenticate users
>     -->
>     <Resource name="UserDatabase" auth="Container"
>               type="org.apache.catalina.UserDatabase"
>               description="User database that can be updated and saved"
>               factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
>               pathname="conf/tomcat-users.xml" />
>
>     <Resource name="jdbc/dspaceWeb"
>               type="javax.sql.DataSource"
>               auth="Container"
>               username="*******"
>               password="*******"
>               driverClassName="org.postgresql.Driver"
>               url="jdbc:postgresql://localhost:5432/dspace"
>               initialSize="5"
>               maxTotal="50"
>               maxIdle="15"
>               minIdle="5" />
>   </GlobalNamingResources>
>
> > >    3. Created a context.xml in /dspace/webapps/xmlui/META-INF/
> >
> > I think this is the problem. That location looks to be outside of the
> > default appBase. If I'm right, there is already a Context element
> > defined for this web application somewhere else. You need to find that
> > Context definition and add the ResourceLink to it.
>
> Right. It is indeed possible that the underlying configuration is not set
> up correctly.
>
> This is the relevant extract from server.xml:
>
> <Host name="localhost"  appBase="webapps"
>             unpackWARs="true" autoDeploy="true">
>
>         <Valve className="org.apache.catalina.valves.AccessLogValve"
> directory="logs"
>                prefix="localhost_access_log" suffix=".txt"
>                pattern="%h %l %u %t &quot;%r&quot; %s %b" />
>
>         <Context path="/solr" docBase="/dspace/webapps/solr"/>
>       </Host>
>
> <Host name="********" appBase="/dspace/webapps"
>             unpackWARs="true" autoDeploy="true">
>
>
>         <Valve className="org.apache.catalina.valves.AccessLogValve"
> directory="logs"
>                prefix="localhost_access_log" suffix=".txt"
>                pattern="%h %l %u %t &quot;%r&quot; %s %b" />
>
>         <Context path="" docBase="/dspace/webapps/xmlui"/>
>         <Context path="/rest"  docBase="/dspace/webapps/rest" />
>         <Context path="/oai"  docBase="/dspace/webapps/oai" />
> </Host>
>
> Host appBase for the domain earlier said "webapps", like it still does for
> localhost, but I changed to "dspace/webapps".
>
> No change when I restart Tomcat.
>
> Under /home/lib/dspace/apache-tomcat-9.0.12/conf/Catalina there are
> folders for the hosts that have been set up on the server but none of them
> contain files.
>
> I am not sure where to look elsewhere for this Context element.
>
> Any ideas?
> Hrafn
>
>
>
> On Thu, Nov 26, 2020 at 9:30 PM Mark Thomas <[hidden email]> wrote:
>
>> On 26/11/2020 19:57, Hrafn Malmquist wrote:
>>
>> <snip/>
>>
>> > I am running:
>> >
>> > CentOS Linux release 7.8.2003
>> > Tomcat 9.0.12
>> > PostgreSQL 9.5.23
>> > openjdk version "1.8.0_262"
>> > OpenJDK Runtime Environment (build 1.8.0_262-b10)
>>
>> There are good reasons to update to a more recent Tomcat 9 release but
>> I'm not aware of any fixes that would impact the specific issue you are
>> currently seeing.
>>
>> >    1.  I downloaded postgresql-42.2.18.jar from
>> >    https://jdbc.postgresql.org/download/postgresql-42.2.18.jar and
>> placed
>> >    it in /home/lib/dspace/apache-tomcat-9.0.12/lib
>>
>> OK. That looks good.
>>
>> >    2. In /home/lib/dspace/apache-tomcat-9.0.12/conf/server.xml I added
>> the
>> >    following Resource
>> >
>> >    <Resource name="jdbc/dspaceWeb"
>> >                  factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
>> >                  type="javax.sql.DataSource"
>> >                  auth="Container"
>> >                  username="*****"
>> >                  password="******"
>> >                  driverClassName="org.postgresql.Driver"
>> >                  url="jdbc:postgresql://localhost:5432/dspace"
>> >                  initialSize="5"
>> >                  maxTotal="50"
>> >                  maxIdle="15"
>> >                  minIdle="5" />
>>
>> Exactly where in server.xml did you put this? What element was it nested
>> under?
>>
>> >    There are a couple of issues that confuse me:
>> >    a) the official documentation about JNDI datasource examples [0]
>> says "*This
>> >    author has not had success here, although others have reported so.
>> >    Clarification would be appreciated here."  *in the section about
>> >    Postgres. In other words, we can't be sure this works?
>>
>> That text needs to be removed. The author clearly made an error in their
>> config. This definitely works.
>>
>> >    b) in the official documtation about JDBC connection pool it says
>> that
>> >    if you want to define a JDBC connection pool in the Apache Container
>> (which
>> >    I understand to be trying to do) you have to specify the factory
>> attribute
>> >    and set the value to org.apache.tomcat.jdbc.pool.DataSourceFactory
>> [1]
>>
>> Do you want to use Tomcat's jdbc-pool? If so leave the factory attribute
>> as is. If you want to use Tomcat's built in copy for Apache Commons
>> DBCP2, remove the factory attribute.
>>
>> Personally, I'd go with DBCP2 but either should work.
>>
>> >    3. Created a context.xml in /dspace/webapps/xmlui/META-INF/
>>
>> I think this is the problem. That location looks to be outside of the
>> default appBase. If I'm right, there is already a Context element
>> defined for this web application somewhere else. You need to find that
>> Context definition and add the ResourceLink to it.
>>
>> >
>> >    <Context docBase="/dspace/webapps/xmlui">>        <ResourceLink
>> name="jdbc/dspace"
>> >                      global="jdbc/dspaceWeb"
>> >                      type="javax.sql.DataSource"
>> >        />
>> >    </Context>
>>
>> Mark
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>>
Reply | Threaded
Open this post in threaded view
|

Re: Problems configuring a JNDI JDBC pool to Postgres

markt
On 26/11/2020 22:52, Hrafn Malmquist wrote:
> Ahhhh
>
> How silly of me. It's right there staring me in the face nested inside the
> host in server.xml, right?

Right.

The changes to GlobalResources look good.

You'll probably want to revert the change you made to appBase.

> So, best practice is to remove contexts from host and place either under
> [engine]/[host] or directly in META-INF in app?

Generally, yes although I'd recommend changing one thing at a time so
get the DataSource working before you start moving config files around.

You'll want something like:

<Context path="" docBase="/dspace/webapps/xmlui">
    <ResourceLink name="jdbc/dspace"
                  global="jdbc/dspaceWeb"
                  type="javax.sql.DataSource"
                  />
</Context>

in server.xml

Mark


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problems configuring a JNDI JDBC pool to Postgres

Hrafn Malmquist
Great. Thanks.

Glad it was a simple fix.

By the way, why would you choose DBCP2 over Tomcat? Intuitively, I would
assume a pool designed specifically for Tomcat would be more efficient than
a more generic Apache library?

Hrafn


On Fri, Nov 27, 2020 at 8:24 AM Mark Thomas <[hidden email]> wrote:

> On 26/11/2020 22:52, Hrafn Malmquist wrote:
> > Ahhhh
> >
> > How silly of me. It's right there staring me in the face nested inside
> the
> > host in server.xml, right?
>
> Right.
>
> The changes to GlobalResources look good.
>
> You'll probably want to revert the change you made to appBase.
>
> > So, best practice is to remove contexts from host and place either under
> > [engine]/[host] or directly in META-INF in app?
>
> Generally, yes although I'd recommend changing one thing at a time so
> get the DataSource working before you start moving config files around.
>
> You'll want something like:
>
> <Context path="" docBase="/dspace/webapps/xmlui">
>     <ResourceLink name="jdbc/dspace"
>                   global="jdbc/dspaceWeb"
>                   type="javax.sql.DataSource"
>                   />
> </Context>
>
> in server.xml
>
> Mark
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Problems configuring a JNDI JDBC pool to Postgres

markt
On 27/11/2020 19:56, Hrafn Malmquist wrote:
> Great. Thanks.
>
> Glad it was a simple fix.
>
> By the way, why would you choose DBCP2 over Tomcat? Intuitively, I would
> assume a pool designed specifically for Tomcat would be more efficient than
> a more generic Apache library?

There is very little between them in terms of performance, especially if
you configure them for equivalent behaviour.

I'd summarise the differences as:
- DBCP2 follows the JDBC specs a little more closely by default
- jdbc-pool has marginally better performance under high load
- jdbc-pool has better JMX monitoring
- issues tend to get fixed sooner in DBCP2

I tend to value spec compliance so that gives DBCP2 the edge for me.

Mark

>
> Hrafn
>
>
> On Fri, Nov 27, 2020 at 8:24 AM Mark Thomas <[hidden email]> wrote:
>
>> On 26/11/2020 22:52, Hrafn Malmquist wrote:
>>> Ahhhh
>>>
>>> How silly of me. It's right there staring me in the face nested inside
>> the
>>> host in server.xml, right?
>>
>> Right.
>>
>> The changes to GlobalResources look good.
>>
>> You'll probably want to revert the change you made to appBase.
>>
>>> So, best practice is to remove contexts from host and place either under
>>> [engine]/[host] or directly in META-INF in app?
>>
>> Generally, yes although I'd recommend changing one thing at a time so
>> get the DataSource working before you start moving config files around.
>>
>> You'll want something like:
>>
>> <Context path="" docBase="/dspace/webapps/xmlui">
>>     <ResourceLink name="jdbc/dspace"
>>                   global="jdbc/dspaceWeb"
>>                   type="javax.sql.DataSource"
>>                   />
>> </Context>
>>
>> in server.xml
>>
>> Mark
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]