Spring, application.properties for creation of ElephantSQL tables

Question

I try to do deploying (I use pivotal.io).
Before deploying I try to create tables of my DB. On pivotal.io I create the test database (ElephantSQL). This new DB have:
Shared high performance cluster
20 MB data
4 concurrent connections

I use Spring and this describe my DB in application properties. This works if I create DB on my localhost.`

spring.datasource.url=jdbc:postgresql://stampy.db.elephantsql.com:5432/iyraxwqa
spring.datasource.username=iyraxwqa
spring.datasource.password=*************************
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=create

spring.jpa.database=POSTGRESQL
spring.datasource.platform=postgres
spring.jpa.show-sql=false`

When I Run my application I see this ERROR:

2017-05-14 12:53:38.810 ERROR 4880 --- [           main] o.a.tomcat.jdbc.pool.ConnectionPool      : Unable to create initial connections of pool.

org.postgresql.util.PSQLException: FATAL: too many connections for role "iyraxwqa"
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455) ~[postgresql-9.4.1212.jre7.jar:9.4.1212.jre7]
    at org.postgresql.core.v3.QueryExecutorImpl.readStartupMessages(QueryExecutorImpl.java:2586) ~[postgresql-9.4.1212.jre7.jar:9.4.1212.jre7]
    at org.postgresql.core.v3.QueryExecutorImpl.<init>(QueryExecutorImpl.java:113) ~[postgresql-9.4.1212.jre7.jar:9.4.1212.jre7]
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:222) ~[postgresql-9.4.1212.jre7.jar:9.4.1212.jre7]
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:51) ~[postgresql-9.4.1212.jre7.jar:9.4.1212.jre7]
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:215) ~[postgresql-9.4.1212.jre7.jar:9.4.1212.jre7]
    at org.postgresql.Driver.makeConnection(Driver.java:404) ~[postgresql-9.4.1212.jre7.jar:9.4.1212.jre7]
    at org.postgresql.Driver.connect(Driver.java:272) ~[postgresql-9.4.1212.jre7.jar:9.4.1212.jre7]
    at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:310) ~[tomcat-jdbc-8.5.6.jar:na]
    at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:203) ~[tomcat-jdbc-8.5.6.jar:na]
    at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:718) [tomcat-jdbc-8.5.6.jar:na]

I include hibernate h3p0 and add this code:

spring.jpa.properties.hibernate.c3p0.min_size = 1
spring.jpa.properties.hibernate.c3p0.max_size = 2
spring.jpa.properties.hibernate.c3p0.timeout = 300

But I see the same error.
If I try to create manually all is working, but I have a lot of tables and half year ago I created tables with spring and hibernate
One of my tables:

@Entity
@Table(name = "INTERIOR", schema = "public")
public class InteriorModel extends AllFinishProductModel {

@Column(name = "PHOTO")
private String photo;
@Column(name = "PHOTO01")
private String photo01;
@Column(name = "PHOTO02")
private String photo02;
@Id
@Column(name = "ID")
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Long id;
@Column
private String name;
@Column(name = "DESCRIPTION")
private String description;
@Column(name = "COLOR")
private String color;
@Column(name = "QUANTITY")
private Double quantity;
@Column(name = "PRICE")
private BigDecimal price;
// getters and setters....

Somebody know, where my mistake?

Check out [this](http://stackoverflow.com/questions/30778015/how-to-increase-the-max-connections-in-postgres) answer. — Abdullah Khan, May 14 '17 at 11:40
As you can see, I include `h3p0` and have min connections =1, max = 2 — Viking, May 14 '17 at 12:04
what about the server's maximum conneciton? Check that out. Check if your `Postgresql` server allows more than one connection. — Abdullah Khan, May 14 '17 at 12:57
You are allowed 4 concurrent connections, the default poolsize is 10... Your hibernate c3p0 configuration won't do anything so remove those. Also when deploying to CF there is automatic reconfiguration so you shouldn't need to configure anything but your default (local) datasource. Pivotal CF should take care of the rest. If that isn't the case the max connection limit to 4. — M. Deinum, May 16 '17 at 18:44

score 2 · Answer 1 · answered May 16 '17 at 18:35

I have the same problem, I think you are using the Free plan(Tiny Turtle). I think the problem is the max number of connection that PosgreSql(elephantsql server) support, to know the max limit connection you can execute the follow sql script in your ElephantSql browser:

select * from pg_roles where rolname='iyraxwqa'

it display your role configuration in postgresql and you can see the column 'rolconnlimit' to know the max number of connection supported

score 0 · Accepted Answer · answered May 16 '17 at 19:21

0

I don't understand why, but when I delete c3p0 and started to use tomcat everything works. This code is working:

spring.datasource.tomcat.max-wait=1000
spring.datasource.tomcat.max-active=3
spring.datasource.tomcat.test-on-borrow=true

answered May 16 '17 at 19:21

Viking

177
5
16

Spring, application.properties for creation of ElephantSQL tables

2 Answers2