Poatgresql database on more than one disk

Barry wrote:

Hi All,

I am a newcommer to Postgresql, currently I am looking at moving
a Pick based application across to PostgreSQL.

I am using RH Linux and Postgresql 7.3.6

The test system I am using has a 2 channel raid card with a disk pack
connected to each channel. The OS and Postgresql sits on its own internal
disk.

I would like to be able to use one disk pack for development and the
other for end user testing / verification (the tape drive can only backup
one disk pack completely, not both)

I have spent a fair amount of time researching how I can run two
databases, one on each disk pack but have not been able to find a
solution.

Is it possible to configure Postgresql to have seperate databases
on seperate disks ?

Not easily as will be with the Table Space feature that most probably
will be present on 7.5

With 7.3.6 what you can do is move your db and create a link in the
original place:

-bash-2.05b$ oid2name
All databases:
---------------------------------
17142 = kalman
19185 = photodb
27895 = empdb
1 = template1
17141 = template0
5776262 = logs

-bash-2.05b$ pwd
/var/lib/pgsql/data/base

-bash-2.05b$ ll
total 32
drwx------ 2 postgres postgres 4096 Feb 8 15:18 1
drwx------ 2 postgres postgres 4096 Feb 8 03:56 17141
drwx------ 2 postgres postgres 4096 May 25 19:37 17142
drwx------ 2 postgres postgres 8192 Feb 8 15:58 19185
drwx------ 3 postgres postgres 8192 May 16 02:46 27895
drwx------ 2 postgres postgres 4096 May 18 00:06 5776262

in my case if I want the database kalman to another disk what I have to
do is: stop the engine, move the directory /var/lib/pgsql/data/base/17142
to the new location and create the link.
With the same mechanism you can also place different tables in different
disks.

I hope this help you.

Regards
Gaetano Mendola

Gaetano Mendola <mendola@bigfoot.com> wrote in message news:<40B3E30C.3020408@bigfoot.com>...

Barry wrote:

Hi All,

I am a newcommer to Postgresql, currently I am looking at moving
a Pick based application across to PostgreSQL.

I am using RH Linux and Postgresql 7.3.6

The test system I am using has a 2 channel raid card with a disk pack
connected to each channel. The OS and Postgresql sits on its own internal
disk.

I would like to be able to use one disk pack for development and the
other for end user testing / verification (the tape drive can only backup
one disk pack completely, not both)

I have spent a fair amount of time researching how I can run two
databases, one on each disk pack but have not been able to find a
solution.

Is it possible to configure Postgresql to have seperate databases
on seperate disks ?

Not easily as will be with the Table Space feature that most probably
will be present on 7.5

With 7.3.6 what you can do is move your db and create a link in the
original place:

-bash-2.05b$ oid2name
All databases:
---------------------------------
17142 = kalman
19185 = photodb
27895 = empdb
1 = template1
17141 = template0
5776262 = logs

-bash-2.05b$ pwd
/var/lib/pgsql/data/base

-bash-2.05b$ ll
total 32
drwx------ 2 postgres postgres 4096 Feb 8 15:18 1
drwx------ 2 postgres postgres 4096 Feb 8 03:56 17141
drwx------ 2 postgres postgres 4096 May 25 19:37 17142
drwx------ 2 postgres postgres 8192 Feb 8 15:58 19185
drwx------ 3 postgres postgres 8192 May 16 02:46 27895
drwx------ 2 postgres postgres 4096 May 18 00:06 5776262

in my case if I want the database kalman to another disk what I have to
do is: stop the engine, move the directory /var/lib/pgsql/data/base/17142
to the new location and create the link.
With the same mechanism you can also place different tables in different
disks.

I hope this help you.

Regards
Gaetano Mendola

Works like a treat, thank you for your time.

Barry

On Tue, 2004-05-25 at 18:33, Barry wrote:

I would like to be able to use one disk pack for development and the
other for end user testing / verification (the tape drive can only backup
one disk pack completely, not both)

Is it possible to configure Postgresql to have seperate databases
on seperate disks ?

One solution is to run two postmasters (two services). Each would use a
different value for the PGDATA environment variable.

The pro to this is that there would be a greater degree of separation
between your development environment and test environment. You could
start and stop the development environment service separately from the
test environment, for instance.

The con is that only one postmaster can bind to port 5432 at a time.
You would have to set up one of the postmasters to use a different
port... perhaps 5433?

Since you are using RedHat, take a look at /etc/init.d/postgresql. You
should see where the the PGDATA environment variable is set in the top
of the script. PGPORT is also set, which is the port number that the
postmaster will use. (I am using Fedora Core 1 and Fedora Core 2. I
assume that RedHat's scripts are very similar to this, if not
identical).

