How to Query SQL Server on AWS in PostgreSQL

How do I query SQL Server on AWS from PostgreSQL?

Use the tds_fdw foreign-data wrapper to connect PostgreSQL to an AWS-hosted Microsoft SQL Server instance and run cross-database queries.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why query SQL Server on AWS from PostgreSQL?

Centralizing analysis eliminates ETL overhead, lets teams join PostgreSQL data with AWS SQL Server data instantly, and keeps a single source of truth inside your main warehouse.

What prerequisites must be met?

A running AWS RDS or EC2 SQL Server instance, network access (VPC or public IP), PostgreSQL 13+ with superuser rights, and the tds_fdw or tds_fdw-based package such as pgsql-tds-fdw.

How to install tds_fdw?

Step 1 — install dependencies

On Ubuntu 22.04 run: sudo apt-get install postgresql-13-tds-fdw freetds-bin.Mac users can use Homebrew.

Step 2 — create the extension

Connect as superuser and run CREATE EXTENSION IF NOT EXISTS tds_fdw;. Verify with \dx.

How to connect PostgreSQL to AWS SQL Server?

Define a foreign server that points at the RDS endpoint, port 1433, and target database.Store credentials in CREATE USER MAPPING.

How to import SQL Server tables?

Run IMPORT FOREIGN SCHEMA to create read-through tables like customers, orders, and products in your PostgreSQL public schema.

How to run cross-database joins?

Query the foreign tables just like local ones.PostgreSQL planner will push eligible filters down to SQL Server, minimizing network traffic.

Best practices for performance?

Select only needed columns, add indexes in SQL Server to match join predicates, and use SET tds_fdw.fetch_size for large result sets.

Common mistakes and fixes

Mismatched data types: Cast SQL Server DATETIME2 to PostgreSQL TIMESTAMP explicitly.
Network timeouts: Open port 1433 in the AWS security group and increase tds_fdw.connection_timeout.

Why How to Query SQL Server on AWS in PostgreSQL is important

How to Query SQL Server on AWS in PostgreSQL Example Usage


-- Join local PostgreSQL table promo_codes with remote SQL Server tables
SELECT c.name,
       o.id        AS order_id,
       o.total_amount,
       p.code      AS promo_code
FROM   Orders      o
JOIN   Customers   c ON c.id = o.customer_id
JOIN   promo_codes p ON p.order_id = o.id  -- local table
WHERE  o.order_date >= CURRENT_DATE - INTERVAL '90 days'
ORDER  BY o.total_amount DESC
LIMIT  50;

How to Query SQL Server on AWS in PostgreSQL Syntax


-- 1. Install extension (superuser)
CREATE EXTENSION IF NOT EXISTS tds_fdw;

-- 2. Register the remote SQL Server running on AWS
CREATE SERVER aws_sqlserver
  FOREIGN DATA WRAPPER tds_fdw
  OPTIONS (
    servername 'sqlserver-demo.abc123xyz.us-east-1.rds.amazonaws.com',
    port '1433',
    database 'ecommerce'
  );

-- 3. Map a PostgreSQL role to a SQL Server login
CREATE USER MAPPING FOR app_user
  SERVER aws_sqlserver
  OPTIONS (
    username 'readonly',
    password 'AWS_SECRET_PASSWORD'
  );

-- 4. Import needed tables into PostgreSQL schema "public"
IMPORT FOREIGN SCHEMA dbo
  LIMIT TO (Customers, Orders, Products, OrderItems)
  FROM SERVER aws_sqlserver INTO public;

-- 5. Example read through foreign table
SELECT id, name, email, created_at FROM Customers WHERE created_at > CURRENT_DATE - INTERVAL '30 days';

Common Mistakes

Forgetting to grant privileges on the foreign server. Fix by executing GRANT USAGE ON FOREIGN SERVER aws_sqlserver TO app_user; so non-superusers can query.
Importing every table blindly. This bloats the catalog and slows planning. Limit IMPORT FOREIGN SCHEMA to only the required tables or use LIMIT TO.

Frequently Asked Questions (FAQs)

Can I write to AWS SQL Server from PostgreSQL?

Yes. Define UPDATE, INSERT, or DELETE permissions in the user mapping and ensure foreign tables have OPTIONS (updatable 'true'). Test on staging first.