Markus Schiltknecht wrote:
> Bruce Momjian wrote:
> > OK, I have updated the title to be "Statement-Based Replication Using
> > Middleware". I personally think statement-based replication only makes
> > sense in middleware because when you are in the backend,
>
> I completely agree.
>
> > you have more
> > information and can do things better, either by modifying the statement
> > or passing actual data rows, like Slony does, so I want to restrict this
> > to middleware like pgpool, and Usogres, which was an early
> > implementation of this idea.
>
> That's fine and reasonable.
>
> > Am I OK now?
>
> The title and first paragraph are fine.
>
> I'd still say that the second paragraph, about limitations is too pgpool
> specific. How's that for sequoia?
OK, I made it more open-ended:
If queries are simply broadcast unmodified, functions like
<function>random()</>, <function>CURRENT_TIMESTAMP</>, and
sequences would have different values on different servers.
This is because each server operates independently, and because
SQL queries are broadcast (and not actual modified rows). If
this is unacceptable, either the middleware or the application
must query such values from a single server and then use those
values in write queries. Also, care must be taken that all
transactions either commit or abort on all servers, perhaps
using two-phase commit (<xref linkend="sql-prepare-transaction"
endterm="sql-prepare-transaction-title"> and <xref
linkend="sql-commit-prepared" endterm="sql-commit-prepared-title">.
Pgpool is an example of this type of replication.
> And I'm unsure what you mean by mentioning 2PC there. Do you have to
> 'make sure every transaction commits or aborts' yourself with pgpool? Or
> did you just want to mention that pgpool does (and has to do) that for you?
I am not sure pgpool does that, but perhaps it should. Looking at the
pgpool web site, it seems it does not use 2PC (see replication_strict):
http://pgpool.projects.postgresql.org/
replication_mode
set this true if you are going to use replication functionality.
Default is false.
replication_strict
If true, pgpool will wait for the completion of the master query
before sending a query to the secondary server. This is the safest and
default operating mode for pgpool. Default is true.
The HA docs merely say that 2PC might be a good way to keep the servers
consistent.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
