On Sun, 2020-11-08 at 13:09 -0600, Susan Hurst wrote: > The first pass thru https://www.postgresql.org/docs/12/fdwhandler.html > does not tell me what I think I need to know, but I will digest this > more thoroughly. Maybe I need to understand more of the lingo re: > foreign data wrappers. I do understand that all fdw names must be unique > within a database so if I want to connect to more than one foreign db, I > need a different name for each connection. I cannot name each fdw > postgres_fdw. I would like to name the fdws something like: > dbname_to_foreigndbname. > > For example, here are 2 possible fdws: > > create foreign data wrapper stp_to_geo; > create foreign data wrapper stp_to_metrics; > > That syntax creates the fdw and I can create user mappings but I cannot > import any foreign schemas into my database. The error message says that > there is no handler for the fdw. That's where I'm stuck. > > BTW, I did try using postgres_fdw as a handler... > > create foreign data wrapper stp_to_geo handler postgres_fdw; > > ...but then I got these errors: > ERROR: function postgres_fdw() does not exist > ERROR: foreign-data wrapper "stp_to_geo" does not exist > > Looks like I need to study a bit more. This is how you would create a new foreign data wrapper object for PostgreSQL: CREATE FOREIGN DATA WRAPPER myfdw HANDLER public.postgres_fdw_handler VALIDATOR public.postgres_fdw_validator; This assumes that you installed the extension "postgres_fdw" in schema "public". But you normally don't have to create a new foreign data wrapper: the one named "postgres_fdw" that is created by the extension is good enough. The only reason would be to have a foreign data wrapper with non-default options, but since there are no options for "postgres_fdw", that is moot. So don't do that. The hierarchy of objects is as follows: - The foreign data wrapper encapsulates the code required to access the foreign data source. You need only one per database; no need to create a new one. - The foreign server encapsulates the connection string to access a remote PostgreSQL database. Define one per remote database you want to access. - The user mapping encapsulates the credentials for a user to access a foreign server. Create one per user and foreign server (or a single one for PUBLIC = everybody). - The foreign table describes how a remote table is mapped locally. Define one per table that interests you. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
