replication in Postgres

On 11/25/07, Glyn Astill <glynastill@yahoo.co.uk> wrote:

So far the only methods I see would be usable for us are Slony I and
WAL log shipping.

WAL shipping probably does not work the way you think it does. The
secondary server that receives the log pieces is not actually able to
serve any queries; it only processes them as part of a sort of
indefinitely extended recovery procedure. Therefore, the current
shipping support is only good for maintaining a "warm standby" server.
The manual explains this in detail.

Someone is working on extending the current system to allow read-only
queries on a standby server [1]http://archives.postgresql.org/pgsql-hackers/2007-02/msg01390.php, thus making it a "hot standby", but
this feature apparently won't be included until 8.4 [2]http://archives.postgresql.org/pgsql-general/2007-09/msg00752.php.

[1]: http://archives.postgresql.org/pgsql-hackers/2007-02/msg01390.php
[2]: http://archives.postgresql.org/pgsql-general/2007-09/msg00752.php

Alexander.

On Sun, 2007-11-25 at 23:38 +0100, Alexander Staubo wrote:

On 11/25/07, Glyn Astill <glynastill@yahoo.co.uk> wrote:

So far the only methods I see would be usable for us are Slony I and
WAL log shipping.

WAL shipping probably does not work the way you think it does. The
secondary server that receives the log pieces is not actually able to
serve any queries; it only processes them as part of a sort of
indefinitely extended recovery procedure. Therefore, the current
shipping support is only good for maintaining a "warm standby" server.
The manual explains this in detail.

Someone is working on extending the current system to allow read-only
queries on a standby server [1], thus making it a "hot standby", but
this feature apparently won't be included until 8.4 [2].

[1] http://archives.postgresql.org/pgsql-hackers/2007-02/msg01390.php
[2] http://archives.postgresql.org/pgsql-general/2007-09/msg00752.php

http://archives.postgresql.org/pgsql-general/2007-11/msg01193.php

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

On Sun, 2007-11-25 at 22:18 +0000, Glyn Astill wrote:

So far the only methods I see would be usable for us are Slony I and
WAL log shipping.

Does anyone here have a similar setup or any recommendations?

This link may be of some use

http://www.2ndquadrant.com/replication.html

plus 2ndQuadrant now offers training to directly address this decision.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

Someone is working on extending the current system to allow read-only
queries on a standby server [1], thus making it a "hot standby", but
this feature apparently won't be included until 8.4 [2].

My 2 cents...

I would rather see someone working on true synchronous replication,
rather than a readable hot-standby. Yeah, I know, different problems
with different solutions. But,. for my money, a readable hot-standby
doesn't give me added functionality, it's just a performance option.
Whereas synchronous replication has much greater value from a the
perspective of running a 24x7 business.

I'm on the verge of dumping our commercial DB in favor of PostgreSQL,
but this is one issue that is holding me back.

Jeff

Jeff Larsen escribi�:

Someone is working on extending the current system to allow read-only
queries on a standby server [1], thus making it a "hot standby", but
this feature apparently won't be included until 8.4 [2].

My 2 cents...

I would rather see someone working on true synchronous replication,
rather than a readable hot-standby. Yeah, I know, different problems
with different solutions. But,. for my money, a readable hot-standby
doesn't give me added functionality, it's just a performance option.
Whereas synchronous replication has much greater value from a the
perspective of running a 24x7 business.

I'm on the verge of dumping our commercial DB in favor of PostgreSQL,
but this is one issue that is holding me back.

Maybe you can persuade Markus Schiltknecht to let you have a peek at his
Postgres-R project, http://www.postgres-r.org

http://www.postgres-r.org/about/sponsoring

--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"�Qu� importan los a�os? Lo que realmente importa es comprobar que
a fin de cuentas la mejor edad de la vida es estar vivo" (Mafalda)

Glyn Astill wrote:

Thanks everyone for your replies. EnterpriseDB looks like the way to
go if we want good replication.

Sorry, this makes no sense to me -- EnterpriseDB has no replication
solution that I know of.

Postgres-r sounds very nice but moving our organisations data onto a
system that it work in progress is very scary.

You are already offloading your data to PostgreSQL which is a work in
progress too ...

--
Alvaro Herrera http://www.PlanetPostgreSQL.org/
"En el principio del tiempo era el desencanto. Y era la desolaci�n. Y era
grande el esc�ndalo, y el destello de monitores y el crujir de teclas."
("Sean los P�jaros Pulentios", Daniel Correa)

On Mon, 2007-11-26 at 07:25 -0600, Jeff Larsen wrote:

Someone is working on extending the current system to allow read-only
queries on a standby server [1], thus making it a "hot standby", but
this feature apparently won't be included until 8.4 [2].

