Re: Isolation of multiple databse instances provided by a single postgres server

"Peter J. Holzer" <[email protected]> Thu, 21 Nov 2019 23:21:32 +0100

Newsgroups	gmane.comp.db.postgresql.general
Message-ID	<[email protected]>

On 2019-11-21 07:21:10 -0800, Adrian Klaver wrote:
> On 11/21/19 6:51 AM, Laurenz Albe wrote:
> > - The different databases in a cluster are physically located in
> >    the same tablespace, but they are logically strictly separated.
> >    You cannot connect to one database and access another database from there.
> 
> dblink(https://www.postgresql.org/docs/11/dblink.html) and
> FDW(https://www.postgresql.org/docs/11/sql-createforeigntable.html) would
> beg to differ.

I was thinking of FDW.

But I think Laurenz had the SQL syntax in mind which does include the
database in the fully qualified form of a tablename. Something like

db2=# select * from db2.public.t3;
╔════╤═══════════════════════╗
║ id │           t           ║
╟────┼───────────────────────╢
║  1 │ this is db2.public.t3 ║
╚════╧═══════════════════════╝
(1 row)

works. But when you try to access a table in a different database you
get an error:

db2=# select * from db1.public.t1;
ERROR:  cross-database references are not implemented: "db1.public.t1"
LINE 1: select * from db1.public.t1;

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [email protected]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

signature.asc (application/pgp-signature, 833 B)