Обсуждение: managing multiple db developers
When I worked for an Oracle shop, the dba set up individual “schemas” for each developer. That allowed us a database instanceof our own for experiments, including modifying stored procedures and such, without the risk of negative impact onother developers or modifying the stable dev-test and QA databases. I am wondering how to accomplish a similar arrangement in an postgres cluster. Thank you, - Mark Phillips
On Tue, Jan 15, 2019 at 10:44 AM Mark Phillips <mark.phillips@mophilly.com> wrote: > > When I worked for an Oracle shop, the dba set up individual “schemas” for each developer. That allowed us a database instanceof our own for experiments, including modifying stored procedures and such, without the risk of negative impact onother developers or modifying the stable dev-test and QA databases. > > I am wondering how to accomplish a similar arrangement in an postgres cluster. I recommend leveraging the fact that PostgreSQL lacks per-instance licensing fees and have each developer run (more or less) apt-get install postgresql on their personal development virtual machine David J.
PostgreSQL supports schema's as well, a schema in PostgreSQL is a simple namespace, which is a different concept from Oracle but you can accomplish the same thing with a schema, in particular via the "CREATE SCHEMA AUTHORIZATION" approach https://www.postgresql.org/docs/11/sql-createschema.html On 1/15/19 11:07 AM, David G. Johnston wrote: > On Tue, Jan 15, 2019 at 10:44 AM Mark Phillips > <mark.phillips@mophilly.com> wrote: >> When I worked for an Oracle shop, the dba set up individual “schemas” for each developer. That allowed us a database instanceof our own for experiments, including modifying stored procedures and such, without the risk of negative impact onother developers or modifying the stable dev-test and QA databases. >> >> I am wondering how to accomplish a similar arrangement in an postgres cluster. > I recommend leveraging the fact that PostgreSQL lacks per-instance > licensing fees and have each developer run (more or less) > > apt-get install postgresql > > on their personal development virtual machine > > David J. >
You can create individual database for each developer. That works as a cluster, but you can specify smaller memory numbers for each developer.
On Tuesday, January 15, 2019, 10:08:05 AM PST, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Jan 15, 2019 at 10:44 AM Mark Phillips
<mark.phillips@mophilly.com> wrote:
>
> When I worked for an Oracle shop, the dba set up individual “schemas” for each developer. That allowed us a database instance of our own for experiments, including modifying stored procedures and such, without the risk of negative impact on other developers or modifying the stable dev-test and QA databases.
>
> I am wondering how to accomplish a similar arrangement in an postgres cluster.
I recommend leveraging the fact that PostgreSQL lacks per-instance
licensing fees and have each developer run (more or less)
apt-get install postgresql
on their personal development virtual machine
David J.
<mark.phillips@mophilly.com> wrote:
>
> When I worked for an Oracle shop, the dba set up individual “schemas” for each developer. That allowed us a database instance of our own for experiments, including modifying stored procedures and such, without the risk of negative impact on other developers or modifying the stable dev-test and QA databases.
>
> I am wondering how to accomplish a similar arrangement in an postgres cluster.
I recommend leveraging the fact that PostgreSQL lacks per-instance
licensing fees and have each developer run (more or less)
apt-get install postgresql
on their personal development virtual machine
David J.
On Jan 15, 2019, at 10:07 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
I recommend leveraging the fact that PostgreSQL lacks per-instance
licensing fees and have each developer run (more or less)
Thank you for the suggestion.
That has worked for us to a degree. We ran into situations where the developer had configuration issues and/or the dba was able to determine where “missing” changes came from.
Cheers,
- Mark
On Jan 15, 2019, at 10:14 AM, S. Bob <sbob@quadratum-braccas.com> wrote:
PostgreSQL supports schema's as well, a schema in PostgreSQL is a simple namespace, which is a different concept from Oracle but you can accomplish the same thing with a schema, in particular via the "CREATE SCHEMA AUTHORIZATION" approach
Thank you for the suggestion.
My only concern about using pg schemas is the level of separation. Also, I have toyed with create schema but, to my ignorant mind, I don’t see the tables and such in the “dev schema”.
- Mark
On Jan 15, 2019, at 12:16 PM, suresh neravati <suresh.neravati@yahoo.com> wrote:
You can create individual database for each developer. That works as a cluster, but you can specify smaller memory numbers for each developer.
Thank you. We have used that approach some, but I will revisit the idea with an eye on improving the use of logon and group roles.
- Mark
On 1/15/19 1:31 PM, Mark Phillips wrote:
On Jan 15, 2019, at 10:14 AM, S. Bob <sbob@quadratum-braccas.com> wrote:PostgreSQL supports schema's as well, a schema in PostgreSQL is a simple namespace, which is a different concept from Oracle but you can accomplish the same thing with a schema, in particular via the "CREATE SCHEMA AUTHORIZATION" approachThank you for the suggestion.My only concern about using pg schemas is the level of separation. Also, I have toyed with create schema but, to my ignorant mind, I don’t see the tables and such in the “dev schema”.- Mark
You'll need to augment setting the schema with setting the search_path, you can also set a default search_path for a user
On Jan 15, 2019, at 1:52 PM, S. Bob <sbob@quadratum-braccas.com> wrote:
You'll need to augment setting the schema with setting the search_path, you can also set a default search_path for a user
Thank you. I will work with that.
- Mark
Mark Phillips wrote: > On Jan 15, 2019, at 10:14 AM, S. Bob <sbob@quadratum-braccas.com> wrote: > > PostgreSQL supports schema's as well, a schema in PostgreSQL is a simple namespace, > > which is a different concept from Oracle but you can accomplish the same thing > > with a schema, in particular via the "CREATE SCHEMA AUTHORIZATION" approach > > Thank you for the suggestion. > > My only concern about using pg schemas is the level of separation. Also, I have > toyed with create schema but, to my ignorant mind, I don’t see the tables and such > in the “dev schema”. To emulate Oracle's behavior, create user x and his schema with CREATE ROLE x LOGIN; CREATE SCHEMA x AUTHORIZATION x; You don't have to mess with the search_path, because by default the schema with the same name as the user is the first on the search_path anyway. There are no "system privileges" to grant. To be secure, remove the CREATE privilege on the schema "public": REVOKE CREATE ON SCHEMA public FROM PUBLIC; Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com