I have a few foreign data wrappers set up from my main PostgreSQL database to other databases. Is there a table I can query to list all of the FDWs? Unfortunately select * from information_schema.schemata doesn't give any hints about which schemas are foreign and which are local.
-
1There is no such thing as a foreign schema. Only schemas which contain (at least one? Exclusively?) foreign tables. And how would you turn a "foreign" schema into an "active" fdw?jjanes– jjanes2020-07-28 18:53:09 +00:00Commented Jul 28, 2020 at 18:53
-
@jjanes "IMPORT FOREIGN SCHEMA my_schema FROM SERVER my_server INTO my_server_my_schema;" I assumed that postgres was representing the entire schema as foreign somehow, but it seems like the "IMPORT FOREIGN SCHEMA" is just a shortcut to make multiple pg_foreign_table entries.rcv– rcv2020-07-28 21:33:19 +00:00Commented Jul 28, 2020 at 21:33
Add a comment
|
1 Answer
The information you're looking for is in various pg_catalog tables. pg_class is the main table that represents all relations, normal or foreign; you can limit it to foreign tables by including WHERE relkind='f'. Information on the foreign data wrappers themselves is in pg_foreign_data_wrappers, on servers in pg_foreign_servers, and on foreign tables in pg_foreign_tables.
Start with select relname from pg_class where relkind='f' to just get a list of table names, and from there you can use the pg_catalog docs to find whatever other pieces of information you need for what you're trying to do.
