Re: [GENERAL] High Availability

[Tatsuo Ishii]

> On 07/20/2016 11:53 AM, dangal wrote:
>>
>> Dear , I have a question for them , in our work we have an environment
>> with
>> streaming replication and everything works in the best way , we are
>> trying
>> to implement high availability and for that we try to use pgpool and
>> we
>> could not get it to work with md5 authentication
> 
> The errors/problems where?
> 
>> The area of systems my company consulted us that we viability of using
>> DRBD
>> The truth is that looking at your site only see old references to that
>> product , without being pgpool recommending us to make high
>> availability?
> 
> With out more information this is throwing darts at a board, so:
> 
> What version(s) of Postgres?
> 
> What version(s) of pgpool?
> 
> What is your setup, number of masters/slaves and layout?
> 
> What is your definition of high availability?

Also the original poster might want to take a look at FAQ:

http://pgpool.net/mediawiki/index.php/FAQ

Especially this:

http://pgpool.net/mediawiki/index.php/FAQ#I_created_pool_hba.conf_and_pool_passwd_to_enable_md5_authentication_through_pgpool-II_but_it_does_not_work._Why.3F

and this:

http://pgpool.net/mediawiki/index.php/FAQ#md5_authentication_does_not_work._Please_help

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability

[Adrian Klaver]

On 07/20/2016 11:53 AM, dangal wrote:


Dear , I have a question for them , in our work we have an environment with
streaming replication and everything works in the best way , we are trying
to implement high availability and for that we try to use pgpool and we
could not get it to work with md5 authentication


The errors/problems where?


The area of systems my company consulted us that we viability of using DRBD
The truth is that looking at your site only see old references to that
product , without being pgpool recommending us to make high availability?


With out more information this is throwing darts at a board, so:

What version(s) of Postgres?

What version(s) of pgpool?

What is your setup, number of masters/slaves and layout?

What is your definition of high availability?




Thank you very much



--
View this message in context: 
http://postgresql.nabble.com/High-Availability-tp5912759.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] High Availability

[dangal]

Dear , I have a question for them , in our work we have an environment with
streaming replication and everything works in the best way , we are trying
to implement high availability and for that we try to use pgpool and we
could not get it to work with md5 authentication
The area of systems my company consulted us that we viability of using DRBD
The truth is that looking at your site only see old references to that
product , without being pgpool recommending us to make high availability?
Thank you very much



--
View this message in context: 
http://postgresql.nabble.com/High-Availability-tp5912759.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High availability and load balancing ...

[Venkata Balaji N]

On Thu, Jun 9, 2016 at 8:01 PM, Sunil N Shinde 
wrote:

> Thanks Venkata.
>
>
>
> I am considering latest version now i.e. 9.4 or 9.5 on Linux 6.
>
> Is there any difference in setup from 9.1 to 9.5?
>

There is no difference in the setup. Streaming Replication in the version
9.5 is a lot better with a lot of bug fixes specific to streaming
replication and with a few extra parameters compared to the version 9.1.
Please refer to postgresql documentation.

Regards,

Venkata B N
Fujitsu Australia

Re: [GENERAL] High availability and load balancing ...

[Sunil N Shinde]

Thanks Venkata.

I am considering latest version now i.e. 9.4 or 9.5 on Linux 6.
Is there any difference in setup from 9.1 to 9.5?


Thanks & Regards,
Sunil N Shinde

From: Venkata Balaji N [mailto:nag1...@gmail.com]
Sent: 08 June 2016 12:46
To: Sunil N Shinde 
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] High availability and load balancing ...


I need to do the setup for High availability function.
Also want to implement load balancing for 02 nodes.

You will have to build streaming replication which was introduced in 
PostgreSQL-9.0

I think PGPool will be require for that. Can I use PGPool without cost.

pgpool-II is an open source tool which can be used for connection pooling and 
load balancing.

 Can I get the basic steps to do this setup?

Database--  Postgresql 9.1
 OS --  Linux 6

Below is the link which explains the basic steps to setup "streaming 
replication"

https://www.postgresql.org/docs/9.1/static/warm-standby.html

By the way, version 9.1 is very old and will reach end-of-life soon. You are 4 
major versions behind, did you consider using latest version ?

Regards,
Venkata B N

Fujitsu Australia

Re: [GENERAL] High availability and load balancing ...

[Tatsuo Ishii]

> Hi,
> 
> I need to do the setup for High availability function.
> Also want to implement load balancing for 02 nodes.
> I think PGPool will be require for that. Can I use PGPool without cost.

Yes, you can. Pgpool-II is an open source product.

> Can I get the basic steps to do this setup?

http://www.pgpool.net/pgpool-web/contrib_docs/simple_sr_setting2_3.3/index.html

It's a little bit out dated but mostly useful for a configuration
using the latest pgpool-II version and PostgreSQL 9.1.

If you have further questions regarding pgpool-II, I recommend you to
move pgpool-II specific mailing list.

http://www.pgpool.net/mailman/listinfo/pgpool-general

> Database--  Postgresql 9.1
>  OS --  Linux 6
> 
> 
> Thanks & Regards,
> Sunil N Shinde

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High availability and load balancing ...

[Venkata Balaji N]

>
>
>
> I need to do the setup for High availability function.
>
> Also want to implement load balancing for 02 nodes.
>

You will have to build streaming replication which was introduced in
PostgreSQL-9.0


> I think PGPool will be require for that. Can I use PGPool without cost.
>

pgpool-II is an open source tool which can be used for connection pooling
and load balancing.


>  Can I get the basic steps to do this setup?
>
>
>
> Database--  Postgresql 9.1
>
>  OS --  Linux 6
>

Below is the link which explains the basic steps to setup "streaming
replication"

https://www.postgresql.org/docs/9.1/static/warm-standby.html

By the way, version 9.1 is very old and will reach end-of-life soon. You
are 4 major versions behind, did you consider using latest version ?

Regards,
Venkata B N

Fujitsu Australia

[GENERAL] High availability and load balancing ...

[Sunil N Shinde]

Hi,

I need to do the setup for High availability function.
Also want to implement load balancing for 02 nodes.
I think PGPool will be require for that. Can I use PGPool without cost.

Can I get the basic steps to do this setup?

Database--  Postgresql 9.1
 OS --  Linux 6


Thanks & Regards,
Sunil N Shinde

Re: [GENERAL] High Availability Cluster

[Sameer Kumar]

On Thu, Dec 4, 2014 at 12:32 PM, Tatsuo Ishii  wrote:

> No. Just the implementation of watchdog has been changed (more
> precisely new life check mode added). In recent versions (since 3.3)
> it uses UDP packet for life check of pgpool, which is pretty much
> similar to heartbeat.
>
>
​Thanks for adding the details.

​


> > If you are up for a commercial product, you may want to consider using
> > something like EnterpriseDB's failover manager.
>
> Commercial support for pgpool-II is provided by the way.
>
​
Noted :)​


Best Regards,

*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533

M: *+65 8110 0350*  T: +65 6438 3504 | www.ashnik.com

*[image: icons]*



[image: Email patch] 



This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).

Re: [GENERAL] High Availability Cluster

[Tatsuo Ishii]

> On Thu, Nov 27, 2014 at 7:26 PM, Maila Fatticcioni > wrote:
> 
>> I was wondering if there is another way to set up a complete cluster
>> that would manage in case of failure:
>>
>> * the relocation of the VIP (virtual ip address)
>> * the relocation of the main instance of Postgresql
>>
> 
> ​Consider trying pgpool. It has an HA mode (and I think the latest version
> works using a heartbeat, the older one uses watchdog).

No. Just the implementation of watchdog has been changed (more
precisely new life check mode added). In recent versions (since 3.3)
it uses UDP packet for life check of pgpool, which is pretty much
similar to heartbeat.

> If you are up for a commercial product, you may want to consider using
> something like EnterpriseDB's failover manager.

Commercial support for pgpool-II is provided by the way.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability Cluster

[Sameer Kumar]

On Thu, Nov 27, 2014 at 7:26 PM, Maila Fatticcioni  wrote:

> I was wondering if there is another way to set up a complete cluster
> that would manage in case of failure:
>
> * the relocation of the VIP (virtual ip address)
> * the relocation of the main instance of Postgresql
>

​Consider trying pgpool. It has an HA mode (and I think the latest version
works using a heartbeat, the older one uses watchdog).

If you are up for a commercial product, you may want to consider using
something like EnterpriseDB's failover manager.
​


Best Regards,

*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533

M: *+65 8110 0350*  T: +65 6438 3504 | www.ashnik.com

*[image: icons]*



[image: Email patch] 



This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).

Re: [GENERAL] High Availability Cluster

[Maila Fatticcioni]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/27/2014 12:34 PM, Andreas Kretschmer wrote:
> Maila Fatticcioni  wrote:
> 
>> -BEGIN PGP SIGNED MESSAGE- Hash: SHA1
>> 
>> Hello. I need to set up a high availability cluster with two
>> servers. I have looked for the best configuration I could use but
>> I cannot find anything I like. I cannot used the DRBD option and
>> I have to use
>> 
>> I was wondering if there is another way to set up a complete
>> cluster that would manage in case of failure:
>> 
>> * the relocation of the VIP (virtual ip address) * the relocation
>> of the main instance of Postgresql
>> 
>> Best Regards, Maila Fatticcioni
> 
> Why not DRBD & Pacemaker?
> 
> 
> Andreas
> 

We would prefer to not use DRBD to speed up the performance.
Is there any other available solution?

Maila

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlR3UlMACgkQi2q3wPb3FcNA9gCfd/veNBuNkmPSIbjdkHqeRdgK
CDMAoLul8+oO6CI9L9/7uCMlblLab9qP
=9OrT
-END PGP SIGNATURE-


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability Cluster

[Andreas Kretschmer]

Maila Fatticcioni  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> Hello.
> I need to set up a high availability cluster with two servers. I have
> looked for the best configuration I could use but I cannot find
> anything I like. I cannot used the DRBD option and I have to use
> 
> I was wondering if there is another way to set up a complete cluster
> that would manage in case of failure:
> 
> * the relocation of the VIP (virtual ip address)
> * the relocation of the main instance of Postgresql
> 
> Best Regards,
> Maila Fatticcioni

Why not DRBD & Pacemaker?


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] High Availability Cluster

[Maila Fatticcioni]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hello.
I need to set up a high availability cluster with two servers. I have
looked for the best configuration I could use but I cannot find
anything I like. I cannot used the DRBD option and I have to use
Ubuntu 14.04 as OS.
In the past I have set up some clusters using the replication of
Postgresql 9.3. I put one node as master and one node as an hot
standby replication. To complete the installation I have configured
Heartbeat + Pacemaker in order to manage the relocation of the virtual
IP used by the master node and the relocation from the master to the
slave instance of Postgresql.

I was wondering if there is another way to set up a complete cluster
that would manage in case of failure:

* the relocation of the VIP (virtual ip address)
* the relocation of the main instance of Postgresql

Best Regards,
Maila Fatticcioni
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlR3CkgACgkQi2q3wPb3FcNKWACgqfpq7+z7h7qK3z2+H9Qg4Cdw
KWkAn0e1cjtVshTTRYt2GEtt5Wa4FRfz
=5ea1
-END PGP SIGNATURE-


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability with Postgres

[Craig Ringer]

On 23/06/10 03:05, John R Pierce wrote:

> yeah.  generally when money is involved in the transactions, you gotta
> stick to the 'no committed data lost ever'.  there's plenty of other use
> cases for that too.

2PC is sometimes a reasonable alternative to shared-storage failover,
though. It can be a lot slower, but it lets you maintain the machines as
completely separate systems with no shared failure points.

--
Craig Ringer

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability with Postgres

[Dimitri Fontaine]

John R Pierce  writes:
> yeah.  generally when money is involved in the transactions, you gotta stick
> to the 'no committed data lost ever'.  there's plenty of other use cases for
> that too.

Well, it's a cost/benefit/risk evaluation you have to make. It'd be bad
news that the cost for covering your risk is more expensive that the
risk itself, meaning there's no benefit walking the extra mile.

Regards,
-- 
dim

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability with Postgres

[John R Pierce]

On 06/22/10 1:58 AM, Dimitri Fontaine wrote:

John R Pierce  writes:
   

