PostgreSQL schema updates in CI

[ajolma]

I need to update PostgreSQL schema during CI. If the schema update fails, the CI fails, and the code that relies on the new schema is not installed. I'm trying a solution, which adds a ShellScriptAction (we use cdk v1) into the Code Pipeline that runs python code for the update. We have multiple accounts so the action is setup so that in the python code assumes a role that has the rights to do the db update.

All works well for one account (sbox, the repo and pipeline are in dev). But the python code fails when I'm running it on dev account:

Can't create a connection to host dev-lahde-aurora-cluster.cluster-c9gcepwmxuec.eu-west-1.rds.amazonaws.com and port 5432 (timeout is None and source_address is None).

I'm assuming this may have to do with the vpc/security group the shell script action virtual machine runs. However, trying to set those for the action leads to all kinds of cross stack / cross account problems and failures earlier in the pipeline (in the Update Pipeline step).

I must add that I'm using a cdk library that is built on top of vanilla aws-cdk that helps developing multi-account CI-solution when pipeline is triggered by commit, which I do not fully understand.

Also, I can create a lambda for db create/update which runs after a stack has been created or updated but that solution is IMO dangerous when it allows code that relies on new version of db schema to be installed when the schema update may fail.

Anyway, I guess this must be a rather common feature and there must be good solutions but I have not really found out those. Any advice or pointers?