Thread: Native DB replication for PG

Native DB replication for PG

From
"Gauthier, Dave"
Date:

I believe v9 will have native DB master/slave DB replication (correct if wrong).  If so, what’s the best guess on when will v9 be released?

 

Thanks!

Re: Native DB replication for PG

From
Merlin Moncure
Date:
On Fri, Apr 30, 2010 at 2:17 PM, Gauthier, Dave <dave.gauthier@intel.com> wrote:
> I believe v9 will have native DB master/slave DB replication (correct if
> wrong).  If so, what’s the best guess on when will v9 be released?

well, depends on how you define replication, but yes.   my _guess_ on
release is late summer.  the key event to watch for is entering beta
then you can figure 1-2 months.

merlin

Re: Native DB replication for PG

From
Scott Marlowe
Date:
On Fri, Apr 30, 2010 at 12:17 PM, Gauthier, Dave
<dave.gauthier@intel.com> wrote:
> I believe v9 will have native DB master/slave DB replication (correct if
> wrong).  If so, what’s the best guess on when will v9 be released?

If I had to plan server deployments for the next year (and I do) I'd
be sticking with pg 8.3 and a proven replication engine.  Next summer
I'll be seriously considering 9.0 and hot PITR slaves.

Re: Native DB replication for PG

From
"Gauthier, Dave"
Date:
>> On Fri, Apr 30, 2010 at 12:17 PM, Gauthier, Dave
>> <dave.gauthier@intel.com> wrote:
>>> I believe v9 will have native DB master/slave DB replication (correct if
>>> wrong).  If so, what's the best guess on when will v9 be released?
>>
>> If I had to plan server deployments for the next year (and I do) I'd
>> be sticking with pg 8.3 and a proven replication engine.  Next summer
>
>Surely you mean 8.4? :-)
>
>Ray.

I googled postgres 8.4 feathres and found no mention of DB replication support at...

http://www.postgresql.org/about/press/features84.html

-----Original Message-----
From: Raymond O'Donnell [mailto:rod@iol.ie]
Sent: Friday, April 30, 2010 4:39 PM
To: Scott Marlowe
Cc: Gauthier, Dave; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Native DB replication for PG

On 30/04/2010 21:30, Scott Marlowe wrote:
> On Fri, Apr 30, 2010 at 12:17 PM, Gauthier, Dave
> <dave.gauthier@intel.com> wrote:
>> I believe v9 will have native DB master/slave DB replication (correct if
>> wrong).  If so, what's the best guess on when will v9 be released?
>
> If I had to plan server deployments for the next year (and I do) I'd
> be sticking with pg 8.3 and a proven replication engine.  Next summer

Surely you mean 8.4? :-)

Ray.

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

Re: Native DB replication for PG

From
"Joshua D. Drake"
Date:
On Fri, 2010-04-30 at 13:42 -0700, Gauthier, Dave wrote:
> >> On Fri, Apr 30, 2010 at 12:17 PM, Gauthier, Dave
> >> <dave.gauthier@intel.com> wrote:
> >>> I believe v9 will have native DB master/slave DB replication (correct if
> >>> wrong).  If so, what's the best guess on when will v9 be released?
> >>
> >> If I had to plan server deployments for the next year (and I do) I'd
> >> be sticking with pg 8.3 and a proven replication engine.  Next summer
> >
> >Surely you mean 8.4? :-)

No, I would buy the 8.3 argument as well. Depending on your conservative
level. 8.4 is fine and all but 8.3 is about as rock solid as it gets. It
is a great baseline at this point.

Joshua D. Drake


--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering

Re: Native DB replication for PG

From
Scott Marlowe
Date:
On Fri, Apr 30, 2010 at 2:38 PM, Raymond O'Donnell <rod@iol.ie> wrote:
> On 30/04/2010 21:30, Scott Marlowe wrote:
>> On Fri, Apr 30, 2010 at 12:17 PM, Gauthier, Dave
>> <dave.gauthier@intel.com> wrote:
>>> I believe v9 will have native DB master/slave DB replication (correct if
>>> wrong).  If so, what’s the best guess on when will v9 be released?
>>
>> If I had to plan server deployments for the next year (and I do) I'd
>> be sticking with pg 8.3 and a proven replication engine.  Next summer
>
> Surely you mean 8.4? :-)