failure modes can
include things like failing fans (which will be detected, resulting in a
server shutdown if too many fail), power supply failure (redundant PSUs, but
I've seen the power combining circuitry fail).   Any of these sorts of
failures will result in a failover without corrupting the data.

and of course, intentional planned failovers to do OS maintenance...  you
patch the standby system, fail over to it and verify its good, then patch
the other system.
 

Ah, I see the use case much better now, thank you. And I begin too see
how expensive reaching such a goal is, too. Going from "I can lose this
many transactions" to "No data lost, ever" is at that price, though.
   


yeah.  generally when money is involved in the transactions, you gotta 
stick to the 'no committed data lost ever'.  there's plenty of other use 
cases for that too.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability with Postgres

[Greg Smith]

John R Pierce wrote:
I don't like power cycling servers, so I'd prefer not to use power 
switch based fencing, although I believe my blade box's management 
unit is supported as a power fencing device.


I consider power control fencing to be a secondary resort if you don't 
have hardware where a storage switch fence can be used.  It can be a 
useful implementation for those situations, and not all shared storage 
is attached with a FC switch.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability with Postgres

[Dimitri Fontaine]

John R Pierce  writes:
> failure modes can
> include things like failing fans (which will be detected, resulting in a
> server shutdown if too many fail), power supply failure (redundant PSUs, but
> I've seen the power combining circuitry fail).   Any of these sorts of
> failures will result in a failover without corrupting the data.
>
> and of course, intentional planned failovers to do OS maintenance...  you
> patch the standby system, fail over to it and verify its good, then patch
> the other system.

Ah, I see the use case much better now, thank you. And I begin too see
how expensive reaching such a goal is, too. Going from "I can lose this
many transactions" to "No data lost, ever" is at that price, though.

Regards,
-- 
dim

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability with Postgres

[Devrim GÜNDÜZ]

On Mon, 2010-06-21 at 23:08 -0400, Greg Smith wrote:
> 
> The hard part of shared storage failover is always solving the "shoot 
> the other node in the head problem", to keep a down node from coming 
> back once it's no longer the active one.  In order to do that well,
> you really need to lock the now unavailable node from accessing the
> storage at the hardware level--"fencing"--with disabling its storage
> port being one way to handle that.  Figure out how you're going to do
> that reliably in a way that's integrated into a proper cluster
> manager, and there's no reason you can't do this with PostgreSQL. 

FWIW, I know a prod instances that has 4 PostgreSQL servers (on 4
different hardware, I mean) running on Red Hat Cluster Suite, and it has
been running more than 2 years w/o any issues. The only issues were
related to RHCS+HP hardware, but as of RHEL 5.5, all issues are
resolved.
-- 
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] High Availability with Postgres

[John R Pierce]

On 06/21/10 8:08 PM, Greg Smith wrote:
The hard part of shared storage failover is always solving the "shoot 
the other node in the head problem", to keep a down node from coming 
back once it's no longer the active one.  In order to do that well, 
you really need to lock the now unavailable node from accessing the 
storage at the hardware level--"fencing"--with disabling its storage 
port being one way to handle that.  Figure out how you're going to do 
that reliably in a way that's integrated into a proper cluster 
manager, and there's no reason you can't do this with PostgreSQL.


In my dev-lab tests of some clusters, I used the QLogic 5600 FC switch 
that connects my motly collection of servers...  I used RHCS for one 
test, it supported the qlogic via telnet...   I created two zone sets in 
the qlogic, one for each state, with the standby host blocked from 
accessing the LUN, and the cluster manager used telnet to talk to the 
switch.I ran heartbeats over two seperate ethernets (one was the lab 
LAN segment, the other was a private switch i have all the servers 
connected to for various tests, and such).  The qlogic switch also 
had another zoneset for all sorts of other servers and storage which 
wasn't affected by these clustering tests.


I don't like power cycling servers, so I'd prefer not to use power 
switch based fencing, although I believe my blade box's management unit 
is supported as a power fencing device.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability with Postgres

[Greg Smith]

John R Pierce wrote:
the commercial cluster software vendors insist on using dedicated 
connections for the heartbeat messages between the cluster members and 
insist on having fencing capabilities (for instance, disabling the 
fiber switch port of the formerly active server and enabling the port 
for the to-be-activated server).  with linux-ha and heartbeat, you're 
on your own.


This is worth highlighting.  As John points out, it's straighforward to 
build a shared storage implementation using PostgreSQL and either one of 
the commercial clustering systems or using Linux-HA.  And until 
PostgreSQL gets fully synchronous replication, it's a viable alternate 
solution for "must not lose a transaction" deployments when the storage 
used is much more reliable than the nodes.


The hard part of shared storage failover is always solving the "shoot 
the other node in the head problem", to keep a down node from coming 
back once it's no longer the active one.  In order to do that well, you 
really need to lock the now unavailable node from accessing the storage 
at the hardware level--"fencing"--with disabling its storage port being 
one way to handle that.  Figure out how you're going to do that reliably 
in a way that's integrated into a proper cluster manager, and there's no 
reason you can't do this with PostgreSQL.


There's a description of the fencing options for Linux-HA at 
http://www.clusterlabs.org/doc/crm_fencing.html ; the cheap way to solve 
this problem is to have a UPS that disables the power going to the shot 
node.  Once that's done, you can then safely failover the shared storage 
to another system.  At that point, you can probably even turn back on 
the power, presuming that the now rebooted system will be able to regain 
access to the storage during a fresh system start.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability with Postgres

[John R Pierce]

On 06/21/10 12:23 PM, Dimitri Fontaine wrote:

John R Pierce  writes:
   

Two DB servers will be using a common external storage (with raid).
 

This is also one of the only postgres HA configurations that won't lose
/any/ committed transactions on a failure.  Most all PITR/WAL
replication/Slony/etc configs, the standby storage runs several seconds
behind realtime.
 

I'm not clear on what error case it protects against, though. Either the
data is ok and a single PostgreSQL system will restart fine, or the data
isn't and you're hosed the same with or without the second system.

What's left is hardware failure that didn't compromise the data. I
didn't see much hardware failure yet, granted, but I'm yet to see a
motherboard, some RAM or a RAID controller failing in a way that leaves
behind data you can trust.
   


in most of the HA clusters I've seen, the raid controllers are in the 
SAN, not in the hosts, and they have their own failover, with shared 
write cache, also extensive use of ECC so things like double-bit memory 
errors are detected and treated as a failure.   the sorts of high end 
SANs used in these kinds of systems have 5-9's reliability, through 
extensive use of redundancy, dual port disks, fully redundant 
everything, mirrored caches, etc.


ditto, the servers used in these sorts of clusters have ECC memory, so 
memory failure should be detected rather than passed on blindly in the 
form of corrupted data.   Server grade CPUs, especially the RISC ones, 
have extensive ECC internally on their caches, data busses, etc, so any 
failure there is detected rather than allowed to corrupt data.  failure 
modes can include things like failing fans (which will be detected, 
resulting in a server shutdown if too many fail), power supply failure 
(redundant PSUs, but I've seen the power combining circuitry fail).   
Any of these sorts of failures will result in a failover without 
corrupting the data.


and of course, intentional planned failovers to do OS maintenance...  
you patch the standby system, fail over to it and verify its good, then 
patch the other system.


We had a large HA system at an overseas site fail over once due to 
flooding in the primary computer room caused by a sprinkler system 
failure upstairs.   The SAN was mirrored to a SAN in the 2nd DC (fiber 
inteconnected) and the backup server was also in the second DC across 
campus, so it all failed over gracefully.   This particular system was 
large Sun hardware and big EMC storage, and it was running Oracle rather 
than Postgres.   We've had several big UPS failures at various sites, 
too, ditto HVAC, over a 15 year period.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability with Postgres

[Dimitri Fontaine]

John R Pierce  writes:
>>> Two DB servers will be using a common external storage (with raid).
>
> This is also one of the only postgres HA configurations that won't lose
> /any/ committed transactions on a failure.  Most all PITR/WAL
> replication/Slony/etc configs, the standby storage runs several seconds
> behind realtime.

I'm not clear on what error case it protects against, though. Either the
data is ok and a single PostgreSQL system will restart fine, or the data
isn't and you're hosed the same with or without the second system.

What's left is hardware failure that didn't compromise the data. I
didn't see much hardware failure yet, granted, but I'm yet to see a
motherboard, some RAM or a RAID controller failing in a way that leaves
behind data you can trust.

So my question would be, what case do you handle better with a shared
external storage compared to shared nothing servers with some sort of
replication (including WAL shipping)?

Regards,
-- 
dim

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability with Postgres

[Yaroslav Tykhiy]

Hi,

On 21/06/2010, at 3:37 AM, Raymond O'Donnell wrote:


On 20/06/2010 17:34, Elior Soliman wrote:

Hello,

My company looking for some solution for High availability with  
Postgres.


There's quite a bit of information in the documentation here:

 http://www.postgresql.org/docs/8.4/static/high-availability.html


And to keep oneself up to speed:

http://momjian.us/main/blogs/pgblog/2010.html#June_16_2010

Yar

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability with Postgres

[Raymond O'Donnell]

On 20/06/2010 17:34, Elior Soliman wrote:
> Hello,
> 
> My company looking for some solution for High availability with Postgres.

There's quite a bit of information in the documentation here:

  http://www.postgresql.org/docs/8.4/static/high-availability.html

HTH,

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability with Postgres

[John R Pierce]

On 06/20/10 10:36 AM, David Fetter wrote:

On Sun, Jun 20, 2010 at 07:34:10PM +0300, Elior Soliman wrote:
   

My company looking for some solution for High availability with Postgres.

Our optional solution is as follows :
Two DB servers will be using a common external storage (with raid).
 

Stop right there.  This is the Oracle way of doing things, and it
doesn't work for PostgreSQL.
   


Sure it does, as long as the database filesystem is only mounted on the 
currently active server, and only that instance of postgres is running.  
This is the traditional HA 'active/standby' server configuration.  Note, 
I'm *not* talking about Oracle RAC active-active clustering.


This is also one of the only postgres HA configurations that won't lose 
/any/ committed transactions on a failure.  Most all PITR/WAL 
replication/Slony/etc configs, the standby storage runs several seconds 
behind realtime.


Use a cluster manager, like Linux Heartbeat, Veritas Cluster, Sun 
Cluster, etc, to manage the state transitions.



on a manual failover, the active server stops postgres, frees the shared 
IP, umounts the shared storage, then the standby server fences the 
formerly active server so it can't access the storage if it 
accidentially tried, adopts the shared IP, mounts the shared storage, 
starts postgres and is online.


on a failed server failover the standby server does the same thing.

the commercial cluster software vendors insist on using dedicated 
connections for the heartbeat messages between the cluster members and 
insist on having fencing capabilities (for instance, disabling the fiber 
switch port of the formerly active server and enabling the port for the 
to-be-activated server).  with linux-ha and heartbeat, you're on your own.


of course, a system like this, your external shared raid should itself 
be redundant, and have controller failover abilities, and each cluster 
server should have redundant connecctions to the two storage 
controllers.  With fiberchannel you use two switches and two HBAs on 
each node.  with iscsi, you'd use two ethernet switches and NICs on each 
host.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability with Postgres

[Craig Ringer]

On 21/06/10 00:34, Elior Soliman wrote:
> Hello,
> 
> My company looking for some solution for High availability with Postgres.
> 
> Our optional solution is as follows :
> Two DB servers will be using a common external storage (with raid). Both
> servers are going to use the same DB files on the storage (as
> active/passive)

Why do you want that configuration?

For PostgreSQL, a warm standby setup using WAL-based replication (PITR,
or "point in time replication") is usually the preferred approach.

There are also add-on replication solutions like bucardo and slony-I.

> Now I'm trying to understand how Postgres can work with thi
> configuration. I.e :
> 
> DB_server1 crashed, so we want to start DB_server2 using same files.
> Is it possible ?

Depending on why and how db_server1 crashed, it may be. If the DB server
crashed in such a way as that it didn't stomp all over the shared
storage, then you can unplug db_server1 from the shared storage or
otherwise render that shared storage completely inaccessible by
db_server1, then start the database on db_server2.

If you fail to render it inaccessible by db_server1 or make sure
db_server1 is stone dead (powered off) first, you may land up with two
postmasters trying to work with the same data. This *will* cause
corruption if you override/bypass Pg's attempts to detect and prevent
this fault condition.

The only time you can really use shared storage is if you have a
heartbeat setup with STONITH. Even then, I'd personally prefer to use
network replication if at all possible, since it removes the shared
storage as a central point of failure. It also lets you use much, much
faster locally attached storage where disks are dedicated to PostgreSQL.

--
Craig Ringer

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability with Postgres

[David Fetter]

On Sun, Jun 20, 2010 at 07:34:10PM +0300, Elior Soliman wrote:
> Hello,
> 
> My company looking for some solution for High availability with Postgres.
> 
> Our optional solution is as follows :
> Two DB servers will be using a common external storage (with raid).

Stop right there.  This is the Oracle way of doing things, and it
doesn't work for PostgreSQL.

> Both servers are going to use the same DB files on the storage (as
> active/passive)
> 
> Now I'm trying to understand how Postgres can work with this
> configuration.  I.e :

It does not.

There are plenty of ways to get that broad spectrum of sometimes
contradictory things people mean when they use the phrase "HA" with
PostgreSQL, but you must first define your requirements.  Once you
have done so, it will be possible to create strategies for achieving
same.

What are the actual requirements?  Things that would be nice to have?
What are your priorities for both?

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] High Availability with Postgres

[Elior Soliman]

Hello,

My company looking for some solution for High availability with Postgres.

Our optional solution is as follows :
Two DB servers will be using a common external storage (with raid). Both
servers are going to use the same DB files on the storage (as
active/passive)

Now I'm trying to understand how Postgres can work with this configuration.
I.e :

DB_server1 crashed, so we want to start DB_server2 using same files.
Is it possible ?

Regards,
Elior

[GENERAL] high-availability support for turnkey appliances (for turnkey postgresql and others)

[Liraz Siri]

Hi Todd,

Interesting you should bring that up, supporting high availability is
actually something we've talked about doing a bit further down the road.
Before we get to that though, there are still quite a few low hanging
fruit to pick.

If someone with experience in this field picks up the ball and makes his
expertise available to us we'll probably be able to do this earlier
rather than later.

Cheers,
Liraz

Todd Yocum wrote:
> 
> Just a FWIW, for a customer I recently did some research into
> HighAvailabilty solutions (HA) for a JBoss system that has a database
> backend. While PostgreSQL was my database of choice for the customer,
> there were no turnkey HA solutions for PostgreSQL. A turnkey solution
> that provides that would probably be of value to customers, in my opinion.
> 
> Liraz Siri wrote:
>> Also, we need help spreading
>> the word that an easy-to-use PostgreSQL software appliance exists. Any
>> ideas?
>>
>> Cheers,
>> Liraz
>>
>>   

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability for PostgreSQL on Windows 2003.

[Jonah H. Harris]

On Tue, Nov 18, 2008 at 11:09 AM, Pietro Tedesco
<[EMAIL PROTECTED]> wrote:
> We have an instance of PostgreSQL on Windows 2003 with some application
> and our customer have asked for solution
> 24x7 without human intervention for problem on the hardware/software
> primary instance.
> Actualy there is a solution with standby.
> Is there a product of High Availability for PostgreSQL on Windows 2003?

I successfully configured an active/passive cluster using Double-Take
Software with Postgres 8.3 on Windows2003.

-- 
Jonah H. Harris, Senior DBA
myYearbook.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability for PostgreSQL on Windows 2003.

[Serge Fonville]

There are a couple of solutions probably.
First off, search pgfoundry for possibilities, look into clustering and
replication.

A little more insight would make it easier to answer your question:
Determine what you exactly want, what kind of uptime at what expense.
How should it be made available (single ip, multiple ips(requires
application changes))
Do you wan't a full cluster (load balancing, distributed transaction,
service provisioning, failover), or will replication suffice and running a
separate heartbeat checking script) to effectively fail over.
Is downtime an option (a couple of minutes)
How is the rest of the environment protected against downtime
What version of Windows is it(Standard,Enterprise,Datacenter) for MSCS you
require at least Enterprise
What other services will be running on the servers with PostgreSQL
How many servers will you be running in the solution
Have you previously configured a similar application (SQL
Server,Exchange,ISA,etc)
Is moving to another OS an option
How long should implementation take

