Обсуждение: Replication mode

On 1/10/18 15:24, Azimuddin Mohammed wrote:
> May I know what is the core difference between HOT standby. Warm Stand by ?

In PostgreSQL, "hot standby" is a mode for an instance in recovery that
allows read-only commands to be executed on it.  The term "warm standby"
is not used by PostgreSQL, but you could think of a server in recovery
that does not allow queries to be a warm standby, if you wish.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 11/01/18 11:00, Peter Eisentraut wrote:

> On 1/10/18 15:24, Azimuddin Mohammed wrote:
>> May I know what is the core difference between HOT standby. Warm Stand by ?
> In PostgreSQL, "hot standby" is a mode for an instance in recovery that
> allows read-only commands to be executed on it.  The term "warm standby"
> is not used by PostgreSQL, but you could think of a server in recovery
> that does not allow queries to be a warm standby, if you wish.
>

We do talk about 'warm standby' in the docs:

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

...altho we do not precisely say how to setup this up as opposed to the 
'hot' variant. In fact it is pretty easy to (accidentally) get a warm 
standby by forgetting to set :

standby_mode = 'on'

in recovery.conf

Cheers

Mark

On 11/01/18 11:11, Mark Kirkwood wrote:
>
>
> ...altho we do not precisely say how to setup this up as opposed to 
> the 'hot' variant. In fact it is pretty easy to (accidentally) get a 
> warm standby by forgetting to set :
>
> standby_mode = 'on'
>
> in recovery.conf
>

Sorry - the above is completely wrong, should have said: setting

hot_standby = 'off'

in postgresql.conf gets you a 'warm' variant.

On Jan 10, 2018, at 3:17 PM, Azimuddin Mohammed <azimeiu@gmail.com> wrote:
>
> Thanks!
> But with hot standby my slave will be in read-only mode ...how can I just use my slave as a backup and not do any
readoperation and let my primary server do all read and write operation. If my primary fails I will do manual failover.
Thewhole idea is to remove any 3rd party open source tools as we are not sure how well they behave and avoid any single
pointof failure.  

Just don't send queries to the slave. (There is nothing in PG that would automatically distribute queries to it.)


--
Scott Ribe
https://www.linkedin.com/in/scottribe/
(303) 722-0567

On 11/01/18 11:17, Azimuddin Mohammed wrote:

> Thanks!
> But with hot standby my slave will be in read-only mode ...how can I 
> just use my slave as a backup and not do any read operation and let my 
> primary server do all read and write operation. If my primary fails I 
> will do manual failover. The whole idea is to remove any 3rd party 
> open source tools as we are not sure how well they behave and avoid 
> any single point of failure.
>

Whether you have made the standby/slave hot or merely warm doesn't 
really change the 'standby' part - the mechanism is identical (applying 
wal information to keep the it up to date with its primary). In either 
case they cannot be altered by clients (attempting to) connect to them. 
So most folk always make their standbys 'hot' (it is very convenient to 
have the *option* of being able to query them if you need to).

Does that help?

Cheers

Mark

On 11/01/18 13:08, Azimuddin Mohammed wrote:

> Thanks mark..
> That does help...
> Our db is not transactional ..it more of write few times read many 
> times ..What HA  do you  recommend for us by not using any open source 
> tools...and what could be the best backup startegy...
>

Wow - that 'not using any open source tools' restriction pretty much 
rules out any options I'd come up with (was that *really* what you meant 
to write - Postgres is after all open source too...)?

regards

Mark

On 11/01/18 14:10, Azimuddin Mohammed wrote:

> :-)
> Our fear is using open source  tools  for HA creates dependency and 
> single point of failure(pgpool). Or say pacemaker which our enterprise 
> do not recommend.
> We are planning to use postgres to use for our hadoop metastore which 
> is presently on Oracle.
>

Ok, I understand. The usual way this single point of failure thing is 
handled is to use something like a pair of HAProxy servers with 
redundant network switches (i.e make the HA manager itself HA). While 
this is a bit fiddly it is reasonably well documented and standard 
(experienced system and network admins should have no difficultly 
getting it to work).

There are also specialized Postgres related solutions for HA (e.g repmgr 
from 2ndquadrant) that might be worth a look as well.

regards

Mark

On Wed, Jan 10, 2018 at 6:10 PM, Azimuddin Mohammed <azimeiu@gmail.com> wrote:
> :-)
> Our fear is using open source  tools  for HA creates dependency and single
> point of failure(pgpool).

I fail to see how one is related to the other.

> Or say pacemaker which our enterprise do not
> recommend.

Why do your enterprise not recommend? Pacemaker paired with Corosync
is very capable. What do your enterprise recommend if not those two?
In fact they're very very good at eliminating single points of
failure. Pretty much seamless IP takeover etc.

> We are planning to use postgres to use for our hadoop metastore which is
> presently on Oracle.

Best of luck! Please let us know how it works.

I get the feeling there's some religious argument going on in the
background where you are about whether or not open source can do the
job etc. Let's hope postgresql gets the foot in the door eh?