3

I need to create a new schema in Postgres when the spring boot loads. So, it should check if the schema doesn't exist then create a new schema. I am using application.properties for database configuration. 

spring.datasource.platform=postgres
spring.datasource.url=jdbc:postgresql://${vcap.services.postgresql-lite.credentials.hostname}:${vcap.services.postgresql-lite.credentials.port}/${vcap.services.postgresql-lite.credentials.dbname}
spring.datasource.username=${vcap.services.postgresql-lite.credentials.username}
spring.datasource.password=${vcap.services.postgresql-lite.credentials.password}
spring.jpa.properties.hibernate.default_schema=${DATABASE_SCHEMA}
spring.jpa.hibernate.ddl-auto=update
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect

The default schema that Postgres uses is public, I need to change that make my own schema, which I'll define in env.

nirvair
  • 4,001
  • 10
  • 51
  • 85

2 Answers2

2

Use the following your application.properties file:

spring.datasource.initialize=true
spring.datasource.schema=${DB_SCHEMA}

In your schema file add the following:

CREATE TABLE IF NOT EXISTS...
mukut bhattacharjee
  • 191
  • 10
1

You can have a schema-postgresql.sql file placed in src/main/resources with content: as follows:

CREATE SCHEMA IF NOT EXISTS ;

abj1305
  • 635
  • 9
  • 21
  • Do I need to add anything in application.properties? – nirvair Nov 02 '18 at 09:32
  • You have all the things covered in your application.properties it seems. You can refer this [link](https://github.com/AbhijeetSakhalkar/SprintBoot/blob/master/src/main/resources/application.properties) as I have tested it and is working. Make sure your postgres version is later to 9.1 – abj1305 Nov 02 '18 at 10:03
  • I get an error saying: Relation schema_name doesn't exist. – nirvair Nov 02 '18 at 10:46
  • Can you paste your stacktrace ?? – abj1305 Nov 02 '18 at 10:47
  • I guess your postgres version is older – abj1305 Nov 02 '18 at 11:05
  • It’s 9.3. And I’ll attach the stacktrace in question in few minutes – nirvair Nov 02 '18 at 11:06
  • It’s the same. I also added the sql file. – nirvair Nov 02 '18 at 11:17
  • I figured out why I got the error. While the application boots, I had command line runner added in profile which adds some dummy data to database. I switched that off, the application runs fine, however there is no schema created. The only schema created is public. – nirvair Nov 02 '18 at 11:49
  • You need some clean up in the application.properties file. Please refer to this [link](https://stackoverflow.com/questions/49438517/why-spring-boot-2-0-application-does-not-run-schema-sql). I think this should solve your problem. – abj1305 Nov 12 '18 at 08:59
  • @nirvair Were you able to find solution ? facing same issue need to create schema automatically but not working for me. – Harish Bagora Sep 02 '20 at 06:00