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;
$DBConn.Open();
$DBCmd = $DBConn.CreateCommand();
$DBCmd.CommandText = "CREATE DATABASE " + $DatabaseToCreate + “;”;
$DBCmd.ExecuteNonQuery();
$DBConn.Close();
Which results in the following exception:
“Windows PowerShell is in NonInteractive mode. Read and Prompt functionality is not available.”