Regards,

Serge Fonville

Re: [GENERAL] High Availability for PostgreSQL on Windows 2003.

[Serge Fonville]

There are a couple of solutions probably.
First off, search pgfoundry for possibilities, look into clustering and
replication.

A little more insight would make it easier to answer your question:
Determine what you exactly want, what kind of uptime at what expense.
How should it be made available (single ip, multiple ips(requires
application changes))
Do you wan't a full cluster (load balancing, distributed transaction,
service provisioning, failover), or will replication suffice and running a
separate heartbeat checking script) to effectively fail over.
Is downtime an option (a couple of minutes)
How is the rest of the environment protected against downtime
What version of Windows is it(Standard,Enterprise,Datacenter) for MSCS you
require at least Enterprise
What other services will be running on the servers with PostgreSQL
How many servers will you be running in the solution
Have you previously configured a similar application (SQL
Server,Exchange,ISA,etc)
Is moving to another OS an option
How long should implementation take

Regards,

Serge Fonville

On Tue, Nov 18, 2008 at 5:09 PM, Pietro Tedesco <
[EMAIL PROTECTED]> wrote:

> We have an instance of PostgreSQL on Windows 2003 with some application
> and our customer have asked for solution
> 24x7 without human intervention for problem on the hardware/software
> primary instance.
> Actualy there is a solution with standby.
> Is there a product of High Availability for PostgreSQL on Windows 2003?
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] High Availability for PostgreSQL on Windows 2003.

[Magnus Hagander]

On 18 nov 2008, at 17.09, Pietro Tedesco  
<[EMAIL PROTECTED]> wrote:


We have an instance of PostgreSQL on Windows 2003 with some  
application

and our customer have asked for solution
24x7 without human intervention for problem on the hardware/software
primary instance.
Actualy there is a solution with standby.
Is there a product of High Availability for PostgreSQL on Windows  
2003?




You can use microsoft clustering service (I think it's called that)  
for clustering.


But: we really recommend a unix like platform for anything ha with  
postgresql.


/Magnus

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] High Availability for PostgreSQL on Windows 2003.

