I need simple example how to copy data from database DB1 table T1 to database DB2 table T2.
T2 has identical structure like T1 (same column names, properties. Just different data) DB2 running on same server like DB1, but on different port.
1
-
1Use a foreign data wrapper.user330315– user3303152016-09-14 10:01:21 +00:00Commented Sep 14, 2016 at 10:01
Add a comment
|
2 Answers
In the case the two databases are on two different server instances, you could export in CSV from db1 and then import the data in db2 :
COPY (SELECT * FROM t1) TO '/home/export.csv';
and then load back into db2 :
COPY t2 FROM '/home/export.csv';
Again, the two tables on the two different database instances must have the same structure.
Using the command line tools : pg_dump and psql , you could do even in this way :
pg_dump -U postgres -t t1 db1 | psql -U postgres -d db2
You can specify command line arguments to both pg_dump and psql to specify the address and/or port of the server .
Another option would be to use an external tool like : openDBcopy, to perform the migration/copy of the table.
7 Comments
aleroot
Removed, since the actual solution for the question, is to export in CSV and import back.
Laurenz Albe
Why
COPY (SELECT * FROM t1) and not COPY t1? Also, omit the db1 and db2 qualifiers.Norbertas Brazaitis
Okey. csv solution is accepted. But 1 problem remains. When copying into db2 i need sames values except ID. I need insert like new rows with new ID's. Any suggestions?
|
You can try this one -
pg_dump -t table_name_to_copy source_db | psql target_db
1 Comment
PirateApp
upvoted! how do you copy only 4 columns from table1 of database 1 to 4 columns of table1 of database2?