Nope.  8.3 and 8.4 have similar performance, and we don't really need
any of the newer features from 8.4 right yet.  Also 8.4.1 was crashing
on us under heavy load and I haven't had time to investigate issue
yet.

Re: Native DB replication for PG

From
Greg Smith
Date:
Joshua D. Drake wrote:
> On Fri, 2010-04-30 at 13:42 -0700, Gauthier, Dave wrote:
>
>>>> If I had to plan server deployments for the next year (and I do) I'd
>>>> be sticking with pg 8.3 and a proven replication engine.  Next summer
>>>>
>>> Surely you mean 8.4? :-)
>>>
>
> No, I would buy the 8.3 argument as well. Depending on your conservative
> level. 8.4 is fine and all but 8.3 is about as rock solid as it gets.

Unless you don't vacuum enough on a bigger database, run out of FSM
pages, and the whole vacuum strategy goes to hell afterwards.  I would
say that running into that issue is *probable* for an 8.3 install of any
significant size, whereas the odds of running into a regression in 8.4
relative to 8.3 is pretty low.  The whole "the older version is always
more reliable" mantra doesn't make sense when you've got a major known
issue in the older release that just goes away by using the newer one,
and I feel that's the case with 8.4 vs. 8.3.

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


Re: Native DB replication for PG

From
"Joshua D. Drake"
Date:
On Fri, 2010-04-30 at 13:42 -0700, Gauthier, Dave wrote:
> >> On Fri, Apr 30, 2010 at 12:17 PM, Gauthier, Dave
> >> <dave.gauthier@intel.com> wrote:
> >>> I believe v9 will have native DB master/slave DB replication (correct if
> >>> wrong).  If so, what's the best guess on when will v9 be released?
> >>
> >> If I had to plan server deployments for the next year (and I do) I'd
> >> be sticking with pg 8.3 and a proven replication engine.  Next summer
> >
> >Surely you mean 8.4? :-)

No, I would buy the 8.3 argument as well. Depending on your conservative
level. 8.4 is fine and all but 8.3 is about as rock solid as it gets. It
is a great baseline at this point.

Joshua D. Drake


--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering



Re: Native DB replication for PG

From
Greg Smith
Date:
Scott Marlowe wrote:
> I tested 8.4 what I thought was fairly hardly last year only
> to have 8.4.1 die under the same load that 8.3 handled without a
> problem, and reverted to the known working version putting testing
> 8.4.1 on hold.
>
> So to ME, the choice is a fully functional 8.3 installation that has
> NO problems with free space map because of configuration choices, or
> an 8.4 with a known (to me) issue of crashing and dying.
>

FYI, since December of 2009 (release of 8.4.2) there have been 10 bugs
fixed with the word "crash" in their description, as well as 7 memory
leaks that could potentially lead to crash.  Even six months ago I was
still hesitant to push 8.4 toward production systems; the number of bugs
shaken out in the last two releases has been substantial.

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


Re: Native DB replication for PG

From
Scott Marlowe
Date:
On Sat, May 1, 2010 at 12:59 AM, Greg Smith <greg@2ndquadrant.com> wrote:
> Scott Marlowe wrote:
>>
>> I tested 8.4 what I thought was fairly hardly last year only
>> to have 8.4.1 die under the same load that 8.3 handled without a
>> problem, and reverted to the known working version putting testing
>> 8.4.1 on hold.
>>
>> So to ME, the choice is a fully functional 8.3 installation that has
>> NO problems with free space map because of configuration choices, or
>> an 8.4 with a known (to me) issue of crashing and dying.
>>
>
> FYI, since December of 2009 (release of 8.4.2) there have been 10 bugs fixed
> with the word "crash" in their description, as well as 7 memory leaks that
> could potentially lead to crash.  Even six months ago I was still hesitant
> to push 8.4 toward production systems; the number of bugs shaken out in the
> last two releases has been substantial.

Exactly, which is why I posted a followup saying I knew it was quite
possible the bug had been fixed.  But hope is not a method, so until I
can test to be sure the problem I was hitting was one of the ones
fixed, I'll keep production on 8.3 for now.  Because for me, it's
proven itself reliable over ~2 years of very heavy use.

Re: Native DB replication for PG

