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

On Thu, 2019-11-21 at 06:55 -0500, stan wrote:
> It seems to me that I can have one Postgres "server" running listening on a
> single port on a single machine. It appears that the data files for this
> "server" are managed internally by the Postgres server instance, and I
> have no control of what is stored where in them. In an Oracle world, I can
> create tablespaces, which have a relationship to OS files, and I can
> explicitly control what objects are stored in which tablespaces (OS file),
> thus, for example, when I do a hot backup, I put a specific tablespaces in
> backup mode, and can then safely copy this OS file (yes I have to properly
> deal with archive logs). Thus I would be somewhat comfortable have to
> distinct "instance: provided by that one Oracle "server".
>
> It appears to me that, within this one Postgres "instance", there are 2
> levels of "isolation", which are database, and schemas. Is this correct? If
> so, how does this cores pond to physical on disk storage?

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.

- There can be several schemas in a database.
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
--
Cybertec | https://www.cybertec-postgresql.com