Core team statement on replication in PostgreSQL

[Heikki Linnakangas-2]

Teodor Sigaev wrote:
> Is it possible to use catalog version number as WAL version?

No, because we don't change the catalog version number in minor
releases, even though we might change WAL format.

--
   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

[Andrew Sullivan-5]

On Wed, Jun 04, 2008 at 09:24:20AM +0200, Markus Schiltknecht wrote:
>
> Are the slides of your PgCon talk available for download somewhere?

There weren't any slides, really (there were 4 that I put up in case
the cases I was discussing needed back-references, but they didn't).
Joshua tells me that I'm supposed to make the paper readable and put
it up on Command Prompt's website, so I will soon.

> BTW: up until recently, there was yet another mailing list:
> pgreplication-general@.... It was less focused on hooks
> and got at least some traffic. :-) Are those mails still archived
> somewhere?

Unless whoever was operating that list moved it to pgfoundry, I doubt
it (except on backups somewhere).

A

--
Andrew Sullivan
ajs@...
[phone] x104
http://www.commandprompt.com/

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

[Tom Lane-2]

"Heikki Linnakangas" <heikki@...> writes:
> Hmm, WAL version compatibility is an interesting question. Most minor
> releases hasn't changed the WAL format, and it would be nice to allow
> running different minor versions in the master and slave in those cases.
> But it's certainly not unheard of to change the WAL format. Perhaps we
> should introduce a WAL version number, similar to catalog version?

Yeah, perhaps.  In the past we've changed the WAL page ID field for
this; I'm not sure if that's enough or not.  It does seem like a good
idea to have a way to check that the slaves aren't trying to read a
WAL version they don't understand.  Also, it's possible that the WAL
format doesn't change across a major update, but you still couldn't
work with say an 8.4 master and an 8.3 slave, so maybe we need the
catalog version ID in there too.

                        regards, tom lane

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

[Hannu Krosing-3]

On Wed, 2008-06-04 at 10:40 -0400, Tom Lane wrote:

> "Heikki Linnakangas" <heikki@...> writes:
> > Hmm, WAL version compatibility is an interesting question. Most minor
> > releases hasn't changed the WAL format, and it would be nice to allow
> > running different minor versions in the master and slave in those cases.
> > But it's certainly not unheard of to change the WAL format. Perhaps we
> > should introduce a WAL version number, similar to catalog version?
>
> Yeah, perhaps.  In the past we've changed the WAL page ID field for
> this; I'm not sure if that's enough or not.  It does seem like a good
> idea to have a way to check that the slaves aren't trying to read a
> WAL version they don't understand.  Also, it's possible that the WAL
> format doesn't change across a major update, but you still couldn't
> work with say an 8.4 master and an 8.3 slave, so maybe we need the
> catalog version ID in there too.

And something dependent on datetime being integer.

We probably won't need to encode presence of user defined types, like
PostGis , being present ?

-----
Hannu



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

[Tom Lane-2]

Hannu Krosing <hannu@...> writes:

> On Wed, 2008-06-04 at 10:40 -0400, Tom Lane wrote:
>> "Heikki Linnakangas" <heikki@...> writes:
> Hmm, WAL version compatibility is an interesting question. Most minor
> releases hasn't changed the WAL format, and it would be nice to allow
> running different minor versions in the master and slave in those cases.
> But it's certainly not unheard of to change the WAL format. Perhaps we
> should introduce a WAL version number, similar to catalog version?
>>
>> Yeah, perhaps.  In the past we've changed the WAL page ID field for
>> this; I'm not sure if that's enough or not.  It does seem like a good
>> idea to have a way to check that the slaves aren't trying to read a
>> WAL version they don't understand.  Also, it's possible that the WAL
>> format doesn't change across a major update, but you still couldn't
>> work with say an 8.4 master and an 8.3 slave, so maybe we need the
>> catalog version ID in there too.

> And something dependent on datetime being integer.

This thread is getting out of hand, actually.

Heikki's earlier comment about pg_control reminded me that we already
have a unique system identifier stored in pg_control and check that
against WAL headers.  So I think we already have enough certainty that
the master and slaves have the same pg_control and hence are the same
for everything checked by pg_control.