[Pietro Tedesco]

We have an instance of PostgreSQL on Windows 2003 with some application
and our customer have asked for solution
24x7 without human intervention for problem on the hardware/software
primary instance.
Actualy there is a solution with standby.
Is there a product of High Availability for PostgreSQL on Windows 2003?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability / Replication with Sequoia

[[EMAIL PROTECTED]]

Thanks for the help guys,

I should clear up a little what I am trying to achieve I think.

The primary users of this db and application will be located in an
office, each user with a desktop machine, all networked. They need to
work with this DB in a fairly heavy kind of way, in so far as to say
that 80% of their day will be working with the application and the db.

The primary source of data will / must be located on a database server
that is actually in a different facility. It is possible to reach this
server from the office, and is done so daily, however the speed of
connection is very slow and is frequently disconnected - in short
unrelaible. To implement an extension of this 'primary' db with the
associated hardware and licensing costs at the local site is beyond
what the business is willing to pay. It also goes directly against the
'structure' that has been laid out by the IT group in that they want
all the db servers in a single location - regardless of business
impact they want to make their budget savings.

So, what I want to do is to satisfy the IT group by keeping a 'master'
copy of the db on their off-site facility, which in fact will be
populated from a source system sitting on my desk. The ETL tools will
be used for creating a completely (or as near as possible) automated
system for populating the 'master' that is offsite.

What I wanted to do next was to have Postgres installed on each of the
local users machines, along with the application they require, and run
them as a cluster - if one db goes down or one machine dies the client
software / app can still connect to the cluster and keep functioning
from another machine. I could then have the defective machine attended
to and if necessary re-built... In short the ability to work would not
be interrupted. Or at least thats the hope.

These desktops shut down each night too, as the staff leave to go
home. There is no possibility to install a server locally
(unfortunately). So with this in mind I was hoping that the
'automatic' nature of Sequoia would allow for recovery / updating from
the master or others in the cluster and keep all the local db's up to
date without the users having to do anything.

There is also a desire to have a mobile copy of this db / app for some
of the mobile users that come in to the office. They wont be able to
update while external due to the way the network is designed, but once
back in the office they could do this. I was hoping once again to keep
this as effortless as possible for the users. I am still hoping that
this may be achieveable.

In summary, what we are looking at is an install of Postgres on each
machine, a copy of Tomcat running the application, and maybe Sequoia
or Slony or some combination of both. ETL is handled separately (by
me) and the users are supposed to just be able to get on with their
work.

Do you think this is achieveable or am I up the creek and reaching too
far here?

Cheers

The Frog (you caught me out - its not my real name!)

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability / Replication with Sequoia

[Shane Ambler]

Richard Huxton wrote:

[EMAIL PROTECTED] wrote:



I am looking now at a scenario that does not seem to be a native
ability of Postgres, but might possibly be overcome with Sequoia. I am
hoping that there exists the possibility of using Sequoia to replicate
a DB between / among a number of machines in the office, some of which
are not always connected to the lan.


You might want to look at slony with log-shipping
  http://www.slony.info/documentation/logshipping.html
Have a master server on the lan, others grab files as and when they 
need. Do read the "limitations" though.


I haven't used this or Sequoia before but slony log-shipping sounds 
closer to what you want for the mobile users.


Sequoia appears to cater for always available servers to replicate to so 
may not suite your mobile users.



The application does not allow writeback to the db, so for all intents
and purposes you can consider it read only.


Fine with slony.


To keep the applications database up to date with new information I
would be using ETL applications like Spoon / PDI. This will be done to
an as yet undecided 'point of origin', but it is probably safe to say
that it will be a commercial db server somewhere on our network. The
latency from our network to the 'Data Warehouse' (read as badly
managed dogs breakfast) is huge. Suffice to say the desire for local
db's is high, as is the desire to make the application portable for
our sometimes connected laptop users.


The syncing with your commercial DB is probably the most fiddly bit. 
That's not so bad, since it's one-way.




Not sure I get your whole plan here or not

The main thing I am thinking here is whether you plan to have each user 
sync from the commercial db or another postgresql db?


Sequoia appears to support different db sources but I would check 
whether it supports replicating the same data between different 'brands' 
of db or whether the master and slave must be the same brand if this is 
where you plan to use it.



You could look at having postgresql draw data from your commercial db on 
a set schedule something like odbclink at pgfoundry may help there.



How many of your users are mobile? Could most users be accommodated by 
one central server with some mobile users getting the local copy to take 
with them? Maybe syncing data when they request it? Or are you set on 
automating the syncing?






--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High Availability / Replication with Sequoia

[Richard Huxton]

[EMAIL PROTECTED] wrote:

Hi Guys,

I have been testing / working with Postgres for a work project, and so
far I am really impressed with this DB system. Takes a little getting
used to, but I am really beginning to love it.


Good to hear it Mr ...Umm... Frog.


I am looking now at a scenario that does not seem to be a native
ability of Postgres, but might possibly be overcome with Sequoia. I am
hoping that there exists the possibility of using Sequoia to replicate
a DB between / among a number of machines in the office, some of which
are not always connected to the lan.


You might want to look at slony with log-shipping
  http://www.slony.info/documentation/logshipping.html
Have a master server on the lan, others grab files as and when they 
need. Do read the "limitations" though.



The application does not allow writeback to the db, so for all intents
and purposes you can consider it read only.


Fine with slony.


To keep the applications database up to date with new information I
would be using ETL applications like Spoon / PDI. This will be done to
an as yet undecided 'point of origin', but it is probably safe to say
that it will be a commercial db server somewhere on our network. The
latency from our network to the 'Data Warehouse' (read as badly
managed dogs breakfast) is huge. Suffice to say the desire for local
db's is high, as is the desire to make the application portable for
our sometimes connected laptop users.


The syncing with your commercial DB is probably the most fiddly bit. 
That's not so bad, since it's one-way.



Does anyone have any experience or comments that they would like to
share about this sort of scenario? Its a fairly big jump from just
having Postgres running on my laptop for dev purposes to pushing this
to multiple machines and I would really appreciate any feedback you
guys might have.


Not used the log-shipping variant of slony, but I'm happy enough with 
the regular connected-version.


--
  Richard Huxton
  Archonet Ltd

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] High Availability / Replication with Sequoia

[[EMAIL PROTECTED]]

Hi Guys,

I have been testing / working with Postgres for a work project, and so
far I am really impressed with this DB system. Takes a little getting
used to, but I am really beginning to love it.

I am looking now at a scenario that does not seem to be a native
ability of Postgres, but might possibly be overcome with Sequoia. I am
hoping that there exists the possibility of using Sequoia to replicate
a DB between / among a number of machines in the office, some of which
are not always connected to the lan.

The scenario is like this. On each of the machines I would want to
have Postgres installed and only to accepting connections from the
local machine. Also on each of these machines would be running Tomcat
or similar hosting the required application (app to connect to local
Postgres installation). Sequoia would then be used as a form of
replication from machine to machine to ensure that the database is
kept up to date.

The application does not allow writeback to the db, so for all intents
and purposes you can consider it read only.

To keep the applications database up to date with new information I
would be using ETL applications like Spoon / PDI. This will be done to
an as yet undecided 'point of origin', but it is probably safe to say
that it will be a commercial db server somewhere on our network. The
latency from our network to the 'Data Warehouse' (read as badly
managed dogs breakfast) is huge. Suffice to say the desire for local
db's is high, as is the desire to make the application portable for
our sometimes connected laptop users.

Does anyone have any experience or comments that they would like to
share about this sort of scenario? Its a fairly big jump from just
having Postgres running on my laptop for dev purposes to pushing this
to multiple machines and I would really appreciate any feedback you
guys might have.

Thanks in advance

