JIYIK CN >

In this tutorial, we will learn how to insert data from a database hosted on a remote server into a database in our local computer. We will use the remote service provider Heroku PostgreSQL, which offers a free plan for testing purposes.

Insert data from a database hosted on a remote server into a database in your local machine

Create a new application and add a PostgreSQL datastore. You can 设置access the credentials to connect to the remote server by clicking the link.

To connect to the remote database, use the following command and make sure you are not connected to the corporate network to avoid timeout errors due to communication failures.

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

Output:

dagbp3p323638g=>

Create a remote_employeetable named that will hold records containing employee information such as id, first name, last nameand 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

Add three records to remote_employeethe table to ensure that we have data to fetch from the remote server to the local server.

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

The following query verifies that our three records have been inserted into the database.

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)

You must connect to the local server and create a local_databasedatabase called that will contain the local entities to be populated with data from the remote entities.

>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".

Create a local_employeetable named that contains remote_employeethe same columns as , because they contain the same data types.

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

dblinkInsert in PostgreSQL usingSelect

dblinkis an extension which allows us to query data from other databases. We create an extension using the following command.

local_database=# create extension dblink;

Before getting data from the remote server, we can dblink_connecttest the connection using as shown below. If the query returns OK, this indicates that our remote database has been connected successfully and we can now execute insert, update, and delete queries.

Note the connection name temp_conn, we will use it in our insert query.

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)

The query that inserts data into our local_employeetable consists of an insert statement and a select statement to our remote server.

Use to execute a select statement to obtain data by specifying the connection name temp_connand SQL string .dblink

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_employeeAll records in temp_employeeare returned as a temporary table and dblinkare successfully inserted into the local table by utilizing the connection local_employee.

local_database=# select * from local_employee;

Output:

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)