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

Question

我试图从一个Postgres数据库复制整个表至另一个数据库，有什么建议吗？

- nix

---

如果您愿意安装DBeaver，它有一种非常简单的方法可以在您连接的两个数据库之间进行转移。只需右键单击源表并选择“导出数据”，然后将目标设置为目标数据库中的表，并将其设置为目标即可。 - rovyko

@rovyko 我正在尝试在DBeaver中使用动态SQL完成相同的事情。如果您知道如何做到这一点，请告诉我。 - Roshan Maharjan

Answer 1

提取表格并直接将其导入目标数据库：

pg_dump -t table_to_copy source_db | psql target_db

注意：如果另一个数据库已经设置好了表格，您应该使用-a标志仅导入数据，否则您可能会看到奇怪的错误，例如“内存不足”：

pg_dump -a -t table_to_copy source_db | psql target_db

- thomax

---

远程数据库链接将如何工作？例如，我需要从不同的位置进行转储。 - curlyreggie

@curlyreggie 我没有尝试过这个方法，但我认为它应该是可行的。可以尝试将用户和服务器的具体信息添加到命令中，例如 pg_dump -U remote_user -h remote_server -t table_to_copy source_db | psql target_db。 - thomax

你可以尝试这样做："pg_dump -U 远程用户名 -h 远程服务器地址 -t 需要复制的表 源数据库名称 | psql 目标数据库名称 -U 远程用户名 -h 远程服务器地址" - Hua Zhang

请注意，如果另一个数据库已经设置了该表，请使用“-a”标志进行_仅数据_的转储。例如，pg_dump -a -t my_table my_db | psql target_db。此外，如果您的数据库位于服务器上，我建议将数据库转储到文件中，然后将该文件传输到数据库，最后将文件内容发送到psql。例如，pg_dump -a -t my_table my_db > my_file.sql，然后将文件传输到您的服务器，最后使用以下命令将文件内容发送到另一个数据库：psql my_other_db < my_file.sql。 - Nick Brady

要转储一个区分大小写的表，请执行以下操作： pg_dump -t '"tableToCopy"' source_db | psql target_db。 请注意，表名用单引号和双引号括起来。 - gilad905

Answer 2

你还可以使用pgAdmin II中的备份功能。只需按照以下步骤操作：

• 在pgAdmin中，右键单击要移动的表，选择“备份”
• 选择输出文件的目录并将格式设置为“plain”
• 点击“Dump Options #1”选项卡，勾选“Only data”或“only Schema”（取决于您正在进行的操作）
• 在查询部分下，点击“使用列插入”和“用户插入命令”。
• 点击“备份”按钮。这将输出为一个 .backup 文件
• 使用记事本打开这个新文件。您将看到所需的表/数据的插入脚本。将其复制并粘贴到pgAdmin中的新数据库SQL页面中。以pgScript方式运行 - 查询->执行为pgScript F6

这种方法效果很好，而且可以同时处理多个表格。

- a2ron44

---

这是一个很好的基于图形用户界面的解决方案，可用于在数据库之间传输数据。谢谢！ - kgx

在“对象”部分下，您可以选择多个表格。在OSX上，点击SQL按钮或通过“工具”菜单获取“SQL编辑器”以粘贴从备份文件中复制的SQL语句。 - Aleck Landgraf

工作正常，谢谢。但是在大表上非常缓慢...有没有更好的方法来加速它？（比如忽略外键或其他什么？） - TimoSolo

这是关于如何加快备份和恢复的Postgres文档页面，Timothy。请您核对。 - laurie

旧答案但仍然相关，非常有效，只是在导出整个数据库时不要忘记设置禁用触发器。 - norbertas.gaulia

Answer 3

使用 dblink 会更方便！

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);

- tinychen

---

为什么有两个不同的数据库名称出现了两次？哪一个是源数据库，哪一个是目标数据库？ - arulraj.net

我们要插入的tableA是目标表，而dbLink中的tableA是源表。 - aggietech

如果我想使用 dblink，但我不知道源表的结构怎么办？ - Ossarotte

@Ossarotte，嘿，你找到你问题的答案了吗？ - Ibrahim Noor

Answer 4

在具有与两个服务器的连接性的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" )

- Alexey Sviridov

---

不需要导出，只需使用PGPASSWORD=password1 psql -U ...即可，这样甚至不需要显式的子shell！通常情况下，您可能需要先进行一些设置，因此子shell仍然是必要的。此外，密码不会被导出到后续进程中。谢谢！ - lmat - Reinstate Monica

@LimitedAtonement 实际上你说得对，导出和子shell都是不必要的。这只是更复杂脚本的一部分，而且我甚至没有尝试过不使用导出和子shell，所以我提供它原样只是为了诚实地提供可行的解决方案。 - Alexey Sviridov

目标数据库中必须存在该表。要创建它，请尝试使用pg_dump -t '<table_name>' --schema-only命令。 - fjsj

将密码放入~/.pgpass文件中。 - Hans Ginzel

Answer 5

如果您拥有两个远程服务器，则可以按照以下步骤进行操作：

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

如果您已经有一个现有架构，它将把源数据库中提到的表复制到目标数据库的同名表中。

- Piyush S. Wanare

Answer 6

首先安装dblink 然后，你可以这样做：

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 
);

- user3946530

---

这个答案很好，因为它允许我们过滤复制的行（在dblink的第二个参数中添加WHERE子句）。然而，在Postgres 9.4中，我们需要明确列名，例如：INSERT INTO l_tbl (l_col1, l_col2, l_col3) SELECT * FROM dblink('dbname=r_db hostaddr=r_ip password=r_pass user=r_usr', 'select r_col1, r_col2, r_col3 from r_tbl where r_col1 between ''2015-10-29'' AND ''2015-10-30'' ') AS t1(col1 MACADDR, col2 TIMESTAMP, col3 NUMERIC(7,1)); （l表示本地，r表示远程。转义单引号。提供列类型。） - hamx0r

Answer 7

使用pg_dump命令导出表数据，然后使用psql命令进行恢复。

- Pablo Santa Cruz

---

然后使用另一个具有足够权限的数据库角色进行连接。http://www.postgresql.org/docs/8.4/static/app-pgdump.html - Frank Heikens

我做错了什么吗？pg_dump -t "tablename" dbName --role "postgres" > db.sql“postgres”是我想要设置角色的用户。 但是它仍然给我“拒绝访问”的错误信息。 - nix

你有写入db.sql文件的权限吗？ - pcent

我该如何检查我拥有哪些权限？ - nix

这个线程很旧了，但是对于其他遇到这个问题的人，请尝试在PgAdminIII中使用“工具-->备份”菜单，这似乎可以解决权限问题。 - John

不是一个有用的答案，因为其他答案展示了如何使用pg_dump。 - scottlittle

Answer 8

以下是我的解决方案。首先将其转储到文件中：

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

然后加载转储文件：

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

- max

---

对于转储负载，还需要使用“-h localhost”。 - DTukans

我在加载过程中遇到了一个错误“ERROR: syntax error at or near "ÿ_" --LINE 1: ÿ_-”。解决方法是不使用PowerShell，而是使用cmd。 - fredtantini

Answer 9

您可以执行以下操作：

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>

---

- Gowtham Balusamy

---

你想对此发表一些看法吗？ - Muhammad Omer Aslam

这是合法的，你欠我。 - Muhammad Omer Aslam

Answer 10

如果您想将数据库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

- RKT

---

我尝试过了。这不起作用，因为你只能提供第一个密码。 - max

@max 在运行命令之前可以执行 export PGPASSWORD=<passw>。 - lukaszzenko