PostgreSQL - How to import custom data type when creating foreign table (using postgres-fdw)?

Question

I'm trying to create foreign table view using postgresql_fdw (https://www.postgresql.org/docs/current/postgres-fdw.html).

When trying to IMPORT FOREIGN SCHEMA public FROM SERVER replica_db1 INTO db1, it reports

type "public.custom_type" does not exist

same as in https://www.postgresql.org/docs/current/postgres-fdw.html

I want to know, how can I automatically copy custom data type into target db?

Thanks!

Answer 1

The documentation tells you:

If the remote tables to be imported have columns of user-defined data types, the local server must have compatible types of the same names.

So make sure that the local database has a type of the same name, and it had better be similar too (at least have the same text representation).

If you want functions and operators on that type to be pushed down, you'll have to put them into an extension that you install in both databases.

Then specify that extension in the extension option of the foreign server.