pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Another Data directory for second database - feasible?
7+ messages / 5 participants
[nested] [flat]

* Another Data directory for second database - feasible?
@ 2005-04-22 16:10 Tomeh, Husam <htomeh@firstam.com>
  2005-04-22 17:03 ` Re: Another Data directory for second database - feasible? Scott Marlowe <smarlowe@g2switchworks.com>
  2005-04-22 17:15 ` Re: Another Data directory for second database - feasible? Michael Fuhr <mike@fuhr.org>
  2005-04-22 18:44 ` Re: Another Data directory for second database - feasible? Chris Browne <cbbrowne@acm.org>
  0 siblings, 3 replies; 7+ messages in thread

From: Tomeh, Husam @ 2005-04-22 16:10 UTC (permalink / raw)
  To: pgsql-admin

 
I have a postgres installation with one database on a server. I'd need
to create another database; however, I'm curious if there's a way to
create the second database in a different directory than the existing
one. Is that feasible and how? Or does postgres only point to one data
directory?  Thanks in advance.


-- 
 Husam 

**********************************************************************
This message contains confidential information intended only for the 
use of the addressee(s) named above and may contain information that 
is legally privileged.  If you are not the addressee, or the person 
responsible for delivering it to the addressee, you are hereby 
notified that reading, disseminating, distributing or copying this 
message is strictly prohibited.  If you have received this message by 
mistake, please immediately notify us by replying to the message and 
delete the original message immediately thereafter.

Thank you.                                       FADLD Tag
**********************************************************************




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Another Data directory for second database - feasible?
  2005-04-22 16:10 Another Data directory for second database - feasible? Tomeh, Husam <htomeh@firstam.com>
@ 2005-04-22 17:03 ` Scott Marlowe <smarlowe@g2switchworks.com>
  2 siblings, 0 replies; 7+ messages in thread

From: Scott Marlowe @ 2005-04-22 17:03 UTC (permalink / raw)
  To: Tomeh, Husam <htomeh@firstam.com>; +Cc: pgsql-admin

On Fri, 2005-04-22 at 11:10, Tomeh, Husam wrote:
>  I have a postgres installation with one database on a server. I'd need
> to create another database; however, I'm curious if there's a way to
> create the second database in a different directory than the existing
> one. Is that feasible and how? Or does postgres only point to one data
> directory?  Thanks in advance.

If you're using 8.0, you can use tablespaces.  See:

http://www.postgresql.org/docs/8.0/static/manage-ag-tablespaces.html

If you're using 7.4 or before, you can use alternate locations.  See:

http://www.postgresql.org/docs/7.4/static/manage-ag-alternate-locs.html



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Another Data directory for second database - feasible?
  2005-04-22 16:10 Another Data directory for second database - feasible? Tomeh, Husam <htomeh@firstam.com>
