From: | Julien Rouhaud <rjuju123(at)gmail(dot)com> |
---|---|
To: | Igor Korot <ikorot01(at)gmail(dot)com> |
Cc: | "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Attaching database |
Date: | 2022-10-15 06:06:32 |
Message-ID: | 20221015060632.c4tvuxwychkfiha5@jrouhaud |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)table(at)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2022-10-15 15:20:55 | Re: Attaching database |
Previous Message | David G. Johnston | 2022-10-15 04:53:35 | Re: Attaching database |