Re: Attaching database

Hi, guys,
After reading the documentation on
https://www.postgresql.org/docs/current/postgres-fdw.html
and checking the example I have a different question.

The presentation in the link referenced doesn't explain how to get the
table list on the
remote server and the information on the specific table.

The example tals about connection and creating a linked table, but it
doesn't explain
how to know the information about the tables on the remote

Is it possible or I will have to know this beforehand?

(Sorry for the top-posting).

Thank you.


On Sat, Oct 15, 2022 at 5:57 PM Alex Theodossis <alex@dossi.info> wrote:
>
> Hi,
>
> you can only run inquires now on information_schema for the database you
> are connected to.
>
> If you are looking/searching in pg_database though, you can information
> globally.
>
> Something changed recently (I am using Dbeaver); I was able to attach to
> a single database and see all my databases; run queries, etc.
>
> With the latest update, now you have to have a separate connection to
> each database.
>
> Navicat now does the same thing, so my thinking is they must have
> changed the drivers.
>
> Regards,
>
> On 10/15/22 02:06, Julien Rouhaud wrote:
> > Hi,
> >
> > On Fri, Oct 14, 2022 at 11:16:44PM -0500, Igor Korot wrote:
> >> Sorry for resurrecting this old thread...
> >> If an attaching the DB creates new connection which will be cmpletely
> >> independent - how the INFORMATION_SCHEMA.table@table_catalog
> >> field is handled.
> >>
> >> Lets say I open connection to the DB (finance) and then attached another DB
> >> (finance_2021).
> >>
> >> So, when I call SELECT table_schema, table_name FROM INFORMATION_SCHEMA.table
> >> I will get all tables from (finance) DB only.
> >> And to get all tables from (finance_2021) I need to make this catalog current
> >> and reissue the query.
> >>
> >> Am I right?
> > No.  In postgres, databases are completely disjoint containers and once you
> > have a connection on a given database it will stay on that database, there's no
> > way around that.
> >
> > Using postgres_fdw allows you to create a local table that will point to
> > another table, possibly on another database or even another server, but it will
> > still be a (foreign) table, that has to be created in the current database in
> > the schema of your choice.
> >
> > Depending on your use case, maybe what you could do is create a finance_2021
> > schema, and create all the foreign tables you need in that schema pointing to
> > the finance_2021 database.  Any table existing in both "finance" and
> > "finance_2021" will then be visible in information_schema.tables, with a
> > different table_schema.  If you have multiple schemas in each database, then
> > find a way to make it work, maybe adding a _2021 suffix on each schema or
> > something like that.
> >
> > You can then maybe use the search_path (see
> > https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH) to
> > use by default one of the set of tables rather than the other.  But in any
> > case, it's only a workaround that has to be implemented on your client, as you
> > will always be connected on the same database, and see both set of object in
> > information_schema.
> >
> >
> --
> Alex Theodossis
> alex@dossi.info
> 347-514-5420
>
>
>