I have been posting about different types of FDWs available with Postgres distribution by BigSQL. Recently one of our customer had a reqirement of bridging their SQLServer database with Postgres. Postgres by BigSQL is already equiped with it.
Here is how we can set it up in simple, easy steps.
Step 1. Install FreeTDS in your Operating system
Tabular Data Stream is the data exchange protocol / format used in SQL Server and Sybase and FreeTDS is a opensource project which impliments the TDS accross all operating systems. Most of the linux distributions made it available in their repository. On CentOS Linux, we can install it from repo like:
sudo yum -y install epel-release
sudo yum install freetds.x86_64
This steps may vary according to the operating system you use.
Step 2. Install TDS fdw
From the BigSQL installation directory invoke pgc commandline utililty to install tds_fdw for the postgres installation
./pgc install tds_fdw1-pg95
Step 3. Connect to Postgres and Create tds_fdw
On psql prompt, we can issue create extension statement like:
create extension tds_fdw;
This step requires freetds libraries to be available in library path.
Step 4. Create Server Definition and Grant Privilleges on that
create server TDS_TestDB foreign data wrapper tds_fdw options (servername '10.150.10.160', port '1433', database 'master', tds_version '7.0', character_set 'UTF-8');
grant usage on foreign server TDS_TestDB to postgres;
Step 5. Create user mapping
for creating user mapping, we have to have a valid user credentials on the SQLServer. Provide the user credentials in the statement as below.
create user mapping for postgres server TDS_TestDB options (username 'sa', password 'mysapassword');
Step 6. Define a “foreign” table in postgres
The “foreign table” needs to have the same defenition as the the table in SQL Server.
create foreign table T1 (ID int) server TDS_TestDB options (table 'T1');
Step 7. Verify the setup by querying.
Now we are ready to query on the foreign table.