Обсуждение: Doubts PostgreSQL
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.
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/
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.
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.
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.
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