Copy a table from one database to another in Postg

Question

I am trying to copy an entire table from one database to another in Postgres. Any suggestions?

Answer 1

I tried some of the solutions here and they were really helpful. In my experience best solution is to use psql command line, but sometimes i don't feel like using psql command line. So here is another solution for pgAdminIII

create table table1 as(
 select t1.* 
 from dblink(
   'dbname=dbSource user=user1 password=passwordUser1',
   'select * from table1'  
  ) as t1(
    fieldName1 as bigserial,
    fieldName2 as text,
    fieldName3 as double precision 
  )
 )

The problem with this method is that the name of the fields and their types of the table you want to copy must be written.

Answer 2

If you have both remote server then you can follow this:

pg_dump -U Username -h DatabaseEndPoint -a -t TableToCopy SourceDatabase | psql -h DatabaseEndPoint -p portNumber -U Username -W TargetDatabase

It will copy the mentioned table of source Database into same named table of target database, if you already have existing schema.

Answer 3

Here is what worked for me. First dump to a file:

pg_dump -h localhost -U myuser -C -t my_table -d first_db>/tmp/table_dump

then load the dumped file:

psql -U myuser -d second_db</tmp/table_dump

Answer 4

pg_dump does not work always.

Given that you have the same table ddl in the both dbs you could hack it from stdout and stdin as follows:

 # grab the list of cols straight from bash

 psql -d "$src_db" -t -c \
 "SELECT column_name 
 FROM information_schema.columns 
 WHERE 1=1 
 AND table_name='"$table_to_copy"'"
 # ^^^ filter autogenerated cols if needed     

 psql -d "$src_db" -c  \
 "copy ( SELECT col_1 , col2 FROM table_to_copy) TO STDOUT" |\
 psql -d "$tgt_db" -c "\copy table_to_copy (col_1 , col2) FROM STDIN"

Answer 5

Using psql, on linux host that have connectivity to both servers

( export PGPASSWORD=password1 
  psql -U user1 -h host1 database1 \
  -c "copy (select field1,field2 from table1) to stdout with csv" ) \
| 
( export PGPASSWORD=password2 
  psql -U user2 -h host2 database2 \ 
   -c "copy table2 (field1, field2) from stdin csv" )

Answer 6

First install dblink

Then, you would do something like:

INSERT INTO t2 select * from 
dblink('host=1.2.3.4
 user=*****
 password=******
 dbname=D1', 'select * t1') tt(
       id int,
  col_1 character varying,
  col_2 character varying,
  col_3 int,
  col_4 varchar 
);