使用 PostgreSQL 在数据库之间传输数据

Question

我需要从另一个数据库传输一些数据。旧数据库名为 paw1.movi​​esDB，新数据库名为 paw1。每个表的架构如下。

Awards (name of the table)(new DB)
Id [PK] Serial           Award

Nominations (name of the table) (old DB)
Id [PK] Serial           nominations

如何将旧数据库中的数据复制到新数据库中？

Answer 1

我只需要做这件事，所以我想我应该把食谱贴在这里。这假设两个数据库位于同一服务器上。

首先，将表从旧数据库复制到新数据库。在命令行：

pg_dump -U postgres -t <old_table> <old_database> | psql -U postgres -d <new_database>

接下来，将复制表的权限授予新数据库的用户。登录 psql：

psql -U postgres -d <new_database>

ALTER TABLE <old_table> OWNER TO <new_user>;

\q

此时，新数据库中复制的表仍具有旧数据库中的名称 。假设您想将数据移动到其他地方，例如 ，您可以只使用常规 SQL 查询：

INSERT INTO <new_table> (field1, field2, field3) 
SELECT field1, field2, field3 from <old_table>;

完成！

Answer 2

PostgreSQL 中的数据库是隔离的；当您连接到 PostgreSQL 服务器时，您仅连接到一个数据库，您无法使用 SQL 查询将数据从一个数据库复制到另一个数据库。

如果您来自 MySQL：MySQL（宽松地）所谓的“数据库”是“schemas" - 某种名称空间。 PostgreSQL 数据库可以有许多模式，每个模式都有自己的表和视图，您可以使用 schema.table 语法从一种模式复制到另一种模式。

如果您确实有两个不同的 PostgreSQL 数据库，则将数据从一个数据库传输到另一个数据库的常见方法是导出表（使用 pg_dump -t ）到文件，并将它们导入到另一个数据库（使用 psql）。

如果您确实需要从不同的 PostgreSQL 数据库获取数据，Grant Johnson 的回答中提到的另一个选项是 dblink，这是一个附加模块（在 contrib/ 中）。

更新：

Postgres 在 9.1 中引入了“外部数据包装器”（在提出问题后发布）。外部数据包装器允许通过 外部表 "https://www.postgresql.org/docs/current/postgres-fdw.html" rel="noreferrer">Postgres FDW 可以访问远程表（在不同的服务器和数据库上）就好像它是本地表一样。

Answer 3

这对我有用，可以将表从本地主机远程复制到 Heroku 的 postgresql：

pg_dump -C -t source_table -h localhost source_db | psql -h 目的地主机 -U 目的地用户 -p 目的地端口 目的地数据库

这会为您创建表。

另一个方向（从 Heroku 到本地）
pg_dump -C -t 源表 -h 源主机 -U 源用户 -p 源端口 源数据库 | psql -h 本地主机目的地数据库

Answer 4

来自：hxxp://dbaspot.com/postgresql/348627-pg_dump-t-give-where-condition.html（注意：链接现已损坏）

# create temp table with the data
psql mydb
CREATE TABLE temp1 (LIKE mytable);
INSERT INTO temp1 SELECT * FROM mytable WHERE myconditions;
\q

# export the data to a sql file
pg_dump --data-only --column-inserts -t temp1 mtdb > out.sql
psql mydb
DROP TABLE temp1;
\q

# import temp1 rows in another database
cat out.sql | psql -d [other_db]
psql other_db
INSERT INTO mytable (SELECT * FROM temp1);
DROP TABLE temp1;

另一种在远程中有用的方法

  # export a table csv and import in another database
  psql-remote> COPY elements TO '/tmp/elements.csv' DELIMITER ',' CSV HEADER;
  $ scp host.com:/tmp/elements.csv /tmp/elements.csv
  psql-local> COPY elements FROM '/tmp/elements.csv' DELIMITER ',' CSV;

Answer 5

如果这是一次性的，可以使用三个选项来复制它：

1. 使用 db_link （我认为它仍在 contrib 中）
2. 让应用程序完成工作。
3. 导出/导入

如果这是持续的需求，答案是：

1. 更改同一数据库中的模式
2. db_link

