Cross Database migration in Postgres??

Hi,

Target: To migrate a tables data from one database to another database in Postgres(my working version is 8.3)

Solution: The "dblink" command in Postgres will help you do just this.

Example:
Consider 2 databases app1 and app2.
Each of the databases has table "emp".
To make it simple lets just migrate data of 2 columns(emp_id, emp_name) from app1 to app2.
Connect to db app2(Where you want the Data to be inserted) and run the command:

Insert into emp (
 SELECT
  *
 FROM
  dblink('dbname=app1', 'select emp_id, emp_name from emp')
       AS t1(emp_id int, emp_name text)

Prerequisites:

If you face an error that the function dblink(unknown,unknown) is not present, then this is what you can do,
Find the dblink.sql file in location of Postgres. Something like this, /PostgreSQL/8.3/share/postgresql/contrib
Execute this file and the function will be created. Then try the solution.

The dblink command is used to execute all sorts of commands to remote databases. So the uses that you can make for solutions with this command are many.

References:
http://www.postgresql.org/docs/current/static/dblink.html
http://developer.postgresql.org/pgdocs/postgres/contrib-dblink.html