The Frog

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High-availability

[Francisco Reyes]

Although I rarely see it mentioned, Skype has some replication tools that 
they opensourced.


https://developer.skype.com/SkypeGarage/DbProjects/SkyTools

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] High-availability

[Chander Ganesan]

Simon Riggs wrote:

On Mon, 2007-06-04 at 10:21 -0400, Chander Ganesan wrote:

  

It's not too hard to put together a "warm standby" synchronous
replication mechanism with overhead that isn't too much more than what
you incur by enabling PITR...  Such systems can also have very fast
failover on failure detection (via heartbeat2), and be synchronous.



Do you have any performance measurements of either the replication
overhead or the failover time? I'm interested in how well we cope with
high transaction rates. Thanks.

  
Aside from a bunch of customized pgbench benchmarks (on the 9.6 GB 
sample database we use), which are "better than nothing, but far from 
the best", not really.  In my experience, the larger the database; 
slower the commit rate; and less frequently the checkpoints - the better 
the performance of synchronous warm-replication.  In our tests, higher 
commit rates and more frequent checkpoints incur a higher penalty.  
Basically, the more WAL activity the higher the cost.


If I have time I'll see if we can run a more meaningful metric (need to 
generate a smaller database for that) the next time we have a 
performance tuning class (in August).


The failover time is tunable to some extent...via heartbeat2 (incurs < 
1% performance penalty, but with sub-second failover this can go up a 
bit), and can be pretty quick (I usually set it up with around a 3 
second failover time on node failure, then factor that in with the 
amount of time required for WAL auto-recovery)...it really depends a lot 
on what your metric is for "failure" (since node failover is probably 
the "worst worst case").


--
Chander Ganesan
The Open Technology Group
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com

Re: [GENERAL] High-availability

[Simon Riggs]

On Mon, 2007-06-04 at 10:21 -0400, Chander Ganesan wrote:

> It's not too hard to put together a "warm standby" synchronous
> replication mechanism with overhead that isn't too much more than what
> you incur by enabling PITR...  Such systems can also have very fast
> failover on failure detection (via heartbeat2), and be synchronous.

Do you have any performance measurements of either the replication
overhead or the failover time? I'm interested in how well we cope with
high transaction rates. Thanks.

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com



---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] High-availability

[Bohdan Linda]

On Mon, Jun 04, 2007 at 04:21:32PM +0200, Chander Ganesan wrote:
> I think you'll typically find that you can get one or the other - 
> synchronous replication, or load balancing...but not both.  On the other 

Hi,

I am in very similar position, but I am more failover oriented. I am
considering using pgcluster, which shall resolve both at the cost of
slight transaction overhead. Does anyone have any experience with this?
What problems may I expect in this setup?

Kind regards,
Bohdan 

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

   http://www.postgresql.org/docs/faq

Re: [GENERAL] High-availability

[Madison Kelly]

Chander Ganesan wrote:

Madison Kelly wrote:

Hi all,

  After realizing that 'clustering' in the PgSQL docs means multiple 
DBs behind one server, and NOT multple machines, I am back at square 
one, feeling somewhat the fool. :P


  Can anyone point me to docs/websites that discuss options on 
replicating in (as close as possible to) realtime? Ideally with load 
balancing while both/all servers are up, and failover/resyncing when a 
member fails and is restored.
If you're interested in the "less than ideal" case (no load balancing, 
but synchronous replication in a "warm standby" type mode), there are 
several options, such as shared disk (two systems sharing a SAN or NAS 
with heartbeat-style fail over - shared disk scenario), or DRBD (where 
block level changes to one device are mirrored in real-time over to 
another, with heartbeat style fail over - this is a "shared nothing" 
type scenario).  It's not too hard to put together a "warm standby" 
synchronous replication mechanism with overhead that isn't too much more 
than what you incur by enabling PITR...  Such systems can also have very 
fast failover on failure detection (via heartbeat2), and be synchronous.


I think you'll typically find that you can get one or the other - 
synchronous replication, or load balancing...but not both.  On the other 
hand, if you were really serious about having close to both, you could 
have a three node setup - two (a provider and subscriber) that run using 
Slony-I (and async replication) and one that runs using one of the 
aforementioned methods (i.e., DRBD and warm-standby synchronous 
replication).  In such cases a "failover" would mean switching to the 
synchronous replication system.  You should even be able to get SLONY to 
continuing to avail you with load balancing in such a case, without 
having to re-sync - though I haven't tried this myself...  You'd still 
have a potential query that got stale data (when it went to a Slony-I 
subscriber), but you would never lose a committed transaction.  You'd 
have the added benefit of a "shared nothing" environment as well...


As a side plug, we discuss and implement a few of these options in our 
PostgreSQL performance tuning course.. 
http://www.otg-nc.com/training-courses/coursedetail.php?courseid=47&cat_id=8 



  Is this even possible on PostgreSQL?

  Being a quite small company, proprietary hardware and fancy software 
licenses are not possible (ie: 'use oracle' won't help).


  I've looked at slony, but it looks more like a way to push 
occasional copies to slaves, and isn't meant to be real time. Am I 
wrong by chance?


  Thanks for any help/tips/pointers!


Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com
*Expert PostgreSQL Training - On-Site and Public Enrollment*


Madi

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings




Thank you for your reply!

The more I learn, the more I am leaning towards the DRBD/shared-nothing 
setup. Our loads are not terribly heavy at this point. I hate the idea 
of having a nice server sitting there doing nothing 99% of the time, but 
it looks like the most viable way of setting up HA at this point. Given 
that I am learning as I go, I think the three-way setup you describe 
would be a bit too ambitious for me just now. That said, I do have a 
spare third server that I could use for just such a setup, should I feel 
comfortable enough down the road.


Madi

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] High-availability

[Chander Ganesan]

Madison Kelly wrote:

Hi all,

  After realizing that 'clustering' in the PgSQL docs means multiple 
DBs behind one server, and NOT multple machines, I am back at square 
one, feeling somewhat the fool. :P


  Can anyone point me to docs/websites that discuss options on 
replicating in (as close as possible to) realtime? Ideally with load 
balancing while both/all servers are up, and failover/resyncing when a 
member fails and is restored.
If you're interested in the "less than ideal" case (no load balancing, 
but synchronous replication in a "warm standby" type mode), there are 
several options, such as shared disk (two systems sharing a SAN or NAS 
with heartbeat-style fail over - shared disk scenario), or DRBD (where 
block level changes to one device are mirrored in real-time over to 
another, with heartbeat style fail over - this is a "shared nothing" 
type scenario).  It's not too hard to put together a "warm standby" 
synchronous replication mechanism with overhead that isn't too much more 
than what you incur by enabling PITR...  Such systems can also have very 
fast failover on failure detection (via heartbeat2), and be synchronous.


I think you'll typically find that you can get one or the other - 
synchronous replication, or load balancing...but not both.  On the other 
hand, if you were really serious about having close to both, you could 
have a three node setup - two (a provider and subscriber) that run using 
Slony-I (and async replication) and one that runs using one of the 
aforementioned methods (i.e., DRBD and warm-standby synchronous 
replication).  In such cases a "failover" would mean switching to the 
synchronous replication system.  You should even be able to get SLONY to 
continuing to avail you with load balancing in such a case, without 
having to re-sync - though I haven't tried this myself...  You'd still 
have a potential query that got stale data (when it went to a Slony-I 
subscriber), but you would never lose a committed transaction.  You'd 
have the added benefit of a "shared nothing" environment as well...


As a side plug, we discuss and implement a few of these options in our 
PostgreSQL performance tuning course.. 
http://www.otg-nc.com/training-courses/coursedetail.php?courseid=47&cat_id=8


  Is this even possible on PostgreSQL?

  Being a quite small company, proprietary hardware and fancy software 
