My application runs a Liquibase script targeting a remote database to run existing shp2pgsql and psql commands. But Liquibase doesn’t find them there

by

Summary

The issue arises when running a Liquibase script in a Spring Boot application that targets a remote PostGIS database. The script attempts to execute shp2pgsql and psql commands, but Liquibase fails to find them, resulting in a crash. This occurs because the commands are installed on the PostGIS database pod, but Liquibase is searching for them on the Spring Boot application pod.

Root Cause

The root cause of this issue is that Liquibase is executing the commands on the Spring Boot application pod, rather than the PostGIS database pod where the commands are installed. This is because the executable attribute in the executeCommand tag is set to /bin/bash, which is executed on the local pod.

Why This Happens in Real Systems

This issue occurs in real systems because:

  • Liquibase scripts are often designed to run on the same system as the database, where the required commands are installed.
  • When deploying applications to cloud environments, such as Kubernetes, the application and database may be running on separate pods, leading to issues with command execution.
  • The executable attribute in Liquibase scripts is often set to a default value, such as /bin/bash, without considering the target environment.

Real-World Impact

The real-world impact of this issue includes:

  • Application crashes: The Spring Boot application crashes due to the failure to execute the shp2pgsql and psql commands.
  • Data import failures: The failure to execute the commands prevents the import of shapefile data into the PostGIS table.
  • Deployment issues: The issue can prevent successful deployment of the application to cloud environments, such as Kubernetes.

Example or Code

To correct this issue, the Liquibase script can be modified to execute the commands on the PostGIS database pod. One possible solution is to use a Kubernetes InitContainer to copy the required commands to the Spring Boot application pod.

kubectl exec -it postgres-6ff89665d8-2hr9j -- bin/bash
cp /usr/lib/postgresql/17/bin/psql /tmp/
cp /usr/bin/shp2pgsql /tmp/

How Senior Engineers Fix It

Senior engineers can fix this issue by:

  • Modifying the Liquibase script: Update the executable attribute to point to the correct location of the shp2pgsql and psql commands.
  • Using a Kubernetes InitContainer: Copy the required commands to the Spring Boot application pod using an InitContainer.
  • Configuring the application: Configure the Spring Boot application to execute the commands on the PostGIS database pod.

Why Juniors Miss It

Junior engineers may miss this issue because:

  • Lack of experience: Limited experience with Liquibase, PostGIS, and Kubernetes can make it difficult to identify the root cause of the issue.
  • Insufficient testing: Failure to test the application in a cloud environment, such as Kubernetes, can prevent the issue from being discovered.
  • Incomplete understanding: Incomplete understanding of how Liquibase scripts are executed and how commands are resolved in a cloud environment can lead to mistakes.