blocks|key|991615|text|使用dblink会更方便！|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|991616|truncate+table+tableA;

insert+into+tableA
select+*
from+dblink('hostaddr=xxx.xxx.xxx.xxx+dbname=mydb+user=postgres',
++++++++++++'select+a,b+from+tableA')
+++++++as+t1(a+text,b+text);|code-block|syntax|javascript|991617|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/current/static/contrib-dblink-function.html^0|2|6|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Using <a href="http://www.postgresql.org/docs/current/static/contrib-dblink-function.html" rel="noreferrer">dblink</a> would be more convenient!
<pre><code>truncate table tableA;

insert into tableA
select *
from dblink('hostaddr=xxx.xxx.xxx.xxx dbname=mydb user=postgres',
 'select a,b from tableA')
 as t1(a text,b text);
</code></pre>

blocks|key|1206092|text|在与两台服务器都有连接的linux主机上使用psql|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1206093|(+export+PGPASSWORD=password1+
++psql+-U+user1+-h+host1+database1+\
++-c+"copy+(select+field1,field2+from+table1)+to+stdout+with+csv"+)+\
%7C+
(+export+PGPASSWORD=password2+
++psql+-U+user2+-h+host2+database2+\+
+++-c+"copy+table2+(field1,+field2)+from+stdin+csv"+)|code-block|syntax|javascript|1206094|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>( 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" )
</code></pre>

blocks|key|991700|text|第一个install+dblink|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|991701|然后，您将执行如下操作：|991702|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+
);|code-block|syntax|javascript|991703|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/message-id/f642fc4f-d81f-48a0-ae6f-30a210e42f4c@11g2000prv.googlegroups.com^0|3|E|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

First <a href="http://www.postgresql.org/message-id/f642fc4f-d81f-48a0-ae6f-30a210e42f4c@11g2000prv.googlegroups.com" rel="noreferrer">install dblink</a> 

Then, you would do something like:

<pre><code>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 
);
</code></pre>

blocks|key|1206335|text|如果你有两个远程服务器，那么你可以这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1206336|pg_dump+-U+Username+-h+DatabaseEndPoint+-a+-t+TableToCopy+SourceDatabase+%7C+psql+-h+DatabaseEndPoint+-p+portNumber+-U+Username+-W+TargetDatabase|code-block|syntax|javascript|1206337|如果您已经有了模式，它会将提到的源数据库的表复制到目标数据库的同名表中。|1206338|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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

<pre><code>pg_dump -U Username -h DatabaseEndPoint -a -t TableToCopy SourceDatabase | psql -h DatabaseEndPoint -p portNumber -U Username -W TargetDatabase
</code></pre>

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

blocks|key|1205999|text|使用pg_dump转储表数据，然后使用psql恢复它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1206000|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Use pg_dump to dump table data, and then restore it with psql.

blocks|key|1206286|text|要在本地设置中将表从数据库A移动到数据库B，请使用以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1206287|pg_dump+-h+localhost+-U+owner-name+-p+5432+-C+-t+table-name+database1+%7C+psql+-U+owner-name+-h+localhost+-p+5432+database2|code-block|syntax|javascript|1206288|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

To move a table from database A to database B at your local setup, use the following command:

<pre class="lang-sh prettyprint-override"><code>pg_dump -h localhost -U owner-name -p 5432 -C -t table-name database1 | psql -U owner-name -h localhost -p 5432 database2
</code></pre>

blocks|key|1206426|text|以下是对我起作用的方法。第一次转储到文件：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1206427|pg_dump+-h+localhost+-U+myuser+-C+-t+my_table+-d+first_db>/tmp/table_dump|code-block|syntax|javascript|1206428|然后加载转储的文件：|1206429|psql+-U+myuser+-d+second_db</tmp/table_dump|1206430|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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

<pre><code>pg_dump -h localhost -U myuser -C -t my_table -d first_db&gt;/tmp/table_dump
</code></pre>

then load the dumped file:

<pre><code>psql -U myuser -d second_db&lt;/tmp/table_dump
</code></pre>

blocks|key|1206382|text|与user5542464和Piyush+S.+Wanare的答案相同，但分为两个步骤：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1206383|pg_dump+-U+Username+-h+DatabaseEndPoint+-a+-t+TableToCopy+SourceDatabase+>+dump
cat+dump+%7C+psql+-h+DatabaseEndPoint+-p+portNumber+-U+Username+-W+TargetDatabase|code-block|syntax|javascript|1206384|否则，管道会同时询问这两个密码。|1206385|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/33609607/788700|1|https://stackoverflow.com/a/39012075/788700^0|1|B|0|D|G|1|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]|$A|Y|B|Z|1|10]]|C|$]]|$1|D|3|E|5|F|7|11|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|12|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|13|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]|S|$5|N|O|P|C|$Q|T]]]]

Same as answers by <a href="https://stackoverflow.com/a/33609607/788700">user5542464</a> and <a href="https://stackoverflow.com/a/39012075/788700">Piyush S. Wanare</a> but split in two steps:

<pre class="lang-sh prettyprint-override"><code>pg_dump -U Username -h DatabaseEndPoint -a -t TableToCopy SourceDatabase &gt; dump
cat dump | psql -h DatabaseEndPoint -p portNumber -U Username -W TargetDatabase
</code></pre>

otherwise the pipe asks the two passwords in the same time.

blocks|key|992002|text|pg_dump并不总是有效的。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|992003|假设您在两个数据库中有相同的表ddl，您可以从stdout和stdin中获取它，如下所示：|992004|+#+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"'"
+#+%5E%5E%5E+filter+autogenerated+cols+if+needed+++++

+psql+-d+"$src_db"+-c++\
+"copy+(+SELECT+col_1+,+col2+FROM+table_to_copy)+TO+STDOUT"+%7C\
+psql+-d+"$tgt_db"+-c+"\copy+table_to_copy+(col_1+,+col2)+FROM+STDIN"|code-block|syntax|javascript|992005|entityMap^0|0|7|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|S|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

<code>pg_dump</code> 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:

<pre><code> # 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"
</code></pre>

blocks|key|992017|text|检查此python+script|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|992018|python+db_copy_table.py+"host=192.168.1.1+port=5432+user=admin+password=admin+dbname=mydb"+"host=localhost+port=5432+user=admin+password=admin+dbname=mydb"+alarmrules+-w+"WHERE+id=19"+-v
Source+number+of+rows+=+2
INSERT+INTO+alarmrules+(id,login,notifybyemail,notifybysms)+VALUES+(19,'mister1',true,false);
INSERT+INTO+alarmrules+(id,login,notifybyemail,notifybysms)+VALUES+(19,'mister2',true,false);|code-block|syntax|javascript|992019|entityMap|0|LINK|mutability|MUTABLE|url|https://gist.github.com/viewpointsa/bba4d475126ec4ef9427fd3c2fdaf5c1^0|3|D|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Check this <a href="https://gist.github.com/viewpointsa/bba4d475126ec4ef9427fd3c2fdaf5c1" rel="nofollow noreferrer">python script</a>

<pre><code>python db_copy_table.py "host=192.168.1.1 port=5432 user=admin password=admin dbname=mydb" "host=localhost port=5432 user=admin password=admin dbname=mydb" alarmrules -w "WHERE id=19" -v
Source number of rows = 2
INSERT INTO alarmrules (id,login,notifybyemail,notifybysms) VALUES (19,'mister1',true,false);
INSERT INTO alarmrules (id,login,notifybyemail,notifybysms) VALUES (19,'mister2',true,false);
</code></pre>

blocks|key|992107|text|作为另一种选择，您还可以使用外部数据包装扩展将远程表公开为本地表。然后，您可以通过从远程数据库中的表中进行选择来将其插入到表中。唯一的缺点是它不是很快。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|992108|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

As an alternative, you could also expose your remote tables as local tables using the foreign data wrapper extension. You can then insert into your tables by selecting from the tables in the remote database. The only downside is that it isn't very fast.

blocks|key|991765|text|您必须使用DbLink将一个表的数据复制到不同数据库中的另一个表中。您必须安装并配置DbLink扩展才能执行跨数据库查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|991766|我已经在这个主题上创建了详细的帖子。Please+visit+this+link|offset|length|991767|entityMap|0|LINK|mutability|MUTABLE|url|http://www.dbrnd.com/2015/05/postgresql-cross-database-queries-using/^0|0|I|M|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

You have to use DbLink to copy one table data into another table at different database. 
You have to install and configure DbLink extension to execute cross database query. 

I have already created detailed post on this topic. 
<a href="http://www.dbrnd.com/2015/05/postgresql-cross-database-queries-using/" rel="nofollow">Please visit this link</a>

blocks|key|1206532|text|如果两个数据库(从和到)都有密码保护，在这种情况下，终端不会询问两个数据库的密码，密码提示将只出现一次。因此，要修复此问题，请将密码与命令一起传递。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1206533|PGPASSWORD=<password>+pg_dump+-h+<hostIpAddress>+-U+<hostDbUserName>+-t+<hostTable>+>+<hostDatabase>+%7C+PGPASSWORD=<pwd>+psql+-h+<toHostIpAddress>+-d+<toDatabase>+-U+<toDbUser>|code-block|syntax|javascript|1206534|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If the both DBs(from &amp; to) are password protected, in that scenario terminal won't ask for the password for both the DBs, password prompt will appear only once.
So, to fix this, pass the password along with the commands.

<pre><code>PGPASSWORD=&lt;password&gt; pg_dump -h &lt;hostIpAddress&gt; -U &lt;hostDbUserName&gt; -t &lt;hostTable&gt; &gt; &lt;hostDatabase&gt; | PGPASSWORD=&lt;pwd&gt; psql -h &lt;toHostIpAddress&gt; -d &lt;toDatabase&gt; -U &lt;toDbUser&gt;
</code></pre>

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

Copy a table from one database to another in Postgres

数据库

服务器

我正尝试在Postgres中将整个表从一个数据库复制到另一个数据库。有什么建议吗？

问在Postgres中将表从一个数据库复制到另一个数据库
EN

回答 13

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Postgres中将表从一个数据库复制到另一个数据库EN

回答 13

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Postgres中将表从一个数据库复制到另一个数据库
EN