From
Devrim GÜNDÜZ
Date:
On Sat, 2010-05-01 at 02:59 -0400, Greg Smith wrote:
> FYI, since December of 2009 (release of 8.4.2) there have been 10 bugs
> fixed with the word "crash" in their description, as well as 7 memory
> leaks that could potentially lead to crash.  Even six months ago I was
> still hesitant to push 8.4 toward production systems; the number of
> bugs shaken out in the last two releases has been substantial.

FWIW, we are using 8.4.3 under heavy (thousands of transactions per
second) update+delete load (as compared to inserts), and new FSM helped
us a lot -- also it did not crash even for a second. We got rid of all
performance related issues after switching from 8.3 to 8.4.
--
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

Attachment

Re: Native DB replication for PG

From
Scott Marlowe
Date:
2010/5/1 Devrim GÜNDÜZ <devrim@gunduz.org>:
> On Sat, 2010-05-01 at 02:59 -0400, Greg Smith wrote:
>> FYI, since December of 2009 (release of 8.4.2) there have been 10 bugs
>> fixed with the word "crash" in their description, as well as 7 memory
>> leaks that could potentially lead to crash.  Even six months ago I was
>> still hesitant to push 8.4 toward production systems; the number of
>> bugs shaken out in the last two releases has been substantial.
>
> FWIW, we are using 8.4.3 under heavy (thousands of transactions per
> second) update+delete load (as compared to inserts), and new FSM helped
> us a lot -- also it did not crash even for a second. We got rid of all
> performance related issues after switching from 8.3 to 8.4.

I tested 8.4.1 under three different loads.  A mostly transactional
load, a most write load for storing web site statistics, and a
mnogosearch db with moderate load.  They all ran great for a week or
so in testing.  In order to put it into production we started with the
least important of those dbs, statistics.  We can afford to lose an
hour here or there no a saturday, so we tried it.  And it worked fine
for 3 weeks when pg 8.4.1, on the same machine that was still running
8.3.latest at the time, crashed.  Happened again a few days later, and
we switched back over during maintenance.  The old 8.3 db for stats
was still there so it was just a matter of taking down stats, loading
in the data from the 8.4 db and switching it over.

Mnogo was switched over at the same time as the stats db and has never
once crashed the 8.4.x db it is running on.  So whatever we were doing
that made 8.4 crash may have had to do with the partitioned nature of
our stats db, or some other issue unique to our stats db.

Re: Native DB replication for PG

From
Scott Marlowe
Date:
On Fri, Apr 30, 2010 at 9:02 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> Joshua D. Drake wrote:
>>
>> On Fri, 2010-04-30 at 13:42 -0700, Gauthier, Dave wrote:
>>
>>>>>
>>>>> If I had to plan server deployments for the next year (and I do) I'd
>>>>> be sticking with pg 8.3 and a proven replication engine.  Next summer
>>>>>
>>>>
>>>> Surely you mean 8.4? :-)
>>>>
>>
>> No, I would buy the 8.3 argument as well. Depending on your conservative
>> level. 8.4 is fine and all but 8.3 is about as rock solid as it gets.
>
> Unless you don't vacuum enough on a bigger database, run out of FSM pages,
> and the whole vacuum strategy goes to hell afterwards.  I would say that
> running into that issue is *probable* for an 8.3 install of any significant
> size, whereas the odds of running into a regression in 8.4 relative to 8.3
> is pretty low.  The whole "the older version is always more reliable" mantra
> doesn't make sense when you've got a major known issue in the older release
> that just goes away by using the newer one, and I feel that's the case with
> 8.4 vs. 8.3.

Exactly.  I've got a LOT of effort involved in free space map sizing
and monitoring on 8.3.  However, for me it's no longer a serious
problem.  Free space map is 10 to 20x what it needs to be on my
machines now and works like a charm in 8.3.  8.4 randomly crashed, and
honestly I can't afford to test and help fix it right now.  This
summer I can and will either with 9.0 or 8.4.  But we're talking db
crashes that were happening once every 2 to 3 weeks for me, so testing
it takes a lot of time for me.  And I can't do it with my productions
servers.  I tested 8.4 what I thought was fairly hardly last year only
to have 8.4.1 die under the same load that 8.3 handled without a
problem, and reverted to the known working version putting testing
8.4.1 on hold.

So to ME, the choice is a fully functional 8.3 installation that has
NO problems with free space map because of configuration choices, or
an 8.4 with a known (to me) issue of crashing and dying.

Re: Native DB replication for PG

From
Scott Marlowe
Date:
On Fri, Apr 30, 2010 at 11:33 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Fri, Apr 30, 2010 at 9:02 PM, Greg Smith <greg@2ndquadrant.com> wrote:
>> Joshua D. Drake wrote:
>>>
>>> On Fri, 2010-04-30 at 13:42 -0700, Gauthier, Dave wrote:
>>>
>>>>>>
>>>>>> If I had to plan server deployments for the next year (and I do) I'd
>>>>>> be sticking with pg 8.3 and a proven replication engine.  Next summer
>>>>>>
>>>>>
>>>>> Surely you mean 8.4? :-)
>>>>>
>>>
>>> No, I would buy the 8.3 argument as well. Depending on your conservative
>>> level. 8.4 is fine and all but 8.3 is about as rock solid as it gets.
>>
>> Unless you don't vacuum enough on a bigger database, run out of FSM pages,
>> and the whole vacuum strategy goes to hell afterwards.  I would say that
>> running into that issue is *probable* for an 8.3 install of any significant
>> size, whereas the odds of running into a regression in 8.4 relative to 8.3
>> is pretty low.  The whole "the older version is always more reliable" mantra
>> doesn't make sense when you've got a major known issue in the older release
>> that just goes away by using the newer one, and I feel that's the case with
>> 8.4 vs. 8.3.
>
> Exactly.  I've got a LOT of effort involved in free space map sizing
> and monitoring on 8.3.  However, for me it's no longer a serious
> problem.  Free space map is 10 to 20x what it needs to be on my
> machines now and works like a charm in 8.3.  8.4 randomly crashed, and
> honestly I can't afford to test and help fix it right now.  This
> summer I can and will either with 9.0 or 8.4.  But we're talking db
> crashes that were happening once every 2 to 3 weeks for me, so testing
> it takes a lot of time for me.  And I can't do it with my productions
> servers.  I tested 8.4 what I thought was fairly hardly last year only
> to have 8.4.1 die under the same load that 8.3 handled without a
> problem, and reverted to the known working version putting testing
> 8.4.1 on hold.
>
> So to ME, the choice is a fully functional 8.3 installation that has
> NO problems with free space map because of configuration choices, or
> an 8.4 with a known (to me) issue of crashing and dying.

Note that it could well be fixed by now, and I'll first test either
9.0 beta or 8.4.latest. as slony slaves before I go any further.

Re: Native DB replication for PG

From
Tom Lane
Date:
Greg Smith <greg@2ndquadrant.com> writes:
> Scott Marlowe wrote:
>> I tested 8.4 what I thought was fairly hardly last year only
>> to have 8.4.1 die under the same load that 8.3 handled without a
>> problem, and reverted to the known working version putting testing
>> 8.4.1 on hold.

> FYI, since December of 2009 (release of 8.4.2) there have been 10 bugs
> fixed with the word "crash" in their description, as well as 7 memory
> leaks that could potentially lead to crash.  Even six months ago I was
> still hesitant to push 8.4 toward production systems; the number of bugs
> shaken out in the last two releases has been substantial.

Are we reading the same CVS log?  I find quite a few commit messages
mentioning the word "crash", but the *only* post-8.4.0 crash fix that
applied to 8.4 and didn't also get committed into 8.3 (and often a
lot further back than that) was this, which made it into 8.4.2:

2009-09-22 11:46  tgl

    * src/: backend/catalog/dependency.c,
    test/regress/expected/rules.out, test/regress/sql/rules.sql
    (REL8_4_STABLE), backend/catalog/dependency.c,
    test/regress/expected/rules.out, test/regress/sql/rules.sql: Fix
    crash if a DROP is attempted on an internally-dependent object.
    Introduced in 8.4 rewrite of dependency.c.  Per bug #5072 from Amit
    Khandekar.

I searched the CVS log in detail back to the start of 2010, and found
only half a dozen patches of any flavor that were applied to 8.4 but not
8.3 (discounting cosmetic changes such as docs-only patches).  Only one
of these was a server crash condition, and most of them applied to
new-in-8.4 features that you wouldn't even exercise if you were simply
migrating an 8.3-compatible application.

