Thursday, August 15, 2013

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




No comments: