Core team statement on replication in PostgreSQL

[Gregory Stark]

"Greg Smith" <gsmith@...> writes:

> On Mon, 9 Jun 2008, Tom Lane wrote:
>
>> It should also be pointed out that the whole thing becomes uninteresting
>> if we get real-time log shipping implemented.  So I see absolutely no
>> point in spending time integrating pg_clearxlogtail now.
>
> There are remote replication scenarios over a WAN (mainly aimed at disaster
> recovery) that want to keep a fairly updated database without putting too much
> traffic over the link.  People in that category really want zeroed
> tail+compressed archives, but probably not the extra overhead that comes with
> shipping smaller packets in a real-time implementation.

Instead of zeroing bytes and depending on compression why not just pass an
extra parameter to the archive command with the offset to the logical end of
data. The archive_command could just copy from the start to that point and not
bother transferring the rest.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's RemoteDBA services!

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Heikki Linnakangas-2]

Gregory Stark wrote:
> Instead of zeroing bytes and depending on compression why not just pass an
> extra parameter to the archive command with the offset to the logical end of
> data.

Because the archiver process doesn't have that information.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Josh Berkus]

All,

> > For the slave to not interfere with the master at all, we would need to
> > delay application of WAL files on each slave until visibility on that
> > slave allows the WAL to be applied, but in that case we would have
> > long-running transactions delay data visibility of all slave sessions.
>
> Right, but you could segregate out long-running queries to one slave
> server that could be further behind than the others.

I still see having 2 different settings:

Synchronous: XID visibility is pushed to the master.  Maintains synchronous
failover, and users are expected to run *1* master to *1* slave for most
installations.

Asynchronous: replication stops on the slave whenever minxid gets out of
synch.  Could have multiple slaves, but noticeable lag between master and
slave.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[ITAGAKI Takahiro-2]

Josh Berkus <josh@...> wrote:

> I still see having 2 different settings:
>
> Synchronous: XID visibility is pushed to the master.  Maintains synchronous
> failover, and users are expected to run *1* master to *1* slave for most
> installations.
>
> Asynchronous: replication stops on the slave whenever minxid gets out of
> synch.  Could have multiple slaves, but noticeable lag between master and
> slave.

I agree with you that we have sync/async option in log-shipping.
Also, we could have another setting - synchronous-shipping and
asynchronous-flushing. We won't lose transactions if both servers are
down at once and can avoid delays to flush wal files into primary's disks.

As for multiple slaves, we could have a cascading configuration;
WAL receiver also delivers WAL records to other servers.
I think it is simple that the postgres core has only one-to-one replication
and multiple slaves are supported by 3rd party's WAL receivers.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center



--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Kevin Grittner]

>>> On Mon, Jun 9, 2008 at  9:48 PM, in message
<Pine.GSO.4.64.0806092243080.11286@...>, Greg Smith
<gsmith@...> wrote:
> On Mon, 9 Jun 2008, Tom Lane wrote:
>
>> It should also be pointed out that the whole thing becomes
uninteresting
>> if we get real-time log shipping implemented.  So I see absolutely
no
>> point in spending time integrating pg_clearxlogtail now.
>
> There are remote replication scenarios over a WAN (mainly aimed at
> disaster recovery) that want to keep a fairly updated database
without
> putting too much traffic over the link.  People in that category
really
> want zeroed tail+compressed archives, but probably not the extra
overhead
> that comes with shipping smaller packets in a real-time
implementation.
 
We ship the WAL files over a (relatively) slow WAN for disaster
recovery purposes, and we would be fine with replacing our current
techniques with real-time log shipping as long as:
 
(1)  We can do it asynchronously.  (i.e., we don't have to wait for
WAN latency to commit transactions.)
 
(2)  It can ship to multiple targets.  (Management dictates that we
have backups at the site of origin as well as our central site.  A
failure to replicate to one must not delay the other.)
 
(3)  It doesn't consume substantially more WAN bandwidth overall.
 
A solution which fails to cover any of these leaves pg_clearxlogtail
interesting to us.
 
-Kevin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Robert Hodges]

Hi everyone,

This is a quick update on a promise I made early in June to suggest requirements as well as ways to add replication hooks that would support logical replication, as opposed to the physical replication work currently underway based on NTT’s code.  

Well, June was a pretty busy month, so it has taken a while to get back to this.  However, we are now beginning to examine options for PostgreSQL logical replication.  To make a long story short we are willing to commit resources to this problem or fund other people to do it for us.  If you are interested please contact me directly.  Meanwhile, we are quite serious about this problem and intend to work on helpful additions to PostgreSQL in this area.  I will post more as we make progress.  

Thanks, Robert

--
Robert Hodges, CTO, Continuent, Inc.
Email:  robert.hodges@...

P.s., Happy 12th birthday everyone!

[Marko Kreen-2]

On 7/10/08, Robert Hodges <robert.hodges@...> wrote:

>  This is a quick update on a promise I made early in June to suggest
> requirements as well as ways to add replication hooks that would support
> logical replication, as opposed to the physical replication work currently
> underway based on NTT's code.
>
>  Well, June was a pretty busy month, so it has taken a while to get back to
> this.  However, we are now beginning to examine options for PostgreSQL
> logical replication.  To make a long story short we are willing to commit
> resources to this problem or fund other people to do it for us.  If you are
> interested please contact me directly.  Meanwhile, we are quite serious
> about this problem and intend to work on helpful additions to PostgreSQL in
> this area.  I will post more as we make progress.

Well, I'm not exactly sure what you are planning.  It's OK to do
draft design privately, but before actually starting coding, the
design should be discussed in -hackers.

And I'm not exactly sure what you mean in "logical replication"?
Way to log DDL statements?  Do you want to log DML also?

FWIW, here's very draft design for functionality that could be used
to make current Slony-I/Londiste-like solutions to replicate DDL also.

1. CREATE DDL TRIGGER statement that allows to call function
   for all DDL statements.

   Only filtering that makes sense here is filtering by area:
   tables/functions/views/etc.

   It must be possible to do AFTER trigger.  Whether BEFORE
   trigger for DDL make sense or not, I'm not sure.

2. When function is called, following information is given:

   - Object type the event was for (table/view/function)
   - Array of object names.
   - SQL statement as text.

   The trigger function can filter further based on object names
   whether it does want to log the event or not.

Trying to make the trigger run on only subset of events is complex,
and parsing the SQL to pieces for trigger to understand it better
is also complex and neither is needed.  Unless there are some common
situation where such simple design fails to work, I would not make
the scheme more complex.

Also the design should be based on assumption that the target side
is exactly in sync.  Eg. DROP CASCADE should be replicated as DROP CASCADE.
We should not make scheme more complex to survive cases where target
is not in sync.  That way madness lies.  The effect should be like
same SQL statements are applied to target by hand, no more, no less.

--
marko

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Robert Hodges]

Hi Marko,

No fear, we definitely will discuss on pgsql-hackers.  I just wanted to make sure that people understood we are still committed to solving this problem and will one way or another commit resources to help.  

Just to be clear, by logical replication I mean replication based on sending SQL or near-SQL (e.g., generic DML events) between servers.   Physical replication on the other hand uses internal formats to replicate changes without intervening conversion to SQL, for example by shipping WAL records.  There are advantages to each for different applications.  BTW, I heard this nomenclature from Simon Riggs.  It seems quite helpful.

The DDL trigger proposal is interesting and would be a very useful feature addition to PostgreSQL.  To execute correctly it may also be necessary to know which database you were using at the time the SQL was issued.  

For our part we are looking for ways to replicate most or all data on a server as efficiently as possible.  Generic call-outs at commit time or reading the log directly are attractive approaches.  Depending on the implementation you can avoid double writes of replicated data on the master host.  Also, it avoids the management headache of ensuring that triggers are correctly installed.  It seems as if one of these generic approaches could hook into WAL record transport.

Cheers, Robert

On 7/10/08 4:56 AM, "Marko Kreen" <markokr@...> wrote:

On 7/10/08, Robert Hodges <robert.hodges@...> wrote:
>  This is a quick update on a promise I made early in June to suggest
> requirements as well as ways to add replication hooks that would support
> logical replication, as opposed to the physical replication work currently
> underway based on NTT's code.
>
>  Well, June was a pretty busy month, so it has taken a while to get back to
> this.  However, we are now beginning to examine options for PostgreSQL
> logical replication.  To make a long story short we are willing to commit
> resources to this problem or fund other people to do it for us.  If you are
> interested please contact me directly.  Meanwhile, we are quite serious
> about this problem and intend to work on helpful additions to PostgreSQL in
> this area.  I will post more as we make progress.

Well, I'm not exactly sure what you are planning.  It's OK to do
draft design privately, but before actually starting coding, the
design should be discussed in -hackers.

And I'm not exactly sure what you mean in "logical replication"?
Way to log DDL statements?  Do you want to log DML also?

FWIW, here's very draft design for functionality that could be used
to make current Slony-I/Londiste-like solutions to replicate DDL also.

1. CREATE DDL TRIGGER statement that allows to call function
   for all DDL statements.

   Only filtering that makes sense here is filtering by area:
   tables/functions/views/etc.

   It must be possible to do AFTER trigger.  Whether BEFORE
   trigger for DDL make sense or not, I'm not sure.

2. When function is called, following information is given:

   - Object type the event was for (table/view/function)
   - Array of object names.
   - SQL statement as text.

   The trigger function can filter further based on object names
   whether it does want to log the event or not.

