Distributed Database With dblink On PostgreSQL

Distributed database is a database that is under the control of a database management system (DBMS) which allows the storage media to be stored on various computers that are placed on the same physical location, or may be stored on a network of interconnected computers, or it can be said in a distributed database file are physically separated but logically integrated.

PostgreSQL is a database server that is open source. In addition to offering standard features of the language SQL (Structured Query Language), this database server provides additional features in the form of classes, inheritance, type and function, so the database is included in the ORDBMS (Object Relational Database Management System). Another important feature which is owned PostgreSQL include: constraints, triggers, rules, and transaction integrity that provides convenience in the implementation process.

Distributed database in PostgreSQL using DBLINK (Database Link Library) is part of the project PostgreSQL since version 7.2 as the name used for the interconnection between multiple databases. The need to enter data from the local database to remote database.

Syntax :

  1. Displaying data on the remote database
    select * from dblink(’host=host user=user password= password dbname= dbname’,sql);
  2. Manipulate data on the remote database
    Select dblink_exec(’host=host user=user password= password dbname= dbname’,sql);Note :
    host : ip address of the remote database
    user : username of the remote database
    password : password of the remote database
    dbname : name of the remote database
    sql : sql syntax to be executed on a remote database

Example :

 

 

 

 

For example, the database db_rmt (remote database) contained a table named student with fields is id_student and name.

  • Syntax to display data on table student from a local database is :select * from dblink(’host=192.168.0.7 user=user_rmt password= pass_rmt dbname= db_rmt’,’select * from student’);
  • Syntax to manipulate data on table student from a local database, eg :
    • Update the name of the student who has the id ‘S001’ to ‘Budi’ :
      Select dblink_exec(’host=192.168.0.7 user=user_rmt password= pass_rmt dbname= db_rmt’,’update student set name=”Budi” where id_student=S001’);
    • Delete the data of the student who has the id ‘S001’
      Select dblink_exec(’host=192.168.0.7 user=user_rmt password= pass_rmt dbname= db_rmt’, ’delete from student where id_student=S001’);