My 2 cents...

I would rather see someone working on true synchronous replication,
rather than a readable hot-standby. Yeah, I know, different problems
with different solutions. But,. for my money

Well, I'm looking for sponsors to allow me to work on synchronous
replication, amongst other issues. It looks like its going to have to be
user companies, rather than vendors that sponsor these things.

I've got the plans, I just need the time to execute them.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

Alvaro Herrera, 26.11.2007 15:07:

EnterpriseDB has no replication solution that I know of.

Quote from
http://www.enterprisedb.com/products/enterprisedb_replication.do

"EnterpriseDB Replication Server replicates data across the enterprise
in near real time to meet a wide array of business challenges. Data can
be replicated to or from heterogeneous EnterpriseDB, Oracle and
PostgreSQL databases across distant geographies"

Thomas

Alvaro Herrera wrote:

Glyn Astill wrote:

Thanks everyone for your replies. EnterpriseDB looks like the way to
go if we want good replication.

Sorry, this makes no sense to me -- EnterpriseDB has no replication
solution that I know of.

Yeah, there is:

http://www.enterprisedb.com/products/enterprisedb_replication.do

Regards. Dave.

Sorry, this makes no sense to me -- EnterpriseDB has no replication
solution that I know of.

slony is bundled with the database

Postgres-r sounds very nice but moving our organisations data onto a
system that it work in progress is very scary.

You are already offloading your data to PostgreSQL which is a work in
progress too ...

;)

Alvaro Herrera wrote:

Glyn Astill wrote:

Thanks everyone for your replies. EnterpriseDB looks like the way to
go if we want good replication.

Sorry, this makes no sense to me -- EnterpriseDB has no replication
solution that I know of.

Yeah, there is:

http://www.enterprisedb.com/products/enterprisedb_replication.do

Yes, but I'd like something better than "near real time" as the above
page describes. Or maybe someone could clarify that.... Besides,
EnterpriseDB does not save me enough money. In my current commercial
DB, if a transaction is committed on the master, it is guaranteed to
be committed to the secondary. In our business, losing one customer
order could lose us the customer for good.

Jeff

On Nov 26, 2007, at 10:14 AM, Jeff Larsen wrote:

Yes, but I'd like something better than "near real time" as the above
page describes. Or maybe someone could clarify that.... Besides,
EnterpriseDB does not save me enough money. In my current commercial
DB, if a transaction is committed on the master, it is guaranteed to
be committed to the secondary. In our business, losing one customer
order could lose us the customer for good.

So you want synchronous replication. Search on that term in the
archives for possible solutions (or lack thereof) in postgres.

If you don't specify your requirements clearly, don't expect useful
advice ;-)

jlar310@gmail.com ("Jeff Larsen") writes:

Alvaro Herrera wrote:

Glyn Astill wrote:

Thanks everyone for your replies. EnterpriseDB looks like the way to
go if we want good replication.

Sorry, this makes no sense to me -- EnterpriseDB has no replication
solution that I know of.

Yeah, there is:

http://www.enterprisedb.com/products/enterprisedb_replication.do

Yes, but I'd like something better than "near real time" as the above
page describes. Or maybe someone could clarify that.... Besides,
EnterpriseDB does not save me enough money. In my current commercial
DB, if a transaction is committed on the master, it is guaranteed to
be committed to the secondary. In our business, losing one customer
order could lose us the customer for good.

I believe that what they are using is a version of Slony-I, which
certainly falls into the "near real time" replication category.

Historically, when people think they require "something better than
near-real-time," they frequently find that the "something better"
turns out to be too expensive to live with.

"Near real time" usually refers to the notion of asynchronous
replication, where it is a little bit nondeterministic how far behind
a replica may be. (Which is definitely the case for Slony-I.)

Unfortunately, the only way to make things deterministic (or to get
from "near real time" to "*GUARANTEED* real time") is to jump to
synchronous replication, which is not much different from 2PC (Two
Phase Commit), and which is certain to be prohibitively expensive
across a WAN.

At this point, I tend to get visions of Tom Cruise telling Jack
Nicholson, "I want real time replication!", and getting the response:
"You can't HANDLE real time replication!"
--
(format nil "~S@~S" "cbbrowne" "linuxfinances.info")
http://cbbrowne.com/info/slony.html
"Any sufficiently complicated C or Fortran program contains an ad hoc
informally-specified bug-ridden slow implementation of half of Common
Lisp." -- Philip Greenspun

Yes, but I'd like something better than "near real time" as the above
page describes. Or maybe someone could clarify that.... Besides,
EnterpriseDB does not save me enough money. In my current commercial
DB, if a transaction is committed on the master, it is guaranteed to
be committed to the secondary. In our business, losing one customer
order could lose us the customer for good.

