Monday, December 5, 2016

Postgres Cross-Server/Cross-Database Querying

To demonstrate cross database quering we will create two databases "PlaygroundLive" & "PlaygroundStaging".Both these database can be on same server or different postgresql server.In current case they are on same server.

On both databases create below table

create table TestTable
(
  id int primary key,
  name varchar(500)
)

on staging add three rows

insert into TestTable(id,name) values(1,'sagar'),(2,'sangram'),(3,'sachin');

on live add two rows

insert into TestTable(id,name) values(1,'sagar'),(2,'sangram');

Now create extensions called dblink & foreign data wrapper

CREATE EXTENSION postgres_fdw;

CREATE EXTENSION dblink;

Check if extension got created or not by checking newly added procedures/functions related to new extension db_link.

SELECT pg_namespace.nspname, pg_proc.proname FROM pg_proc, pg_namespace WHERE pg_proc.pronamespace=pg_namespace.oid AND pg_proc.proname LIKE '%dblink%';

On our staging Database connect to live database,check if connectivity succeed or not,query below give OK on success

SELECT dblink_connect('host=localhost user=postgres password=sangram dbname=PlaygroundLive');

CREATE FOREIGN DATA WRAPPER dbrnd VALIDATOR postgresql_fdw_validator;

Creating server connection:
Here we have both database on same postgres sql server yet we have to follow same process we need to provide server details & server IP & database to connect for.
CREATE SERVER demodbrnd FOREIGN DATA WRAPPER postgres_fdw OPTIONS (hostaddr '127.0.0.1', dbname 'PlaygroundLive');

you can check if server creation succeeded by running below command

SELECT * FROM pg_foreign_server; 
 
CREATE USER MAPPING FOR postgres SERVER demodbrnd OPTIONS (user 'postgres',
password 'sangram');
 
here postgres user on local server is mapped to postgres user on remote server

Connect to Server:

SELECT dblink_connect('demodbrnd');

GRANT USAGE ON FOREIGN SERVER demodbrnd TO postgres;

Running Cross Database Queries:
now from staging database we can run queries to live database


1) Select:
SELECT * FROM public.dblink('demodbrnd','select id,name from public.TestTable') AS DATA(id INTEGER,name CHARACTER VARYING);

it will have two records in output.

2) INSERT:
SELECT dblink_connect('demodbrnd');
select * from dblink_exec('INSERT INTO public.TestTable values(3,''sachin'')')

One more record will get added to TestTable on Live database.Now TestTable on both database are identical.

3) SELECT INTO:
SELECT * INTO temp_TestTable
FROM public.dblink('demodbrnd','select id,name from public.TestTable') 
AS DATA(id INTEGER,name CHARACTER VARYING);

Here temp_TestTable is created on Staging database will get all three 
records from TestTable in Live database.

4) Parametrized SELECT:
--using parametrized query with prepare
PREPARE migrate_data (integer) AS
INSERT INTO temp_TestTable
SELECT id, name
FROM dblink('demodbrnd', 'select id,name from public.TestTable')
AS t(id integer, name varchar)
WHERE id > $1;

EXECUTE migrate_data(2);

execution of prepare statement will add one more record to temp_
TestTable so there will 4 records now.

--check one more record got inserted
select * from temp_TestTable

TO delete Foreign data wrapper follow following sequence of drop 
queries as object stacked one above other.
Create Schema called “ForeignSchema” in staging database

Now import all tables from remote database public schema into staging 
atabase “ForeignSchema” as follows.

IMPORT FOREIGN SCHEMA public
FROM SERVER demodbrnd INTO "ForeignSchema";

Now remove 1st record from staging database table so that we are sure that 
both table differ as follows

delete from public.TestTable where id=1;

you can check that he our “TestTable” from live server has been imported 
into local database in schmema “ ForeignSchema” by running command 

select * from "ForeignSchema".TestTable;

Now we can run insert into statement also:
insert into "ForeignSchema".TestTable
select id+3,name from public.TestTable;

DROPPING Foreign Data WRAPPER: 
drop USER MAPPING FOR postgres SERVER demodbrnd ;
drop SERVER demodbrnd;
drop FOREIGN DATA WRAPPER dbrnd;

Sunday, December 4, 2016

