pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAnother 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>
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 -
@ 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 - feasible?
@ 2005-04-22 17:03 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Tomeh, Husam <htomeh@firstam.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 17:15 Michael Fuhr <mike@fuhr.org>
parent: Tomeh, Husam <htomeh@firstam.com>
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 18:44 Chris Browne <cbbrowne@acm.org>
parent: Tomeh, Husam <htomeh@firstam.com>
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 20:11 Michelle Konzack <linux4michelle@freenet.de>
parent: Chris Browne <cbbrowne@acm.org>
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 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