So in general I think there is no objective evidence to support a
position that 8.4 is less stable than 8.3, at least not since about
8.4.2.  This is fairly typical of our release series, IME.  I expect
9.0 will have a much longer period before its bug curve falls to match
the previous releases, of course.

I'm curious to get to the bottom of Scott's report.  It's possible that
he hit one of the two or three 8.4-only crashes we fixed since 8.4.1;
or the bug may still be lurking.

            regards, tom lane

Re: Native DB replication for PG

From
Scott Marlowe
Date:
On Sat, May 1, 2010 at 9:27 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> I'm curious to get to the bottom of Scott's report.  It's possible that
> he hit one of the two or three 8.4-only crashes we fixed since 8.4.1;
> or the bug may still be lurking.

I'll definitely be testing it this summer to see if it triggers a
crash on the latest 8.4 and possibly 9.0 as well.  The stats db is the
ne place I can run bleeding edge releases since the data there isn't
really critical for daily operations, especially the data created
during the summer lull.

Re: Native DB replication for PG

From
Greg Smith
Date:
Tom Lane wrote:
> Greg Smith <greg@2ndquadrant.com> writes:
>
>> FYI, since December of 2009 (release of 8.4.2) there have been 10 bugs
>> fixed with the word "crash" in their description, as well as 7 memory
>> leaks that could potentially lead to crash.
> Are we reading the same CVS log?  I find quite a few commit messages
> mentioning the word "crash", but the *only* post-8.4.0 crash fix that
> applied to 8.4 and didn't also get committed into 8.3 (and often a
> lot further back than that) was this...

I based those figures on uses of the word "crash" in the release notes
for things fixed in 8.4.2 and 8.4.3, not the CVS logs.  And I didn't
filter out the stuff that was also backported, on the theory that Scott
might be running into one of those issues in his test 8.4.1 install, but
not his 8.3 one even though the 8.3 instance was theoretically
vulnerable to it as well.

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


Re: Native DB replication for PG

From
"Gauthier, Dave"
Date:
So, back to the base note, as far as native replication support, I should wait for v9 whic'll probably be out by the
endof the summer? 

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Saturday, May 01, 2010 12:16 PM
To: Tom Lane
Cc: Greg Smith; jd@commandprompt.com; Gauthier, Dave; rod@iol.ie; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Native DB replication for PG

On Sat, May 1, 2010 at 9:27 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> I'm curious to get to the bottom of Scott's report.  It's possible that
> he hit one of the two or three 8.4-only crashes we fixed since 8.4.1;
> or the bug may still be lurking.

I'll definitely be testing it this summer to see if it triggers a
crash on the latest 8.4 and possibly 9.0 as well.  The stats db is the
ne place I can run bleeding edge releases since the data there isn't
really critical for daily operations, especially the data created
during the summer lull.

Re: Native DB replication for PG

From
Raymond O'Donnell
Date:
On 30/04/2010 21:30, Scott Marlowe wrote:
> On Fri, Apr 30, 2010 at 12:17 PM, Gauthier, Dave
> <dave.gauthier@intel.com> wrote:
>> I believe v9 will have native DB master/slave DB replication (correct if
>> wrong).  If so, what’s the best guess on when will v9 be released?
>
> If I had to plan server deployments for the next year (and I do) I'd
> be sticking with pg 8.3 and a proven replication engine.  Next summer

Surely you mean 8.4? :-)

Ray.

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


Re: Native DB replication for PG

From
Merlin Moncure
Date:
On Mon, May 3, 2010 at 9:29 AM, Gauthier, Dave <dave.gauthier@intel.com> wrote:
> So, back to the base note, as far as native replication support, I should wait for v9 whic'll probably be out by the
endof the summer? 

for 'native' replication waiting for 9.0 is your only option, because
all of the other replication options available today are 3rd party
unless you count warm standby.

the various 3rd party replication solutions have pros and cons.
speaking generally, they are more complex to set up and administer
than hs/sr but also more flexible.

merlin

Re: Native DB replication for PG

From
Scott Marlowe
Date:
On Mon, May 3, 2010 at 7:29 AM, Gauthier, Dave <dave.gauthier@intel.com> wrote:
> So, back to the base note, as far as native replication support, I should wait for v9 whic'll probably be out by the
endof the summer? 

Yep.  You should really start testing now though if you're gonna use it.