Everyone probably seen 100% CPU while slave recovers a WAL with lots of deletes on instance with big shared_buffers. To avoid it I usually chase sync with small shared_buffers (it reads a day of WALs in ~ one minute with 2GB and 5minutes of WAL in ~1 minute with 64GB for example) and then restart Postgres with shared_buffers = 64GB (ready for propagating a master and serving with no downtime). But what I see - it starts recovering hour old WAL! And yet changes made on master a minute ago were on the slave before taking it down. I check the state:

13138 postgres  20   0 65.8g 686m 684m R 99.7  0.6   8:07.41 postgres: startup process   recovering 00000003000009DE0000009F

and then I check the master:

postgres@master:~$ ll 9.3/main/pg_xlog/00000003000009DE0000009E
-rw------- 1 postgres postgres 16777216 Mar 14 07:57 9.3/main/pg_xlog/00000003000009DE0000009E

So it is processing WAL he was supposed to recover before restart (the WAL is hour old)! I'm missing something here - please explain?

from master settings:

  • checkpoint_completion_target is 0.9
  • checkpoint_segments 500
  • it has two other slaves reading from it with hot_standby_feedback on
  • 1
    Maybe it was restarted before the last restartpoint (which is the replay of a master checkpoint on the standby) was reached? Are your master checkpoints widely spread out? Mar 14 '17 at 9:23
  • Thanks for the answer! I updated the question with hopefully relevant info. Please take a look and give me a hint if I checked something irrelevant. I'm not sure I understand "checkpoints widely spread out"
    – Vao Tsun
    Mar 14 '17 at 9:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.