Re: Replication

Hi Everyone,

I'm looking for the best solution for "Hot Standbys" where once the
primary server fails, the standby will take over and act just like the
master did. The standby must support INSERTS and UPDATES as well (once
the master has failed)

Are there any solutions like this? Looking on the Postgresql site, all
the standby solutions seem to be read only..

Thanks

Jonathan Tripathy wrote on 01.11.2010 20:53:
> Hi Everyone,
>
> I'm looking for the best solution for "Hot Standbys" where once the
> primary server fails, the standby will take over and act just like
> the master did. The standby must support INSERTS and UPDATES as well
> (once the master has failed)
>
> Are there any solutions like this? Looking on the Postgresql site,
> all the standby solutions seem to be read only..

9.0 has streaming replication and "Hot Standby"

http://www.postgresql.org/docs/current/static/hot-standby.html
http://www.postgresql.org/docs/current/static/warm-standby.html#STREAMING-REPLICATION

Regards
Thomas

On 01/11/10 20:01, Thomas Kellerer wrote:
> Jonathan Tripathy wrote on 01.11.2010 20:53:
>> Hi Everyone,
>>
>> I'm looking for the best solution for "Hot Standbys" where once the
>> primary server fails, the standby will take over and act just like
>> the master did. The standby must support INSERTS and UPDATES as well
>> (once the master has failed)
>>
>> Are there any solutions like this? Looking on the Postgresql site,
>> all the standby solutions seem to be read only..
>
> 9.0 has streaming replication and "Hot Standby"
>
> http://www.postgresql.org/docs/current/static/hot-standby.html
> http://www.postgresql.org/docs/current/static/warm-standby.html#STREAMING-REPLICATION
>
>
> Regards
> Thomas
>
>
>
But does that not only allow "read-only" things to work on the standby?

On Mon, Nov 1, 2010 at 2:12 PM, Jonathan Tripathy <jonnyt(at)abpni(dot)co(dot)uk> wrote:
>
> On 01/11/10 20:01, Thomas Kellerer wrote:
>>
>> Jonathan Tripathy wrote on 01.11.2010 20:53:
>>>
>>> Hi Everyone,
>>>
>>> I'm looking for the best solution for "Hot Standbys" where once the
>>> primary server fails, the standby will take over and act just like
>>> the master did. The standby must support INSERTS and UPDATES as well
>>> (once the master has failed)
>>>
>>> Are there any solutions like this? Looking on the Postgresql site,
>>> all the standby solutions seem to be read only..
>>
>> 9.0 has streaming replication and "Hot Standby"
>>
>> http://www.postgresql.org/docs/current/static/hot-standby.html
>>
>> http://www.postgresql.org/docs/current/static/warm-standby.html#STREAMING-REPLICATION
>>
>> Regards
>> Thomas
>>
>>
>>
> But does that not only allow "read-only" things to work on the standby?

Yep. Generally when to fail over is considered a business decision.
I think only pgpool supports automatic failover but has a lot of
limitations to deal with otherwise.

Jonathan Tripathy wrote on 01.11.2010 21:12:
>>
>> 9.0 has streaming replication and "Hot Standby"
>>
>> http://www.postgresql.org/docs/current/static/hot-standby.html
>> http://www.postgresql.org/docs/current/static/warm-standby.html#STREAMING-REPLICATION
>>
>>
> But does that not only allow "read-only" things to work on the standby?
>

But you didn't ask for read/write on the standby, only for a standby that can take of the master once the master fails:
"must support INSERTS and UPDATES as well (once the master has failed)"

That's exactly what the hot standby does: As long as it is in standby mode it's read-only.
Once the failover has happened the standby is the new master and will allow read/write access.

Thomas

