swap columns PostGIS

swap-columns.sh

#!/usr/bin/env bash
set -eu

# LOCALHOST - source PGPASSWORD from .env file
PG_HOST="localhost"
PG_DB="postgres"
PG_USER="postgres"
PG_PORT="5432"

# step 1 copy schema
SCHEMAS="wijkenbuurten2017_v42720 wijkenbuurten2018_v42721 wijkenbuurten2019_v42723"
LAYERS="cbs_buurten_ cbs_wijken_ gemeenten"

for schema in $SCHEMAS;do
    echo "INFO: copying schema ${schema} to ${schema}_bak"
    rm -f "${schema}.sql" 
    psql -h "$PG_HOST" -U "$PG_USER" -d "$PG_DB" -p "$PG_PORT" -c "DROP SCHEMA IF EXISTS ${schema}_bak CASCADE;"
    pg_dump -d postgres -h localhost -U postgres -n "$schema" > "${schema}.sql" 
    sed -i "s/${schema}/${schema}_bak/g" "${schema}.sql" 
    sed -i '/idle_in_transaction_session_timeout/d' "${schema}.sql" 
    psql -h "$PG_HOST" -U "$PG_USER" -d "$PG_DB" -p "$PG_PORT" -f "${schema}.sql" > /dev/null
    rm -f "${schema}.sql" 
done

# step 2 verify equality bak schema
for schema in $SCHEMAS;do
    echo "INFO: schema: $schema"
    [[ $schema =~ ^.*([0-9]{4})_v.*$ ]] && year="${BASH_REMATCH[1]}"
    for base_layer in $LAYERS; do
        layer="${base_layer}${year}"
        original_hash=$(psql -h "$PG_HOST" -U "$PG_USER" -d "$PG_DB" -p "$PG_PORT" -t -c "SELECT md5(array_agg(md5((t.*)::varchar))::varchar) FROM (SELECT * FROM ${schema}.${layer} ORDER BY gid) AS t;")
        bak_hash=$(psql -h "$PG_HOST" -U "$PG_USER" -d "$PG_DB" -p "$PG_PORT" -t -c "SELECT md5(array_agg(md5((t.*)::varchar))::varchar) FROM (SELECT * FROM ${schema}_bak.${layer} ORDER BY gid) AS t;")
        if [[ $original_hash != $bak_hash ]];then
            echo "ERROR: layer ${schema}.${layer} not equal to ${schema}_bak.${layer}"
            exit 1
        else
            echo "INFO: layer ${schema}.${layer} equal to ${schema}_bak.${layer}"
        fi
    done
done

# step 3 update view to point to bak schema
for schema in $SCHEMAS;do
    [[ $schema =~ ^.*([0-9]{4})_v.*$ ]] && year=${BASH_REMATCH[1]}
    [[ $schema =~ ^(.*[0-9]{4})_v.*$ ]] && view_schema=${BASH_REMATCH[1]}
    echo "INFO: updating view ${view_schema} to point to ${schema}_bak"
    for base_layer in $LAYERS; do
        layer="${base_layer}${year}"
        psql -h "$PG_HOST" -U "$PG_USER" -d "$PG_DB" -p "$PG_PORT" -t -c  "CREATE OR REPLACE VIEW ${view_schema}.${layer} AS SELECT * FROM ${schema}_bak.${layer}" >/dev/null
    done
done


# step 4 swicht columns in original schema
function switch_columns(){
    local schema_name
    local layer_name
    schema_name="$1"
    layer_name="$2"
    echo "INFO: switch columns for layer "${schema_name}"."${layer_name}""
    psql -h "$PG_HOST" -U "$PG_USER" -d "$PG_DB" -p "$PG_PORT" -t -c "
ALTER TABLE $schema_name.$layer_name RENAME percentage_personen_65_jaar_en_ouder TO percentage_personen_65_jaar_en_ouder_tmp;
ALTER TABLE $schema_name.$layer_name RENAME percentage_personen_45_tot_65_jaar TO percentage_personen_45_tot_65_jaar_tmp;
ALTER TABLE $schema_name.$layer_name RENAME percentage_personen_65_jaar_en_ouder_tmp TO percentage_personen_45_tot_65_jaar;
ALTER TABLE $schema_name.$layer_name RENAME percentage_personen_45_tot_65_jaar_tmp TO percentage_personen_65_jaar_en_ouder;
" >/dev/null
}


for schema in $SCHEMAS;do
    [[ $schema =~ ^.*([0-9]{4})_v.*$ ]] && year=${BASH_REMATCH[1]}
    for base_layer in $LAYERS; do
        layer="${base_layer}${year}"
        switch_columns "${schema}" "${layer}"
    done
done

# step 5 update view to point to original schema
for schema in $SCHEMAS;do
    [[ $schema =~ ^.*([0-9]{4})_v.*$ ]] && year=${BASH_REMATCH[1]}
    [[ $schema =~ ^(.*[0-9]{4})_v.*$ ]] && view_schema=${BASH_REMATCH[1]}
    echo "INFO: updating view ${view_schema} to point to ${schema}"
    for base_layer in $LAYERS; do
        layer="${base_layer}${year}"
        # drop view fist, see: https://dba.stackexchange.com/a/589
        psql -h "$PG_HOST" -U "$PG_USER" -d "$PG_DB" -p "$PG_PORT" -t -c  "DROP VIEW IF EXISTS ${view_schema}.${layer}" >/dev/null
        psql -h "$PG_HOST" -U "$PG_USER" -d "$PG_DB" -p "$PG_PORT" -t -c  "CREATE VIEW ${view_schema}.${layer} AS SELECT * FROM ${schema}.${layer}" >/dev/null
    done
done