licenses are not possible (ie: 'use oracle' won't help).


  I've looked at slony, but it looks more like a way to push 
occasional copies to slaves, and isn't meant to be real time. Am I 
wrong by chance?


  Thanks for any help/tips/pointers!


Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com
*Expert PostgreSQL Training - On-Site and Public Enrollment*


Madi

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] High-availability

[Andrew Sullivan]

On Sun, Jun 03, 2007 at 01:35:49PM -0400, Lew wrote:
> How much data do you put in the DB?  Oracle has a free version, but it has 
> size limits.

AFAIK, Oracle's free version doesn't include RAC, which is what would
be needed to satisfy the request anyway.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
Users never remark, "Wow, this software may be buggy and hard 
to use, but at least there is a lot of code underneath."
--Damien Katz

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] High-availability

[Madison Kelly]

Lew wrote:

Madison Kelly wrote:
  Being a quite small company, proprietary hardware and fancy software 
licenses are not possible (ie: 'use oracle' won't help).


How much data do you put in the DB?  Oracle has a free version, but it 
has size limits.


(Ducking the slings and arrows of outraged PG fans: I prefer Postgre, I 
really do.)




  Hrm, it's hard to say as we're (hoping!) to grow. At the moment, a 
few hundred megs. If the company gets off the ground, possibly much 
more. also, we've got a few (dozen or so) side projects that each have 
their own DBs.


  I think the risk of running into a barrier like a size limit would be 
too much. Even if we get off the ground, the storage needs of the DB 
will outgrow our revenue. I'd hate to be in a position where I am 
dependent on a (potentially) very expensive invoice while we are still 
running on a shoe-string.


  Thanks for the suggestion though! I will poke at the free/trial 
version and, if I am unable to load-balance pgSQL and we run into 
performance problems, I will have a better idea of what options I have 
(ie: bigger iron vs. an oracle license).


  Thanks!

Madi

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] High-availability

[Lew]

Madison Kelly wrote:
  Being a quite small company, proprietary hardware and fancy software 
licenses are not possible (ie: 'use oracle' won't help).


How much data do you put in the DB?  Oracle has a free version, but it has 
size limits.


(Ducking the slings and arrows of outraged PG fans: I prefer Postgre, I really 
do.)


--
Lew

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

Re: [GENERAL] High-availability

[Lew]

Alexander Staubo wrote:

As a side-note, I sat up pgpool-II today, and was pleasantly surprised
about how easy it all was; within two minutes I had two databases in
perfect sync on my laptop. It has limitations (such as in its handling
of sequences), but compared to Slony it's like a breath of fresh
mountain air.


Err, the setup is, I mean. Once you have Slony up and running, it's
pretty smooth.


I wonder what the OP means by "real-time".  The standard definition is "within 
a deterministic time bound".


Replication implies latency.  Ignoring latency or wishing it away will not help.

It is possible to manage latency.  One strategy is to minimize it.  There are 
others.


Also remember the ancient proverb, applicable when two or more nodes are 
trying to agree on what time it is:

"Man with two watches never knows correct time."

I think of this category of issue as the Special Relativity of information.

--
Lew

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

  http://www.postgresql.org/docs/faq

Re: [GENERAL] High-availability

[Madison Kelly]

Alexander Staubo wrote:

On 6/1/07, Madison Kelly <[EMAIL PROTECTED]> wrote:

   After realizing that 'clustering' in the PgSQL docs means multiple
DBs behind one server, and NOT multple machines, I am back at square
one, feeling somewhat the fool. :P


I remember being similarly disappointed in this rampant co-opting of
the word "cluster" back in 7.4 or so. :) A gaggle of geese, a murder
of crows, a cluster of databases, I guess.


   Can anyone point me to docs/websites that discuss options on
replicating in (as close as possible to) realtime? Ideally with load
balancing while both/all servers are up, and failover/resyncing when a
member fails and is restored.


The PostgreSQL documentation gives a pretty good overview of the options:

 http://www.postgresql.org/docs/8.2/interactive/high-availability.html

That said, there is to my knowledge no single, integrated product that
will do all you ask. None are capable of anything near real-time,
automatic failover tends to be left as an exercise for the reader, and
there is a lot of work to get it up and running, and requires
particular care in maintenance and monitoring once it's up.

There are several commercial (Mammoth Replicator comes to mind) and
several open-source projects. Among the open-source ones (Slony-I,
pgpool, PGCluster), I believe Slony-I is the most mature. There are a
few in-progress attempts (pgpool-II, PGCluster 2, PostgreSQL-R) that
are not ready for prime time yet; of these, I believe pgpool-II is the
most promising.

As mentioned in a different thread today, work is being done to
implement WAL-based master-slave replication, which I think should
prove more scalable and more transparent than the current third-party
products:

 http://archives.postgresql.org/pgsql-hackers/2007-03/msg00050.php


   I've looked at slony, but it looks more like a way to push occasional
copies to slaves, and isn't meant to be real time. Am I wrong by chance?


Slony is indeed intended for near-real-time replication; it's
asynchronous, so slaves always lag behind the master. The amount of
discrepancy depends on a bunch of factors -- individual node
performance, network performance, and system load.

Alexander.


That was *exactly* the kind of link I was trying to find.

Thank you!

Madi

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] High-availability

[Alexander Staubo]

On 6/3/07, Alexander Staubo <[EMAIL PROTECTED]> wrote:

As a side-note, I sat up pgpool-II today, and was pleasantly surprised
about how easy it all was; within two minutes I had two databases in
perfect sync on my laptop. It has limitations (such as in its handling
of sequences), but compared to Slony it's like a breath of fresh
mountain air.


Err, the setup is, I mean. Once you have Slony up and running, it's
pretty smooth.

Alexander.

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

Re: [GENERAL] High-availability

[Alexander Staubo]

On 6/3/07, Madison Kelly <[EMAIL PROTECTED]> wrote:

> Slony is indeed intended for near-real-time replication; it's
> asynchronous, so slaves always lag behind the master. The amount of
> discrepancy depends on a bunch of factors -- individual node
> performance, network performance, and system load.

That was *exactly* the kind of link I was trying to find.


You're welcome.

As a side-note, I sat up pgpool-II today, and was pleasantly surprised
about how easy it all was; within two minutes I had two databases in
perfect sync on my laptop. It has limitations (such as in its handling
of sequences), but compared to Slony it's like a breath of fresh
mountain air.

Pgpool-II also supports table partitioning, where you define each
database to have a subset of the data. Pgpool-II then intercepts every
SQL statement and routes it to the correct server. It doesn't work
with referential integrity, I think, which is a major limitation, but
it's the nature of the beast.

Alexander.

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

  http://www.postgresql.org/docs/faq

Re: [GENERAL] High-availability

[Alexander Staubo]

On 6/1/07, Madison Kelly <[EMAIL PROTECTED]> wrote:

   After realizing that 'clustering' in the PgSQL docs means multiple
DBs behind one server, and NOT multple machines, I am back at square
one, feeling somewhat the fool. :P


I remember being similarly disappointed in this rampant co-opting of
the word "cluster" back in 7.4 or so. :) A gaggle of geese, a murder
of crows, a cluster of databases, I guess.


   Can anyone point me to docs/websites that discuss options on
replicating in (as close as possible to) realtime? Ideally with load
balancing while both/all servers are up, and failover/resyncing when a
member fails and is restored.


The PostgreSQL documentation gives a pretty good overview of the options:

 http://www.postgresql.org/docs/8.2/interactive/high-availability.html

That said, there is to my knowledge no single, integrated product that
will do all you ask. None are capable of anything near real-time,
automatic failover tends to be left as an exercise for the reader, and
there is a lot of work to get it up and running, and requires
particular care in maintenance and monitoring once it's up.

