pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDoubts PostgreSQL
4+ messages / 4 participants
[nested] [flat]
* Doubts PostgreSQL
@ 2015-10-08 14:35 rafael.burischipfer <RAFAEL.BURISCHIPFERUSA@GMAIL.COM>
0 siblings, 3 replies; 4+ messages in thread
From: rafael.burischipfer @ 2015-10-08 14:35 UTC (permalink / raw)
To: pgsql-admin
Hello everybody,
I'm Oracle, DB2 and SQL Server DBA, and now I started work with PostgreSQL
and I have some doubts.
1) Can I archive different databases in different directory path?
Example:
I want archive the database production 1 in '/mnt/production_1/archive' and
production 2 in '/mnt/production_2/archive'
2) I want change the WAL location, I don't want the WAL files stay in
default location, I want for example in '/PGDATA/WAL', and Can I create
different WALs to different databases?
3) Can I improve the WAL size? the default is 16,384 KB, I want for example
51,200 KB it is possible?
4) In my instance I have three databases (Production 1, Production 2 and
Production 3), and I have a role named UserProd with password 'test123', I
want the Production 1 database the password be 'passwd123' in Production 2
database be '123-passwd' and in Production 3 database be 'test123', it is
possible?
Thanks a lot people
--
View this message in context: http://postgresql.nabble.com/Doubts-PostgreSQL-tp5869292.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Doubts PostgreSQL
@ 2015-10-08 17:32 hubert depesz lubaczewski <depesz@depesz.com>
parent: rafael.burischipfer <RAFAEL.BURISCHIPFERUSA@GMAIL.COM>
2 siblings, 0 replies; 4+ messages in thread
From: hubert depesz lubaczewski @ 2015-10-08 17:32 UTC (permalink / raw)
To: rafael.burischipfer <RAFAEL.BURISCHIPFERUSA@GMAIL.COM>; +Cc: pgsql-admin
On Thu, Oct 08, 2015 at 07:35:32AM -0700, rafael.burischipfer wrote:
> 1) Can I archive different databases in different directory path?
> Example:
> I want archive the database production 1 in '/mnt/production_1/archive' and
> production 2 in '/mnt/production_2/archive'
if you mean:
a. archive as in "archive_command"
b. database as in one of databases in given pg installation
then no, you can't. wal is shared across all databases in cluster
(installation).
> 2) I want change the WAL location, I don't want the WAL files stay in
> default location, I want for example in '/PGDATA/WAL', and Can I create
> different WALs to different databases?
You can move wal, and then symlink pg_xlog directory, sure.
You can't have wal for different databases go to different directories.
> 3) Can I improve the WAL size? the default is 16,384 KB, I want for example
> 51,200 KB it is possible?
First - why do you think it would be improvement?
> 4) In my instance I have three databases (Production 1, Production 2 and
> Production 3), and I have a role named UserProd with password 'test123', I
> want the Production 1 database the password be 'passwd123' in Production 2
> database be '123-passwd' and in Production 3 database be 'test123', it is
> possible?
User accounts are shared across all databases in cluster. Why not use
separate accounts, then?
Also, while I'm asking - why do you want to move wal to another
location?
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Doubts PostgreSQL
@ 2015-10-08 17:49 Jorge Torralba <jorge.torralba@gmail.com>
parent: rafael.burischipfer <RAFAEL.BURISCHIPFERUSA@GMAIL.COM>
2 siblings, 0 replies; 4+ messages in thread
From: Jorge Torralba @ 2015-10-08 17:49 UTC (permalink / raw)
To: rafael.burischipfer <RAFAEL.BURISCHIPFERUSA@gmail.com>; +Cc: pgsql-admin
You need to remove the way oracle manages databases, roles and instance
from your mind. It is different in postgres.
1. You can create a table space and then create a database in a specific
table space. Look at postgres documentation for creating databases. If
you're talking about archiving wal files, you specify that at
postgresql.conf. The cluster will archive to where it is noted in the
archive command. but this applies to the entire cluster and not per
database.
2. Use symbolic links for pg_xlog if you want them somewhere else.
3. At compile time you can set the size with --with-wal-segsize=xxx. But
I would manage this differently with configuration parameters.
see http://www.postgresql.org/docs/9.0/static/install-procedure.html';
4. You probably would want to split your different prod databases into
separate instances so a failure of one does not impact another. Start
postgres for your separate instances on separate ports and separate data
dirs. for example
pg_ctl -D prod1/data -o "-p 5432" start
pg_ctl -D prod2/data -o "-p 5433" start
Obviously you would need to initialize and get the instances going first
before doing above. You can then asign role passwords to each instance.
You could create three separate databases on a single cluster. But a user
is global across all databases. Therefore you cannot have separate
passwords for the same user on a single cluster.
JT
On Thu, Oct 8, 2015 at 7:35 AM, rafael.burischipfer <
RAFAEL.BURISCHIPFERUSA@gmail.com> wrote:
> Hello everybody,
>
> I'm Oracle, DB2 and SQL Server DBA, and now I started work with PostgreSQL
> and I have some doubts.
>
> 1) Can I archive different databases in different directory path?
> Example:
> I want archive the database production 1 in '/mnt/production_1/archive' and
> production 2 in '/mnt/production_2/archive'
>
> 2) I want change the WAL location, I don't want the WAL files stay in
> default location, I want for example in '/PGDATA/WAL', and Can I create
> different WALs to different databases?
>
> 3) Can I improve the WAL size? the default is 16,384 KB, I want for example
> 51,200 KB it is possible?
>
> 4) In my instance I have three databases (Production 1, Production 2 and
> Production 3), and I have a role named UserProd with password 'test123', I
> want the Production 1 database the password be 'passwd123' in Production 2
> database be '123-passwd' and in Production 3 database be 'test123', it is
> possible?
>
>
> Thanks a lot people
>
>
>
> --
> View this message in context:
> http://postgresql.nabble.com/Doubts-PostgreSQL-tp5869292.html
> Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
--
Thanks,
Jorge Torralba
----------------------------
Note: This communication may contain privileged or other confidential
information. If you are not the intended recipient, please do not print,
copy, retransmit, disseminate or otherwise use the information. Please
indicate to the sender that you have received this email in error and
delete the copy you received. Thank You.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Doubts PostgreSQL
@ 2015-10-08 19:35 lst_hoe02@kwsoft.de
parent: rafael.burischipfer <RAFAEL.BURISCHIPFERUSA@GMAIL.COM>
2 siblings, 0 replies; 4+ messages in thread
From: lst_hoe02@kwsoft.de @ 2015-10-08 19:35 UTC (permalink / raw)
To: pgsql-admin
Zitat von "rafael.burischipfer" <RAFAEL.BURISCHIPFERUSA@gmail.com>:
> Hello everybody,
>
> I'm Oracle, DB2 and SQL Server DBA, and now I started work with PostgreSQL
> and I have some doubts.
>
> 1) Can I archive different databases in different directory path?
> Example:
> I want archive the database production 1 in '/mnt/production_1/archive' and
> production 2 in '/mnt/production_2/archive'
A "database" in PostgreSQL is light-weight compared to Oracle and
share the WAL (redolog) with other databases in the same cluster.
Therefore archive is per cluster (installation) and not per database.
>
> 2) I want change the WAL location, I don't want the WAL files stay in
> default location, I want for example in '/PGDATA/WAL', and Can I create
> different WALs to different databases?
You can link your WAL location to wherever you like, but as said the
same WAL is for the whole cluster.
> 3) Can I improve the WAL size? the default is 16,384 KB, I want for example
> 51,200 KB it is possible?
In PostgreSQL you raise the number of the WAL files with checkpoint_segments.
> 4) In my instance I have three databases (Production 1, Production 2 and
> Production 3), and I have a role named UserProd with password 'test123', I
> want the Production 1 database the password be 'passwd123' in Production 2
> database be '123-passwd' and in Production 3 database be 'test123', it is
> possible?
No, roles are also global per cluster. That said you can easily run
many PostgreSQL cluster on a single machine, the storage needed is
much smaller than for a Oracle installation.
Regards
Andreas
Attachments:
smime.p7s (application/pkcs7-signature, 5.7K) download
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2015-10-08 19:35 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-10-08 14:35 Doubts PostgreSQL rafael.burischipfer <RAFAEL.BURISCHIPFERUSA@GMAIL.COM>
2015-10-08 17:32 ` hubert depesz lubaczewski <depesz@depesz.com>
2015-10-08 17:49 ` Jorge Torralba <jorge.torralba@gmail.com>
2015-10-08 19:35 ` lst_hoe02@kwsoft.de
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