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

Transfer data between databases with PostgreSQL

提问by Nicopuri

I need to transfer some data from another database. The old database is called paw1.moviesDB and the new database is paw1. The schema of each table are the following.

我需要从另一个数据库传输一些数据。旧数据库名为 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

How do I copy the data from old database to the new database?

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

回答by Nate

I just had to do this exact thing so I figured I'd post the recipe here. This assumes that both databases are on the same server.

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

First, copy the table from the old db to the new db (because apparently you can't move data between databases). At the commandline:

首先，将表从旧数据库复制到新数据库（因为显然你不能在数据库之间移动数据）。在命令行：

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

# Just adding extra space here so scrollbar doesn't hide the command

Next, grant permissions of the copied table to the user of the new database. Log into psql:

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

psql -U postgres -d <new_database>

ALTER TABLE <old_table> OWNER TO <new_user>;

\q

Finally, copy data from the old table to the new table. Log in as the new user and then:

最后，将数据从旧表复制到新表。以新用户身份登录，然后：

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

Done!

完毕！

回答by leonbloy

Databases are isolated in PostgreSQL; when you connect to a PostgreSQL server you connect to just one database, you can't copy data from one database to another using a SQL query.

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

If you come from MySQL: what MySQL calls (loosely) "databases" are "schemas" in PostgreSQL - sort of namespaces. A PostgreSQL database can have many schemas, each one with its tables and views, and you can copy from one schema to another with the schema.tablesyntax.

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

If you really have two distinct PostgreSQL databases, the common way of transferring data from one to another would be to export your tables (with pg_dump -t) to a file, and import them into the other database (with psql).

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

If you really need to get data from a distinct PostgreSQL database, another option - mentioned in Grant Johnson's answer - is dblink, which is an additional module (in contrib/).

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

回答by crizCraig

This worked for me to copy a table remotely from my localhost to Heroku's postgresql:

这对我从我的本地主机远程复制一个表到 Heroku 的 postgresql 有用：

pg_dump -C -t source_table -h localhost source_db | psql -h destination_host -U destination_user -p destination_port destination_db

pg_dump -C -t source_table -h localhost source_db | psql -h destination_host -U destination_user -p destination_port destination_db

This creates the table for you.

这将为您创建表格。

For the other direction (from Heroku to local) pg_dump -C -t source_table -h source_host -U source_user -p source_port source_db | psql -h localhost destination_db

对于另一个方向（从 Heroku 到本地） pg_dump -C -t source_table -h source_host -U source_user -p source_port source_db | psql -h localhost destination_db

回答by Thiago Macedo

From: hxxp://dbaspot.c om/postgresql/348627-pg_dump-t-give-where-condition.html (NOTE: the link is now broken)

来自：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;

Another method useful in remotes

另一种在遥控器中有用的方法

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

回答by Grant Johnson

There are three options for copying it if this is a one off:

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

1. Use a db_link (I think it is still in contrib)
2. Have the application do the work.
3. Export/import

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

If this is an ongoing need, the answers are:

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

1. Change to schemas in the same DB
2. db_link

1. 更改为同一数据库中的架构
2. 数据库链接

回答by Anvesh

You can not perform a cross-database query like SQL Server; PostgreSQL does not support this.

您不能像 SQL Server 那样执行跨数据库查询；PostgreSQL 不支持这个。

The DbLink extension of PostgreSQL is used to connect one database to another database. You have install and configure DbLink to execute a cross-database query.

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

I have already created a step-by-step script and example for executing cross database query in PostgreSQL. Please visit this post: PostgreSQL [Video]: Cross Database Queries using the DbLink Extension

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

回答by francisco

Actually, there is some possibility to send a table data from one PostgreSQL database to another. I use the procedural language plperlu (unsafe Perl procedural language) for it.

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

Description (all was done on a Linux server):

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

1. Create plperlu language in your database A

2. Then PostgreSQL can join some Perl modules through series of the following commands at the end of postgresql.conf for the database A:

   plperl.on_init='use DBI;'
   plperl.on_init='use DBD::Pg;'
   

3. You build a function in A like this:

   CREATE OR REPLACE FUNCTION send_data( VARCHAR )
   RETURNS character varying AS
   $BODY$
   my $command = $_[0] || die 'No SQL command!';
   my $connection_string =
   "dbi:Pg:dbname=your_dbase;host=192.168.1.2;port=5432;";
   $dbh = DBI->connect($connection_string,'user','pass',
   {AutoCommit=>0,RaiseError=>1,PrintError=>1,pg_enable_utf8=>1,}
   );
   my $sql = $dbh-> prepare( $command );
   eval { $sql-> execute() };
   my $error = $dbh-> state;
   $sql-> finish;
   if ( $error ) { $dbh-> rollback() } else {  $dbh-> commit() }
   $dbh-> disconnect();
   $BODY$
   LANGUAGE plperlu VOLATILE;
   

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

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

   plperl.on_init='use DBI;'
   plperl.on_init='use DBD::Pg;'
   

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

   CREATE OR REPLACE FUNCTION send_data( VARCHAR )
   RETURNS character varying AS
   $BODY$
   my $command = $_[0] || die 'No SQL command!';
   my $connection_string =
   "dbi:Pg:dbname=your_dbase;host=192.168.1.2;port=5432;";
   $dbh = DBI->connect($connection_string,'user','pass',
   {AutoCommit=>0,RaiseError=>1,PrintError=>1,pg_enable_utf8=>1,}
   );
   my $sql = $dbh-> prepare( $command );
   eval { $sql-> execute() };
   my $error = $dbh-> state;
   $sql-> finish;
   if ( $error ) { $dbh-> rollback() } else {  $dbh-> commit() }
   $dbh-> disconnect();
   $BODY$
   LANGUAGE plperlu VOLATILE;
   

And then you can call the function inside database A:

然后你可以调用数据库 A 中的函数：

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

And the value "zzzzzz" will be added into table "jm" in database B.

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

回答by Federico Cristina

Just like leonbloy suggested, using two schemas in a database is the way to go. Suppose a sourceschema (old DB) and a targetschema (new DB), you can try something like this (you should consider column names, types, etc.):

就像 leonbloy 建议的那样，在数据库中使用两个模式是可行的方法。假设一个源模式（旧数据库）和一个目标模式（新数据库），你可以尝试这样的事情（你应该考虑列名、类型等）：

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