Setting up a linked server from SQL Server 2005 to PostgreSQL

Question

Since I struggled a bit with this one and couldn't find a good online source with simple steps, here it is.

Answer 1

1. Download ODBCng and install it
2. Set up a System DNS that connects to your PostgreSQL server. I named mine POSTGRESQL, which is used in the next couple of steps

3. Run the following code in SSMS to create the linked server. This assumes a PostgreSQL instance on the local machine (hence localhost):

   EXEC master.dbo.sp_addlinkedserver @server = N'POSTGRESQL', @srvproduct=N'Microsoft OLE DB Provider for ODBC Driver', @provider=N'MSDASQL', @datasrc='PostgreSQL', @location='localhost', @catalog='public'

4. Run the following code in SSMS to create a login mapping for the linked server:

   EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'POSTGRESQL', @useself=N'False', @locallogin=NULL, @rmtuser='', @rmtpassword=''

5. Issue statements such as:

   SELECT * FROM OpenQuery(POSTGRESQL, 'select my_column from my_table limit 10')

I had to use the double-quote

Answer 2

Thanks for this!

Answer 3

You can use PGNP OLE DB provider for PostgreSQL (pgoledb.com). See example on how to configure Linked Server at http://www.pgoledb.com/forum/viewtopic.php?f=4&t=6).

It is very high performance and scalable driver. We use it to work with tens of millions rows tables.

Regards, Carlos