Subject: Re: WAL segment not replicated
To: Ted EH <ted.elhourani@gmail.com>, pgsql-admin@lists.postgresql.org
References: 
 <CA+G4h2AF43cw9hF1sSDrUHGz92NZiGDnUC4-cDYudFxpuf+zNA@mail.gmail.com>
From: Ian Barwick <ian.barwick@2ndquadrant.com>
Message-ID: <e009c407-3266-fdcb-c84d-5449c235654a@2ndquadrant.com>
Date: Thu, 1 Mar 2018 13:51:28 +0900
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.6.0
MIME-Version: 1.0
In-Reply-To: 
 <CA+G4h2AF43cw9hF1sSDrUHGz92NZiGDnUC4-cDYudFxpuf+zNA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

On 03/01/2018 11:43 AM, Ted EH wrote:
 > I have a 2 node active/standby setup, with synchronous streaming enabled.
 >
 > WAL segments are replicated as expected on the standby.
 >
 > However, if I manually kill the postgres process with pkill on the primary

Why are you using pkill (and how)?

 > I'm ending up with a standby WAL behind that of the primary. The primary
 > seems to be incrementing its WAL automatically after I restart it.
 >
 > Example:
 > Before killing the pg process:
 > Primary & standby seem to be  synchronizing synchronously:
 >
 >   pg_last_wal_receive_lsn
 > -------------------------
 >   0/5E0108B0
 > (1 row)
 >
 >   pg_current_wal_lsn
 > --------------------
 >   0/5E0108B0
 > (1 row)
 >
 > Now I do "pkill postgres", on the primary the WAL dir has
 > 00000001000000000000005F as the lastest segment (file)  (supposed to be 5E, but unexpectedly getting incremented)
 > while the standby has:
 > 00000001000000000000005E as its lastest segment
 >
 > The problem is if I want to restart the primary as a standby (swapping the roles),
 > it will complain about asking for a WAL too far in the future that is not available
 > on the new primary (old standby):
 >
 > could not receive data from WAL stream: ERROR:  requested starting point 0/5F000000 is ahead of the WAL flush position of this server 0/5E0CBF38
 >
 > requested starting point 0/5F000000 is ahead of the WAL flush position of this server 0/5E0D3200
 >
 > Isn't the primary (original primary) expected to know how far is its standby?

Nope. Once you promote the standby, it's an independent node; what happens
on the original primary is of no consequence.

If the primary was shut down cleanly *before* the standby was promoted,
you should be able to reattach it as a standby.

However it sounds like you've forced an unclean shutdown of your (former)
primary without everything being flushed to the standby. After restarting
the  (former) primary it's probably gone into automatic recovery, replaying
some WAL not flushed to the standby, thus creating a divergence from your
standby, which can't be resolved without manual intervention.

 > Doing a base backup recovery is not an option for me at this point.
 >
 > This is "pg_ctl (PostgreSQL) 10.2"

pg_rewind could potentially help:

   https://www.postgresql.org/docs/current/static/app-pgrewind.html


Regards

Ian Barwick


-- 
  Ian Barwick                   http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services