I have a server with roughly 7000 databases that all have a practically identical schema. I've been querying them and collecting information about their contents in a separate database on the server. The way I have done this so far is using dblink queries wrapped in some Python code that iterates through all the databases. This is fine when I'm able to construct a query beforehand and iterate it, but it's not very efficient if I just want to quickly query one of the databases as dblink queries are somewhat complicated to construct.
I haven't used FDW in the past but I figured it would be much simpler to just import the relevant schema(ta) from every database on the server and store those as objects in my main database. What trips me up is that the FDW documentation begins by suggesting the creation of a foreign server object and user mapping. Since every database is on the same server and queried by the same user, I don't want to begin by adding a step that might be unnecessary.
So my question is, is it necessary to specify server and user mapping to create connections to databases on the same server, with a user that has identical permissions across every database?
Best Answer
Yes. You can't use a foreign server without creating it. And you would have to create one for each database you want to link to, as
dbnamecan only be specified at the foreign server level, not at the level of individual foreign tables. And each server needs its own user mapping, they can't be shared.