pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcreate tablespaces and users
10+ messages / 3 participants
[nested] [flat]
* create tablespaces and users
@ 2019-01-14 15:24 Pepe TD Vo <pepevo@yahoo.com>
0 siblings, 2 replies; 10+ messages in thread
From: Pepe TD Vo @ 2019-01-14 15:24 UTC (permalink / raw)
To: pgsql-admin
Hello experts,
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: create tablespaces and users
@ 2019-01-14 15:46 Shreeyansh Dba <shreeyansh2014@gmail.com>
parent: Pepe TD Vo <pepevo@yahoo.com>
1 sibling, 1 reply; 10+ messages in thread
From: Shreeyansh Dba @ 2019-01-14 15:46 UTC (permalink / raw)
To: Pepe TD Vo <pepevo@yahoo.com>; +Cc: pgsql-admin
In PostgreSQL tablespaces and user all cluster level objects. tablespace is
just another name for a folder where the Postgres server process owner
(typically “postgres” system user) is the owner and can write some files.
In PostgreSQL All objects stored in default tablespace i.e. pg_default.
You can create or alter databases in the tablespace and assign ownership
for that tablespace.
Commands use for creating user and tablespace:
create user <user name>;
create tablespace <name> location '<directory path>' owner <user name>;
Thanks & Regards,
*Shreeyansh DBA Team*
www.shreeyansh.com
On Mon, Jan 14, 2019 at 8:55 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
> Hello experts,
>
> From oracle databases, I have few tablespaces and users created for that
> particular database. How could I do the same for postgres?
>
> thank you.
>
>
>
> *Bach-Nga*No one in this world is pure and perfect. If you avoid people
> for their mistakes you will be alone. So judge less, love and forgive more.
> To call him a dog hardly seems to do him justice though in as much as he
> had four legs, a tail, and barked, I admit he was, to all outward
> appearances. But to those who knew him well, he was a perfect gentleman
> (Hermione Gingold)
>
> **Live simply **Love generously **Care deeply **Speak kindly.
> *** Genuinely rich *** Faithful talent *** Sharing success
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: create tablespaces and users
@ 2019-01-14 16:09 Pepe TD Vo <pepevo@yahoo.com>
parent: Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Pepe TD Vo @ 2019-01-14 16:09 UTC (permalink / raw)
To: Shreeyansh Dba <shreeyansh2014@gmail.com>; +Cc: pgsql-admin
thank you. so after create the database, I need to create user and tablespace with each ownershipcreate user <user name>;create tablespace <name> location '<directory path>' owner <user name>;
what if I transfer the schemas/triggers, etc... from oracle to postgres, are they stayed the same or what? What is the best way suggestion I need to do?
thank you.
v/r, Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
On Monday, January 14, 2019 10:48 AM, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
In PostgreSQL tablespaces and user all cluster level objects. tablespace is just another name for a folder where the Postgres server process owner (typically “postgres” system user) is the owner and can write some files.
In PostgreSQL All objects stored in default tablespace i.e. pg_default. You can create or alter databases in the tablespace and assign ownership for that tablespace.
Commands use for creating user and tablespace:create user <user name>;create tablespace <name> location '<directory path>' owner <user name>;
Thanks & Regards,
Shreeyansh DBA Team
www.shreeyansh.com
On Mon, Jan 14, 2019 at 8:55 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
Hello experts,
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: create tablespaces and users
@ 2019-01-14 16:25 Shreeyansh Dba <shreeyansh2014@gmail.com>
parent: Pepe TD Vo <pepevo@yahoo.com>
0 siblings, 1 reply; 10+ messages in thread
From: Shreeyansh Dba @ 2019-01-14 16:25 UTC (permalink / raw)
To: Pepe TD Vo <pepevo@yahoo.com>; +Cc: pgsql-admin
Are you talking about migration..?? If yes, you can keep the schema /
trigger same as oracle or transfer data in different schema by creating new
as per your requirement whereas the public is the default schema in
PostgreSQL.
Thanks & Regards,
*Shreeyansh DBA Team*
www.shreeyansh.com
On Mon, Jan 14, 2019 at 9:39 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
> thank you.
> so after create the database, I need to create user and tablespace with
> each ownership
> create user <user name>;
> create tablespace <name> location '<directory path>' owner <user name>;
>
> what if I transfer the schemas/triggers, etc... from oracle to postgres,
> are they stayed the same or what? What is the best way suggestion I need
> to do?
>
> thank you.
>
> v/r,
>
>
>
> *Bach-Nga*No one in this world is pure and perfect. If you avoid people
> for their mistakes you will be alone. So judge less, love and forgive more.
> To call him a dog hardly seems to do him justice though in as much as he
> had four legs, a tail, and barked, I admit he was, to all outward
> appearances. But to those who knew him well, he was a perfect gentleman
> (Hermione Gingold)
>
> **Live simply **Love generously **Care deeply **Speak kindly.
> *** Genuinely rich *** Faithful talent *** Sharing success
>
>
> On Monday, January 14, 2019 10:48 AM, Shreeyansh Dba <
> shreeyansh2014@gmail.com> wrote:
>
>
> In PostgreSQL tablespaces and user all cluster level objects. tablespace
> is just another name for a folder where the Postgres server process owner
> (typically “postgres” system user) is the owner and can write some files.
>
> In PostgreSQL All objects stored in default tablespace i.e. pg_default.
> You can create or alter databases in the tablespace and assign ownership
> for that tablespace.
>
> Commands use for creating user and tablespace:
> create user <user name>;
> create tablespace <name> location '<directory path>' owner <user name>;
>
> Thanks & Regards,
> *Shreeyansh DBA Team*
> www.shreeyansh.com
>
>
> On Mon, Jan 14, 2019 at 8:55 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
>
> Hello experts,
>
> From oracle databases, I have few tablespaces and users created for that
> particular database. How could I do the same for postgres?
>
> thank you.
>
>
>
> *Bach-Nga*No one in this world is pure and perfect. If you avoid people
> for their mistakes you will be alone. So judge less, love and forgive more.
> To call him a dog hardly seems to do him justice though in as much as he
> had four legs, a tail, and barked, I admit he was, to all outward
> appearances. But to those who knew him well, he was a perfect gentleman
> (Hermione Gingold)
>
> **Live simply **Love generously **Care deeply **Speak kindly.
> *** Genuinely rich *** Faithful talent *** Sharing success
>
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: create tablespaces and users
@ 2019-01-14 16:33 Pepe TD Vo <pepevo@yahoo.com>
parent: Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Pepe TD Vo @ 2019-01-14 16:33 UTC (permalink / raw)
To: Shreeyansh Dba <shreeyansh2014@gmail.com>; +Cc: pgsql-admin
yes, we want to migrate oracle to postgres with a new name of database in postgres and keep all the schemas/triggers as same as oracle, not migrate oracle to postgres using the default postgres database. Do I need to build a new database name with all the tablespaces?
CREATE TABLESPACE cidr_data LOCATION '/ssd1/postgresql/data';REATE TABLESPACE cidr_data LOCATION '/ssd1/postgresql/data';CREATE DATABASE "CIDR" WITH OWNER = "PSmasteruser" ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TABLESPACE = cidr_data CONNECTION LIMIT = -1;
you can keep the schema / trigger same as oracle or transfer data in different schema by creating new as per your requirement whereas the public is the default schema in PostgreSQL.
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
On Monday, January 14, 2019 11:27 AM, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
Are you talking about migration..?? If yes, you can keep the schema / trigger same as oracle or transfer data in different schema by creating new as per your requirement whereas the public is the default schema in PostgreSQL.
Thanks & Regards,
Shreeyansh DBA Team
www.shreeyansh.com
On Mon, Jan 14, 2019 at 9:39 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
thank you. so after create the database, I need to create user and tablespace with each ownershipcreate user <user name>;create tablespace <name> location '<directory path>' owner <user name>;
what if I transfer the schemas/triggers, etc... from oracle to postgres, are they stayed the same or what? What is the best way suggestion I need to do?
thank you.
v/r, Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
On Monday, January 14, 2019 10:48 AM, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
In PostgreSQL tablespaces and user all cluster level objects. tablespace is just another name for a folder where the Postgres server process owner (typically “postgres” system user) is the owner and can write some files.
In PostgreSQL All objects stored in default tablespace i.e. pg_default. You can create or alter databases in the tablespace and assign ownership for that tablespace.
Commands use for creating user and tablespace:create user <user name>;create tablespace <name> location '<directory path>' owner <user name>;
Thanks & Regards,
Shreeyansh DBA Team
www.shreeyansh.com
On Mon, Jan 14, 2019 at 8:55 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
Hello experts,
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: create tablespaces and users
@ 2019-01-14 16:34 Pepe TD Vo <pepevo@yahoo.com>
parent: Pepe TD Vo <pepevo@yahoo.com>
0 siblings, 0 replies; 10+ messages in thread
From: Pepe TD Vo @ 2019-01-14 16:34 UTC (permalink / raw)
To: Shreeyansh Dba <shreeyansh2014@gmail.com>; +Cc: pgsql-admin
what happened if I have 6 tablespaces from Oracle, should I create all tablespaces in Postgres as well?
thank you. Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
On Monday, January 14, 2019 11:33 AM, Pepe TD Vo <pepevo@yahoo.com> wrote:
yes, we want to migrate oracle to postgres with a new name of database in postgres and keep all the schemas/triggers as same as oracle, not migrate oracle to postgres using the default postgres database. Do I need to build a new database name with all the tablespaces?
CREATE TABLESPACE cidr_data LOCATION '/ssd1/postgresql/data';REATE TABLESPACE cidr_data LOCATION '/ssd1/postgresql/data';CREATE DATABASE "CIDR" WITH OWNER = "PSmasteruser" ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TABLESPACE = cidr_data CONNECTION LIMIT = -1;
you can keep the schema / trigger same as oracle or transfer data in different schema by creating new as per your requirement whereas the public is the default schema in PostgreSQL.
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
On Monday, January 14, 2019 11:27 AM, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
Are you talking about migration..?? If yes, you can keep the schema / trigger same as oracle or transfer data in different schema by creating new as per your requirement whereas the public is the default schema in PostgreSQL.
Thanks & Regards,
Shreeyansh DBA Team
www.shreeyansh.com
On Mon, Jan 14, 2019 at 9:39 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
thank you. so after create the database, I need to create user and tablespace with each ownershipcreate user <user name>;create tablespace <name> location '<directory path>' owner <user name>;
what if I transfer the schemas/triggers, etc... from oracle to postgres, are they stayed the same or what? What is the best way suggestion I need to do?
thank you.
v/r, Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
On Monday, January 14, 2019 10:48 AM, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
In PostgreSQL tablespaces and user all cluster level objects. tablespace is just another name for a folder where the Postgres server process owner (typically “postgres” system user) is the owner and can write some files.
In PostgreSQL All objects stored in default tablespace i.e. pg_default. You can create or alter databases in the tablespace and assign ownership for that tablespace.
Commands use for creating user and tablespace:create user <user name>;create tablespace <name> location '<directory path>' owner <user name>;
Thanks & Regards,
Shreeyansh DBA Team
www.shreeyansh.com
On Mon, Jan 14, 2019 at 8:55 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
Hello experts,
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: create tablespaces and users
@ 2019-01-15 07:26 Thomas Kellerer <spam_eater@gmx.net>
parent: Pepe TD Vo <pepevo@yahoo.com>
1 sibling, 1 reply; 10+ messages in thread
From: Thomas Kellerer @ 2019-01-15 07:26 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Pepe TD Vo schrieb am 14.01.2019 um 16:24:
> From oracle databases, I have few tablespaces and users created for
> that particular database. How could I do the same for postgres?
The question is: what was the reason to use tablespaces in Oracle?
Most of the time I see an Oracle installation with a tablespace per user, it's to manage space more easily.
As Postgres does not store data in one huge file, this is not really relevant here.
As soon as a table is dropped, the space is released from the filesystem.
So just create the users and let them use the default tablespace, unless you used the tablespaces
to distribute the I/O load to different disks.
Thomas
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: create tablespaces and users
@ 2019-01-15 14:42 Pepe TD Vo <pepevo@yahoo.com>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 2 replies; 10+ messages in thread
From: Pepe TD Vo @ 2019-01-15 14:42 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
so you suggest not to create the tablespace and associate with the database, right? What's about the owner? Still postgres or create user owner? From Oracle we have 4 schemas for cidr database.
CREATE DATABASE "CIDR" WITH OWNER = postgres --> or new users???? ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TABLESPACE = pg_default CONNECTION LIMIT = -1; thank you.
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
On Tuesday, January 15, 2019 2:26 AM, Thomas Kellerer <spam_eater@gmx.net> wrote:
Pepe TD Vo schrieb am 14.01.2019 um 16:24:
> From oracle databases, I have few tablespaces and users created for
> that particular database. How could I do the same for postgres?
The question is: what was the reason to use tablespaces in Oracle?
Most of the time I see an Oracle installation with a tablespace per user, it's to manage space more easily.
As Postgres does not store data in one huge file, this is not really relevant here.
As soon as a table is dropped, the space is released from the filesystem.
So just create the users and let them use the default tablespace, unless you used the tablespaces
to distribute the I/O load to different disks.
Thomas
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: create tablespaces and users
@ 2019-01-15 17:57 Pepe TD Vo <pepevo@yahoo.com>
parent: Pepe TD Vo <pepevo@yahoo.com>
1 sibling, 0 replies; 10+ messages in thread
From: Pepe TD Vo @ 2019-01-15 17:57 UTC (permalink / raw)
To: Pepe TD Vo <pepevo@yahoo.com>; Thomas Kellerer <spam_eater@gmx.net>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
In Oracle, we set up each “schema”/user for each developer and/or each database login. This allowed the schema logon into their own database and create their own tables/function/triggers, etc.... How do I avoid all people logon into the postgres db but their own schema/pgsql user?
thank you.
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
On Tuesday, January 15, 2019 9:42 AM, Pepe TD Vo <pepevo@yahoo.com> wrote:
so you suggest not to create the tablespace and associate with the database, right? What's about the owner? Still postgres or create user owner? From Oracle we have 4 schemas for cidr database.
CREATE DATABASE "CIDR" WITH OWNER = postgres --> or new users???? ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TABLESPACE = pg_default CONNECTION LIMIT = -1; thank you.
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
On Tuesday, January 15, 2019 2:26 AM, Thomas Kellerer <spam_eater@gmx.net> wrote:
Pepe TD Vo schrieb am 14.01.2019 um 16:24:
> From oracle databases, I have few tablespaces and users created for
> that particular database. How could I do the same for postgres?
The question is: what was the reason to use tablespaces in Oracle?
Most of the time I see an Oracle installation with a tablespace per user, it's to manage space more easily.
As Postgres does not store data in one huge file, this is not really relevant here.
As soon as a table is dropped, the space is released from the filesystem.
So just create the users and let them use the default tablespace, unless you used the tablespaces
to distribute the I/O load to different disks.
Thomas
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: create tablespaces and users
@ 2019-01-16 08:04 Thomas Kellerer <spam_eater@gmx.net>
parent: Pepe TD Vo <pepevo@yahoo.com>
1 sibling, 0 replies; 10+ messages in thread
From: Thomas Kellerer @ 2019-01-16 08:04 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Pepe TD Vo schrieb am 15.01.2019 um 15:42:
> so you suggest not to create the tablespace and associate with the
> database, right?
Correct.
Unless you used the tablespaces for performance optimization (e.g. spreading the I/O over multiple harddisks) I suggest to stick with the default tablespace.
> What's about the owner? Still postgres or create
> user owner? From Oracle we have 4 schemas for cidr database.
That depends on what you want to allow the users.
You can e.g. create 4 database and make each user the owner of that database.
So every user has full control over their database and the data is clearly separated.
If the users need to access tables from other users (e.g. in a JOIN), then it's best to create a single database with 4 schemas.
In that case "postgres" should be the database owner.
Then create a schema for each user (making the users the owner of those schemas), and you have a setup which is very similar to Oracle.
I would also revoke all grants from the public schema, so that the users don't accidentally create objects there.
Thomas
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2019-01-16 08:04 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-01-14 15:24 create tablespaces and users Pepe TD Vo <pepevo@yahoo.com>
2019-01-14 15:46 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2019-01-14 16:09 ` Pepe TD Vo <pepevo@yahoo.com>
2019-01-14 16:25 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2019-01-14 16:33 ` Pepe TD Vo <pepevo@yahoo.com>
2019-01-14 16:34 ` Pepe TD Vo <pepevo@yahoo.com>
2019-01-15 07:26 ` Thomas Kellerer <spam_eater@gmx.net>
2019-01-15 14:42 ` Pepe TD Vo <pepevo@yahoo.com>
2019-01-15 17:57 ` Pepe TD Vo <pepevo@yahoo.com>
2019-01-16 08:04 ` Thomas Kellerer <spam_eater@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox