PostgreSQL Foreign Table Error Relation Doesn't Exist

Question

I've created a foreign table urltrackerft

In my foreign server util it has table urltracker:

I've followed the steps in creating the foreign server and table here: https://www.postgresql.org/docs/current/postgres-fdw.html

My create script looks something like this:

CREATE FOREIGN TABLE urltrackerft (
    id numeric NOT NULL,
    ...
) SERVER util
OPTIONS (schema_name 'util', table_name 'urltracker');

But when I try to read the urltrackerft table I get this error message:

To me it looks like perhaps the Foreign server isn't exactly synced.

Checking the user mappings, the info looks correct, could it be the host isn't an IP and is a DNS?

Perhaps the firewall or the hba_conf is incorrect for one of the server. But I'm seeing the foreign server util in the primary server. I'm a bit lost on this one. Any help is appreciated, thanks!

The primary server is on PostgreSQL v10, and the foreign server is on PostgreSQL v11.

Answer 1

Your foreign table seems to be in the schema public of database util, if I read your colorful pictures right.

Then it is unsurprising that the following foreign table definition will not work:

OPTIONS (schema_name 'util', table_name 'urltracker')

Use schema_name 'public' instead.