dblink
is a PostgreSQL contrib module that can be found in the folder contrib/dblink.
It is treated as an extension. The goal of this module is to provide simple
functionalities to connect and interact with remote database servers from a
given PostgreSQL server to which your client application or driver is
connected.
Here we are using 2 different server.
Host server -- test_db01 (tables here) & testdb02 (Db link )
Some data
will be inserted on test_db01, and the
goal is to fetch this data to testdb02 using
dblink.
1 - ( By root user ) Confirm contrib rpm for postgres on both server
-bash-4.1$ rpm -qa postgresql9*
postgresql94-contrib-9.4.4-1PGDG.rhel6.x86_64
2 - Let's first prepare
test_db01 and create some data on it.
-bash-4.1$ psql postgres
psql (9.4.4)
Type "help" for help.
testdb=# create table tab (a int, b
varchar(3));
CREATE TABLE
testdb=# insert into tab values (1,
'aaa'), (2,'bbb'), (3,'ccc');
INSERT 0 3
3 - (test_db02) The
sources of dblink have been installed, but they are not yet active on
test_db02. dblink is treated as an extension, which is a functionality that has
been introduced since PostgreSQL 9.1. In order to activate a new extension
module, here dblink, on a PostgreSQL server, the following commands are
necessary.
postgres=# CREATE EXTENSION dblink;
CREATE EXTENSION
postgres=# \dx
List of
installed extensions
Name | Version | Schema
|
Description
---------+---------+------------+--------------------------------------------------------------
dblink
| 1.1 | public | connect to other PostgreSQL databases
from within a database
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural
language
(2 rows)
On both server pg_hba.conf should have entry for both server and
postgres cluster reload/restart.
4- (test_db02) Now
let's fetch the data from test_db02 with dblink while connecting on test_db01.
The function dblink can be invocated to fetch data as it uses as return type
"SETOF record". This implies that the function has to be called in
FROM clause.
postgres=# select * from
dblink('hostaddr=test_db01 port=5432 dbname=testdb', 'select * from tab') as t1
(a int, b varchar(3));
-bash-4.1$ psql
psql (9.4.4)
Type "help" for help.
postgres=# select * from
dblink('hostaddr=test_db01 port=5432 dbname=testdb', 'select * from tab') as t1
(a int, b varchar(3));
a | b
---+-----
1 | aaa
2 | bbb
3 | ccc
(3 rows)
Note :- Do not forget to use aliases in the FROM clause to avoid errors
of the following type:
postgres=# select * from dblink_exec('port=5432 dbname=postgres', 'select * from tab');
ERROR: statement returning results not allowed
It is also possible to do more fancy stuff with
dblink functions. dblink_connect allows you to create a permanent connection to
a remote server. Such connections are defined by names you can choose. This
avoids to have to create new connections to remote servers all the time at
invocating of function dblink, allowing to gain more time by maintaining
connections alive. In case you wish to use the connection created, simply
invocate its name when using dblink functions.
Execution of other queries, like DDL or DML, can
be done with function dblink_exec.
postgres=# select dblink_exec('port=5432 dbname=postgres', 'create table aa (a int, b int)');
dblink_exec -------------- CREATE TABLE(1 row) *****************************************************************
No comments:
Post a Comment