Spring Batch / Postgres : ERROR: relation "batch_job_instance" does not exist

Question

I am trying to configure Spring Batch to use PostGres DB. I have included the following dependencies in my build.gradle.kts file:

implementation("org.springframework.boot:spring-boot-starter-data-jpa")
implementation("org.postgresql:postgresql")

My application.yml for my SpringBatch module has the following included:

spring:
  datasource:
    url: jdbc:postgresql://postgres:5432/springbatchdb
    username: postgres
    password: root
    driverClassName: org.postgresql.Driver

docker-compose.yml

postgres:
    restart: always
    image: postgres:12-alpine
    container_name: postgres
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=root
      - POSTGRES_DB=springbatchdb
    ports:
     - "5432:5432"
    volumes:
      - postgresql:/var/lib/postgresql
      - postgresql_data:/var/lib/postgresql/data

However, when I try to add a data file I see the following error in the logs of both my SpringBatch Docker container, and the PostGres container:

Spring Batch:

<<< Exception in method: org.meanwhileinhell.spring.batch.server.SpringBatchController.handle Error Message: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ?]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "batch_job_instance" does not exist

PostGres:

LOG:  database system is ready to accept connections
2021-01-08 09:54:56.778 UTC [56] ERROR:  relation "batch_job_instance" does not exist at character 39
2021-01-08 09:54:56.778 UTC [56] STATEMENT:  SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = $1 and JOB_KEY = $2
2021-01-08 09:55:27.033 UTC [56] ERROR:  relation "batch_job_instance" does not exist at character 39
2021-01-08 09:55:27.033 UTC [56] STATEMENT:  SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = $1 and JOB_KEY = $2

I can see that the SB server is picking up POSTGRES from my metadata ok.

JobRepositoryFactoryBean     : No database type set, using meta data indicating: POSTGRES

What am I missing to get the initial db configured during the server start?

Edit: I've tried adding spring.datasource.initialize=true explicitly, but no change.

score 17 · Accepted Answer · edited Jan 11 '21 at 10:47

17

Please check below added in application.yml

spring.batch.initialize-schema: always

Please check below dependencies are added

<artifactId>spring-boot-starter-batch</artifactId>

edited Jan 11 '21 at 10:47

MeanwhileInHell

6,780
17
57
106

answered Jan 08 '21 at 16:11

Rakesh

658
6
15

4

In the newer versions the config is now under jdbc - `spring.batch.jdbc.initialize-schema=always` https://docs.spring.io/spring-boot/docs/2.7.x/reference/htmlsingle/#howto.data-initialization.batch – Sašo5 Jun 28 '22 at 07:52
3

After I upgraded my Spring boot to 3 the initialization doesn't work anymore. It doesn't matter if it sets to always. – Mehdi Jan 01 '23 at 08:19
1

@Mehdi Have you figured why yet ? – anhvutnu Feb 08 '23 at 13:03

Zahid Khan · Answer 2 · 2023-02-17T06:32:01.383

Solution that worked for me in Spring 5.0!

I spent a lot of time resolving issues like ERROR: relation "X" does not exist when using the latest Spring Boot Starter 3.0 and Spring Batch 5.0.

spring.batch.jdbc.initialize-schema=always

However, it didn't create the necessary tables for me. Though as per the documentation, it should have created tables.

After a lot of research, I found, that in the latest Spring Batch 5.0, there are a lot of improvements. And I was doing a lot of things wrong when migrating to the new Spring 5.

Remove @EnableBatchProcessing from your configurations. As you don't need that anymore with the latest Spring Batch 5.

Example:

@Configuration
@AllArgsConstructor
@EnableBatchProcessing   //please remove it.
public class SpringBatchConfiguration {}

change it to:

@Configuration
@AllArgsConstructor
public class SpringBatchConfiguration {}

PlatformTransaction Manager: The second thing I was doing wrong was using an incorrect Transaction Manager, if you are using JPA for persisting entities you need a corresponding Transaction Manager.

I was using the ResourcelessTransactionManager() which was wrong in my case and was creating a lot of headaches while running.

For JPA you need a JpaTransactionManager()

