Menu & Search
Creating a Linked Server with a Postgres Database

Creating a Linked Server with a Postgres Database


In my previous post, I created a Link Server to another SQL Server instance This time, it’s a link to a Postgres database!

This guide can be followed from start to finish using the links below.

# Install SQL Server (separate post).
# Install PostgreSQL (separate post).
# Test Connectivity to Remote Server (previous Linked Server post).
# Create Linked Server Login on Postgres.
# Install the Postgres ODBC Driver.
# Configure ODBC Data Source Settings.
# Create Linked Server to a Postgres Database.


Create Linked Server Login on Postgres

As always, this kind of task can be done using a management tool like pgAdmin 4 or via command (psql in this case).

Postgres Create New User

Making this one a Superuser for quickness.

Postgres User Options

And for the post, here’s the above being created using psql.

psql Create and View Users

Install Postgres ODBC Driver

The ODBC driver must be installed on both ends – This is a local set-up though, so I only need to install this once. You can use Stack Builder to install it, or download the package on its own (link here) which I’m doing below.

Postgres ODBC Driver Download

Click famous Next button.

psqlODBC Setup Wizard

Accept License stuff.

psqlODBC License Agreement

Specify directory & we don’t need the documentation.

psqlODBC Feature Selection

And then install!

psqlODBC Installation
psqlODBC Install Wizard

Configure ODBC Data Source Settings

We need to point the ODBC driver on the SQL Server to the Postgres database.

Run ODBC Data Sources as Administrator.

Run ODBC Source as Administrator

Click into the System DSN tab and click Add.

ODBC Data Source System DSN

Select the PostgreSQL Unicode driver.

Create New Postgres ODBC Data Source

Enter all details required as shown. I created a ‘sales’ database for this test which I’m connecting to. 

ODBC Data Source Settings for Postgres

Always worth it to test.

ODBC Test Connection

Create Linked Server to a Postgres Database

(Within SQL Server Management Studio’s Object Explorer)

Expand Server Objects, right-click Linked Servers and select New Linked Server.

SSMS New Linked Server

The Linked server name at the top of the next window is what you’ll need to include in queries, so choose that wisely.

Then choose a provider for ODBC, enter any semi-relevant Product name, and then enter the Data Source name which you entered within the ODBC driver settings.

SSMS New Linked Server Postgres

Enter the Postgres login details as created above within this post.

SSMS Linked Server Login Details

I’m ignoring the Server Options tab for now and rolling with the defaults.

When ready, take a breath, then hit OK!

Nothing will display if it’s successful, so have a go at testing the connection.

SSMS Test Linked Server Connection
Linked Server Connection Test

We’re now linked with the Sales database that’s in Postgres!

Linked Server Query with Postgres