@ 2005-04-22 17:15 ` Michael Fuhr <mike@fuhr.org>
  2 siblings, 0 replies; 7+ messages in thread

From: Michael Fuhr @ 2005-04-22 17:15 UTC (permalink / raw)
  To: Tomeh, Husam <htomeh@firstam.com>; +Cc: pgsql-admin

On Fri, Apr 22, 2005 at 09:10:35AM -0700, Tomeh, Husam wrote:
>  
> I have a postgres installation with one database on a server. I'd need
> to create another database; however, I'm curious if there's a way to
> create the second database in a different directory than the existing
> one. Is that feasible and how? Or does postgres only point to one data
> directory?  Thanks in advance.

Each database is stored in its own directory, usually under
$PGDATA/base.  Do you mean you want to create a database somewhere
else?  What version of PostgreSQL are you using?  8.0 and later
support tablespaces; in previous versions you could use the technique
described in the "Alternative Locations" section of the "Managing
Databases" chapter in the documentation.

http://www.postgresql.org/docs/8.0/interactive/manage-ag-tablespaces.html
http://www.postgresql.org/docs/8.0/interactive/sql-createdatabase.html

http://www.postgresql.org/docs/7.4/interactive/manage-ag-alternate-locs.html
http://www.postgresql.org/docs/7.4/interactive/sql-createdatabase.html

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Another Data directory for second database - feasible?
  2005-04-22 16:10 Another Data directory for second database - feasible? Tomeh, Husam <htomeh@firstam.com>
@ 2005-04-22 18:44 ` Chris Browne <cbbrowne@acm.org>
  2005-04-22 20:11   ` Re: Another Data directory for second database - feasible? Michelle Konzack <linux4michelle@freenet.de>
  2 siblings, 1 reply; 7+ messages in thread

From: Chris Browne @ 2005-04-22 18:44 UTC (permalink / raw)
  To: pgsql-admin

htomeh@firstam.com ("Tomeh, Husam") writes:
> I have a postgres installation with one database on a server. I'd need
> to create another database; however, I'm curious if there's a way to
> create the second database in a different directory than the existing
> one. Is that feasible and how? Or does postgres only point to one data
> directory?  Thanks in advance.

We have plenty of servers with multiple databases, handled in just
this fashion.

At the simplest level, it involves running initdb three times, on
three directories:

$ initdb -D /opt/DB1
$ initdb -D /opt/DB2
$ initdb -D /opt/DB3
$ vi /opt/DB?/postgresql.conf
[change port numbers for each]
$ for db in 1 2 3; do
  pg_ctl -D /opt/DB$db start
done

You probably want to set up some set of "init" scripts that manage
telling the instances where to stow log files and such, and which can
be auto-started upon system bootup.

One of my coworkers has built a really nice init script that's good at
building the directory structure where the DBs will be stowed, hence...

$ vi init_db3.sh
[modify config]
$ ./init_db3.sh mkdir
[builds directory structure]
$ ./init_db3.sh initdb
[runs initdb for you]
$ ./init_db3.sh start

At one point, he was trying to come up with a way of releasing it to
the community; hopefully poking Drew can lead to that coming
available, someday...
-- 
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78.  "I will not tell my Legions of Terror
"And he must  be taken alive!" The command will be:  ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/;



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Another Data directory for second database - feasible?
  2005-04-22 16:10 Another Data directory for second database - feasible? Tomeh, Husam <htomeh@firstam.com>
  2005-04-22 18:44 ` Re: Another Data directory for second database - feasible? Chris Browne <cbbrowne@acm.org>
