SQL SERVER – How to Create Linked Server to PostgreSQL?

SQL
9 Comments

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?

Solarwinds

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';
  1. 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

SQL SERVER - How to Create Linked Server to PostgreSQL? PG-Linked-01

Create a Linked Server in SQL Server

  1. Go to and choose “Download” from menu bar.
    SQL SERVER - How to Create Linked Server to PostgreSQL? PG-Linked-02
  2. Go to the page and choose “PGNP OLEDB Providers for Postgres, Greenplum and Redshift” as shown below.
    SQL SERVER - How to Create Linked Server to PostgreSQL? PG-Linked-03
  3. Install it and then we should see provider it in SSMS
    SQL SERVER - How to Create Linked Server to PostgreSQL? PG-Linked-04
  1. 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
    

     

  2. Once completed, we should be able to see linked server created as shown below.
    SQL SERVER - How to Create Linked Server to PostgreSQL? PG-Linked-05

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');

SQL SERVER - How to Create Linked Server to PostgreSQL? PG-Linked-06

Have you ever created any other 3rd 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)

Solarwinds
, ,
Previous Post
Interview Question of the Week #046 – How @@DATEFIRST and SET DATEFIRST Are Related?
Next Post
SQL SERVER – What Resource Wait Are We Seeing?

Related Posts

9 Comments. Leave new

Leave a Reply

Menu