Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server and PostgreSQL Linked Server Configuration - Part 2


By:   |   Updated: 2015-06-29   |   Comments (15)   |   Related: 1 | 2 | 3 | More > Other Database Platforms

Problem

We need to have PostgreSQL and SQL Server database platforms communicate in our environment. We need to access the PostgreSQL data from SQL Server in an efficient manner. Based on the steps from your first tip, how can we take the next steps to setup the data access?  Can we create a Linked Server from SQL Server to PostgreSQL to access the data?

Solution

In part 1 of this series, we rolled out a simple database infrastructure with a PostgreSQL instance and a SQL Server instance. We have seen how both the servers could communicate with each other at network level.  Then we restored a sample database in PostgreSQL and created two of its table structures in SQL Server.

In this tip, we will show how SQL Server can access Postgres data and populate those tables.

Install PostgreSQL ODBC Driver

Although the de-facto data access library for any modern database should be based on OLE DB, PostgreSQL's official site doesn't list any freely available x64 bit OLE DB providers. As mentioned in part 1, there's a 64-bit OLE DB provider available from a third-party vendor. However, that driver comes with a price tag. The free OLE DB version available from Postgres site is for 32-bit only.

However, PostgreSQL also provides a 64-bit ODBC driver that's downloadable from its official site. In our example, we will download and install this 64-bit ODBC driver (psqlODBC) on our SQL Server.

Step 1: Remote desktop to SQL Server

Step 2: Browse to PostgreSQL's official download site for psqlODBC and download the zip file containing the x64 bit .msi installer. The file we will download is called psqlodbc_09_03_0300-x64-1.zip. We can see the driver is for PostgreSQL 9.3 and it's meant for 64-bit Windows. Once the download completes, unzip the file. The extracted content looks like this:

Extracted files of the x64 bit ODBC driver

Step 3: Double-click to start the psqlodbc_x64.msi installer. The next few images show the straightforward installation process.

Installing the 64-bit ODBC driver for PostgreSQL 9.3
Installing the 64-bit ODBC driver for PostgreSQL 9.3
Installing the 64-bit ODBC driver for PostgreSQL 9.3
Installing the 64-bit ODBC driver for PostgreSQL 9.3
Installing the 64-bit ODBC driver for PostgreSQL 9.3

Create ODBC Data Source

Once the driver has been installed, it's time to create a System DSN from it. So let's start the ODBC Data Source (64 bit) application from the Server Manager applet (see below).

Starting ODBC Data Source (64 bit) applet

In the next few screenshots, we can see how an ODBC data source is created.

Step 1: First let's choose the System DSN tab and then click Add...

Creating a System DSN

Step 2: Next we choose the PostgreSQL Unicode (x64) version and click Finish.

Selecting PostgreSQL Unicode (x64) version

Step 3: In the dialog box that pops-up, provide a name and description for the data source, specify the database name, server's IP address, port, user name and password as connection parameters. Once done, test the details by clicking on the Test button.

Specifying PostgreSQL new data source details

If the test is successful, click Save and then click OK in the ODBC Data Source Administrator.

Create a SQL Server Linked Server to PostgreSQL

Step 1: Start SQL Server Management Studio and connect to the SQL Server instance as "sa" or a sysdmin role member. Expand the Server Objects folder, right click on the Linked Servers node, and then choose "New Linked Server..." option from the pop-up menu.

On the General tab of the New Linked Server dialog box, choose the "Other data source" option, select "Microsoft OLE DB Provide for ODBC Drivers" option from the Provider drop-down list, provide a name for the Product and specify the Data Source name. The data source should be the one we just created: in this case it's world_db_postgres.

Creating linked server against the PostgreSQL instance

Step 2: In the Security tab, choose the fourth option ("Be made using this security context") and provide a login name and password to connect to the remote PostgreSQL instance. In this case we have used the built-in Postgres super user account to keep things simple.

Security option for linked server to PostgreSQL

