pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
WAL segment not replicated 3+ messages / 2 participants [nested] [flat]
* WAL segment not replicated @ 2018-03-01 02:43 Ted EH <ted.elhourani@gmail.com> 2018-03-01 04:51 ` Re: WAL segment not replicated Ian Barwick <ian.barwick@2ndquadrant.com> 0 siblings, 1 reply; 3+ messages in thread From: Ted EH @ 2018-03-01 02:43 UTC (permalink / raw) To: pgsql-admin@lists.postgresql.org 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 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? Doing a base backup recovery is not an option for me at this point. This is "pg_ctl (PostgreSQL) 10.2" ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: WAL segment not replicated 2018-03-01 02:43 WAL segment not replicated Ted EH <ted.elhourani@gmail.com> @ 2018-03-01 04:51 ` Ian Barwick <ian.barwick@2ndquadrant.com> 2018-03-01 22:24 ` Re: WAL segment not replicated Ted EH <ted.elhourani@gmail.com> 0 siblings, 1 reply; 3+ messages in thread From: Ian Barwick @ 2018-03-01 04:51 UTC (permalink / raw) To: Ted EH <ted.elhourani@gmail.com>; pgsql-admin@lists.postgresql.org 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: WAL segment not replicated 2018-03-01 02:43 WAL segment not replicated Ted EH <ted.elhourani@gmail.com> 2018-03-01 04:51 ` Re: WAL segment not replicated Ian Barwick <ian.barwick@2ndquadrant.com> @ 2018-03-01 22:24 ` Ted EH <ted.elhourani@gmail.com> 0 siblings, 0 replies; 3+ messages in thread From: Ted EH @ 2018-03-01 22:24 UTC (permalink / raw) To: Ian Barwick <ian.barwick@2ndquadrant.com>; +Cc: pgsql-admin@lists.postgresql.org My reason for killing the process is to test behavior in the event of an unclean shutdown. In the test, I made sure the former primary is being demoted to standby. To keep things simple, I have repeated the test without restarting the former primary, and without pormoting the stby. Before killing the main pg server process, both primary and stby have 00065 as the latest segment, under pg_wal This time all I did is "sudo pkill postmaster", after which under pg_wal On the stby the latest segment is still 00064, while on the primary it is now 00065 Which means a process continues to run on the primary and creates the next WAL segment (...65). Is this expected? and why? ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-03-01 22:24 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2018-03-01 02:43 WAL segment not replicated Ted EH <ted.elhourani@gmail.com> 2018-03-01 04:51 ` Ian Barwick <ian.barwick@2ndquadrant.com> 2018-03-01 22:24 ` Ted EH <ted.elhourani@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox