postgresql 在 Postgres 中将表从一个数据库复制到另一个数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3195125/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy a table from one database to another in Postgres
提问by nix
I am trying to copy an entire table from one database to another in Postgres. Any suggestions?
我试图在 Postgres 中将整个表从一个数据库复制到另一个数据库。有什么建议？
回答by thomax
Extract the table and pipe it directly to the target database:
提取表并将其直接通过管道传输到目标数据库：
pg_dump -t table_to_copy source_db | psql target_db
Note:If the other database already has the table set up, you should use the -aflag to import data only, else you may see weird errors like "Out of memory":
注意：如果其他数据库已经设置了表，您应该使用该-a标志仅导入数据，否则您可能会看到诸如“内存不足”之类的奇怪错误：
pg_dump -a -t my_table my_db | psql target_db
回答by a2ron44
You can also use the backup functionality in pgAdmin II. Just follow these steps:
您还可以使用 pgAdmin II 中的备份功能。只需按照以下步骤操作：
- In pgAdmin, right click the table you want to move, select "Backup"
- Pick the directory for the output file and set Format to "plain"
- Click the "Dump Options #1" tab, check "Only data" or "only Schema" (depending on what you are doing)
- Under the Queries section, click "Use Column Inserts" and "User Insert Commands".
- Click the "Backup" button. This outputs to a .backup file
- Open this new file using notepad. You will see the insert scripts needed for the table/data. Copy and paste these into the new database sql page in pgAdmin. Run as pgScript - Query->Execute as pgScript F6
- 在 pgAdmin 中，右键单击要移动的表，选择“备份”
- 选择输出文件的目录并将格式设置为“普通”
- 单击“Dump Options #1”选项卡，选中“Only data”或“only Schema”（取决于您在做什么）
- 在查询部分下，单击“使用列插入”和“用户插入命令”。
- 单击“备份”按钮。这输出到 .backup 文件
- 使用记事本打开这个新文件。您将看到表/数据所需的插入脚本。将这些复制并粘贴到 pgAdmin 中的新数据库 sql 页面中。作为 pgScript 运行 - 查询->作为 pgScript F6 执行
Works well and can do multiple tables at a time.
效果很好，一次可以做多个表。
回答by tinychen
回答by Alexey Sviridov
Using psql, on linux host that have connectivity to both servers
在连接到两台服务器的 linux 主机上使用 psql
( 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" )
回答by Alexey Sviridov
First install dblink
首先安装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
);
回答by Pablo Santa Cruz
Use pg_dump to dump table data, and then restore it with psql.
使用pg_dump转储表数据，然后用psql恢复。
回答by Piyush S. Wanare
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.
如果您已经拥有现有架构，它会将源数据库的上述表复制到目标数据库的同名表中。
回答by Gowtham Balusamy
You could do the following:
您可以执行以下操作：
pg_dump -h <host ip address> -U <host db user name> -t <host table> > <host database> | psql -h localhost -d <local database> -U <local db user>
回答by max
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
回答by user5542464
To move a table from database A to database B at your local setup, use the following command:
要将表从数据库 A 移动到本地设置中的数据库 B，请使用以下命令：
pg_dump -h localhost -U owner-name -p 5432 -C -t table-name database1 | psql -U owner-name -h localhost -p 5432 database2
