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
adrian(dot)klaver(at)aklaver(dot)com