So you want synchronous replication. Search on that term in the
archives for possible solutions (or lack thereof) in postgres.

If you don't specify your requirements clearly, don't expect useful
advice ;-)

I'm not looking for advice. My original contribution to this thread
suggested a preferred course of future development. I know what my
options are with the present version, but I see room for improvement.

Jeff

Jeff Larsen wrote:

Alvaro Herrera wrote:

Glyn Astill wrote:

Yes, but I'd like something better than "near real time" as the above
page describes. Or maybe someone could clarify that.... Besides,
EnterpriseDB does not save me enough money.

Well do what EnterpriseDB does :) use Slony. Which is free of course.

In my current commercial
DB, if a transaction is committed on the master, it is guaranteed to
be committed to the secondary. In our business, losing one customer
order could lose us the customer for good.

Well in a proper asynchronous environment this is possible, e.g; if it
gets successfully replicated it will commit on the slave.

However synchronous is obviously the fool proof way to go about this as
you won't get a commit until everyone commits.

Now, if you really want to make your life cheap :)

Use PostgreSQL + Slony on two nodes, then run a third node explicitly
for use with drdbd which is synchronous block level replication.

No license fees :)

Sincerely,

Joshua D. Drake

--- Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:

Glyn Astill wrote:

Thanks everyone for your replies. EnterpriseDB looks like the way

to

go if we want good replication.

Sorry, this makes no sense to me -- EnterpriseDB has no replication
solution that I know of.

This is bullsh*t, it does as I've been talking to them this week.

Postgres-r sounds very nice but moving our organisations data

onto a

system that it work in progress is very scary.

You are already offloading your data to PostgreSQL which is a work
in
progress too ...

Except Postgress has stable releases and is proven and used in tons
of businesses.

--
Alvaro Herrera
http://www.PlanetPostgreSQL.org/
"En el principio del tiempo era el desencanto. Y era la
desolaci�n. Y era
grande el esc�ndalo, y el destello de monitores y el crujir de
teclas."
("Sean los P�jaros Pulentios", Daniel
Correa)

---------------------------(end of
broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

___________________________________________________________
Yahoo! Answers - Got a question? Someone out there knows the answer. Try it
now.
http://uk.answers.yahoo.com/

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Mon, 26 Nov 2007 18:57:19 +0000 (GMT)
Glyn Astill <glynastill@yahoo.co.uk> wrote:

--- Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:

Glyn Astill wrote:

Thanks everyone for your replies. EnterpriseDB looks like the way

to

go if we want good replication.

Sorry, this makes no sense to me -- EnterpriseDB has no replication
solution that I know of.

This is bullsh*t, it does as I've been talking to them this week.

Glyn, relax.. he did say, "that I know of".

Sincerely,

Joshua D. Drake

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHSxfvATb/zqfZUUQRAiCdAJ993n1hJgnxbmH1ewNmzBA9c+/4fACfZnu7
QR4D3O7EZd1N8GSqHf8SgDA=
=K/76
-----END PGP SIGNATURE-----

It it possible to get a system that does syncronous replication and
also allows slaves to catch up if they're down for a period of time
like you can with asyncronous?

I'm just interested.

Of course a grid or a clustwer is better to makesure all servers are
in sync, but there's performance issues with the 2 phase commit isn't
there?

Just for the record I'm a programmer, not a database person really,
so I only know the basics.

--- Jeff Larsen <jlar310@gmail.com> wrote:

Alvaro Herrera wrote:

Glyn Astill wrote:

Thanks everyone for your replies. EnterpriseDB looks like the

way to

go if we want good replication.

Sorry, this makes no sense to me -- EnterpriseDB has no

replication

solution that I know of.

Yeah, there is:

http://www.enterprisedb.com/products/enterprisedb_replication.do

Yes, but I'd like something better than "near real time" as the
above
page describes. Or maybe someone could clarify that.... Besides,
EnterpriseDB does not save me enough money. In my current
commercial
DB, if a transaction is committed on the master, it is guaranteed
to
be committed to the secondary. In our business, losing one customer
order could lose us the customer for good.

Jeff

---------------------------(end of
broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

Glyn Astill

______________________________________________________
Yahoo! Mail now has unlimited storage, which means you can have spam control and more space for those important e-mails.
http://uk.mail.yahoo.com

Glyn Astill escribi�:

It it possible to get a system that does syncronous replication and
also allows slaves to catch up if they're down for a period of time
like you can with asyncronous?

Guess what, Postgres-R is designed to do that.

Just for the record I'm a programmer, not a database person really,
so I only know the basics.

Good to know, that means I can treat anything you say as bullsh*t :-)

--
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
"No renuncies a nada. No te aferres a nada."