On 01/11/10 20:21, Scott Marlowe wrote:
> On Mon, Nov 1, 2010 at 2:12 PM, Jonathan Tripathy<jonnyt(at)abpni(dot)co(dot)uk> wrote:
>> On 01/11/10 20:01, Thomas Kellerer wrote:
>>> Jonathan Tripathy wrote on 01.11.2010 20:53:
>>>> Hi Everyone,
>>>>
>>>> I'm looking for the best solution for "Hot Standbys" where once the
>>>> primary server fails, the standby will take over and act just like
>>>> the master did. The standby must support INSERTS and UPDATES as well
>>>> (once the master has failed)
>>>>
>>>> Are there any solutions like this? Looking on the Postgresql site,
>>>> all the standby solutions seem to be read only..
>>> 9.0 has streaming replication and "Hot Standby"
>>>
>>> http://www.postgresql.org/docs/current/static/hot-standby.html
>>>
>>> http://www.postgresql.org/docs/current/static/warm-standby.html#STREAMING-REPLICATION
>>>
>>> Regards
>>> Thomas
>>>
>>>
>>>
>> But does that not only allow "read-only" things to work on the standby?
> Yep. Generally when to fail over is considered a business decision.
> I think only pgpool supports automatic failover but has a lot of
> limitations to deal with otherwise.

So really Postgresql doesn't have any "Hot Standbys" that once fail-over
has occurred, the system can act as normal? For this, would I have to
looking in Xen or VMWare HA?

I'm guessing the standbys in the "warm-failover" setup allow write
operations?

Thanks

On 01/11/10 20:26, Thomas Kellerer wrote:
> Jonathan Tripathy wrote on 01.11.2010 21:12:
>>>
>>> 9.0 has streaming replication and "Hot Standby"
>>>
>>> http://www.postgresql.org/docs/current/static/hot-standby.html
>>> http://www.postgresql.org/docs/current/static/warm-standby.html#STREAMING-REPLICATION
>>>
>>>
>>>
>> But does that not only allow "read-only" things to work on the standby?
>>
>
> But you didn't ask for read/write on the standby, only for a standby
> that can take of the master once the master fails:
> "must support INSERTS and UPDATES as well (once the master has failed)"
>
> That's exactly what the hot standby does: As long as it is in standby
> mode it's read-only.
> Once the failover has happened the standby is the new master and will
> allow read/write access.
>
> Thomas
>
>
Ahh!! So in both those links above, once the master has failed, the
standby will support writes (As it not acts like the master)?

Thanks

On Mon, Nov 1, 2010 at 3:53 PM, Jonathan Tripathy <jonnyt(at)abpni(dot)co(dot)uk> wrote:
> The standby must support INSERTS and UPDATES as well (once the master has
> failed)
>
> Are there any solutions like this? Looking on the Postgresql site, all the
> standby solutions seem to be read only..

If they are RO it is only while they are replicas, not masters. Once
the server is upgraded to the master role, it becomes RW.

On 01/11/10 21:10, Vick Khera wrote:
> On Mon, Nov 1, 2010 at 3:53 PM, Jonathan Tripathy<jonnyt(at)abpni(dot)co(dot)uk> wrote:
>> The standby must support INSERTS and UPDATES as well (once the master has
>> failed)
>>
>> Are there any solutions like this? Looking on the Postgresql site, all the
>> standby solutions seem to be read only..
> If they are RO it is only while they are replicas, not masters. Once
> the server is upgraded to the master role, it becomes RW.
>
So in the "Hot Standby" setup as described in
http://www.postgresql.org/docs/current/static/hot-standby.html , how
would I automatically make the slave a master?

On Mon, Nov 1, 2010 at 4:39 PM, Jonathan Tripathy <jonnyt(at)abpni(dot)co(dot)uk> wrote:
>
> On 01/11/10 21:10, Vick Khera wrote:
>>
>> On Mon, Nov 1, 2010 at 3:53 PM, Jonathan Tripathy<jonnyt(at)abpni(dot)co(dot)uk>
>>  wrote:
>>>
>>> The standby must support INSERTS and UPDATES as well (once the master has
>>> failed)
>>>
>>> Are there any solutions like this? Looking on the Postgresql site, all
>>> the
>>> standby solutions seem to be read only..
>>
>> If they are RO it is only while they are replicas, not masters.  Once
>> the server is upgraded to the master role, it becomes RW.
>>
> So in the "Hot Standby" setup as described in
> http://www.postgresql.org/docs/current/static/hot-standby.html , how would I
> automatically make the slave a master?

I think you're looking for this:
http://www.postgresql.org/docs/current/static/warm-standby-failover.html

--
To understand recursion, one must first understand recursion.

On 02/11/10 01:56, Scott Marlowe wrote:
> On Mon, Nov 1, 2010 at 4:39 PM, Jonathan Tripathy<jonnyt(at)abpni(dot)co(dot)uk> wrote:
>> On 01/11/10 21:10, Vick Khera wrote:
>>> On Mon, Nov 1, 2010 at 3:53 PM, Jonathan Tripathy<jonnyt(at)abpni(dot)co(dot)uk>
>>> wrote:
>>>> The standby must support INSERTS and UPDATES as well (once the master has
>>>> failed)
>>>>
>>>> Are there any solutions like this? Looking on the Postgresql site, all
>>>> the
>>>> standby solutions seem to be read only..
>>> If they are RO it is only while they are replicas, not masters. Once
>>> the server is upgraded to the master role, it becomes RW.
>>>
>> So in the "Hot Standby" setup as described in
>> http://www.postgresql.org/docs/current/static/hot-standby.html , how would I
>> automatically make the slave a master?
> I think you're looking for this:
> http://www.postgresql.org/docs/current/static/warm-standby-failover.html
>
What is the difference between the "Hot-Standby" and "Warm-Standby"? Is
the only different that the "Hot-Standby" standby servers are read-only,
whereas the "Warm-Standby" standbys can't be queried at all?

Thanks

On Tue, Nov 2, 2010 at 2:59 AM, Jonathan Tripathy <jonnyt(at)abpni(dot)co(dot)uk> wrote:
> What is the difference between the "Hot-Standby" and "Warm-Standby"? Is the
> only different that the "Hot-Standby" standby servers are read-only, whereas
> the "Warm-Standby" standbys can't be queried at all?
>

That's the general definition of those two terms as applied to a
database server.

On Mon, Nov 1, 2010 at 6:39 PM, Jonathan Tripathy <jonnyt(at)abpni(dot)co(dot)uk> wrote:
> So in the "Hot Standby" setup as described in
> http://www.postgresql.org/docs/current/static/hot-standby.html , how would I
> automatically make the slave a master?

If you plan to make it automatic, be absolutely 1000000% sure that
your automated criteria for doing the switchover is really 1000000%
accurate, else you may end up switching when you didn't really want to
for some temporary failure condition. I've never been able to define
something that perfect so we still only ever do manual switchovers.

Based on your questions, you perhaps should be seeking the advice of a
paid consultant expert in such matters if you really value your data.

________________________________

From: pgsql-general-owner(at)postgresql(dot)org on behalf of Vick Khera
Sent: Tue 02/11/2010 13:18
To: pgsql-general
Subject: Re: [GENERAL] Replication

On Tue, Nov 2, 2010 at 2:59 AM, Jonathan Tripathy <jonnyt(at)abpni(dot)co(dot)uk> wrote:
> What is the difference between the "Hot-Standby" and "Warm-Standby"? Is the
> only different that the "Hot-Standby" standby servers are read-only, whereas
> the "Warm-Standby" standbys can't be queried at all?
>

That's the general definition of those two terms as applied to a
database server.

------------------------------------------------------------------------------------------
Excellent!

In terms of streaming "Warm-Standby" replication, how much data loss will occur? Are we talking seconds, minutes, or hours? Let's assume a lightly used database (maybe 50 update queries an hour) and the master and slave are connected by Gigabit ethernet

Thanks

Hey Folks - have a coded myself into a corner yet?

I have a situation with a select count / group by / order by query that
I need to limit each group to 500 entries. Not seeing a way to do this
in a single query, do I need to use multiple queries?

Group x has about 200 entries in it; group y has about 5-8k per x.

select x, y, count(*) as counter from mytable

group by x, y

order by x, counter, y

I only want the first 500 for each x.

Any tips or tricks someone might know would be appreciated.

I'm using postgres 8.3.7.

Thanks, Bill

Jonathan Tripathy <jonnyt(at)abpni(dot)co(dot)uk> writes:
> What is the difference between the "Hot-Standby" and "Warm-Standby"? Is the
> only different that the "Hot-Standby" standby servers are read-only, whereas
> the "Warm-Standby" standbys can't be queried at all?

That and the fact that running queries are not canceled at the time you
flick the switch to have your standby a master. The ongoing read-only
traffic is not affected. That's hot.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On Tue, 2010-11-02 at 06:59 +0000, Jonathan Tripathy wrote:
> >>>
> >> So in the "Hot Standby" setup as described in
> >> http://www.postgresql.org/docs/current/static/hot-standby.html , how would I
> >> automatically make the slave a master?

> > I think you're looking for this:
> > http://www.postgresql.org/docs/current/static/warm-standby-failover.html
> >
> What is the difference between the "Hot-Standby" and "Warm-Standby"? Is
> the only different that the "Hot-Standby" standby servers are read-only,
> whereas the "Warm-Standby" standbys can't be queried at all?

The title of the second HTML page is now out of date. So there is no
warm/hot confusion to worry about, just the name of the page and URL.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services

On 02/11/10 21:21, Vick Khera wrote:
> On Mon, Nov 1, 2010 at 6:39 PM, Jonathan Tripathy <jonnyt(at)abpni(dot)co(dot)uk> wrote:
>> So in the "Hot Standby" setup as described in
>> http://www.postgresql.org/docs/current/static/hot-standby.html , how would I
>> automatically make the slave a master?
>
> If you plan to make it automatic, be absolutely 1000000% sure that
> your automated criteria for doing the switchover is really 1000000%
> accurate, else you may end up switching when you didn't really want to
> for some temporary failure condition. I've never been able to define
> something that perfect so we still only ever do manual switchovers.

From what I've seen, the only way automatic switchovers ever work sanely
is when the node that's promoting its self has a way to pull the plug on
the master it's taking over from. A USB-controlled power board seems to
be a popular cheap option, and isolation on a fibre-channel switch a
more expensive option.

Of course, even then you have to be sure your method for killing the old
master will always work when the slave promotes its self to master, and
will never trigger under any other circumstances. Good luck with that.

(Reading the above par, does anyone else find some IT terminology, when
read out of context, kind of creepy? Unix's killing of children in
particular.)

>
> Based on your questions, you perhaps should be seeking the advice of a
> paid consultant expert in such matters if you really value your data.

+1

There are many people on this list who do paid work. See the PostgreSQL
website for a list of companies that work with PostgreSQL.

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/

2010/11/2 Bill Reynolds <Bill(dot)Reynolds(at)ateb(dot)com>:
>    I’m using postgres 8.3.7.

that's a pity because in 8.4 we have window functions which make this
possible in one query:
select * from (
select x, y, count(*) as counter,
row_number() over(partition by x order by count(*)) rn
from mytable
group by x, y order by x, count(*), y
) subq where subq.rn <= 5;

in 8,3 you will have to use some tricks... for example, temporary
sequence for every group.

CREATE LANGUAGE plpgsql;
create or replace function exec(text) returns text as 'begin execute
$1;return $1;end' language plpgsql;
select exec('create temp sequence tmpseq'||x) from (select distinct x
from mytable) q;
select x,y,counter from (select x, y, count(*) as counter from mytable
group by x, y order by x, counter, y) subq where
nextval(quote_ident('tmpseq'||x))<=5;

--
Filip Rembiałkowski
JID,mailto:filip(dot)rembialkowski(at)gmail(dot)com
http://filip.rembialkowski.net/