Click here to Skip to main content
15,119,116 members
Articles / Database Development / PostgreSQL
Tip/Trick
Posted 9 May 2020

Tagged as

Stats

8.9K views
2 bookmarked

Postgres Foreign Table Example

Rate me:
Please Sign up or sign in to vote.
4.67/5 (3 votes)
9 May 2020CPOL2 min read
Snippet to get started with foreign tables
This is a step by step guide to configure and use foreign tables for the first time.

Introduction

I have been working with Postgres for over 10 years and I must say that I am very satisfied with its performance, reliability and features that make developers' lives easier.

One of those features I love is the support for Foreign tables, a very useful feature that had been there for a few years before I found it a few months ago when I was surfing the web.

Background

Postgres official documentation describes how to create a foreign table but it doesn't show you how to make it work step by step, so I decided to write this post here.

Environment Configuration

For this example, we need two Postgres servers. I am going to use Docker to create them in my PC so I can get rid of them easily once I finish this post.

This step is completely optional since I am going to assume that you already have the servers, but if you are curious and have never worked with it, feel free to go here and here.

You should be able to create the servers with a terminal and these two commands, one for each server.

C++
docker run --name postgres_1 -e POSTGRES_PASSWORD=postgres_1_pw -d postgres
docker run --name postgres_2 -e POSTGRES_PASSWORD=postgres_2_pw -d postgres

You know everything went well if you run docker ps and you see something like:

C++
CONTAINER ID  IMAGE     COMMAND                CREATED       STATUS        PORTS     NAMES
65b706348544  postgres  "docker-entrypoint.s…" 2 minutes ago Up 2 minutes  5432/tcp  postgres_2
16b1dcfd09fb  postgres  "docker-entrypoint.s…" 3 minutes ago Up 3 minutes  5432/tcp  postgres_1

Database Setup

Before we get our hands on the foreign tables, we will need a very basic structure in both servers illustrated below:

Image 1

Once this is completed, we will need a sample table called users with some random data on database_2 located in postgres_2.

Image 2

To read and write the users table from server 1 (postgres_1), we need to use the postgres_fdw extension that will allow us to access data from remote tables, create an origin source of data and, of course, we will need some credentials to access the data in the remote server.

Image 3

Once you have imported the users table from postgres_2 into postgres_1, the users table should be accessible for read or write operations.

Image 4

Putting It All Together

Script for Remote Server (postgres_2)

SQL
CREATE TABLE users
(
    id serial primary key,
    name character varying NOT NULL,
    email character varying NOT NULL UNIQUE,
    bio text
);

INSERT INTO users (name, email, bio) VALUES
('Angelika Bartlett', 'angelika.bartlett@example.com', 'Lorem ipsum dolor sit amet, 
  consectetur adipisicing elit'),
('Roger Scott', 'roger.scott@example.com', 
 'sed do eiusmod tempor incididunt ut labore et dolore magna aliqua'),
('Malia Murray', 'malia.murray@example.com', 'Ut enim ad minim veniam, 
  quis nostrud exercitation ullamco laboris');

Script for Local Server (postgres_1)

SQL
CREATE EXTENSION postgres_fdw;

CREATE SERVER postgres_2
 FOREIGN DATA WRAPPER postgres_fdw
 OPTIONS (dbname 'database_2', host 'postgres_2', port '5432');

CREATE USER MAPPING FOR CURRENT_USER
  SERVER postgres_2
  OPTIONS (user 'postgres', password 'postgres_2_pw');

IMPORT FOREIGN SCHEMA "public" limit to (users) FROM SERVER postgres_2 INTO public;

SELECT *
FROM users;

UPDATE users
SET name = 'Ing. Malia Murray'
WHERE id = 3;

DELETE
FROM users
WHERE id = 3;

Conclusion

I hope this snippet can save a few minutes if you ever need to work with Postgres and foreign tables.

Thanks for reading!

History

  • 9th May, 2020: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Carlos Luis Rojas Aragonés
Chief Technology Officer https://golabstech.com
Costa Rica Costa Rica
CTO and Co-founder of Golabs, a software development company located in Costa Rica. I am very passionate about my job and I really enjoy building things with software and make things happening. I am also a professor at Universidad Técnica Nacional because I believe that knowledge should be shared, I really enjoy seeing people grow from students at the university to great professionals and entrepreneurs.

Comments and Discussions

 
QuestionWell explained, my vote of 5 Pin
Luis Perez Garcia12-May-20 2:25
MemberLuis Perez Garcia12-May-20 2:25 
AnswerRe: Well explained, my vote of 5 Pin
Carlos Luis Rojas Aragonés12-May-20 7:49
professionalCarlos Luis Rojas Aragonés12-May-20 7:49 
Thanks!

I haven't noticed any performance issues so far, but I haven't really tested this on any big database with Terabytes of data.

It would be a good idea to benchmark it in a future article.
SuggestionPostgreSQL portable Pin
RickZeeland9-May-20 10:45
mveRickZeeland9-May-20 10:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.