Make Liquibase execute a shp2pgsql PostGIS command

Question

I have a Liquibase changeset targeting a PostGis database :

<!--Création de la table et vue sur les données du chômage dans les communes en 2016 -->
<changeSet id="chomage_2016" author="mlebihan">
    <comment>Table du chômage par communes en 2016.</comment>
        
    <sql>
         CREATE TABLE chomage_2016(
            codecommune VARCHAR(5) PRIMARY KEY, 
            nomcommune VARCHAR NOT NULL,
            sirencommune VARCHAR NOT NULL,
            populationtotale NUMERIC(10) NOT NULL,
            nombre NUMERIC(10) NOT NULL,
            effectifs NUMERIC(10) NOT NULL,
            pctsurpopactive NUMERIC(4,1) NOT NULL,
            pctsurpoptotale NUMERIC(4,1) NOT NULL
         ) 
         TABLESPACE data;
             
         COMMENT ON TABLE chomage_2016 IS 'Taux de chômage dans les communes en 2016'; 
         COMMENT ON COLUMN chomage_2016.codecommune IS 'Code de la commune';
         COMMENT ON COLUMN chomage_2016.nomcommune IS 'Nom de la commune';
         COMMENT ON COLUMN chomage_2016.sirencommune IS 'SIREN de la commune';
         COMMENT ON COLUMN chomage_2016.populationtotale IS 'Population totale de la commune (en 2019)';
         COMMENT ON COLUMN chomage_2016.nombre IS 'Nombre de chômeurs';
         COMMENT ON COLUMN chomage_2016.effectifs IS 'Population de plus de 15 ans en 2016';
         COMMENT ON COLUMN chomage_2016.pctsurpopactive IS 'Pourcentage sur la population de plus de 15 ans';
         COMMENT ON COLUMN chomage_2016.pctsurpoptotale IS 'Pourcentage sur la population totale de la commune';

        CREATE INDEX idx_chomage_2016_codeCommune ON chomage_2016(codecommune) TABLESPACE data;
        COMMENT ON INDEX idx_chomage_2016_codeCommune IS 'Taux de chômage dans les communes en 2016, par code commune.';
            
        CREATE VIEW view_chomage_2016 AS 
           SELECT row_number() OVER () AS gid, d.codeCommune, d.nomCommune, d.sirencommune, d.populationtotale, 
               d.nombre, d.effectifs, d.pctsurpopactive, d.pctsurpoptotale, c.geom FROM chomage_2016 d 
           JOIN communes_2019 c ON d.codeCommune = c.insee;
        
        COMMENT ON VIEW view_chomage_2016 IS 'Vue entre les taux de chômage de 2016 et communes (OSM) de 2019.';
    </sql>
        
    <rollback>
        <sql>
            DROP VIEW view_chomage_2016;
            DROP INDEX idx_chomage_2016_codeCommune;
            DROP TABLE chomage_2016;
        </sql>
    </rollback>   
</changeSet>

The view it creates depends on a commune_2019 table, that is created from an OpenStreetMap shapefile, by a PostGIS command currently executed from a bash shell :

shp2pgsql -s 4326 -I /data/comptes-france/territoire/2019/communes.shp public.communes_2019 | psql -d comptesfrance -U postgres -h localhost

I would like to integrate the execution of this command under my Liquibase script.
Is it possible ?

Answer 1

1. The execution of shp2pgsql command sends its SQL generated statement to psql who needs a password to connect to the database, and that password cannot be sent through its command line. I've set it with an export PGPASSWORD=....

2. The export command isn't a true executable but a built-in of bash.
   So, it's through a bash command that it has to be called. The bash command goes with a -c option, that starts a new session, and the shp2pgsql has to follow immediately after.

<!-- shp2pgsql -s 4326 -I /data/comptes-france/territoire/2016/communes.shp public.communes_2016 | psql -d comptesfrance -U postgres -h localhost -->  
<changeSet id="shp2pgsql_communes_2019" author="mlebihan" failOnError="false">
    <comment>Importation des shapefiles des contours des communes 2019</comment>

    <executeCommand executable="/bin/bash">
        <arg value="-c"/>
        <arg value="export PGPASSWORD=postgres;
             shp2pgsql 
                -s 4326 
                -I /data/comptes-france/territoire/2019/communes.shp 
                public.communes_2019 
           | psql -d comptesfrance -U postgres -h localhost"/>
    </executeCommand>
</changeSet>

Note : I wrote the second argument with line breaks for better readability, but of course it's not the case, and you have to write it this way :

<arg value="export PGPASSWORD=postgres;shp2pgsql -s 4326 -I /data/comptes-france/territoire/2019/communes.shp public.communes_2019 | psql -d comptesfrance -U postgres -h localhost"/>