Something like:
```
@Bean
public PlatformTransactionManager transactionManager() {
    return new JpaTransactionManager();
}
```
The third thing I learned after mistakes; we don't need to create a bean of Datasource unless we are doing something complex like having 2 Datasource one for writing Spring Batch associated tables and another for persisting our business data.

Wherever we are required to use JobRepository just inject it. Something like:

@Bean
@Autowired
Job job(JobRepository jobRepository) {
    JobBuilder jobBuilderFactory = new JobBuilder("somename", jobRepository );
    return jobBuilderFactory.flow(step1(jobRepository)).end()
            .build();
}

For more details on migrations: Spring Migration 3 Guide.

score 4 · Answer 3 · answered Nov 18 '21 at 18:22

yaml file is

spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=1234
spring.datasource.driver-class-name=org.postgresql.Driver
spring.batch.jdbc.initialize-schema=always

gradle dependencies

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-jdbc'
    implementation 'org.springframework.boot:spring-boot-starter-batch'
    implementation 'org.projectlombok:lombok-maven-plugin:1.18.6.0'
    implementation group: 'org.postgresql', name: 'postgresql', version: '42.3.1'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
    testImplementation 'org.springframework.batch:spring-batch-test'
}

Mahmoud Ben Hassine · Answer 4 · 2021-01-11T12:58:46.187

3

You need to set spring.batch.initialize-schema=always property to tell Spring Boot to create Spring Batch tables automatically. Please refer to the Initialize a Spring Batch Database section of Spring Boot's reference documentation for more details.

edited Jan 11 '21 at 12:58

answered Jan 11 '21 at 10:06

Mahmoud Ben Hassine

28,519
3
32
50

3

This was very helpful. If anyone is still confused, add `spring.batch.initialize-schema=always` to your application.properties file in you SpringBoot application. – need_to_know_now Feb 25 '21 at 08:58

score 3 · Answer 5 · answered May 18 '21 at 15:29

3

For anyone who has spring.batch.initialize-schema=always set already and it's still not working, also verify that you are connecting to the database with a user that has sufficient privileges, including to create the necessary tables.

answered May 18 '21 at 15:29

fenix.shadow

402
4
9

score 1 · Answer 6 · answered Feb 03 '23 at 08:09

1

in application.properties,

Prior Spring Boot 2.5 we can use

spring.batch.initialize-schema=ALWAYS

Later version of Spring Boot 2.5 use below

spring.batch.jdbc.initialize-schema=ALWAYS

answered Feb 03 '23 at 08:09

Praveen RL

588
9
13

score 0 · Answer 7 · answered May 05 '23 at 12:48

I used the spring.batch.jdbc.initialize-schema=always parameter. I also connected with a user that has the permission to create tables. Still, the metadata Spring Batch tables would not be created.

My solution was to create the tables manually. The DDL for doing that is available here. You need to pick the one for your specific DB server. I reproduce below the Oracle version (our case).

CREATE TABLE BATCH_JOB_INSTANCE  (
    JOB_INSTANCE_ID NUMBER(19,0)  NOT NULL PRIMARY KEY ,
    VERSION NUMBER(19,0) ,
    JOB_NAME VARCHAR2(100 char) NOT NULL,
    JOB_KEY VARCHAR2(32 char) NOT NULL,
    constraint JOB_INST_UN unique (JOB_NAME, JOB_KEY)
) SEGMENT CREATION IMMEDIATE;

CREATE TABLE BATCH_JOB_EXECUTION  (
    JOB_EXECUTION_ID NUMBER(19,0)  NOT NULL PRIMARY KEY ,
    VERSION NUMBER(19,0)  ,
    JOB_INSTANCE_ID NUMBER(19,0) NOT NULL,
    CREATE_TIME TIMESTAMP(9) NOT NULL,
    START_TIME TIMESTAMP(9) DEFAULT NULL ,
    END_TIME TIMESTAMP(9) DEFAULT NULL ,
    STATUS VARCHAR2(10 char) ,
    EXIT_CODE VARCHAR2(2500 char) ,
    EXIT_MESSAGE VARCHAR2(2500 char) ,
    LAST_UPDATED TIMESTAMP(9),
    constraint JOB_INST_EXEC_FK foreign key (JOB_INSTANCE_ID)
    references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
) SEGMENT CREATION IMMEDIATE;