However, since by definition pg_control doesn't change in a minor
upgrade, there isn't any easy way to enforce a rule like "slaves must be
same or newer minor version as the master".  I'm not sure that we
actually *want* to enforce such a rule, though.  Most of the time, the
other way around would work fine.

                        regards, tom lane

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

[Simon Riggs]

On Wed, 2008-06-04 at 11:37 -0400, Tom Lane wrote:

> This thread is getting out of hand, actually.

Agreed. We should start new threads for specific things. Please.

> However, since by definition pg_control doesn't change in a minor
> upgrade, there isn't any easy way to enforce a rule like "slaves must be
> same or newer minor version as the master".  I'm not sure that we
> actually *want* to enforce such a rule, though.

Definitely don't want to prevent minor version mismatches. We want to be
able to upgrade a standby, have it catch up with the master then
switchover to the new version. Otherwise we'd have to take whole
replicated system down to do minor upgrades/backouts. Ugh!

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


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

[Jeff Davis-8]

On Wed, 2008-06-04 at 14:17 +0300, Heikki Linnakangas wrote:
> > Would that also cover possible differences in page size, 32bit OS vs.
> > 64bit OS, different timestamp flavour, etc. issues ? AFAIR, all these
> > things can have an influence on how the data is written and possibly
> > make the WAL incompatible with other postgres instances, even if the
> > exact same version...
>
> These are already covered by the information in pg_control.

Another thing that can change between systems is the collation behavior,
which can corrupt indexes (and other bad things).

Regards,
        Jeff Davis


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

[Tom Lane-2]

Jeff Davis <pgsql@...> writes:
> On Wed, 2008-06-04 at 14:17 +0300, Heikki Linnakangas wrote:
>> These are already covered by the information in pg_control.

> Another thing that can change between systems is the collation behavior,
> which can corrupt indexes (and other bad things).

That is covered by pg_control, at least to the extent of forcing the
same value of LC_COLLATE.

                        regards, tom lane

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

[Jeff Davis-8]

On Wed, 2008-06-04 at 14:23 -0400, Tom Lane wrote:
> That is covered by pg_control, at least to the extent of forcing the
> same value of LC_COLLATE.

But the same LC_COLLATE means different things on different systems.
Even "en_US" means something different on Mac versus Linux.

Regards,
        Jeff Davis


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

[Koichi Suzuki-3]

Well, WAL format doesn't only depend on WAL itself, but also depend on
each resource manager.   If we introduce WAL format version
identification, ISTM that we have to take care of the matching of
resource manager in the master and the slave as well.

2008/6/4 Heikki Linnakangas <heikki@...>:

> Stephen Denne wrote:
>>
>> Hannu Krosing wrote:
>>>
>>> The simplest form of synchronous wal shipping would not even need
>>> postgresql running on slave, just a small daemon which reports when wal
>>> blocks are a) received and b) synced to disk.
>>
>> While that does sound simple, I'd presume that most people would want the
>> guarantee of the same version of postgresql installed wherever the logs are
>> ending up, with the log receiver speaking the same protocol version as the
>> log sender. I imagine that would be most easily achieved through using
>> something like the continuously restoring startup mode of current
>> postgresql.
>
> Hmm, WAL version compatibility is an interesting question. Most minor
> releases hasn't changed the WAL format, and it would be nice to allow
> running different minor versions in the master and slave in those cases. But
> it's certainly not unheard of to change the WAL format. Perhaps we should
> introduce a WAL version number, similar to catalog version?
>
> --
>  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
>

--
------
Koichi Suzuki

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

[Tom Lane-2]

"Koichi Suzuki" <koichi.szk@...> writes:
> Well, WAL format doesn't only depend on WAL itself, but also depend on
> each resource manager.   If we introduce WAL format version
> identification, ISTM that we have to take care of the matching of
> resource manager in the master and the slave as well.

That seems a bit overdesigned.  What are the prospects that two builds
of the same Postgres version are going to have different sets of
resource managers in them?

                        regards, tom lane

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

[Koichi Suzuki-3]

If the version of the master and the slave is different and we'd still
like to allow log shipping replication, we need a negotiation if WAL
format for the two is compatible.      I hope it is not in our scope
and I'm worrying too much.

2008/6/5 Tom Lane <tgl@...>:

> "Koichi Suzuki" <koichi.szk@...> writes:
>> Well, WAL format doesn't only depend on WAL itself, but also depend on
>> each resource manager.   If we introduce WAL format version
>> identification, ISTM that we have to take care of the matching of
>> resource manager in the master and the slave as well.
>
> That seems a bit overdesigned.  What are the prospects that two builds
> of the same Postgres version are going to have different sets of
> resource managers in them?
>
>                        regards, tom lane
>

--
------
Koichi Suzuki

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

[Gregory Stark]

"Jeff Davis" <pgsql@...> writes:

> On Wed, 2008-06-04 at 14:17 +0300, Heikki Linnakangas wrote:
>> > Would that also cover possible differences in page size, 32bit OS vs.
>> > 64bit OS, different timestamp flavour, etc. issues ? AFAIR, all these
>> > things can have an influence on how the data is written and possibly
>> > make the WAL incompatible with other postgres instances, even if the
>> > exact same version...
>>
>> These are already covered by the information in pg_control.
>
> Another thing that can change between systems is the collation behavior,
> which can corrupt indexes (and other bad things).

Well, yes and no. It's entirely possible, for example, for a minor release of
an OS to tweak the collation rules for a collation without changing the name.
For the sake of argument they might just be fixing a bug in the collation
rules. From the point of view of the OS that's a minor bug fix that they might
not foresee causing data corruption problems.

Pegging pg_control to a particular release of the OS would be pretty terrible
though. I don't really see an out for this. But it's another roadblock to
consider akin to "not-really-immutable index expressions" for any proposal
which depends on re-finding index pointers :(

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication 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]

Gurjeet Singh wrote:

> On Fri, May 30, 2008 at 10:40 AM, Tom Lane <tgl@...> wrote:
>
> > But since you mention it: one of the plausible answers for fixing the
> > vacuum problem for read-only slaves is to have the slaves push an xmin
> > back upstream to the master to prevent premature vacuuming.  The current
> > design of pg_standby is utterly incapable of handling that requirement.
> > So there might be an implementation dependency there, depending on how
> > we want to solve that problem.
> >
>
> I think it would be best to not make the slave interfere with the master's
> operations; that's only going to increase the operational complexity of such
> a solution.
>
> There could be multiple slaves following a master, some serving

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.

--
  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

[Bruce Momjian-5]

Andreas 'ads' Scherbaum wrote:

> On Fri, 30 May 2008 16:22:41 -0400 (EDT) Greg Smith wrote:
>
> > On Fri, 30 May 2008, Andreas 'ads' Scherbaum wrote:
> >
> > > Then you ship 16 MB binary stuff every 30 second or every minute but
> > > you only have some kbyte real data in the logfile.
> >
> > Not if you use pg_clearxlogtail (
> > http://www.2ndquadrant.com/replication.htm ), which got lost in the giant
> > March commitfest queue but should probably wander into contrib as part of
> > 8.4.
>
> Yes, this topic was discussed several times in the past but to
> solve this it needs a patch/solution which is integrated into PG
> itself, not contrib.

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'?

--
  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:

> 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'?

Perhaps make the zeroing user-settable.

--
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

[Tom Lane-2]

Bruce Momjian <bruce@...> writes:
> Gurjeet Singh wrote:
>> There could be multiple slaves following a master, some serving

> 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.

                        regards, tom lane

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

[Tom Lane-2]

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.

                        regards, tom lane

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

[Greg Smith-12]

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.

--
* Greg Smith gsmith@... http://www.gregsmith.com Baltimore, MD

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

[Koichi Suzuki-3]

Just for information.

In terms of archive compression, I have archive log compression which
will be found in http://pgfoundry.org/projects/pglesslog/

This feature is also included in NTT's synchronized log shipping
replication presented in the last PGCon.

2008/6/10 Greg Smith <gsmith@...>:

> 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.
>
> --
> * Greg Smith gsmith@... http://www.gregsmith.com Baltimore, MD
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@...)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
------
Koichi Suzuki

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