[PostgreSQL] Foreign Tables during the Migration fails in import-schema for server not exist

[priyanshi-yb]

Jira Link: DB-13443
If your source database has FOREIGN tables, then right now Voyager doesn't dump SERVER of that FOREIGN table.

Example -

CREATE EXTENSION postgres_fdw;

CREATE SERVER remote_server                                                                                                                                                       
FOREIGN DATA WRAPPER postgres_fdw                                                                                                                                                         
OPTIONS (host '127.0.0.1', port '5432', dbname 'postgres');

CREATE FOREIGN TABLE foreign_table (                                                                                                                                                  
   id INT,                                                                                                                                                                                       
   name TEXT,                                                                                                                                                                                    
   data JSONB                                                                                                                                                                                
) SERVER remote_server                                                                                                                                                                          OPTIONS (
schema_name 'public', 
table_name 'remote_table'
);

CREATE USER MAPPING FOR postgres                                                                                                                                                
SERVER remote_server                                                                                                                                                                        
OPTIONS (user 'postgres', password 'XXX');

The exported schema will have only -

CREATE FOREIGN TABLE public.foreign_table (                                                                                                                                                  
   id INT,                                                                                                                                                                                       
   name TEXT,                                                                                                                                                                                    
   data JSONB                                                                                                                                                                                
) SERVER remote_server                                                                                                                                                                          OPTIONS (
schema_name 'public', 
table_name 'remote_table'
);

Error in import schema phase -

ERROR: server "remote_server" does not exist (SQLSTATE 42704)

Workaround -

Suggestion is to create the SERVER and its USER MAPPING manually on the target YugabyteDB to get it working.