This is a post on how to create a Linked Server to a PostgreSQL database.
In my previous post, I created a Linked Server to another SQL Server instance. This time, it’s a link to a Postgres database. I recommend checking out my other post for pre-requisite information including testing connectivity to the remote database server.
Follow these steps to create a Linked Server to a Postgres server:
1. Create Linked Server Login on Postgres.
2. Install the Postgres ODBC Driver.
3. Configure ODBC Data Source Settings.
4. Create Linked Server to a Postgres Database.
1. Create Linked Server Login on Postgres
We first need to create a login for the Linked Server on Postgres:
![Postgres Create New User](https://peter-whyte.com/wp-content/uploads/2019/03/pgadmin_create_login.png)
For this example I’m making the user in Postgres a Superuser. This is not ideal, we should be configuring it with the least permissions it requires. This is just a demo though.
![Postgres User Options](https://peter-whyte.com/wp-content/uploads/2019/03/pgadmin_create_login_all_details.png)
For demo purposes, I’m also showing how this would be done using a terminal:
![psql Create and View Users](https://peter-whyte.com/wp-content/uploads/2019/03/psql_create_and_view_logins.png)
2. Install Postgres ODBC Driver
The ODBC driver must be installed on both ends. This step is a reminder to ensure you have this installed.
You can use Stack Builder to install it, or download the Postgres ODBC driver package on its own:
![Postgres ODBC Driver Download](https://peter-whyte.com/wp-content/uploads/2019/03/psqlodbc_download_arrowed.png)
We need to install this:
![psqlODBC Setup Wizard](https://peter-whyte.com/wp-content/uploads/2019/03/psqlODBC_setup_wizard.png)
Accept License stuff.
Specify directory & we don’t need the documentation.
![psqlODBC Feature Selection](https://peter-whyte.com/wp-content/uploads/2019/03/psqlODBC_custom_setup.png)
It’s a straight-forward install:
![psqlODBC Install Wizard](https://peter-whyte.com/wp-content/uploads/2019/03/psqlODBC_install_finished.png)
3. Configure ODBC Data Source Settings
On our SQL Server, we need to configure the ODBC driver for the Postgres database.
To do this, open ODBC Data Sources as Administrator:
![Run ODBC Source as Administrator](https://peter-whyte.com/wp-content/uploads/2019/03/run_odbc_data_sources_as_administrator_arrowed.png)
Click the System DSN tab and click Add.
![ODBC Data Source System DSN](https://peter-whyte.com/wp-content/uploads/2019/03/odbc_data_source_system_dsn_arrowed.png)
Select the PostgreSQL Unicode driver:
![Create New Postgres ODBC Data Source](https://peter-whyte.com/wp-content/uploads/2019/03/create_new_postgres_data_source_arrowed.png)
Enter all details required, In this example I created a ‘sales’ database which I’m connecting to:
![ODBC Data Source Settings for Postgres](https://peter-whyte.com/wp-content/uploads/2019/03/odbc_data_source_postgres_connection.png)
Finally, we should test the connection to verify all is good:
![ODBC Test Connection](https://peter-whyte.com/wp-content/uploads/2019/03/odbc_data_source_postgres_connection_test.png)
4. Create Linked Server to a Postgres Database
To create a Linked Server to Postgres:
1. Expand Server Objects, right-click Linked Servers and select New Linked Server.
![SSMS New Linked Server](https://peter-whyte.com/wp-content/uploads/2019/03/ssms_new_linked_server.png)
2. The New Linked Server Wizard will prompt.
Configure your settings, choose a provider for ODBC, entering the correct Data Source Name which you entered within the ODBC driver settings above in this post.
![SSMS New Linked Server Postgres](https://peter-whyte.com/wp-content/uploads/2019/03/ssms_new_linked_server_general_tab.png)
3. Enter the Postgres Login Details as created above within this post in the Security tab.
![SSMS Linked Server Login Details](https://peter-whyte.com/wp-content/uploads/2019/03/ssms_new_linked_server_security_tab.png)
4. We can skip the Server Options tab and hit OK.
5. Next, we should test the Linked Server connection.
![SSMS Test Linked Server Connection](https://peter-whyte.com/wp-content/uploads/2019/03/ssms_test_linked_server_connection.png)
It should show this:
![Linked Server Connection Test](https://peter-whyte.com/wp-content/uploads/2019/03/linked_server_connection_test.png)
We can now verify the Linked Server is working as it should by running the following query:
-- linked server query example select * from [linked-server-name].[database-name].[schema-name].[table-name];
![Linked Server Query with Postgres](https://peter-whyte.com/wp-content/uploads/2019/03/query_postgres_with_linked_server.png)
I hope this was a good and useful post for you. Feel free to check out the links you see around here for more random useful info from a random database guy!
Leave a Reply