Re: Shared WAL archive between master and standby: WALs not always identical

Am 28.02.2017 02:50 schrieb "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com
>:

It is customary to inline or bottom-posts on these lists. Please follow
the example of those responding to your emails.

On Mon, Feb 27, 2017 at 6:45 PM, Sasa Vilic <sasavilic(at)gmail(dot)com> wrote:

> And also this:
>
> """
> If archive_mode is set to on, the archiver is not enabled during recovery
> or standby mode. If the standby server is promoted, it will start archiving
> after the promotion, but will not archive any WAL it did not generate
> itself. To get a complete series of WAL files in the archive, you must
> ensure that all WAL is archived, before it reaches the standby
> """
>
> If I understand it correctly, WAL started on master but finished on
> standby (after failover) will still not be (old master is presumably dead
> and new master must wait for next WAL segment). Of course, the next WAL
> segment will be sent by new master, but we are going to miss exactly this
> one WAL segment during which failover occurred and thus introduce
> interruption in our WAL stream. Am I right?
>

​Requires knowledge and familiarity I present lack. Sorry. I think I see
where you are going with all of this but it would probably help to
explicitly state the overall concern or plan and not just ask how specific
mechanics work in isolation.

David J.
​

Hi David,

sorry about email formating. I didn't realize that such convetions exists
as I am first time here + I am currently using gmail mobile app. I hope it
looks good this time. :)

My general idea is to have synchronous hot standby and asynchronous shared
wal archive. If that were possible I could actually switch back and forth
between master and slave without interrupting wal stream and with very
short downtime. This also makes PostgreSQL upgrade very easy. Alternative
to this is to have separate backup for master and slave, but this has other
disadvantages:

* I need double disk space for backup if I am going to archive WALs from
standby and master at the same time, or
* I could only archive WALs from current master, but that would require
performing base backup immediately after failover. Otherwise archived WALs
are useless.

Both of these solutions are good solution but not perfect. I thought that
shared wal archive is possible, based on PostgreSQL documention. I also
assume that requirement "not to overwrite existing WAL with different
content" was only there to prevent from accidental mis-configuration.

I wasn't aware that standby server would produce different WAL. My current
test setup looks just like that. And it works, except for 2-3 WALs per day
that are not identical. Everything else is same. I can even restore backup
without issues, but those non-identical WALs still throuble me because I
fear that I am missing something big.

I wrote a small python script to perform WAL decoding and I hope get more
information tommorow morning. Since I was playing today with repmgr (which
internaly uses pg_rewind for switchover) I got suspicios that this could be
it.

Anyway, I hopped that someone had similar setup and experience with it. :(

Sorry for such lengthly email.

Regards,
Sasa