PowerShell with ODBC to interact with PostgreSQL (Linux and Windows)

PostgreSQL is an open-source relational database management system. It is widely used for storing and managing large amounts of data, and its popularity has led to the development of several tools and interfaces for interacting with it. One such interface is ODBC (Open Database Connectivity), which is a standardized API for connecting to databases. While migrating from SQL Server to PostgreSQL, we come across some questions about how to use PowerShell with Postgres. In this blog post, we will discuss about PowerShell with ODBC to interact with PostgreSQL databases.

ODBC

Open Database Connectivity (ODBC) is a standard interface for accessing databases. It provides a uniform way of accessing data stored in a wide range of databases, including PostgreSQL. ODBC drivers act as an intermediary between the database and the application.

Installing the PostgreSQL ODBC driver is easy. On Ubuntu, you can install the PostgreSQL ODBC driver using the following commands:

sudo apt update
sudo apt install -y unixodbc unixodbc-dev odbc-postgresql

For Windows, you can download and install the PostgreSQL ODBC driver msi from the official PostgreSQL website here.

PowerShell

PowerShell is a cross-platform task automation and configuration management framework from Microsoft. It is designed for task automation and can be used to manage and automate various processes across different platforms. PowerShell provides a powerful scripting language and a robust set of tools to automate almost any task.

PowerShell has several benefits. It is platform-independent and can run on Windows, Linux, and macOS, making it a versatile tool for managing and automating processes across different platforms. Additionally, it is object-oriented, which means it is easy to manage complex data structures and automate complex processes. It also integrates with other Microsoft technologies, such as Active Directory and Exchange, as well as other open-source technologies.

To install PowerShell on Ubuntu, you can follow the instructions in the Microsoft documentation here. Alternatively, you can simply execute the following steps.

# Update the list of packages
sudo apt-get update

# Install pre-requisite packages.
sudo apt-get install -y wget apt-transport-https software-properties-common

# Download the Microsoft repository GPG keys
wget -q "https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/packages-microsoft-prod.deb"

# Register the Microsoft repository GPG keys
sudo dpkg -i packages-microsoft-prod.deb

# Delete the the Microsoft repository GPG keys file
rm packages-microsoft-prod.deb

# Update the list of packages after we added packages.microsoft.com
sudo apt-get update

# Install PowerShell
sudo apt-get install -y powershell

# Start PowerShell
pwsh

Typically, Windows comes pre-installed with PowerShell. However, you can download and install the latest version of PowerShell from the official Microsoft website by visiting this link.

PowerShell Script

To run Postgres queries on PowerShell using ODBC, you need to first define the server name, port number, database name, user name, and password for the PostgreSQL database connection. You can then create a connection string using the ODBC driver for PostgreSQL.

Here's an example of a PowerShell file (postgres_odbc.ps1) that executes a SQL query to copy data from the "employee" table to a CSV file (/tmp/dump_table_employee.txt):

$MyServer = "localhost"
$MyPort = "5432"
$MyDB = "postgres"
$MyUid = "postgres"
$MyPass = "postgres"

$DBConnectionString = "DRIVER={PostgreSQL Unicode};Server=$MyServer;Port=$MyPort;Database=$MyDB;Uid=$MyUid;Pwd=$MyPass;"
$DBConn = New-Object System.Data.Odbc.OdbcConnection;
$DBConn.ConnectionString = $DBConnectionString;
$DBConn.Open();
$DBCmd = $DBConn.CreateCommand();
$DBCmd.CommandText = "copy employee to '/tmp/dump_table_employee.txt' csv"
$result=$DBCmd.ExecuteReader();
echo $result

$DBConn.Close();

For Windows, change the filepath /tmp/dump_table_employee.txt accordingly.

Run the postgres_odbc.ps1 file

In Ubuntu,

$ pwsh postgres_odbc.ps1

In Windows, (may require Admin Privileges)

PowerShell.exe -File postgres_odbc.ps1 -ExecutionPolicy Bypass

Now, You should find the CSV exported.

Code Breakdown

The code snippet above does the following:

• Defines the server name, port number, database name, user name, and password for the PostgreSQL database connection.
• Creates a connection string using the ODBC driver for PostgreSQL.
• Opens a connection to the PostgreSQL database using the ODBC driver.
• Defines a SQL query to copy data from the "employee" table to a CSV file.
• Executes the SQL query using the ODBC driver and stores the result.
• Closes the database connection.

Conclusion

By learning how to use PowerShell with PostgreSQL through ODBC, you can easily manage and automate your PostgreSQL database processes. PowerShell provides a platform-independent way to interact with the database and automate complex processes, making it a valuable tool for organizations that rely on PostgreSQL for their data management needs. Whether you're a database administrator or a developer, the combination of PowerShell and PostgreSQL ODBC driver can streamline your workflow and help you manage your data more efficiently. Contact MigOps today, if you are looking to migrate to PostgreSQL. You may fill the following form or send an email to sales@migops.com for inquiries.