Trying to make the trigger run on only subset of events is complex,
and parsing the SQL to pieces for trigger to understand it better
is also complex and neither is needed.  Unless there are some common
situation where such simple design fails to work, I would not make
the scheme more complex.

Also the design should be based on assumption that the target side
is exactly in sync.  Eg. DROP CASCADE should be replicated as DROP CASCADE.
We should not make scheme more complex to survive cases where target
is not in sync.  That way madness lies.  The effect should be like
same SQL statements are applied to target by hand, no more, no less.

--
marko

--
Robert Hodges, CTO, Continuent, Inc.
Email:  robert.hodges@...
Mobile:  [phone]  Skype:  hodgesrm

[Christopher Browne]

markokr@... ("Marko Kreen") writes:
> Also the design should be based on assumption that the target side
> is exactly in sync.  Eg. DROP CASCADE should be replicated as DROP CASCADE.
> We should not make scheme more complex to survive cases where target
> is not in sync.  That way madness lies.  The effect should be like
> same SQL statements are applied to target by hand, no more, no less.

We have, already, in 8.4, a handling of triggers for TRUNCATE; the
reason why support hasn't made it into Slony-I yet relates quite
exactly to this...

The trouble comes in if you do TRUNCATE CASCADE; I'm not quite sure
how to collect together the multiple recordings of the trigger
functions that would be collected as a result; for it all to work,
safely, on the remote node, we'd need to apply all of those truncates
at once.

Note also that there is an issue with coordination of schemas; Slony-I
shuts off the RI triggers on subscribers, so that the target is fairly
certain to not be *entirely* in sync, by express intent.

Those are legitimate differences between source and target.
--
select 'cbbrowne' || '@' || 'linuxfinances.info';
http://cbbrowne.com/info/lsf.html
Rules  of the  Evil Overlord  #145. "My  dungeon cell  decor  will not
feature exposed pipes.  While they add to the  gloomy atmosphere, they
are good  conductors of vibrations and  a lot of  prisoners know Morse
code." <http://www.eviloverlord.com/>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Bruce Momjian-5]

Tom Lane wrote:

> Bruce Momjian <bruce@...> writes:
> > Agreed.  I realize why we are not zeroing those bytes (for performance),
> > but can't we have the archiver zero those bytes before calling the
> > 'archive_command'?
>
> The archiver doesn't know any more about where the end-of-data is than
> the archive_command does.  Moreover, the archiver doesn't know whether
> the archive_command cares.  I think the separate module is a fine
> solution.
>
> It should also be pointed out that the whole thing becomes uninteresting
> if we get real-time log shipping implemented.  So I see absolutely no
> point in spending time integrating pg_clearxlogtail now.

People doing PITR are still going to be saving these files, and for a
long time, so I think this is still something we should try to address.

Added to TODO:

        o Reduce PITR WAL file size by removing full page writes and
          by removing trailing bytes to improve compression

--
  Bruce Momjian  <bruce@...>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Alvaro Herrera-7]

Bruce Momjian wrote:

> Added to TODO:
>
>         o Reduce PITR WAL file size by removing full page writes and
>           by removing trailing bytes to improve compression

If we remove full page writes, how does hint bit setting get propagated
to the slave?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Bruce Momjian-5]

Alvaro Herrera wrote:
> Bruce Momjian wrote:
>
> > Added to TODO:
> >
> >         o Reduce PITR WAL file size by removing full page writes and
> >           by removing trailing bytes to improve compression
>
> If we remove full page writes, how does hint bit setting get propagated
> to the slave?

We would remove full page writes that are needed for crash recovery, but
perhaps keep other full pages.

--
  Bruce Momjian  <bruce@...>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Alvaro Herrera-7]

Bruce Momjian wrote:

> Alvaro Herrera wrote:
> > Bruce Momjian wrote:
> >
> > > Added to TODO:
> > >
> > >         o Reduce PITR WAL file size by removing full page writes and
> > >           by removing trailing bytes to improve compression
> >
> > If we remove full page writes, how does hint bit setting get propagated
> > to the slave?
>
> We would remove full page writes that are needed for crash recovery, but
> perhaps keep other full pages.

How do you tell which is which?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[Bruce Momjian-5]

Alvaro Herrera wrote:

> Bruce Momjian wrote:
> > Alvaro Herrera wrote:
> > > Bruce Momjian wrote:
> > >
> > > > Added to TODO:
> > > >
> > > >         o Reduce PITR WAL file size by removing full page writes and
> > > >           by removing trailing bytes to improve compression
> > >
> > > If we remove full page writes, how does hint bit setting get propagated
> > > to the slave?
> >
> > We would remove full page writes that are needed for crash recovery, but
> > perhaps keep other full pages.
>
> How do you tell which is which?

The WAL format would have to be modified to indicate which entries can
be discarded.

--
  Bruce Momjian  <bruce@...>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers