pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWarm standby server
10+ messages / 6 participants
[nested] [flat]
* Warm standby server
@ 2008-06-26 15:19 Scott Whitney <swhitney@journyx.com>
0 siblings, 1 reply; 10+ messages in thread
From: Scott Whitney @ 2008-06-26 15:19 UTC (permalink / raw)
To: pgsql-admin
I've got 3 different database servers (db01, db02 and db03).
I would like to have a WAL standby server that replays logs for all 3 in
case one goes down, so I can promote that particular server.
Can I do this by installing 3 separate postmasters on this machine?
Obviously, if 2 went down at the same time, I'd have to do some magic to
bring up another machine, but I'm not sure that's a concern.
It'll be a RedHat RHEL-4 box on pg 8.1x
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Warm standby server
@ 2008-06-26 16:06 Simon Riggs <simon@2ndquadrant.com>
parent: Scott Whitney <swhitney@journyx.com>
0 siblings, 1 reply; 10+ messages in thread
From: Simon Riggs @ 2008-06-26 16:06 UTC (permalink / raw)
To: Scott Whitney <swhitney@journyx.com>; +Cc: pgsql-admin
On Thu, 2008-06-26 at 10:19 -0500, Scott Whitney wrote:
> I've got 3 different database servers (db01, db02 and db03).
>
> I would like to have a WAL standby server that replays logs for all 3 in
> case one goes down, so I can promote that particular server.
>
> Can I do this by installing 3 separate postmasters on this machine?
> Obviously, if 2 went down at the same time, I'd have to do some magic to
> bring up another machine, but I'm not sure that's a concern.
Yes, that will work.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Warm standby server
@ 2008-06-26 17:51 Montaseri <montaseri@gmail.com>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 10+ messages in thread
From: Montaseri @ 2008-06-26 17:51 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Scott Whitney <swhitney@journyx.com>; pgsql-admin
I am not so sure of this arrangement's mertis
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Warm standby server
@ 2008-06-26 18:01 Scott Whitney <swhitney@journyx.com>
parent: Montaseri <montaseri@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Scott Whitney @ 2008-06-26 18:01 UTC (permalink / raw)
To: 'Montaseri' <montaseri@gmail.com>; 'Simon Riggs' <simon@2ndquadrant.com>; +Cc: pgsql-admin
Fully agreed, and it's just a concept at the moment. After I have a
prototype standby working next week in the first place, we'll be discussing
those very merits.
A 2nd question: Is it possible to have 2 standby servers with a single
master duplicating to standby1 (at my coloc), and standby2 (at my office)?
Assume no auto-failover.
-----Original Message-----
From: Montaseri [mailto:montaseri@gmail.com]
Sent: Jun 26, 2008 12:51 PM
To: Simon Riggs
Cc: Scott Whitney; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Warm standby server
I am not so sure of this arrangement's mertis
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Warm standby server
@ 2008-06-26 18:20 Montaseri <montaseri@gmail.com>
parent: Scott Whitney <swhitney@journyx.com>
1 sibling, 2 replies; 10+ messages in thread
From: Montaseri @ 2008-06-26 18:20 UTC (permalink / raw)
To: Scott Whitney <swhitney@journyx.com>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-admin
While I am not an expert on WAL, but again I question the merits of such
sophisticated HA configuration. Of course there are use cases for such
configs, but I am only advocating best price performance kind of mentality
As WAL writes the journals all the way down to the disk (ie write thru and
not write behind) before ack-ing toward the next step in a DB operation,
increasing the number of mirrors (one production, one on-site, one off-site,
I count 3 plexes here) will prolong each operation, with the following
exponentially increasing write latencies
production DB writes are at the rate of SCSI, SATA or system bus (30 MBps)
on-site DB writes are at the rate of LAN (10 MBps)
off-site DB writes are at the rate of WAN (200 KBps)
Then if a three-way WAL writes is considered completed after the last WAN
write, then you have effectively lowered your performance to 200 KBytes per
sec writes. Now the gain. If the building gets destroyed, my data is
protected. Ok. what kind of business are we running in that building? ....
what is the rate of writes to database vs probability of building coming
down vs value of data from 2 hours ago vs 10 seconds ago.
Thanks
Medi
On Thu, Jun 26, 2008 at 11:01 AM, Scott Whitney <swhitney@journyx.com>
wrote:
> Fully agreed, and it's just a concept at the moment. After I have a
> prototype standby working next week in the first place, we'll be discussing
> those very merits.
>
> A 2nd question: Is it possible to have 2 standby servers with a single
> master duplicating to standby1 (at my coloc), and standby2 (at my office)?
> Assume no auto-failover.
>
> -----Original Message-----
> From: Montaseri [mailto:montaseri@gmail.com]
> Sent: Jun 26, 2008 12:51 PM
> To: Simon Riggs
> Cc: Scott Whitney; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Warm standby server
>
> I am not so sure of this arrangement's mertis
>
> From HA (High Availability) point of view, the host/server is a single
> point
> of failure which will bring your entire infrastructure down if any of the
> server hardware components fail.
>
> From Performance point of view, you have increased the load on your server
> by 3 folds as all instances would be using your I/O bandwidth to write to
> secondary storage
>
> Given $300 to $400 price of headless servers these days, its much
> economical
> to split the workload on three boxes
>
> Cheers
> Medi
>
>
> On Thu, Jun 26, 2008 at 9:06 AM, Simon Riggs <simon@2ndquadrant.com>
> wrote:
>
>
>
> On Thu, 2008-06-26 at 10:19 -0500, Scott Whitney wrote:
> > I've got 3 different database servers (db01, db02 and db03).
> >
> > I would like to have a WAL standby server that replays logs for
> all 3 in
> > case one goes down, so I can promote that particular server.
> >
> > Can I do this by installing 3 separate postmasters on this
> machine?
> > Obviously, if 2 went down at the same time, I'd have to do some
> magic to
> > bring up another machine, but I'm not sure that's a concern.
>
>
> Yes, that will work.
>
> --
> Simon Riggs www.2ndQuadrant.com
> PostgreSQL Training, Services and Support
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Warm standby server
@ 2008-06-26 18:28 Scott Whitney <swhitney@journyx.com>
parent: Montaseri <montaseri@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Scott Whitney @ 2008-06-26 18:28 UTC (permalink / raw)
To: 'Montaseri' <montaseri@gmail.com>; +Cc: 'Simon Riggs' <simon@2ndquadrant.com>; pgsql-admin
I was thinking along the same lines. However, having 2 standby servers is a
must for us. If my colocation center gets razed to the ground, I need to
have a standby server elsewhere. Having one at the colocation center is for
soft/hardware disasters on any primary. Having one at my office is for
facility disasters at the colocation center.
I envision it working like this:
The archive command pseudocode will be:
rsync <mywalfile> someone@abox
The standby slave servers will then continually rsync that directory while
in recovery mode.
Reading the docs, it looks as if this should work.
It's also my understanding that the archive command happens asynchronously,
so it shouldn't affect the overall performance of the primary. Regardless,
my proposed solution should handle such a scenario.
-----Original Message-----
From: Montaseri [mailto:montaseri@gmail.com]
Sent: Jun 26, 2008 1:21 PM
To: Scott Whitney
Cc: Simon Riggs; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Warm standby server
While I am not an expert on WAL, but again I question the merits of such
sophisticated HA configuration. Of course there are use cases for such
configs, but I am only advocating best price performance kind of mentality
As WAL writes the journals all the way down to the disk (ie write thru and
not write behind) before ack-ing toward the next step in a DB operation,
increasing the number of mirrors (one production, one on-site, one off-site,
I count 3 plexes here) will prolong each operation, with the following
exponentially increasing write latencies
production DB writes are at the rate of SCSI, SATA or system bus (30 MBps)
on-site DB writes are at the rate of LAN (10 MBps)
off-site DB writes are at the rate of WAN (200 KBps)
Then if a three-way WAL writes is considered completed after the last WAN
write, then you have effectively lowered your performance to 200 KBytes per
sec writes. Now the gain. If the building gets destroyed, my data is
protected. Ok. what kind of business are we running in that building? ....
what is the rate of writes to database vs probability of building coming
down vs value of data from 2 hours ago vs 10 seconds ago.
Thanks
Medi
On Thu, Jun 26, 2008 at 11:01 AM, Scott Whitney <swhitney@journyx.com>
wrote:
Fully agreed, and it's just a concept at the moment. After I have a
prototype standby working next week in the first place, we'll be
discussing
those very merits.
A 2nd question: Is it possible to have 2 standby servers with a
single
master duplicating to standby1 (at my coloc), and standby2 (at my
office)?
Assume no auto-failover.
-----Original Message-----
From: Montaseri [mailto:montaseri@gmail.com]
Sent: Jun 26, 2008 12:51 PM
To: Simon Riggs
Cc: Scott Whitney; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Warm standby server
I am not so sure of this arrangement's mertis
From HA (High Availability) point of view, the host/server is a
single point
of failure which will bring your entire infrastructure down if any
of the
server hardware components fail.
From Performance point of view, you have increased the load on your
server
by 3 folds as all instances would be using your I/O bandwidth to
write to
secondary storage
Given $300 to $400 price of headless servers these days, its much
economical
to split the workload on three boxes
Cheers
Medi
On Thu, Jun 26, 2008 at 9:06 AM, Simon Riggs <simon@2ndquadrant.com>
wrote:
On Thu, 2008-06-26 at 10:19 -0500, Scott Whitney wrote:
> I've got 3 different database servers (db01, db02 and
db03).
>
> I would like to have a WAL standby server that replays logs
for
all 3 in
> case one goes down, so I can promote that particular
server.
>
> Can I do this by installing 3 separate postmasters on this
machine?
> Obviously, if 2 went down at the same time, I'd have to do
some
magic to
> bring up another machine, but I'm not sure that's a
concern.
Yes, that will work.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
--
Sent via pgsql-admin mailing list
(pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Warm standby server
@ 2008-06-26 18:37 Simon Riggs <simon@2ndquadrant.com>
parent: Scott Whitney <swhitney@journyx.com>
1 sibling, 0 replies; 10+ messages in thread
From: Simon Riggs @ 2008-06-26 18:37 UTC (permalink / raw)
To: Scott Whitney <swhitney@journyx.com>; +Cc: 'Montaseri' <montaseri@gmail.com>; pgsql-admin
On Thu, 2008-06-26 at 13:01 -0500, Scott Whitney wrote:
> A 2nd question: Is it possible to have 2 standby servers with a single
> master duplicating to standby1 (at my coloc), and standby2 (at my office)?
> Assume no auto-failover.
Yes, that works too.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Warm standby server
@ 2008-06-29 13:53 Chander Ganesan <Chander@otg-nc.com>
parent: Montaseri <montaseri@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Chander Ganesan @ 2008-06-29 13:53 UTC (permalink / raw)
To: Montaseri <montaseri@gmail.com>; +Cc: Scott Whitney <swhitney@journyx.com>; Simon Riggs <simon@2ndquadrant.com>; pgsql-admin
Scott & Montaseri,
Montaseri wrote:
> While I am not an expert on WAL, but again I question the merits of
> such sophisticated HA configuration. Of course there are use cases for
> such configs, but I am only advocating best price performance kind of
> mentality
>
> As WAL writes the journals all the way down to the disk (ie write thru
> and not write behind) before ack-ing toward the next step in a DB
> operation, increasing the number of mirrors (one production, one
> on-site, one off-site, I count 3 plexes here) will prolong each
> operation, with the following exponentially increasing write latencies
"traditional" warm-standby involves replicating the *archived* (read:
not currently-in-use) WAL to the standby servers, something that doesn't
require a synchronous write operation, and something that could be
spread out over a period of time. However, you might have an issue if
the system copies across WAL's slower than new ones are generated.
Scott, you should be aware that the mechanism you are proposing has the
potential to "lose" the transactions that had committed, but had not yet
been archived. That would most likely be the changes in the "currently
being written" WAL file - which hadn't been filled up, and thus had not
yet been archived.
For your colo standby server, you might consider using some synchronous
mechanism for WAL storage. Such a mechanism would have an impact on
server performance but would allow you to achive durability of committed
transactions. I'll actually be talking a bit about this at the PGDay of
Linux World (August 5th)...
--
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC 27560
919-463-0999/877-258-8987
http://www.otg-nc.com
Ask me about Expert PostgreSQL, PostGIS, and other Open Source training.
^ permalink raw reply [nested|flat] 10+ messages in thread
* warm standby server
@ 2008-12-10 12:23 zz_11@mail.bg
0 siblings, 1 reply; 10+ messages in thread
From: zz_11@mail.bg @ 2008-12-10 12:23 UTC (permalink / raw)
To: pgsql-admin
Hi,
I am using pg 8.3 and configured warm standby server. It is working
well, but my db is relative big ( ~ 40 GB).
Is it possible to stop running ?"Warm standby server" and? when it
starts again to continue executing WALs from the stop moment.
I'm using following command to stop the server:
kill -SIGINT `head -1 /POSTGRESQL_DATA_DIR/postmaster.pid`
but when it starts again there is broken link
"/POSTGRESQL_DATA_DIR/pg_xlog/RECOVERYHISTORY" which points to the
following missing file "/WAL_ARCHIVE_DIR/00000001.history"
i wonder if it is fatal or not?
I will not to start the process ot making the base backup and send
it to my warm standby server. My warm standby server is on diferent
location connected via slow connection.
regards, Ivan.
-------------------------------------
Създай профил днес и можеш да спечелиш безплатен самолетен билет за Лондон!
Възползвай се от нашите намаления за Нова Година.Крайният срок наближава-
побързайтe!Само сега–промоционални цени и на Студентските ни програми в САЩ!
http://charter.bg/catalog/index.php
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: warm standby server
@ 2008-12-10 17:56 Simon Riggs <simon@2ndQuadrant.com>
parent: zz_11@mail.bg
0 siblings, 0 replies; 10+ messages in thread
From: Simon Riggs @ 2008-12-10 17:56 UTC (permalink / raw)
To: zz_11@mail.bg; +Cc: pgsql-admin
On Wed, 2008-12-10 at 14:23 +0200, zz_11@mail.bg wrote:
> Hi,
>
> I am using pg 8.3 and configured warm standby server. It is working
> well, but my db is relative big ( ~ 40 GB).
>
> Is it possible to stop running "Warm standby server" and when it
> starts again to continue executing WALs from the stop moment.
Yes
> I'm using following command to stop the server:
>
> kill -SIGINT `head -1 /POSTGRESQL_DATA_DIR/postmaster.pid`
Use pg_ctl
> but when it starts again there is broken link
> "/POSTGRESQL_DATA_DIR/pg_xlog/RECOVERYHISTORY" which points to the
> following missing file "/WAL_ARCHIVE_DIR/00000001.history"
>
> i wonder if it is fatal or not?
That's normal.
> I will not to start the process ot making the base backup and send it
> to my warm standby server. My warm standby server is on diferent
> location connected via slow connection.
That's OK.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2008-12-10 17:56 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-06-26 15:19 Warm standby server Scott Whitney <swhitney@journyx.com>
2008-06-26 16:06 ` Simon Riggs <simon@2ndquadrant.com>
2008-06-26 17:51 ` Montaseri <montaseri@gmail.com>
2008-06-26 18:01 ` Scott Whitney <swhitney@journyx.com>
2008-06-26 18:20 ` Montaseri <montaseri@gmail.com>
2008-06-26 18:28 ` Scott Whitney <swhitney@journyx.com>
2008-06-29 13:53 ` Chander Ganesan <Chander@otg-nc.com>
2008-06-26 18:37 ` Simon Riggs <simon@2ndquadrant.com>
2008-12-10 12:23 warm standby server zz_11@mail.bg
2008-12-10 17:56 ` Re: warm standby server Simon Riggs <simon@2ndQuadrant.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