Data transfer from one table in a PostgreSQL database to the another table in a different database.

Transfer Data between databases with PostgreSQL

      

      pg_dump sourceDB  -t fromTbl -c -s | psql -h 192.16.3.2 targetDB;

 Problem with pg_dump
    pg_dump: server version: 9.x.x ; pg_dump version: 9.y.y
    pg_dump: aborting because of server version mismatch

above command well suited if both server running on same version of PostgreSQL.
 If Source & Target server  are running in different version, have to use COPY command. 
 copy data from one table in a PostgreSQL database to the corresponding table in a different database running on different server.

i.e cross database copy/transfer data in PostgreSQL,

general syntax  for cross database copy command:

psql -c "copy (select list of column  from table_name ) to stdin " dbanme | psql -c "table_name(specify the column ) from stdout " targetDB

• Target table must be exist
• Table/Relation may differ in name
• if source and target databases are following different table schema  
• Want to transfer only few column from source database table to  target database 
• copy/transfer few column  between databases 
• Server may run different version of PostgreSQL database.
• cross database data transfer  

example :
in sourceDB table employee(eid,ename,esalary, edesignation )
in targetDB table staff(sid,sname,spay)
 now we would like to transfer data between this two databases 

psql -c " copy ( select eid, ename, esalary from employee) to stdin " sourceDB | psql -c " copy staff(sid,sname,spay) from stdout " targetDB

data from employee table in source database  copy/transferred to  staff table in targetDB. 

 note :

• single command will do both backup from one database and restore  to another database
• we just moved only 3 column from employee table not all  
• data type of the two table must be sameif source and target  databases in different server use -h option in psql command
• ex :
• psql -h 127.0.0.1 -c "copy (select eid,ename from emp ) to stdin " sourceDB | psql -h 192.168.37.2 -c "copy staff(sid,sname) from stdout " targetDB