Using ODBC drivers to connect to Postgres database via Powershell

Question

I've routinely connected to a postgres database via ODBC using a powershell script. Like this....

$DBConnectionString = "Driver={PostgreSQL Unicode(x64)};Server=$MyServer;Port=$MyPort;Database=$MyDB;Uid=$MyUid;Pwd=$MyPass;Timeout=$timeout;CommandTimeout=$commandTimeout;"
$script:DBConn = New-Object System.Data.Odbc.OdbcConnection;
$DBConn.ConnectionString = $DBConnectionString;
$DBConn.Open();

I've just started getting the error....

Exception calling "Open" with "0" argument(s): "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

Since this script is running a new build, I'm guessing a configuration error. Server is windows 11, Postgre is a fresh installation of 14.2 downloaded from the official site The latest ODBC drivers came from the file psqlodbc_13_02_0000-x64.zip also downloaded from the offical site

I think the ODBC driver installation was successful because if I run ODBC 64 bit administrator, click on the 'drivers' tab I can see the 4 drivers I expect

1. PostgreSQL ANSI
2. PostgreSQL ANSI(x64)
3. PostgreSQL Unicode
4. PostgreSQL Unicode(x64)

I've tried editing the script to try each of the 4 drivers I think I have - but get the same error each time. I know the database is up and working, I can connect via pgAdmin and psql.

It's a new, clean installation of something that has worked on previous servers for several years. What have I forgotten or done wrong?

The script is being run from a desktop shortcut with this target (I think this demonstrates I'm running the 64 bit version)

"%SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe " -NoExit -command "&c:\pwrShl\TestToDb_015.ps1

I've tried setting up a DSN (just for test) and I the ODBC 'test' button says the connection worked. I've tried running the powershell script as administrator - makes no difference.

Answer 1

It should work:

$DBConnectionString = "Driver={PostgreSQL Unicode(x64)};Server=$MyServer;Port=$MyPort;Database=$MyDB;Uid=$MyUid;Pwd=$MyPass;Timeout=$timeout;CommandTimeout=$commandTimeout;"
$DBConn = [System.Data.Odbc.OdbcConnection]::new($DBConnectionString)
$DBConn.Open();