postgreSQL on NAS/SAN?

On Sun, 15 Jun 2003, Daniel Seichter wrote:

Hello,
is it possible, to use a postgreSQL database on a NAS or a SAN? I
somewhere read, that you should not install a database to a RAID5 but the
most NAS and SAN I know, are using RAID5.
Does anyone know aout anything like this?

RAID5 is fine for a database. It provides a fair compromise between
speed, safety, and economy. If you need more speed, you might need to go
to a RAID 1+0 (or 0+1).

running postgresql on a NAS or SAN is quite doable, but you should test
your configuration carefully. Note that many NAS units report write
completion upon receipt of the data (i.e. before it's actually written) so
you may have data integrity issues should the power go out in the middle
of a transaction.

SANs are generally more robust than NAS, but I'm not that familiar with
running a database on one.

One thing you CANNOT do is allow two postmasters to write to the same data
store. That WILL corrupt your database and cause problems.

Hello Scott,

RAID5 is fine for a database. It provides a fair compromise between
speed, safety, and economy. If you need more speed, you might need to go
to a RAID 1+0 (or 0+1).

Ok, well, because a progress-person (not postgresql) said, that it will be
not good for running a (general, not only progress) database on a RAID5
System.

running postgresql on a NAS or SAN is quite doable, but you should test
your configuration carefully. Note that many NAS units report write
completion upon receipt of the data (i.e. before it's actually written) so
you may have data integrity issues should the power go out in the middle
of a transaction.

Ok, then we should use a SAN, if we need to use one.

One thing you CANNOT do is allow two postmasters to write to the same data
store. That WILL corrupt your database and cause problems.

This means, that postgreSQL isn't for configuring clusters? We don't need
one, but we do not know what the future brings :o(

Daniel

On Tue, 17 Jun 2003, Daniel Seichter wrote:

Hello Scott,

RAID5 is fine for a database. It provides a fair compromise between
speed, safety, and economy. If you need more speed, you might need to go
to a RAID 1+0 (or 0+1).

Ok, well, because a progress-person (not postgresql) said, that it will be
not good for running a (general, not only progress) database on a RAID5
System.

It really all depends. If it's a report database with only a tiny
percentage of accesses being write oriented then RAID5 is a great
solution. If it's primarily transactional with lots of writing, then
RAID5 starts to be less of an attractive option.

Generally, the more drives you throw at a RAID5 the better it will
perform, whereas a simple 4 disk setup under RAID1+0 can usually run quite
fast.

running postgresql on a NAS or SAN is quite doable, but you should test
your configuration carefully. Note that many NAS units report write
completion upon receipt of the data (i.e. before it's actually written) so
you may have data integrity issues should the power go out in the middle
of a transaction.

Ok, then we should use a SAN, if we need to use one.

Or make sure if you use a NAS it isn't set to say it wrote the data before
it actually did.

One thing you CANNOT do is allow two postmasters to write to the same data
store. That WILL corrupt your database and cause problems.

This means, that postgreSQL isn't for configuring clusters? We don't need
one, but we do not know what the future brings :o(

Currently, any clustering / failover / replication is an add on. If you
were to want to have two Postgresql servers with replication and failover
between them, they would each need their own data store. That store could
be on the same storage system, they would just have to be in different
directories.

Each replication solution for postgresql has it's advantages /
disadvantages. Are you looking more for failover, load balancing, hot
spare?

Hello Scott,

Are you looking more for failover, load balancing, hot
spare?

I am looking for a hot spare, so if one server crashed, the second will
"spare" it, because if this database will be down (down is meant for longer
than 2 hours) more than two other databases will not continue working (they
could continue working, but without new data, so it will be senseless).

But at the moment it is all more in my mind than on bits and bytes, because
we are in the phase of planning.

Daniel

On 17 Jun 2003 at 15:41, Daniel Seichter wrote:

Hello Scott,

Are you looking more for failover, load balancing, hot
spare?

I am looking for a hot spare, so if one server crashed, the second will
"spare" it, because if this database will be down (down is meant for longer
than 2 hours) more than two other databases will not continue working (they
could continue working, but without new data, so it will be senseless).

If you have upto 2 hours to work with, may be you could go for asynchronous
replication solutions based on replicated checkpointed WAL segments.

Using those solutions+round robin DNS plus heartbeat service should yield what
you are looking for..

Bye
Shridhar

--
Senate, n.: A body of elderly gentlemen charged with high duties and
misdemeanors. -- Ambrose Bierce

On Tue, Jun 17, 2003 at 15:41:45 +0200,
Daniel Seichter <daniel@dseichter.de> wrote:

Hello Scott,

Are you looking more for failover, load balancing, hot
spare?

I am looking for a hot spare, so if one server crashed, the second will
"spare" it, because if this database will be down (down is meant for longer
than 2 hours) more than two other databases will not continue working (they
could continue working, but without new data, so it will be senseless).

Once the orignal postmaster has stopped running (say because its server
died) you could run a different postmaster (on say another server) and
access the same data on your storage system. But if you do this you
will want some sort of safety system so that two postmasters can't
accidentally run at the same time. The normal interlock won't work for you
because it keeps a PID file and checks to see if the pid in that file (if any)
is still running. That doesn't work accross servers.

There is a system which uses your serial port so that if server B detects
server A goes down, it will send a signal over the serial port which
disconnects server A's power supply. That way, server B never
"accidentally" takes over for server A when in fact server A is still
running. I don't remember where these are sold, but they were mentioned
in the MissionCriticalLinux system documentation.

Jon

On Tue, 17 Jun 2003, Bruno Wolff III wrote:

On Tue, Jun 17, 2003 at 06:57:15AM -0600, scott.marlowe wrote:

Currently, any clustering / failover / replication is an add on. If you
were to want to have two Postgresql servers with replication and failover
between them, they would each need their own data store. That store could
be on the same storage system, they would just have to be in different
directories.

Why? This is only needed if both are active that is for load balancing.
The usual failover case of a hot-stand-by does not require this. You can
make the backup machine start its postmaster as soon as the other one
crashes.

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De
ICQ: 179140304, AIM: michaelmeskes, Jabber: meskes@jabber.org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

On Tue, Jun 17, 2003 at 03:41:45PM +0200, Daniel Seichter wrote:

I am looking for a hot spare, so if one server crashed, the second will
"spare" it, because if this database will be down (down is meant for longer
than 2 hours) more than two other databases will not continue working (they
could continue working, but without new data, so it will be senseless).

Not sure what you mean. Shall the second machine take over? Since this
should be hot 2 hours is a lot of time. Using a private network you can
detect failures almost immediately.

I do recommend a a local checking like watchdog or mon, so a restart is
tried before the takeover. And I'd make sure the primary machine stays
down.

Has been done before. :-)

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De
ICQ: 179140304, AIM: michaelmeskes, Jabber: meskes@jabber.org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

On Tue, 2003-06-17 at 12:05, Michael Meskes wrote:

On Tue, Jun 17, 2003 at 03:41:45PM +0200, Daniel Seichter wrote:

I am looking for a hot spare, so if one server crashed, the second will
"spare" it, because if this database will be down (down is meant for longer
than 2 hours) more than two other databases will not continue working (they
could continue working, but without new data, so it will be senseless).

Not sure what you mean. Shall the second machine take over? Since this
should be hot 2 hours is a lot of time. Using a private network you can
detect failures almost immediately.

I do recommend a a local checking like watchdog or mon, so a restart is
tried before the takeover. And I'd make sure the primary machine stays
down.

This is going to sound bad to users of Open Source OSs and databases,
but for all work that has to go into clustering machines and making
databases work with them...

Why not use a clustered-by-design OS like VMS? It is very easy to
put a couple of dual-Alpha boxen cluster-connected via fiber to SCSI
devices. A cluster-aware relational database like Rdb runs on all
nodes of a cluster in a totally shared-disk environment. While both
nodes are working fine, half of the work goes to either node, and if
one node goes down, the other node still does all the work.

-- 
+-----------------------------------------------------------+
| Ron Johnson, Jr.     Home: ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
|                                                           |
| "Oh, great altar of passive entertainment, bestow upon me |
|  thy discordant images at such speed as to render linear  |
|  thought impossible" (Calvin, regarding TV)               |
+-----------------------------------------------------------

On Tue, Jun 17, 2003 at 01:11:49PM -0500, Ron Johnson wrote:

I do recommend a a local checking like watchdog or mon, so a restart is
tried before the takeover. And I'd make sure the primary machine stays
down.

This is going to sound bad to users of Open Source OSs and databases,
but for all work that has to go into clustering machines and making
databases work with them...
...

Which indeed is load balancing again. I thought we were talking about a
simple failover solution. Yes, I know VMS can do that as well. :-)

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De
ICQ: 179140304, AIM: michaelmeskes, Jabber: meskes@jabber.org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

Ron Johnson <ron.l.johnson@cox.net> writes:

On Tue, 2003-06-17 at 12:05, Michael Meskes wrote:

On Tue, Jun 17, 2003 at 03:41:45PM +0200, Daniel Seichter wrote:

I am looking for a hot spare, so if one server crashed, the
second will "spare" it, because if this database will be down
(down is meant for longer than 2 hours) more than two other
databases will not continue working (they could continue working,
but without new data, so it will be senseless).

Not sure what you mean. Shall the second machine take over? Since
this should be hot 2 hours is a lot of time. Using a private
network you can detect failures almost immediately.

I do recommend a a local checking like watchdog or mon, so a
restart is tried before the takeover. And I'd make sure the primary
machine stays down.

This is going to sound bad to users of Open Source OSs and
databases, but for all work that has to go into clustering machines
and making databases work with them...

Why not use a clustered-by-design OS like VMS? It is very easy to
put a couple of dual-Alpha boxen cluster-connected via fiber to SCSI
devices. A cluster-aware relational database like Rdb runs on all
nodes of a cluster in a totally shared-disk environment. While both
nodes are working fine, half of the work goes to either node, and if
one node goes down, the other node still does all the work.

I can't speak for everyone else, but I can tell you *my* reasons for
going with PostgreSQL as opposed to a fancier solution like RDB on
VMS, or Oracle on Solaris, or DB2 on whatever IBM platform sounds
interesting today. PostgreSQL does what I need it to do without
breaking the bank. Sure, it's a little extra work getting PostgreSQL
to do something like hot failover (or load balancing), but when you
can't afford the other options you make do with what you have.

Besides which, PostgreSQL on x86 hardware is almost certainly the best
value around. No one touches it on a price/performance basis, and
PostgreSQL has an impressive array of features. For example, can I
connect my Zope application server running Linux to RDB running on
VMS? I don't believe I can (Oracle would work, however). PostgreSQL
plays nicely with just about any set of development tools you might
care to mention.

Jason

On Tue, 2003-06-17 at 13:20, Michael Meskes wrote:

On Tue, Jun 17, 2003 at 01:11:49PM -0500, Ron Johnson wrote:

I do recommend a a local checking like watchdog or mon, so a restart is
tried before the takeover. And I'd make sure the primary machine stays
down.

This is going to sound bad to users of Open Source OSs and databases,
but for all work that has to go into clustering machines and making
databases work with them...
...

Which indeed is load balancing again. I thought we were talking about a
simple failover solution. Yes, I know VMS can do that as well. :-)

How quickly do the (h/w and manpower) costs of "simple failover"
escalate?

-- 
+-----------------------------------------------------------+
| Ron Johnson, Jr.     Home: ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
|                                                           |
| "Oh, great altar of passive entertainment, bestow upon me |
|  thy discordant images at such speed as to render linear  |
|  thought impossible" (Calvin, regarding TV)               |
+-----------------------------------------------------------

On Tue, 2003-06-17 at 14:01, Jason Earl wrote:

Ron Johnson <ron.l.johnson@cox.net> writes:

On Tue, 2003-06-17 at 12:05, Michael Meskes wrote:

On Tue, Jun 17, 2003 at 03:41:45PM +0200, Daniel Seichter wrote:

[snip]

This is going to sound bad to users of Open Source OSs and
databases, but for all work that has to go into clustering machines
and making databases work with them...

Why not use a clustered-by-design OS like VMS? It is very easy to
put a couple of dual-Alpha boxen cluster-connected via fiber to SCSI
devices. A cluster-aware relational database like Rdb runs on all
nodes of a cluster in a totally shared-disk environment. While both
nodes are working fine, half of the work goes to either node, and if
one node goes down, the other node still does all the work.

I can't speak for everyone else, but I can tell you *my* reasons for
going with PostgreSQL as opposed to a fancier solution like RDB on
VMS, or Oracle on Solaris, or DB2 on whatever IBM platform sounds
interesting today. PostgreSQL does what I need it to do without
breaking the bank. Sure, it's a little extra work getting PostgreSQL
to do something like hot failover (or load balancing), but when you
can't afford the other options you make do with what you have.

Disregarding clustering, I agree with you completely.

Besides which, PostgreSQL on x86 hardware is almost certainly the best
value around. No one touches it on a price/performance basis, and
PostgreSQL has an impressive array of features. For example, can I
connect my Zope application server running Linux to RDB running on
VMS? I don't believe I can (Oracle would work, however). PostgreSQL
plays nicely with just about any set of development tools you might
care to mention.

If it can connect via SQL*Net or ODBC, Rdb will talk to it.

-- 
+-----------------------------------------------------------+
| Ron Johnson, Jr.     Home: ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
|                                                           |
| "Oh, great altar of passive entertainment, bestow upon me |
|  thy discordant images at such speed as to render linear  |
|  thought impossible" (Calvin, regarding TV)               |
+-----------------------------------------------------------

On Tue, 17 Jun 2003, Jason Earl wrote:

Ron Johnson <ron.l.johnson@cox.net> writes:

On Tue, 2003-06-17 at 12:05, Michael Meskes wrote:

On Tue, Jun 17, 2003 at 03:41:45PM +0200, Daniel Seichter wrote:

I am looking for a hot spare, so if one server crashed, the
second will "spare" it, because if this database will be down
(down is meant for longer than 2 hours) more than two other
databases will not continue working (they could continue working,
but without new data, so it will be senseless).

Not sure what you mean. Shall the second machine take over? Since
this should be hot 2 hours is a lot of time. Using a private
network you can detect failures almost immediately.

I do recommend a a local checking like watchdog or mon, so a
restart is tried before the takeover. And I'd make sure the primary
machine stays down.

This is going to sound bad to users of Open Source OSs and
databases, but for all work that has to go into clustering machines
and making databases work with them...

Why not use a clustered-by-design OS like VMS? It is very easy to
put a couple of dual-Alpha boxen cluster-connected via fiber to SCSI
devices. A cluster-aware relational database like Rdb runs on all
nodes of a cluster in a totally shared-disk environment. While both
nodes are working fine, half of the work goes to either node, and if
one node goes down, the other node still does all the work.

I can't speak for everyone else, but I can tell you *my* reasons for
going with PostgreSQL as opposed to a fancier solution like RDB on
VMS, or Oracle on Solaris, or DB2 on whatever IBM platform sounds
interesting today. PostgreSQL does what I need it to do without
breaking the bank. Sure, it's a little extra work getting PostgreSQL
to do something like hot failover (or load balancing), but when you
can't afford the other options you make do with what you have.

Keep in mind, if you need more performance than X86, you can always buy a
used E10K online for ~$24,000 or so (there's one on Eb*y now with 20 400
MHz CPUs for less than that, and it's been coming up week after week with
no buyers.)

Older mainframes are there for $5,000 or so as well.

The linux kernel is supposed to have hot swappable hardware support in it
eventually for both those platforms, so you've got your 24/7 with no need
for a second box. Of course, I'm sure for $5,000 you could afford to buy
two mainframes and fail them over yourself on the one time every fifty
years or so one fails. :-)

I'm certain the license fees for rdb and VMS are no small amount, and with
the E10k or mainframe, you own it outright.

On 18 Jun 2003 at 5:36, scott.marlowe wrote:

The linux kernel is supposed to have hot swappable hardware support in it
eventually for both those platforms, so you've got your 24/7 with no need

Linux supports hot-swappable hardware? As in swappng CPU/RAM/Add on cards on
the fly?

That is news to me. Could you point me to more resources on this?

Bye
Shridhar

--
work, n.: The blessed respite from screaming kids and soap operas for which you
actually get paid.

At 14:41 18.06.2003, Shridhar Daithankar said:
--------------------[snip]--------------------

Linux supports hot-swappable hardware? As in swappng CPU/RAM/Add on cards on
the fly?

That is news to me. Could you point me to more resources on this?

--------------------[snip]--------------------

AFAIK swappable hardware needs to be supported by the hardware as well.
Currently hard disks and power supplies can be hot-swapped, I never heard
of the possibility to hot-swap directly on the data bus (memory, CPU, slot
cards).

As for SCSI disks and Power supplies, Linux supports hot swap. Check out
Dell servers for example.

--

O Ernest E. Vogelsinger

(\) ICQ #13394035
^ http://www.vogelsinger.at/

You can hotswap PCI cards that follow the CompactPCI specification. This
has been in the kernel for years, and I think was originally authored by
Compaq.

Jon

On Wed, 18 Jun 2003, Ernest E Vogelsinger wrote:

Linux supports hot-swappable hardware? As in swappng CPU/RAM/Add on cards on
the fly?

No CPUs or RAM. The problem isn't the kernel, realy, it's x86 hardware.
The kernel guys aren't going to bother to try to support it until there's
hardware support.

However for PCI cards, USB, Firewire, and SCSI devices, Linux has had
hotswap capability for a long while now.

Jon