Thread: Foreign Data Wrapper Handler
Can anyone recommend a good online resource for learning how to set up a foreign data wrapper using a custom fdw name? It seems the trick is to use a handler to make it work but so far the search results have been elusive for creating a fdw with a successful outcome. I'm using 'PostgreSQL 11.8 on amd64-portbld-freebsd12.1, compiled by FreeBSD clang version 8.0.1 (tags/RELEASE_801/final 366581) (based on LLVM 8.0.1), 64-bit' Thanks for your help! Sue -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Susan E Hurst Principal Consultant Brookhurst Data LLC Email: susan.hurst@brookhurstdata.com Mobile: 314-486-3261
On 11/7/20 12:44 PM, Susan Hurst wrote: > Can anyone recommend a good online resource for learning how to set up a > foreign data wrapper using a custom fdw name? It seems the trick is to > use a handler to make it work but so far the search results have been > elusive for creating a fdw with a successful outcome. Have you gone through this?: https://www.postgresql.org/docs/12/fdwhandler.html Also can we get a definition of 'custom fdw name'? > > I'm using 'PostgreSQL 11.8 on amd64-portbld-freebsd12.1, compiled by > FreeBSD clang version 8.0.1 (tags/RELEASE_801/final 366581) (based on > LLVM 8.0.1), 64-bit' > > Thanks for your help! > > Sue > -- Adrian Klaver adrian.klaver@aklaver.com
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. Sue --- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Susan E Hurst Principal Consultant Brookhurst Data LLC Email: susan.hurst@brookhurstdata.com Mobile: 314-486-3261 On 2020-11-07 14:51, Adrian Klaver wrote: > On 11/7/20 12:44 PM, Susan Hurst wrote: >> Can anyone recommend a good online resource for learning how to set up >> a foreign data wrapper using a custom fdw name? It seems the trick is >> to use a handler to make it work but so far the search results have >> been elusive for creating a fdw with a successful outcome. > > Have you gone through this?: > > https://www.postgresql.org/docs/12/fdwhandler.html > > Also can we get a definition of 'custom fdw name'? > > >> >> I'm using 'PostgreSQL 11.8 on amd64-portbld-freebsd12.1, compiled by >> FreeBSD clang version 8.0.1 (tags/RELEASE_801/final 366581) (based on >> LLVM 8.0.1), 64-bit' >> >> Thanks for your help! >> >> Sue >>
On 11/8/20 11:09 AM, 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; Aren't you looking for?: https://www.postgresql.org/docs/12/sql-createserver.html " A foreign server typically encapsulates connection information that a foreign-data wrapper uses to access an external data resource. Additional user-specific connection information may be specified by means of user mappings. The server name must be unique within the database. " > > 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. > > Sue > > > --- > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > Susan E Hurst > Principal Consultant > Brookhurst Data LLC > Email: susan.hurst@brookhurstdata.com > Mobile: 314-486-3261 > > On 2020-11-07 14:51, Adrian Klaver wrote: >> On 11/7/20 12:44 PM, Susan Hurst wrote: >>> Can anyone recommend a good online resource for learning how to set >>> up a foreign data wrapper using a custom fdw name? It seems the >>> trick is to use a handler to make it work but so far the search >>> results have been elusive for creating a fdw with a successful outcome. >> >> Have you gone through this?: >> >> https://www.postgresql.org/docs/12/fdwhandler.html >> >> Also can we get a definition of 'custom fdw name'? >> >> >>> >>> I'm using 'PostgreSQL 11.8 on amd64-portbld-freebsd12.1, compiled by >>> FreeBSD clang version 8.0.1 (tags/RELEASE_801/final 366581) (based on >>> LLVM 8.0.1), 64-bit' >>> >>> Thanks for your help! >>> >>> Sue >>> > > -- Adrian Klaver adrian.klaver@aklaver.com
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
Thanks for the detailed instructions, Laurenz! "The foreign server encapsulates the connection string to access a remote PostgreSQL database. Define one per remote database you want to access." Where do I define "one per remote database"?.....in pg_hba.conf? --- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Susan E Hurst Principal Consultant Brookhurst Data LLC Email: susan.hurst@brookhurstdata.com Mobile: 314-486-3261 On 2020-11-09 11:12, Laurenz Albe wrote: > 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
On Mon, 2020-11-09 at 11:21 -0600, Susan Hurst wrote: > "The foreign server encapsulates the connection string to access a remote > PostgreSQL database. Define one per remote database you want to access." > > Where do I define "one per remote database"?.....in pg_hba.conf? No, in SQL: CREATE FOREIGN SERVER somename FOREIFN DATA WRAPPER postgres_fdw OPTIONS (...); Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
On 11/9/20 9:21 AM, Susan Hurst wrote: > Thanks for the detailed instructions, Laurenz! > > "The foreign server encapsulates the connection string to access a remote > PostgreSQL database. Define one per remote database you want to access." > > Where do I define "one per remote database"?.....in pg_hba.conf? https://www.postgresql.org/docs/12/sql-createserver.html > > --- > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > Susan E Hurst > Principal Consultant > Brookhurst Data LLC > Email: susan.hurst@brookhurstdata.com > Mobile: 314-486-3261 > -- Adrian Klaver adrian.klaver@aklaver.com