If I were doing it, I'd probably copy /etc/init.d/postgresql to
/etc/init.d/postgresql_dev and /etc/init.d/postgresql_test. I'd modify
/etc/init.d/postgresql_test so that it controls my test environment and
/etc/init.d/postgresql_dev so that it controls my development
environment. I'd use chkconfig to disable the service postgresql and
enable the services postgresql_dev and postgresql_test. (That way, if
upgrades clobber /etc/init.d/postgresql, you're not left in a bind).

You can contact me off-list if you need more help with this solution.

--
Philip A. Chapman

Application Development:
Java, Visual Basic (MCP), PostgreSQL, MySQL, MSSQL
Linux, Windows 9x, Windows NT, Windows 2000, Windows XP

Could you just use the initlocation tool and specify the 'location'
parameter when creating the database?

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Gaetano Mendola
Sent: Tuesday, May 25, 2004 8:22 PM
To: Barry; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Poatgresql database on more than one disk

Barry wrote:

Hi All,

I am a newcommer to Postgresql, currently I am looking at moving
a Pick based application across to PostgreSQL.

I am using RH Linux and Postgresql 7.3.6

The test system I am using has a 2 channel raid card with a disk pack
connected to each channel. The OS and Postgresql sits on its own internal
disk.

I would like to be able to use one disk pack for development and the
other for end user testing / verification (the tape drive can only backup
one disk pack completely, not both)

I have spent a fair amount of time researching how I can run two
databases, one on each disk pack but have not been able to find a
solution.

Is it possible to configure Postgresql to have seperate databases
on seperate disks ?

Not easily as will be with the Table Space feature that most probably
will be present on 7.5

With 7.3.6 what you can do is move your db and create a link in the
original place:

-bash-2.05b$ oid2name
All databases:
---------------------------------
17142 = kalman
19185 = photodb
27895 = empdb
1 = template1
17141 = template0
5776262 = logs

-bash-2.05b$ pwd
/var/lib/pgsql/data/base

-bash-2.05b$ ll
total 32
drwx------ 2 postgres postgres 4096 Feb 8 15:18 1
drwx------ 2 postgres postgres 4096 Feb 8 03:56 17141
drwx------ 2 postgres postgres 4096 May 25 19:37 17142
drwx------ 2 postgres postgres 8192 Feb 8 15:58 19185
drwx------ 3 postgres postgres 8192 May 16 02:46 27895
drwx------ 2 postgres postgres 4096 May 18 00:06 5776262

in my case if I want the database kalman to another disk what I have to
do is: stop the engine, move the directory /var/lib/pgsql/data/base/17142
to the new location and create the link.
With the same mechanism you can also place different tables in different
disks.

I hope this help you.

Regards
Gaetano Mendola

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

On Wed, 2004-05-26 at 07:24, Barry wrote:

Gaetano Mendola <mendola@bigfoot.com> wrote in message news:<40B3E30C.3020408@bigfoot.com>...

Barry wrote:

...

The test system I am using has a 2 channel raid card with a disk pack
connected to each channel. The OS and Postgresql sits on its own internal
disk.

I would like to be able to use one disk pack for development and the
other for end user testing / verification (the tape drive can only backup
one disk pack completely, not both)

I have spent a fair amount of time researching how I can run two
databases, one on each disk pack but have not been able to find a
solution.

Is it possible to configure Postgresql to have seperate databases
on seperate disks ?

Not easily as will be with the Table Space feature that most probably
will be present on 7.5

With 7.3.6 what you can do is move your db and create a link in the
original place:

...

Works like a treat, thank you for your time.

You need to be aware that certain files in the top level of ${PGDATA}
(pg_clog/* and pg_xlog) are essential for reading your data and apply
globally to all databases. If pg_clog is destroyed, the database is
effectively destroyed too. It's not clear that your arrangements will
get that copied to tape.

You may also not be aware that simply dumping the files to tape is not
necessarily going to give you a consistent or recoverable database
unless you stop the postmaster first. pg_dumpall (or pg_dump for single
databases) can be used to dump transaction-consistent backups that
include every transaction completed at the time it begins to run; it is
also likely to take less space on tape than a dump of the raw database
files. If you recover a set of files that have been copied to tape by
an external utility while the database is active, you may find that some
transactions at least are unrecoverable and at the worst tables may be
corrupted. See the chapter on backups in the manual.

--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"How precious also are thy thoughts unto me, O God! how
great is the sum of them! If I should count them, they
are more in number than the sand; when I awake, I am
still with thee." Psalms 139: 17,18

--On Monday, May 31, 2004 2:14 PM -0500 "Philip A. Chapman"
<pchapman@pcsw.us> wrote:

On Tue, 2004-05-25 at 18:33, Barry wrote:

Is it possible to configure Postgresql to have seperate databases
on seperate disks ?

One solution is to run two postmasters (two services). Each would use a
different value for the PGDATA environment variable.

Pardon me for being dense, but I don't see how, with this approach, you
maintain transaction integrity between the two databases. Suppose you need
a transaction involving tables from both databases. Can you explain how you
achieve this?

Pardon me for being dense, but I don't see how, with this approach, you
maintain transaction integrity between the two databases. Suppose you need
a transaction involving tables from both databases. Can you explain how you
achieve this?

You can't anyways (server side) no matter where the two
separate databases reside unless you use contrib/dblink in
which case it again matters not where the databases reside.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346