Thread: Attaching database
Hi, IIUC, PostgreSQL does not support attaching a database to an existing connection. However I was told that I can use this: https://www.postgresql.org/docs/9.3/postgres-fdw.html. Is it the same thing? Why there is no simple ATTACH <db> AS <dbname> ? Thank you.
Hi
po 19. 10. 2020 v 19:40 odesílatel Igor Korot <ikorot01@gmail.com> napsal:
Hi,
IIUC, PostgreSQL does not support attaching a database to an existing
connection.
However I was told that I can use this:
https://www.postgresql.org/docs/9.3/postgres-fdw.html.
Is it the same thing? Why there is no simple
ATTACH <db> AS <dbname>
It is a different thing - postgresql_fdw does nested connect - it uses client-server protocol.
For postgres connect and sql engine process is one entity - and engine is written without a possibility to reconnect to another database.
Regards
Pavel
?
Thank you.
Hi, Pavel, On Mon, Oct 19, 2020 at 12:51 PM Pavel Stehule <pavel.stehule@gmail.com> wrote: > > Hi > > po 19. 10. 2020 v 19:40 odesílatel Igor Korot <ikorot01@gmail.com> napsal: >> >> Hi, >> IIUC, PostgreSQL does not support attaching a database to an existing >> connection. >> However I was told that I can use this: >> https://www.postgresql.org/docs/9.3/postgres-fdw.html. >> >> Is it the same thing? Why there is no simple >> >> ATTACH <db> AS <dbname> >> > > It is a different thing - postgresql_fdw does nested connect - it uses client-server protocol. > > For postgres connect and sql engine process is one entity - and engine is written without a possibility to reconnect toanother database. So if I understand correctly the postgresql_fdw is creating a second connection and uses it as kind of "virtualizing mechanism" in order to give access to the second database. Am I right? Thank you. > > Regards > > Pavel > > > >> >> ? >> >> Thank you. >> >>
po 19. 10. 2020 v 20:18 odesílatel Igor Korot <ikorot01@gmail.com> napsal:
Hi, Pavel,
On Mon, Oct 19, 2020 at 12:51 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> Hi
>
> po 19. 10. 2020 v 19:40 odesílatel Igor Korot <ikorot01@gmail.com> napsal:
>>
>> Hi,
>> IIUC, PostgreSQL does not support attaching a database to an existing
>> connection.
>> However I was told that I can use this:
>> https://www.postgresql.org/docs/9.3/postgres-fdw.html.
>>
>> Is it the same thing? Why there is no simple
>>
>> ATTACH <db> AS <dbname>
>>
>
> It is a different thing - postgresql_fdw does nested connect - it uses client-server protocol.
>
> For postgres connect and sql engine process is one entity - and engine is written without a possibility to reconnect to another database.
So if I understand correctly the postgresql_fdw is creating a second
connection and uses it as kind of "virtualizing mechanism"
in order to give access to the second database.
yes - it does new connect to somewhere (local or remote server, but mechanism is absolutely same)
Am I right?
Thank you.
>
> Regards
>
> Pavel
>
>
>
>>
>> ?
>>
>> Thank you.
>>
>>
On 2020-10-19 20:21:05 +0200, Pavel Stehule wrote:
> po 19. 10. 2020 v 20:18 odesílatel Igor Korot <ikorot01@gmail.com> napsal:
> On Mon, Oct 19, 2020 at 12:51 PM Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
> > It is a different thing - postgresql_fdw does nested connect - it uses
> > client-server protocol.
> >
> > For postgres connect and sql engine process is one entity - and engine is
> > written without a possibility to reconnect to another database.
>
> So if I understand correctly the postgresql_fdw is creating a second
> connection and uses it as kind of "virtualizing mechanism"
> in order to give access to the second database.
>
>
> yes - it does new connect to somewhere (local or remote server, but mechanism
> is absolutely same)
In case this isn't clear:
It is the server (or more specifically, the foreign data wrapper) which
opens that connection. To the client it looks like it's just accessing a
normal table within the same database.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachment
Hi, guys, On Sun, Oct 25, 2020 at 1:27 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote: > > On 2020-10-19 20:21:05 +0200, Pavel Stehule wrote: > > po 19. 10. 2020 v 20:18 odesílatel Igor Korot <ikorot01@gmail.com> napsal: > > On Mon, Oct 19, 2020 at 12:51 PM Pavel Stehule <pavel.stehule@gmail.com> > > wrote: > > > It is a different thing - postgresql_fdw does nested connect - it uses > > > client-server protocol. > > > > > > For postgres connect and sql engine process is one entity - and engine is > > > written without a possibility to reconnect to another database. > > > > So if I understand correctly the postgresql_fdw is creating a second > > connection and uses it as kind of "virtualizing mechanism" > > in order to give access to the second database. > > > > > > yes - it does new connect to somewhere (local or remote server, but mechanism > > is absolutely same) > > In case this isn't clear: > > It is the server (or more specifically, the foreign data wrapper) which > opens that connection. To the client it looks like it's just accessing a > normal table within the same database. 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? Thank you. > > hp > > -- > _ | Peter J. Holzer | Story must make more sense than reality. > |_|_) | | > | | | hjp@hjp.at | -- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!"
On Fri, Oct 14, 2022 at 9:17 PM Igor Korot <ikorot01@gmail.com> wrote:
Hi, guys,
On Sun, Oct 25, 2020 at 1:27 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>
> On 2020-10-19 20:21:05 +0200, Pavel Stehule wrote:
> In case this isn't clear:
>
> It is the server (or more specifically, the foreign data wrapper) which
> opens that connection. To the client it looks like it's just accessing a
> normal table within the same database.
Sorry for resurrecting this old thread...
Then why did you do it? You couldn't send a new email without copying possibly no longer interested people and with better thought out self-contained content that simply notes you are somehow using an FDW.
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
Call this how exactly? There are three information_schema instances that you can be talking about, though only the one in the local database is going to be called that. If you are dealing with FDWs you would have to have different names involved.
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?
Do it and find out? Then if still confused, show what you attempted. But I don't know what this concept of "make the catalog current" you speak of comes from. That isn't a thing that I am aware of. Where do you see this documented?
David J.
Hi, David et al, On Fri, Oct 14, 2022 at 11:39 PM David G. Johnston <david.g.johnston@gmail.com> wrote: > > On Fri, Oct 14, 2022 at 9:17 PM Igor Korot <ikorot01@gmail.com> wrote: >> >> Hi, guys, >> >> On Sun, Oct 25, 2020 at 1:27 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote: >> > >> > On 2020-10-19 20:21:05 +0200, Pavel Stehule wrote: >> >> > In case this isn't clear: >> > >> > It is the server (or more specifically, the foreign data wrapper) which >> > opens that connection. To the client it looks like it's just accessing a >> > normal table within the same database. >> >> Sorry for resurrecting this old thread... > > > Then why did you do it? You couldn't send a new email without copying possibly no longer interested people and with betterthought out self-contained content that simply notes you are somehow using an FDW. > >> >> 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 > > > Call this how exactly? There are three information_schema instances that you can be talking about, though only the onein the local database is going to be called that. If you are dealing with FDWs you would have to have different namesinvolved. > >> >> 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? > > > Do it and find out? Then if still confused, show what you attempted. But I don't know what this concept of "make thecatalog current" you speak of comes from. That isn't a thing that I am aware of. Where do you see this documented? Making catalog current means switching between DBs. Remember initially I connected to (finance) DB, which made the (finance) catalog current. Then I "opened a second connection" to (finance_2021), which made that current catalog and so that select would give me all tables from (finance_2021). I hope now its clearer. Thank you. > > David J.
On Fri, Oct 14, 2022 at 9:47 PM Igor Korot <ikorot01@gmail.com> wrote:
Making catalog current means switching between DBs.
Remember initially I connected to (finance) DB, which made the (finance) catalog
current.
Then I "opened a second connection" to (finance_2021), which made
that current catalog and so that select would give me all tables from
(finance_2021).
I hope now its clearer.
No, it is not clearer. I suggest you show some code. Given what you've said my guess is you are not correct.
David J.
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.
On 10/14/22 21:46, Igor Korot wrote: > Making catalog current means switching between DBs. > Remember initially I connected to (finance) DB, which made the (finance) catalog > current. > Then I "opened a second connection" to (finance_2021), which made > that current catalog and so that select would give me all tables from > (finance_2021). No you didn't. Rough outline of what happens: 1) You connected to the database finance. 2) While in the finance database you queried the foreign tables that are linked to finance_2021. 3) The queries made connections the to finance_2021 for the purposes of making the data visible in the foreign tables in finance. 4) The client you did this did not 'leave' the finance database, so the only information_schema you have access to is in the finance database. > > I hope now its clearer. > > Thank you. > >> >> David J. > > -- Adrian Klaver adrian.klaver@aklaver.com
On 10/15/22 08:20, Adrian Klaver wrote:
> On 10/14/22 21:46, Igor Korot wrote:
>
>
>> Making catalog current means switching between DBs.
>> Remember initially I connected to (finance) DB, which made the
>> (finance) catalog
>> current.
>> Then I "opened a second connection" to (finance_2021), which made
>> that current catalog and so that select would give me all tables from
>> (finance_2021).
>
> No you didn't.
>
> Rough outline of what happens:
>
> 1) You connected to the database finance.
> 2) While in the finance database you queried the foreign tables that are
> linked to finance_2021.
> 3) The queries made connections the to finance_2021 for the purposes of
> making the data visible in the foreign tables in finance.
> 4) The client you did this did not 'leave' the finance database, so the
^
in
> only information_schema you have access to is in the finance database.
>
>>
>> I hope now its clearer.
>>
>> Thank you.
>>
>>>
>>> David J.
>>
>>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Hi, Adrian,
On Sat, Oct 15, 2022, 10:51 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/15/22 08:20, Adrian Klaver wrote:
> On 10/14/22 21:46, Igor Korot wrote:
>
>
>> Making catalog current means switching between DBs.
>> Remember initially I connected to (finance) DB, which made the
>> (finance) catalog
>> current.
>> Then I "opened a second connection" to (finance_2021), which made
>> that current catalog and so that select would give me all tables from
>> (finance_2021).
>
> No you didn't.
>
> Rough outline of what happens:
>
> 1) You connected to the database finance.
> 2) While in the finance database you queried the foreign tables that are
> linked to finance_2021.
> 3) The queries made connections the to finance_2021 for the purposes of
> making the data visible in the foreign tables in finance.
> 4) The client you did this did not 'leave' the finance database, so the
^
in
> only information_schema you have access to is in the finance database.
So any and all operations/queries performed before, during or after that will be done on (finance) catalog), because this is the "main" connection, right?
Thank you.
>
>>
>> I hope now its clearer.
>>
>> Thank you.
>>
>>>
>>> David J.
>>
>>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 10/15/22 08:56, Igor Korot wrote: > Hi, Adrian, > > > So any and all operations/queries performed before, during or after that > will be done on (finance) catalog), because this is the "main" > connection, right? > I think you are getting stuck on SQLite terminology: https://sqlite.org/lang_attach.html "Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. ..." That is different mechanism all together. The closest I can come with alternate example is linked tables in Access. You are working in one client connected to Server A that makes as needed connections to Server B to move data back and forth. You are not actually connected full time to Server B and your point of reference is the connection your client initially made to Server A. So yes because it is the "main" connection. -- Adrian Klaver adrian.klaver@aklaver.com
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
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 > > >
Hi, On Tue, Oct 18, 2022 at 10:06:40PM -0500, Igor Korot wrote: > 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? In general it's up to you to make sure that the remote table definition matches the local one. You can use IMPORT FOREIGN SCHEMA (1) to automate this process, but you will still have to make sure that any subsequent modification on the remote table (added/removed column, changed datatype...) is also done on the local foreign table. > (Sorry for the top-posting). This isn't really related to the previous discussion so it's quite ok, although it's usually a good practice to trim the unwanted parts of the previous message (in this case the whole previous message). [1] https://www.postgresql.org/docs/current/sql-importforeignschema.html