Step 3: In the Server Options tab, choose the following options:

Specifying linked server data access options

Click OK. If the connection is successful, the Linked Server will be created without any error.

Expanding the Linked Server node in SQL Server Management Studio would show us the tables in the world database in PostgreSQL.

Accessing PostgreSQL tables using linked server

Access PostgreSQL Data from SQL Server

Now that we can see the remote data, let's fetch it into SQL Server. Open a new query window in Management Studio, select the world database and execute the following commands:

INSERT INTO city SELECT * FROM WORLD_SAMPLE.world.[public].city

The result should show 4079 rows have been copied. Next, execute this command:

INSERT INTO country SELECT * FROM WORLD_SAMPLE.world.[public].country

This should show 239 rows have been copied.

To be sure, you can count the number of rows in the local tables.

Conclusion

So now we have it. We have created an ODBC connection against the remote PostgreSQL instance, created a linked server on top of it and then executed two commands to copy across the data. There was no need to export the source data into text files and importing them using BCP or BULK INSERT.

This process can obviously be automated via scripts and stored procedures that are called by SQL Server Integration Services Packages or SQL Server Agent Jobs. SQL Server doesn't give us any option to create push or pull replication subscription against PostgreSQL databases.

We haven't discussed data access speed via ODBC, nor have we discussed any migration pitfalls like data type mismatches. The idea was to show how SQL Server can access PostgreSQL data seamlessly. Executing any PostgreSQL functions or stored procedures from SQL Server is another area your data migration team may have to consider.

Next Steps
  • Stay tuned for the final part of this series
  • Download and install the PostgreSQL ODBC driver and configure a data source and linked server to access PostgreSQL data
  • Visit PostgreSQL official website for more information


Last Updated: 2015-06-29


get scripts

next tip button



About the author
MSSQLTips author Sadequl Hussain Sadequl Hussain has been working with SQL Server since version 6.5 and his life as a DBA has seen him managing mission critical systems.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, July 15, 2019 - 1:34:40 PM - Bill Back To Top

I am trying to setup a linked server from AWS EC2 SQL server to AWS RDS Postgres and followed your procedures step by step, but it does not work, the error message is:

OLE DB provider "MSDASQL" for linked server "RDSDEVPOSTGRES" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "RDSDEVPOSTGRES" returned message "FATAL:  password authentication failed for user "xxxxxx".

The ODBC system DSN was setup and tested successfully, I also tried to connect the AWS Postgres inatance use pgAdmin, it worked fine as well.

I am not sure what I missed, any help is great appreciated.


Thursday, June 21, 2018 - 8:27:14 AM - Shobha Back To Top

Very easy to understand article specially if you are doing the PostgreSQL migration to SQL Server the first time. Looking forward to next article in series.

I have been working with PostgreSQL to SQL Server migration for a couple of months. Still stumbling with all the various data type conversions, specially arrays, bytea, etc. . Will be nice to have an article on that. We have also purchased the third party OLE DB provider to help with big tables (over 5 million rows).   


Tuesday, April 24, 2018 - 11:49:33 AM - bipin Back To Top

could not connect to server unknown host. where is server address


Tuesday, December 19, 2017 - 9:33:21 AM - m32pl Back To Top

 

 Thanks for a great article.

I finally get it working, just odbc configuration must be performed on the computer where ms-sql is running (sql error 7303)

 

 


Friday, March 31, 2017 - 7:11:03 AM - Pawan Back To Top

Hi,

Can you please confirm if a Server reboot is required after installing PostgreSQL ODBC driver.

 

 


Tuesday, December 13, 2016 - 7:00:27 PM - CJ Back To Top

Thanks for the post.    

Im trying to connect to PostgreSQL Database which is SSL allow . i manage to create and connect successfully with ODBC Dat Source .

but  im getting below  *Error with SQL Link server  configuration. 

My Server Version details 

SQL 11.0.3156 

PostgreSQL 9.5.4 

 

  **Error 

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "PostgreSQL".

OLE DB provider "MSDASQL" for linked server "PostgreSQL" returned message "[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application". (Microsoft SQL Server, Error: 7303)

 

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3156&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

 

 


Friday, July 29, 2016 - 9:13:36 PM - Javier Back To Top

 Gracias! muy completa y precisa la informacion.

Thanks! very torough and accurate information. I was struggling to perform a database lookup to a PostgresSQL DB from a modern application that only supports .NET System.Data.OleDb class data providers. Instead of needlesly spending money on buying a commercial component (I couldn´t put to work the free available ones...) I followed the detailed instructions on linking a Postgres 9 DB in MSSMStudio 2014 and it, after a few tweaks, simply worked. Great! 

 


Thursday, June 30, 2016 - 1:33:12 PM - Manuel Flores Back To Top

 

 Hi, I try to connect SQL Server 2008 R2 Enterprise with Postgre, Created and tested ODBC success, but  when I want to create the Linked Server using the ODBC show me one error, i teked the screen, next

"Cannot initailize the data source object of OLEDB provider "MSDASQL" for Linked Server

"POSTGRE_CC_REPORTING"

OLE DB provider "MSDASQL" for linked server "POSTGRE_CC_REPORTING" returned message 

"[Microsoft][ODBC driver manager] Data source name not found and no default driver specified".

(Microsoft SQL Server, Error:7303)

 

And I Found the error code, but I can't to resolve this isue.

 

In my Developer envoriontment It works without problem. but In Production environment Not.

 

Can you Help me?

Best regards

 


Tuesday, February 23, 2016 - 4:41:51 PM - Sadequl Hussain Back To Top

Hi Abouzar,

 

You can find commercial OLEDB provider for PostgreSQL from the PGNP site:

http://www.pgoledb.com/

 


Tuesday, February 23, 2016 - 12:34:37 AM - abouzar Back To Top

 

 hi

tanks for your education

i need to to create a link server to postgres via oledb provider instead odbc connection

please help me?

tanks alot


Wednesday, July 29, 2015 - 8:22:05 AM - Sadequl Hussain Back To Top

Hi Graves,

 

Not sure why you need to use OPENQUERY instead of directly calling the linked server, and also what is the error message youa re geting.

However, I noticed in your query you have used "workbooks" in the OPENQUERY statement and "workbook" when using the linked server. Do you receive any error message for this? Also, I would recommend using a linked server without any special chacraters like hyphens embedded (for example, BI_PORTAL instead of BI-Portal. If that can be implemented, you can use a command like this:

SELECT * FROM BI_PORTAL.workgroup.[public].domains.

Hope this helps


Tuesday, July 28, 2015 - 3:32:07 AM - Graves Kilsgaard Back To Top

Hi Sadequl

Thanks for a great article.

I have tried for sometime to integrate a progreSQL database with an MS SQL 2012 database.

I have to write an OPENQUERY: SELECT * FROM OPENQUERY ([BI-Portal], ' SELECT * FROM workbooks ') to get data from ProgreSQL to MS SQL through SSMS.

I cannot write a query like: select * from [Bi-Portal].[workgroup].[public].[domains]

Is that something you have had any experience with?

I have used the ODBC driver as you described :-)

Thanks on advance.


Tuesday, June 30, 2015 - 7:06:28 AM - Sadequl Hussain Back To Top

Hi Sabir, yes, once you create a linked server to a remote data source, you can access its tables as if they were local. You need to use the <linked_server_name><database_name>.<schema_name>.<table_name> notation to access the remote tables.


Tuesday, June 30, 2015 - 5:18:21 AM - Sabir Hazratzai Back To Top

Can we use the linked postgre tables directly in our views and stored procedures?


Monday, June 29, 2015 - 7:02:52 PM - Ekbal Back To Top

Once again excellet Sadeq...  Good piece of informaitons. Cheers


Learn more about SQL Server tools