How to fix HV00R: fdw_table_not_found in PostgreSQL

-- Show detailed foreign table information:
\d+ schema_name.foreign_table_name

-- Query system catalogs for FDW table mapping:
SELECT ft.ftoptions, s.srvname
FROM pg_foreign_table ft
JOIN pg_foreign_server s ON ft.ftserver = s.oid
WHERE ft.ftrelid = 'schema_name.foreign_table_name'::regclass;

-- For postgres_fdw connecting to another PostgreSQL instance:
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname = 'remote_schema'
  AND tablename = 'remote_table';

-- Check for case sensitivity issues:
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname ILIKE 'remote_schema'
  AND tablename ILIKE 'remote_table';

-- For other FDWs, use appropriate remote catalog queries
-- For MySQL FDW:
SHOW TABLES FROM remote_database LIKE 'remote_table';

-- Drop and recreate the foreign table with correct remote table name:
DROP FOREIGN TABLE IF EXISTS schema_name.foreign_table_name;

CREATE FOREIGN TABLE schema_name.foreign_table_name (
  -- column definitions
)
SERVER remote_server
OPTIONS (table_name 'correct_remote_table');

-- Or use IMPORT FOREIGN SCHEMA to recreate multiple tables:
IMPORT FOREIGN SCHEMA remote_schema
  LIMIT TO (correct_remote_table)
  FROM SERVER remote_server
  INTO schema_name;

-- Test the FDW connection directly:
SELECT * FROM dblink(
  'dbname=remote_db host=remote_host user=fdw_user',
  'SELECT 1'
) AS t(test int);

-- For postgres_fdw, check user mapping:
SELECT * FROM pg_user_mappings
WHERE srvname = 'remote_server';

-- Ensure the remote user has SELECT permission on the table:
-- On remote server:
GRANT SELECT ON remote_schema.remote_table TO fdw_user;

-- For postgres_fdw, refresh the foreign table cache:
ALTER FOREIGN TABLE schema_name.foreign_table_name
  OPTIONS (ADD refresh 'true');

-- Or disconnect and reconnect to force cache refresh:
-- Restart the PostgreSQL service or
-- Execute for each foreign table:
NOTIFY pg_fdw_refresh_cache;

-- Check if there are any pending schema changes:
SELECT * FROM pg_foreign_table
WHERE ftrelid = 'schema_name.foreign_table_name'::regclass;