Upgrade postgresql from 9.x to 15.x on an old ADempiere 3.6.0LTS ?

[nilskm]

Hi!

I have an old installation of ADempiere 3.6.0LTS on a postgresql 9.x db. I'd like to upgrade it to a later version of postgresql since 9.x is not supported anymore. I've upgraded the db and that wasn't a problem. I've also replaced the jdbc jar with the latest one. But when I try to start everything up I get errors I don't understand. Do anyone know if there's something specific I need to fix in that old ADempiere installation to get it to work? I've tried to look through all commits in the DB-classes but I can't find anything significant.

This is the error I get:



[INFO] Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 2, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> AdempiereDS, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> org.postgresql.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hge15yb41swpbgz1jv4op2|27feae0f, idleConnectionTestPeriod -> 1200, initialPoolSize -> 1, jdbcUrl -> jdbc:postgresql://erp-testserver:5432/adempiere?encoding=UNICODE, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 900, maxIdleTimeExcessConnections -> 1200, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 1, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> SELECT Version FROM AD_System, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
[WARNING] com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@10ed7f5c -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (2). Last acquisition attempt exception: org.postgresql.util.PSQLException: This ResultSet is closed.
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkClosed(AbstractJdbc2ResultSet.java:2839)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.setFetchSize(AbstractJdbc2ResultSet.java:1874)
at org.postgresql.jdbc4.Jdbc4Statement.createResultSet(Jdbc4Statement.java:37)
at org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler.handleResultRows(AbstractJdbc2Statement.java:219)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1812)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
at org.postgresql.jdbc2.AbstractJdbc2Connection.execSQLUpdate(AbstractJdbc2Connection.java:366)
at org.postgresql.jdbc2.AbstractJdbc2Connection.getTransactionIsolation(AbstractJdbc2Connection.java:883)
at com.mchange.v2.c3p0.impl.NewPooledConnection.<init>(NewPooledConnection.java:107)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:198)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)

[Carlos Antonio Ruiz Gomez]

Hi Nils, is better to migrate that installation to iDempiere.

The adempiere project is too behind on technology (f.e. still running on java 8 I think) and on iDempiere security patches (f.e. still is very easy to get the SuperUser/System password).

The issue you're facing is probably that the old c3p0 maybe has problems with the new postgresql.jar

But also, moving to postgresql 15 will open other problems that we have solved on iDempiere because postgresql doesn't accept some statements that were accepted before 15.

See for example IDEMPIERE-5657 and IDEMPIERE-5678

Regards,

Carlos Ruiz

[nilskm]

Thank you so much for taking the time to answer Carlos. I understand that you're right and I will probably try in a few months. Still I'll have a look at  c3p0 and maybe settle for an upgrade to postgresql 12 or similar. Thanks again! 

Regards

Nils