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

On Thu, Nov 21, 2019 at 08:18:21AM -0600, Ron wrote:
> 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 [, ... ] ) ]
>
OH, that does look familiar, thanks for taking time to educate me.
>
>
> > 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.

Got that.
>
>
> > 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.

I am still struggling with the schema - role - user relationship in Postgres.
It appears to me there is one more layer than is needed/useful.
>
> > If
> > so, how does this cores pond to physical on disk storage?
>
> It corresponds not at all.

Understand that now.
>
> 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.

Thanks again.

BTW, I do appreciate your taking time to clarify things I am confused on,
Hopefully I can pass on this knowledge to to others.

I have decide to isolate my sandbox instance by running on a physically
different machine BTW, which was what the original question was about, but I
am learning other things that I knew I did not have a full understanding of,
and I appreciate that.

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin