使用dblink.sql在PostgreSQL中的两个表之间复制数据 [英] Copy data between two tables in PostgreSQL using dblink.sql

问题描述

我使用PostgreSQL 9.1。我需要从一个数据库的一个表转移必需的列到另一个数据库的另一个表，而不是模式。

我发现 dblink.sql 文件必须在 share / contrib 中。但我的 contrib 文件夹是空的。在哪里可以下载 dblink.sql 文件，并可以执行我的查询？

I am using PostgreSQL 9.1. I need to transfer required columns from one table of one database into another table of another database, but not schema.
I found that dblink.sql file has to be there in share/contrib. But my contrib folder is empty. Where can I download the dblink.sql file and can execute my query?

当我执行查询时，它显示一条错误信息：

When I execute the query now it shows an error message:

cross database reference is not possible ...

任何人都可以帮助我如何在两个数据库之间传输数据数据库？

Can anyone help me how to transfer the data between two databases?

推荐答案

将软件包安装到系统后，如相关问题中所述安装扩展 dblink 到您的数据库中（您正在运行此代码，外部数据库不需要它）：

After you have installed the package into your system as detailed in the related question install the extension dblink into your database (the one you are running this code in, the foreign db does not need it):

CREATE EXTENSION dblink;

您可以找到在手册中编写示例。

这是一个简单的版本，用于在dbs之间复制数据：
首先，创建外部服务器

CREATE SERVER mydb
FOREIGN DATA WRAPPER postgresql
OPTIONS (hostaddr '111.111.111.111',port '5432',dbname 'mydb');

预先安装FOREIGN DATA WRAPPER postgresql

然后创建打开连接，删除旧数据（可选），提取新数据，运行 ANALYZE 并关闭连接的函数：

FOREIGN DATA WRAPPER postgresql was pre-installed in my case.
Then create function that opens a connection, removes old data (opotional), fetches new data, runs ANALYZE and closes the connection:

CREATE OR REPLACE FUNCTION f_tbl_sync()
  RETURNS text AS
$BODY$
SELECT dblink_connect('mydb');  -- USER MAPPING for postgres, PW in .pgpass

TRUNCATE tbl;  -- optional

INSERT INTO tbl
SELECT * FROM dblink(
  'SELECT tbl_id, x, y
   FROM   tbl
   ORDER  BY tbl_id')
    AS b(
 tbl_id int
,x int
,y int)

ANALYZE tbl;

SELECT dblink_disconnect();
$BODY$
  LANGUAGE sql VOLATILE;

这篇关于使用dblink.sql在PostgreSQL中的两个表之间复制数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！