Re: Attaching database - Mailing list pgsql-general
From | Alex Theodossis |
---|---|
Subject | Re: Attaching database |
Date | |
Msg-id | 5f19c045-07ae-c2ba-5d95-3c125cc84146@dossi.info Whole thread Raw |
In response to | Attaching database (Igor Korot <ikorot01@gmail.com>) |
Responses |
Re: Attaching database
|
List | pgsql-general |
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
pgsql-general by date: