Thread: 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@abpni.co.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@abpni.co.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@abpni.co.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@abpni.co.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@abpni.co.uk> wrote: > > On 01/11/10 21:10, Vick Khera wrote: >> >> On Mon, Nov 1, 2010 at 3:53 PM, Jonathan Tripathy<jonnyt@abpni.co.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@abpni.co.uk> wrote: >> On 01/11/10 21:10, Vick Khera wrote: >>> On Mon, Nov 1, 2010 at 3:53 PM, Jonathan Tripathy<jonnyt@abpni.co.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@abpni.co.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@abpni.co.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@postgresql.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@abpni.co.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@abpni.co.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@abpni.co.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.Reynolds@ateb.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.rembialkowski@gmail.com
http://filip.rembialkowski.net/