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

On 11/21/19 5:55 AM, stan wrote:
> On Wed, Nov 20, 2019 at 04:24:40PM -0600, Ron wrote:
>> On 11/20/19 4:03 PM, stan wrote:
>>> I am working on a fairly small application to use for managing a companies
>>> business.
>>>
>>> I have a "production" instance hosted by one of the cloud providers, and 2
>>> other instances. This is fairly new to me. In the past, I have created
>>> applications by keeping a set of scripts that can be used to rebuild the
>>> database, and pg_dump to restore the date. Based on some recommendations I
>>> am using pg_basebackup to backup the production instance nightly. My
>>> background is primarily Oracle. I realize looking at the way pg_basebackup
>>> works that multiple database instances, provided by one server are actually
>>> stored in the same physical OS files.
>>>
>>>
>>> We have traditionally (in the Postgres world) had a sandbox, that we used
>>> for upgrades, and testing development methodologies, and this seems to be
>>> supported pretty well by pg_dump.
>>>
>>> Now that I know "too much" I am concerned about hosting the sandbox on the
>>> same Postgres instance.
>> What specifically do you mean by "instance"??? (I know what it means in the
>> SQL Server world, and in Postgres all the databases accessible via a single
>> $PGDATA are called a *cluster*.)
> Sorry for my incorrect terminology. I am probably confused about the
> technology here. Let me try to explain what I think I understand.
>
> 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),

Same in Postgres.

https://www.postgresql.org/docs/9.6/sql-createtablespace.html

CREATE TABLESPACEtablespace_name
[ OWNER {new_owner | CURRENT_USER | SESSION_USER } ]
LOCATION 'directory'
[ WITH (tablespace_option =value [, ... ] ) ]

> 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".

There's no ability to backup a single tablespace in Postgres, because the
purpose behind them is not the same in Oracle and Postgres.

> It appears to me that, within this one Postgres "instance", there are 2
> levels of "isolation", which are database, and schemas. Is this correct?

Yes, but ... schema in Postgres are /different/ from Oracle schema.  In
Postgres, the CREATE SCHEMA command creates a schema, whereas CREATE USER
creates a schema in Oracle.

> If
> so, how does this cores pond to physical on disk storage?

It corresponds not at all.

The directory that a table's files go in is solely dependent on the
tablespace it lives in (typically "pg_default", who's location is $PGDATA).

>>> Recognizing that this is a fairly small application, what are wiser folks
>>> than I recommendations?
>>>
>>> Should I run the sandbox from different Postgres server, possibly even on a
>>> different machine? Is pg_dump still good way to move the production
>>> instance to the sandbox, and perhaps even the other way around?
>> Running CAT, STG, UAT, DEV, etc on different VMs is certainly one solution,
>> isolating them from each other.
> Makes sense.
>> OTOH, you can initdb multiple clusters on the same host, accessing them via
>> different $PGDATA variables and port numbers.
> That is consistent with what I thought I understood.
>
> Thanks for taking time to educate me.

--
Angular momentum makes the world go 'round.