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

On 11/21/19 6:51 AM, Laurenz Albe wrote:
> On Thu, 2019-11-21 at 06:55 -0500, stan wrote:

> You can use tablespaces in PostgreSQL, which are directories on a
> different file system, to put your data elsewhere.
> But that has very limited use-cases, and normally you don't
> create a tablespace.
> 
> About isolation:
> - 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.

> 
> - There can be several schemas in a database.

There are several schemas in a database. In a new database:

test=# \dnS
         List of schemas
         Name        |  Owner
--------------------+----------
  information_schema | postgres
  pg_catalog         | postgres
  pg_temp_1          | postgres
  pg_toast           | postgres
  pg_toast_temp_1    | postgres
  public             | postgres


>    You can access a table in a schema if you have the required privilege
>    on both the schema and the table.
> 
> This is entirely independent of physical storage, which is provided
> by tablespaces.  Tables from different databases can be located in the
> same tablespace and vice versa.
> 
> Think of "database" and "schema" as a logical separation in SQL.
> 
> You cannot backup and restore an individual tablespace, only the
> whole cluster.
> 
> Yours,
> Laurenz Albe
> 


-- 
Adrian Klaver
[email protected]