Answer 6

1. 如果您的源数据库和目标数据库驻留在同一本地计算机上，您可以使用：

注意：- Sourcedb 已存在于您的数据库中。

CREATE DATABASE targetdb WITH TEMPLATE sourcedb;

此语句将 sourcedb 复制到 targetdb。

2. 如果您的源数据库和目标数据库驻留在不同的服务器上，您可以使用以下步骤：

第 1 步：- 将源数据库转储到文件中。

pg_dump -U postgres -O sourcedb sourcedb.sql

注意：- 这里 postgres 是用户名，因此请相应地更改名称。

第 2 步：- 将转储文件复制到远程服务器。

第 3 步：- 在远程服务器中创建新数据库

CREATE DATABASE targetdb;

第 4 步：- 在远程服务器上恢复转储文件

psql -U postgres -d targetdb -f sourcedb.sql

（pg_dump 是一个独立的应用程序（即，你在 shell/命令行中运行的东西，而不是 Postgres/SQL 命令。）

这应该可以做到。

Answer 7

不能像SQL Server那样进行跨数据库查询； PostgreSQL 不支持这一点。

PostgreSQL 的 DbLink 扩展用于将一个数据库连接到另一个数据库。您已安装并配置 DbLink 来执行跨数据库查询。

我已经创建了一个用于在 PostgreSQL 中执行跨数据库查询的分步脚本和示例。请访问这个
帖子：PostgreSQL [视频]：跨数据库查询使用 DbLink 扩展

Answer 8

实际上，有可能将表数据从一个 PostgreSQL 数据库发送到另一个数据库。我使用过程语言 plperlu（不安全的 Perl 过程语言）来实现它。

描述（全部在 Linux 服务器上完成）：

1. 在数据库中创建 plperlu 语言 A

2. 然后 PostgreSQL 可以通过以下一系列命令加入一些 Perl 模块：数据库 A 的 postgresql.conf 结尾：

   plperl.on_init='使用 DBI;'
   plperl.on_init='使用 DBD::Pg;'
   

3. 你在 A 中构建一个函数，如下所示：

   创建或替换函数 send_data( VARCHAR )
   返回字符随 AS 变化
   $身体$
   我的 $command = $_[0] || die '没有 SQL 命令！';
   我的 $connection_string =
   “dbi:Pg:dbname=your_dbase;主机=192.168.1.2;端口=5432;”;
   $dbh = DBI->connect($connection_string,'用户','通行证',
   {自动提交=>0，RaiseError=>1，PrintError=>1，pg_enable_utf8=>1，}
   ）；
   我的 $sql = $dbh->准备（$命令）；
   评估{ $sql->执行（） };
   我的 $error = $dbh->状态;
   $sql->结束;
   if ( $error ) { $dbh->; rollback() } else { $dbh->;犯罪（） }
   $dbh->断开（）;
   $身体$
   语言 plperlu VOLATILE;
   

然后您可以在数据库 A 中调用该函数：

SELECT send_data( 'INSERT INTO jm (jm) VALUES (''zzzzzz'')' );

并且值“zzzzzz”将被添加到数据库 B 中的表“jm”中。

Answer 9

使用 dbeaver，传输数据是小菜一碟。
执行步骤

1. 选择您想要传输数据的所有表。
   免责声明：如果我们单击其中所有表格的表格文件夹
   r 目前它不起作用。

2. 右键单击并选择导入。

3. 选择
   “导入源”中的表：
   

4. 在“选择输入对象”中
   从您想要获取对应数据的位置选择数据库源
   每个表名称。

5. 单击“下一步”并继续。

我接受问候。

Answer 10

我认为 PostgreSQL 服务器管理员可以限制 pg_dump 实用程序的使用。

因此，我使用 \copy 到元命令导出到 CSV 并导入到目标数据库。

Answer 11

就像 leonbloy 所建议的那样，在数据库中使用两个模式是正确的方法。假设有一个源架构（旧数据库）和一个目标架构（新数据库），您可以尝试这样的操作（您应该考虑列名称、类型等）：

INSERT INTO target.Awards SELECT * FROM source.Nominations;