Postgres fdw server relation doesn't exist

Question

I am trying to create a link between postgres databases on my local, using FDW. Here is my code to create this link:

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER IF NOT EXISTS TEST_SERVER FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', dbname 'TestDatabase', port '5432');

CREATE USER MAPPING IF NOT EXISTS FOR postgres SERVER TEST_SERVER OPTIONS (user 'postgres', password 'postgres');

CREATE FOREIGN TABLE IF NOT EXISTS TEST_PUBLIC.TEST_TABLE(
                ID INT NOT NULL,
                VALUE VARCHAR(11) NOT NULL
) SERVER TEST_SERVER;    

TEST table exists on TestDatabase, and test_public schema exists on the current database I'm creating the FDW on. When I originally created this and tested it I could see the objects with no issues, however part of my development process (for this specific project) is to drop all objects in the current and rerun from the ground up so when we move this to a new environment we know from start to finish everything is solid.

The problem I am having is I can no longer see the foreign table "TEST_TABLE".

When I run this:

SELECT * FROM information_schema.tables WHERE table_schema = 'test_public'

I get the following returned:

table_catalog table_schema  table_name  table_type
TestDatabase  test_public   test_table  FOREIGN

But when I select from the table test_public.test_table I get the following Error:

relation "test_public.test_table" does not exist 

I was able to figure out all of my woes along the way, but even after running this step by step instead of a full deployment I can't seem to access the foreign table anymore. Any suggestions as to what I am missing?

Answer 1

You need to use OPTIONS clause to map the source table to the foreign table (see details).

In your case it should be something similar to (assuming the source table is named test in public schema):

create foreign table test_public.test_table(
id int not null, 
value varchar(11) not null) 
server test_server 
options(schema_name 'public', table_name 'test');

Here is an adapted example from your code using also a local instance.

Here is the source code:

create database source_db;
\c source_db 
create table source_table(c int); 
insert into source_table values(12);
--
\c postgres
create schema target_schema;
create server target_server foreign data wrapper postgres_fdw
options (host '127.0.0.1', dbname 'source_db', port '5431');
create user mapping for postgres server target_server 
options (user 'postgres', password 'postgres'); 
create foreign table target_schema.target_table(c int) server target_server 
options(schema_name 'public', table_name 'source_table');
select * from target_schema.target_table;

Here is the execution:

create database source_db;
CREATE DATABASE

You are now connected to database "source_db" as user "postgres".

create table source_table(c int);
CREATE TABLE

insert into source_table values(12);
INSERT 0 1

You are now connected to database "postgres" as user "postgres".

create schema target_schema;
CREATE SCHEMA

create server target_server foreign data wrapper postgres_fdw
options (host '127.0.0.1', dbname 'source_db', port '5431');
CREATE SERVER

create user mapping for postgres server target_server 
options (user 'postgres', password 'postgres');
CREATE USER MAPPING

create foreign table target_schema.target_table(c int) server target_server 
options(schema_name 'public', table_name 'source_table');
CREATE FOREIGN TABLE

select * from target_schema.target_table;
 c  
----
 12
(1 row)