How can I use Octopus deploy to setup a PSQL database?

I’ve been trying and failing to setup users and run some scripts in an automated fashion against a postgres instance. I’ve done ARM templating from Azure to setup the instance, and Azure CLI in the same step to setup the databases, but now I need to run .sql files to setup users, passwords, and run some schema generation against a few of the DBs.

I’m struggling big time. Azure CLI doesn’t do these sorts of things, and whenever I try to use psql insert-commands-and-connection-info, it fails out, claiming that “The term ‘psql.exe’ is not recognized as the name of a cmdlet, function, script file, or operable program.” (despite psql.exe being accessible from the system PATH, and me being able to run psql commands in Powershell on the actual Octopus server host machine).

My google-fu fails me too, as Google just redirects me to information about setting up the actual instance in Azure, or just running PSQL commands from an interactive CLI. Is what I’m attempting to do at all possible? I can’t be the first to try this!

Thanks in advance,
Jeff

Hi @jeffrey.mitchell !

Sorry to hear that you’re having issues with your Octopus deployments. From the looks of that error, it sounds like perhaps psql.exe’s path was added to the PATH environmental variable after the Octopus server (or Tentacle if this is running on a worker/target) service started.

Since the env vars are snapshotted at service start, and then the deployment processes start as child processes of the main process, they would not know about the updated values. A quick service restart will force an update of the variables, including the PATH

Hopefully this gets you up and running! Please let us know if you have any further questions.

Hey Justin,

Appreciate that fix, I had rebooted the host VM after installing PGAdmin and adding it to the PATH, but once I restarted the actual OctopusDeploy service, it picked up. Still struggling a little with the syntax for psql, but I’m further than before!

My struggle now is attempting to do the following:

psql.exe "host=IP dbname=postgres user=user@instance password=******" just hangs on the Script Console, which is expected, since this command in Powershell opens an interactive shell. However, when I try adding flags for commands or files, the output includes psql: warning: extra command-line argument "SELECT version():" ignored, and opens up the postgres shell again. How can I get the command to just run and output what I need, rather than pushing into the postgres shell?

Hi @jeffrey.mitchell,

I’m glad that got you a little further! In general, things that run interactive sessions won’t work through Octopus, as the deployments themselves are non-interactive. The usual method of deploying database changes/commands is to use deployment tools such as DBUp, Flyway, RoundhousE, or Liquibase to directly run pre-specified scripts. We have some samples of workflows setup on our samples instance that show how database deployments are normally handled in deployment automation. You can find the Postgres space here: Octopus Deploy

If you require any further info - please don’t hesitate to let us know what your intended deployment should do, and we can help you to model that out.

Hey Justin,

I’ve made a bit of progress! I stumbled on a nice Stack Overflow question/answer that helped immensely: https://stackoverflow.com/a/31945033/9423996

By adding the $env variable for PGPASSWORD, and adding --% to the beginning of my command, I can get Octopus to output PSQL commands now, with an example:

$env:PGPASSWORD = "thePassword"
psql --% -h x.x.x.x -U admin@instance -d postgres -c "SELECT CURRENT_DATE;"

outputs the current date in the Script Console! image

The last remaining bit will be to see if it will run .sql files, but this is a fun puzzle to solve. Thanks for the help so far.

1 Like