迹忆客 专注技术分享

本教程将学习如何将托管在远程服务器上的数据库中的数据插入到我们本地计算机中的数据库中。我们将使用远程服务提供商 Heroku PostgreSQL，它为测试目的提供免费计划。

将远程服务器上托管的数据库中的数据插入本地计算机中的数据库

创建一个新应用并添加一个 PostgreSQL 数据存储。可以通过单击设置链接访问连接到远程服务器的凭据。

要连接远程数据库，请使用以下命令并确保你没有连接到公司网络，以避免通信失败导致超时错误。

>psql -h ec2-52-49-56-163.eu-west-1.compute.amazonaws.com -d dagbp3p323638g -U cvkmaeedgzlxdr

输出：

dagbp3p323638g=>

创建一个名为 remote_employee 的表，该表将保存包含员工信息的记录，例如 id、first name、last name 和 email。

dagbp3p323638g=> create table remote_employee(id SERIAL NOT NULL UNIQUE,first_name varchar(50),last_name varchar(50),email varchar(50),PRIMARY KEY(id));
CREATE TABLE

将三个记录添加到 remote_employee 表中，以确保我们有数据可以从远程服务器获取到本地服务器。

dagbp3p323638g=> insert into remote_employee(first_name,last_name,email)values ('john','doe','john@gmail.com');
INSERT 0 1

dagbp3p323638g=> insert into remote_employee(first_name,last_name,email)values ('peter','parker','peter@gmail.com');
INSERT 0 1

dagbp3p323638g=> insert into remote_employee(first_name,last_name,email)values ('mary','public','mary@gmail.com');
INSERT 0 1

以下查询验证我们的三个记录是否已插入数据库。

dagbp3p323638g=> select * from remote_employee;
 id | first_name | last_name |      email
----+------------+-----------+-----------------
  1 | john       | doe       | john@gmail.com
  2 | peter      | parker    | peter@gmail.com
  3 | mary       | public    | mary@gmail.com
(3 rows)

你必须连接到本地服务器并创建一个名为 local_database 的数据库，该数据库将包含要填充来自远程实体的数据的本地实体。

>psql -U postgres

postgres=# create database local_database;
CREATE DATABASE

postgres=# \c local_database;
You are now connected to database "local_database" as user "postgres".

创建一个名为 local_employee 的表，其中包含与 remote_employee 相同的列，因为它们包含相同的数据类型。

local_database=# create table local_employee(id SERIAL NOT NULL UNIQUE,first_name varchar(50),last_name varchar(50),email varchar(50),PRIMARY KEY(id));
CREATE TABLE

在 PostgreSQL 中使用 dblink 插入 Select

dblink 是一个扩展，它允许我们从其他数据库中查询数据，我们使用以下命令创建一个扩展。

local_database=# create extension dblink;

在从远程服务器获取数据之前，我们可以使用 dblink_connect 测试连接，如下所示。如果查询返回 OK，这表明我们的远程数据库已经连接成功，我们现在可以执行插入、更新和删除查询。

注意连接名称 temp_conn，我们将在插入查询中使用它。

local_database=# SELECT dblink_connect('temp_conn', 'dbname=dagbp3p323638g port=5432 host=ec2-52-49-56-163.eu-west-1.compute.amazonaws.com user=cvkmaeedgzlxdr password=336ea6e67129e8f082140f1b60954dafa33940f17b02e1f580ea45f10401f85e');
 dblink_connect
----------------
 OK
(1 row)

向我们的 local_employee 表插入数据的查询是由一个插入语句和一个对我们远程服务器的选择语句组成的。

通过指定连接名称 temp_conn 和 SQL 字符串，使用 dblink 执行 select 语句以获取数据。

local_database=# INSERT INTO local_employee
SELECT id,first_name,last_name,email FROM dblink('temp_conn','SELECT id, first_name, last_name, email FROM remote_employee') AS temp_employee(id integer,first_name varchar(50),last_name varchar(50),email varchar(50));
INSERT 0 3

remote_employee 中的所有记录都作为临时表 temp_employee 返回，并通过利用 dblink 连接成功插入到本地表 local_employee 中。

local_database=# select * from local_employee;

输出：

id | first_name | last_name |      email
----+------------+-----------+-----------------
  1 | john       | doe       | john@gmail.com
  2 | peter      | parker    | peter@gmail.com
  3 | mary       | public    | mary@gmail.com
(3 rows)