What is the best way to include a step to make sure my PostgreSQL database is created

As a part of our deployment process we would like to include a step in which we check whether our PostgreSQL database was created and if not create and add some permissions to it for a certain user.
I have found an example for SQL Server but I’m having a hard time to translate that to PostgreSQL and can’t seem to find any PostgreSQL specific examples.

This is what I have so far (which is not working):
Install-Module -Name PostgreSQLCmdlets

$DatabaseToCreate = $OctopusParameters[“Database.Name”]
$DbHost = $OctopusParameters[“Database.Host”]
$DbName = “postgres”
$DbUsernameMigrations = $OctopusParameters[“Database.UsernameMigrations”]
$DbPasswordMigrations = $OctopusParameters[“Database.PasswordMigrations”]
$DbPort = $OctopusParameters[“Database.Port”]

$DBConnectionString = “Driver={PostgreSQL UNICODE(x64)};Server=$DbHost;Port=$DbPort;Database=$DbName;Uid=$DbUsernameMigrations;Pwd=$DbPasswordMigrations;”
$DBConn = New-Object System.Data.Odbc.OdbcConnection;
$DBConn.ConnectionString = $DBConnectionString;
$DBCmd = $DBConn.CreateCommand();
$DBCmd.CommandText = "CREATE DATABASE " + $DatabaseToCreate + “;”;

Which results in the following exception:
“Windows PowerShell is in NonInteractive mode. Read and Prompt functionality is not available.”

Hello @haastnooit , and welcome to the forum!

We actually have a community step template that covers this scenario.

You can take a look at the parameters and backing code for Postgres - Create Database If Not Exists at the library site here

You can also see an example usage in our Samples instance - we have a whole host of PostgreSQL sample deployments here, and each one of the projects utilizes that step template. You should be able to log in as a guest and you can see how the project is structured.

Hopefully that helps, have a look and let me know if you run into any other questions or issues!