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

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  …

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

pgBucket v1.0 is ready

pgBucket v1.0 pgBucket v1.0 (concurrent job scheduler for PostgreSQL) is released. This version is more stable and fixed the issues which was observed in the previous beta releases.
Highlights of this tool are Schedule OS/DB level jobsCron style syntax {Schedule up to seconds}On fly job modificationsInstant daemon status by retrieving live job queue, job hashEnough cli options to deal with all the configured/scheduled job Here is the URL for the pgBucket build/usage instructions. https://bitbucket.org/dineshopenscg/pgbucket
I hope this tool will be helpful for the PostgreSQL users to get things done in the scheduled time. Note: This tool requires c++11{gcc version >= 4.9.3} to compile.
--Dinesh