pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedReplication mode
16+ messages / 9 participants
[nested] [flat]
* Replication mode
@ 2018-01-10 20:24 Azimuddin Mohammed <azimeiu@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Azimuddin Mohammed @ 2018-01-10 20:24 UTC (permalink / raw)
To: pgsql-admin
Hello,
May I know what is the core difference between HOT standby. Warm Stand by ?
--
Regards,
Azim
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Replication mode
@ 2018-01-10 22:00 Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
parent: Azimuddin Mohammed <azimeiu@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Peter Eisentraut @ 2018-01-10 22:00 UTC (permalink / raw)
To: Azimuddin Mohammed <azimeiu@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Replication mode
@ 2018-01-10 22:11 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
parent: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
0 siblings, 2 replies; 16+ messages in thread
From: Mark Kirkwood @ 2018-01-10 22:11 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; Azimuddin Mohammed <azimeiu@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Replication mode
@ 2018-01-10 22:17 Azimuddin Mohammed <azimeiu@gmail.com>
parent: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
1 sibling, 2 replies; 16+ messages in thread
From: Azimuddin Mohammed @ 2018-01-10 22:17 UTC (permalink / raw)
To: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>; +Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; pgsql-admin
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.
On Jan 10, 2018 4:11 PM, "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz>
wrote:
> 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
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Replication mode
@ 2018-01-10 22:36 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
parent: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
1 sibling, 1 reply; 16+ messages in thread
From: Mark Kirkwood @ 2018-01-10 22:36 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; Azimuddin Mohammed <azimeiu@gmail.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Replication mode
@ 2018-01-10 22:40 Satish <satish.alreja@gmail.com>
parent: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 0 replies; 16+ messages in thread
From: Satish @ 2018-01-10 22:40 UTC (permalink / raw)
To: pgsql-admin
Urgent - Please unsubscribe satish.alreja@gmail.com from all Postgres lists.
On Wed, Jan 10, 2018 at 5:36 PM, Mark Kirkwood <
mark.kirkwood@catalyst.net.nz> wrote:
>
>
> 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.
>
>
--
Satish
Ph: +1 (704) 464 0160 (Home)
+1 (704) 464 0611 (Home)
+1 (704) 960 0226 (Mob)
Do all the good you can, by all the means you can, in all the ways you can,
in all the places you can, at all the times you can, to all the people you
can, as long as ever you can.
- John Wesley
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Replication mode
@ 2018-01-10 22:43 scott ribe <scott_ribe@elevated-dev.com>
parent: Azimuddin Mohammed <azimeiu@gmail.com>
1 sibling, 0 replies; 16+ messages in thread
From: scott ribe @ 2018-01-10 22:43 UTC (permalink / raw)
To: Azimuddin Mohammed <azimeiu@gmail.com>; +Cc: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>; Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; pgsql-admin
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 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.
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Replication mode
@ 2018-01-10 22:44 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
parent: Azimuddin Mohammed <azimeiu@gmail.com>
1 sibling, 1 reply; 16+ messages in thread
From: Mark Kirkwood @ 2018-01-10 22:44 UTC (permalink / raw)
To: Azimuddin Mohammed <azimeiu@gmail.com>; +Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Replication mode
@ 2018-01-11 00:08 Azimuddin Mohammed <azimeiu@gmail.com>
parent: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 1 reply; 16+ messages in thread
From: Azimuddin Mohammed @ 2018-01-11 00:08 UTC (permalink / raw)
To: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>; +Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; pgsql-admin
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...
Appreciate your response.
On Jan 10, 2018 4:44 PM, "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz>
wrote:
> 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
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Replication mode
@ 2018-01-11 00:21 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
parent: Azimuddin Mohammed <azimeiu@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Mark Kirkwood @ 2018-01-11 00:21 UTC (permalink / raw)
To: Azimuddin Mohammed <azimeiu@gmail.com>; +Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Replication mode
@ 2018-01-11 01:10 Azimuddin Mohammed <azimeiu@gmail.com>
parent: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 4 replies; 16+ messages in thread
From: Azimuddin Mohammed @ 2018-01-11 01:10 UTC (permalink / raw)
To: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>; +Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; pgsql-admin
:-)
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.
On Jan 10, 2018 6:21 PM, "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz>
wrote:
> 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
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Replication mode
@ 2018-01-11 01:37 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
parent: Azimuddin Mohammed <azimeiu@gmail.com>
3 siblings, 0 replies; 16+ messages in thread
From: Mark Kirkwood @ 2018-01-11 01:37 UTC (permalink / raw)
To: Azimuddin Mohammed <azimeiu@gmail.com>; +Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Replication mode
@ 2018-01-11 01:39 Rui DeSousa <rui.desousa@icloud.com>
parent: Azimuddin Mohammed <azimeiu@gmail.com>
3 siblings, 0 replies; 16+ messages in thread
From: Rui DeSousa @ 2018-01-11 01:39 UTC (permalink / raw)
To: Azimuddin Mohammed <azimeiu@gmail.com>; +Cc: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>; Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; pgsql-admin
Azimuddin,
Then you should look at the tools you have at hand. i.e. Using nothing but the OS (FreeBSD/CARP/ZFS), Postgres and some custom scripts.
In the postgres.conf file setup synchronous replication and the application_name in the recovery.conf file:
synchronous_standby_names = '1 (standby, witness)'
----------- -----------
| | | |
| primary |---sync--->| standby |
| | | |
----------- -----------
| |
| |
---------carp-----------
|
|
async/sync
|
|
-----------
| |
| witness |
| |
-----------
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Replication mode
@ 2018-01-11 01:59 Scott Marlowe <scott.marlowe@gmail.com>
parent: Azimuddin Mohammed <azimeiu@gmail.com>
3 siblings, 0 replies; 16+ messages in thread
From: Scott Marlowe @ 2018-01-11 01:59 UTC (permalink / raw)
To: Azimuddin Mohammed <azimeiu@gmail.com>; +Cc: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>; Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; pgsql-admin
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?
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Replication mode
@ 2018-01-11 02:19 Ramesh naik <ramesh.esl@gmail.com>
parent: Azimuddin Mohammed <azimeiu@gmail.com>
3 siblings, 1 reply; 16+ messages in thread
From: Ramesh naik @ 2018-01-11 02:19 UTC (permalink / raw)
To: Azimuddin Mohammed <azimeiu@gmail.com>; +Cc: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>; Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; pgsql-admin
Hi Azim,
Pgpool is not of SPOF. You can use watchdog feature in PgPool-II to achieve
HA.
I’ve used it before. I didn’t find any difficulties in configuring or
managing
FYI..
http://www.pgpool.net/docs/latest/en/html/runtime-watchdog-config.html
On Thu, 11 Jan 2018 at 9:11 AM, Azimuddin Mohammed <azimeiu@gmail.com>
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.
>
>
> On Jan 10, 2018 6:21 PM, "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz>
> wrote:
>
>> 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
>>
> --
Regards,
Ramesh Naik E
+65 8202 7519
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Replication mode
@ 2018-01-12 12:14 Shreeyansh Dba <shreeyansh2014@gmail.com>
parent: Ramesh naik <ramesh.esl@gmail.com>
0 siblings, 0 replies; 16+ messages in thread
From: Shreeyansh Dba @ 2018-01-12 12:14 UTC (permalink / raw)
To: Ramesh naik <ramesh.esl@gmail.com>; +Cc: Azimuddin Mohammed <azimeiu@gmail.com>; Mark Kirkwood <mark.kirkwood@catalyst.net.nz>; Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; pgsql-admin
The HA & SPOF dependency can be removed completely with help of PostgreSQL
& Pgpool-II design and replace no use of any licensed software components.
We have implemented a very complex HA architecture at one of our customer
using Open Source tools (PostgreSQL/PgpoolII).
--
[image: http://www.shreeyansh.com] <http://www.shreeyansh.com;
On Thu, Jan 11, 2018 at 7:49 AM, Ramesh naik <ramesh.esl@gmail.com> wrote:
> Hi Azim,
>
> Pgpool is not of SPOF. You can use watchdog feature in PgPool-II to
> achieve HA.
> I’ve used it before. I didn’t find any difficulties in configuring or
> managing
>
> FYI..
> http://www.pgpool.net/docs/latest/en/html/runtime-watchdog-config.html
>
> On Thu, 11 Jan 2018 at 9:11 AM, Azimuddin Mohammed <azimeiu@gmail.com>
> 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.
>>
>>
>> On Jan 10, 2018 6:21 PM, "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz>
>> wrote:
>>
>>> 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
>>>
>> --
> Regards,
> Ramesh Naik E
>
> +65 8202 7519
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2018-01-12 12:14 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-01-10 20:24 Replication mode Azimuddin Mohammed <azimeiu@gmail.com>
2018-01-10 22:00 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2018-01-10 22:11 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-01-10 22:17 ` Azimuddin Mohammed <azimeiu@gmail.com>
2018-01-10 22:43 ` scott ribe <scott_ribe@elevated-dev.com>
2018-01-10 22:44 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-01-11 00:08 ` Azimuddin Mohammed <azimeiu@gmail.com>
2018-01-11 00:21 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-01-11 01:10 ` Azimuddin Mohammed <azimeiu@gmail.com>
2018-01-11 01:37 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-01-11 01:39 ` Rui DeSousa <rui.desousa@icloud.com>
2018-01-11 01:59 ` Scott Marlowe <scott.marlowe@gmail.com>
2018-01-11 02:19 ` Ramesh naik <ramesh.esl@gmail.com>
2018-01-12 12:14 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-01-10 22:36 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-01-10 22:40 ` Satish <satish.alreja@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox