Postgres Foreign Table Example

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.

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:

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:

Once this is completed, we will need a sample table called users with some random data on database_2 located in postgres_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.

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

Putting It All Together

Script for Remote Server (postgres_2)

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)

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

• 9^th May, 2020: Initial version