Skip to main content

DBLINK in PostgreSQL

Hi,

Find the below DBLINK concepts in PostgreSQL, PostgresPlus as well.


PostgreSQL
----------
Foreign Data Wrapper
--------------------
This is the one utility which encapsulates the data from the different Database servers using Handler functions.

Ex:- In PG 9.1, we can encapsulates the data from My Sql and store to PostgreSQL using handlers.
CREATE FOREIGN DATA WRAPPER HANDLER VALIDATOR ;

Currently(<= 9.0) PostgreSQL compatible with only remote PostgreSQL. From 9.1 onwards, we can make the PostgreSQL to communicate with SQL Server/My SQL through these wrappers. Servers ------- This is the one object which is having the Data wrapper methodology as well the remote host connectivity details. Ex:- CREATE SERVER FOREIGN DATA WRAPPER OPTIONS (address , port ,dbname );

User Mapping
------------
This is the one object which maps the current dbserver user/role to remote dbserver user.

Ex:-CREATE USER MAPPING FOR SERVER OPTIONS (username , password );

DBLINK
------
Dblink is one of the contrib module, which collects the data from the remote or different database through TCP/IP.

Test Case
---------
Remote
------
HOST :: 127.0.0.1
PORT :: 5434
Dbname :: postgres
Username :: postgres
Table :: dblinktest

Local
-----
HOST :: 127.0.0.1
PORT :: 5433
Username :: dinesh

Here, we are fetching the 5434 cluster's dblinktest table data as a "dinesh" user.

Step 1:
-------
postgres=# CREATE FOREIGN DATA WRAPPER DBLINK_FDW;
CREATE FOREIGN DATA WRAPPER

--It takes the postgresql by default handler and default validator.

Step 2:
-------
postgres=# CREATE SERVER DBLINK_FDW_SERVER FOREIGN DATA WRAPPER DBLINK_FDW OPTIONS(hostaddr '127.0.0.1',dbname 'postgres',port '5434');
CREATE SERVER

Step 3:
-------
postgres=# CREATE USER MAPPING FOR DINESH SERVER DBLINK_FDW_SERVER OPTIONS(USER 'postgres',password 'postgres');
CREATE USER MAPPING

Step 4:
-------
postgres=# GRANT USAGE ON FOREIGN SERVER DBLINK_FDW_SERVER to DINESH;
GRANT
--Giving the required permissions to the user Dinesh.

Step 5:
-------
postgres=# \c postgres dinesh
You are now connected to database "postgres".
postgres=> SELECT DBLINK_CONNECT('DBLINK_CONN','dblink_fdw_server');
dblink_connect
----------------
OK
(1 row)

Step 6:
-------
postgres=> SELECT * FROM DBLINK('DBLINK_CONN','SELECT * FROM TEST LIMIT 3') AS T(A INT);
a
---
1
2
3
(3 rows)


PostgresPlus
--------------
As we all know, Postgresplus is oracle compatable. So, We can directly execute the followind dblink in Postgreplus Advanced server.

Syntax:-

CREATE DATABASE LINK
CONNECT TO IDENTIFIED BY ''
USING libpq 'host= dbname= port=';

Ex:-
Step 1:
-------
edb=# CREATE DATABASE LINK DBLINK_PPAS CONNECT TO enterprisedb IDENTIFIED BY 'adminedb' using libpq 'host=127.0.0.1 dbname=edb port=5555';
CREATE DATABASE LINK
edb=# show port;
port
------
5444
(1 row)

Step 2:
-------
edb=# SELECT * FROM TEST@DBLINK_PPAS LIMIT 3;
t
---
1
2
3
(3 rows)

--Dinesh

Comments

Popular posts from this blog

pgBucket - A new concurrent job scheduler

Hi All,

I'm so excited to announce about my first contribution tool for postgresql. I have been working with PostgreSQL from 2011 and I'm really impressed with such a nice database.

I started few projects in last 2 years like pgHawk[A beautiful report generator for Openwatch] , pgOwlt [CUI monitoring. It is still under development, incase you are interested to see what it is, attaching the image here for you ],


pgBucket [Which I'm gonna talk about] and learned a lot and lot about PostgreSQL/Linux internals.

Using pgBucket we can schedule jobs easily and we can also maintain them using it's CLI options. We can update/insert/delete jobs at online. And here is its architecture which gives you a basic idea about how it works.


Yeah, I know there are other good job schedulers available for PostgreSQL. I haven't tested them and not comparing them with this, as I implemented it in my way.
Features are: OS/DB jobsCron style sytaxOnline job modificationsRequired cli options

Pgpool Configuration & Failback

I would like to share the pgpool configuration, and it's failback mechanism in this post.

Hope it will be helpful to you in creating pgpool and it's failback setup.

Pgpool Installation & Configuration

1. Download the pgpool from below link(Latest version is 3.2.1).
    http://www.pgpool.net/mediawiki/index.php/Downloads


2. Untart the pgpool-II-3.2.1.tar.gz and goto pgpool-II-3.2.1 directory.

3. Install the pgpool by executing the below commands:

./configure ­­prefix=/opt/PostgreSQL92/ ­­--with­-pgsql­-includedir=/opt/PostgreSQL92/include/ --with­-pgsql­-libdir=/opt/PostgreSQL92/lib/ make make install 4. You can see the pgpool files in /opt/PostgreSQL92/bin location.
/opt/PostgreSQL92/bin $ ls clusterdb   droplang  pcp_attach_node  pcp_proc_count pcp_systemdb_info  pg_controldata  pgpool pg_test_fsync pltcl_loadmod  reindexdb createdb    dropuser  pcp_detach_node  pcp_proc_info createlang  ecpg      pcp_node_count   pcp_promote_node oid2name  pcp_pool_status  pcp_stop_pgpool  …

N-Node Mutlimaster Replication With Bucardo...!

Our team recently got  a problem, which is to solve the N-Node multi master replication in PostgreSQL.

We all know that, there are some other db engines like Postgres-XC which works in this way. But, we don't have any tool available in PostgreSQL, except Bucardo.

Bucardo is the nice solution for 2-Nodes. Is there a way we can exceed this limitation from 2 to N..?

As an initial step on this, I have done with 3 Nodes, which I believe, we can extend this upto N. { I might be wrong here.}

Please follow the below steps to set up the 1 - 1 multi master replication.

1. Follow the below steps to get all the pre-requisites for the Bucardo.

yum install perl-DBIx-Safe or apt-get install libdbix-safe-perl Install the below components from CPAN. DBI DBD::Pg Test::Simple boolean (Bucardo 5.0 and higher) Download the latest tarball from here. tar xvfz Bucardo-4.4.8.tar.gz cd Bucardo-4.4.8 perl Makefile.PL make sudo make install 2. We need to create plperl extension in db. For this, download…