Installing SQL SERVER on Fedora 23

Here are my notes on installation of MSSQL SERVER on Fedora.

First become root
    # su -

Microsoft sql server on fedora requires two new YUM repository one for actual sql server and other for command line tool to query it.

Add Microsoft Software repository to your Fedoara Machine as follows

   #curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo > /etc/yum.repos.d/mssql-server.repo

   #curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo

this will basically create two files in /etc/yum.repos.d/ folder namely “mssql-server.repo” & “msprod.repo”

Installing SQL Server :
   to install sql server issue following command

         #yum install -y mssql-server

this will install package yet we need to configure it by issuing following command

       #/opt/mssql/bin/sqlservr-setup

this will ask for System Administrator (SA) account password which requires two be 8 character,also ask if you want to start sql server at time of startup.

You can check if server is running by issuing following command

    #systemctl status mssql-server

output of this command in my case is

mssql-server.service - Microsoft(R) SQL Server(R) Database Engine
Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; disabled; vendor preset: disabled)
Active: active (running) since Sun 2016-12-04 20:04:11 IST; 27min ago
Main PID: 6431 (sqlservr)
Tasks: 144
Memory: 547.2M
CPU: 45.053s
CGroup: /system.slice/mssql-server.service
├─6431 /opt/mssql/bin/sqlservr
└─6493 /opt/mssql/bin/sqlservr

Dec 04 20:04:19 SangramOnFedora23 sqlservr[6431]: [145B blob data]
Dec 04 20:04:20 SangramOnFedora23 sqlservr[6431]: [66B blob data]
Dec 04 20:04:20 SangramOnFedora23 sqlservr[6431]: [75B blob data]
Dec 04 20:04:20 SangramOnFedora23 sqlservr[6431]: [96B blob data]
Dec 04 20:04:20 SangramOnFedora23 sqlservr[6431]: [100B blob data]
Dec 04 20:04:20 SangramOnFedora23 sqlservr[6431]: [71B blob data]
Dec 04 20:04:20 SangramOnFedora23 sqlservr[6431]: [124B blob data]
Dec 04 20:09:22 SangramOnFedora23 sqlservr[6431]: [71B blob data]
Dec 04 20:11:04 SangramOnFedora23 sqlservr[6431]: [156B blob data]
Dec 04 20:11:04 SangramOnFedora23 sqlservr[6431]: [193B blob data]

Microsoft SQL server runs on port 1433 we need to open it for commutation with server from outside of localhost.

If you firewall is running & blocking 1433 port then add exception to it

        # firewall-cmd --zone=public --add-port=1433/tcp --permanent
       # firewall-cmd --reload

If we have sql server up & running we can install tool to connect to it

    #yum install mssql-tools

This package needs “msodbcsqlpackage and it has dependency on “unixODBC” package.
In my case version of “ unixODBC” is higher than what “msodbcsql” can handle probably in with GAMBAS so I was getting error in installation of “mssql-tools

after quick googling I got a solution what we need is remove existing unixodbc package as follows

#yum remove unixODBC

then we need to add new repo & import corresponding keys

#yum-config-manager --add-repo https://apt-mo.trafficmanager.net/yumrepos/mssql-rhel7-release/
#yum-config-manager --enable mssql-rhel7-release
#wget "http://aka.ms/msodbcrhelpublickey/dpgswdist.v1.asc"
#rpm --import dpgswdist.v1.asc

and then install “msodbcsql

#yum install msodbcsql

after successful installation try to install “mssql-tools

#yum install mssql-tools

On success we will check how to connect to server from command line.
Run below command,do not forgot to replace SA account password with your sql server’s SA password.

#sqlcmd -S localhost -U SA -P 'SA_Account_PASSWORD'

we will run simple command to check if we are through.First type some sql command say 

select name from sys.databases;

hit enter and then type “go”  do not end it with semicolon and hit again to see result of sql query.

Output should be similar to below:

name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb 

to come out of sql-cmd command prompt need to use "quit" command.

We can use universal sql GUI tools like squirrel sql , dbeaver to connect to our newly installed 
sql server. Though  I got some success with squirrel sql but it was messed between Oracle JAVA & OPEN JAVA 
when I tried it on Ubuntu 16.We will explore this soon.

References:
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-red-hat