Search code examples
postgresqlpostgres-fdw

PostgreSQL Foreign Table Error Relation Doesn't Exist

I've created a foreign table urltrackerft

In my foreign server util it has table urltracker:

enter image description here

enter image description here

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: enter image description here

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? enter image description here

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.

Solution

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