SQL SERVER – How to Create Linked Server to PostgreSQL?

The databases world is expanding and I have been fortunate enough to learn and share my experiences around databases like MySQL, PostgreSQL apart from working with SQL Server. I always try to expand my horizon and try various database products. In case you don’t know, I have a course on Pluralsight about PostgreSQL.

It is always interesting to integrate various products and make them work seamlessly. Due to my knowledge of both database products, I have been asked one question very frequently.

How can I create linked servers in SQL connecting with Postgres?

Though this question looks simple and easy – I thought of writing a note to show you the actual steps to achieve the same. This blog shows a simple example about creating linked server. There is a provider called PGOLEDB which can be used for this purpose.

Create a Database and Table in PostgreSQL

1. Create database:

CREATE DATABASE SQLAuthority WITH OWNER = postgres ENCODING = 'UTF8';

2. Once database is created, change the connection, create table and insert some data.

CREATE TABLE MyTable 
( 
ID integer NOT NULL, 
Name varchar(128) NOT NULL
);
insert into MyTable values (1, 'Pinal Dave');

Verify that we have data in table

Select * from MyTable

Create a Linked Server in SQL Server

1. Go to and choose “Download” from menu bar.
   
2. Go to the page and choose “PGNP OLEDB Providers for Postgres, Greenplum and Redshift” as shown below.
   
3. Install it and then we should see provider it in SSMS
   

4. Modify and run below script in SQL Server. You need to change Server Name, port etc.

   -- Change parameter for provider Allow In Procees = true / DynamicParameters = true
   EXEC MASTER.dbo.sp_MSset_oledb_prop N'PGNP'  ,N'AllowInProcess',1
   GO
   EXEC MASTER.dbo.sp_MSset_oledb_prop N'PGNP'  ,N'DynamicParameters',1
   GO
   DECLARE @name NVARCHAR(4000);
   DECLARE @provider NVARCHAR(4000);
   DECLARE @servername NVARCHAR(4000);
   DECLARE @port NVARCHAR(4000);
   DECLARE @db_name NVARCHAR(4000)
   -- destination postgres database
   SET @name = N'SQLAuth_PG';
   SET @provider = N'PGNP';
   SET @servername = N'localhost';
   SET @port = 'PORT=5432;'
   SET @db_name = N'sqlauthority';
   -- create linked server
   EXEC MASTER.dbo.sp_addlinkedserver @server = @name
   ,@srvproduct = N'PGNP'
   ,@provider = N'PGNP'
   ,@datasrc = @servername
   ,@provstr = @port
   ,@catalog = @db_name
   -- username and password for postgres
   EXEC MASTER.dbo.sp_addlinkedsrvlogin @rmtsrvname = @name
   ,@useself = N'False'
   ,@locallogin = NULL
   ,@rmtuser = N'postgres'
   ,@rmtpassword = 'sa'
   -- set up Extended properties of the Linked Server
   EXEC MASTER.dbo.sp_serveroption @server = @name
   ,@optname = 'data access'
   ,@optvalue = 'true'
   EXEC MASTER.dbo.sp_serveroption @server = @name
   ,@optname = 'use remote collation'
   ,@optvalue = 'true'
   EXEC MASTER.dbo.sp_serveroption @server = @name
   ,@optname = 'rpc'
   ,@optvalue = 'true'
   EXEC MASTER.dbo.sp_serveroption @server = @name
   ,@optname = 'rpc out'
   ,@optvalue = 'true'
   GO
   

    

5. Once completed, we should be able to see linked server created as shown below.
   

Test the Linked Server

Here are the test queries which I have used based on earlier script.

-- USING FOUR PART NAMING
SELECT [id],[name]
FROM   [SQLAuth_PG].[sqlauthority].[public].[mytable]
GO
-- USING OPENQUERY
SELECT *
FROM OPENQUERY(SQLAuth_PG, 'select id, name from mytable');

Have you ever created any other 3^rd party linked server in your environments? What methods have you used? Will you be kind enough to share the same via comments?

Reference : Pinal Dave (https://blog.sqlauthority.com)