CREATE TABLE BATCH_JOB_EXECUTION_PARAMS  (
    JOB_EXECUTION_ID NUMBER(19,0) NOT NULL ,
    PARAMETER_NAME VARCHAR(100 char) NOT NULL ,
    PARAMETER_TYPE VARCHAR(100 char) NOT NULL ,
    PARAMETER_VALUE VARCHAR(2500 char) ,
    IDENTIFYING CHAR(1) NOT NULL ,
    constraint JOB_EXEC_PARAMS_FK foreign key (JOB_EXECUTION_ID)
    references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) SEGMENT CREATION IMMEDIATE;

CREATE TABLE BATCH_STEP_EXECUTION  (
    STEP_EXECUTION_ID NUMBER(19,0)  NOT NULL PRIMARY KEY ,
    VERSION NUMBER(19,0) NOT NULL,
    STEP_NAME VARCHAR2(100 char) NOT NULL,
    JOB_EXECUTION_ID NUMBER(19,0) NOT NULL,
    CREATE_TIME TIMESTAMP(9) NOT NULL,
    START_TIME TIMESTAMP(9) DEFAULT NULL ,
    END_TIME TIMESTAMP(9) DEFAULT NULL ,
    STATUS VARCHAR2(10 char) ,
    COMMIT_COUNT NUMBER(19,0) ,
    READ_COUNT NUMBER(19,0) ,
    FILTER_COUNT NUMBER(19,0) ,
    WRITE_COUNT NUMBER(19,0) ,
    READ_SKIP_COUNT NUMBER(19,0) ,
    WRITE_SKIP_COUNT NUMBER(19,0) ,
    PROCESS_SKIP_COUNT NUMBER(19,0) ,
    ROLLBACK_COUNT NUMBER(19,0) ,
    EXIT_CODE VARCHAR2(2500 char) ,
    EXIT_MESSAGE VARCHAR2(2500 char) ,
    LAST_UPDATED TIMESTAMP(9),
    constraint JOB_EXEC_STEP_FK foreign key (JOB_EXECUTION_ID)
    references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) SEGMENT CREATION IMMEDIATE;

CREATE TABLE BATCH_STEP_EXECUTION_CONTEXT  (
    STEP_EXECUTION_ID NUMBER(19,0) NOT NULL PRIMARY KEY,
    SHORT_CONTEXT VARCHAR2(2500 char) NOT NULL,
    SERIALIZED_CONTEXT CLOB ,
    constraint STEP_EXEC_CTX_FK foreign key (STEP_EXECUTION_ID)
    references BATCH_STEP_EXECUTION(STEP_EXECUTION_ID)
) SEGMENT CREATION IMMEDIATE;

CREATE TABLE BATCH_JOB_EXECUTION_CONTEXT  (
    JOB_EXECUTION_ID NUMBER(19,0) NOT NULL PRIMARY KEY,
    SHORT_CONTEXT VARCHAR2(2500 char) NOT NULL,
    SERIALIZED_CONTEXT CLOB ,
    constraint JOB_EXEC_CTX_FK foreign key (JOB_EXECUTION_ID)
    references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) SEGMENT CREATION IMMEDIATE;

CREATE SEQUENCE BATCH_STEP_EXECUTION_SEQ START WITH 0 MINVALUE 0 MAXVALUE 9223372036854775807 ORDER NOCYCLE;
CREATE SEQUENCE BATCH_JOB_EXECUTION_SEQ START WITH 0 MINVALUE 0 MAXVALUE 9223372036854775807 ORDER NOCYCLE;
CREATE SEQUENCE BATCH_JOB_SEQ START WITH 0 MINVALUE 0 MAXVALUE 9223372036854775807 ORDER NOCYCLE;

Spring Batch / Postgres : ERROR: relation "batch_job_instance" does not exist

7 Answers7

Solution that worked for me in Spring 5.0!

Linked