@ 2005-04-22 20:11   ` Michelle Konzack <linux4michelle@freenet.de>
  0 siblings, 0 replies; 7+ messages in thread

From: Michelle Konzack @ 2005-04-22 20:11 UTC (permalink / raw)
  To: pgsql-admin

Am 2005-04-22 14:44:00, schrieb Chris Browne:

> One of my coworkers has built a really nice init script that's good at
> building the directory structure where the DBs will be stowed, hence...
> 
> $ vi init_db3.sh
> [modify config]
> $ ./init_db3.sh mkdir
> [builds directory structure]
> $ ./init_db3.sh initdb
> [runs initdb for you]
> $ ./init_db3.sh start
> 
> At one point, he was trying to come up with a way of releasing it to
> the community; hopefully poking Drew can lead to that coming
> available, someday...

Nice but I use under Debian/WOODY the "daemon" Directory

My /etc/init.d/tdpgsql use 'run-parts' and start all
databases in /etc/tdpgsql.d/*

So you put files like

/etc/tdpgsql.d/1michelleprivat
/etc/tdpgsql.d/2secret
/etc/tdpgsql.d/3business
/etc/tdpgsql.d/friends
/etc/tdpgsql.d/zzz

with all the configurations and 'run-parts' will start
or stop them in in order.

Please note, that the files in "/etc/tdpgsql.d/" should
be executable.

Greetings
Michelle

-- 
Linux-User #280138 with the Linux Counter, http://counter.li.org/
Michelle Konzack   Apt. 917                  ICQ #328449886
                   50, rue de Soultz         MSM LinuxMichi
0033/3/88452356    67100 Strasbourg/France   IRC #Debian (irc.icq.com)

Attachments:

  signature.pgp (application/pgp-signature, 188B) download

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Another Data directory for second database -
@ 2005-04-22 17:03 Tomeh, Husam <htomeh@firstam.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Tomeh, Husam @ 2005-04-22 17:03 UTC (permalink / raw)
  To: pgsql-admin

 
I have a postgres installation with one database on a server. I'd need
to create another database; however, I'm curious if there's a way to
create the second database in a different data directory than the
existing one. Is that feasible and how? I have read you can have start
multiple postmaster processes with different data locations. Is this the
way to do it? 

--
 Husam
**********************************************************************
This message contains confidential information intended only for the 
use of the addressee(s) named above and may contain information that 
is legally privileged.  If you are not the addressee, or the person 
responsible for delivering it to the addressee, you are hereby 
notified that reading, disseminating, distributing or copying this 
message is strictly prohibited.  If you have received this message by 
mistake, please immediately notify us by replying to the message and 
delete the original message immediately thereafter.

Thank you.                                       FADLD Tag
**********************************************************************




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Another Data directory for second database -
@ 2005-04-22 22:09 Tomeh, Husam <htomeh@firstam.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Tomeh, Husam @ 2005-04-22 22:09 UTC (permalink / raw)
  To: Chris Browne <cbbrowne@acm.org>; pgsql-admin

Thanks for the feedback. I have tried this way and seems to be working
just fine. For this approach, I have a postmaster for each databases,
correct?

The approach that another gentleman pointed out is the use of
alternative location where it actually uses one postmaster and creates a
symbolic link in the default base directory (using initlocation).

Both are great options and I think for my specific situation, I'd go
with your way, since it'll allow me more flexibility on maintaining the
different databases. I can shutdown one postmaster for maintenance for
instance, without affecting the other database. 


-- 
 Husam 


-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Chris Browne
Sent: Friday, April 22, 2005 11:44 AM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Another Data directory for second database -
feasible?

htomeh@firstam.com ("Tomeh, Husam") writes:
> I have a postgres installation with one database on a server. I'd need

> to create another database; however, I'm curious if there's a way to 
> create the second database in a different directory than the existing 
> one. Is that feasible and how? Or does postgres only point to one data

> directory?  Thanks in advance.

We have plenty of servers with multiple databases, handled in just this
fashion.

At the simplest level, it involves running initdb three times, on three
directories:

$ initdb -D /opt/DB1
$ initdb -D /opt/DB2
$ initdb -D /opt/DB3
$ vi /opt/DB?/postgresql.conf
[change port numbers for each]
$ for db in 1 2 3; do
  pg_ctl -D /opt/DB$db start
done

You probably want to set up some set of "init" scripts that manage
telling the instances where to stow log files and such, and which can be
auto-started upon system bootup.

One of my coworkers has built a really nice init script that's good at
building the directory structure where the DBs will be stowed, hence...

$ vi init_db3.sh
[modify config]
$ ./init_db3.sh mkdir
[builds directory structure]
$ ./init_db3.sh initdb
[runs initdb for you]
$ ./init_db3.sh start

At one point, he was trying to come up with a way of releasing it to the
community; hopefully poking Drew can lead to that coming available,
someday...
--
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78.  "I will not tell my Legions of Terror
"And he must  be taken alive!" The command will be:  ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/;

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

**********************************************************************
This message contains confidential information intended only for the 
use of the addressee(s) named above and may contain information that 
is legally privileged.  If you are not the addressee, or the person 
responsible for delivering it to the addressee, you are hereby 
notified that reading, disseminating, distributing or copying this 
message is strictly prohibited.  If you have received this message by 
mistake, please immediately notify us by replying to the message and 
delete the original message immediately thereafter.

Thank you.                                       FADLD Tag
**********************************************************************




^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2005-04-22 22:09 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-22 16:10 Another Data directory for second database - feasible? Tomeh, Husam <htomeh@firstam.com>
2005-04-22 17:03 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-04-22 17:15 ` Michael Fuhr <mike@fuhr.org>
2005-04-22 18:44 ` Chris Browne <cbbrowne@acm.org>
2005-04-22 20:11   ` Michelle Konzack <linux4michelle@freenet.de>
2005-04-22 17:03 Re: Another Data directory for second database - Tomeh, Husam <htomeh@firstam.com>
2005-04-22 22:09 Re: Another Data directory for second database - Tomeh, Husam <htomeh@firstam.com>

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