There are several commercial (Mammoth Replicator comes to mind) and
several open-source projects. Among the open-source ones (Slony-I,
pgpool, PGCluster), I believe Slony-I is the most mature. There are a
few in-progress attempts (pgpool-II, PGCluster 2, PostgreSQL-R) that
are not ready for prime time yet; of these, I believe pgpool-II is the
most promising.

As mentioned in a different thread today, work is being done to
implement WAL-based master-slave replication, which I think should
prove more scalable and more transparent than the current third-party
products:

 http://archives.postgresql.org/pgsql-hackers/2007-03/msg00050.php


   I've looked at slony, but it looks more like a way to push occasional
copies to slaves, and isn't meant to be real time. Am I wrong by chance?


Slony is indeed intended for near-real-time replication; it's
asynchronous, so slaves always lag behind the master. The amount of
discrepancy depends on a bunch of factors -- individual node
performance, network performance, and system load.

Alexander.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

[GENERAL] High-availability

[Madison Kelly]

Hi all,

  After realizing that 'clustering' in the PgSQL docs means multiple 
DBs behind one server, and NOT multple machines, I am back at square 
one, feeling somewhat the fool. :P


  Can anyone point me to docs/websites that discuss options on 
replicating in (as close as possible to) realtime? Ideally with load 
balancing while both/all servers are up, and failover/resyncing when a 
member fails and is restored.


  Is this even possible on PostgreSQL?

  Being a quite small company, proprietary hardware and fancy software 
licenses are not possible (ie: 'use oracle' won't help).


  I've looked at slony, but it looks more like a way to push occasional 
copies to slaves, and isn't meant to be real time. Am I wrong by chance?


  Thanks for any help/tips/pointers!

Madi

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] High-Availability Question

[Chander Ganesan]

Ivan Zolotukhin wrote:

Hello,


> Third idea:
> I install the Slony-I, linux-ha and postgresql on the same server of
> the two 2U servers. The web application access to the db server
> directly and without pgpool.


AFAIK, Slony does not have failover capabilities you need in HA solution:

http://gborg.postgresql.org/project/slony1/genpage.php?howto_overview

So you will need pgpool layer (or some other connection manager +
network monitoring software) to detect failures anyway.

I believe the HA scripts would need to call the appropriate slonik 
scripts to cause the failover to occur "properly" (calling the slonik 
failover command).  I think this is a viable option - albeit dangerous 
(since some committed transactions might be lost).  Normally, you'd also 
have the HA script take over the IP of the master as well, but if you 
used pgpool (in master/slave mode) then you could have it tell pgpool to 
make the switchover for you.  Keep in mind that if you ran a separate 
pgpool server you'd want to ensure that it had a backup as well.


pgpool is nice, since when a master fails, the changes aren't lost on 
the slave.  However, AFAIK getting to the starting point (where both 
servers are identical) would require the cluster to be down for a bit.  
Slony-I does a much better job of this in that it performs the 
replication itself (it will bring the slave up to a consistent state 
automagically).  However, that can be a time consuming task, depending 
on how much data you have.


Just a thought, but I think it should be possible to use Slony-I to 
bring a master and slave up to a consistent state, then perform a 'lock 
set' then a 'wait for event' (to ensure both nodes are consistent), then 
a disable of slony-I (so both nodes are writeable) then switch from 
pgpool degeneration mode to replication mode.  I think this would give 
you the best of both worlds - pgpool without the startup downtime, 
synchronous replication, etc.  You'd have a "recovery period" where 
Slony-I sync'd things up...  You'd still have some of the pgpool 
shortcomings (most notably things like 'select nexval()' type statements 
and lack of secure authentication...but wouldn't suffer from data loss 
in the case of a failure.


--
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] High-Availability Question

[ProAce]

> First idea:
> I install the pgpool on each web server (the web server farm include
> 16 web servers), and configure the pgpool as replication mode. The web
> application (written by php) access to the db server through the local
> pgpool daemon.
> The idea sounds a little unusual, dose it seems workable?
> I just use very simple sql statment in the web application, no any
> complex statment.

This is not unusual and actualy I think it's a good idea. Also you
could enjoy the advantage of the load-balance capability of pgpool in
this case.



I say the idea is a little unusual because I'm concerned about the
health check status on each pgpool daemon ( on each web server ).
If the health check status is different from each pgpool, the data
will be not consistency.
Does any situation cause of the different health check status?

PS: My web server farm and database servers are not in the same
subnet, but the network architecture between those subnet is fully
redundancy. Even the db server binds two network interface to increase
the availability.

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

  http://www.postgresql.org/docs/faq

Re: [GENERAL] High-Availability Question

[Ivan Zolotukhin]

Hello,


> Third idea:
> I install the Slony-I, linux-ha and postgresql on the same server of
> the two 2U servers. The web application access to the db server
> directly and without pgpool.


AFAIK, Slony does not have failover capabilities you need in HA solution:

http://gborg.postgresql.org/project/slony1/genpage.php?howto_overview

So you will need pgpool layer (or some other connection manager +
network monitoring software) to detect failures anyway.

Regards,
Ivan Zolotukhin

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

Re: [GENERAL] High-Availability Question

[Tatsuo Ishii]

> I hava a db server (mysql) in my web application, it include 550
> tables and about 10 rows in each table. Now, I want to change the
> db server to postgresql and construct a HA environment.
> 
> I have two 2U servers to build postgresql server (one is master, the
> other is slave), and two 1U servers for any purpose about the ha
> environment. And I expect  to use the FreeBSD as the operation system.
> 
> My request is, when a server fail (no matter the master or slave), the
> web server can still access (read/write) the database correctly.
> 
> I have three ideas about the HA environment, does anyone give me some advices?
> Or guide me to learn more advanced ideas. Thanks.  :)
> 
> First idea:
> I install the pgpool on each web server (the web server farm include
> 16 web servers), and configure the pgpool as replication mode. The web
> application (written by php) access to the db server through the local
> pgpool daemon.
> The idea sounds a little unusual, dose it seems workable?
> I just use very simple sql statment in the web application, no any
> complex statment.

This is not unusual and actualy I think it's a good idea. Also you
could enjoy the advantage of the load-balance capability of pgpool in
this case.

> Second idea:
> I install the pgpool and linux-ha on the two 1U server, and configure
> the pgpool as replication mode. The web application access to the db
> server through the pgpool daemon.

I'm not familiar with linux-ha so have no idea if this works or not.

> Third idea:
> I install the Slony-I, linux-ha and postgresql on the same server of
> the two 2U servers. The web application access to the db server
> directly and without pgpool.

I'm not sure what would happen with this configuration.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] High-Availability Question

[ProAce]

I hava a db server (mysql) in my web application, it include 550
tables and about 10 rows in each table. Now, I want to change the
db server to postgresql and construct a HA environment.

I have two 2U servers to build postgresql server (one is master, the
other is slave), and two 1U servers for any purpose about the ha
environment. And I expect  to use the FreeBSD as the operation system.

My request is, when a server fail (no matter the master or slave), the
web server can still access (read/write) the database correctly.

I have three ideas about the HA environment, does anyone give me some advices?
Or guide me to learn more advanced ideas. Thanks.  :)

First idea:
I install the pgpool on each web server (the web server farm include
16 web servers), and configure the pgpool as replication mode. The web
application (written by php) access to the db server through the local
pgpool daemon.
The idea sounds a little unusual, dose it seems workable?
I just use very simple sql statment in the web application, no any
complex statment.

Second idea:
I install the pgpool and linux-ha on the two 1U server, and configure
the pgpool as replication mode. The web application access to the db
server through the pgpool daemon.

Third idea:
I install the Slony-I, linux-ha and postgresql on the same server of
the two 2U servers. The web application access to the db server
directly and without pgpool.

sincerely,
proace

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match