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).
Making this one a Superuser for quickness.
And for the post, here’s the above being created using psql.
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.
Click famous Next button.
Accept License stuff.
Specify directory & we don’t need the documentation.
And then install!
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.
Click into the System DSN tab and click Add.
Select the PostgreSQL Unicode driver.
Enter all details required as shown. I created a ‘sales’ database for this test which I’m connecting to.
Always worth it to test.
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.
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.
Enter the Postgres login details as created above within this post.
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.
We’re now linked with the Sales database that’s in Postgres!
Thank-you for this post Peter, its been more than 5 years (2015) since I last put together a linked server to Postgres and this is for access to the Tableau Server db so was concerned about updating the driver.
For any other user, TS will install the drivers, but not an environment. Given the driver specification for TS, it is fine to skip the driver install, starting your journey from: Configure ODBC Data Source Settings.
That’s interesting to hear Steve, thank you.
I’ve done a bunch with Tableau Desktop & Tableau Online but not yet had the pleasure of meeting Tableau Server. I had a wee look at the Tableau driver docs (https://www.tableau.com/support/drivers) and you’re right we can see from there “The Windows driver for PostgreSQL is installed by default with Tableau Server”. This should show up within Programs & Features (appwiz.cpl) as far as I know. Definitely best to check existing drivers before proceeding with an install of one.
[…] https://peter-whyte.com/creating-a-linked-server-with-a-postgres-database/ […]